Oracle declare cursor.

Preamble

The cursor is certain operator SELECT which is declared in PLSQL code. Let’s consider three different syntaxes of the cursor declaration.

A CURSOR WITHOUT PARAMETERS (SIMPLE)

Cursor announcement without any parameters, simple cursor.

Syntax

CURSOR cursor_name
IS
SELECT_statement;

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

CURSOR c1
IS
SELECT course_number
FROM courses_tbl
WHERE course_name = name_in;

The resultant set of this cursor is all course_numbers in which course_name matches variable name_in.
Below is the 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;

PARAMETER CURSOR

Let’s declare a cursor with parameters.

Syntax

CURSOR cursor_name (parameter_list)
IS
SELECT_statement;

For example, you can define a cursor called c2, as shown 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 where subject_id corresponds to subject_id obtained with the cursor using the subject_id_in parameter.

A CURSOR WITH RETURN CONDITION

Finally, we can announce the cursor with a return condition.

Syntax

CURSOR cursor_name
RETURN field%ROWTYPE
IS
SELECT_statement;

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

CURSOR c3
RETURN courses_tbl%ROWTYPE
IS
SELECT *
FROM courses_tbl
WHERE subject = 'Mathematics';

The result set of this cursor will be all columns of course_tb with the subject having ‘Mathematics’.

Cursors in Oracle PLSQL

About Enteros

​Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of clouds, RDBMS, NoSQL, and machine learning database platforms.​