学习内容
为了处理SQL语句，存储过程进程分配一段内存区域来保存上下文联系，游标是指向上下文区域的句柄或指针。借助游标，存储过程可以控制上下文区域的变化。
课程学习
连接数据库
#第一次进入等待15秒
#数据库启动中...
su -
omm
gsql
-r
1.准备数据
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);
2. SELECT语句，用一个游标读取一个表
–开始一个事务
start
transaction;
–建立一个名为cursor1的游标。
CURSOR
cursor1 FOR SELECT * FROM tpcds.reason ORDER BY 1;
–在系统视图pg_cursors中查看可用游标
select
* from pg_cursors;
–抓取头3行到游标cursor1里
FETCH
FORWARD 3 FROM cursor1;
–从当前关联位置开始，抓取前面的1行
FETCH
BACKWARD 1 FROM cursor1;
–关闭游标并提交事务
CLOSE
cursor1;
select
* from pg_cursors;
end;
3. VALUES子句，用一个游标读取VALUES子句中的内容
–建立一个名为cursor2的游标
start
transaction;
CURSOR
cursor2 FOR VALUES(1,2),(0,3) ORDER BY 1;
FETCH
FORWARD 2 FROM cursor2;
CLOSE
cursor2;
end;
4. WITH HOLD游标的使用
–声明该游标在创建它的事务结束后仍可继续使用
DECLARE
cursor1 CURSOR WITH HOLD FOR SELECT * FROM tpcds.reason ORDER BY 1;
–抓取接下来的3行
FETCH
FORWARD 3 FROM cursor1;
CLOSE
cursor1;
5.移动游标
START
TRANSACTION;
CURSOR
cursor1 FOR SELECT * FROM tpcds.reason ORDER BY 1;
–忽略游标cursor1的前3行
MOVE
FORWARD 3 FROM cursor1;
–抓取游标cursor1的前4行
FETCH
4 FROM cursor1;
CLOSE
cursor1;
end;
6.存储过程中使用游标
create or replace procedure test_cursor
as
company_name varchar(100);
company_loc varchar(100);
company_no integer;
cursor c1_all is --cursor
without args
select name from company
order by 1;
begin
if not c1_all%isopen then
open c1_all;
end if;
loop
fetch c1_all into
company_name;
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;
7.清理数据
drop
schema tpcds cascade;
drop
table company;
课程作业
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);
创建游标
CURSOR cursor1 FOR SELECT * FROM tpcds.reason ORDER BY 2;
抓取数据
FETCH FORWARD 1 from cursor1;
fetch backward 2 from cursor1;
MOVE重定位游标
MOVE FORWARD 3 FROM cursor1;
2.
在系统视图pg_cursors中查看游标
select * from pg_cursors;
3.
创建一个使用游标的存储过程
create
or replace procedure test_cursor
as
company_loc varchar(100);
cursor c1_all is --cursor without args
select loc from company;
begin
if not c1_all%isopen then
open c1_all;
end if;
loop
fetch c1_all into company_loc;
RAISE INFO 'company_loc: %'
,company_loc;
exit when c1_all%notfound;
end loop;
if c1_all%isopen then
close c1_all;
end if;
end;
/
call
test_cursor();
drop
procedure test_cursor;
4.清理数据
drop schema tpcds cascade;
drop table company;
总结
游标的使用十分有用处，采用存储过程使用游标有点神奇。
