Oracle游标用法详解

本文概述

• 如何声明游标
• 如何在函数中使用游标
• 如何打开游标
• 如何在函数中使用打开游标
• 如何从游标中获取行
• 如何关闭游标
• 游标内的游标

游标是指向专用SQL区域的指针, 该区域存储有关SELECT或DML语句(如INSERT, UPDATE, DELETE或MERGE)的处理信息。

游标是一种机制, 可帮助你为SELECT语句分配名称并操纵该SQL语句中的信息。

如何声明游标

句法

CURSOR cursor_name
IS
  SELECT_statement;

让我们看看如何定义一个称为c1的游标。我们使用的表名称为” course”, 其列为” course_id”和” course_name”。

例子

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

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

如何在函数中使用游标

例子

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

如何打开游标

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

句法

OPEN cursor_name;

例子

OPEN c1;

如何在函数中使用打开游标

此函数指定如何使用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;

参数

1)cursor_name：指定要获取行的游标的名称。

2)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

注意：每次从第一个游标检索到新记录时, 都必须连续打开和关闭第二个游标。这样, 第二个游标将使用第一个游标的新变量值。