学习笔记
在系统视图pg_cursors中查看可用游标
omm=# select * from pg_cursors;
name | statement | is_holdable | is_binary | is_scrollable | creation_time
---------±----------------------------------------------------------±------------±----------±--------------±------------------------------
cursor1 | CURSOR cursor1 FOR SELECT * FROM tpcds.reason ORDER BY 1; | f | f | t | 2021-12-22 09:52:15.486393+08
(1 row)
VALUES子句，用一个游标读取VALUES子句中的内容
omm=# CURSOR cursor2 FOR VALUES(1,2),(0,3) ORDER BY 1;
DECLARE CURSOR
WITH HOLD游标，在创建它的事务结束后仍可继续使用
omm=# DECLARE cursor1 CURSOR WITH HOLD FOR SELECT * FROM tpcds.reason ORDER BY 1;
DECLARE CURSOR
课后作业
1.创建游标，且使用select子句指定游标返回的行，分别使用FETCH抓取数据，MOVE重定位游标
omm=# create schema my_schema;
CREATE SCHEMA
omm=#
omm=# create table my_schema.product
omm-# (
omm(# id integer,
omm(# name char(30)
omm(# );
CREATE TABLE
omm=#
omm=# insert into my_schema.product values(1,‘apple’),
omm-# (2,‘orange’),(3,‘banana’),(4,‘juice’),(5,‘pear’),
omm-# (6,‘grape’),(7,‘mango’);
INSERT 0 7
omm=#
omm=# select * from my_schema.product;
id | name
----±-------------------------------
1 | apple
2 | orange
3 | banana
4 | juice
5 | pear
6 | grape
7 | mango
(7 rows)
omm=# start transaction;
START TRANSACTION
omm=# cursor cur for select * from my_schema.product order by 1;
DECLARE CURSOR
omm=# fetch forward 2 from cur;
id | name
----±-------------------------------
1 | apple
2 | orange
(2 rows)
omm=# move forward 2 from cur;
MOVE 2
omm=# fetch forward 2 from cur;
id | name
----±-------------------------------
5 | pear
6 | grape
(2 rows)
2.在系统视图pg_cursors中查看游标
omm=# select * from pg_cursors;
name | statement | is_holdable | is_binary | is_scrollable | creation_time
------±-----------------------------------------------------------±------------±----------±--------------±------------------------------
cur | cursor cur for select * from my_schema.product order by 1; | f | f | t | 2021-12-22 10:14:53.452979+08
(1 row)
omm=# close cur;
CLOSE CURSOR
omm=# end;
COMMIT
omm=# select * from pg_cursors;
name | statement | is_holdable | is_binary | is_scrollable | creation_time
------±----------±------------±----------±--------------±--------------
(0 rows)
3.创建一个使用游标的存储过程
omm=# select * from my_schema.product;
id | name
----±-------------------------------
1 | apple
2 | orange
3 | banana
4 | juice
5 | pear
6 | grape
7 | mango
(7 rows)
omm=# create or replace procedure test_cur
omm-# as
omm# product_id integer;
omm# product_name char(30);
omm# cursor test_cur is --cursor without args
omm# select id, name from my_schema.product order by 1, 2;
omm# begin
omm# if not test_cur%isopen then
omm# open test_cur;
omm# end if;
omm# loop
omm# fetch test_cur into product_id, product_name;
omm# raise info 'product_name: %' ,product_name;
omm# exit when test_cur%notfound;
omm# end loop;
omm# if test_cur%isopen then
omm# close test_cur;
omm# end if;
omm# end;
omm# /
CREATE PROCEDURE
omm=# call test_cur();
INFO: product_name: apple
INFO: product_name: orange
INFO: product_name: banana
INFO: product_name: juice
INFO: product_name: pear
INFO: product_name: grape
INFO: product_name: mango
INFO: product_name: mango
test_cur
(1 row)
4.清理数据
omm=# drop procedure test_cur;
DROP PROCEDURE
omm=# drop schema my_schema cascade;
NOTICE: drop cascades to table my_schema.product
DROP SCHEMA
omm=#
