Create Explicit Cursor - PL/SQL Program
Consider the following tables to complete the following assignment.
Table 1: 'Emp_Detail'
| Employee_id | First_Name | Last_name | Salary | DEPT_ID |
|---|
| 1 | Shruti | Shrabya | 50000 | 1 |
| 2 | Jaya | Singh | 10000 | 2 |
| 3 | Mangala | Thokal | 60000 | 3 |
| 4 | Surendra | Maurya | 70000 | 4 |
Table2: 'Department'
| Dept_ID | Dept_Name | Manager_ID |
|---|
| 1 | Accounting | 1 |
| 2 | Shipping | 3 |
| 3 | Store | 3 |
Q. Write a PL/SQL block to create a explicit cursor using For Loop and display the employees who have salary greater than 48000.
Answer:
DECLARE
CURSOR c_detail IS
SELECT dept_name,d.dept_id,first_name,salary
FROM department d JOIN emp_detail e
ON e.dept_id = d.dept_id
WHERE salary > 48000;
BEGIN
FOR item IN c_detail
LOOP
DBMS_OUTPUT.PUT_LINE(item.first_name||' '||item.dept_name||' '||item.dept_id||' '||' '||item.salary);
END LOOP;
END;
This code will create a explicit cursor and display the records of employees for selected columns and employees who have salary greater the 48000.
Output:
