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

Further approaches to the above program - 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 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;



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