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

openGauss每日一练第17天 openGauss定义游标
1.准备数据

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 company(name varchar(100), loc varchar(100), no integer);
insert into company values ('macrosoft',    'usa',          001);
insert into company values ('oracle',       'usa',          002);
insert into company values ('backberry',    'canada',       003);

复制

2. SELECT语句，用一个游标读取一个表
   –开始一个事务

start transaction;
–建立一个名为cursor1的游标。

CURSOR cursor1 FOR SELECT * FROM tpcds.reason ORDER BY 1;
–在系统视图pg_cursors中查看可用游标

select * from pg_cursors;
–抓取头3行到游标cursor1里

FETCH FORWARD 3 FROM cursor1;
–从当前关联位置开始，抓取前面的1行

FETCH BACKWARD 1 FROM cursor1;
–关闭游标并提交事务

CLOSE cursor1;
select * from pg_cursors;
end;
3. VALUES子句，用一个游标读取VALUES子句中的内容
–建立一个名为cursor2的游标

start transaction;
CURSOR cursor2 FOR VALUES(1,2),(0,3) ORDER BY 1;
FETCH FORWARD 2 FROM cursor2;
CLOSE cursor2;
end;

复制

4. WITH HOLD游标的使用
   –声明该游标在创建它的事务结束后仍可继续使用

DECLARE cursor1 CURSOR WITH HOLD FOR SELECT * FROM tpcds.reason ORDER BY 1;
–抓取接下来的3行

FETCH FORWARD 3 FROM cursor1;
CLOSE cursor1;
5.移动游标
START TRANSACTION;
CURSOR cursor1 FOR SELECT * FROM tpcds.reason ORDER BY 1;
–忽略游标cursor1的前3行

MOVE FORWARD 3 FROM cursor1;
–抓取游标cursor1的前4行

FETCH 4 FROM cursor1;
CLOSE cursor1;
end;
6.存储过程中使用游标

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;
/
call test_cursor_1();
drop procedure test_cursor_1;

复制

7.清理数据

drop schema tpcds cascade;
drop table company;

复制

课后作业
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=# 

复制