Cursors Type in Oracle

The Oracle engine uses a work area for its internal processing in order to execute an SQL statement. This work area is private to SQL’s operation and is called a Cursor. The data that is stored in the cursor is called ACTIVE DATA SET. The values retrieved from a table are held in cursor opened in memory by the Oracle engine.

Types of cursors:-

1. Implicit cursors
2. Explicit cursors

1.Explicit Cursor

Cursors are used when the SQL statement is expected to return more than one row. For example select * from student. So, cursor is a buffer which will store the results of the recent query. A cursor must be declared and its definition contains query. The cursor must be defined in the DECLARE section of the program. A cursor must be opened before processing and close after processing. Thus a cursor created and used by User himself / herself is known as Explicit Cursor.

• SYNTAX TO DEFINE CURSOR:

CURSOR <CURSOR NAME> IS <SELECT STATEMENT>;

For eg :- CURSOR TMP_CUR IS SELECT * FROM EMP;

• SYNTAX TO OPEN CURSOR

OPEN <CURSOR_NAME>

• SYNTAX TO GET DATA FROM CURSOR

FETCH <CURSOR_NAME> INTO <VAR1>, <VAR2>, <VAR3>

OR

FETCH <CURSOR_NAME> INTO <RECORD_NAME>

• SYNTAX TO CLOSE CURSOR

CLOSE <CURSOR_NAME>

Explicit Cursor management

The steps involved in using an explicit cursor and manipulating data in its active set are:-

1. Declare a cursor mapped to a SQL select statement that retrieves data for processing.
2. Fetch data from the cursor one row at a time into memory variables.
3. Process the data held in the memory variables as required using a loop.
4. Exit from the loop after processing is complete.
5. Close the cursor.

Cursor Attributes

Attribute	Description
%NOTFOUND	TO determine if a row was retrieved. Use after FETCH. NOTFOUND is TRUE if a row was not retrieved. NOTFOUND is FALSE if a row was retrieved.
%FOUND	To determine if a row was fetched. Use after FETCH.
%ROWCOUNT	ROWCOUNT is zero when the cursor is opened.
%ISOPEN	To determine if the cursor is open. ISOPEN is TRUE if a cursor is open. ISOPEN is FALSE if a cursor is not open.

To read all the records from table using cursor

Example:-

set serveroutput on

declare

            cursor s is select empno,ename from emp;

            m emp%rowtype;

begin

            open s;

            loop

                        Fetch s into m.empno,m.ename;

                        exit when s%notfound;

                        dbms_output.put_line(m.empno||' '||m.ename);

                        --dbms_output.put_line(m.ename);

            end loop;

end;

%Notfound :- After opening a cursor, ‘fetch’ statement is used to fetch rows from the active set, one at a time. The attribute %notfound indicates whether fetch statement returns row from the Active set. If the last ‘fetch’ fails to return a row, then %notfound evaluates to true, else, it evaluates to false.

%Found

The % found attribute is the logical opposite of %notfound. It evaluates to true if the ‘fetch’ statement succeeds. It would be evaluated to false if the last ‘fetch’ command fails because no more rows were available.

%Rowcount

The %rowcount attribute is used to return the number of rows fetched. Before the first fetch %rowcont is zero. When the ‘fetch’ statement returns a row, then the number is incremented. It in set to zero when the cursor is opened.

Example of %ROWCOUNT:-

SET SERVEROUTPUT ON

DECLARE

            MREC EMP%ROWTYPE;

            CURSOR STU_CUR IS SELECT * FROM EMP;

BEGIN

            OPEN STU_CUR;

            LOOP

                        FETCH STU_CUR INTO MREC;

                        EXIT WHEN STU_CUR%NOTFOUND;

                        EXIT WHEN STU_CUR%ROWCOUNT>3;

DBMS_OUTPUT.PUT_LINE(MREC.EMPNO ||' '|| MREC.ENAME);

END LOOP;

CLOSE STU_CUR;

END;

%Isopen

If the cursor is already open, then, the attribute %isopen evaluates to true, else it evaluates to false.

Cursor For Loop

A ‘cursor for’ loop can be used instead of explicit cursors to simplify coding. A ‘cursor for’ loop implicitly opens a cursor, repeatedly fetches rows from the active set; and then, closes the cursor when all rows have been processed. The syntax for the ‘cursor for’ loop is as follows

For < record_name> in < cursor _name > loop

            Sequence_of_statements;

End loop ;

This is another technique commonly used to control the Loop..End loop within a PL/SQL block. This is an example of a machine defined loop exit.

Here, the verb ‘for’ automatically creates the memory variable of the %rowtype. The for verb ensures that a row from the cursor is loaded in the declared memory variable and the loop executed once. This goes on until all the rows of the cursor have been loaded into the memory variable. After this, the loop stops.

A ‘cursor for loop’ automatically does the following:-

ð Implicitly declares its loop index as a %rowtype record.

ð Opens a cursor.

ð Fetches a row from the cursor for each loop iteration.

ð Closes the cursor when all the rows have been processed.

Example:-

set serveroutput on

set verify off

declare

            cursor emp_cur is select * from emp;

            mrec emp%rowtype;

begin

            for mrec in emp_cur loop

                        dbms_output.put_line(mrec.empno || mrec.ename || mrec.job);

            end loop;

end;/

Implicit cursor

PL/SQL implicitly declares cursors for all SQL data manipulation statements, including queries that return one row. For queries that return more than one row, we should use explicit cursors to access the rows individually.

The implicit cursor attributes are:

Ø    %notfound

Ø    %found

Ø    %rowcount

Ø    %isopen

%Notfound

% notfound attribute evaluates to true if DML statements do not return any row, else it evaluates to false.

%Found 

The %found attribute is the logical opposite of the %notfound attribute. The %found attribute is evaluated to true if the SQL DML statement affects one or more rows, else it is evaluated to false.

%Rowcount

The %rowcount attribute counts the number of rows returned by an SQL DML statement. The %rowcount will return zero if the DML statement does not affect any row.

%isopen

Oracle closes the SQL cursor automatically after executing its associated SQL statement. As a result, %isopen is always evaluated to false.

Example:-

clear screen

set serveroutput on

declare

begin

            delete from emp where empno=7369;

            if sql%notfound then

                        dbms_output.put_line('no record found');

            else

                        dbms_output.put_line(sql%rowcount);

            end if;

end; /

Parameterized Cursors

Commercial applications require that the Query, which defines the cursors, be generic and the data that is retrieved from the table to allowed should to change according to need. Oracle recognizes this and permits the creations of parameterized cursors for use. The contents of a parameterized cursor will constantly change depending upon the value passed to the parameter.

Since the cursor accepts user defined values into the Parameters, thus changing the result set extracted, it is called as Parameterized cursors. 

Syntax:- (To declare)

Cursor Cursorname (variablename datatype) is <Select Statement >

Syntax:- (To open)

Open cursorname (value / variable / expression)

Example:-

clear screen

set serveroutput on

declare

            temp varchar2(10);

cursor c1(n varchar2) is select ename from emp

where ename=n;

            m varchar2(10);

begin

            temp:='&temp';

            open c1(temp);

            fetch c1 into m;

            dbms_output.put_line('EMPNAME IS'||temp);

            close c1;

end;