openGauss每日一练第17天 | 定义游标 from seven
学习目标
学习openGauss定义游标
为了处理SQL语句，存储过程进程分配一段内存区域来保存上下文联系，游标是指向上下文区域的句柄或指针。借助游标，存储过程可以控制上下文区域的变化。
课程学习
连接数据库
#第一次进入等待15秒#数据库启动中...
su - omm
gsql -r
1.准备数据
create schema tpcds;
CREATE TABLE tpcds.reason
(
r_reason_sk integer,
r_reason_id character(16),
r_reason_desc character(100)
);
INSERT INTO tpcds.reason values(3,'AAAAAAAABAAAAAAA','reason 1'),
(10,'AAAAAAAABAAAAAAA','reason 2'),(4,'AAAAAAAABAAAAAAA','reason 3'),
(10,'AAAAAAAABAAAAAAA','reason 4'),(10,'AAAAAAAABAAAAAAA','reason 5'),
(20,'AAAAAAAACAAAAAAA','reason 6'),(30,'AAAAAAAACAAAAAAA','reason 7');
create table company(name varchar(100), loc varchar(100), no integer);
insert into company values ('macrosoft', 'usa', 001);
insert into company values ('oracle', 'usa', 002);
insert into company values ('backberry', 'canada', 003);
2. SELECT语句，用一个游标读取一个表
–开始一个事务
start transaction;
–建立一个名为cursor1的游标。
CURSOR cursor1 FOR SELECT * FROM tpcds.reason ORDER BY 1;
–在系统视图pg_cursors中查看可用游标
select * from pg_cursors;
–抓取头3行到游标cursor1里
FETCH FORWARD 3 FROM cursor1;
–从当前关联位置开始，抓取前面的1行
FETCH BACKWARD 1 FROM cursor1;
–关闭游标并提交事务
CLOSE cursor1;
select * from pg_cursors;
end;
3. VALUES子句，用一个游标读取VALUES子句中的内容
–建立一个名为cursor2的游标
start transaction;
CURSOR cursor2 FOR VALUES(1,2),(0,3) ORDER BY 1;
FETCH FORWARD 2 FROM cursor2;
CLOSE cursor2;
end;
4. WITH HOLD游标的使用
–声明该游标在创建它的事务结束后仍可继续使用
DECLARE cursor1 CURSOR WITH HOLD FOR SELECT * FROM tpcds.reason ORDER BY 1;
–抓取接下来的3行
FETCH FORWARD 3 FROM cursor1;
CLOSE cursor1;
5.移动游标
START TRANSACTION;
CURSOR cursor1 FOR SELECT * FROM tpcds.reason ORDER BY 1;
–忽略游标cursor1的前3行
MOVE FORWARD 3 FROM cursor1;
–抓取游标cursor1的前4行
FETCH 4 FROM cursor1;
CLOSE cursor1;
end;
6.存储过程中使用游标
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;
/
call test_cursor_1();
drop procedure test_cursor_1;
7.清理数据
drop schema tpcds cascade;
drop table company;
课程作业
1. 创建游标，且使用select子句指定游标返回的行，分别使用FETCH抓取数据，MOVE重定位游标
omm=# create table t1(id int,name char(30));
CREATE TABLE
omm=# \d t1
Table "public.t1"
Column | Type | Modifiers
--------+---------------+-----------
id | integer |
name | character(30) |
omm=# \d+ t1
Table "public.t1"
Column | Type | Modifiers | Storage | Stats target | Description
--------+---------------+-----------+----------+--------------+-------------
id | integer | | plain | |
name | character(30) | | extended | |
Has OIDs: no
Options: orientation=row, compression=no
omm=# insert into t1 values(101,'mary'),(102,'tom'),(103,'jake'),(104,'lisi'),(105,'wangwu'),(106,'zhaoliu'),(107,'seven');
INSERT 0 7
omm=# select * from t1;
id | name
-----+--------------------------------
101 | mary
102 | tom
103 | jake
104 | lisi
105 | wangwu
106 | zhaoliu
107 | seven
(7 rows)
omm=# start transaction;
START TRANSACTION
omm=# cursor cursor1 for select * from t1 limit 7;
DECLARE CURSOR
omm=# fetch 3 from cursor1;
103 | jake
(3 rows)
omm=# id | name
-----+--------------------------------
101 | mary
102 | tom
omm=# fetch 2 from cursor1;
id | name
-----+--------------------------------
104 | lisi
105 | wangwu
(2 rows)
omm=# move forward 1 from cursor1;
MOVE 1
omm=# fetch 1 from cursor1;
id | name
-----+--------------------------------
107 | seven
(1 row)
omm=# close cursor1;
CLOSE CURSOR
omm=# end;
COMMIT
2. 在系统视图pg_cursors中查看游标
omm=# start transaction;
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 14:18:22.99545+08
(1 row)
omm=# close c1;
CLOSE CURSOR
omm=# end;
COMMIT
3. 创建一个使用游标的存储过程
omm=# create or replace procedure test_c1
omm-# as
omm$# t1_id int;
omm$# t1_name char(30);
omm$# cursor c1 is select * from t1;
omm$# begin
omm$# if not c1%isopen then
omm$# open c1;
omm$# end if;
omm$# loop
omm$# fetch c1 into t1_id,t1_name;
omm$# raise info 't1_id: %',t1_id;
omm$# raise info 't1_name: %', t1_name;
omm$# exit when c1%notfound;
omm$# end loop;omm$#
close c1;omm$#
end;omm$#
/
CREATE PROCEDURE
omm=# call test_c1();
INFO: t1_id: 101
INFO: t1_name: mary
INFO: t1_id: 104
INFO: t1_name: lisi
INFO: t1_id: 102
INFO: t1_name: tom
INFO: t1_id: 103
INFO: t1_name: jake
INFO: t1_id: 105
INFO: t1_name: wangwu
INFO: t1_id: 106
omm=# INFO: t1_name: zhaoliu
INFO: t1_id: 107
INFO: t1_name: seven
INFO: t1_id: 107
INFO: t1_name: seven
test_c1
---------
(1 row)
4. 清理数据
omm=# drop table t1;
DROP TABLE
omm=# drop procedure test_c1;
DROP PROCEDURE
