openGauss每日一练第 17 天 |定义游标及课后作业

openGauss 每日一练第 17 天学习打卡，学习 openGauss 定义游标基本操作！

学习目标

学习 openGauss 定义游标

前面每日一练链接：

openGauss每日一练第 1 天 | 数据库和表的基本操作（一）
openGauss每日一练第 2 天 | 数据库和表的基本操作（二）
openGauss每日一练第 3 天 | 前三课作业实操练习
openGauss每日一练第 4 天 | 角色管理及课后作业
openGauss每日一练第 5 天 | 用户管理及课后作业
openGauss每日一练第 6 天 | 模式管理及课后作业
openGauss每日一练第 7 天 | 表空间管理及课后作业
openGauss每日一练第 8 天 | 分区表管理及课后作业
openGauss每日一练第 9 天 | 普通表索引管理及课后作业
openGauss每日一练第 10 天 | 分区表索引管理及课后作业
openGauss每日一练第 11 天 | 视图管理及课后作业
openGauss每日一练第 12 天 | 自定义数据类型管理及课后作业
openGauss每日一练第 13 天 | 数据导入操作及课后作业
openGauss每日一练第 14 天 | 数据导出操作及课后作业
openGauss每日一练第 15 天 | 定义存储过程和函数及课后作业
openGauss每日一练第 16 天 | 事务控制及课后作业

课程学习

学习 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重定位游标

create table company(name varchar(100), loc varchar(100), num integer);
insert into company values ('macrosoft',    'usa',          001);
insert into company values ('oracle',       'usa',          002);
insert into company values ('backberry',    'canada',       003);

start transaction;
CURSOR comp_cursor FOR SELECT * FROM company where num<=3;

FETCH FORWARD 2 FROM comp_cursor;

MOVE FORWARD 2 FROM comp_cursor;
FETCH 3 FROM comp_cursor;
CLOSE comp_cursor;
select * from pg_cursors;
end;

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

select * from pg_cursors;

jiekexu=#  select * from pg_cursors;
    name     |                         statement                          | is_holdable | is_binary | is_scrollable |         creation_time         
-------------+------------------------------------------------------------+-------------+-----------+---------------+-------------------------------
 comp_cursor | CURSOR comp_cursor FOR SELECT * FROM company where num<=3; | f           | f         | t             | 2021-12-24 12:41:20.726963+08
(1 row)

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

create or replace procedure jiekexu_test_cursor_1
as
    company_name    varchar(100);
    company_loc varchar(100);
    company_num  integer;

    cursor c1_all is --cursor without args
        select name, loc, num 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_num;
		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 jiekexu_test_cursor_1();

4.清理数据

drop procedure jiekexu_test_cursor_1;

欧耶，第十七课定义游标过程实操及课后作业练习题完成啦！第十八课见！！！