openGauss每日一练第17天 | 学习心得体会

学习openGauss定义游标

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

create schema mytest;
CREATE TABLE mytest.test
(
r_test_sk integer,
r_test_id character(16),
r_test_desc character(100)
);
INSERT INTO mytest.test values(3,'AAAAAAAABAAAAAAA','test 1'),
(10,'AAAAAAAABAAAAAAA','test 2'),(4,'AAAAAAAABAAAAAAA','test 3'),
(10,'AAAAAAAABAAAAAAA','test 4'),(10,'AAAAAAAABAAAAAAA','test 5'),
(20,'AAAAAAAACAAAAAAA','test 6'),(30,'AAAAAAAACAAAAAAA','test 7');
start transaction;
CURSOR cursor1 FOR SELECT * FROM mytest.test ORDER BY 1;
MOVE FORWARD 3 FROM cursor1;
FETCH 4 FROM cursor1;

omm=# create schema mytest;
omm=# CREATE TABLE mytest.test
omm-# CREATE SCHEMA
omm(# (
r_test_sk integer,
omm(# r_test_desc character(100)
r_test_id character(16),
omm(# omm(# );
CREATE TABLE
omm=# INSERT INTO mytest.test values(3,'AAAAAAAABAAAAAAA','test 1'),
omm-# (10,'AAAAAAAABAAAAAAA','test 2'),(4,'AAAAAAAABAAAAAAA','test 3'),
omm-# (10,'AAAAAAAABAAAAAAA','test 4'),(10,'AAAAAAAABAAAAAAA','test 5'),
omm-# (20,'AAAAAAAACAAAAAAA','test 6'),(30,'AAAAAAAACAAAAAAA','test 7');
INSERT 0 7
omm=# start transaction;
START TRANSACTION
omm=# CURSOR cursor1 FOR SELECT * FROM mytest.test ORDER BY 1;
DECLARE CURSOR
omm=# FETCH FORWARD 3 FROM cursor1;

-------
         3 | AAAAAAAABAAAAAAA | test 1                                                                                        
      
         4 | AAAAAAAABAAAAAAA | test 3                                                                                        
      
        10 | AAAAAAAABAAAAAAA | test 5                                                                                        
      
(3 rows)

 r_test_sk |    r_test_id     |                                             r_test_desc                                       
       
-----------+------------------+-----------------------------------------------------------------------------------------------omm=# 
omm=# MOVE FORWARD 3 FROM cursor1;
omm=# MOVE 3
omm=# FETCH 4 FROM cursor1;
 r_test_sk |    r_test_id     |                                             r_test_desc                                       
       
-----------+------------------+-----------------------------------------------------------------------------------------------
-------
        30 | AAAAAAAACAAAAAAA | test 7                                                                                        
      
(1 row)

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

select * from pg_cursors;
CLOSE cursor1;
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 mytest.test ORDER BY 1; | f           | f         | t             | 2021-12-20 14:
14:54.960517+08
(1 row)

omm=# CLOSE cursor1;
CLOSE CURSOR
omm=# select * from pg_cursors;
 name | statement | is_holdable | is_binary | is_scrollable | creation_time 
------+-----------+-------------+-----------+---------------+---------------
(0 rows)

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

create table mycompany(name varchar(100), loc varchar(100), no integer);
insert into mycompany values ('macrosoft',    'usa',          001);
insert into mycompany values ('oracle',       'usa',          002);
insert into mycompany values ('backberry',    'canada',       003);
create or replace procedure test_cursor_1
as
    mycompany_name    varchar(100);
    mycompany_loc varchar(100);
    mycompany_no  integer;

    cursor c1_all is --cursor without args
        select name, loc, no from mycompany order by 1, 2, 3;
begin
    if not c1_all%isopen then
        open c1_all;
    end if;
    loop
        fetch c1_all into mycompany_name, mycompany_loc, mycompany_no;
		RAISE INFO 'mycompany_name: %' ,mycompany_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;

4.清理数据

drop schema mytest cascade;
drop table mycompany;