openGauss每日一练第17天 | 学习心得体会

游标

存储过程进程分配了一段内存区域保存上下文，游标是一个指针，指向上下文区域的内容。

1.练习

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;
select * from pg_cursors;
FETCH FORWARD 3 FROM cursor1;
FETCH BACKWARD 1 FROM cursor1;
CLOSE cursor1;
select * from pg_cursors;
end;

start transaction;
CURSOR cursor2 FOR VALUES(1,2),(0,3) ORDER BY 1;
FETCH FORWARD 2 FROM cursor2;
CLOSE cursor2;
end;

DECLARE cursor1 CURSOR WITH HOLD FOR SELECT * FROM tpcds.reason ORDER BY 1;
FETCH FORWARD 3 FROM cursor1;
CLOSE cursor1;
START TRANSACTION;
CURSOR cursor1 FOR SELECT * FROM tpcds.reason ORDER BY 1;
MOVE FORWARD 3 FROM cursor1;
FETCH 4 FROM cursor1;
CLOSE cursor1;
end;

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;

drop schema tpcds cascade;
drop table company;

2.作业

重新看了文章要求，还是要看作业，这里把作业贴出来。

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');

START TRANSACTION;
CURSOR cursor1 FOR SELECT * FROM tpcds.reason;
FETCH FORWARD 2 FROM cursor1;
MOVE FORWARD 3 FROM cursor1;
FETCH 1 FROM cursor1;
CLOSE cursor1;
END;

SELECT * FROM pg_cursors;

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;