学习心得

为了处理SQL语句，存储过程进程分配一段内存区域来保存上下文联系，
游标是指向上下文区域的句柄或指针。
借助游标，存储过程可以控制上下文区域的变化。

通过游标, 建立起相邻行数据之间联系!
CURSOR
MOVE/FETCH FORWARD/BACKWARD

0.进入系统

su - omm
gsql -r

1. 创建游标，且使用select子句指定游标返回的行，分别使用FETCH抓取数据，MOVE重定位游标

建立表test_schema.products, 并插入数据

create schema test_schema;
create TABLE test_schema.products
(
id integer,
name varchar(20),
category varchar(20)
);

INSERT INTO test_schema.products values
(1, 'apple', 'fruit'),
(2, 'orange', 'fruit'),
(3, 'box', 'misc'),
(4, 'eggplant', 'vege'),
(5, 'cabbage', 'vege');

开启transaction, 定义cursor

start transaction;
CURSOR test_cursor FOR SELECT * FROM test_schema.products ORDER BY 1;
select * from pg_cursors;

– 回显

name | statement | is_holdable | is_binary | is_scrollable | creation_time
-------------+---------------+-------------+-----------+---------------+---------------
test_cursor | CURSOR test_cursor FOR SELECT * FROM test_schema.products ORDER BY 1; | f | f | t | 202
1-12-19 23:09:32.473053+08
(1 row)

Fetch 前3行

FETCH FORWARD 3 FROM test_cursor;

– 回显

id | name | category
----+--------+----------
1 | apple | fruit
2 | orange | fruit
3 | box | misc
(3 rows)

Move Backward之后再Fetch 2行看看

MOVE BACKWARD 1 FROM test_cursor;
FETCH FORWARD 2 FROM test_cursor;

– 回显

MOVE 2
id | name | category
----+----------+----------
3 | box | misc
4 | eggplant | vege
(2 rows)

2. 在系统视图pg_cursors中查看游标

select * from pg_cursors;

name | statement | is_holdable | is_binary | is_scrollable |
creation_time
-------------+-----------------------------------------------------------------------+-------------+-----------+---------------+----
---------------------------
test_cursor | CURSOR test_cursor FOR SELECT * FROM test_schema.products ORDER BY 1; | f | f | t | 202
1-12-19 23:19:29.366522+08
(1 row)

end;

– 回显

COMMIT

3. 创建一个使用游标的存储过程

CREATE or REPLACE PROCEDURE test_cursor_func
AS
product_id integer;
product_name varchar(20);
product_category varchar(20);

cursor cur_all is
select id, name, category from test_schema.products order by 1, 2, 3;
begin
if not cur_all%isopen then
open cur_all;
end if;
loop
fetch cur_all into product_id, product_name, product_category;
RAISE INFO 'product_name: %' ,product_name;
exit when cur_all%notfound;
end loop;
if cur_all%isopen then
close cur_all;
end if;
end;
/

– 回显

CREATE PROCEDURE

call test_cursor_func();
drop procedure test_cursor_func;

– 回显

INFO: product_name: apple
INFO: product_name: orange
INFO: product_name: box
INFO: product_name: eggplant
INFO: product_name: cabbage
INFO: product_name: cabbage
test_cursor_func
------------------

(1 row)
DROP PROCEDURE

4. 清理数据

drop table test_schema.products;
drop schema test_schema;