Cursors

Implicit cursors
Whenever a SQL statement is issued the Database server opens an area of memory in which the command is parsed and executed. This area is called a cursor. Microsoft tends to refer to cursors as datasets throughout much of their databse product documentation..

When the executable part of a PL/SQL block issues a SQL command, PL/SQL creates an implicit cursor which has the identifier SQL. PL/SQL manages this cursor for you.

PL/SQL provides some attributes which allow you to evaluate what happened when the implicit cursor was last used. You can use these attributes in PL/SQL statements like functions but you cannot use then within SQL statements.
The SQL cursor attributes are :

• %ROWCOUNT:The number of rows processed by a SQL statement.
  
• %FOUND: TRUE if at least one row was processed.
  
• %NOTFOUND: TRUE if no rows were processed.
  
• %ISOPEN:TRUE if cursor is open or FALSE if cursor has not been opened or has been closed. Only used with explicit cursors

Example:
DECLARE
ROW_DEL_NO NUMBER;
BEGIN
DELETE * FROM JD11.SECTION;
ROW_DEL_NO := SQL%ROWCOUNT;
END;

Explicit cursors
SELECT statements that occur within PL/SQL blocks are known as embedded, they must return one row and may only return one row. To get around this you can define a SELECT statement as a cursor (an area of memory), run the query and then manipulate the returned rows within the cursor. Cursors are controlled via four command statements.
They are :

• DECLARE:Defines the name and structure of the cursor together with the SELECT statement that will populate the cursor with data. The query is validated but not executed.
• OPEN: Executes the query that populates the cursor with rows.
  
• FETCH:Loads the row addressed by the cursor pointer into variables and moves the cursor pointer on to the next row ready for the next fetch.
  
• CLOSE:Releases the data within the cursor and closes it. The cursor can be reopened to refresh its data.
  

Cursors are defined within a DECLARE section of a PL/SQL block. For example:
DECLARE
CURSOR MYCUR IS SELECT ISBN, COST FROM JD11.BOOK;

The cursor is defined by the CURSOR keyword followed by the cursor identifier (MYCUR in this case) and then the SELECT statement used to populate it, the SELECT statement can be any legal query.

Cursors are opened with the OPEN statement, this populates the cursor with data.
DECLARE
CURSOR MYCUR IS SELECT ISBN, COST FROM JD11.BOOK;
BEGIN
OPEN MYCUR;
END;

To access the rows of data within the cursor we use the FETCH statement.

DECLARE
CURSOR MYCUR IS SELECT ISBN, COST FROM JD11.BOOK;
THISISBN NUMBER(10);
THISCOST NUMBER(10,2);
BEGIN
OPEN MYCUR;
FETCH MYCUR INTO THISISBN, THISCOST;
END;

The FETCH statement reads the column values for the current cursor row and puts them into the specified variables. The cursor pointer is updated to point at the next row. If the cursor has no more rows the variables will be set to null on the first FETCH attempt, subsequent FETCH attempts will raise an exception.

The CLOSE statement releases the cursor and any rows within it, you can open the cursor again to refresh the data in it.

DECLARE
CURSOR MYCUR IS SELECT ISBN, COST FROM JD11.BOOK;
THISISBN NUMBER(10);
THISCOST NUMBER(10,2);
BEGIN
OPEN MYCUR;
FETCH MYCUR INTO THISISBN, THISCOST;
CLOSE MYCUR;
END;

To process all the rows within a cursor we simply need to place the FETCH statement in a loop and check the cursor NOTFOUND attribute to see if we successfully fetched a row or not.

DECLARE
CURSOR MYCUR IS SELECT ISBN, COST FROM JD11.BOOK;
THISISBN NUMBER(10);
THISCOST NUMBER(10,2);
BEGIN
OPEN MYCUR;
LOOP
FETCH MYCUR INTO THISISBN, THISCOST;
EXIT WHEN MYCUR%NOTFOUND;
END LOOP;
CLOSE MYCUR;
END;

PL/SQL records may be based on a cursor. This is very convenient for processing rows from the active data set. An example follows :

DECLARE
CURSOR MYCUR IS SELECT ISBN, COST FROM JD11.BOOK;
PARTBOOK MYCUR%ROWTYPE;
BEGIN
OPEN MYCUR;
LOOP
FETCH MYCUR INTO PARTBOOK;
EXIT WHEN MYCUR%NOTFOUND;
IF PARTBOOK.ISBN = 21 THEN
PARTBOOK.COST = 19.10;
END IF;
END LOOP;
CLOSE MYCUR;
END;

You can use the WHERE CURRENT OF clause to execute DML commands against the current row of a cursor, this makes it easier to update rows. An example is below :

DECLARE
CURSOR MYCUR IS SELECT ISBN, COST FROM JD11.BOOK;
PARTBOOK MYCUR%ROWTYPE;
BEGIN
OPEN MYCUR;
LOOP
FETCH MYCUR INTO PARTBOOK;
EXIT WHEN MYCUR%NOTFOUND;
IF PARTBOOK.ISBN = 21 THEN
DELETE FROM JD11.BOOK WHERE CURRENT OF MYCUR;
END IF;
END LOOP;
CLOSE MYCUR;
END;

Note that I didn’t need to explicitly specify the row that I want deleted, PL/SQL supplies the required row identifier from the current record in the cursor ensuring that only the correct row is deleted.

It’s possible to vary the returned result set by using parameters, parameters allow you to specify the query selection criteria when you open the cursor.

DECLARE
CURSOR MYCUR (PARAM1 NUMBER) IS SELECT ISBN, COST FROM JD11.BOOK WHERE ISBN = PARAM1;
PARTBOOK MYCUR%ROWTYPE;
BEGIN
OPEN MYCUR(21);
FETCH MYCUR INTO PARTBOOK;
CLOSE MYCUR;
OPEN MYCUR(101);
FETCH MYCUR INTO PARTBOOK;
CLOSE MYCUR;
END;