Let me explain each of the statements individually.
cursor c_emp is
select ename,deptno from emp;
The above statement declares a cursor ‘c_emp’ which can hold all employee names and department numbers from the table ‘emp’.
cursor c_dept is
select deptno,dname from dept;
The above statement declares a cursor ‘c_dept’ which can hold all department numbers and department names from the table ‘dept’.
The above two statements declare two variables, each based on its own cursor type. That means ‘r_emp’ can hold both the employee name and the department number at one time; similarly, ‘r_dept’ can hold both department number and department name. Each of those variables can hold only one row (one set of values) from their respective cursors at a time.
fetch c_dept into r_dept;
exit when c_dept%notfound;
The first statement opens the cursor ‘c_dept’. We start a loop to fetch each and every row from ‘c_dept’. We fetch each and every successive row from ‘c_dept’ into ‘r_dept’ (for every iteration). And finally we exit the loop, if the fetch could not find any row to fetch.
The above statements display the current department name fetched in the form of a heading.
fetch c_emp into r_emp;
exit when c_emp%notfound;
Now, we fetch each and every row from cursor ‘c_emp’ into ‘r_emp’ using a loop.
if r_emp.deptno = r_dept.deptno then
I am checking whether the ‘deptno’ of ‘r_dept’ (currently related to the outer loop) is matching with the ‘deptno’ of ‘r_emp’ or not. If it matches, the employee belongs to that department. So we immediately display the employee name.
Finally, we close each of the cursors, based on the termination of the respective loops.
blog comments powered by Disqus