db2存储过程游标的使用

存储过程取结果集用到数据游标下面示例，是个简单的游标使用，具体需要自己扩展

SET SCHEMA TMISUSR ;

SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","TMISUSR";


CREATE PROCEDURE "TMISUSR"."PRO_SELECT_DEBT"
 (IN "PI_DETAIL_FLAG" VARCHAR(4), 
  IN "PI_FISK_CODE" VARCHAR(10), 
  IN "PI_DEBT_CODE" VARCHAR(16), 
  OUT "PO_I_RETCODE" INTEGER, 
  OUT "PO_S_RETTEXT" VARCHAR(5120), 
  OUT "PO_S_RETINFO" VARCHAR(9000)
 ) 
  SPECIFIC "TMISUSR"."SQL170616163451030"
  DYNAMIC RESULT SETS 1
  LANGUAGE SQL
  NOT DETERMINISTIC
  CALLED ON NULL INPUT
  EXTERNAL ACTION
  OLD SAVEPOINT LEVEL
  MODIFIES SQL DATA
  INHERIT SPECIAL REGISTERS
  ------------------------------------------------------------------------


P1: BEGIN
---------------------------------------------------------------------
-----------************1.变量声明***********---------------
---------------------------------------------------------------------
-- 声明变量

DECLARE v_Stmt     VARCHAR(15000) DEFAULT ' ';

-- 声明错误代码
DECLARE SQLCODE INT DEFAULT 0;
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE v_s_ErrMsg varchar(1024);--错误原因描述

--声明游标
DECLARE cursor1 CURSOR WITH RETURN TO CLIENT FOR cursor1_sql;

-- 声明异常处理
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000'
BEGIN
END;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
GET DIAGNOSTICS EXCEPTION 1 v_s_ErrMsg = MESSAGE_TEXT;
    VALUES(SQLCODE,'错误状态：'||SQLSTATE||'说明:'||v_s_ErrMsg,v_Stmt) into PO_I_RETCODE,PO_S_RETINFO,PO_S_RETTEXT;
END;


  ---------------------------------------------------------------------
-----------************2.查询条件***********---------------
---------------------------------------------------------------------
if pi_detail_flag=1
   then
   if pi_debt_code is null or pi_debt_code=''
   then 
set v_Stmt ='select * from 
table_debt as a,
table_debt_kind as b,
table_finance_kind as c 
where  a.fisk_code='''||pi_fisk_code||''' and a.debt_kind=b.debt_kind_code and 
a.debt_finance_kind=c.finance_kind_code ';


else 
set v_Stmt ='select * from 
table_debt as a,
table_debt_kind as b,
table_finance_kind as c
where a.fisk_code='''||pi_fisk_code||'''  and a.debt_code='''||pi_debt_code||''' 
and a.debt_kind=b.debt_kind_code and 
a.debt_finance_kind=c.finance_kind_code ';
      end if;


elseif  pi_debt_code is null or pi_debt_code=''
then
set v_Stmt ='select * from table_debt where fisk_code='''||pi_fisk_code||'''';

else
set v_Stmt ='select * from table_debt where fisk_code='''||pi_fisk_code||''' and debt_code='''||pi_debt_code||'''';

end if;


     PREPARE cursor1_sql FROM v_Stmt;
-- 游标对客户机应用程序保持打开
OPEN CURSOR1;  

VALUES (0,v_Stmt) INTO PO_I_RETCODE,PO_S_RETTEXT;
END P1;