Oracle Cursor游标

Oracle游标 是指向私有 SQL 区域的指针，该区域存储有关 SELECT 或 DML 语句（如 INSERT、UPDATE、DELETE 或 MERGE）处理的信息。

游标是一种机制，可帮助您为 SELECT 语句指定名称并操作该 SQL 语句中的信息。

一、Oracle 如何声明游标

语法：

CURSOR cursor_name  
IS  
  SELECT_statement;   

让我们看看如何定义一个名为 c1 的游标。我们正在使用包含“course_id”和“course_name”列的表名“course”。

示例：

CURSOR c1  
IS  
  SELECT course_id  
  FROM courses  
  WHERE course_name = name_in;  

在上面的示例中，该游标的结果集是所有 course_id，其 course_name 与名为 name_in 的变量匹配。

二、Oracle 如何在函数中使用光标

语法：

CREATE OR REPLACE Function FindCourse  
   ( name_in IN varchar2 )  
   RETURN number  
IS  
 cnumber number;  
CURSOR c1  
   IS  
     SELECT course_id  
     FROM courses  
     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;  

输出结果为：

Function created.
0.09 seconds

三、Oracle 如何打开游标

游标声明后，必须使用 open 语句打开游标。

语法：

OPEN cursor_name;  

示例：

OPEN c1;  

四、Oracle 如何在函数中使用打开光标

此函数指定如何使用 open 语句。

示例：

CREATE OR REPLACE Function FindCourse  
  ( name_in IN varchar2 )  
  RETURN number  
IS  
   cnumber number;  
CURSOR c1  
   IS  
     SELECT course_id  
     FROM courses  
   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;  

输出结果为：

Function created.
0.09 seconds

五、如何从游标中获取行

此语句在声明并打开游标后使用。它用于从游标中获取行。

语法：

FETCH cursor_name INTO variable_list;   

参数说明：

• cursor_name：它指定您希望获取行的游标的名称。
• variable_list：它指定您希望存储游标结果集的变量列表。

示例：

定义一个游标：

CURSOR c1  
IS  
   SELECT course_id  
   FROM courses  
   WHERE course_name = name_in;  

获取数据：

FETCH c1 into cnumber;  

让我们举个例子，将 course_id 获取到名为 cnumber 的变量中。

CREATE OR REPLACE Function FindCourse  
   ( name_in IN varchar2 )  
   RETURN number  
IS  
   cnumber number;  
 CURSOR c1  
   IS  
     SELECT course_id  
     FROM courses  
     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;   

六、如何关闭光标

CLOSE 语句是最后一步，它用于在您完成使用游标后关闭游标。

语法：

CLOSE cursor_name;  

关闭游标的语句：

CLOSE c1;  

示例：

CREATE OR REPLACE Function FindCourse  
   ( name_in IN varchar2 )  
   RETURN number  
IS  
cnumber number;  
CURSOR c1  
   IS  
     SELECT course_id  
     FROM courses  
     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;  

七、光标内的光标

也可以在游标中声明游标。以下示例指定如何在游标中声明游标。

在此示例中，有一个名为 get_tables 的游标检索所有者和 table_name 值。然后将这些值用于名为 get_columns 的第二个游标中。

示例：

CREATE OR REPLACE PROCEDURE MULTIPLE_CURSORS_PROC is  
   v_owner varchar2(40);  
   v_table_name varchar2(40);  
   v_column_name varchar2(100);  
     
   /* First cursor */  
   CURSOR get_tables IS  
     SELECT DISTINCT tbl.owner, tbl.table_name  
     FROM all_tables tbl  
     WHERE tbl.owner = 'SYSTEM';  
       
   /* Second cursor */  
   CURSOR get_columns IS  
     SELECT DISTINCT col.column_name  
     FROM all_tab_columns col  
     WHERE col.owner = v_owner  
     AND col.table_name = v_table_name;  
       
   BEGIN  
     
   -- Open first cursor  
   OPEN get_tables;  
   LOOP  
      FETCH get_tables INTO v_owner, v_table_name;  
        
      -- Open second cursor  
      OPEN get_columns;  
      LOOP  
         FETCH get_columns INTO v_column_name;  
      END LOOP;  
     CLOSE get_columns;  
    END LOOP;  
   CLOSE get_tables;  
  EXCEPTION  
   WHEN OTHERS THEN  
 raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);  
end MULTIPLE_CURSORS_PROC;  

输出结果为：

Procedure created.
0.16 seconds

注意：每次从第一个游标检索新记录时，您必须不断打开和关闭第二个游标。这样，第二个游标将使用第一个游标的新变量值。