1.创建游标，且使用select子句指定游标返回的行，分别使用FETCH抓取数据，MOVE重定位游标
create table test(a int,b varchar(10));
insert into test values(1,'zhangsan1');
insert into test values(2,'zhangsan2');
insert into test values(3,'zhangsan3');
insert into test values(4,'zhangsan4'),(5,'zhangsan5'),(6,'zhangsan6');
start transaction;
CURSOR cursor1 FOR SELECT * FROM test ORDER BY 1;
select * from pg_cursors;
FETCH FORWARD 3 FROM cursor1;
FETCH BACKWARD 1 FROM cursor1;
CLOSE cursor1;
select * from pg_cursors;
end;
START TRANSACTION;
CURSOR cursor1 FOR SELECT * FROM test ORDER BY 1;
MOVE FORWARD 3 FROM cursor1;
FETCH 4 FROM cursor1;
CLOSE cursor1;
end;
2.在系统视图pg_cursors中查看游标
select * from pg_cursors;
3.创建一个使用游标的存储过程
create or replace procedure test_cursor
as
aa integer;
bb varchar(10);
cursor test_cur is SELECT a,b FROM test order by 1;
begin
if not test_cur%isopen then
open test_cur;
end if;
loop
fetch test_cur into aa,bb;
RAISE INFO 'id num: %' ,aa;
exit when test_cur%notfound;
end loop;
if test_cur%isopen then
close test_cur;
end if;
end;
/
call test_cursor();
drop procedure test_cursor;
4.清理数据
drop table test;
