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

How the program works - 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

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. 



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