This article is written about how to use the Oracle/PLSQL FETCH announcement with syntax and examples.
Description
The purpose of the use of a cursor, in most cases, is to retrieve the rows from your cursor so that some kind of operation can be carried out on the data. After declaring and opening your cursor, the subsequent step is to use the FETCH assertion to fetch rows from your cursor.
Syntax
The syntax for the FETCH assertion in Oracle/PLSQL is:
FETCH cursor_name INTO variable_list;Parameters or Arguments
cursor_name The identify of the cursor that you desire to fetch rows. variable_list The listing of variables, comma delimited, that you wish to shop the cursor end 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 information from this cursor is:
FETCH c1 into cnumber;This would fetch the first course_number into the variable called cnumber.
Below is a feature 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;