Oracle FETCH operator.

Preamble

FETCH operator – The purpose of using a cursor, in most cases, is to obtain rows from the cursor so that some type of operation can be performed on data.

After declaring and opening the cursor, the next step is to select rows from the cursor using the OracleFETCH operator.

Syntax of the FETCH operator

FETCH cursor name INTO variable_list;

Parameters and arguments of the operator

• cursor_name – The name of the cursor from which you want to extract strings.
• variable_list – is the list of comma-separated variables into which you want to save the resulting set of cursors.

Example:
Let’s define the cursor this way.

CURSOR c1
IS
SELECT course_number
FROM courses_tbl
WHERE course_name = name_in;

The command that will be used to extract data from this cursor:

FETCH c1 into cnumber;

This command will select the first course_number in the cnumber variable.
Next is a function that shows how to use the FETCH operator.

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;

About Enteros

Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of clouds, RDBMS, NoSQL, and machine learning database platforms.