DB2 游标

第一种

begin
    declare v_count integer;
    declare v_name varchar(10);
    declare v_age int;
    declare v_id int;
    select count(*) into v_count from tb_user;
    begin
    	declare c_user cursor for select id, user_name, user_age from tb_user;
	    open c_user;
	    while (v_count > 0) do
	        fetch c_user into v_id, v_name, v_age;
	        set v_name = v_name || '_u';
	        set v_age = v_age * 2;
	        update tb_user set user_name = v_name, user_age = v_age where id = v_id;
	        set v_count = v_count - 1;
	    end while;
	    close c_user;
  	end;
end @

第二种

begin
    declare v_name varchar(10);
    declare v_age int;
    declare v_id int;
    for v1 as c_user cursor for select id, user_name, user_age from tb_user;
    do
        fetch c_user into v_id, v_name, v_age;
	      set v_name = v_name || '_u';
	      set v_age = v_age * 2;
	      update tb_user set user_name = v_name, user_age = v_age where id = v_id;
    end for;
end @

感觉有问题，因为只更新了奇数行的数据。

第三种

create procedure pro_select_user
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 tb_user;
	  OPEN v_cur;
	END;
	
	RETURN v_errCode;
END@

RESULT SETS 1表示返回一个结果集

2个结果集：

create procedure pro_select_user
DYNAMIC RESULT SETS 2
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_cur1 CURSOR WITH RETURN TO CALLER FOR SELECT * FROM tb_user;
		DECLARE v_cur2 CURSOR WITH RETURN TO CALLER FOR SELECT * FROM tb_user;
		OPEN v_cur1;
		OPEN v_cur2;
	END;
	
	RETURN v_errCode;
END@

WITHOUT RETURN/WITH return

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

WITH RETURN TO CALLER   

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

WITH RETURN TO CLIENT   

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

若要从一个过程中返回结果集，需要：
创建一个过程，创建时指定 DYNAMIC RESULT SETS 子句。
声明游标，声明时指定 WITH RETURN 子句。
打开该游标，并使之保持 open 状态。
如果关闭该游标，则结果集将不能返回给调用者应用程序。