Using Declare a Cursor in Oracle

This article is written about how to declare a cursor in Oracle/PLSQL with syntax and examples.

Description

A cursor is a SELECT declaration that is described inside the announcement part of your PLSQL code. We’ll take a seem at three distinct syntaxes to declare a cursor.

Cursor without parameters (simplest)

Declaring a cursor except any parameters is the simplest cursor. Let’s take a nearer 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 referred to as 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 suits the variable known as 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 extra 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 may want to outline 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 fits the subject_id surpassed to the cursor through 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 should define a cursor referred to as c3 as below.

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

The end result set of this cursor is all columns from the course_tbl the place the concern is Mathematics.