DB2例程调用其它存储过程一例

DROP TABLE proc_log@

CREATE TABLE proc_log(detail VARCHAR(3000))@

DROP PROCEDURE proc_a@

CREATE PROCEDURE proc_a(v_a INTEGER,v_b INTEGER,OUT v_c INTEGER)

SPECIFIC proc_a

LANGUAGE SQL

BEGIN  

  INSERT INTO proc_log(detail) VALUES('执行存储过程proc_a');

  SET v_c=v_a+v_b;

  INSERT INTO proc_log(detail) VALUES('传入参数a='||TRIM(CHAR(v_a))||'，b='||TRIM(CHAR(v_b))||'，执行结果c='||TRIM(CHAR(v_c)));

END@

DROP PROCEDURE proc_b@

CREATE PROCEDURE proc_b()

SPECIFIC proc_b

LANGUAGE SQL

BEGIN

  DECLARE v_a INTEGER;

  DECLARE v_b INTEGER;

  DECLARE v_c INTEGER;

  DECLARE v_sql VARCHAR(100);

  DECLARE s STATEMENT; 

  

  SET (v_a,v_b)=(10,20);

  SET v_sql='CALL proc_a(?,?,?)';

  

  INSERT INTO proc_log(detail) VALUES('执行存储过程proc_b');

  PREPARE s FROM v_sql;

  EXECUTE s INTO v_c USING v_a,v_b;

  INSERT INTO proc_log(detail) VALUES('end');  

END@

CALL proc_b()@

SELECT * FROM proc_log WITH UR@