Database Interaction with PL/SQL: Explict Cursors in Depth

Please note that all the examples in this series have been tested only with Oracle 10g. I didn’t really test them with all the previous versions of Oracle. I suggest you to refer the documentation of respective version you are using, if any of the programs failed to execute.

A different example

In my previous article, I gave some examples of explicit cursors. But you can also achieve the same output (of that example) using only SQL statements. Now we shall see a different example using the same concept of explicit cursor.  Let us consider the following example:

declare

  cursor c_dept is

        select deptno,dname from dept;

  r_dept      c_dept%rowtype;

  v_sumsal    number(5);

  v_maxsal    emp.sal%type;

  v_count           number(2);

begin

  open c_dept;

  loop

        fetch c_dept into r_dept;

        exit when c_dept%notfound;

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

        dbms_output.put_line(r_dept.dname);

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

        select sum(sal) into v_sumsal from emp

              where deptno = r_dept.deptno;

        select max(sal) into v_maxsal from emp

              where deptno = r_dept.deptno;

        select count(sal) into v_count from emp

              where deptno = r_dept.deptno;

        dbms_output.put_line('No. of employees:' || v_count);

        dbms_output.put_line('Highest Salary:' || v_maxsal);

        dbms_output.put_line('Total Salaries:' || v_sumsal);

  end loop;

  close c_dept;

end;

The above program iterates through each of the departments and finally displays the output as something like the following:

--------------
ACCOUNTING
--------------
No. of employees:3
Highest Salary:5000
Total Salaries:8750
--------------
RESEARCH
--------------
No. of employees:5
Highest Salary:3000
Total Salaries:10875

The above output looks somewhat different from the traditional SQL output. We can control the lines of output in our own way. The output need not be in the form of rows divided into columns. We can display anything we like through PL/SQL in a controlled manner. I hope the above program is very simple. I am using three SELECT…INTO statements based on the current department number retrieved through cursor. You can further simplify the same above program as follows (with the use of FOR loop):

declare

  cursor c_dept is

        select deptno,dname from dept;

  v_sumsal    number(5);

  v_maxsal    emp.sal%type;

  v_count           number(2);

begin

  for r_dept in c_dept;

  loop

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

        dbms_output.put_line(r_dept.dname);

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

        select sum(sal), max(sal), count(sal)

              into v_sumsal,v_maxsal,v_count from emp

              where deptno = r_dept.deptno;

        dbms_output.put_line('No. of employees:' || v_count);

        dbms_output.put_line('Highest Salary:' || v_maxsal);

        dbms_output.put_line('Total Salaries:' || v_sumsal);

  end loop;

end;