关于DB2游标的一些要点

关于DB2游标的一些要点

文章分类:数据库

游标一般用来迭代结果集中的行

为了在一个过程中处理一个游标的结果，需要做以下事情：

在存储过程块的开头部分 DECLARE 游标。

打开该游标。

将游标的结果取出到之前已声明的本地变量中（隐式游标处理除外，在下面的 FOR 语句中将对此加以解释）。

关闭该游标。（注意：如果现在不关闭游标，当过程终止时将隐式地关闭游标）。

注：游标的申明如果放在中间段，要用”begin。。。end;”.段分割标志分割开；

游标使用的步骤如下：

1、说明游标。说明游标的时候并不执行select语句。

declare <游标名> cursor for ;

2、打开游标。打开游标实际上是执行相应的select语句，把查询结果读取到缓冲区中。这时候游标处于活动状态，指针指向查询结果集的第一条纪录。

open <游标名>;

3、推进游标指针并读取当前纪录。用fetch语句把游标指针向前推进一条纪录，同时将缓冲区中的当前纪录读取出来送到变量中。fetch语句通常用在一个循环结构体中，通过循环执行fetch语句逐条取出结果集中的行进行处理。现在好多数据库中，还允许任意方向任意步长易懂游标指针，而不仅仅是把游标指针向前推进一行了。

fetch <游标名> into <变量1>,<变量2>...

4、关闭游标。用close语句关闭游标，释放结果集占用的缓冲区及其他资源。游标关闭后，就不再和原来的查询结果集相联系。但游标可以再次打开，与新的查询结果相联系。

close <游标名>;

基本结构:

定义游标：

DECLARE 游标名 CURSOR FOR

Select 语句；

打开游标：

OPEN游标名；

取值：

FETCH游标名INTO 变量列表

游标例子:

--先插入测试数据

create table test(id int,city char(20))

insert into test values(1,'wuhan'),(2,'hangzhou'),(3,'chengdu')

create procedure Test(

out v_message varchar(500)

)

LANGUAGE SQL

BEGIN

DECLARE v_city char(20);

DECLARE v_count int;

SET v_message = '';

select count(*) into v_count from test;

BEGIN

DECLARE v_CUR CURSOR FOR SELECT city FROM test FOR READ ONLY; OPEN v_CUR;

WHILE v_count > 0 DO

FETCH v_CUR INTO v_city;

set v_message = v_message ||v_city||' ';

set v_count = v_count -1;

end while;

END;

END@

运行结果为:

call Test(?)

completed successfully.

输出参数的值

--------------------------

参数名： V_MESSAGE

参数值： wuhan hangzhou chengdu

返回状态 = 0

Statement processed successfully in 4.39 secs.

除了这种结构外，还有一种使用for的游标的结构,例子如下:

create procedure Test(

out v_message varchar(500)

)

LANGUAGE SQL

BEGIN

DECLARE v_city char(20);

DECLARE v_count int;

SET v_message = '';

FOR V1 AS CURSOR1 CURSOR FOR select city as v_city from test

DO

set v_message = v_message||v_city||' ';

END FOR;

END@

运行结果:

call Test(?)

completed successfully.

输出参数的值

--------------------------

参数名： V_MESSAGE

参数值： wuhan hangzhou chengdu

返回状态 = 0

Statement processed successfully in 0.18 secs.

可以看到第二种游标使用起来非常简单。但是它不能使用 with hold 选项，这个with hold有什么用呢？默认情况下，,在Commit和Rollback时,游标将被关闭。所以如果游标循环体内有Commit或Rollback时，不能使用for形式的游标。但是第一种游标可以使用，可以在第一种游标定义时加上with hold 选项，那么在游标循环体内Commit和Rollback时，游标也不会关闭。

使用Commit和Rollback也不会关闭的游标，如下:

DECLARE v_CUR CURSOR with hold for SELECT city FROM test FOR READ ONLY;

如果要修改游标当前记录，需要定义可修改的游标，如下:

DECLARE v_CUR CURSOR for SELECT city FROM test FOR update;;

注意：for update 不能和 GROUP BY、 DISTINCT、 ORDER BY、 FOR READ ONLY及UNION, EXCEPT但 UNION ALL除外）一起使用。

在 DB2存储过程中，除了迭代结果集中的行以外，游标还可以做更多的事情。游标还可用于将结果集返回给调用程序或其他过程。

WITHOUT RETURN/WITH return 选项指定游标的结果表是否用于作为从一个过程中返回的结果集。