Home arrow Oracle arrow Database Interaction with PL/SQL: Explict Cursors in Depth

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.

TABLE OF CONTENTS:
  1. Database Interaction with PL/SQL: Explict Cursors in Depth
  2. Working with more than one cursor
  3. How the program works
  4. Further approaches to the above program
  5. Cursors with parameters – a complicated example
  6. Can we use JOINS in cursors?
By: Jagadish Chatarji
Rating: starstarstarstarstar / 25
August 02, 2005

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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;



 
 
>>> More Oracle Articles          >>> More By Jagadish Chatarji
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

ORACLE ARTICLES

- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: