Cursor

when an SQL statement is processed, Oracle creates a memory area(work area) known as context area, to execute SQL commands and store processing information in that context area. A cursor is a pointer to this context area, PL/SQL allows us to access this area, through a name – Cursor name. Cursors used in Oracle are of two types.

1)Implicit cursor

2)Explicit cursor

1)Implicit cursor

It is automatically generated by Oracle when an SQL statement is executed, It is used to store information about most recently executed SQL command. These are created by default to process the DML statements like INSERT, UPDATE,DELETE etc. are executed. Oracle provides some attributes known as Implicit cursor's attributes to check the status of DML operations. Some of them are:

%FOUND - It returns TRUE if the most recent fetch operation fetched a

record successfully.

%NOTFOUND - It returns 'TRUE' if the most recent fetch operation could not able to fetch any record.

%ROWCOUNT -It returns 'TRUE' if the given cursor is already opened, else it returns 'FALSE'

%ISOPEN - It returns the numerical value. It gives the actual count of records that got affected by the DML commands.

SQL> select * from emp;

 ENO       ENAME      SALARY        

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

       111 raju             12720
       222 rani             41220
       333 vani             72220
       444 joy              88220

Program on Implicit cursor:

set serveroutput on

cl scr

declare

t number(2);

begin

update emp set salary=salary+500 where eno>300;

if(sql%found) then

dbms_output.put_line('FOUND');

else

dbms_output.put_line('NOT FOUND');

end if;

t:=sql%rowcount;

dbms_output.put_line('Number of Rows Updated:'||t);

end;

/

Output:

SQL> @d:\joy.sql

FOUND

Number of Rows Updated:2

PL/SQL procedure successfully completed.

2)Explicit Cursor:

The Explicit cursors are defined by the programmers to gain more control over the context area. It is used to store result of a multi-row query. The data retrieved is stored in this type of cursor for further process. These cursors should be defined in the declaration section of the PL/SQL block. It is created on a SELECT statement which returns more than one row.

General Syntax of explicit cursor:

CURSOR cursor_name IS select_statement; 

We need to follow the steps given below while working with Explicit Cursors

1.Declare the cursor to initialize in the memory.

2.Open the cursor to allocate memory. 

3.Fetch the cursor to retrieve data.

4.Close the cursor to release allocated memory.

Declare cursor : It defines the cursor with a name and the associated SELECT statement.

Syntax :CURSOR cursor_name IS SELECT statement;

Open the cursor: It is used to allocate memory for the cursor and make it available to fetch the rows returned by the SQL statements into it.

Syntax:OPEN cursor_name;

Fetch the cursor: It is used to access one row at a time. We can fetch rows from a cursor once it is opened.

Syntax:FETCH cursor_name INTO variable_list;

Close the cursor: It is used to release the allocated memory. 

Syntax:Close cursor_name;

 

SQL> select * from emp;

 ENO       ENAME      SALARY        

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

       111 raju             12720
       222 rani             41220
       333 vani             72220
       444 joy              88220

Program on Explicit cursor:

set serveroutput on

cl scr

declare

c_eno emp.eno%type;

c_ename emp.ename%type;

c_salary emp.salary%type;

cursor c_emp is  select eno,ename,salary from emp where eno>300;

begin

open c_emp;

dbms_output.put_line('ENO ENAME SALARY');

loop

fetch c_emp into c_eno,c_ename,c_salary ;

exit when c_emp%notfound;

dbms_output.put_line(c_eno||' '||c_ename||' '||c_salary);

end loop;

close c_emp;

end;

/

Output:

SQL> @d:\joy.sql

ENO ENAME SALARY

333 vani 72720

444 joy 88720

PL/SQL procedure successfully completed.