33. CURSORS

CURSORS

·         Oracle will make use of internal memory areas (implicit cursors) for sql statements to process the records

·         This memory areas will be defined in a area called SGA (system global area)

·         Oracle allows to provide our own memory areas (explicit cursors) to get control over each of the record

Definition:-  It is a pointer pointing towards the arranged data in a context area. In explicit cursors user has to declare, open, fetch and close the cursors. Whereas in the case of impolicit cursors system has to look after all this functionalities.

Cursor Functionalities:

Step 1:  Declaring the cursor

Syntax: cursor cursor_name is select …

Step 2:  Opening a cursor

Syntax: open cursor_name

Step 3:  Fetching records into variable from cursor

Syntax: fetch cursor_name into variable;

Step 4:  Closing cursor

Syntax: close cursor_name

Cursor Attributes:

1.   Cursor_name%isopen;

2.   Cursor_name%found;

3.   Cursor_name%notfound;

4.   Cursor_name%rowcount;

5.   Cursor_name%bulk_rowcount;

6.   Cursor_name%bulk_exception; (save exception 11g)

Cursor_name%isopen: returns true if cursor is opened else false

Cursor_name%found: returns true if records are found else false

Cursor_name%notfound: reverse to the found

Cursor_name%rowcount: returns number of records fetched to that state

Example for explicit cursor:

Declare

   Cursor c is select ename form emp;

   Vname c%rowtype;

Begin

   Open c;

Loop

   Fetch c into vname;

   Exit when c%not found;

   Dbms_output.put_line(vname.ename);

End loop;

End;
-------------------------------------------------------------------------------

Ex:  Declare

  Cursor c is select ename for emp;

  Vname emp.ename%type;

Begin

  Open c;

Loop

  Fetch c into vname;

  Exit when c%notfound;

  Dbms_output.put_line(vname);

End loop;

  Close c;

End;

Cursor event		%isopen	%found	%notfound	%rowcount
Open cursor	Before	F	E	E	E
	AFTER	T	N	N	O
1ST FETCH	BEFORE	T	N	N	O
	AFTER	T	T	F	I
2ND FETCH (FETCHES)	BEFORE	T	T	F	I
	AFTER	T	T	F	DD
LAST FETCH	BEFORE	T	T	F	DD
	AFTER	T	F	T	DD
CLOSE CURSOR	BEFORE	T	F	T	DD
	AFTER	F	E	E	E

E -> Exceptions     DD -> data dependence

In for loops no need to open fetch and close the cursors. It happens through for loop
--------------------------------------------------------------------------------------

For loop cursors:

Declare

   cursor c is select ename for emp;

begin

   for I in c loop

   dbms_output.put_line(i.ename);

end loop;

end;

------------------------------------------------------------------------------------------------------------------------

Parameterised Cursors:  In cursors we use “In” parameters

Ex:

Declare

Cursor c(x emp.deptno%type) is select ename from emp where deptno = x;

Vname emp.ename%type;

Vno number(5):=&n;

Begin

Open c(vno);

Loop

Fetch c into vname;

Exit when c%notfound;

Dbms_output.put_line(vname);

End loop;

Close c;

End;

---------------------------------------------------------------------------------------------------------------

Using for loop:

Ex:

Declare

Cursor c(x emp.deptno%type) is

select ename from emp where deptno = x;

Begin

For I in c(vno) loop

Dbms_output.put_line(i.ename);

End loop;

End;

-------------------------------------------------------------------------------------------------------------

Static cursors:  we can open the cursor n no.of times

Ex:-

Declare

cursor c is select ename from emp;

Vname emp.ename%type;

Begin

Open c;

Loop

Fetch c into vname;

Exit when c%notfound;

Dbms_output.put_line(vname);

End loop;

Close c;

Open c;

End;

-------------------------------------------------------------------------------------------------------------------------

a) Implicit Cursors:

Declare

Vno emp1.deptno%type:=&n;

Begin

Delete from emp1 where deptno = vno;

If sql%rowcount >3 then

Dbms_output.put_line(sql%rowcount||’employees not possible to delete’);

Rollback;

Else

Dbms_output.put_line(sql%rowcount||’employees possible to delete’);

Commit;

End if;

End;

We use implicit cursors to find out the status of DML operations.

-------------------------------------------------------------------------------------------------------------------------

REF CURSOR

•   Ref cursor is a datatype
•    We use ref cursors to handle multiple select statements
•    We can pass ref cursor variable to a parameter value

Syntax: Type typename is ref cursor;

Var typename;

Ex:-

Declare

Type rec is ref cursor;

Vrec rec;

Vemp emp.ename%type;

Vdept dept.loc%type;

Begin

Open vrec for select ename from emp;

Loop

Fetch vrec into vemp;

Exit when vrec%notfound;

Dbms_output.put_line(vemp);

End loop;

Close vrec;

Dbms_output.put_line(‘++++++++’);

Open vrec for select dname from dept;

Loop

Fetch vrec into vdept;

Exit when vrec%notfound;

Dbms_output.put_line(vdept);

End loop;

Close vrec;

End;

---------------------------------------------------------------------------------------------------------------------

For update cursor:

•           We use for update clause to provide row level locking in a table
•    Where current of cursorname
•   We use current of cursorname to refer the records of a table which are processed by a cursor
•    We mainly use this to simplify the coding path, without for update it is not possible to provide/refer where current of clause

Ex:

Declare

Cursor c is select * from emp1 where deptno = &n for update no wait;

Vrow emp1%rowtype;

Begin

Open c;

Loop

Fetch c into vrow;

Exit when c%notfound;

Update emp1 set sal = 6000 where current of c;

End loop;

End;

-----------------------------------------------------------------------------------------------------------------------

Ref cursors are two types

1. Strong ref cursors
2. Weak ref cursors

•     If you restrict the ref cursor datatype with return datatype such refcursors are called “strong ref cursors”
• 
  
•     In strong ref cursors we have to process the only the records which are compartable to the return datatype.
• 
  
•    In weak refcursors there is no restrictions which means we can process records of variable data types (different data types)

Ex:-

Declare

Type rec is ref cursor return emp%rowtype;

Vrec rec;

Vemp emp%rowtype;

Vdept dept%rowtype;

Begin

Open vrec for select * from emp;

Loop

Fetch vrec into vemp;

Exit when vrec%notfound;

End loop;

Close vrec;

Dbms_output.put_line(‘++++++’)

Open vrec for select * from dept;

End;