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;