Cursors
A Cursor in PL/SQL is used to return rows of data from an SQL query (a ResultSet). You can iterate over a Cursor in a PL/SQL using a LOOP or return a Cursor to a calling entity such as a Java application. There are two types of Cursors in PL/SQL. Implicit and Explicit, the differences are as follows:
Implicit Cursors
Implicit Cursors are indirectly created by Oracle when an INSERT, UPDATE, DELETE, or SELECT statement is executed. However, in the case of a SELECT statement implicit Cursors are only created when one row is returned, if more than one row is returned we must Explicitly declare a Cursor.
Explicit Cursors
Explicit Cursors are explicitly declared in PL/SQL code and they return multiple rows from a SELECT statement. Once these rows are returned, the can be itereated over one at time using a LOOP construct inside of a Stored Procedure, Package, Function, etc. Or they can be returned to a calling application or procedure.
Here is an example of an Explicit Cursor being who’s resulset is be iterated over with a loop construct:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
-- Declaring the a Cursor
CURSOR MY_CORSOR is
SELECT DISTINCT
userid, username, first_name, last_name
FROM T_USER WHERE
username like '%o%'
-- Looping through and executing when no records
-- are found
FOR MY_CURSOR_rec in MY_CORSOR
LOOP
EXIT WHEN MY_CORSOR%NOTFOUND;
-- Execute some logic here for example
IF MY_CURSOR_rec.username = 'mo' THEN
-- Do Something
END IF;
END LOOP; |
If the Cursor above were executed. A ResultSet similar to what is displayed below (Figure 1) could be returned, and with that ResultSet come a number of attributes that can be used to interact with the Cursor.
Figure 1: Oracle PL/SQL Cursor Attributes
Cursor State and Status
Oracle provides attributes for Cursors in order to evaluate their state. These attributes can be used in many situations, for example, if you look at Line 12 of the code above you will see we are using the %NOTFOUND attirbute. This is telling our loop to exit when no more rows are found in the Cursor.
Below are all of the attributes provided by ORACLE for identifying Cursor state:
%ISOPEN
- Returns TRUE if the cursor is open, FALSE if the cursor is closed.
- Look at line 09 (below) for an example of how %ISOPEN can be used.
%FOUND
- Returns INVALID_CURSOR if cursor is declared, but not open; or if cursor has been closed.
- Returns NULL if cursor is open, but fetch has not been executed.
- Returns TRUE if a successful fetch has been executed.
- Returns FALSE if no row was returned.
- Look at line 15 (below) for an example of how %FOUND can be used.
%NOTFOUND
- Returns INVALID_CURSOR if cursor is declared, but not open; or if cursor has been closed.
- Return NULL if cursor is open, but fetch has not been executed.
- Returns FALSE if a successful fetch has been executed.
- Returns TRUE if no row was returned.
- This was covered in line 12 of the example above.
%ROWCOUNT
- Returns INVALID_CURSOR if cursor is declared, but not open; or if cursor has been closed.
- Returns the number of rows fetched.
- The ROWCOUNT attribute doesn’t give the real row count until you have iterated through the entire cursor. In other words, you shouldn’t rely on this attribute to tell you how many rows are in a cursor after it is opened.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
DECLARE
CURSOR USERS_CR IS
SELECT userid,username, useraddress
FROM user
WHERE username like '%Mo%';
USERS_CR_row USERS_CR%ROWTYPE;
BEGIN
IF (NOT USERS_CR%ISOPEN) THEN
OPEN USERS_CR;
END IF;
FETCH USERS_CR INTO USERS_CR_row;
WHILE (USERS_CR_row%FOUND)LOOP
--Execute some logic here....
FETCH USERS_CR INTO USERS_CR_row;
END LOOP;
IF (USERS_CR%ISOPEN)THEN
CLOSE USERS_CR;
END IF;
END; |
Additional Reading:
ORACLE PL/SQL Documentation