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

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

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

omm=# CREATE TABLE products
omm-# ( product_id integer,
omm(# product_name char(30),
omm(# category char(20)
omm(# );
CREATE TABLE
omm=# 
omm=# 
omm=#  INSERT INTO products VALUES
omm-# (1502, 'olympus camera','electrncs'),
omm-# (1601, 'lamaze', 'toys'),
omm-# (1666, 'harry potter','toys'),
omm-# (1818, 'chair','jiaju');(1700, 'wait interface', 'Books'),
omm-# 
INSERT 0 5
omm=# start transaction;
START TRANSACTION
omm=# CURSOR cursor1 FOR SELECT * FROM products ORDER BY 1;
DECLARE CURSOR
omm=# FETCH FORWARD 3 FROM cursor1;
 product_id |          product_name          |       category       
------------+--------------------------------+----------------------
       1502 | olympus camera                 | electrncs           
       1601 | lamaze                         | toys                
       1666 | harry potter                   | toys                
(3 rows)

omm=# FETCH BACKWARD 1 FROM cursor1;
 product_id |          product_name          |       category       
------------+--------------------------------+----------------------
       1601 | lamaze                         | toys                
(1 row)
omm=# select * from pg_cursors;
  name   |                       statement                       | is_holdable | is_binary | is_scrollable |         creation_time         
---------+-------------------------------------------------------+-------------+-----------+---------------+-------------------------------
 cursor1 | CURSOR cursor1 FOR SELECT * FROM products ORDER BY 1; | f           | f         | t             | 2021-12-17 19:41:05.388446+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)

omm=# end;
COMMIT
omm=# start transaction;
START TRANSACTION
omm=# CURSOR cursor1 FOR SELECT * FROM products ORDER BY 1;
DECLARE CURSOR
omm=# MOVE FORWARD 3 FROM cursor1;
MOVE 3
omm=# FETCH 4 FROM cursor1;
 product_id |          product_name          |       category       
------------+--------------------------------+----------------------
       1700 | wait interface                 | Books               
       1818 | chair                          | jiaju               
(2 rows)

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

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

omm=# create or replace procedure test_cursor_1
omm-# as
omm$#     pname    char(30);
omm$#     pcategory char(20);
omm$# 
omm$#     cursor c1_all is --cursor without args
omm$#         select product_name, category from products order by 1, 2;
omm$# begin
omm$#     if not c1_all%isopen then
omm$#         open c1_all;
    end if;
omm$# omm$#     loop
omm$#         fetch c1_all into pname, pcategory;
omm$# 
ABORT                      COMMIT                     END                        MOVE                       ROLLBACK                   UNLISTEN
ALTER                      COPY                       EXECUTE                    NOTIFY                     SAVEPOINT                  UPDATE
ANALYZE                    CREATE                     EXPLAIN                    PREPARE                    SECURITY LABEL             VACUUM
CHECKPOINT                 DELETE FROM                INSERT                     REINDEX                    SHOW                       
CLOSE                      DISCARD                    LISTEN                     RELEASE                    START                      
CLUSTER                    DO                         LOAD                       RESET                      TABLE                      
BEGIN                      DEALLOCATE                 FETCH                      REASSIGN                   SELECT                     VALUES
CALL                       DECLARE                    GRANT                      REFRESH MATERIALIZED VIEW  SET                        WITH
COMMENT                    DROP                       LOCK                       REVOKE                     TRUNCATE                   
omm$# RAISE INFO 'pname: %' ,pcategory;
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=# 
omm=# 
omm=# call test_cursor_1();
INFO:  pname: jiaju               
INFO:  pname: toys                
INFO:  pname: toys                
INFO:  pname: electrncs           
INFO:  pname: Books               
INFO:  pname: Books               
 test_cursor_1 
---------------
 
(1 row)

4.清理数据

omm=# drop procedure test_cursor_1;
DROP PROCEDURE
omm=# drop table products;
DROP TABLE
omm=#