存储过程多游标的使用

多游标使用，条件不满足时游标可以的返回用户不同的结果。

SET SCHEMA TMISUSR ;

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


CREATE PROCEDURE "TMISUSR"."PRC_BBUS_GATHER_BUS"
 (IN "PI_I_DATE" INTEGER, 
  IN "PI_S_ORGCODE" VARCHAR(1024), 
  IN "PI_S_LOGORG" VARCHAR(12), 
  IN "PI_S_SAVE" CHARACTER(1), 
  OUT "PO_I_RETCODE" INTEGER, 
  OUT "PO_S_RETTEXT" VARCHAR(1024)
 ) 
  SPECIFIC "TMISUSR"."SQL170503154616167"
  DYNAMIC RESULT SETS 1
  LANGUAGE SQL
  NOT DETERMINISTIC
  CALLED ON NULL INPUT
  EXTERNAL ACTION
  OLD SAVEPOINT LEVEL
  MODIFIES SQL DATA
  INHERIT SPECIAL REGISTERS
  ------------------------------------------------------------------------
-- SQL 存储过程 
------------------------------------------------------------------------
------------------------------------------------------------------------
--PI_I_DATE 年度
--PI_S_ORGCODE 机构集合
    -- PI_S_LOGORG VARCHAR(12), 登陆机构
-- PI_S_SAVE            保存标志
-- po_i_RetCode 返回码（0－成功，1-错误信息 2-提示信息）
-- po_s_RetText 返回码文本说明
--------------------------------------------------------------------
-----****************    功能说明    ****************** -------------
--------------------------------------------------------------------
-- 20XX年度人员、PRC_BBUS_GATHER_BUS
--call  PRC_BBUS_GATHER_BUS(201211,'''010000000002'',''020000000002''','000000000000','1',?,?)
--call  PRC_BBUS_GATHER_BUS(201211,'''010000000002'',''020000000002''','000000000000','0',?,?)
------------------------------------------------------------------------
P1: BEGIN
---------------------------------------------------------------------
-----------************1.变量声明**********************---------------
---------------------------------------------------------------------
-- 声明变量 
DECLARE v_Stmt VARCHAR(7680) DEFAULT '';--
DECLARE V_STMT_TMP VARCHAR(5120) DEFAULT '';--临时拼接
DECLARE V_STMT_INSERT       VARCHAR(1024) DEFAULT '';--最后插入用语句
DECLARE V_STMT_ORG          VARCHAR(1024)    DEFAULT '';--
DECLARE I_YEAR CHAR(4);--
DECLARE V_STMT_YEAR VARCHAR(100)    DEFAULT ''; --
DECLARE V_S_ERRMSG VARCHAR(1024);--
DECLARE I_COUNT INTEGER     DEFAULT 1;--
DECLARE V_S_SUM_ORG           VARCHAR(12) DEFAULT ''; --需要汇总的所有机构
DECLARE V_I_BUSNUM            INTEGER     DEFAULT 0; --业务量数
--DECLARE V_S_INFO              VARCHAR(256) DEFAULT ''; --汇总表错误信息


--错误原因描述
DECLARE SQLCODE INT DEFAULT 0;--
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';--
-- 声明CURSOR
DECLARE cursor1 CURSOR WITH RETURN TO CLIENT FOR cursor1_sql;--


-- 声明异常处理
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
GET DIAGNOSTICS EXCEPTION 1 V_S_ERRMSG = MESSAGE_TEXT;--
VALUES(SQLCODE, '错误状态：'||SQLSTATE||'说明:'||V_S_ERRMSG) INTO PO_I_RETCODE, PO_S_RETTEXT;--
END;--
 
--业务量信息汇总
DECLARE GLOBAL TEMPORARY TABLE SESSION.T_BUS(
i_OfMonth Integer,
s_TreCode Char(10),
s_TreLevel Char(1),
i_IncomeNum BIGINT,
i_CorrNum BIGINT,
i_PayNum BIGINT,
i_DwbkNum BIGINT,
i_OtherNum BIGINT,
f_IncomeAmt Decimal(20,2),
f_DwbkAmt Decimal(20,2),
f_CorrAmt Decimal(20,2),
f_PayAmt Decimal(20,2),
f_OtherAmt Decimal(20,2),
s_TreLevelExtend Char(2),
s_OfProvTreCode Char(10),
s_OfCityTreCode Char(10),
s_OfCountyTreCode Char(10),
s_OfOrgCode VarChar(12),
s_OrgLevel Char(1),
s_BankType VarChar(4),
s_OfOrgSetDept VarChar(60),
s_OfProvOrgCode VarChar(12),
s_OfCityOrgCode VarChar(12),
s_OfCountyOrgCode VarChar(12),
s_EnrolOrg VarChar(12)
)IN TS_USRTMP_32K WITH REPLACE NOT LOGGED ON ROLLBACK PRESERVE ROWS ON COMMIT PRESERVE ROWS;--
---------------------------------------------------------------------
-----------************2.查询条件**********************---------------
---------------------------------------------------------------------
SET V_STMT_YEAR = ' AND C.i_OfMonth = '||TRIM(CHAR(PI_I_DATE))||' ';--6位年月
SET V_STMT_ORG = ' AND C.s_OfOrgCode IN ('||PI_S_ORGCODE||') ';--
SET I_YEAR = TRIM(CHAR(PI_I_DATE/100));--

----------------判断条件，根据判断，输出错误或者提示信息--------------
    SET v_Stmt = 'SELECT S_ORGCODE FROM TG_BB_ORG WHERE I_YEAR = '||I_YEAR||' AND S_ENROLORG = '''||PI_S_LOGORG||''' WITH UR';--
    SET PO_S_RETTEXT = '';--
 
-- 声明游标
BEGIN 
    DECLARE cs_org CURSOR  FOR cs_org_sql; --
    PREPARE cs_org_sql FROM v_Stmt;--
    --游标对客户机应用程序保持打开
    OPEN cs_org;--
    FETCH cs_org into V_S_SUM_ORG;--
WHILE (SQLSTATE = '00000') DO
---校验条件，如果不通过，不能进行数据汇总
--SELECT COUNT(*) AS I_INCOMENUM FROM TG_BB_BUS WHERE I_OFMONTH=? AND S_OFORGCODE=? WITH UR
SELECT COUNT(*) INTO V_I_BUSNUM FROM TG_BB_BUS WHERE I_OFMONTH = PI_I_DATE AND S_OFORGCODE = V_S_SUM_ORG WITH UR; --
--判断明细表是否有数
IF V_I_BUSNUM = 0 THEN
    SET PO_S_RETTEXT = PO_S_RETTEXT||V_S_SUM_ORG||',';--
END IF;--


---判断汇总表是否已经有数
-- SELECT COUNT(*) INTO V_I_BUSNUM FROM TR_BB_BUS WHERE I_OFMONTH = PI_I_DATE AND S_OFORGCODE = V_S_SUM_ORG  WITH UR;--
-- IF V_I_BUSNUM = 0 THEN
--    SET V_S_INFO = V_S_INFO||V_S_SUM_ORG||',';--
-- END IF;--
FETCH cs_org into V_S_SUM_ORG;--
END WHILE;--
    CLOSE cs_org;--
END;--
   
    IF LENGTH(PO_S_RETTEXT) <> 0 THEN 
   --返回结果 1-错误信息，不能继续
    VALUES(1,'业务量汇总失败！ '||TRIM(CHAR(PI_I_DATE))||'月，所属机构代码'||PO_S_RETTEXT||'的业务量数据不存在。请到业务量数据管理中维护！') INTO PO_I_RETCODE,PO_S_RETTEXT;--
SET v_Stmt = 'SELECT * FROM SESSION.T_BUS';--
PREPARE cursor1_sql FROM v_Stmt;--
OPEN cursor1;--
RETURN;--
--ELSEIF  LENGTH(V_S_INFO) <> 0  THEN
   --返回结果 2- 提示信息 可以继续
--   VALUES(2,'业务量汇总表当中'||I_YEAR||'月,所属机构代码'||V_S_INFO||'数据已存在,是否覆盖?') INTO PO_I_RETCODE,PO_S_RETTEXT;--
ELSE 
---------------------------------------------------------------------
-----------************3.输出结果**********************---------------
---------------------------------------------------------------------
--收入笔数：缴款书笔数
--支出笔数：财政实拨笔数+财政直接支付明细数+财政授权支付明细数
--退还笔数：收入退还书笔数
--更正笔数：更正通知书笔数
--其他笔数：直接支付额度明细数+授权支付额度明细数+国债原始凭证数+支付系统往来明细数+国库内部往来明细数+同城票据交换明细数+行库往来明细数+其他会计凭证数
  
--收入金额：收入业务金额
--支出金额：支出金额
--退还金额：收入退还金额
--更正金额：更正金额
--其他金额：其他金额
SET v_Stmt = 'INSERT INTO SESSION.T_BUS  SELECT C.i_OfMonth,C.s_TreCode,C.s_TreLevel,
C.i_IncomeNum,C.i_CorrNum,i_PayNum+i_DirectNum+i_GrantNum AS i_PayNum,i_DwbkNum,i_DirectPlanNum+i_GrantPlanNum+i_DebtNum+i_PSNum+
COALESCE(i_TreTradeNum,0)+COALESCE(i_CityPayNum,0)+i_AcctTreNum+i_OtherAcctNum AS i_OtherNum,
f_IncomeAmt,f_DwbkAmt,f_CorrAmt,f_PayAmt,f_OtherAmt,
A.s_TreLevelExtend,A.s_OfProvTreCode,
CASE WHEN A.s_TreLevel IN (''1'',''2'') THEN A.s_OfProvTreCode ELSE A.s_OfCityTreCode END AS s_OfCityTreCode,
CASE WHEN A.s_TreLevel IN (''1'',''2'') THEN A.s_OfProvTreCode ELSE 
CASE WHEN A.s_TreLevel = ''3'' THEN A.s_OfCityTreCode ELSE A.s_OfCountyTreCode END END AS s_OfCountyTreCode,
A.s_OfOrgCode,
CASE WHEN s_OrgCode in (''330200000003'',''350100000003'',''360000000003'',''380100000003'',''400000000003'') THEN ''2'' 
ELSE B.s_OrgLevel END AS s_OrgLevel,A.s_BankType,B.s_OfOrgSetDept,B.s_OfProvOrgCode,
CASE WHEN B.s_OrgLevel IN (''1'',''2'') THEN B.s_OfProvOrgCode ELSE B.s_OfCityOrgCode END AS s_OfCityOrgCode,
CASE WHEN B.s_OrgLevel IN (''1'',''2'') THEN B.s_OfProvOrgCode ELSE 
CASE WHEN B.s_OrgLevel = ''3'' THEN B.s_OfCityOrgCode ELSE B.s_OfCountyOrgCode END END AS s_OfCountyOrgCode,C.s_EnrolOrg
FROM tg_bb_tre A,tg_bb_org B,tg_bb_bus  C 
WHERE 1=1 ' ||V_STMT_YEAR||V_STMT_ORG||' AND a.i_Year = '||I_YEAR||' AND a.s_TreCode = c.s_TreCode'||' AND b.i_Year = '||I_YEAR||' AND b.s_OrgCode = c.s_OfOrgCode';--
PREPARE S FROM v_Stmt;--
EXECUTE S;--


--保存
IF PI_S_SAVE = '1' THEN
   --删除原有数据
   SET V_STMT_INSERT = 'DELETE FROM TR_BB_BUS WHERE I_OFMONTH = '||TRIM(CHAR(PI_I_DATE))||' AND S_ENROLORG = '''||PI_S_LOGORG||''' WITH UR';--
   PREPARE S FROM V_STMT_INSERT;--
   EXECUTE S;--
   
   -- 插入到汇总表中
   SET V_STMT_INSERT = 'INSERT INTO TR_BB_BUS SELECT I_OFMONTH,S_TRECODE,S_TRELEVEL,I_INCOMENUM,F_INCOMEAMT,
   I_PAYNUM,F_PAYAMT,I_DWBKNUM,F_DWBKAMT,I_CORRNUM,F_CORRAMT,I_OTHERNUM,F_OTHERAMT,S_TRELEVELEXTEND,
   S_OFPROVTRECODE,S_OFCITYTRECODE,S_OFCOUNTYTRECODE,S_OFORGCODE,S_ORGLEVEL,S_BANKTYPE,S_OFORGSETDEPT,
   S_OFPROVORGCODE,S_OFCITYORGCODE,S_OFCOUNTYORGCODE,S_ENROLORG  FROM SESSION.T_BUS';--
   PREPARE S FROM V_STMT_INSERT;--
   EXECUTE S;--
END IF;--

--SET v_Stmt =临时表输出
SET v_Stmt = 'SELECT * FROM SESSION.T_BUS';--
 
PREPARE cursor1_sql FROM v_Stmt;--
-- 游标对客户机应用程序保持打开
OPEN cursor1; --
VALUES (0,'') INTO PO_I_RETCODE,PO_S_RETTEXT;--


END IF;--

END P1;