openGauss每日一练第17天｜学习openGauss定义游标的操作

坚持学习openGauss数据库，坚持每天打卡。第十七天学习openGauss定义游标的操作。

连接opengauss

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.
omm=#

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

omm=# create schema tpcc;
CREATE SCHEMA
omm=# create table tpcc.reason
omm-# (
omm(# r_reason_sk integer,
omm(# r_reason_id character(16),
omm(# r_reason_desc character(100)
omm(# );
CREATE TABLE
omm=# insert into tpcc.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 tpcc.company(name varchar(100), loc varchar(100), no integer); 
CREATE TABLE
omm=# insert into tpcc.company(name,loc,no) values('macrosoft','usa',001),('oracle','usa',002),('backberry','canada',003);
INSERT 0 3
omm=# start transaction;
START TRANSACTION
omm=# cursor cursor1 for select * from tpcc.reason order by 1;
DECLARE CURSOR
--使用fetch抓取数据
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)
--move重定位游标
omm=# move forward 3 from cursor1;
MOVE 3

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

omm=# select * from pg_cursors;
  name   |                        statement                         | is_holdable | is_binary | is_scrollable |         creati
on_time         
---------+----------------------------------------------------------+-------------+-----------+---------------+---------------
----------------
 cursor1 | cursor cursor1 for select * from tpcc.reason order by 1; | f           | f         | t             | 2021-12-17 21:
18:58.132273+08
(1 row)

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

omm=# create or replace procedure test_cursor
omm-# as
omm$#   company_name varchar(100);
omm$#   company_loc varchar(100);
omm$#   company_no integer;
omm$#   cursor c1_all is --cursor without args
omm$#   select name, loc, no from tpcc.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$# 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();
INFO:  company_name: backberry
INFO:  company_name: macrosoft
INFO:  company_name: oracle
INFO:  company_name: oracle
 test_cursor 
-------------
 
(1 row)

4.清理数据

omm=# drop procedure test_cursor;
DROP PROCEDURE
omm=# drop schema tpcc cascade;
NOTICE:  drop cascades to 2 other objects
DETAIL:  drop cascades to table tpcc.reason
drop cascades to table tpcc.company
DROP SCHEMA

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