openGauss每日一练第17天 | 游标的使用

游标

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

游标的使用分为显式游标和隐式游标。
显式游标主要用于对查询语句的处理，尤其是在查询结果为多条记录的情况下。
对于非查询语句，如修改、删除操作，则由系统自动地为这些操作设置游标并创建其工作区，这些由系统隐含创建的游标称为隐式游标。
隐式游标的名称为 SQL，这是由系统定义的。

游标命令只能在事务块里使用。

----准备数据

create schema lvzz;
create table lvzz.reason
(
r_reason_sk integer,
r_reason_id character(16),
r_reason_desc character(30)
);
insert into lvzz.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 lvzz.company(name varchar(100), loc varchar(100), no integer);
insert into lvzz.company values ('macrosoft','usa',001);
insert into lvzz.company values ('oracle','usa',002);
insert into lvzz.company values ('backberry','canada',003);

----select 语句使用游标

--建立一个名为cursor1的游标
start transaction;
cursor cursor1 for select * from lvzz.reason order by 1;

--在系统视图pg_cursors中查看可用游标
select * from pg_cursors;

--抓取头3行到游标cursor1里，再抓取前1行
fetch forward 3 from cursor1;
fetch backward 1 from cursor1;

--关闭游标并提交事务
close cursor1;
select * from pg_cursors;
end;

----values子句游标使用

start transaction;
cursor cursor2 for values(1,2),(0,3) order by 1;
fetch forward 2 from cursor2;
close cursor2;
end;

----with hold 游标的使用，声明该游标在创建它的事务结束后仍可继续使用

declare cursor1 cursor with hold for select * from lvzz.reason order by 1;

--抓取前3行数据
fetch forward 3 from cursor1;
close cursor1;

----移动游标

start transaction;
cursor cursor1 for select * from lvzz.reason order by 1;

--忽略游标cursor1的前3行
move forward 3 from cursor1;
fetch 4 from cursor1;
close cursor1;
end;

----存储过程中使用游标

create or replace procedure lvzz.test_cursor_1
as
    company_name varchar(100);
    company_loc varchar(100);
    company_no integer;

    cursor c1_all is select name, loc, no from lvzz.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 lvzz.test_cursor_1();
drop procedure lvzz.test_cursor_1;

课程作业

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

--数据准备
create schema lvzz;
create table lvzz.test(id int,name varchar(20));
insert into lvzz.test values
(1,'one'),(2,'two'),(3,'three'),(4,'four'),(5,'five'),(6,'six'),(7,'seven');

--创建游标cursor1
start transaction;
cursor cursor1 for select * from lvzz.test order by 1;

--获取前3行的数据
fetch forward 3 from cursor1;

--重定位到第4行的位置，并获取1行数据
move forward 3 from cursor1;
fetch 1 from cursor1;

close cursor1;
end;

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

--创建游标cursor2
start transaction;
cursor cursor2 for select * from lvzz.test order by 1;
select * from pg_cursors;

close cursor2;
end;

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

create or replace procedure lvzz.test_cursor1
as
    id integer;
    name varchar(20);

    cursor c1 is select * from lvzz.test order by 1;
begin
    if not c1%isopen then open c1;
    end if;
    loop
        fetch c1 into id, name;
        raise info 'name: %' ,name;
        exit when c1%notfound;
    end loop;
    if c1%isopen then close c1;
    end if;
end;
/

call lvzz.test_cursor1();

4、清理数据

drop table lvzz.test;
drop procedure lvzz.test_cursor1;
drop schema lvzz;