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

Cursors with parameters – a complicated example - Oracle

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

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.



 
 
>>> 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: