Cursor Basics

A cursor is a SELECT statement that is defined within the declaration section of your
PLSQL code. We'll take a look at three different syntaxes for cursors.

Cursor without parameters (simplest)

The basic syntax for a cursor without parameters is:

CURSOR cursor_name
IS
SELECT_statement;

For example, you could define a cursor called c1 as below.

CURSOR c1
IS
SELECT course_number
from courses_tbl
where course_name = name_in;

The result set of this cursor is all course_numbers whose course_name matches the
variable called name_in.

Below is a function that uses this cursor.

CREATE OR REPLACE Function FindCourse
( name_in IN varchar2 )
RETURN number
IS
cnumber number;

CURSOR c1
IS
SELECT course_number
from courses_tbl
where course_name = name_in;

BEGIN

open c1;
fetch c1 into cnumber;

if c1%notfound then
cnumber := 9999;

end if;

close c1;

RETURN cnumber;

END;

Cursor with parameters

The basic syntax for a cursor with parameters is:

CURSOR cursor_name (parameter_list)
IS
SELECT_statement;

For example, you could define a cursor called c2 as below.

CURSOR c2 (subject_id_in IN varchar2)
IS
SELECT course_number
from courses_tbl
where subject_id = subject_id_in;

The result set of this cursor is all course_numbers whose subject_id matches the
subject_id passed to the cursor via the parameter.

Cursor with return clause

The basic syntax for a cursor with a return clause is:

CURSOR cursor_name
RETURN field%ROWTYPE
IS
SELECT_statement;

For example, you could define a cursor called c3 as below.

CURSOR c3
RETURN courses_tbl%ROWTYPE
IS
SELECT *
from courses_tbl
where subject = 'Mathematics';

The result set of this cursor is all columns from the course_tbl where the subject is
Mathematics.

OPEN Statement

Once you've declared your cursor, the next step is to open the cursor.
The basic syntax to OPEN the cursor is:

OPEN cursor_name;

For example, you could open a cursor called c1 with the following command:

OPEN c1;

Below is a function that demonstrates how to use the OPEN statement:

CREATE OR REPLACE Function FindCourse
( name_in IN varchar2 )
RETURN number
IS
cnumber number;

CURSOR c1
IS
SELECT course_number
from courses_tbl
where course_name = name_in;

BEGIN

open c1;
fetch c1 into cnumber;

if c1%notfound then
cnumber := 9999;

end if;

close c1;

RETURN cnumber;

END;

FETCH Statement

The purpose of using a cursor, in most cases, is to retrieve the rows from your cursor so
that some type of operation can be performed on the data. After declaring and opening
your cursor, the next step is to FETCH the rows from your cursor.

The basic syntax for a FETCH statement is:

FETCH cursor_name INTO <list of variables>;

For example, you could have a cursor defined as:

CURSOR c1
IS
SELECT course_number
from courses_tbl
where course_name = name_in;

The command that would be used to fetch the data from this cursor is:

FETCH c1 into cnumber;

This would fetch the first course_number into the variable called cnumber;

Below is a function that demonstrates how to use the FETCH statement.

CREATE OR REPLACE Function FindCourse
( name_in IN varchar2 )
RETURN number
IS
cnumber number;

CURSOR c1
IS
SELECT course_number
from courses_tbl
where course_name = name_in;

BEGIN

open c1;
fetch c1 into cnumber;

if c1%notfound then
cnumber := 9999;

end if;

close c1;

RETURN cnumber;

END;

CLOSE Statement

The final step of working with cursors is to close the cursor once you have finished using
it.

The basic syntax to CLOSE the cursor is:

CLOSE cursor_name;

For example, you could close a cursor called c1 with the following command:

CLOSE c1;

Below is a function that demonstrates how to use the CLOSE statement:

CREATE OR REPLACE Function FindCourse
( name_in IN varchar2 )
RETURN number
IS
cnumber number;

CURSOR c1
IS
SELECT course_number
from courses_tbl
where course_name = name_in;

BEGIN

open c1;
fetch c1 into cnumber;

if c1%notfound then
cnumber := 9999;

end if;

close c1;

RETURN cnumber;

END;

Below is an example of how you might use the %NOTFOUND attribute.

CREATE OR REPLACE Function FindCourse
( name_in IN varchar2 )
RETURN number
IS
cnumber number;
CURSOR c1
IS
SELECT course_number
from courses_tbl
where course_name = name_in;

BEGIN

open c1;
fetch c1 into cnumber;

if c1%notfound then
cnumber := 9999;
end if;

close c1;

RETURN cnumber;

END;

SELECT FOR UPDATE Statement

The Select For Update statement allows you to lock the records in the cursor result set.
You are not required to make changes to the records in order to use this statement. The
record locks are released when the next commit or rollback statement is issued.

The syntax for the Select For Update is:

CURSOR cursor_name
IS
select_statement
FOR UPDATE [of column_list] [NOWAIT];

For example, you could use the Select For Update statement as follows:

CURSOR c1
IS
SELECT course_number, instructor
from courses_tbl
FOR UPDATE of instructor;

If you plan on updating or deleting records that have been referenced by a Select For
Update statement, you can use the Where Current Of statement.

WHERE CURRENT OF Statement

If you plan on updating or deleting records that have been referenced by a Select For
Update statement, you can use the Where Current Of statement.

The syntax for the Where Current Of statement is either:

UPDATE table_name
SET set_clause
WHERE CURRENT OF cursor_name;

OR

DELETE FROM table_name
WHERE CURRENT OF cursor_name;

The Where Current Of statement allows you to update or delete the record that was last
fetched by the cursor.

Updating using the WHERE CURRENT OF Statement

Here is an example where we are updating records using the Where Current Of
Statement:

CREATE OR REPLACE Function FindCourse
( name_in IN varchar2 )
RETURN number
IS
cnumber number;

CURSOR c1
IS
SELECT course_number
from courses_tbl
where course_name = name_in
FOR UPDATE of instructor;

BEGIN

open c1;
fetch c1 into cnumber;

if c1%notfound then
cnumber := 9999;

else
UPDATE courses_tbl
SET instructor = 'SMITH'
WHERE CURRENT OF c1;

COMMIT;

end if;

close c1;

RETURN cnumber;

END;

Deleting using the WHERE CURRENT OF Statement

Here is an example where we are deleting records using the Where Current Of
Statement:

CREATE OR REPLACE Function FindCourse
( name_in IN varchar2 )
RETURN number
IS
cnumber number;

CURSOR c1
IS
SELECT course_number
from courses_tbl
where course_name = name_in
FOR UPDATE of instructor;

BEGIN

open c1;
fetch c1 into cnumber;

if c1%notfound then
cnumber := 9999;

else
DELETE FROM courses_tbl
WHERE CURRENT OF c1;

COMMIT;

end if;

close c1;

RETURN cnumber;

END;

Download CURSORBasics.pdf.html