Cursors in PL/SQL

Oracle supports two types of cursors that can be used in PL/SQL: the implicit cursor and the explicit cursor. The implicit cursor is created and managed by PL/SQL when the executable part of the PL/SQL block processes a SQL statement while the explicit cursor is created and managed by the programmer.

Before I demonstate the use of the cursor, I wanted a table that contained parts of the donor table. I created the donor_part table to hold the name, yrgoal and state. Actually I really wanted the table empty, so I deleted the data as shown below.

SQL CODE:

SQL> CREATE TABLE donor_part
  2  (name, yrgoal, state)
  3  AS SELECT name, yrgoal, state FROM donor;

Table created.

SQL> SELECT * FROM donor_part;

NAME               YRGOAL ST
--------------- --------- --
Stephen Daniels       500 MA
Jennifer Ames         400 RI
Carl Hersey               RI
Susan Ash             100 MA
Nancy Taylor           50 MA
Robert Brooks          50 MA

6 rows selected.

SQL> delete from donor_part;

6 rows deleted.

SQL> select * from donor_part;

no rows selected

When using explicit cursor in PL/SQL there are four things that must be accomplished by the programmer.

First, the cursor must be declared.
Second, the cursor needs to be opened.
Third, the fetch is used to fetch the results of the query into the variables declared in PL/SQL.
Fourth, the cursor needs to be closed.

In this PL/SQL block I declared three variables v_name, v_yrgoal and v_state to correspond to the three fields in the new table that I created above. Then I declared a cursor with a select statement which is actually the query to be processed by the cursor.

In the begin, I open the declared cursor and then fetch the first record into the variables that I declared. This will put name, yrgoal, and state from the first record into v_name, v_yrgoal and v_state. Then I put in a WHILE LOOP that will be executed as long as the donor_cursor finds a record. Inside the loop I insert the data into the record into the new table and then fetch the next record. Notice the construction which puts the initializing fetch before the loop. Inside the loop the processing (putting the record into the new file) is done and then the next fetch is executed. The WHILE LOOP is done as long as the fetch is successful and a record is fetched. This is tested with the donor_cursor%FOUND clause. When the loop is complete, the cursor is closed. Looking at the results you can see that there are now six records in the new table that correspond to the six records in the donor table.

SQL CODE:

SQL> edit cursor1

PL/SQL CODE:

DECLARE
  v_name     donor.name%TYPE;
  v_yrgoal   donor.yrgoal%TYPE;
  v_state    donor.state%TYPE;
  CURSOR donor_cursor IS
    SELECT name, yrgoal, state
      FROM donor;
BEGIN
OPEN donor_cursor;
FETCH donor_cursor INTO v_name, v_yrgoal, v_state;
WHILE donor_cursor%FOUND LOOP
  INSERT INTO donor_part
    VALUES(v_name, v_yrgoal, v_state);
  FETCH donor_cursor INTO v_name, v_yrgoal, v_state;
END LOOP;
CLOSE donor_cursor;
END;
/

SQL CODE:

SQL> @ cursor1
Input truncated to 1 characters

PL/SQL procedure successfully completed.

SQL> select * from donor_part;

NAME               YRGOAL ST
--------------- --------- --
Stephen Daniels       500 MA
Jennifer Ames         400 RI
Carl Hersey               RI
Susan Ash             100 MA
Nancy Taylor           50 MA
Robert Brooks          50 MA

6 rows selected.

I can now modify this program so that the only rows that are written to the new table are rows with a yrgoal > 50 The only change I need to make is in the processing within the loop. I added an IF statement so the insert is only done if v_yrgoal is > 50.

SQL CODE:

SQL> edit cursor2

PL/SQL CODE:

DECLARE
  v_name     donor.name%TYPE;
  v_yrgoal   donor.yrgoal%TYPE;
  v_state    donor.state%TYPE;
  CURSOR donor_cursor IS
    SELECT name, yrgoal, state
      FROM donor;
BEGIN
OPEN donor_cursor;
FETCH donor_cursor INTO v_name, v_yrgoal, v_state;
WHILE donor_cursor%FOUND LOOP
  IF v_yrgoal > 50 THEN
    INSERT INTO donor_part
      VALUES(v_name, v_yrgoal, v_state);
  END IF;
  FETCH donor_cursor INTO v_name, v_yrgoal, v_state;
END LOOP;
CLOSE donor_cursor;
END;
/
SQL CODE:
SQL> @ cursor2
Input truncated to 1 characters

PL/SQL procedure successfully completed.

SQL> SELECT * FROM donor_part;

NAME               YRGOAL ST
--------------- --------- --
Stephen Daniels       500 MA
Jennifer Ames         400 RI
Susan Ash             100 MA

I can also use the SELECT in the cursor to accomplish the goal of only showing selected 
records.  In this example I added WHERE yrgoal > 50 to the SELECT.  Notice then when I 
test these PL/SQL blocks, I need to clean out donor_part prior to the test or I will see 
the results of the previous test in addition to the current test.


SQL CODE: 
SQL> delete from donor_part;

6 rows deleted.

SQL> edit cursor2a

PL/SQL CODE:
DECLARE
  v_name     donor.name%TYPE;
  v_yrgoal   donor.yrgoal%TYPE;
  v_state    donor.state%TYPE;
  CURSOR donor_cursor IS
    SELECT name, yrgoal, state
      FROM donor
      WHERE yrgoal> 50;
BEGIN
OPEN donor_cursor;
FETCH donor_cursor INTO v_name, v_yrgoal, v_state;
WHILE donor_cursor%FOUND LOOP
  INSERT INTO donor_part
    VALUES(v_name, v_yrgoal, v_state);
  FETCH donor_cursor INTO v_name, v_yrgoal, v_state;
END LOOP;
CLOSE donor_cursor;
END;
/

SQL CODE:
SQL> @ cursor2a
Input truncated to 1 characters

PL/SQL procedure successfully completed.

SQL> SELECT * FROM donor_part;

NAME               YRGOAL ST
--------------- --------- --
Stephen Daniels       500 MA
Jennifer Ames         400 RI
Susan Ash             100 MA

We will be looking at four SQL cursor attributes that can be very useful in programming 
because they allow for verification of the results of DML statements.  The SQL%ROWCOUNT 
tells how many rows were effected by the last SQL statement that was executed and can be 
used to only process a certain number of rows.  The SQL%FOUND which was illustrated above, 
gives a boolean TRUE if the last SQL statement effected rows and the SQL%NOTFOUND gives a 
boolean true if the last SQL statement did not affect any rows.  The %FOUND and %NOTFOUND 
are very useful for testing when a loop is complete.  The SQL%ISOPEN will only fetch rows 
if the cursor is open.  For example, you could do the following test in your code: IF NOT 
donor_cursor%ISOPEN THEN.  Inside the loop you could open the cursor and do any other 
processing needed.


In the examples above I have been using the WHILE loop (my primary choice).  You can also 
use the other loop choices discussed in the loop section.  In this example I will do the 
program above using the EXIT WHEN and test using the %NOTFOUND.  The syntax is LOOP which 
includes the processing.  The last command inside the loop will say EXIT WHEN the name of 
the cursor%NOTFOUND meaning exit the loop when the fetch does not yield a row to be 
processed.


SQL CODE:
SQL> delete from donor_part;

4 rows deleted.

SQL> edit cursor2b

PL/SQL CODE:
DECLARE
  v_name     donor.name%TYPE;
  v_yrgoal   donor.yrgoal%TYPE;
  v_state    donor.state%TYPE;
  CURSOR donor_cursor IS
    SELECT name, yrgoal, state
      FROM donor
      WHERE yrgoal> 50;
BEGIN
OPEN donor_cursor;
FETCH donor_cursor INTO v_name, v_yrgoal, v_state;
LOOP
  INSERT INTO donor_part
    VALUES(v_name, v_yrgoal, v_state);
  FETCH donor_cursor INTO v_name, v_yrgoal, v_state;
  EXIT WHEN donor_cursor%NOTFOUND;
END LOOP;
CLOSE donor_cursor;
END;
/

SQL CODE:
SQL> @ cursor2b
Input truncated to 1 characters

PL/SQL procedure successfully completed.

SQL> SELECT * FROM donor_part;

NAME               YRGOAL ST
--------------- --------- --
Stephen Daniels       500 MA
Jennifer Ames         400 RI
Susan Ash             100 MA

In the next example, I only want to process the first 4 rows in the donor table.  I can 
handle this using ROWCOUNT.  As always I should always cover all my bases by testing for 
FOUND as well in case there are less than 4 rows in the table.  Note that these two 
conditions are in an AND relationship because I only want to continue processing if both of 
them are true.


SQL CODE:
SQL> edit cursor3

PL/SQL CODE:
DECLARE
  v_name     donor.name%TYPE;
  v_yrgoal   donor.yrgoal%TYPE;
  v_state    donor.state%TYPE;
  CURSOR donor_cursor IS
    SELECT name, yrgoal, state
      FROM donor;
BEGIN
OPEN donor_cursor;
FETCH donor_cursor INTO v_name, v_yrgoal, v_state;
WHILE donor_cursor%ROWCOUNT < 5 AND donor_cursor%FOUND LOOP
  INSERT INTO donor_part
    VALUES(v_name, v_yrgoal, v_state);
  FETCH donor_cursor INTO v_name, v_yrgoal, v_state;
END LOOP;
CLOSE donor_cursor;
END;
/

SQL CODE:

SQL> @ cursor3

PL/SQL procedure successfully completed.

SQL> SELECT * FROM donor_part;

NAME               YRGOAL ST
--------------- --------- --
Stephen Daniels       500 MA
Jennifer Ames         400 RI
Carl Hersey               RI
Susan Ash             100 MA

I am now altering a table to contain a column called row_rowid which will be of the type 
ROWID.  ROWID is the databse pseudocolumn type which holds a unique number for each row.  
As a note, ROWID can be converted to character using ROWIDTOCHAR.


SQL CODE:
SQL> ALTER TABLE donor_part_rowid
  2  ADD (row_rowid ROWID);

Table altered.
SQL> desc donor_part_rowid;
 Name                            Null?    Type
 ------------------------------- -------- ----
 NAME                                     VARCHAR2(15)
 YRGOAL                                   NUMBER(7,2)
 STATE                                    VARCHAR2(2)
 ROW_ROWID                                ROWID

SQL> edit cursor4

In the PL/SQL block code shown below, I first gave a record/row name donor_rec to the row 
that I was creating in the cursor select.  Then in the loop, I printed out a field from the 
rows just to establish that I had been there and then wrote the information on the row to 
a the new table called donor_part_rowid that I created and showed the alteration of in the 
example above.  Note that when I output the line, I could not show the whole row, I had to 
show a column/field and when I did the insert I had to refer to a column/field, I could not 
use the donor_rec as the value.  Also, pay special attention to the use of ROWID.  It came 
it with the cursor select by using the reserved word rowid.  Remember, ROWID is not on the 
donor table.  Then when I wanted to insert the information in the row_rowid field on the 
donor_part_rowid table, I simply referred to it as part of the record that was created in 
the cursor setup.  The rowid is stored in the table in Oracles own internal format as you 
can see in the output below. 

 
PL/SQL CODE:
SET SERVEROUTPUT ON
DECLARE
  CURSOR donor_cursor IS
    SELECT name, yrgoal, state, rowid
      FROM donor;
  donor_rec donor_cursor%ROWTYPE;
BEGIN
OPEN donor_cursor;
FETCH donor_cursor INTO donor_rec;
WHILE donor_cursor%FOUND LOOP
  dbms_output.put_line('Donor record: '||donor_rec.yrgoal);
  INSERT INTO donor_part_rowid
    VALUES (donor_rec.name, donor_rec.yrgoal, donor_rec.state, donor_rec.rowid);
  FETCH donor_cursor INTO donor_rec;
END LOOP;
CLOSE donor_cursor;
END;
/
SET SERVEROUTPUT OFF

SQL CODE:
SQL> @ cursor4
Donor record: 500
Donor record: 400
Donor record:
Donor record: 100
Donor record: 50
Donor record: 50

PL/SQL procedure successfully completed.

Input truncated to 20 characters
SQL> SELECT * FROM donor_part_rowid;

NAME               YRGOAL ST ROW_ROWID
--------------- --------- -- ------------------
Stephen Daniels       500 MA 000001D4.0000.0002
Jennifer Ames         400 RI 000001D4.0001.0002
Carl Hersey               RI 000001D4.0002.0002
Susan Ash             100 MA 000001D4.0003.0002
Nancy Taylor           50 MA 000001D4.0004.0002
Robert Brooks          50 MA 000001D4.0005.0002

6 rows selected.