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

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

-- 1.
create schema tpcds;
create table tpcds.test (id int);
insert into tpcds.test values (0), (1), (2), (3), (4);
select * from tpcds.test;

start transaction;
cursor cursor1 for select * from tpcds.test;
fetch forward 3 from cursor1;
-- 0 1 2
move backward 2 from cursor1;
fetch 4 from cursor1;
-- 1 2 3 4

-- 2.
select * from pg_cursors;
end
select * from pg_cursors;
-- 无游标

-- 3.
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);
create or replace procedure test_cursor
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();
drop procedure test_cursor;

-- 4.
drop schema tpcds cascade;
drop table company;