游标

声明游标变量

所有在PL/SQL中对游标的访问都会通过游标变量，它总是特殊的数据类型refcursor。创建游标变量的一种方法是把它声明为一个类型为refcursor的变量。另外一种方法是使用游标声明语法，通常是：

    name [ [ NO ] SCROLL ] CURSOR [ ( arguments ) ] FOR query;

（为了对Oracle的兼容性，可以用IS替代FOR）。如果指定了SCROLL，那么游标可以反向滚动；如果指定了NO SCROLL，那么反向取的动作会被拒绝；如果二者都没有被指定，那么能否进行反向取就取决于查询。如果指定了arguments， 那么它是一个逗号分隔的name`` ``datatype对的列表， 它们定义在给定查询中要被参数值替换的名称。实际用于替换这些名字的值将在游标被打开之后指定。

示例：

    DECLARE
        curs1 refcursor;
        curs2 CURSOR FOR SELECT * FROM tenk1;
        curs3 CURSOR (key integer) FOR SELECT * FROM tenk1 WHERE unique1 = key;

所有这三个变量都是refcursor类型，但是第一个可以用于任何查询，而第二个已经被绑定了一个完全指定的查询，并且最后一个被绑定了一个参数化查询。（游标被打开时，key将被一个整数参数值替换）。变量curs1被称为未绑定，因为它没有被绑定到任何特定查询。

打开游标

在一个游标可以被用来检索行之前，它必需先被打开（这是和 SQL 命令DECLARE CURSOR等效的操作）。PL/SQL有三种形式的OPEN命令，其中两种用于未绑定游标变量，另外一种用于已绑定的游标变量。

OPEN FOR query

    OPEN unbound_cursorvar [ [ NO ] SCROLL ] FOR query;

该游标变量被打开并且被给定要执行的查询。游标不能是已经打开的，并且它必需已经被声明为一个未绑定的游标变量（也就是声明为一个简单的refcursor变量）。该查询必须是一条SELECT或者其它返回行的东西（例如EXPLAIN）。该查询会按照其它PL/SQL中的 SQL 命令同等的方式对待：先代换PL/SQL变量名，并且执行计划会被缓存用于可能的重用。当一个PL/SQL变量被替换到游标查询中时，替换的值是在OPEN时它所具有的值。对该变量后续的改变不会影响游标的行为。对于一个已经绑定的游标，SCROLL和NO SCROLL选项具有相同的含义。

示例：

    OPEN curs1 FOR SELECT * FROM foo WHERE key = mykey;

OPEN FOR EXECUTE

    OPEN unbound_cursorvar [ [ NO ] SCROLL ] FOR EXECUTE query_string
                                         [ USING expression [, ... ] ];

打开游标变量并且执行指定的查询。该游标不能是已打开的，并且必须已经被声明为一个未绑定的游标变量（也就是声明为一个简单的refcursor变量）。该查询以和EXECUTE中相同的方式被指定为一个字符串表达式。照例，这提供了灵活性，因此查询计划可以在两次运行之间变化，并且它也意味着在该命令字符串上还没有完成变量替换。正如EXECUTE，可以通过format()和USING将参数值插入到动态命令中。SCROLL和NO SCROLL选项具有和已绑定游标相同的含义。

示例：

    OPEN curs1 FOR EXECUTE format('SELECT * FROM %I WHERE col1 = $1',tabname) USING keyvalue;

在这个例子中，表名被通过format()插入到查询中。 col1的比较值被通过一个USING参数插入， 所以它不需要引用。

打开一个已绑定的游标

    OPEN bound_cursorvar [ ( [ argument_name := ] argument_value [, ...] ) ];

这种形式的OPEN被用于打开一个游标变量，它的查询是在声明时绑定的。该游标不能是已经打开的。当且仅当该游标被声明为接收参数时，才必需出现一个实际参数值表达式的列表。这些值将被替换到命令中。

一个已绑定游标的查询计划总是被认为是可缓存的，在这种情况中没有EXECUTE的等效形式。注意SCROLL和NO SCROLL不能在OPEN中指定，因为游标的滚动行为已经被确定。

使用位置或命名记号可以传递参数值。在位置记号中，所有参数都必须按照顺序指定。在命名记号中，每一个参数的名字被使用:=指定以将它和参数表达式分隔开，允许混合位置和命名记号。

例子（这些例子使用上面例子中的游标声明）：

    OPEN curs2;
    OPEN curs3(42);
    OPEN curs3(key := 42);

因为在一个已绑定游标的查询上已经完成了变量替换，实际有两种方式将值传到游标中：给OPEN一个显式参数，或者在查询中隐式引用一个PL/SQL变量。不过，只有在已绑定游标之前声明的变量才将会被替换到游标中。在两种情况下，要被传递的值都是在OPEN时确定的。例如，得到上例中curs3相同效果的另一种方式是

    DECLARE
        key integer;
        curs4 CURSOR FOR SELECT * FROM tenk1 WHERE unique1 = key;
    BEGIN
        key := 42;
        OPEN curs4;

使用游标

一旦一个游标已经被打开，那么就可以用这里描述的语句操作它。

这些操作不需要发生在打开该游标开始操作的同一个函数中。你可以从一个函数返回一个refcursor值，并且让调用者在该游标上操作（在内部，refcursor值只是一个包含该游标活动查询的所谓入口的字符串名称。这个名字可以被传递、赋予给其它refcursor变量等等，而不用担心扰乱入口）。

所有入口会在事务的结尾被隐式地关闭。因此一个refcursor值只能在该事务结束前用于引用一个打开的游标。

FETCH

    FETCH [ direction { FROM | IN } ] cursor INTO target;

就像SELECT INTO一样，FETCH从游标中检索下一行到目标中，目标可以是一个行变量、记录变量或者逗号分隔的简单变量列表。如果没有下一行，目标会被设置为 NULL。与SELECT INTO一样，可以检查特殊变量FOUND来看一行是否被获得。

direction子句可以是 SQL FETCH命令中允许的任何变体，除了那些能够取得多于一行的。即它可以是 NEXT、 PRIOR、 FIRST、 LAST、 ABSOLUTE count、 RELATIVE count、 FORWARD或者 BACKWARD。 省略direction和指定NEXT是一样的。在使用count的形式中，count可以是任意的整数值表达式（与 SQL 命令FETCH不一样，FETCH仅允许整数常量）。除非游标被使用SCROLL选项声明或打开，否则要求反向移动的direction值很可能会失败。

cursor必须是一个引用已打开游标入口的refcursor变量名。

示例：

    FETCH curs1 INTO rowvar;
    FETCH curs2 INTO foo, bar, baz;
    FETCH LAST FROM curs3 INTO x, y;
    FETCH RELATIVE -2 FROM curs4 INTO x;

MOVE

    MOVE [ direction { FROM | IN } ] cursor;

MOVE重新定位一个游标而不检索任何数据。MOVE的工作方式与FETCH命令很相似，但是MOVE只是重新定位游标并且不返回至移动到的行。与SELECT INTO一样，可以检查特殊变量FOUND来看要移动到的行是否存在。

示例：

    MOVE curs1;
    MOVE LAST FROM curs3;
    MOVE RELATIVE -2 FROM curs4;
    MOVE FORWARD 2 FROM curs4;

UPDATE/DELETE WHERE CURRENT OF

    UPDATE table SET ... WHERE CURRENT OF cursor;
    DELETE FROM table WHERE CURRENT OF cursor;

当一个游标被定位到一个表行上时，使用该游标标识该行就可以对它进行更新或删除。对于游标的查询可以是什么是有限制的（尤其是不能有分组），并且最好在游标中使用FOR UPDATE。

示例：

    UPDATE foo SET dataval = myval WHERE CURRENT OF curs1;

CLOSE

    CLOSE cursor;

CLOSE关闭一个已打开游标的底层入口。这样就可以在事务结束之前释放资源，或者释放掉该游标变量以便再次打开。

示例：

    CLOSE curs1;

返回游标

PL/SQL函数可以向调用者返回游标。这对于返回多行或多列（特别是巨大的结果集）很有用。要想这么做，该函数打开游标并且把该游标的名字返回给调用者（或者简单的使用调用者指定的或已知的入口名打开游标）。调用者接着可以从游标中取得行。游标可以由调用者关闭，或者是在事务关闭时自行关闭。

用于一个游标的入口名可以由编程者指定或者自动生成。要指定一个入口名，只需要在打开refcursor变量之前简单地为它赋予一个字符串。OPEN将把refcursor变量的字符串值用作底层入口的名字。不过，如果refcursor变量为空，OPEN会自动生成一个与任何现有入口不冲突的名称，并且将它赋予给refcursor变量。

下面的例子显示了一个调用者提供游标名字的方法：

   CREATE TABLE test (col text);
    INSERT INTO test VALUES ('123');

    CREATE FUNCTION reffunc(refcursor) RETURN refcursor IS
    BEGIN
        OPEN $1 FOR SELECT col FROM test;
        RETURN $1;
    END; 

    BEGIN;
    SELECT reffunc('funccursor');
    FETCH ALL IN funccursor;
    COMMIT;

下面的例子使用了自动游标名生成：

    CREATE FUNCTION reffunc2() RETURN refcursor IS
    DECLARE
        ref refcursor;
    BEGIN
        OPEN ref FOR SELECT col FROM test;
        RETURN ref;
    END; 

    -- 需要在一个事务中使用游标。
    BEGIN;
    SELECT reffunc2();

          reffunc2
    --------------------
     <unnamed cursor 1>
    (1 row)

    FETCH ALL IN "<unnamed cursor 1>";
    COMMIT;

下面的例子展示了从一个函数中返回多个游标的一种方法：

    CREATE FUNCTION myfunc(refcursor, refcursor) RETURN SETOF refcursor IS
    BEGIN
        OPEN $1 FOR SELECT * FROM table_1;
        RETURN NEXT $1;
        OPEN $2 FOR SELECT * FROM table_2;
        RETURN NEXT $2;
    END;
    

    -- 需要在一个事务中使用游标。
    BEGIN;

    SELECT * FROM myfunc('a', 'b');

    FETCH ALL FROM a;
    FETCH ALL FROM b;
    COMMIT;

通过一个游标的结果循环

有一种FOR语句的变体，它允许通过游标返回的行进行迭代。语法是：

    [ <<label>> ]
    FOR recordvar IN bound_cursorvar [ ( [ argument_name := ] argument_value [, ...] ) ] LOOP
        statements
    END LOOP [ label ];

该游标变量必须在声明时已经被绑定到某个查询，并且它不能已经被打开。FOR语句会自动打开游标，并且在退出循环时自动关闭游标。当且仅当游标被声明要使用参数时，才必须出现一个实际参数值表达式的列表。这些值会被替换到查询中，采用OPEN期间的方式。

变量recordvar会被自动定义为record类型，并且只存在于循环内部（循环中该变量名任何已有定义都会被忽略）。每一个由游标返回的行都会被陆续地赋值给这个记录变量并且执行循环体。