openGauss每日一练第17天 课程笔记和作业

数据库环境

openGauss:2.0.0 - 数据库实训平台

学习目标

为了处理SQL语句，存储过程进程分配一段内存区域来保存上下文联系，游标是指向上下文区域的句柄或指针。借助游标，存储过程可以控制上下文区域的变化。

学习笔记

• 在系统视图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-22 09:52:15.486393+08
(1 row)

• VALUES子句，用一个游标读取VALUES子句中的内容

omm=# CURSOR cursor2 FOR VALUES(1,2),(0,3) ORDER BY 1;
DECLARE CURSOR

• WITH HOLD游标，在创建它的事务结束后仍可继续使用

omm=# DECLARE cursor1 CURSOR WITH HOLD FOR SELECT * FROM tpcds.reason ORDER BY 1;
DECLARE CURSOR

课后作业

1.创建游标，且使用select子句指定游标返回的行，分别使用FETCH抓取数据，MOVE重定位游标

omm=# create schema my_schema;
CREATE SCHEMA
omm=# 
omm=# create table my_schema.product
omm-# (
omm(#     id integer,
omm(#     name char(30)
omm(# );
CREATE TABLE
omm=# 
omm=# insert into my_schema.product values(1,'apple'),
omm-# (2,'orange'),(3,'banana'),(4,'juice'),(5,'pear'),
omm-# (6,'grape'),(7,'mango');
INSERT 0 7
omm=# 
omm=# select * from my_schema.product;
 id |              name              
----+--------------------------------
  1 | apple                         
  2 | orange                        
  3 | banana                        
  4 | juice                         
  5 | pear                          
  6 | grape                         
  7 | mango                         
(7 rows)

omm=# start transaction;
START TRANSACTION
omm=# cursor cur for select * from my_schema.product order by 1;
DECLARE CURSOR
omm=# fetch forward 2 from cur;
 id |              name              
----+--------------------------------
  1 | apple                         
  2 | orange                        
(2 rows)

omm=# move forward 2 from cur;
MOVE 2
omm=# fetch forward 2 from cur;
 id |              name              
----+--------------------------------
  5 | pear                          
  6 | grape                         
(2 rows)

2.在系统视图pg_cursors中查看游标

omm=# select * from pg_cursors;
 name |                         statement                          | is_holdable | is_binary | is_scrollable |         creation_time         
------+------------------------------------------------------------+-------------+-----------+---------------+-------------------------------
 cur  | cursor cur for select * from my_schema.product order by 1; | f           | f         | t             | 2021-12-22 10:14:53.452979+08
(1 row)
omm=# close cur;
CLOSE CURSOR
omm=# end;
COMMIT
omm=# select * from pg_cursors;
 name | statement | is_holdable | is_binary | is_scrollable | creation_time 
------+-----------+-------------+-----------+---------------+---------------
(0 rows)

3.创建一个使用游标的存储过程

omm=# select * from my_schema.product;
 id |              name              
----+--------------------------------
  1 | apple                         
  2 | orange                        
  3 | banana                        
  4 | juice                         
  5 | pear                          
  6 | grape                         
  7 | mango                         
(7 rows)

omm=# create or replace procedure test_cur
omm-# as
omm$#     product_id    integer;
omm$#     product_name char(30);
omm$#   cursor test_cur is --cursor without args
omm$#   select id, name from my_schema.product order by 1, 2;
omm$# begin
omm$#     if not test_cur%isopen then
omm$#         open test_cur;
omm$#     end if;
omm$#     loop        
omm$#         fetch test_cur into product_id, product_name;
omm$#         raise info 'product_name: %' ,product_name;        
omm$#         exit when test_cur%notfound;
omm$#     end loop;
omm$#     if test_cur%isopen then
omm$#         close test_cur;
omm$#     end if;
omm$# end;
omm$# /
CREATE PROCEDURE

omm=# call test_cur();
INFO:  product_name: apple                         
INFO:  product_name: orange                        
INFO:  product_name: banana                        
INFO:  product_name: juice                         
INFO:  product_name: pear                          
INFO:  product_name: grape                         
INFO:  product_name: mango                         
INFO:  product_name: mango                         
 test_cur 
----------
 
(1 row)

4.清理数据

omm=# drop procedure test_cur;
DROP PROCEDURE
omm=# drop schema my_schema cascade;
NOTICE:  drop cascades to table my_schema.product
DROP SCHEMA
omm=# 

学习资源

• 帮助文档 CURSOR
• openGauss SQL学习参考资料
• 每日一练：openGauss数据库在线实训课程
• openGauss每日一练 | 21期养成好习惯，提升技术能力！
• 墨天轮Markdown编辑器使用介绍
• 墨天轮数据库在线实训平台V1.0操作手册
• 墨天轮数据社区

欢迎各位同学一起来交流学习心得！