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

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

create schema eason;
CREATE TABLE eason.products
(
id integer,
name character(16),
p_desc character(100)
);
INSERT INTO eason.products values(1,'computer','AAA'),
(2,'phone','BBB'),(3,'keyboard','CCC'),(4,'book','DDD'),(5,'cup','EEE');

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

FETCH FORWARD 2 FROM cursor1;
MOVE FORWARD 2 FROM cursor1;
FETCH FORWARD 2 FROM cursor1;
FETCH backWARD 2 FROM cursor1;
FETCH backWARD 2 FROM cursor1;
FETCH backWARD 2 FROM cursor1;

从截图可以理解为，游标是将表中的数据放入到游标，然后FETCH FORWARD表示从头行开始往前获取2行数据，此时move将从第三行开始移动，往前移动到低5行，再次fetch forward 2时，由于后面只有第五行了，所以只能获取到1行即第5行数据。fetch backward同理。每次fetch时都会移动关联行。

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

select * from pg_cursors;

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

CREATE TABLE products
(
name character(100),
loc character(100),
no integer
);
INSERT INTO products values('computer','AAA',1),
('phone','BBB',2),('keyboard','CCC',3),('book','DDD',4),('cup','EEE',5);



create or replace procedure test_cursor_1
as
    products_name    varchar(100);
    products_loc varchar(100);
    products_no  integer;

    cursor c1_all is 
        select name, loc, no from products order by 1, 2, 3;
begin
    if not c1_all%isopen then
        open c1_all;
    end if;
    loop
        fetch c1_all into products_name, products_loc, products_no;
		RAISE INFO 'products_name: %' ,products_name;
        exit when c1_all%notfound;
    end loop;
    if c1_all%isopen then
        close c1_all;
    end if;
end;
/
call test_cursor_1();

4.清理数据

drop procedure test_cursor_1;
dtop table products;