Database Interaction with PL/SQL: Explict Cursors in Depth (
Page 1 of 6 )
This is part 11 of a series of articles focusing on database interactions
with Oracle PL/SQL. In my previous article, I introduced the concept of the
explicit cursor and looked at several approaches for using the explicit cursors.
In this article, we shall delve more deeply into explicit cursors.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;
/ 24 
