FETCH Statement

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

Description

The purpose of using a cursor, in most cases, is to retrieve the rows from your cursor so that some type of operation can be performed on the data. After declaring and opening your cursor, the next step is to use the FETCH statement to fetch rows from your cursor.

Syntax

The syntax for the FETCH statement in Oracle/PLSQL is:

FETCH cursor_name INTO variable_list;

Parameters or Arguments

cursor_name

The name of the cursor that you wish to fetch rows.

variable_list

The list of variables, comma delimited, that you wish to store the cursor result set in.

Example

For example, you could have a cursor defined as:

CURSOR c1

IS

   SELECT course_number

   FROM courses_tbl

   WHERE course_name = name_in;

The command that would be used to fetch the data from this cursor is:

FETCH c1 into cnumber;

This would fetch the first course_number into the variable called cnumber.

Below is a function that demonstrates how to use the FETCH 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;