学习目标
学习openGauss定义游标
为了处理SQL语句，存储过程进程分配一段内存区域来保存上下文联系，游标是指向上下文区域的句柄或指针。借助游标，存储过程可以控制上下文区域的变化。
课程学习实操
连接数据库
#第一次进入等待15秒 #数据库启动中... su - omm gsql -r复制
1.准备数据
create schema tpcds; CREATE TABLE tpcds.reason ( r_reason_sk integer, r_reason_id character(16), r_reason_desc character(100) ); INSERT INTO tpcds.reason values(3,'AAAAAAAABAAAAAAA','reason 1'), (10,'AAAAAAAABAAAAAAA','reason 2'),(4,'AAAAAAAABAAAAAAA','reason 3'), (10,'AAAAAAAABAAAAAAA','reason 4'),(10,'AAAAAAAABAAAAAAA','reason 5'), (20,'AAAAAAAACAAAAAAA','reason 6'),(30,'AAAAAAAACAAAAAAA','reason 7'); create table company(name varchar(100), loc varchar(100), no integer); insert into company values ('macrosoft', 'usa', 001); insert into company values ('oracle', 'usa', 002); insert into company values ('backberry', 'canada', 003);omm=# create schema tpcds; CREATE SCHEMA omm=# CREATE TABLE tpcds.reason omm-# ( omm(# r_reason_sk integer, omm(# r_reason_id character(16), omm(# omm(# ); r_reason_desc character(100) 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=# 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=#
2. SELECT语句，用一个游标读取一个表
–开始一个事务
start transaction;omm=# start transaction; START TRANSACTION复制
–建立一个名为cursor1的游标。
CURSOR cursor1 FOR SELECT * FROM tpcds.reason ORDER BY 1;omm=# CURSOR cursor1 FOR SELECT * FROM tpcds.reason ORDER BY 1; DECLARE CURSOR复制
–在系统视图pg_cursors中查看可用游标
select * from pg_cursors;omm=# select * from pg_cursors; name | statement | is_holdable | is_binary | is_scrollable | creati on_time ---------+-----------------------------------------------------------+-------------+-----------+---------------+-------------- ---------------- cursor1 | CURSOR cursor1 FOR SELECT * FROM tpcds.reason ORDER BY 1; | f | f | t | 2021-12-17 12 :09:40.42017+08 (1 row) omm=#复制
–抓取头3行到游标cursor1里
FETCH FORWARD 3 FROM 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=#复制
–从当前关联位置开始，抓取前面的1行
FETCH BACKWARD 1 FROM cursor1;omm=# FETCH BACKWARD 1 FROM cursor1; 4 | AAAAAAAABAAAAAAA | reason 3 (1 row) r_reason_sk | r_reason_id | r_reason_desc -------------+------------------+--------------------------------------------------------------------------------------------- --------- omm=#复制
–关闭游标并提交事务
CLOSE cursor1; select * from pg_cursors; end;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=#
3. VALUES子句，用一个游标读取VALUES子句中的内容
–建立一个名为cursor2的游标
start transaction; CURSOR cursor2 FOR VALUES(1,2),(0,3) ORDER BY 1; FETCH FORWARD 2 FROM cursor2; CLOSE cursor2; end;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 omm=#复制
4. WITH HOLD游标的使用
–声明该游标在创建它的事务结束后仍可继续使用
DECLARE cursor1 CURSOR WITH HOLD FOR SELECT * FROM tpcds.reason ORDER BY 1;omm=# DECLARE cursor1 CURSOR WITH HOLD FOR SELECT * FROM tpcds.reason ORDER BY 1; DECLARE CURSOR omm=#复制
–抓取接下来的3行
FETCH FORWARD 3 FROM cursor1; CLOSE 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=# CLOSE cursor1; CLOSE CURSOR omm=#复制
5.移动游标
START TRANSACTION; CURSOR cursor1 FOR SELECT * FROM tpcds.reason ORDER BY 1;omm=# START TRANSACTION; START TRANSACTION omm=# CURSOR cursor1 FOR SELECT * FROM tpcds.reason ORDER BY 1; DECLARE CURSOR复制
–忽略游标cursor1的前3行
MOVE FORWARD 3 FROM cursor1;omm=# MOVE FORWARD 3 FROM cursor1; MOVE 3复制
–抓取游标cursor1的前4行
FETCH 4 FROM cursor1; CLOSE cursor1; end;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.存储过程中使用游标
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; / call test_cursor_1(); drop procedure test_cursor_1;omm$# create or replace procedure test_cursor_1 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 DECLARE LOCK SELECT BEGIN DO PREPARE START CALL DROP REASSIGN TABLE CHECKPOINT END REFRESH MATERIALIZED VIEW TRUNCATE ALTER DELETE FROM MOVE SET ANALYZE DISCARD NOTIFY SHOW COMMIT GRANT REVOKE VALUES COPY INSERT ROLLBACK WITH CREATE LISTEN SAVEPOINT DEALLOCATE LOAD SECURITY LABEL omm$# CLOSE EXECUTE REINDEX UNLISTEN CLUSTER EXPLAIN RELEASE UPDATE COMMENT FETCH RESET VACUUM 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 omm=#复制
7.清理数据
drop schema tpcds cascade; drop table company;omm=# drop schema tpcds cascade; NOTICE: drop cascades to table tpcds.reason DROP SCHEMA omm=# drop table company; DROP TABLE omm=#
复制
课程作业
1.创建游标，且使用select子句指定游标返回的行，分别使用FETCH抓取数据，MOVE重定位游标
omm=# create schema test; CREATE SCHEMA omm=# create table test.t1(id int); CREATE TABLE omm=# insert into test.t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10); INSERT 0 10 omm=# select * from t1; id ---- 1 2 3 4 5 6 7 8 9 10 (10 rows) omm=# start transaction; START TRANSACTION omm=# CURSOR cursor1 for select * from test.t1 order by id; DECLARE CURSOR omm=# fetch forward 3 from cursor1; id ---- 1 2 3 (3 rows) omm=# fetch backward 1 from cursor1; id ---- 2 (1 row) omm=# move forward 3 from cursor1; MOVE 3 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 test.t1 order by id; | f | f | t | 2021-12-17 12:34: 20.231711+08 (1 row) omm=#复制
3.创建一个使用游标的存储过程
create or replace procedure test_cursor_1 as v_id integer; cursor c1_all is --cursor without args select id from test.t1 order by 1; begin if not c1_all%isopen then open c1_all; end if; loop fetch c1_all into v_id; RAISE INFO 'id: %' ,v_id; exit when c1_all%notfound; end loop; if c1_all%isopen then close c1_all; end if; end; /omm=# create or replace procedure test_cursor_1 omm-# as omm$# omm$# omm$# v_id integer; cursor c1_all is --cursor without args omm$# select id from test.t1 order by 1; omm$# begin omm$# if not c1_all%isopen then omm$# open c1_all; omm$# end if; omm$# loop omm$# fetch c1_all into v_id; omm$# ABORT DECLARE LOCK SELECT ALTER DELETE FROM MOVE SET ANALYZE DISCARD NOTIFY SHOW BEGIN DO PREPARE START CALL DROP REASSIGN TABLE CHECKPOINT END REFRESH MATERIALIZED VIEW TRUNCATE COMMIT GRANT REVOKE VALUES COPY INSERT ROLLBACK WITH CREATE LISTEN SAVEPOINT CLOSE EXECUTE REINDEX UNLISTEN CLUSTER EXPLAIN RELEASE UPDATE COMMENT FETCH RESET VACUUM DEALLOCATE LOAD SECURITY LABEL omm$# RAISE INFO 'id: %' ,v_id; 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: id: 1 INFO: id: 9 INFO: id: 2 INFO: id: 3 INFO: id: 4 INFO: id: 5 INFO: id: 6 INFO: id: 7 INFO: id: 8 INFO: id: 10 INFO: id: 10 test_cursor_1 --------------- (1 row) omm=#
4.清理数据
omm=# drop procedure test_cursor_1;
DROP PROCEDURE
omm=# drop table test.t1;
DROP TABLE
omm=# drop schema test;
DROP SCHEMA
omm=#
学习总结
通过本节课的学习，我掌握了游标的基本使用方法，包括创建游标，移动游标，删除游标等等。
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
评论
相关阅读
MogDB 发布更新，解决 openGauss 数据库在长事务情况下Ustore表膨胀问题
MogDB
298次阅读
2025-04-17 10:41:41
openGauss荣获中国软件行业协会多奖项，技术升级再创行业新高度
openGauss
263次阅读
2025-04-30 14:30:58
MogDB 发布更新，解决 openGauss 数据库在长事务情况下Ustore表膨胀问题
云和恩墨
196次阅读
2025-04-16 09:52:02
GitCode 成 openGauss 新归宿，国产开源数据库里程碑事件
严少安
155次阅读
2025-04-27 11:37:53
荣誉时刻！openGauss认证证书快递已发，快来看看谁榜上有名！
墨天轮小教习
136次阅读
2025-04-23 17:39:13
单个执行机并行执行MySQL到openGauss数据迁移子任务
Clipnosis
117次阅读
2025-04-30 16:39:58
openGauss6.0.0适配操作系统自带的软件，不依赖三方库
来杯拿铁
87次阅读
2025-04-18 10:49:53
opengauss使用gs_probackup进行增量备份恢复
进击的CJR
82次阅读
2025-04-09 16:11:58
Postgresql数据库单个Page最多存储多少行数据
maozicb
74次阅读
2025-04-23 16:02:19
openGauss新特性 | openGauss-DataVec向量数据库特性介绍
openGauss
45次阅读
2025-04-17 10:41:47
