Database Interaction with PL/SQL: Explict Cursors in Depth - How the program works

(Page 3 of 6 )

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’.

r_emp   c_emp%rowtype;

  r_dept      c_dept%rowtype;

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.

open c_dept;

  loop

        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.

dbms_output.put_line('-----------------');

        dbms_output.put_line(r_dept.dname);

        dbms_output.put_line('-----------------');

The above statements display the current department name fetched in the form of a heading.

open c_emp;

        loop

              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

        dbms_output.put_line(r_emp.ename);

  end if;

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.

end loop;

        close c_emp;

  end loop;

  close c_dept;

end;

Finally, we close each of the cursors, based on the termination of the respective loops. 

Next: Further approaches to the above program >>
 

More Oracle Articles
More By Jagadish Chatarji

Comments On: Database Interaction with PL/SQL: Explict Cursors in Depth

· Hello guys, this is my in-depth article on working with explicit cursors in PL/SQL....    · Thank you very much for your in depth look at cursors. It helps me see what else can...   >>> Post your comment now!