数据库游标简介

目录一、引入游标二、声明游标变量三、打开游标四、提取数据五、属性六、扩展FETCHFETCH部分参数简介：MOVEUPDATE/DELETE WHERE CURRENT OFCLOSE七、返回游标一、引入游标为了避免一次执行整个SQL，返回结果包含大量行，撑爆内存，整个时候可以使用游标来封装刚刚提到的“整个查询”。游标有显式游标和隐式游标：显式游标主要用于对查询语句的处理，尤其是查询结果返回多条记

承缘丶

4252人浏览 · 2021-07-15 20:02:01

承缘丶 · 2021-07-15 20:02:01 发布

UPDATE/DELETE WHERE CURRENT OF

七、返回游标

一、引入游标

为了避免一次执行整个SQL，返回结果包含大量行，撑爆内存，整个时候可以使用游标来封装刚刚提到的“整个查询”。

游标有显式游标和隐式游标：

显式游标主要用于对查询语句的处理，尤其是查询结果返回多条记录的情况。
隐式游标由系统定义，“对于非查询语句，如修改、删除操作，则由系统自动地为这些操作设置游标并创建其工作区，这些由系统隐含创建的游标称为隐式游标，隐式游标的名字为SQL”。

一个游标使用的例子（下文皆以MPP为例）：

CREATE OR REPLACE FUNCTION "jwy_sys"."cursorTest_lyg"()

  RETURNS "pg_catalog"."text" AS $BODY$

        

         DECLARE

                  -- 事先定义变量
 
                  dbms_output text;

                  product_id nvarchar2(55);

                  product_name nvarchar2(55);

                  category nvarchar2(55);

        
            -- 游标1
         CURSOR c1 is

                  select product_id,product_name,category from jwy_tst.products;

        
            -- 游标2
         CURSOR c2(sect_id INTEGER) is

                  select product_id,product_name,category from jwy_tst.products where product_id > sect_id;

                 
            -- 游标3
         TYPE CURSOR_TYPE is ref CURSOR ;

         c3 CURSOR_TYPE;

        

         sql_str text;

         BEGIN

         -- Routine body goes here...

                  OPEN c1;

                          LOOP

                                   fetch c1 into product_id,product_name,category;

                                   exit when c1%NOTFOUND;

                                   dbms_output := dbms_output || ';' || product_id||'-'||product_name||'-'||category;

                         

                          END LOOP;

                          CLOSE c1;




                  OPEN c2(1600);

                          LOOP

                                   fetch c2 into product_id,product_name,category;

                                   exit when c2%NOTFOUND;

                                   dbms_output := dbms_output || ';' || product_id||'--'||product_name||'--'||category;

                         

                          END LOOP;

                          CLOSE c2;




         sql_str := 'select product_id,product_name,category from jwy_tst.products;';

         open c3 for sql_str;

                          LOOP

                                   fetch c3 into product_id,product_name,category;

                                   exit when c3%NOTFOUND;

                                   dbms_output := dbms_output || ';' || product_id||'---'||product_name||'---'||category;

                         

                          END LOOP;

                          CLOSE c3;

                         

         RETURN dbms_output;

END$BODY$

  LANGUAGE plpgsql VOLATILE

  COST 100

二、声明游标变量

就如上面第一个例子中的语句，定义变量的方式：

         CURSOR c1 is

                  select product_id,product_name,category from jwy_tst.products;

        

         CURSOR c2(sect_id INTEGER) is

                  select product_id,product_name,category from jwy_tst.products where product_id > sect_id;

                 

         TYPE CURSOR_TYPE is ref CURSOR ;

         c3 CURSOR_TYPE;

上面三个变量都是refcursor类型（特殊的数据类型），第二个已经被绑定了一个完全指定的查询，最后一个呗绑定了一个参数化查询，第一个是可以用于任何查询的，它没有被绑定。

常用声明语法：

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

解释：为了兼容Oracle，FOR可以改为IS。如果指定了SCROLL则游标可以反向查，NO SCROLL则不可以，如果不指定，则将这个是否可以反向取交给了查询决定。

三、打开游标

就如上面第一个例子中的语句，打开游标：

OPEN c1;


OPEN c2(1600);


sql_str := 'select product_id,product_name,category from jwy_tst.products;';

open c3 for sql_str;

附加一个例子:

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

 keyvalue;

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

四、提取数据

检索结果集中的数据行，放入指定的变量中。

例如：

OPEN c1;

        LOOP

             fetch c1 into product_id,product_name,category;

             exit when c1%NOTFOUND;

             dbms_output := dbms_output || ';' || product_id||'-'||product_name||'-'||category;

                         
        END LOOP;

        CLOSE c1;

五、属性

游标的属性用于控制程序流程或者了解程序的状态。当运行DML语句时，数据库打开一个内建游标并处理结果，游标是维护查询结果的内存中的一个区域，游标在运行DML语句时打开，完成后关闭。

显式游标的属性为：

•%FOUND布尔型属性：当最近一次读记录时成功返回，则值为TRUE。

•%NOTFOUND布尔型属性：与%FOUND相反。

•%ISOPEN布尔型属性：当游标已打开时返回TRUE。

•%ROWCOUNT数值型属性：返回已从游标中读取的记录数。

隐式游标属性为：

•SQL%FOUND布尔型属性：当最近一次读记录时成功返回，则值为TRUE。

•SQL%NOTFOUND布尔型属性：与%FOUND相反。

•SQL%ROWCOUNT数值型属性：返回已从游标中读取得记录数。

•SQL%ISOPEN布尔型属性：取值总是FALSE。SQL语句执行完毕立即关闭隐式游标。

例如：

DELETE FROM aaa WHERE a.id = 1;


IF SQL%NOTFOUND THEN

    DELETE FROM aaa WHERE a.id = 2;

END IF;

六、扩展

FETCH

FETCH [ direction { FROM | IN } ] cursor_name;

注释：{ FROM | IN } cursor_name使用关键字FROM或IN指定游标名称。取值范围是已创建的游标的名称。

其中direction子句可以是 SQL FETCH命令中允许的除了那些能够取得多于一行的任何变体。

即它可以是：

NEXT
PRIOR
FIRST
LAST
ABSOLUTE count
RELATIVE count
count
ALL
FORWARD
FORWARD count
FORWARD ALL
BACKWARD
BACKWARD count
BACKWARD ALL

FETCH部分参数简介：

- NEXT（缺省值）

从当前关联位置开始，抓取下一行。

- PRIOR

从当前关联位置开始，抓取上一行。

- FIRST

抓取查询的第一行（和ABSOLUTE 1相同）。

- LAST

抓取查询的最后一行（和ABSOLUTE -1相同）。

- ABSOLUTE count

抓取查询中第count行。

ABSOLUTE抓取不会比用相对位移移动到需要的数据行更快，因为下层的实现必须遍历所有中间的行。

count取值范围：有符号的整数

count为正数，就从查询结果的第一行开始，抓取第count行。当count小于当前游标位置时，涉及到rewind操作，暂不支持。

count为负数或0，涉及到反向扫描操作，暂不支持。

- RELATIVE count

从当前关联位置开始，抓取随后或前面的第count行。

取值范围：有符号的整数

count为正数就抓取当前关联位置之后的第count行。

如果有数据的话，RELATIVE 0重新抓取当前行。

count为负数，涉及到反向扫描操作，暂不支持。

- FORWARD

抓取下一行（和NEXT一样）。

- FORWARD count

与RELATIVE count的效果相同，从当前关联位置开始，抓取随后或前面的第count行。

- FORWARD ALL

从当前关联位置开始，抓取所有剩余行。

- BACKWARD

从当前关联位置开始，抓取前面一行(和PRIOR一样) 。

- BACKWARD count

从当前关联位置开始，抓取前面的count行（向后扫描）。

取值范围：有符号的整数

count为正数就抓取当前关联位置之前的第count行。

如果有数据的话，BACKWARD 0重新抓取当前行。

count为负数就抓取当前关联位置之后的第abs（count）行。

- BACKWARD ALL
从当前关联位置开始，抓取所有前面的行（向后扫描）。

省略direction和指定NEXT是一样的。除非游标被使

用SCROLL选项声明或打开，否则要求反向移动的direction值很可能会失败。

MOVE

MOVE在不检索数据的情况下重新定位一个游标。

MOVE [ direction [ FROM | IN ] ] cursor_name;

其中direction子句为可选参数。与FETCH相同。

UPDATE/DELETE WHERE CURRENT OF

UPDATE table SET ... WHERE CURRENT OF cursor;

DELETE FROM table WHERE CURRENT OF cursor;

当一个游标被定位到一个表行上时，使用该游标标识该行就可以对它进行更新或删除。对于

游标的查询可以是什么是有限制的（尤其是不能有分组），并且最好在游标中使用FOR

UPDATE。

CLOSE

CLOSE { cursor_name | ALL } ;

CLOSE cursor;

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

该游标变量以便再次打开。

七、返回游标

CREATE OR REPLACE FUNCTION "jwy_sys"."cursorTest_lyg2"(refcursor)

  RETURNS "pg_catalog"."refcursor" AS $BODY$

         BEGIN

         -- Routine body goes here...


         open $1 for select product_id,product_name,category from jwy_tst.products;


         RETURN $1;

END$BODY$

  LANGUAGE plpgsql VOLATILE

  COST 100

此时去调用：

开启事务：
begin

执行SQL：
select * from "jwy_sys"."cursorTest_lyg2"('refcursor');

FETCH ALL IN refcursor;

提交：
COMMIT

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

华为云开发者联盟

为开发者提供学习成长、分享交流、生态实践、资源工具等服务，帮助开发者快速成长。

更多推荐

通过HPA+CronHPA组合应对业务复杂弹性伸缩场景

华为云开发者联盟

FT-FMEA融合混沌演练，零售运营系统韧性架构在线验证实践

华为云开发者联盟

如何使用Python和Plotly绘制3D图形

华为云开发者联盟

所有评论(0)

查看更多评论

承缘丶

@qq_30168227

已为社区贡献4条内容