openGauss每日一练第17天|cursor

学习openGauss定义游标

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


连接数据库
root@modb:~# su - omm
omm@modb:~$ gsql -r
gsql ((openGauss 2.0.0 build 78689da9) compiled at 2021-03-31 21:03:52 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.


1.创建游标，且使用select子句指定游标返回的行，分别使用FETCH抓取数据，MOVE重定位游标
omm=# create table t1(id int,name varchar(10));
CREATE TABLE
omm=# insert into t1 values(10,'aa'),(20,'bb'),(30,'cc'),(40,'dd'),(50,'ee');
INSERT 0 5

omm=# start transaction;
START TRANSACTION
omm=# cursor c1 for select * from t1;
DECLARE CURSOR
omm=# fetch forward 3 from c1;
id | name
----+------
10 | aa
20 | bb
30 | cc
(3 rows)
omm=# close c1;
CLOSE CURSOR
omm=# end;
COMMIT

omm=# start transaction;
START TRANSACTION
omm=# cursor c1 for select * from t1;
DECLARE CURSOR
omm=# move forward 1 from c1;
MOVE 1
omm=# fetch 3 from c1;
id | name
----+------
20 | bb
30 | cc
40 | dd
(3 rows)

omm=# close c1;
CLOSE CURSOR
omm=# end;
COMMIT



2.在系统视图pg_cursors中查看游标
omm=# start transaction
omm-# ;
START TRANSACTION
omm=# cursor c1 for select * from t1;
DECLARE CURSOR
omm=# select * from pg_cursors;
name | statement | is_holdable | is_binary | is_scrollable | creation_time

------+---------------------------------+-------------+-----------+---------------+------------------------------
-
c1 | cursor c1 for select * from t1; | f | f | t | 2021-12-17 10:45:24.033953+08
(1 row)

omm=# close c1;
CLOSE CURSOR
omm=# select * from pg_cursors;
name | statement | is_holdable | is_binary | is_scrollable | creation_time
------+-----------+-------------+-----------+---------------+---------------
(0 rows)

omm=# end;
COMMIT

3.创建一个使用游标的存储过程
omm=# create or replace procedure pro_cursor
omm=# as
omm=# v_id int;
omm=# v_name varchar(10);
omm=#
omm=# cursor c1_all is select id, name from t1 order by 1;
omm=# begin
omm=# if not c1_all%isopen then
omm=# open c1_all;
omm=# end if;
omm=# loop
omm=# fetch c1_all into v_id, v_name;
omm=# RAISE INFO 'v_id: %' ,v_id;
omm=# exit when c1_all%notfound;
omm=# end loop;
omm=# if c1_all%isopen then
omm=# close c1_all;
omm=# end if;
omm=# end;
omm=# /
CREATE PROCEDURE
omm=# call pro_cursor();
INFO: v_id: 10
INFO: v_id: 20
INFO: v_id: 30
INFO: v_id: 40
INFO: v_id: 50
INFO: v_id: 50
pro_cursor
------------

(1 row)
omm=# drop procedure pro_cursor;
DROP PROCEDURE


4.清理数据
omm=# drop table t1;
DROP TABLE