Cursors

A cursor is a temporary work area created in the system memory when a SQL statement is executed. A cursor contains information on a select statement and the rows of data accessed by it. This temporary work area is used to store the data retrieved from the database, and manipulate this data. A cursor can hold more than one row, but can process only one row at a time. The set of rows the cursor holds is called the active set.
There are two types of cursors in PL/SQL  :

1. Implicit cursors.
2. Explicit cursors.

Both implicit and explicit cursors have the same functionality, but they differ in the way they are accessed.

Cursor Attributes
Name	Description
%FOUND	Returns TRUE if record was fetched successfully, FALSE otherwise.
%NOTFOUND	Returns TRUE if record was not fetched successfully, FALSE otherwise.
%ROWCOUNT	Returns number of records fetched from cursor at that point in time.
%ISOPEN	Returns TRUE if cursor is open, FALSE otherwise.

Implicit cursors :

These are created by default when DML statements like, INSERT, UPDATE, and DELETE statements are executed. They are also created when a SELECT statement that returns just one row is executed.
When you execute DML statements like DELETE, INSERT, UPDATE and SELECT statements, implicit statements are created to process these statements.
Oracle provides few attributes called as implicit cursor attributes to check the status of DML operations. The cursor attributes available are %FOUND, %NOTFOUND, %ROWCOUNT, and %ISOPEN.
For example, When you execute INSERT, UPDATE, or DELETE statements the cursor attributes tell us whether any rows are affected and how many have been affected. When a SELECT... INTO statement is executed in a PL/SQL Block, implicit cursor attributes can be used to find out whether any row has been returned by the SELECT statement. PL/SQL returns an error when no data is selected.
Consider the PL/SQL Stock that uses implicit cursor attributes as shown below:

Example:

DECLARE   
    Eid  number(3); 
 BEGIN 
    UPDATE emp set eid=&eid where salary=&salary; eid:=sql%rowcount;
    IF SQL%found then 
          dbms_output.put_line('success');
    ELSE 
          dbms_output.put_line ( ' not' ) ;
    END IF; 
          dbms_output.put_line( 'rowcount'||eid);  
 END; 
 /

Output:

Explicit Cursors :

They must be created when you are executing a SELECT statement that returns more than one row. Even though the cursor stores multiple records, only one record can be processed at a time, which is called as current row. When you fetch a row the current row position moves to next row.
An explicit cursor is defined in the declaration section of the PL/SQL Block. It is created on a SELECT Statement which returns more than one row. We can provide a suitable name for the cursor.

Syntax:

CURSOR cursor_name IS select_statement; 

cursor _name -A suitable name for the cursor.
Select_statement - A select query which returns multiple rows.

How to use Explicit Cursor?

There are four steps in using an Explicit Cursor.

1. DECLARE the cursor in the declaration section
2. OPEN the cursor in the Execution Section.
3. FETCH the data from cursor into PL/SQL variables or records in the Execution Section.
4. CLOSE the cursor in the Execution Section before you end the PL/SQL Block.

Declaring a Cursor in the Declaration Section:

Syntax:

CURSOR CURSORNAME IS SELECT.......;

How to access an Explicit Cursor?

These are the three steps in accessing the cursor.
 Open the cursor :

Syntax:

OPEN cursor_name; 

Fetch the records in the cursor one at a time :

Syntax:

FETCH cursor_name INTO record_name; 
  OR
FETCH cursor_name INTO variable_list; 

Close the cursor :

Syntax:

CLOSE  cursor__name; 

When a cursor is opened, the first row becomes the current row. When the data is fetched it is copied to the record or variables and the logical pointer moves to the next row and it becomes the current row. On every fetch statement, the pointer moves to the next row. If you want to fetch after the last row, the program will throw an error. When there is more than one row in a cursor we can use loops along with explicit cursor attributes to fetch all the records.

 Points to remember while fetching a row:

1.  We can fetch the rows in a cursor to a PL/SQL Record or a list of variables created in the PL/SQL Block.
2. If you are fetching a cursor to a PL/SQL Record, the record should have the same structure as the cursor.
3.  If you are fetching a cursor to a list of variables, the variables should be listed in the same order in the fetch statement as the columns are present in the cursor. 

General Form of using an explicit cursor is:

Syntax:

DECLARE 
   variables; 
   records; 
   create a cursor; 
 BEGIN
   OPEN cursor;
   FETCH cursor; 
   process the records; 
   CLOSE cursor; 
 END;    
 

Example:

DECLARE 
   CURSOR er IS select eid,name from emp order by name ; 
   id emp.eid%type; 
   ename emp.name%type; 
 BEGIN 
   OPEN er;  
   Loop 
    FETCH er into id,ename; 
    Exit when er%notfound; 
    dbms_output.put_line (id || ename);  
   end loop; 
     close er; 
 END; 
 /

Output:

Cursor For Loop :

Oracle provides another loop-statements to control loops specifically for cursors.
This statements is a variation of the basic FOR loop , and it is known as cursor for loops.

Syntax:

FOR VARIABLE IN CURSORNAME
LOOP
  <EXECUTE COMMANDS>
END LOOP

Parameterized Cursors :

Oracle allows to pass parameters to cursors that can be used to provide condition with WHERE clause.If parameters are passed to curso, that cursor is called a parameterized cursors.

Syntax:

CURSOR CURSORNAME (VARIABLENAME DATATYPES ) IS SELECT.........

And, parameters canbe passed to cursor while opening it using syntax-

Syntax:

OPEN CURSORNAME (VALUE / VARIABLE / EXPRESSION );

---

• Prev
• Next

---

Questions

---

---