LightDB中的存储过程（九）—— 游标

LightDB支持存储过程，除了支持Postgres的plpgsql存储过程，还兼容Oracle的存储过程，新增了plorasql过程语言。上一篇中我们介绍了存储过程中的包，这一篇主要讲述存储过程中的游标。

游标

当在PL/SQL块中执行查询语句或DML语句时，会在内存中分配一个私有的SQL缓冲区，缓冲区中包含了处理过程的必须信息，包括已经处理完的行数、指向被分析行的指针和查询情况下的活动集，即查询语句返回的数据集。该缓冲区域称为上下文。游标是指向该缓冲区的句柄或指针。

在PL/SQL程序中，根据游标的创建与管理的不同，可以分为两类：

• 隐式游标： 由系统创建并管理的游标。主要用于处理DML语句以及单行的SELECT…INTO语句。
• 显示游标： 由用户创建并管理的游标。用户需要声明并定义显示游标，为游标命名，并将游标与一个SELECT查询相关联，然后可以采用下列方式之一处理游标缓冲区中的查询结果集：
  • 使用OPEN语句打开游标，使用FETCH语句检索游标，使用CLOSE语句关闭游标。
  • 使用FOR LOOP语句处理查询结果集。

显示游标

可以在一个PL/SQL块、子程序或包中先声明一个显式游标，然后定义该显式游标，也可以同时进行显式游标的声明与定义。

定义游标的示例如下：

declare
	cursor c1 return mystudent%rowtype;  --声明游标c1
	cursor c1 is select * from mystudent;  -- 定义游标c1
	r1 mystudent%rowtype;
begin
	open c1;
	fetch c1 into r1;
	close c1;
	raise notice '%', r1;
end;
/
1
2
3
4
5
6
7
8
9
10
11

声明与定义一个显式游标：

declare
	cursor c2 is select * from mystudent where rownum < 4;   -- 显式游标必须在PL/SQL块、子程序、包的声明部分进行声明与定义
	r2 mystudent%rowtype;
	r3 c2%rowtype;   -- 显式游标定义后，可以使用cursor_name%ROWTYPE定义记录类型的变量
begin
	open c2;   -- 打开游标
	fetch c2 into r2;
	fetch c2 into r3;
	close c2;
	raise notice '%', r2;
	raise notice '%', r3;
end;
/
1
2
3
4
5
6
7
8
9
10
11
12
13

声明与定义一个显式游标需要注意下列事项：

• 显式游标必须在PL/SQL块、子程序、包的声明部分进行声明与定义
• 显式游标定义时可以引用PL/SQL变量，但变量必须在游标定义之前声明
• 定义显式游标时并没有生成数据，只是将显式游标定义信息保存到数据字典中
• 显式游标定义后，可以使用cursor_name%ROWTYPE定义记录类型的变量

定义好游标后如何使用游标呢？我们先看一下如何打开与关闭显式游标。显式游标声明和定义后，需要使用OPEN语句打开游标，才能对查询结果集进行处理。

create or replace procedure shenlan
is
	cursor c2 is select * from mystudent where rownum < 4;  -- 存储过程中定义游标
	r2 mystudent%rowtype;
	r3 c2%rowtype;
begin
	open c2;   -- 打开游标
	fetch c2 into r2;
	fetch c2 into r3;
	close c2;  -- 关闭游标
	raise notice '%', r2;
	raise notice '%', r3;
end;
/
-- 调用结果
postgres@postgres=# call shenlan;
NOTICE:  (100,cd,changan)
NOTICE:  (100,ce,changan)
CALL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

当执行OPEN语句打开显式游标时，系统完成下列操作：

1. 为显式游标定义中的查询语句的执行分配资源，如内存缓冲区。
2. 处理显式游标定义中的查询语句。如果查询语句中包含变量或游标参数，需要确定变量或参数的值，以便确定最终的结果集。
3. 把显式游标指针指向查询结果集的第一条记录之前的位置。

打开游标需要注意下列事项：

• 只有在打开显式游标时，系统才真正创建缓冲区，并从数据库中检索数据。
• 显式游标一旦打开，就无法再次打开，除非先关闭。
• 如果显式游标定义的查询语句中的变量或游标参数发生编号，则只能在下次打开游标时才生效。

显式游标对应的查询结果集处理完后，应该使用CLOSE语句及时关闭显式游标，以释放显式游标所占用的系统资源。显式游标关闭后，不能再检索显式游标对应的查询结果集，也不能引用显式游标的属性。

打开游标后，可以通过FETCH检索显式游标。打开游标，将查询结果集放入缓冲区后，需要将游标缓冲区的数据以记录为单位检索出来，然后在PL/SQL中进行过程化的处理。

create or replace package changan    -- CREATE PACKAGE 创建包规范
is
	cursor c2 is select * from mystudent where rownum < 4;  -- 包中定义游标
	r2 mystudent%rowtype;
	r3 c2%rowtype;
	procedure shenlan;  -- 声明一个存储过程
end;
/

create or replace package body changan   -- CREATE PACKAGE BODY 创建包体
is
	procedure shenlan
	is
	begin
		open c2;   -- 打开游标
		fetch c2 into r2;   -- 检索显式游标
		fetch c2 into r3;
		close c2;  -- 关闭游标
		raise notice '%', r2;
		raise notice '%', r3;
	end;
end;
/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

对游标第一次使用FETCH语句时，游标指针指向结果集中的第一条记录，因此操作的对象是结果集中的第一条记录，操作完成后，游标指针指向下一条记录。另外需要注意，游标指针只能向下移动，不能回退。如果想检索完第二条记录后又返回到第一条记录，则必须关闭游标，然后重新打开游标。

FOR循环检索游标

另外可使用FOR循环检索游标，利用FOR循环检索游标时，系统会自动打开游标、检索游标和关闭游标。用户只需要考虑如何声明与定义游标，以及进行业务处理。

declare
	cursor cursor_name is select ...;  -- 声明与定义游标
begin
	for loop_variable in cursor_name loop
		...
	end loop;
end;
1
2
3
4
5
6
7

利用FOR循环检索游标时，系统首先隐含地定义一个数据类型为cursor_name%ROWTYPE的循环变量loop_variable，然后自动打开游标，从查询结果集中提取数据并放入loop_variable变量中，同时进行%FOUND属性检查以确定是否检索到数据。当查询结果集中所有的数据都检索完毕或循环中断时，系统自动关闭游标。

declare
	cursor c3 is select * from mystudent where rownum < 4;
	r3 c3%rowtype;
begin
	for v in c3 loop   -- 使用FOR循环检索游标
		raise notice '%', v;
	end loop;
end;
/
1
2
3
4
5
6
7
8
9

显示游标的属性

可以通过显示游标的属性返回显示游标的状态。显式游标具有下列4个属性：

• %ISOPEN: 判断显式游标是否打开
• %FOUND： 判断是否检索到数据
• %NOTFOUND：判断是否没有检索到数据
• %ROWCOUNT： 返回已经检索到了多少条记录。

declare
	cursor c3 is select * from mystudent where rownum < 4;
	r3 c3%rowtype;
begin
	if not c3%ISOPEN then
		open c3;
	end if;
	fetch c3 into r3;
	
	if c3%FOUND then
		raise notice ' found %', r3;
	end if;
	
	if c3%NOTFOUND then
		raise notice 'not found %', r3;
	end if;

	raise notice '%', c3%ROWCOUNT;
	
	if c3%ISOPEN then
		close c3;
	end if;
end;
/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

隐式游标

隐式游标是由PL/SQL自动构建并管理的会话游标，又称为SQL游标。当用户每次执行SELECT语句或DML语句时，PL/SQL打开一个隐式游标。用户不能控制隐式游标，但可以通过隐式游标属性获取隐式游标的状态信息。隐式游标属性返回的是最近执行的SELECT语句或DML语句的信息。

declare
	r3 mystudent%rowtype;
begin
	select * into r3 from mystudent where rownum < 2;
	if SQL%FOUND then  -- 隐式游标属性
		raise notice '%', r3;
	end if;
end;
/
1
2
3
4
5
6
7
8
9

游标变量

显式游标在定义时与特定的查询相关联，其结构是不变的，因此又称为静态游标。游标变量是一个指向查询结果集的指针，不与特定的查询关联，因此具有更大的灵活性。

需要注意的是检索游标变量时只能使用简单循环或WHILE循环，不能使用FOR循环。

declare
	type refcur is ref cursor return mystudent%rowtype;
	v refcur;
	r mystudent%rowtype;
begin
	open v for select * from mystudent where rownum < 5;
	
	loop
		fetch v into r;
		exit when v%NOTFOUND;
		raise notice '%', r;
	end loop;
	
	close v;
end;
/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

更多请参考LightDB官网