Oracle / PLSQL: OPEN Statement

This Oracle tutorial explains how to use the Oracle/PLSQL OPEN statement with syntax and examples.

Description

Once you've declared your cursor, the next step is to use the OPEN statement to open the cursor.

Syntax

The syntax to open a cursor using the OPEN statement in Oracle/PLSQL is:

OPEN cursor_name;

Parameters or Arguments

cursor_name

The name of the cursor that you wish to open.

Example

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;