-- Create the Employee table if it doesn't exist
CREATE TABLE Employee (
EmpId VARCHAR2(10),
EmpName VARCHAR2(50),
Job VARCHAR2(50),
Manager VARCHAR2(10),
DeptNo VARCHAR2(10),
HireDate DATE,
Salary NUMBER(10, 2),
Comm NUMBER(10, 2)
);
-- Insert the values into the Employee table
INSERT INTO Employee (EmpId, EmpName, Job, Manager, DeptNo, HireDate, Salary, Comm)
VALUES ('E0001', 'Abey', 'Tester', 'E0004', 'D004', TO_DATE('15/12/12', 'DD/MM/YY'), 30000, 500);
INSERT INTO Employee (EmpId, EmpName, Job, Manager, DeptNo, HireDate, Salary, Comm)
VALUES ('E0002', 'Jesto', 'Analyst', 'E0001', 'D002', TO_DATE('24/04/11', 'DD/MM/YY'), 25000, 650);
INSERT INTO Employee (EmpId, EmpName, Job, Manager, DeptNo, HireDate, Salary, Comm)
VALUES ('E0003', 'Adarsh', 'Clerk', 'E0004', 'D004', TO_DATE('10/01/13', 'DD/MM/YY'), 15000, 500);
INSERT INTO Employee (EmpId, EmpName, Job, Manager, DeptNo, HireDate, Salary, Comm)
VALUES ('E0004', 'Kevin', 'Admin', 'E0005', 'D002', TO_DATE('10/10/13', 'DD/MM/YY'), 20000, 1750);
INSERT INTO Employee (EmpId, EmpName, Job, Manager, DeptNo, HireDate, Salary, Comm)
VALUES ('E0005', 'Bony', 'Manager', NULL, 'D001', TO_DATE('11/04/11', 'DD/MM/YY'), 50000, 1000);
INSERT INTO Employee (EmpId, EmpName, Job, Manager, DeptNo, HireDate, Salary, Comm)
VALUES ('E0006', 'Manu', 'Supplier', 'E0001', 'D003', TO_DATE('19/06/13', 'DD/MM/YY'), 5000, 450);
DECLARE
-- Declare variables to store employee details
v_emp_id employee.empid%TYPE;
v_emp_name employee.empname%TYPE;
v_job employee.job%TYPE;
v_manager employee.manager%TYPE;
v_dept_no employee.deptno%TYPE;
v_hire_date employee.hiredate%TYPE;
v_salary employee.salary%TYPE;
v_comm employee.comm%TYPE;
-- Declare an explicit cursor for the employees table
CURSOR emp_cursor IS
SELECT empid, empname, job, manager, deptno, hiredate, salary, comm
FROM employee;
BEGIN
-- Open the explicit cursor
-- Fetch and display employee details using a FOR loop
FOR emp_rec IN emp_cursor LOOP
-- Assign values to variables from cursor record
v_emp_id := emp_rec.empid;
v_emp_name := emp_rec.empname;
v_job := emp_rec.job;
v_manager := emp_rec.manager;
v_dept_no := emp_rec.deptno;
v_hire_date := emp_rec.hiredate;
v_salary := emp_rec.salary;
v_comm := emp_rec.comm;
-- Display the employee details
DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_emp_id);
DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_emp_name);
DBMS_OUTPUT.PUT_LINE('Job: ' || v_job);
DBMS_OUTPUT.PUT_LINE('Manager: ' || v_manager);
DBMS_OUTPUT.PUT_LINE('Department Number: ' || v_dept_no);
DBMS_OUTPUT.PUT_LINE('Hire Date: ' || v_hire_date);
DBMS_OUTPUT.PUT_LINE('Salary: ' || v_salary);
DBMS_OUTPUT.PUT_LINE('Commission: ' || v_comm);
DBMS_OUTPUT.PUT_LINE('------------------------');
END LOOP;
-- Close the cursor
CLOSE emp_cursor;
END;
/
Write, Run & Share PL/SQL code online using OneCompiler's Oracle PL/SQL online editor and compiler for free. It's one of the robust, feature-rich online editor and compiler for Oracle PL/SQL running on latest version 23c (23.3.0.0). Getting started with the OneCompiler's Oracle PL/SQL editor is really simple and pretty fast. The editor shows sample boilerplate code when you choose language as 'PL/SQL' and start writing code to learn and test online without worrying about tedious process of installation.
PL/SQL is procedural extension for SQL created by Oracle. It is by default embedded into the Oracle Database. PL/SQL program units are compiled and stored inside the Oracle Database which results in optimal execution times as the PL/SQL and SQL run within the same server process.
Following is the syntax structure for the PL/SQL code blocks
DECLARE
<declarations section>
BEGIN
<executable command(s)>
EXCEPTION
<exception handling>
END;
DECLARE
message varchar2(100):= 'Hello, World!';
BEGIN
dbms_output.put_line(message);
END;
/
CREATE OR REPLACE FUNCTION
hello_user
(user_name IN VARCHAR2)
RETURN VARCHAR2
IS
BEGIN
RETURN 'Hello ' || user_name;
END hello_user;
/
BEGIN
dbms_output.put_line(hello_user('Peter'));
END;
/
BEGIN
DBMS_OUTPUT.put_line (1/0);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('error is: ' || SQLERRM);
END;