关于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 选项指定游标的结果表是否用于作为从一个过程中返回的结果集。

WITH RETURN TO CALLER 选项指定将来自游标的结果集返回给调用者，后者可以是另一个过程或一个客户机应用程序。这是默认选项。

WITH RETURN TO CLIENT 选项指定将来自游标的结果集返回给客户机应用程序，绕过任何中间的嵌套过程。

若要从一个过程中返回结果集，需要：

创建一个过程，创建时指定 DYNAMIC RESULT SETS 子句。

声明游标，声明时指定 WITH RETURN 子句。

打开该游标，并使之保持 open 状态。

如果关闭该游标，则结果集将不能返回给调用者应用程序。

下例演示了一个游标的声明，该游标从一个过程中返回一个结果集：

create procedure Test(

out v_message varchar(500)

)

DYNAMIC RESULT SETS 1

LANGUAGE SQL

BEGIN

--异常处理

DECLARE SQLCODE INT;

DECLARE v_errCode INT DEFAULT 0;

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION

BEGIN

SET v_errCode = SQLCODE;

END;

--使用游标返回结果集

BEGIN

DECLARE v_cur CURSOR WITH HOLD WITH RETURN TO CALLER FOR

SELECT * FROM test;

OPEN v_cur;

END;

RETURN v_errCode;

END@

运行结果:

call Test(?)

completed successfully.

输出参数的值

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

参数名： V_MESSAGE

参数值： -

结果集 1

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

ID CITY ----------- --------------------

1 wuhan

2 hangzhou

3 chengdu

3 条记录已选择。

返回状态 = 0

子存储过程调用 | 临时表