1.准备数据
--0、使用gsql登录openGauss
root@modb:~# su - omm
omm@modb:~$ gsql -r
gsql ((openGauss 2.0.0 build 78689da9) compiled at 2021-03-31 21:03:52 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
omm=#
--1、创建schema
omm=# create schema tpcds;
CREATE SCHEMA
--2、创建表tpcds.reason
omm=# CREATE TABLE tpcds.reason
omm-# (
omm(# r_reason_sk integer,
omm(# r_reason_id character(16),
omm(# r_reason_desc character(100)
omm(# );
CREATE TABLE
--3、插入数据
omm=# INSERT INTO tpcds.reason values(3,'AAAAAAAABAAAAAAA','reason 1'),
omm-# omm-# (10,'AAAAAAAABAAAAAAA','reason 2'),(4,'AAAAAAAABAAAAAAA','reason 3'),
(10,'AAAAAAAABAAAAAAA','reason 4'),(10,'AAAAAAAABAAAAAAA','reason 5'),
omm-# (20,'AAAAAAAACAAAAAAA','reason 6'),(30,'AAAAAAAACAAAAAAA','reason 7');
INSERT 0 7
omm=#
--4、创建表company
omm=# create table company(name varchar(100), loc varchar(100), no integer);
CREATE TABLE
--5、插入数据
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
2. SELECT语句，用一个游标读取一个表
--1、开始一个事务
omm=# start transaction;
START TRANSACTION
--2、建立一个名为cursor1的游标
omm=# CURSOR cursor1 FOR SELECT * FROM tpcds.reason ORDER BY 1;
DECLARE CURSOR
--3、在系统视图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-18 21:42:08.637189+08
(1 row
--4、抓取头3行到游标cursor1里
omm=# FETCH FORWARD 3 FROM cursor1;
r_reason_sk | r_reason_id | r_reason_desc
-------------+------------------+------------------------------------------------------------------------------------------------------
omm=# 3 | AAAAAAAABAAAAAAA | reason 1
4 | AAAAAAAABAAAAAAA | reason 3
10 | AAAAAAAABAAAAAAA | reason 5
(3 rows)
--5、从当前关联位置开始，抓取前面的1行
omm=# FETCH BACKWARD 1 FROM cursor1;
r_reason_sk | r_reason_id | r_reason_desc
-------------+------------------+------------------------------------------------------------------------------------------------------
4 | AAAAAAAABAAAAAAA | reason 3
(1 row)
--6、关闭游标并提交事务
omm=# CLOSE cursor1;
CLOSE CURSOR
omm=# select * from pg_cursors;
name | statement | is_holdable | is_binary | is_scrollable | creation_time
------+-----------+-------------+-----------+---------------+---------------
(0 rows)
omm=# end;
COMMIT
3. VALUES子句，用一个游标读取VALUES子句中的内容
--建立一个名为cursor2的游标
omm=# start transaction;
START TRANSACTION
omm=# CURSOR cursor2 FOR VALUES(1,2),(0,3) ORDER BY 1;
DECLARE CURSOR
omm=# FETCH FORWARD 2 FROM cursor2;
column1 | column2
---------+---------
0 | 3
1 | 2
(2 rows)
omm=# CLOSE cursor2;
CLOSE CURSOR
omm=# end;
COMMIT
4. WITH HOLD游标的使用
--1、声明该游标在创建它的事务结束后仍可继续使用
omm=# DECLARE cursor1 CURSOR WITH HOLD FOR SELECT * FROM tpcds.reason ORDER BY 1;
DECLARE CURSOR
--2、抓取接下来的3行
omm=# FETCH FORWARD 3 FROM cursor1;
r_reason_sk | r_reason_id | r_reason_desc
-------------+------------------+------------------------------------------------------------------------------------------------------
3 | AAAAAAAABAAAAAAA | reason 1
4 | AAAAAAAABAAAAAAA | reason 3
10 | AAAAAAAABAAAAAAA | reason 5
(3 rows)
omm=# CLOSE cursor1;
CLOSE CURSOR
5.移动游标
omm=# START TRANSACTION;
START TRANSACTION
omm=# CURSOR cursor1 FOR SELECT * FROM tpcds.reason ORDER BY 1;
DECLARE CURSOR
--忽略游标cursor1的前3行
omm=# MOVE FORWARD 3 FROM cursor1;
MOVE 3
--抓取游标cursor1的前4行
omm=# FETCH 4 FROM cursor1;
r_reason_sk | r_reason_id | r_reason_desc
-------------+------------------+------------------------------------------------------------------------------------------------------
10 | AAAAAAAABAAAAAAA | reason 4
10 | AAAAAAAABAAAAAAA | reason 2
20 | AAAAAAAACAAAAAAA | reason 6
30 | AAAAAAAACAAAAAAA | reason 7
(4 rows)
omm=# CLOSE cursor1;
CLOSE CURSOR
omm=# end;
COMMIT
6.存储过程中使用游标
omm=# create or replace procedure test_cursor_1
as
company_name varchar(100);
company_loc varchar(100);
company_no integer;
cursor c1_all is --cursor without args
select name, loc, no from company order by 1, 2, 3;
begin
if not c1_all%isopen then
open c1_all;
end if;
loop
fetch c1_all into company_name, company_loc, company_no;
RAISE INFO 'company_name: %' ,company_name;
exit when c1_all%notfound;
end loop;
if c1_all%isopen then
close c1_all;
end if;
end;
/
CREATE PROCEDURE
omm=#
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
7.清理数据
omm=# drop schema tpcds cascade;
NOTICE: drop cascades to table tpcds.reason
DROP SCHEMA
omm=# drop table company;
DROP TABLE
练习：
1.创建游标，且使用select子句指定游标返回的行，分别使用FETCH抓取数据，MOVE重定位游标
omm=# create schema schema1;
CREATE SCHEMA
omm=# CREATE TABLE schema1.tb_1
omm-# omm(# id integer,
(
omm(# name character(50),
omm(# xuexiao character(100)
omm(# );
CREATE TABLE
INSERT INTO schema1.tb_1 values(10,'xiaoming','test1'),
(20,'xiaohong','test2'),(30,'xiaohei','test3'),
(40,'xiaolv','test4'),(50,'xiaolan','test5'),
(60,'xiaozi','test6'),(70,'xiaofen','test7');
start transaction;
CURSOR cursor1 FOR SELECT name FROM schema1.tb_1 ORDER BY 1;
--抓取头4行到游标cursor1里
omm=# FETCH FORWARD 4 FROM cursor1;
name
----------------------------------------------------
xiaofen
xiaohei
xiaohong
xiaolan
(4 rows)
omm=# MOVE FORWARD 3 FROM cursor1;
MOVE 3
2.在系统视图pg_cursors中查看游标
omm=# select * from pg_cursors;
name | statement | is_holdable | is_binary | is_scrollable | creation_time
---------+--------------------------------------------------------------+-------------+-----------+---------------+-----------------------------
--
cursor1 | CURSOR cursor1 FOR SELECT name FROM schema1.tb_1 ORDER BY 1; | f | f | t | 2021-12-18 23:06:05.992702+0
8
(1 row)
3.创建一个使用游标的存储过程
omm=# create or replace procedure test_cur1
omm-# as
omm$# company_name varchar(100);
omm$# company_loc varchar(100);
omm$# company_no integer;
omm$#
omm$# cursor c1_all is --cursor without args
omm$# begin
omm$# select name, loc, no from company order by 1, 2, 3;
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$#
ABORT COPY EXPLAIN REASSIGN SETALTER CREATE FETCH REFRESH MATERIALIZED VIEW SHOW
ANALYZE DEALLOCATE GRANT REINDEX START
BEGIN DECLARE INSERT RELEASE TABLE
CALL DELETE FROM LISTEN RESET TRUNCATE
CHECKPOINT DISCARD LOAD REVOKE UNLISTEN
CLOSE DO LOCK ROLLBACK UPDATE
CLUSTER DROP MOVE SAVEPOINT VACUUM
COMMENT END NOTIFY SECURITY LABEL VALUES
COMMIT EXECUTE PREPARE SELECT WITH
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
4.清理数据
omm=# drop table schema1.tb_1;
DROP TABLE
omm=# drop schema schema1;
DROP SCHEMA
omm=# drop procedure test_cur1;
DROP PROCEDURE
最后修改时间：2021-12-18 23:23:09
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
