CURSORS

CURSORS

o    Cursor is a buffer area which is used to process multiple records and also record by record by process.

o    There are two type

.                             Implicit Cursors

.                             Explicit Cursors

1. Implicit Cursors

§  Sql statements returns a single record is called implicit cursors

§  Implicit cursor operations done by the system.

§  Open by the system.

§  Fetch the records by the system

§  Close by the system.

Example:

1. Declare

2. X  emp%rowtype;

3. Begin

4. Select * into X from emp where empno=7369;

5. Dbms_output.put_line(x.empno||’,’||x.ename);

6. End;

     2. Explicit Cursors

• Sql statements return a multiple records is called explicit cursors
• Explicit cursor operations done by the user.
•   Declare by the user
•  Open by the user
•  Fetch the records by the user
• Close by the user

    Example:

  Declare

Cursor c1 is select ename,sal from emp;

V_Name varchar2(10);

V_Sal number(10);

Begin

Open C1;

Fetch c1 into v_name,v_sal;

Dbms_output.put_line(v_name||’,’||v_sal);

Close C1;

End;

              Ref Cursor

  o    Ref Cursors are user define types which is used to process multiple records and also this is record by record process

o    Generally through the static cursors we are using only one select statement at a time for single active set area where as in ref cursors we are executing no of select statements dynamically for single active set area.

o    Thats why these type of cursors are also called as dynamic cursors.

o    By using ref cursors we return large amount of data from oracle database into client applications.

o    There are 2 Types

           §  Strong Ref Cursor

           §  Weak Ref Cursor

§  Strong Ref Cursor

-  It is one of the ref cursor which is having return type.

-  Weak Ref Cursor

-  It is one of the ref cursor which does not have a return type.

Note : In ref cursor we are executing select statements using open .... for statement.

Example 1 : 

Declare

Type t1 is ref cursor;

v_t t1;

i emp%rowtype;

begin

open v_t for select * from emp where sal>2000;

loop

fetch v_t into i;

exit when v_t%notfound;

dbms_output.put_line(i.ename||' '||i.sal);

end loop;

close v_t;

end;

Example 2 :

Declare

type t1 is ref cursor;

v_t t1;

i emp%rowtype;

j dept%rowtype;

v_no number(5):=&no;

begin

if v_no=1 then

open v_t for select * from emp;

loop

fetch v_t into i;

exit when v_t%notfound;

dbms_output.put_line(i.ename||' '||i.deptno);

end loop;

close v_t;

elsif v_no=2 then

open v_t for select * from dept;

loop

fetch v_t into j;

exit when v_t%notfound;

dbms_output.put_line(j.deptno||' '||j.dname);

end loop;

close v_t;

end if;

end;

Example 3 :

create or replace package pg1

is

type t1 is ref cursor return emp%rowtype;

type t2 is ref cursor return dept%rowtype;

procedure p1 (p_t1 out t1);

procedure p2 (p_t2 out t2);

end;

create or replace package body pg1 is

procedure p1 (p_t1 out t1)

is

begin

open p_t1 for select * from emp;

end p1;

procedure p2 (p_t2 out t2)

is

begin

open p_t2 for select * from dept;

end p2;

end;

Execution 

variable a refcursor

variable b refcursor

exec pg1.p1(:a);

exec pg1.p2(:b);

print a b;