课后作业

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

连接openGauss

su - omm
gsql -r

1.游标的基本使用

1.1准备数据

CREATE TABLE TAB
(
r_reason_sk integer,
r_reason_id character(16),
r_reason_desc character(100)
);
INSERT INTO TAB values(1,'AAAAAAAABAAAAAAA','reason 1'),
(2,'AAAAAAAABAAAAAAA','reason 2'),(3,'AAAAAAAABAAAAAAA','reason 3'),
(4,'AAAAAAAABAAAAAAA','reason 4'),(5,'AAAAAAAABAAAAAAA','reason 5'),
(6,'AAAAAAAACAAAAAAA','reason 6'),(7,'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);

1.2开始事务

start transaction;

1.3创建浮标，使用select子句指定游标返回的行

CURSOR cur FOR SELECT * FROM TAB ORDER BY 1;

1.4抓取数据

抓取头6行到游标里

FETCH FORWARD 6 FROM cur;

从当前关联位置开始，抓取前面的5行

FETCH BACKWARD 5 FROM cur;

1.5重定义游标

忽略游标cur的前2行

MOVE FORWARD 2 FROM cur;

2.查看游标

CLOSE cur;select * from pg_cursors;end;

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

3.1声明游标在创建它的事务结束后仍可继续使用

DECLARE cur CURSOR WITH HOLD FOR SELECT * FROM TAB ORDER BY 1;

3.2查看游标

select * from pg_cursors;

3.3存储过程中使用游标

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

    cursor c1_all is 
        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 TABLE TAB;
DROP TABLE company;