自己安装的openGauss环境
启动openGauss
gs_ctl -D /gauss/data/db1/ start
登录openGauss
gsql -d postgres -p 26000 -r
1.创建游标，且使用select子句指定游标返回的行，分别使用FETCH抓取数据，MOVE重定位游标
create schema schema1;
create table schema1.products
(product_id INTEGER,
product_name Char(30)
);
insert into schema1.products(product_id,product_name) values(1,‘desc’),(2,‘modify’),(3,‘add’),(4,‘create’),(5,‘alter’);
select * from schema1.products;
START TRANSACTION;
CURSOR cursor1 FOR SELECT * FROM schema1.products limit 4;
FETCH FORWARD 2 FROM cursor1;
MOVE FORWARD 1 FROM cursor1;
2.在系统视图pg_cursors中查看游标
select * from pg_cursors;
end;
3.创建一个使用游标的存储过程
create or replace procedure p_cursor_1
as
r_products RECORD;
CURSOR cursor1 FOR SELECT * FROM schema1.products ;
begin
if not cursor1%isopen then
open cursor1;
end if;
loop
fetch cursor1 into r_products;exit when cursor1%notfound;RAISE INFO ‘product_id: %,product_name: %’ ,r_products.product_id,r_products.product_name;
end loop;
if cursor1%isopen then
close cursor1;
end if;
end;
/
call p_cursor_1();
4.清理数据
drop procedure p_cursor_1;
drop table schema1.products;
drop schema schema1;
