openGauss每日一练第17天 | 学习心得体会【少安】

学习目标

学习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重定位游标
2.在系统视图pg_cursors中查看游标

-- pre-work
create table t1 (id int);
insert into t1 values (1),(2),(3);

-- 1.
begin;
cursor c1 for select * from t1;
fetch forward 2 from c1;
move forward 2 from c1;

-- 2.
select * from pg_cursors;

-- rollback;

3.创建一个使用游标的存储过程
4.清理数据

-- 3.
CREATE OR
REPLACE PROCEDURE pro_cursor AS
pid INT; CURSOR cursor_1 IS
SELECT id
FROM t1; BEGIN
if NOT cursor_1%isopen THEN OPEN cursor_1; END if; LOOP FETCH cursor_1 INTO pid;
        RAISE INFO 'pid: %', pid; EXIT WHEN cursor_1%notfound; END LOOP;
if cursor_1%isopen THEN CLOSE cursor_1; END if; END;
/
call pro_cursor();

-- 4.
drop procedure pro_cursor;
drop table t1;

学习心得

声明游标需要在事务中进行

ERROR:  DECLARE CURSOR can only be used in transaction blocks

参考文档

openGauss每日一练 | by 少安 | 汇总

openGauss产品文档 pg_cursors

松鼠镇楼，按时打卡