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

一.创建游标，且使用select子句指定游标返回的行，分别使用FETCH抓取数据，MOVE重定位游标
0.准备数据
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’);

1.创建游标，且使用select子句指定游标返回的行

start transaction;
CURSOR cursor1 FOR SELECT * FROM tpcds.reason ORDER BY 1;
复制

2.使用FETCH抓取数据

FETCH FORWARD 3 FROM cursor1;
复制

3.使用MOVE重定位游标

MOVE FORWARD 1 FROM cursor1;
FETCH FORWARD 3 FROM cursor1;
复制

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

select * from pg_cursors;
复制

三.创建一个使用游标的存储过程
1.准备数据

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.创建一个使用游标的存储过程

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

四.清理数据

drop schema tpcds cascade;
drop table company;
复制