openGauss每日一练第17天 | 游标的学习

今天学习游标，CURSOR命令定义一个游标，用于在一个大的查询里面检索少数几行数据。

游标的作用:为了处理SQL语句，存储过程进程分配一段内存区域来保存上下文联系。游标是指向上下文区域的句柄或指针。借助游标，存储过程可以控制上下文区域的变化。

https://opengauss.org/zh/docs/2.1.0/docs/Developerguide/CURSOR.html

语法：

CURSOR cursor_name
    [ BINARY ]  [ NO SCROLL ]  [ { WITH | WITHOUT } HOLD ]
    FOR query ;
复制

注意事项：

• 游标命令只能在事务块里使用；
• 通常游标和SELECT一样返回文本格式。因为数据在系统内部是用二进制格式存储的，系统必须对数据做一定转换以生成文本格式。一旦数据是以文本形式返回，客户端应用需要把它们转换成二进制进行操作。使用FETCH语句，游标可以返回文本或二进制格式。
• 应该小心使用二进制游标。文本格式一般都比对应的二进制格式占用的存储空间大。二进制游标返回内部二进制形态的数据，可能更易于操作。如果想以文本方式显示数据，则以文本方式检索会为用户节约很多客户端的工作。比如，如果查询从某个整数列返回1，在缺省的游标里将获得一个字符串1，但在二进制游标里将得到一个4字节的包含该数值内部形式的数值（大端顺序）。

游标的使用需要结合fetch来获取数据；

https://opengauss.org/zh/docs/2.1.0/docs/Developerguide/FETCH.html#zh-cn_topic_0283137321_zh-cn_topic_0237122165_zh-cn_topic_0059778422_s1ee72832a27547e4949061a010e24578

学习笔记：

1.     视图查看游标信息：pg_cursors
2.     游标for 跟sql语句也可用values:CURSOR [cursor] FOR VALUES(1,2),(0,3) ORDER BY 1;
3.     抓取游标数据：FETCH FORWARD n FROM [cursor];

4.     游标在相应事务结束后仍可使用:WITH HOLD

课程练习

1.创建游标，且使用select子句指定游标返回的行，分别使用FETCH抓取数据，MOVE重定位游标

openGauss=# select * from t;
 id |              name              
----+--------------------------------
  1 | a                             
  2 | b                             
  3 | c                             
  4 | d                             
(4 rows)

openGauss=# start transaction;
START TRANSACTION
openGauss=# cursor cursor1 for select * from t;
DECLARE CURSOR
openGauss=# select * from pg_cursors;
  name   |              statement              | is_holdable | is_binary | is_scrollable |         creation_time         
---------+-------------------------------------+-------------+-----------+---------------+-------------------------------
 cursor1 | cursor cursor1 for select * from t; | f           | f         | t             | 2021-12-18 21:31:52.387529+08
(1 row)

openGauss=# fetch forward 2 cursor1;
 id |              name              
----+--------------------------------
  1 | a                             
  2 | b                             
(2 rows)

openGauss=# move forward 1 from cursor1;
MOVE 1
openGauss=# fetch forward 1 cursor1;
 id |              name              
----+--------------------------------
  4 | d                             
(1 row)

openGauss=# 
复制

2.在系统视图pg_cursors中查看游标

openGauss=# start transaction;
START TRANSACTION
openGauss=# cursor cursor1 for select * from t;
DECLARE CURSOR
openGauss=# select * from pg_cursors;
  name   |              statement              | is_holdable | is_binary | is_scrollable |         creation_time         
---------+-------------------------------------+-------------+-----------+---------------+-------------------------------
 cursor1 | cursor cursor1 for select * from t; | f           | f         | t             | 2021-12-18 21:31:52.387529+08
(1 row)
复制

3.创建一个使用游标的存储过程

create or replace procedure p_cursor
as
    aa    varchar(100);
    bb  integer;
    cursor c1_all is select id, name from t order by 1;
begin
    if not c1_all%isopen then
        open c1_all;
    end if;
    loop
        fetch c1_all into bb, aa;
                RAISE INFO 'aa: %' ,aa;
        exit when c1_all%notfound;
    end loop;
    if c1_all%isopen then
        close c1_all;
    end if;
end;
/
复制

openGauss=# call p_cursor();
INFO:  aa: a
INFO:  aa: b
INFO:  aa: c
INFO:  aa: d
INFO:  aa: d
 p_cursor 
----------
 
(1 row)
复制

4.清理数据

openGauss=# drop procedure p_cursor;
DROP PROCEDURE
openGauss=# 
复制