学习openGauss的第十七天
主要内容是学习openGauss定义游标
为了处理SQL语句，存储过程进程分配一段内存区域来保存上下文联系，游标是指向上下文区域的句柄或指针。借助游标，存储过程可以控制上下文区域的变化。
连接数据库
su - omm
gsql -r
1.创建游标，且使用select子句指定游标返回的行，分别使用FETCH抓取数据，MOVE重定位游标
create database tpcc ;
\c tpcc
create schema tpc;
create table tpc.tab(col1 int,col2 char(20),col3 char(20));
insert into tpc.tab values(1,'a','aa'),(2,'b','bb'),(3,'c','cc'),(4,'d','dd'),(5,'e','ee'),(6,'f','ff');
start transaction ;
cursor cursor1 for select * from tpc.tab ;
fetch forward 2 from cursor1 ;
move backward 1 from cursor1 ;
fetch backward 1 from cursor1 ;
close cursor1 ;
end ;
复制
omm=# create database tpcc ;
CREATE DATABASE
omm=# \c tpcc
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "tpcc" as user "omm".
tpcc=# create schema tpc;
CREATE SCHEMA
tpcc=# create table tpc.tab(col1 int,col2 char(20),col3 char(20));
CREATE TABLE
tpcc=# insert into tpc.tab values(1,'a','aa'),(2,'b','bb'),(3,'c','cc'),(4,'d','dd'),(5,'e','ee'),(6,'f','ff');
INSERT 0 6
tpcc=# start transaction ;
START TRANSACTION
tpcc=# cursor cursor1 for select * from tpc.tab ;
DECLARE CURSOR
tpcc=# fetch forward 2 from cursor1 ;
col1 | col2 | col3
------+----------------------+----------------------
1 | a | aa
2 | b | bb
(2 rows)
tpcc=# move backward 1 from cursor1 ;
MOVE 1
tpcc=# fetch backward 1 from cursor1 ;
col1 | col2 | col3
------+------+------
(0 rows)
tpcc=# close cursor1 ;
CLOSE CURSOR
tpcc=# end ;
COMMIT
2.在系统视图pg_cursors中查看游标
select * from pg_cursors ;复制tpcc=# start transaction ;
START TRANSACTION
tpcc=#
tpcc=# cursor cursor1 for select * from tpc.tab ;
DECLARE CURSOR
tpcc=# select * from pg_cursors ;
name | statement | is_holdable | is_binary | is_s
crollable | creation_time
---------+--------------------------------------------+-------------+-----------+-----
----------+-------------------------------
cursor1 | cursor cursor1 for select * from tpc.tab ; | f | f | t
| 2021-12-27 20:47:02.409439+08
(1 row)
tpcc=# close cursor1 ;
CLOSE CURSOR
tpcc=# end ;
COMMIT
3.创建一个使用游标的存储过程
create or replace procedure test_cursor
as
c1 int;
c2 char(20);
c3 char(20);
cursor cur1 is
select col1, col2, col3 from tpc.tab ;
begin
if not cur1%isopen then
open cur1;
end if;
loop
fetch cur1 into c1, c2, c3;
RAISE INFO 'c1: %' ,c1;
exit when cur1%notfound;
end loop;
if cur1%isopen then
close cur1;
end if;
end;
/
call test_cursor();
复制tpcc=# create or replace procedure test_cursor
tpcc-# as
tpcc$# c1 int;
tpcc$# c2 char(20);
tpcc$# c3 char(20);
tpcc$# cursor cur1 is
tpcc$# select col1, col2, col3 from tpc.tab ;
tpcc$# begin
tpcc$# if not cur1%isopen then
tpcc$# open cur1;
tpcc$# end if;
tpcc$# loop
tpcc$# fetch cur1 into c1, c2, c3;
tpcc$#
ABORT DROP RESET
ALTER END REVOKE
ANALYZE EXECUTE ROLLBACK
BEGIN EXPLAIN SAVEPOINT
CALL FETCH SECURITY LABEL
CHECKPOINT GRANT SELECT
CLOSE INSERT SET
CLUSTER LISTEN SHOW
COMMENT LOAD START
COMMIT LOCK TABLE
COPY MOVE TRUNCATE
CREATE NOTIFY UNLISTEN
DEALLOCATE PREPARE UPDATE
DECLARE REASSIGN VACUUM
DELETE FROM REFRESH MATERIALIZED VIEW VALUES
DISCARD REINDEX WITH
DO RELEASE
tpcc$# RAISE INFO 'c1: %' ,c1;
tpcc$# exit when cur1%notfound;
tpcc$# end loop;
tpcc$# if cur1%isopen then
tpcc$# close cur1;
tpcc$# end if;
tpcc$# end;
tpcc$# /
CREATE PROCEDURE
tpcc=# call test_cursor();
INFO: c1: 1
INFO: c1: 2
INFO: c1: 3
INFO: c1: 4
INFO: c1: 5
INFO: c1: 6
INFO: c1: 6
test_cursor
-------------
(1 row)
4.清理数据
drop procedure test_cursor ;
drop table tpc.tab ;
drop schema tpc ;
drop database tpcc ;复制tpcc=# drop procedure test_cursor ;
DROP PROCEDURE
tpcc=# drop table tpc.tab ;
DROP TABLE
tpcc=# drop schema tpc ;
DROP SCHEMA
tpcc=# drop database tpcc ;
ERROR: cannot drop the currently open database
tpcc=# \c omm
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "omm" as user "omm".
omm=# drop database tpcc ;
DROP DATABASE
