openGauss每日一练第17天 | 定义游标

为了处理SQL语句，存储过程进程分配一段内存区域来保存上下文联系，游标是指向上下文区域的句柄或指针。借助游标，存储过程可以控制上下文区域的变化。
omm=# create schema tpcds;
CREATE SCHEMA
omm=# CREATE TABLE tpcds.reason
omm-# (
omm(# r_reason_sk integer,
omm(# r_reason_id character(16),
omm(# r_reason_desc character(100)
omm(# );
CREATE TABLE
omm=# INSERT INTO tpcds.reason values(3,'AAAAAAAABAAAAAAA','reason 1'),
omm-# (10,'AAAAAAAABAAAAAAA','reason 2'),(4,'AAAAAAAABAAAAAAA','reason 3'),
omm-# (10,'AAAAAAAABAAAAAAA','reason 4'),(10,'AAAAAAAABAAAAAAA','reason 5'),
omm-# (20,'AAAAAAAACAAAAAAA','reason 6'),(30,'AAAAAAAACAAAAAAA','reason 7');
INSERT 0 7
omm=# create table company(name varchar(100), loc varchar(100), no integer);
CREATE TABLE
omm=# insert into company values ('macrosoft', 'usa', 001);
INSERT 0 1
omm=# insert into company values ('oracle', 'usa', 002);
INSERT 0 1
omm=# insert into company values ('backberry', 'canada', 003);
INSERT 0 1
SELECT语句，用一个游标读取一个表
–开始一个事务
omm=# start transaction;
START TRANSACTION
–建立一个名为cursor1的游标
omm=# CURSOR cursor1 FOR SELECT * FROM tpcds.reason ORDER BY 1;
DECLARE CURSOR
–在系统视图pg_cursors中查看可用游标
omm=# select * from pg_cursors;
name | statement | is_holdable | is_binary | is_scrollable
| creation_time
---------+-----------------------------------------------------------+-------------+-----------+--------------
-+-------------------------------
cursor1 | CURSOR cursor1 FOR SELECT * FROM tpcds.reason ORDER BY 1; | f | f | t
| 2021-12-17 10:47:57.424911+08
(1 row)
–抓取头3行到游标cursor1里
omm=# FETCH FORWARD 3 FROM cursor1;
r_reason_sk | r_reason_id | r_reason_desc

-------------+------------------+-----------------------------------------------------------------------------
-------------------------
3 | AAAAAAAABAAAAAAA | reason 1

4 | AAAAAAAABAAAAAAA | reason 3

10 | AAAAAAAABAAAAAAA | reason 5

(3 rows)
–从当前关联位置开始，抓取前面的1行
omm=# FETCH BACKWARD 1 FROM cursor1;
r_reason_sk | r_reason_id | r_reason_desc

-------------+------------------+-----------------------------------------------------------------------------
-------------------------
4 | AAAAAAAABAAAAAAA | reason 3

(1 row)
–关闭游标并提交事务
omm=# CLOSE cursor1;
CLOSE CURSOR
omm=# select * from pg_cursors;
omm=# name | statement | is_holdable | is_binary | is_scrollable | creation_time
------+-----------+-------------+-----------+---------------+---------------
(0 rows)

omm=#
end;
COMMIT
omm=#
VALUES子句，用一个游标读取VALUES子句中的内容
–建立一个名为cursor2的游标
omm=# start transaction;
START TRANSACTION
omm=# CURSOR cursor2 FOR VALUES(1,2),(0,3) ORDER BY 1;
DECLARE CURSOR
omm=# FETCH FORWARD 2 FROM cursor2;
column1 | column2
---------+---------
0 | 3
1 | 2
(2 rows)

omm=# CLOSE cursor2;
CLOSE CURSOR
omm=# end;
COMMIT
omm=#
WITH HOLD游标的使用
–声明该游标在创建它的事务结束后仍可继续使用
omm=# DECLARE cursor1 CURSOR WITH HOLD FOR SELECT * FROM tpcds.reason ORDER BY 1;
DECLARE CURSOR
–抓取接下来的3行
omm=# FETCH FORWARD 3 FROM cursor1;
r_reason_sk | r_reason_id | r_reason_desc

-------------+------------------+-----------------------------------------------------------------------------
-------------------------
3 | AAAAAAAABAAAAAAA | reason 1

4 | AAAAAAAABAAAAAAA | reason 3

10 | AAAAAAAABAAAAAAA | reason 5

(3 rows)

omm=# CLOSE cursor1;
CLOSE CURSOR
移动游标
omm=# START TRANSACTION;
START TRANSACTION
omm=# CURSOR cursor1 FOR SELECT * FROM tpcds.reason ORDER BY 1;
DECLARE CURSOR
–忽略游标cursor1的前3行
omm=# MOVE FORWARD 3 FROM cursor1;
MOVE 3
–抓取游标cursor1的前4行
omm=# FETCH 4 FROM cursor1;
r_reason_sk | r_reason_id | r_reason_desc

-------------+------------------+-----------------------------------------------------------------------------
-------------------------
10 | AAAAAAAABAAAAAAA | reason 4

10 | AAAAAAAABAAAAAAA | reason 2

30 | AAAAAAAACAAAAAAA | reason 7

(4 rows)

20 | AAAAAAAACAAAAAAA | reason 6

omm=# CLOSE cursor1;
CLOSE CURSOR
omm=# end;
COMMIT
存储过程中使用游标
create or replace procedure test_cursor_1
as
company_name varchar(100);
company_loc varchar(100);
company_no integer;

cursor c1_all is --cursor without args
select name, loc, no from company order by 1, 2, 3;
begin
if not c1_all%isopen then
open c1_all;
end if;
loop
fetch c1_all into company_name, company_loc, company_no;
RAISE INFO 'company_name: %' ,company_name;
exit when c1_all%notfound;
end loop;
if c1_all%isopen then
close c1_all;
end if;
end;
/

omm=# call test_cursor_1();
INFO: company_name: backberry
INFO: company_name: macrosoft
INFO: company_name: oracle
INFO: company_name: oracle
test_cursor_1
---------------

(1 row)

omm=# drop procedure test_cursor_1;
DROP PROCEDURE

omm=# start transaction;
START TRANSACTION
omm=# CURSOR cur_olab1 FOR SELECT * FROM olab.t1 where c1 <5;
DECLARE CURSOR
omm=# select * from pg_cursors;
name | statement | is_holdable | is_binary | is_scrollable
| creation_time
-----------+---------------------------------------------------------+-------------+-----------+--------------
-+-------------------------------
cur_olab1 | CURSOR cur_olab1 FOR SELECT * FROM olab.t1 where c1 <5; | f | f | t
| 2021-12-17 11:01:08.398556+08
(1 row)

omm=# FETCH FORWARD 2 FROM cur_olab1;
c1 | c2
----+----
1 | a
2 | b
(2 rows)

omm=# MOVE FORWARD 3 FROM cur_olab1;
MOVE 2
omm=# FETCH 1 FROM cur_olab1;
c1 | c2
----+----
(0 rows)

omm=# end;
omm=# COMMIT

取出的cursor内的值在减少，注意move与取出一样减少了cursor内的值
omm=# start transaction;
START TRANSACTION
omm=# CURSOR cur_olab1 FOR SELECT * FROM olab.t1 where c1 <5;
omm=# DECLARE CURSOR

omm=# fetch forward 1 from cur_olab1;
c1 | c2
----+----
1 | a
(1 row)

omm=# move forward 2 from cur_olab1;
MOVE 2
omm=# fetch 1 from cur_olab1;
c1 | c2
----+----
4 | d
(1 row)

omm=# end;

raise info只能有一个值，否则ERROR: too many parameters specified for RAISE
create or replace procedure olab.proce_cur1
as
proc1 int;
proc2 varchar(10);
cursor cur2 is
select c1, c2 from olab.t1 where c2 in ('a','b','c');
begin
if not cur2%isopen then
open cur2;
end if;
loop
fetch cur2 into proc1, proc2;
RAISE INFO 'cursortest: %' ,proc1;
exit when cur2%notfound;
end loop;
if cur2%isopen then
close cur2;
end if;
end;
/
CREATE PROCEDURE
omm=# call olab.proce_cur1();
INFO: cursortest: 2
INFO: cursortest: 3
INFO: cursortest: 3
INFO: cursortest: 1
proce_cur1
------------

(1 row)

omm=# drop procedure olab.proce_cur1;
DROP PROCEDURE
omm=# drop table olab.t1;
DROP TABLE
omm=# drop schema olab;
DROP SCHEMA