实训课程更直观的体现出了游标的作用和用法，作业打卡来啦~
#第一次进入等待15秒
#数据库启动中...
su - omm
gsql -r
1.创建游标，且使用select子句指定游标返回的行，分别使用FETCH抓取数据，MOVE重定位游标
create schema yc;
create table yc.student(id int,name char(20));
insert into yc.student values(1,'yc'),(2,'hedy'),(3,'longon'),(4,'lily');
start transaction;
CURSOR yc_cur for select * from yc.student order by id;
FETCH 2 FROM yc_cur;
MOVE FORWARD 1 FROM yc_cur;
FETCH 2 FROM yc_cur;
CLOSE yc_cur;
END;
2.在系统视图pg_cursors中查看游标
start transaction;
CURSOR yc_cur for select * from yc.student order by id;
SELECT * FROM PG_CURSORS;
CLOSE yc_cur;
END;
3.创建一个使用游标的存储过程
create or replace procedure test_yc_cur
as
st_id int;
st_name varchar(20);
cursor c1_all is --cursor without args
select id,name from yc.student order by id,name;
begin
if not c1_all%isopen then
open c1_all;
end if;
loop
fetch c1_all into st_id,st_name;
RAISE INFO 'name: %',st_name;
exit when c1_all%notfound;
end loop;
if c1_all%isopen then
close c1_all;
end if;
end;
/
call test_yc_cur();
drop procedure test_yc_cur;
4.清理数据
drop schema yc cascade;
