学习目标
学习openGauss定义游标
为了处理SQL语句，存储过程进程分配一段内存区域来保存上下文联系，游标是指向上下文区域的句柄或指针。借助游标，存储过程可以控制上下文区域的变化;
课程作业
1.创建游标，且使用select子句指定游标返回的行，分别使用FETCH抓取数据，MOVE重定位游标
SQL:
create schema tpcds_1;
CREATE TABLE tpcds_1.table ( r_reason_sk integer, r_reason_id character(16), r_reason_desc character(100) );
INSERT INTO tpcds_1.table 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');
start transaction;
CURSOR cursor1 FOR SELECT * FROM tpcds_1.table ORDER BY 1;
select * from pg_cursors;
FETCH FORWARD 3 FROM cursor1;
MOVE FORWARD 3 FROM cursor1;
FETCH 4 FROM cursor1;
CLOSE cursor1;
end;
截图展示：
2.在系统视图pg_cursors中查看游标
SQL：
select * from pg_cursors;
截图展示：
3.创建一个使用游标的存储过程
SQL：
create or replace procedure test_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__1();
drop procedure test_cursor_1;
截图展示：
4.清理数据
SQL：
drop procedure test_1;
drop table company;
截图展示：
