Database Interaction with PL/SQL: Explict Cursors in Depth

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;

{mospagebreak title=Working with more than one cursor}

We can work with more than one cursor at a time by using nested loops. Let us consider the following example:

declare

  cursor c_emp is

        select ename,deptno from emp;

  cursor c_dept is

        select deptno,dname from dept;

  r_emp c_emp%rowtype;

  r_dept      c_dept%rowtype;

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(‘—————–‘);

        open c_emp;

        loop

              fetch c_emp into r_emp;

              exit when c_emp%notfound;

              if r_emp.deptno = r_dept.deptno then

                    dbms_output.put_line(r_emp.ename);

              end if;

        end loop;

        close c_emp;

  end loop;

  close c_dept;

end;

 

Before discussing such a lengthy program, let us see the output of above program:

—————–
ACCOUNTING
—————–
KING
CLARK
MILLER
—————–
RESEARCH
—————–
JONES
FORD

And so on. It displays all employees separated and categorized under department names. The next section explains the above program in a great deal of detail.

{mospagebreak title=How the program works}

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. 

{mospagebreak title=Further approaches to the above program}

The above program can be solved using just the cursor FOR loop as follows (which is best shown with simplicity):

declare

  cursor c_emp is

        select ename,deptno from emp;

  cursor c_dept is

        select deptno,dname from dept;

begin

  for r_dept in c_dept

  loop

        dbms_output.put_line(r_dept.dname);

        dbms_output.put_line(‘—————–‘);

        for r_emp in c_emp

        loop

              if r_emp.deptno = r_dept.deptno then

                    dbms_output.put_line(r_emp.ename);

              end if;

        end loop;

  end loop;

end;

I hope will agree with me that the above approach is very simple when compared with the program demonstrated in the first section. 

Cursors with parameters – a simple example

In all of the above programs, the ‘if’ statement looks bit awkward. If no employee exists in a particular department, still the inner loop iterates through all employees, which is totally unnecessary. That means I need to retrieve only the employees I want (instead of all employees). This is where the concept of “cursors with parameters” comes in.  First of we shall see a very simple example of using “cursors with parameters”:

declare

  cursor c_emp(v_deptno   emp.deptno%type) is

        select ename from emp where deptno=v_deptno;

  v_ename     emp.ename%type;

begin

  open c_emp(10);

  loop

        fetch c_emp into v_ename;

        exit when c_emp%notfound;

        dbms_output.put_line(v_ename);

  end loop;

  close c_emp;

end;

Within the above program, the cursor ‘c_emp’ is declared with a parameter ‘v_deptno’. That means we need to provide a value to that variable (v_deptno) at the time of opening the cursor, which gets directly substituted within the WHERE condition of the SELECT statement of cursor. The cursor FOR loop version of the same program as above will be as follows:

declare

  cursor c_emp(v_deptno   emp.deptno%type) is

        select ename from emp where deptno=v_deptno;

begin

  for v_emp in c_emp(10)

  loop

        dbms_output.put_line(v_emp.ename);

  end loop;

end;

{mospagebreak title=Cursors with parameters – a complicated example}

The following is a new way of writing the program discussed in the previous section “Working with more than one cursor.”

declare

  cursor c_dept is

        select deptno,dname from dept;

  cursor c_emp(v_deptno emp.deptno%type) is

        select ename,deptno from emp

              where deptno = v_deptno;

 

begin

  for r_dept in c_dept

  loop

        dbms_output.put_line(r_dept.dname);

        dbms_output.put_line(‘—————–‘);

        for r_emp in c_emp(r_dept.deptno)

        loop

              dbms_output.put_line(r_emp.ename);

        end loop;

  end loop;

end;

 

Here we have a new declaration of cursor ‘c_emp’ as follows:

cursor c_emp(v_deptno emp.deptno%type) is

        select ename,deptno from emp

              where deptno = v_deptno;

The above statement states that the SELECT statement needs to be executed based on the value sent through ‘v_deptno’ which is of type ‘emp.deptno%type’. For proceeding to another statement, we have:

for r_emp in c_emp(r_dept.deptno)

This statement opens the cursor ‘c_emp’ based on the value sent (in this case the current value of ‘r_dept.deptno’). If you need to know the traditional way of working with the same concept (without using cursor FOR loop), the following would be the answer:

declare

  cursor c_emp(v_deptno dept.deptno%type) is

        select ename,deptno from emp

              where deptno=v_deptno;

  cursor c_dept is

        select deptno,dname from dept;

  r_emp c_emp%rowtype;

  r_dept      c_dept%rowtype;

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(‘—————–‘);

        open c_emp(r_dept.deptno);

        loop

              fetch c_emp into r_emp;

              exit when c_emp%notfound;

              dbms_output.put_line(r_emp.ename);

        end loop;

        close c_emp;

  end loop;

  close c_dept;

end;

And I hope you can understand the above program without any explanation.

{mospagebreak title=Can we use JOINS in cursors?}

Why not? We can use almost any type of query (including joins, sub-queries etc) with cursor declarations. If you have any expressions as columns, then you are required to provide an alias for each of them.  The following program is a demonstration:

declare

  cursor c_emp is

        select ename,sal*12 as annsal,dname

        from emp,dept

        where emp.deptno = dept.deptno;

  r_emp c_emp%rowtype;

begin

  open c_emp;

  loop

        fetch c_emp into r_emp;

        exit when c_emp%notfound;

        dbms_output.put_line(r_emp.ename || ‘ – ‘ || r_emp.annsal || ‘ – ‘ || r_emp.dname);

  end loop;

  close c_emp;

end;

In the above program, “sal*12” is an expression provided with an alias “annsal” (annual salary). The beauty of ‘r_emp’ is that it automatically contains ‘annsal’ as a member without having any declaration. And I provide the cursor FOR loop version for the above program as follows:

declare

  cursor c_emp is

        select ename,sal*12 as annsal,dname

        from emp,dept

        where emp.deptno = dept.deptno;

begin

  for r_emp in c_emp

  loop

        dbms_output.put_line(r_emp.ename || ‘ – ‘ || r_emp.annsal || ‘ – ‘ || r_emp.dname);

  end loop;

end;

[gp-comments width="770" linklove="off" ]

chat sex hikayeleri Ensest hikaye