Create cursor in nested loops & display records
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 cursor in nested loops and display the records department wise.
Answer:
DECLARE
CURSOR c_dept IS
SELECT *
FROM department
WHERE manager_id IS NOT NULL
ORDER BY dept_name;
r_dept c_dept%ROWTYPE;
--Declaration of department cursor and record variable.
CURSOR c_emp (c_dept_no department.dept_id%TYPE) IS
SELECT *
FROM emp_detail
WHERE dept_id = c_dept_no;
r_emp c_emp%ROWTYPE;
--Declaration of employees cursor and record variable.
BEGIN
OPEN c_dept;
LOOP
FETCH c_dept INTO r_dept;
EXIT WHEN c_dept%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('----------------------------------');
DBMS_OUTPUT.PUT_LINE('Department Name : '||r_dept.dept_name);
DBMS_OUTPUT.PUT_LINE('----------------------------------');
OPEN c_emp(r_dept.dept_id);
LOOP
FETCH c_emp INTO r_emp;
EXIT WHEN c_emp%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Employees Details : '||r_emp.employee_id
||' '||r_emp.first_name||' '||r_emp.last_name||' '||r_emp.salary);
END LOOP;
CLOSE c_emp;
END LOOP;
CLOSE c_dept;
END;
The above code will display the records of employees for selected columns as department wise.
Output: