db2笔记_3_调用存储过程

调用存储过程

--出参  ?出参名$类型$out，后面跟入参

CALL 存储过程名(?ret$integer$out,?msg$varchar$out,'XXX',1,NULL);

创建存储过程

create or replace procedure 存储过程名(
    OUT o_ret  int,
    OUT o_info  VARCHAR(100),
    IN i_Param1  VARCHAR(100),
    IN i_Param2  int,
    IN i_Param3  VARCHAR(100)
) 
specific 存储过程名
RESULT SETS 2 --返回结果集数量
LANGUAGE SQL

BEGIN
  DECLARE SQLCODE INT DEFAULT 0;
  DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
  DECLARE v_sqlcode INT DEFAULT 0;
  DECLARE v_sqlstate CHAR(5) DEFAULT '00000';
  DECLARE v_Max int;
  DECLARE v_Min int;
  DECLARE v_Sql_Max varchar(1000);
  DECLARE v_st_Max statement;
  DECLARE v_cur1_Max CURSOR WITH RETURN FOR v_st_Max;
  DECLARE v_Sql varchar(1000);
  DECLARE v_st statement;
  DECLARE v_cur1 CURSOR WITH RETURN FOR v_st;

  SET v_Min = i_Param2;

  SET v_Sql_Max = 'select max(id) from tableName';
  prepare v_st_Max from v_Sql_Max;
  OPEN v_cur1_Max;
  FETCH v_cur1_Max INTO v_Max;
  CLOSE v_cur1_Max;

  set v_sql='select * from tableName where id < '||v_Max ||' AND '|| ' RN > '||v_Min||' WITH ur';
  PREPARE v_st FROM v_Sql;
  OPEN v_cur1;

  BEGIN
    DECLARE cur2 CURSOR WITH RETURN TO caller FOR
    select * from tableName2 where aaa = i_Param2; 
    OPEN cur2;
  END;
    
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    SET v_sqlcode = SQLCODE,v_sqlstate = SQLSTATE;
    SET o_ret = -99,o_info = '在['||coalesce(o_info,'未知错误')||']处发生异常 SQLCode：'||char(v_sqlcode)||'SQLState：'||char(v_sqlstate);
  END;
  
  SET o_ret = 99, o_info = ''; 
  
END;