子存储过程调用

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

with return to client 选项指定将来自游标的结果返回给客户机应用程序，绕过中间的任何过程.

子存储过程：

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; 

 

set v_message = '子存储过程';

--使用游标返回结果集

BEGIN

DECLARE v_cur CURSOR WITH HOLD WITH RETURN TO CALLER FOR

SELECT * FROM test;

OPEN v_cur;

END;

 

RETURN v_errCode;

END@

--调用子存储过程

create procedure CallTest(

out v_message varchar(500)

)

DYNAMIC RESULT SETS 1

LANGUAGE SQL

BEGIN

 

DECLARE SQLCODE INT;

DECLARE v_errCode INT DEFAULT 0;

DECLARE result1 RESULT_SET_LOCATOR VARYING; --用来接收子存储过程的结果集

DECLARE v_name varchar(500); --用来接收子存储过程的输出参数

DECLARE v_id int;

DECLARE v_city varchar(50);

--异常处理

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION

BEGIN

SET v_errCode = SQLCODE;

END; 

 

 

--定义临时表

DECLARE GLOBAL TEMPORARY TABLE Temp

(

id int,

ctiy varchar(50)

)ON COMMIT PRESERVE ROWS WITH REPLACE NOT LOGGED;

 

--调用子存储过程

CALL Test(v_name);

 

if v_errCode = 0 then

set v_message = '调用' ||v_name||'成功';

else

set v_message = '调用' ||v_name||'失败';

end if;

 

--调用子过程的结果集

ASSOCIATE RESULT SET LOCATORS (result1) WITH PROCEDURE Test;

ALLOCATE   callC2   CURSOR   FOR   RESULT   SET   result1;

 

--将子过程结果集插入临时表

FETCH FROM callC2 INTO v_id, v_city;

WHILE sqlcode = 0

DO

INSERT INTO session.temp values(v_id,v_city);

FETCH FROM callC2 INTO v_id, v_city;

END WHILE;

 

--使用游标返回结果集

BEGIN

DECLARE v_cur CURSOR WITH HOLD WITH RETURN TO CALLER FOR

SELECT * FROM session.temp;

OPEN v_cur;

END;

 

RETURN v_errCode;

END@