opengauss学习的第17天

#opengauss的第17天

1.创建游标，且使用select子句指定游标返回的行，分别使用FETCH抓取数据，MOVE重定位游标
omm=# create table t2(id bigint,name char(40));
CREATE TABLE
omm=# start transaction;
START TRANSACTION
omm=#
omm=# CURSOR cursor1 FOR SELECT * FROM t2 ORDER BY 1;
DECLARE CURSOR
omm=# select * from pg_cursors;
name | statement | is_holdable | is_binary | is_scrollable | crea
tion_time
---------±------------------------------------------------±------------±----------±--------------±------------

cursor1 | CURSOR cursor1 FOR SELECT * FROM t2 ORDER BY 1; | f | f | t | 2021-12-17 1
0:24:26.293055+08
(1 row)

omm=#
omm=# FETCH FORWARD 3 FROM cursor1;
id | name
----±-----
(0 rows)

omm=# MOVE FORWARD 3 FROM cursor1;
MOVE 0
omm=#
CLOSE cursor1;
omm=# CLOSE CURSOR
omm=# select * from pg_cursors;
(0 rows)

omm=# end; name | statement | is_holdable | is_binary | is_scrollable | creation_time
------±----------±------------±----------±--------------±--------------

COMMIT

2.在系统视图pg_cursors中查看游标
omm=# CURSOR cursor1 FOR SELECT * FROM t2 ORDER BY 1;
DECLARE CURSOR
omm=# select * from pg_cursors;
name | statement | is_holdable | is_binary | is_scrollable | crea
tion_time
---------±------------------------------------------------±------------±----------±--------------±------------

cursor1 | CURSOR cursor1 FOR SELECT * FROM t2 ORDER BY 1; | f | f | t | 2021-12-17 1
0:24:26.293055+08

3.创建一个使用游标的存储过程
omm=# create table company(name varchar(100), loc varchar(100), no integer);
CREATE TABLE
omm=# insert into company values (‘macrosoft’, ‘usa’, 001);
INSERT 0 1
omm=# insert into company values (‘oracle’, ‘usa’, 002);
INSERT 0 1
omm=# insert into company values (‘backberry’, ‘canada’, 003);
INSERT 0 1
omm-# as
omm# create or replace procedure test_cursor_1 company_name varchar(100); omm# company_loc varchar(100);
omm# company_no integer; omm# cursor c1_all is --cursor without args
omm# select name, loc, no from company 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 company_name, company_loc, company_no; omm# RAISE INFO ‘company_name: %’ ,company_name;
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: company_name: backberry
INFO: company_name: macrosoft
INFO: company_name: oracle
INFO: company_name: oracle
test_cursor_1

(1 row)

omm=# drop procedure test_cursor_1;
DROP PROCEDURE

4.清理数据
omm=# drop table company;
DROP TABLE
omm=# drop table t2;
DROP TABLE