openGauss每日一练第17天 |学习openGauss定义游标
1.创建游标，且使用select子句指定游标返回的行，分别使用FETCH抓取数据，MOVE重定位游标
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’);
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);
start transaction;
CURSOR cursor1 FOR SELECT * FROM tpcds.reason ORDER BY 1;
FETCH FORWARD 3 FROM cursor1;
MOVE FORWARD 3 FROM cursor1;
2.在系统视图pg_cursors中查看游标
select * from pg_cursors;
3.创建一个使用游标的存储过程
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;
4.清理数据
drop schema tpcds cascade;
drop table company;
