openGauss每日一练第17天 | 学习心得体会
补之前的作业。今天(2021年12月26日)朋友推荐我看这个教程。
一下午都在做这个作业，每行代码都通过墨天轮的平台验证过了。
做完作业有个考试，91分通过。
总体感觉这个课程还是很不错的，一共21天，下面是第17天的作业内容。
17.1 openGauss定义游标
课程作业
1.创建游标，且使用select子句指定游标返回的行，分别使用FETCH抓取数据，MOVE重定位游标
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);
start transaction;
CURSOR cursor1 FOR SELECT * FROM tpcds.reason ORDER BY 1;
select * from pg_cursors;
FETCH FORWARD 3 FROM cursor1;
MOVE FORWARD 3 FROM cursor1
=========================
2.在系统视图pg_cursors中查看游标
end;
select * from pg_cursors;
=========================
3.创建一个使用游标的存储过程
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;
=========================
4.清理数据
drop schema tpcds cascade;
drop table company;
=========================
P.S.
从第十七天开始，难度继续增加！！
