学到游标处理了，这一课程对数据库开发还是比较有用的，需要掌握，操作如下：
学习目标：学习openGauss定义游标
为了处理SQL语句，存储过程进程分配一段内存区域来保存上下文联系，游标是指向上下文区域的句柄或指针。借助游标，存储过程可以控制上下文区域的变化。
1.登录数据库环境
Welcome to 墨天轮.
This is Web Terminal of modb.pro; Good Good Study, Day Day Up.
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.
2.创造数据
omm=# create schema tpcds;
CREATE SCHEMA
omm=# CREATE TABLE tpcds.reason
omm-# (
omm(# omm(# r_reason_sk integer,
r_reason_id character(16),
omm(# r_reason_desc character(100)
omm(# );
CREATE TABLE
omm=# INSERT INTO tpcds.reason values(3,'AAAAAAAABAAAAAAA','reason 1'),
omm-# (10,'AAAAAAAABAAAAAAA','reason 2'),(4,'AAAAAAAABAAAAAAA','reason 3'),
omm-# (10,'AAAAAAAABAAAAAAA','reason 4'),(10,'AAAAAAAABAAAAAAA','reason 5'),
omm-# (20,'AAAAAAAACAAAAAAA','reason 6'),(30,'AAAAAAAACAAAAAAA','reason 7');
INSERT 0 7
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
3.创建游标
omm=# start transaction;
START TRANSACTION
omm=# CURSOR cursor1 FOR SELECT * FROM tpcds.reason ORDER BY 1;
DECLARE CURSOR
查看一下游标相关信息
omm=# select * from pg_cursors;
name | statement | is_holdable | is_binary | is_scrollable | creation_ti
me
---------+-----------------------------------------------------------+-------------+-----------+---------------+--------------------
-----------
cursor1 | CURSOR cursor1 FOR SELECT * FROM tpcds.reason ORDER BY 1; | f | f | t | 2021-12-17 20:29:48
.031019+08
(1 row)
4.抓取头3行到游票cursor1中
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=# FETCH BACKWARD 1 FROM cursor1;
r_reason_sk | r_reason_id | r_reason_desc
-------------+------------------+---------------------------------------------------------------------------------------------------
---
4 | AAAAAAAABAAAAAAA | reason 3
(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;
COMMIT
omm=# start transaction;
omm=# CURSOR cursor2 FOR VALUES(1,2),(0,3) ORDER BY 1;
START TRANSACTION
DECLARE CURSOR
omm=# FETCH FORWARD 2 FROM cursor2;
column1 | column2
---------+---------
0 | 3
1 | 2
(2 rows)
关闭游标
omm=# CLOSE cursor2;
CLOSE CURSOR
omm=# end;
COMMIT
omm=#
5.with hold游标使用
omm=# DECLARE cursor1 CURSOR WITH HOLD FOR SELECT * FROM tpcds.reason ORDER BY 1;
DECLARE CURSOR
omm=# FETCH FORWARD 3 FROM cursor1;
3 | AAAAAAAABAAAAAAA | reason 1
4 | AAAAAAAABAAAAAAA | reason 3
10 | AAAAAAAABAAAAAAA | reason 5
(3 rows)
r_reason_sk | r_reason_id | r_reason_desc
-------------+------------------+---------------------------------------------------------------------------------------------------
---
omm=# CLOSE cursor1;
CLOSE CURSOR
omm=#
omm=#
omm=#
omm=# START TRANSACTION;
START TRANSACTION
omm=# CURSOR cursor1 FOR SELECT * FROM tpcds.reason ORDER BY 1;
DECLARE CURSOR
omm=#
omm=#
omm=# MOVE FORWARD 3 FROM cursor1;
MOVE 3
omm=#
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
omm=#
omm=#
6.存储过程中使用游标
omm=# create or replace procedure test_cursor_1
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$# 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$#
ABORT DECLARE LOCK SELECT
BEGIN DO PREPARE START
CALL DROP REASSIGN TABLE
ALTER DELETE FROM MOVE SET
ANALYZE DISCARD NOTIFY SHOW
CHECKPOINT END REFRESH MATERIALIZED VIEW TRUNCATE
CLOSE EXECUTE REINDEX UNLISTEN
CLUSTER EXPLAIN RELEASE UPDATE
COMMENT FETCH RESET VACUUM
COMMIT GRANT REVOKE VALUES
COPY INSERT ROLLBACK WITH
CREATE LISTEN SAVEPOINT
DEALLOCATE LOAD SECURITY LABEL
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;
omm=# DROP PROCEDURE
清理数据
omm=#
omm=# drop schema tpcds cascade;
NOTICE: drop cascades to table tpcds.reason
DROP SCHEMA
omm=# drop table company;
DROP TABLE
omm=# omm=#
课程作业
1.创建游标，且使用select子句指定游标返回的行，分别使用FETCH抓取数据，MOVE重定位游标
omm=# create schema alex;
CREATE SCHEMA
omm=# create table alex.t1
omm-# (
omm(# r_reason_sk integer,
omm(# r_reason_desc character(100)
omm(# );
r_reason_id character(16),
omm(# CREATE TABLE
omm=#
omm=#
omm=#
omm=#
omm=# INSERT INTO alex.t1 values(3,'AAAAAAAABAAAAAAA','reason 1'),
omm-# (10,'AAAAAAAABAAAAAAA','reason 2'),(4,'AAAAAAAABAAAAAAA','reason 3'),
omm-# (10,'AAAAAAAABAAAAAAA','reason 4'),(10,'AAAAAAAABAAAAAAA','reason 5'),
omm-# (20,'AAAAAAAACAAAAAAA','reason 6'),(30,'AAAAAAAACAAAAAAA','reason 7');
INSERT 0 7
omm=#
omm=#
omm=#
omm=#
omm=# create table t2(name varchar(100), loc varchar(100), no integer);
CREATE TABLE
omm=# insert into t2 values ('macrosoft', 'usa', 001);
INSERT 0 1
omm=# insert into t2 values ('oracle', 'usa', 002);
INSERT 0 1
omm=# insert into t2 values ('backberry', 'canada', 003);
INSERT 0 1
omm=#
omm=#
omm=#
omm=#
omm=# start transaction;
START TRANSACTION
omm=# omm=#
omm=#
omm=#
omm=# cursor cursor1 for select * from alex.t1 order by 1;
DECLARE CURSOR
omm=#
omm=# fetch forward 2 from cursor1;
r_reason_sk | r_reason_id | r_reason_desc
-------------+------------------+---------------------------------------------------------------------------------------------------
---
3 | AAAAAAAABAAAAAAA | reason 1
4 | AAAAAAAABAAAAAAA | reason 3
(2 rows)
omm=#
omm=# move forward 2 from cursor1;
MOVE 2
omm=#
omm=# fetch forward 2 from cursor1;
r_reason_sk | r_reason_id | r_reason_desc
-------------+------------------+---------------------------------------------------------------------------------------------------
---
10 | AAAAAAAABAAAAAAA | reason 2
20 | AAAAAAAACAAAAAAA | reason 6
(2 rows)
omm=#
omm=#
2.在系统视图pg_cursors中查看游标
omm=# select * from pg_cursors;
name | statement | is_holdable | is_binary | is_scrollable | creation_time
---------+------------------------------------------------------+-------------+-----------+---------------+-------------------------
------
cursor1 | cursor cursor1 for select * from alex.t1 order by 1; | f | f | t | 2021-12-17 20:36:10.6103
87+08
(1 row)
3.创建一个使用游标的存储过程
omm=# create or replace procedure alex_proc_cursor_1
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$# select name, loc, no from t2 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$#
ABORT DECLARE LOCK SELECT
ALTER DELETE FROM MOVE SET
ANALYZE DISCARD NOTIFY SHOW
BEGIN DO CLOSE EXECUTE REINDEX UNLISTEN
CLUSTER EXPLAIN RELEASE UPDATE
COPY INSERT ROLLBACK WITH
CREATE LISTEN SAVEPOINT
DEALLOCATE LOAD SECURITY LABEL
omm$# PREPARE START
CALL DROP REASSIGN TABLE
CHECKPOINT END REFRESH MATERIALIZED VIEW TRUNCATE
COMMENT FETCH RESET VACUUM
COMMIT GRANT REVOKE VALUES
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=#
omm=#
omm=#
omm=# call alex_proc_cursor_1();
INFO: company_name: oracle
INFO: company_name: backberry
INFO: company_name: macrosoft
INFO: company_name: oracle
alex_proc_cursor_1
--------------------
(1 row)
4.清理数据
omm=# drop procedure alex_proc_cursor_1;
DROP PROCEDURE
