暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / openGauss每日一练第 17 天 | 学习openGauss定义游标

openGauss每日一练第 17 天 | 学习openGauss定义游标

原创 海潮 2021-12-31
403

👉openGauss SQL学习参考

学习目标

学习openGauss定义游标

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

课程学习

连接openGauss

root@modb:~# su - omm
omm@modb:~$ 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 tpcds.company(name varchar(100), loc varchar(100), no integer);
insert into tpcds.company values ('macrosoft',    'usa',          001);
insert into tpcds.company values ('oracle',       'usa',          002);
insert into tpcds.company values ('backberry',    'canada',       003);

mydb=# select * from tpcds.reason ;
 r_reason_sk |   r_reason_id    |                                            r_reason_desc                                             
-------------+------------------+------------------------------------------------------------------------------------------------------
           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                                                                                            
(7 rows)

mydb=# select * from tpcds.company ;
   name    |  loc   | no 
-----------+--------+----
 macrosoft | usa    |  1
 oracle    | usa    |  2
 backberry | canada |  3
(3 rows)

mydb=#

2.SELECT 语句，用一个游标读取一个表

--开始一个事务
mydb=# start transaction;
START TRANSACTION

--建立一个名为 cursor1 的游标。
mydb=# CURSOR cursor1 FOR SELECT * FROM tpcds.reason ORDER BY 1;
DECLARE CURSOR

--在系统视图 pg_cursors 中查看可用游标
mydb=# 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             | 2022-01-03 14:36:22.002958+08
(1 row)

--抓取头3行到游标 cursor1 里
mydb=# 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)

--从当前关联位置开始，抓取前面的1行
mydb=# FETCH BACKWARD 1 FROM cursor1;
 r_reason_sk |   r_reason_id    |                                            r_reason_desc                                             
-------------+------------------+------------------------------------------------------------------------------------------------------
           4 | AAAAAAAABAAAAAAA | reason 3                                                                                            
(1 row)

--关闭游标并提交事务
mydb=# CLOSE cursor1;
CLOSE CURSOR
mydb=# select * from pg_cursors;
 name | statement | is_holdable | is_binary | is_scrollable | creation_time 
------+-----------+-------------+-----------+---------------+---------------
(0 rows)

mydb=# end;
COMMIT
mydb=#

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

--建立一个名为 cursor2 的游标
mydb=# start transaction;
START TRANSACTION
mydb=# CURSOR cursor2 FOR VALUES(1,2),(0,3) ORDER BY 1;
DECLARE CURSOR
mydb=# FETCH FORWARD 2 FROM cursor2;
 column1 | column2 
---------+---------
       0 |       3
       1 |       2
(2 rows)

mydb=# FETCH FORWARD 1 FROM cursor2;   --向前移动
 column1 | column2 
---------+---------
(0 rows)

mydb=# FETCH BACKWARD 1 FROM cursor2;  --向后移动
 column1 | column2 
---------+---------
       1 |       2
(1 row)

mydb=# FETCH BACKWARD 1 FROM cursor2;
 column1 | column2 
---------+---------
       0 |       3
(1 row)

mydb=# FETCH BACKWARD 1 FROM cursor2;
 column1 | column2 
---------+---------
(0 rows)

mydb=# CLOSE cursor2;
CLOSE CURSOR
mydb=# FETCH BACKWARD 1 FROM cursor2;
ERROR:  cursor "cursor2" does not exist
mydb=# end;
ROLLBACK
mydb=#

4.WITH HOLD 游标的使用

--声明该游标在创建它的事务结束后仍可继续使用
mydb=# DECLARE cursor1 CURSOR WITH HOLD FOR SELECT * FROM tpcds.reason ORDER BY 1;
DECLARE CURSOR

–-抓取接下来的3行
mydb=# 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)

mydb=# CLOSE cursor1;
CLOSE CURSOR

--test2
mydb=# begin;
BEGIN
mydb=# DECLARE cursor1 CURSOR WITH HOLD FOR SELECT * FROM tpcds.reason ORDER BY 1;
DECLARE CURSOR
mydb=# 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)

mydb=# end;
COMMIT
mydb=# FETCH FORWARD 3 FROM cursor1;
 r_reason_sk |   r_reason_id    |                                            r_reason_desc                                             
-------------+------------------+------------------------------------------------------------------------------------------------------
          30 | AAAAAAAACAAAAAAA | reason 7                                                                                            
(1 row)

mydb=# close cursor1;
CLOSE CURSOR
mydb=# FETCH FORWARD 3 FROM cursor1;
ERROR:  cursor "cursor1" does not exist
mydb=#

5.移动游标

mydb=# START TRANSACTION;
START TRANSACTION
mydb=# CURSOR cursor1 FOR SELECT * FROM tpcds.reason ORDER BY 1;
DECLARE CURSOR

--–忽略游标 cursor1 的前3行
mydb=# MOVE FORWARD 3 FROM cursor1;
MOVE 3

--–抓取游标 cursor1 的前4行
mydb=# 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)

mydb=# FETCH 1 FROM cursor1; 
 r_reason_sk | r_reason_id | r_reason_desc 
-------------+-------------+---------------
(0 rows)

mydb=# MOVE FORWARD 1 FROM cursor1; 
MOVE 0
mydb=# CLOSE cursor1;
CLOSE CURSOR
mydb=# end;
COMMIT
mydb=#

6.存储过程中使用游标

mydb=# select * from company ;
   name    |  loc   | no 
-----------+--------+----
 macrosoft | usa    |  1
 oracle    | usa    |  2
 backberry | canada |  3
(3 rows)

mydb=# 

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;
/

mydb=# 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)

mydb=# drop procedure test_cursor_1;
DROP PROCEDURE
mydb=#

7.清理数据

mydb=# drop schema tpcds cascade;
NOTICE:  drop cascades to 2 other objects
DETAIL:  drop cascades to table reason
drop cascades to table company
DROP SCHEMA
mydb=# drop table company;
DROP TABLE
mydb=#

课后作业

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

mydb=# create table t1(id int,name varchar(10));
CREATE TABLE
mydb=# insert into t1 values(1,'a'),(2,'b'),(3,'c'),(4,'d'),(5,'e');
INSERT 0 5
mydb=# 
mydb=# start transaction;
START TRANSACTION
mydb=# CURSOR cursor1 FOR SELECT * FROM t1 ORDER BY 1;
DECLARE CURSOR
mydb=# FETCH FORWARD 3 FROM cursor1;
 id | name 
----+------
  1 | a
  2 | b
  3 | c
(3 rows)

mydb=# MOVE FORWARD 1 FROM cursor1;   --移动游标 指向第5行开始位置
MOVE 1
mydb=# FETCH FORWARD 1 FROM cursor1;
 id | name 
----+------
  5 | e
(1 row)

mydb=# FETCH FORWARD 1 FROM cursor1;   --游标结果集已取完
 id | name 
----+------
(0 rows)

mydb=# FETCH BACKWARD 1 FROM cursor1;   --向后取数
 id | name 
----+------
  5 | e
(1 row)

mydb=# FETCH BACKWARD 1 FROM cursor1;
 id | name 
----+------
  4 | d
(1 row)

mydb=# CLOSE cursor1;
CLOSE CURSOR
mydb=# end;
COMMIT
mydb=#

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

mydb=#  start transaction;
START TRANSACTION
mydb=#  CURSOR cursor1 FOR SELECT * FROM t1 ORDER BY 1;
DECLARE CURSOR
mydb=# select * from pg_cursors;                       
  name   |                    statement                    | is_holdable | is_binary | is_scrollable |         creation_time         
---------+-------------------------------------------------+-------------+-----------+---------------+-------------------------------
 cursor1 | CURSOR cursor1 FOR SELECT * FROM t1 ORDER BY 1; | f           | f         | t             | 2022-01-03 19:34:16.420119+08
(1 row)

mydb=# end;
COMMIT
mydb=# select * from pg_cursors;
 name | statement | is_holdable | is_binary | is_scrollable | creation_time 
------+-----------+-------------+-----------+---------------+---------------
(0 rows)

mydb=#

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

mydb=# select * from t1 ;
 id | name 
----+------
  1 | a
  2 | b
  3 | c
  4 | d
  5 | e
(5 rows)

mydb=# 

create or replace procedure yhc_cursor_1
as
    v_id  integer;
    v_name  varchar(10);

    cursor c1(v1 int) is  --cursor without args
        select id,name from t1 where id > v1 order by 1 ;
begin
    if not c1%isopen then
        open c1(3);
    end if;
    loop
        fetch c1 into v_id,v_name;
        RAISE INFO 'id: %, name:%', v_id,v_name;
        exit when c1%notfound;
    end loop;
    if c1%isopen then
        close c1;
    end if;
end;
/

mydb=# call yhc_cursor_1();
INFO:  id: 4, name:d
INFO:  id: 5, name:e
INFO:  id: 5, name:e
 yhc_cursor_1 
--------------
 
(1 row)

mydb=# drop procedure yhc_cursor_1;
DROP PROCEDURE
mydb=#

4.清理数据

mydb=# drop table t1 ;
DROP TABLE
mydb=#
opengauss
最后修改时间：2022-01-03 20:15:09
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论