学习目标：
学习openGauss定义游标
课程学习：
为了处理SQL语句，存储过程进程分配一段内存区域来保存上下文联系，游标是指向上下文区域的句柄或指针。借助游标，存储过程可以控制上下文区域的变化。
学习内容部分如下;
课程作业：
1.创建游标，且使用select子句指定游标返回的行，分别使用FETCH抓取数据，MOVE重定位游标
这里使用和课程学习时一样的初始数据：
omm=# create schema tpcds;
CREATE SCHEMA
omm=# CREATE TABLE tpcds.reason
omm-# (
omm(# r_reason_sk integer,
omm(# r_reason_id character(16),
omm(# r_reason_desc character(100)
omm(# );
CREATE TABLE
omm=# INSERT INTO tpcds.reason values(3,'AAAAAAAABAAAAAAA','reason 1'),
omm-# (10,'AAAAAAAABAAAAAAA','reason 2'),(4,'AAAAAAAABAAAAAAA','reason 3'),
omm-# (10,'AAAAAAAABAAAAAAA','reason 4'),(10,'AAAAAAAABAAAAAAA','reason 5'),
omm-# (20,'AAAAAAAACAAAAAAA','reason 6'),(30,'AAAAAAAACAAAAAAA','reason 7');
INSERT 0 7
omm=#
omm=# create table company(name varchar(100), loc varchar(100), no integer);
CREATE TABLE
omm=# insert into company values ('macrosoft', 'usa', 001);
INSERT 0 1
omm=# insert into company values ('oracle', 'usa', 002);
INSERT 0 1
omm=# insert into company values ('backberry', 'canada', 003);
omm=# INSERT 0 1
omm=# start transaction;
START TRANSACTION
omm=# cursor cursor_0 for select * from tpcds.reason order by 1;
DECLARE CURSOR
omm=# fetch forward 2 from cursor_0;
(2 rows)
r_reason_sk | r_reason_id | r_reason_desc
-------------+------------------+---------------------------------------------------------------------------------------------
---------
3 | AAAAAAAABAAAAAAA | reason 1
4 | AAAAAAAABAAAAAAA | reason 3
omm=# move backward 1 from cursor_0;
MOVE 1
2.在系统视图pg_cursors中查看游标
omm=# select * from pg_cursors;
name | statement | is_holdable | is_binary | is_scrollable | cre
ation_time
----------+------------------------------------------------------------+-------------+-----------+---------------+------------
-------------------
cursor_0 | cursor cursor_0 for select * from tpcds.reason order by 1; | f | f | t | 2021-12-23
16:41:52.829561+08
(1 row)
omm=# end;
COMMIT
3.创建一个使用游标的存储过程
这里使用和课程学习时一样的数据：
omm=# create or replace procedure test_cursor_1
omm-# as
omm$# company_name varchar(100);
omm$# company_loc varchar(100);
omm$# company_no integer;
omm$#
omm$# cursor c1_all is --cursor without args
omm$# select name, loc, no from company order by 1, 2, 3;
omm$# begin
omm$# if not c1_all%isopen then
omm$# open c1_all;
omm$# end if;
omm$# loop
omm$# fetch c1_all into company_name, company_loc, company_no;
omm$#
ABORT DECLARE LOCK SELECT
ALTER DELETE FROM MOVE SET
ANALYZE DISCARD NOTIFY SHOW
BEGIN DO PREPARE START
CALL DROP REASSIGN TABLE
CHECKPOINT END REFRESH MATERIALIZED VIEW TRUNCATE
CLOSE EXECUTE REINDEX UNLISTEN
CLUSTER EXPLAIN RELEASE UPDATE
COMMENT FETCH RESET VACUUM
COMMIT GRANT REVOKE VALUES
COPY INSERT ROLLBACK WITH
CREATE LISTEN SAVEPOINT
DEALLOCATE LOAD SECURITY LABEL
omm$# RAISE INFO 'company_name: %' ,company_name;
omm$# exit when c1_all%notfound;
omm$# end loop;
omm$# if c1_all%isopen then
omm$# close c1_all;
omm$# end if;
omm$# end;
omm$# /
CREATE PROCEDURE
4.清理数据
omm=# drop procedure test_cursor_1;
DROP PROCEDURE
omm=# drop schema tpcds cascade;
NOTICE: drop cascades to table tpcds.reason
DROP SCHEMA
omm=# drop table company;
DROP TABLE
