PLSQL CURSORS

CURSORS:-

Cursor is a  method to access more than one record from database. It will allocate an area of memory known as context area. It contains information necessary information including  the no of rows to be processed.

Types of CURSOR’S :-

1. Implicit Cursor:- It is used for all SQL Statements (DML). It is opened and closed by the PL/SQL Engine Implicitly.

2. Explicit Cursor :- It has to be define Explicitly we have to do

1.   Declare the Cursor.

2.   Open the Cursor for a Query.

3.   Fetch the records into the PL/SQL variables.

4.   Close the Cursor.

                               

STEPS REQUIRED FOR CURSOR MANAGEMENT

          1) Declaration Of Cursor.

           Cursor name and select statement for the cursor

           to be provided in DECLARE division. During this

           plsql keeps information about cursor only.

                CURSOR <cursor name> IS <query>;

        2) Opening of Cursor.

                   During this step, plsql performs the following:

                - allocation of memory for cursor.

                - retrieval of records into cursor from db.

                - Positioning record pointer at 1st record.

                OPEN <cursor name>;

        3) Fetching record values from cursor into plsql

           variables.

           Values from current record are retrieved from

           cursor into plsql variables and record pointer

           is moved to next record.

                FETCH <cursor name> INTO <variable list>;

        4) Closing of Cursor.

           During this, plsql removes memory reserved for

           cursor so thelink between db & cursor is gone.

                CLOSE <cursor name>;

       

CURSOR ATTRIBUTES

        These are system variables provided by plsql to

        keep the cursor status. These are :

1. %FOUND :- Keeps boolean value that indicate whether

                fetch is successful or failure. If TRUE

                then success and if FALSE failure.

2. %NOTFOUND :- reverse of the above.

3. %ROWCOUNT :- Keeps the number value that indicate how

                many records have been fetched. In other

                words current record number which always

                start from 1.

4. %ISOPEN :- Keeps boolean value to indicate whether

                cursor is open or close. True for Open &

                False for Not Open.

        These are used with cursor name. The syntax is:

                <cursor name><attribute name>;

                Ex: C1%Found;

LIMITATIONS:-

        1) CURSOR is READ only. That means DML operations

           are not allowed using CURSOR. Only records can

            be retrieved.

        2) CURSOR is FORWARD only. i.e. record pointer in

           cursor can not move back to previous records.