db2存储过程WHILE-DO的使用

SET SCHEMA TMISUSR ;


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


CREATE PROCEDURE "TMISUSR"."PRC_BBUS_GATHER_TRE"
 (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(7680)
 ) 
  SPECIFIC "TMISUSR"."SQL160504163325916"
  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              登陆机构
--PI_S_SAVE             是否保存进表 (0-不保存 1-保存)
-- po_i_RetCode 返回码（0－成功，1－错误信息 2-提示信息 ）
-- po_s_RetText 返回码文本说明
--------------------------------------------------------------------
-----****************    功能说明    ****************** -------------
--------------------------------------------------------------------
--call  PRC_BBUS_GATHER_TRE(2012,'''010000000002'',''020000000002''','000000000000','1',?,?)
------------------------------------------------------------------------
P1: BEGIN
---------------------------------------------------------------------
-----------************1.变量声明**********************---------------
---------------------------------------------------------------------
-- 声明变量
-- DateKind 1-收入、2-支出、3-库存，添加区分标志位分别排序分组
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 V_STMT_YEAR VARCHAR(100)    DEFAULT ''; --
DECLARE V_I_CNT               INTEGER DEFAULT 0; -- 判断机构数据是否存在
DECLARE V_I_CNT_TRE           INTEGER DEFAULT 0; -- 判断国库合格性
DECLARE V_I_CNT_SUM           INTEGER DEFAULT 0; -- 判断机构汇总数据是否存在
DECLARE V_S_SUM_ORG           VARCHAR(12) DEFAULT ''; --需要汇总的所有机构

DECLARE V_S_ERRMSG VARCHAR(1024);--
--错误原因描述
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_ORG(
I_YEAR SMALLINT,
S_ORGCODE VARCHAR(12),
S_ORGNAME VARCHAR(60),
S_ORGLEVEL CHAR(1),
S_OFORGSETDEPT VARCHAR(60),
S_BANKTYPE VARCHAR(4),
S_BOOKSYSTYPE CHAR(1),
S_MAINTRECODE CHAR(10),
S_MAINTRENAME VARCHAR(60),
I_MANGNUM INTEGER,
S_OFRPTORGNAME VARCHAR(60),
S_OFCOUNTYORGCODE VARCHAR(12),
S_OFCITYORGCODE VARCHAR(12),
S_OFPROVORGCODE VARCHAR(12),
S_GOVERNORGCODE VARCHAR(12),
S_GOVERNORGLEVEL CHAR(1),
I_TOPTRENUM INTEGER,
I_PROVTRENUM INTEGER,
I_CITYTRENUM INTEGER,
I_COUNTYTRENUM INTEGER,
I_TOWNTRENUM INTEGER,
I_VILLTRENUM INTEGER,
I_AGTBANKNUM INTEGER,
I_CHKCITYNUM INTEGER,
I_CHKCOUNTYNUM INTEGER,
S_ENROLORG VARCHAR(12)
)IN TS_USRTMP_32K WITH REPLACE NOT LOGGED ON ROLLBACK PRESERVE ROWS ON COMMIT PRESERVE ROWS;--
 
---------------------------------------------------------------------
-----------************2.查询条件**********************---------------
---------------------------------------------------------------------
SET V_STMT_ORG = ' AND s_OrgCode IN ('||PI_S_ORGCODE||') ';--
SET V_STMT_YEAR = ' AND i_Year = '||TRIM(CHAR(PI_I_DATE))||' ';--
    SET v_Stmt = 'SELECT S_ORGCODE FROM TG_BB_ORG WHERE I_YEAR = '||TRIM(CHAR(PI_I_DATE))||' 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(*) INTO V_I_CNT FROM TG_BB_ORG WHERE I_YEAR= PI_I_DATE AND S_ORGCODE = V_S_SUM_ORG WITH UR;--
--  SELECT COUNT(*) AS I_MANGNUM FROM TG_BB_TRE WHERE I_YEAR=? AND S_OFORGCODE in ( ? )  WITH UR
--  SELECT COUNT(*) AS I_MANGNUM FROM TG_BB_TRESUB WHERE I_YEAR=? AND S_ORGCODE in ( ? )  WITH UR
    --  SELECT COUNT(*) AS I_MANGNUM FROM TR_BB_CHKSPOT WHERE I_YEAR=? AND S_CHKORGCODE in ( ? ) WITH UR
IF V_I_CNT = 0 THEN
    SET po_s_RetText = po_s_RetText||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 
--返回结果
VALUES(1,po_s_RetText||'机构数据为空，请先维护机构数据!') INTO po_i_RetCode,po_s_RetText;--
SET v_Stmt = 'SELECT * FROM SESSION.T_ORG';--
PREPARE cursor1_sql FROM v_Stmt;--
OPEN cursor1;--
RETURN;--
ELSE
--VALUES(0,'还存在未确认机构，不允许进行年终结转操作!') INTO po_i_RetCode,po_s_RetText;--

---------------------------------------------------------------------
-----------************3.输出结果**********************---------------
---------------------------------------------------------------------
     
--汇总机构数据表
SET v_Stmt = 'INSERT INTO SESSION.T_ORG (i_Year,s_OrgCode,s_OrgName,s_OrgLevel,s_OfOrgSetDept,s_BankType,s_BookSysType,s_MainTrecode,
S_MAINTRENAME,i_MangNum,s_OfRptOrgName,s_OfProvOrgCode,s_OfCityOrgCode,s_OfCountyOrgCode,s_GovernOrgCode,s_GovernOrgLevel,s_EnrolOrg)  
SELECT B.i_Year,s_OrgCode,s_OrgName,CASE WHEN s_OrgCode in (''330200000003'',''350100000003'',''360000000003'',''380100000003'',''400000000003'') 
THEN ''2'' ELSE s_OrgLevel END AS s_OrgLevel,s_OfOrgSetDept,B.s_BankType,s_BookSysType
,s_MainTrecode,A.s_TreName AS S_MAINTRENAME,i_MangNum,s_OfRptOrgName,B.s_OfProvOrgCode,
CASE WHEN s_OrgLevel IN (''1'',''2'') THEN B.s_OfProvOrgCode ELSE B.s_OfCityOrgCode END AS s_OfCityOrgCode,
CASE WHEN s_OrgLevel IN (''1'',''2'') THEN B.s_OfProvOrgCode ELSE 
CASE WHEN s_OrgLevel = ''3'' THEN B.s_OfCityOrgCode ELSE B.s_OfCountyOrgCode END END AS s_OfCountyOrgCode
,s_GovernOrgCode,s_GovernOrgLevel
,B.s_EnrolOrg FROM tg_bb_tre A ,tg_bb_org B WHERE B.i_Year = '||TRIM(CHAR(PI_I_DATE))||' AND B.s_OrgCode IN ('||PI_S_ORGCODE||') AND A.i_Year = B.i_Year AND A.s_TreCode=B.s_MainTrecode';--
PREPARE S FROM v_Stmt;--
EXECUTE S;--
--汇总国库数据表
SET V_STMT_TMP = 'SELECT s_OrgCode,VALUE(SUM(i_TopTreNum),0) as i_TopTreNum,VALUE(SUM(i_ProvTreNum),0) as i_ProvTreNum,
VALUE(SUM(i_CityTreNum),0) as i_CityTreNum,VALUE(SUM(i_CountyTreNum),0) as i_CountyTreNum,
VALUE(SUM(i_TownTreNum),0) as i_TownTreNum,VALUE(SUM(i_VillTreNum),0) as i_VillTreNum
from (SELECT s_OrgCode,CASE WHEN s_TreLevelExtend = ''10'' THEN num ELSE 0 END AS i_toptrenum,
CASE WHEN s_TreLevelExtend = ''20'' OR s_TreLevelExtend = ''21'' THEN num ELSE 0 END as i_provtrenum,
CASE WHEN s_TreLevelExtend = ''30'' OR s_TreLevelExtend = ''31'' THEN num ELSE 0 END as i_CityTreNum,
CASE WHEN s_TreLevelExtend = ''40'' THEN num ELSE 0 END as i_CountyTreNum,
CASE WHEN s_TreLevelExtend = ''41'' THEN num ELSE 0 END as i_TownTreNum,
CASE WHEN s_TreLevelExtend = ''50'' THEN num ELSE 0 END as i_VillTreNum FROM(
SELECT s_OfOrgCode AS s_OrgCode,s_TreLevelExtend, COUNT(*) AS num FROM tg_bb_tre WHERE 1=1 '||V_STMT_YEAR||' AND s_OfOrgCode IN ('||PI_S_ORGCODE||') 
GROUP BY s_OfOrgCode,s_TreLevelExtend ORDER BY s_TreLevelExtend ASC)) GROUP BY s_OrgCode';--
SET v_Stmt = 'MERGE INTO SESSION.T_ORG AS a
USING (
'||V_STMT_TMP||'
) AS b
ON a.s_OrgCode = b.s_OrgCode
WHEN MATCHED THEN 
UPDATE SET (a.i_TopTreNum,a.i_ProvTreNum,a.i_CityTreNum,a.i_CountyTreNum,a.i_TownTreNum,a.i_VillTreNum) = 
(b.i_TopTreNum,b.i_ProvTreNum,b.i_CityTreNum,b.i_CountyTreNum,b.i_TownTreNum,b.i_VillTreNum)';--

PREPARE S FROM v_Stmt;--
EXECUTE S;--

--汇总集中支付表
SET v_Stmt = 'UPDATE SESSION.T_ORG SET I_AGTBANKNUM = 0 ';--
-- SET V_STMT_TMP = 'SELECT s_OrgCode,VALUE(SUM(i_AgtBankNum),0) as i_AgtBankNum FROM (
-- SELECT s_OrgCode,i_AgtBankNum FROM tg_bb_tresub WHERE 1=1 '||V_STMT_YEAR||V_STMT_ORG||') GROUP BY s_OrgCode';--
-- SET v_Stmt = 'MERGE INTO SESSION.T_ORG AS a
-- USING (
-- '||V_STMT_TMP||'
-- ) AS b
-- ON a.s_OrgCode = b.s_OrgCode
-- WHEN MATCHED THEN 
-- UPDATE SET (a.i_AgtBankNum) = (b.i_AgtBankNum)';--
PREPARE S FROM v_Stmt;--
EXECUTE S;--

--汇总实地检查表
SET V_STMT_TMP = 'SELECT s_ChkOrgcode AS s_OrgCode,SUM(i_ChkCityNum) AS i_ChkCityNum,SUM(i_ChkCountyNum) AS i_ChkCountyNum FROM tr_bb_chkspot WHERE 1=1 '||V_STMT_YEAR||' AND s_ChkOrgcode in ('||PI_S_ORGCODE||') 
    GROUP BY s_ChkOrgcode';--
SET v_Stmt = 'MERGE INTO SESSION.T_ORG AS a
USING (
'||V_STMT_TMP||'
) AS b
ON a.s_OrgCode = b.s_OrgCode
WHEN MATCHED THEN 
UPDATE SET (a.i_ChkCityNum,a.i_ChkCountyNum) = (b.i_ChkCityNum,b.i_ChkCountyNum)';--
PREPARE S FROM v_Stmt;--
EXECUTE S;--

--是否将汇总好的临时表数据保存到正式汇总表,当数据确认时，不保存 0-不保存 1-保存
IF PI_S_SAVE = '1' THEN
   --删除原有数据
       SET V_STMT_INSERT = 'DELETE FROM TR_BB_ORG WHERE I_YEAR='||TRIM(CHAR(PI_I_DATE))||' AND S_ORGCODE IN ('||PI_S_ORGCODE||') WITH UR';--
   PREPARE S FROM V_STMT_INSERT;--
   EXECUTE S;--

       -- 插入到汇总表中
       SET V_STMT_INSERT = 'INSERT INTO TR_BB_ORG SELECT * FROM SESSION.T_ORG';--
   PREPARE S FROM V_STMT_INSERT;--
   EXECUTE S;--
END IF;--

--SET v_Stmt =临时表输出
SET v_Stmt = 'SELECT * FROM SESSION.T_ORG';--
PREPARE cursor1_sql FROM v_Stmt;--

-- 游标对客户机应用程序保持打开
OPEN cursor1; --
VALUES (0,v_Stmt) INTO PO_I_RETCODE,PO_S_RETTEXT;--


END IF;--
END P1;