Oracle/PLSQL: FETCH Statement

 

Oracle/PLSQL: FETCH Statement

---

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 FETCH the rows from your cursor.

译：大多数情况下，使用游标的目的，就是从你的游标中取得行，然后可以对这些数据进行一些操作。声明操作游标后，下一步就是从游标中 FETCH 行了。

The basic syntax for a FETCH statement is:

FETCH cursor_name INTO <list of variables>;

 

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;

译：这将会把第一个 course_number 取到名为 cnumber 的游标中。

Below is a function that demonstrates how to use the FETCH statement.

译：下面是一个演示如何使用 FETCH 语句的方法。

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;