作业：
1.创建游标，且使用select子句指定游标返回的行，分别使用FETCH抓取数据，MOVE重定位游标
omm=# create table student (id int,name char(10),age char(2));
CREATE TABLE
omm=# insert into student values (1,'titi','12'),(2,'pipi','15'),(3,'kiki','17'),(4,'lolo','18'),(5,'bibi','19');
INSERT 0 5
omm=# select * from student;
id | name | age
----+------------+-----
1 | titi | 12
2 | pipi | 15
3 | kiki | 17
4 | lolo | 18
5 | bibi | 19
(5 rows)
omm=# start transaction;
omm=# START TRANSACTION
omm=# cursor cursor1 for select * from student order by 1;
DECLARE CURSOR
omm=# select * from pg_cursors;
name | statement | is_holdable | is_binary | is_scrollable | creation_t
ime
---------+------------------------------------------------------+-------------+-----------+---------------+-------------------
------------
cursor1 | cursor cursor1 for select * from student order by 1; | f | f | t | 2021-12-17 14:45:3
0.680166+08
(1 row)
omm=# fetch forward 3 from cursor1;
id | name | age
----+------------+-----
1 | titi | 12
2 | pipi | 15
3 | kiki | 17
(3 rows)
omm=# FETCH BACKWARD 1 FROM cursor1;
id | name | age
----+------------+-----
2 | pipi | 15
(1 row)
omm=# CLOSE cursor1;
CLOSE CURSOR
omm=# select * from pg_cursors;
name | statement | is_holdable | is_binary | is_scrollable | creation_time
------+-----------+-------------+-----------+---------------+---------------
(0 rows)
omm=# end;
omm=# COMMIT
omm=# start transaction;
START TRANSACTION
omm=# cursor cursor1 for select * from student order by 1;
DECLARE CURSOR
omm=# select * from student order by 1;
id | name | age
----+------------+-----
1 | titi | 12
2 | pipi | 15
3 | kiki | 17
4 | lolo | 18
5 | bibi | 19
(5 rows)
omm=# move forward 2 from cursor1;
MOVE 2
omm=# fetch 3 from cursor1;
id | name | age
----+------------+-----
3 | kiki | 17
4 | lolo | 18
5 | bibi | 19
(3 rows)
omm=# close cursor1;
CLOSE CURSOR
omm=# end;
COMMIT
2.在系统视图pg_cursors中查看游标
omm=# select * from pg_cursors;
name | statement | is_holdable | is_binary | is_scrollable | creation_t
omm=# start transaction;
omm=# START TRANSACTION
omm=# cursor cursor1 for select * from student order by 1;
DECLARE CURSOR
omm=# select * from pg_cursors;
name | statement | is_holdable | is_binary | is_scrollable | creation_t
ime
---------+------------------------------------------------------+-------------+-----------+---------------+-------------------
------------
cursor1 | cursor cursor1 for select * from student order by 1; | f | f | t | 2021-12-17 14:45:3
0.680166+08
(1 row)
omm=# end;
COMMIT
3.创建一个使用游标的存储过程
omm=# create or replace procedure test_cursor_1
omm-# as
omm$# st_id integer;
omm$# st_name char(10);
st_age char(2);
omm$#
omm$# omm$# cursor c1_all is --cursor without args
omm$# select id ,name ,age from student 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 st_id, st_name, st_age;
omm$# RAISE INFO 'st_id: %' ,st_id;
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
omm=# call test_cursor_1();
INFO: st_id: 1
INFO: st_id: 2
INFO: st_id: 3
INFO: st_id: 4
INFO: st_id: 5
INFO: st_id: 5
test_cursor_1
---------------
(1 row)
omm=# drop procedure test_cursor_1;
DROP PROCEDURE
4.清理数据
omm=# drop table student;
omm=# DROP TABLE
