心得：
游标是SQL 的一种数据访问机制。可以将游标简单的看成是查询的结果集的一个指针，可以根据需要在结果集上面来回滚动，浏览需要的数据。
课程作业
1.创建游标，且使用select子句指定游标返回的行，分别使用FETCH抓取数据，MOVE重定位游标
root@modb:~# su - omm
omm@modb:~$ gsql -r
gsql ((openGauss 2.0.0 build 78689da9) compiled at 2021-03-31 21:03:52 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
omm=#create schema yangkai;
omm=#create table yangkai.yb_table
(
id int,
name varchar(20),
age CHAR(4)
);
omm=# insert into yangkai.yb_table values (1, 'yangkai1',10), (2, 'yangkai2',15), (3, 'yangkai3',20);
INSERT 0 3
omm=# select * from yangkai.yb_table;
id | name | age
------+----------+------
1 | yangkai1 | 10
2 | yangkai2 | 15
3 | yangkai3 | 20
(3 rows)
omm=#
omm=# start transaction;
START TRANSACTION
omm=# CURSOR cursor1 FOR select * from yangkai.yb_table order by 1;
DECLARE CURSOR
omm=# FETCH FORWARD 3 FROM cursor1;
id | name | age
------+----------+------
1 | yangkai1 | 10
2 | yangkai2 | 15
3 | yangkai3 | 20
(3 rows)
omm=# FETCH BACKWARD 1 FROM cursor1;
id | name | age
------+----------+------
2 | yangkai2 | 15
(1 row)
omm=# CLOSE cursor1;
omm=#end;
2.在系统视图pg_cursors中查看游标
omm=# select * from pg_cursors;
name | statement | is_holdable | is_binary | is_scrollable | creation_time
---------+---------------------------------------------------------------+-------------+-----------+---------------+-------------------------------
cursor1 | CURSOR cursor1 FOR select * from yangkai.yb_table order by 1; | f | f | t | 2021-12-20 22:17:34.684501+08
(1 row)
3.创建一个使用游标的存储过程
create or replace procedure yangkai.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 yangkai cascade;
