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.
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.