Oracle / PLSQL: Declare a Cursor

This Oracle tutorial explains how to declare a cursor in Oracle/PLSQL with syntax and examples.

Description

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 to declare a cursor.

Cursor without parameters (simplest)

Declaring a cursor without any parameters is the simplest cursor. Let's take a closer look.

Syntax

The syntax for a cursor without parameters in Oracle/PLSQL is:

CURSOR cursor_name
IS
  SELECT_statement;

Example

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

As we get more complicated, we can declare cursors with parameters.

Syntax

The syntax for a cursor with parameters in Oracle/PLSQL is:

CURSOR cursor_name (parameter_list)
IS
  SELECT_statement;

Example

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

Finally, we can declare a cursor with a return clause.

Syntax

The syntax for a cursor with a return clause in Oracle/PLSQL is:

CURSOR cursor_name
RETURN field%ROWTYPE
IS
   SELECT_statement;

Example

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.