db2存储过程if的使用

SET SCHEMA TMISUSR ;


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


CREATE PROCEDURE "TMISUSR"."PRC_BBUS_GATHER_ORGUSR"
 (IN "PI_I_DATE" INTEGER, 
  IN "PI_S_RPTCODE" CHARACTER(4), 
  IN "PI_S_ORGCODE" CHARACTER(14), 
  IN "PI_S_ORGLEVEL" CHARACTER(1), 
  IN "PI_S_SIGTREAREA" VARCHAR(1024), 
  IN "PI_S_GOVERNFLAG" CHARACTER(1), 
  IN "PI_S_STEP" CHARACTER(1), 
  OUT "PO_I_RETCODE" INTEGER, 
  OUT "PO_S_RETTEXT" VARCHAR(10240)
 ) 
  SPECIFIC "TMISUSR"."SQL180510151121198"
  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_ORGLEVEL    机构基级次
-- PI_S_GOVERNFLAG  辖属标志(本级，全辖)
-- PI_S_STEP        查询标志(1、国库，2、人员)
-- po_i_RetCode    返回码（0－成功，其他－失败）
-- po_s_RetText    返回码文本说明
--------------------------------------------------------------------
-----****************    功能说明    ****************** -------------
--------------------------------------------------------------------
-- XXXX年度国库机构及人员汇总报告表PRC_BBUS_GATHER_ORGUSR
--call PRC_BBUS_GATHER_ORGUSR(2013,'5310','''030000000002''','2','','0','1',?,?)
------------------------------------------------------------------------
P1: BEGIN
---------------------------------------------------------------------
-----------************1.变量声明**********************---------------
---------------------------------------------------------------------
-- 声明变量 
DECLARE v_Stmt VARCHAR(10240) DEFAULT '';--
DECLARE V_STMT_FROM VARCHAR(5120) DEFAULT '';--临时拼接
DECLARE V_WHR_ORG          VARCHAR(1024)    DEFAULT '';--
DECLARE V_STMT_YEAR VARCHAR(100)    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_USER(
S_TRETYPE   VARCHAR(4),
i_zknum     INTEGER,
i_fknum     INTEGER,
i_zxzknum INTEGER,
i_xzknum    INTEGER,
i_cqzknum INTEGER,
i_xknum     INTEGER,
i_dlzxzknum INTEGER,
i_dlxzknum INTEGER,
i_dlxknum INTEGER
)IN TS_USRTMP_32K WITH REPLACE NOT LOGGED ON ROLLBACK PRESERVE ROWS ON COMMIT PRESERVE ROWS;--


---------------------------------------------------------------------
-----------************2.查询条件**********************---------------
---------------------------------------------------------------------
SET V_STMT_YEAR = ' AND i_Year = '||TRIM(CHAR(PI_I_DATE))||' ';--

--根据辖属标志判断需要展示的数据范围
IF PI_S_GOVERNFLAG='0' THEN
--根据机构级次判断需要展示的数据信息范围
CASE PI_S_ORGLEVEL
WHEN '1' THEN 
SET V_WHR_ORG='';--
WHEN '2' THEN 
SET V_WHR_ORG=' AND s_OfProvOrgCode = '||pi_s_OrgCode||' ';--
WHEN '3' THEN
SET V_WHR_ORG=' AND s_OfCityOrgCode = '||pi_s_OrgCode||' ';--
WHEN '4' THEN
SET V_WHR_ORG=' AND s_OfCountyOrgCode = '||pi_s_OrgCode||' ';--
WHEN '5' THEN
SET V_WHR_ORG=' AND s_OfOrgCode = '||pi_s_OrgCode||' ';--
END CASE;--
ELSE
IF PI_S_STEP='1' THEN
   SET V_WHR_ORG=' AND s_OfOrgCode = '||pi_s_OrgCode||' ';--
ELSE
   SET V_WHR_ORG=' AND s_OrgCode = '||pi_s_OrgCode||' ';--
END IF;--
END IF;--

---------------------------------------------------------------------
-----------************3.输出结果**********************---------------
---------------------------------------------------------------------
IF PI_S_STEP='1' THEN
   SET v_Stmt = 'INSERT INTO SESSION.T_USER (S_TRETYPE,i_zknum,i_fknum,i_zxzknum,i_xzknum,i_cqzknum,i_xknum,i_dlzxzknum,i_dlxzknum,i_dlxknum)
SELECT S_TRETYPE,sum(i_zknum)as i_zknum,sum(i_fknum) as i_fknum,sum(i_zxzknum) as i_zxzknum,sum(i_xzknum) as i_xzknum,sum(i_cqzknum) as i_cqzknum,sum(i_xknum) as i_xknum,
sum(i_dlzxzknum) as i_dlzxzknum, sum(i_dlxzknum) as i_dlxzknum,sum(i_dlxknum) as i_dlxknum FROM
(SELECT S_TRETYPE,
  CASE WHEN  S_TRELEVEL=''1'' AND S_BANKTYPE=''100'' THEN 1 ELSE 0 END as i_zknum,
  CASE WHEN  S_TRELEVEL=''2'' AND S_BANKTYPE=''100'' THEN 1 ELSE 0 END as i_fknum,
  CASE WHEN  S_TRELEVEL=''3'' AND S_BANKTYPE=''100'' THEN 1 ELSE 0 END as i_zxzknum,
  CASE WHEN  S_TRELEVEL=''4'' AND S_TRELEVELEXTEND=''40'' AND S_BANKTYPE=''100'' THEN 1 ELSE 0 END as i_xzknum,
  CASE WHEN  S_TRELEVEL=''4'' AND S_TRELEVELEXTEND=''41'' AND S_BANKTYPE=''100'' THEN 1 ELSE 0 END as i_cqzknum,
  CASE WHEN  S_TRELEVEL=''5'' AND S_BANKTYPE=''100'' THEN 1 ELSE 0 END as i_xknum,
  CASE WHEN  S_TRELEVEL=''3'' AND S_BANKTYPE>''100'' THEN 1 ELSE 0 END as i_dlzxzknum,
  CASE WHEN  S_TRELEVEL=''4'' AND S_BANKTYPE>''100'' THEN 1 ELSE 0 END as i_dlxzknum,
  CASE WHEN  S_TRELEVEL=''5'' AND S_BANKTYPE>''100'' THEN 1 ELSE 0 END as i_dlxknum ';--
--条件
SET v_Stmt = v_Stmt||' FROM TG_BB_TRE WHERE 1=1 '||V_STMT_YEAR||V_WHR_ORG||') GROUP BY S_TRETYPE ORDER BY S_TRETYPE ';--
PREPARE S FROM v_Stmt;--
EXECUTE S;--
ELSE

--条件
SET V_STMT_FROM = ' FROM TG_BB_USER WHERE 1=1 '||V_STMT_YEAR||V_WHR_ORG||' ';--

SET v_Stmt = 'INSERT INTO SESSION.T_USER (S_TRETYPE,i_zknum,i_fknum,i_zxzknum,i_xzknum,i_cqzknum,i_xknum,i_dlzxzknum,i_dlxzknum,i_dlxknum)
SELECT S_TRETYPE,sum(i_zknum)as i_zknum,sum(i_fknum) as i_fknum,sum(i_zxzknum) as i_zxzknum,sum(i_xzknum) as i_xzknum,sum(i_cqzknum) as i_cqzknum,sum(i_xknum) as i_xknum,
sum(i_dlzxzknum) as i_dlzxzknum, sum(i_dlxzknum) as i_dlxzknum,sum(i_dlxknum) as i_dlxknum FROM
(SELECT  ''1'' as S_TRETYPE,
  CASE WHEN  S_ORGLEVEL=''1'' AND S_BANKTYPE=''100'' THEN I_FULLFORMALNUM ELSE 0 END as i_zknum,
  CASE WHEN  S_ORGLEVEL=''2'' AND S_BANKTYPE=''100'' THEN I_FULLFORMALNUM ELSE 0 END as i_fknum,
  CASE WHEN  S_ORGLEVEL=''3'' AND S_BANKTYPE=''100'' THEN I_FULLFORMALNUM ELSE 0 END as i_zxzknum,
  CASE WHEN  S_ORGLEVEL=''4'' AND S_BANKTYPE=''100'' THEN I_FULLFORMALNUM ELSE 0 END as i_xzknum,
  CASE WHEN  S_ORGLEVEL=''9'' AND S_BANKTYPE=''100'' THEN I_FULLFORMALNUM ELSE 0 END as i_cqzknum,
  CASE WHEN  S_ORGLEVEL=''5'' AND S_BANKTYPE=''100'' THEN I_FULLFORMALNUM ELSE 0 END as i_xknum,
  CASE WHEN  S_ORGLEVEL=''3'' AND S_BANKTYPE>''100'' THEN I_FULLFORMALNUM ELSE 0 END as i_dlzxzknum,
  CASE WHEN  S_ORGLEVEL=''4'' AND S_BANKTYPE>''100'' THEN I_FULLFORMALNUM ELSE 0 END as i_dlxzknum,
  CASE WHEN  S_ORGLEVEL=''5'' AND S_BANKTYPE>''100'' THEN I_FULLFORMALNUM ELSE 0 END as i_dlxknum '
||V_STMT_FROM
||' UNION ALL 
SELECT  ''2'' as S_TRETYPE,
  CASE WHEN  S_ORGLEVEL=''1'' AND S_BANKTYPE=''100'' THEN I_FULLENGAGENUM ELSE 0 END as i_zknum,
  CASE WHEN  S_ORGLEVEL=''2'' AND S_BANKTYPE=''100'' THEN I_FULLENGAGENUM ELSE 0 END as i_fknum,
  CASE WHEN  S_ORGLEVEL=''3'' AND S_BANKTYPE=''100'' THEN I_FULLENGAGENUM ELSE 0 END as i_zxzknum,
  CASE WHEN  S_ORGLEVEL=''4'' AND S_BANKTYPE=''100'' THEN I_FULLENGAGENUM ELSE 0 END as i_xzknum,
  CASE WHEN  S_ORGLEVEL=''9'' AND S_BANKTYPE=''100'' THEN I_FULLENGAGENUM ELSE 0 END as i_cqzknum,
  CASE WHEN  S_ORGLEVEL=''5'' AND S_BANKTYPE=''100'' THEN I_FULLENGAGENUM ELSE 0 END as i_xknum,
  CASE WHEN  S_ORGLEVEL=''3'' AND S_BANKTYPE>''100'' THEN I_FULLENGAGENUM ELSE 0 END as i_dlzxzknum,
  CASE WHEN  S_ORGLEVEL=''4'' AND S_BANKTYPE>''100'' THEN I_FULLENGAGENUM ELSE 0 END as i_dlxzknum,
  CASE WHEN  S_ORGLEVEL=''5'' AND S_BANKTYPE>''100'' THEN I_FULLENGAGENUM ELSE 0 END as i_dlxknum '
||V_STMT_FROM
||' UNION ALL
SELECT  ''3'' as S_TRETYPE,
  CASE WHEN  S_ORGLEVEL=''1'' AND S_BANKTYPE=''100'' THEN I_PARTFORMALNUM ELSE 0 END as i_zknum,
  CASE WHEN  S_ORGLEVEL=''2'' AND S_BANKTYPE=''100'' THEN I_PARTFORMALNUM ELSE 0 END as i_fknum,
  CASE WHEN  S_ORGLEVEL=''3'' AND S_BANKTYPE=''100'' THEN I_PARTFORMALNUM ELSE 0 END as i_zxzknum,
  CASE WHEN  S_ORGLEVEL=''4'' AND S_BANKTYPE=''100'' THEN I_PARTFORMALNUM ELSE 0 END as i_xzknum,
  CASE WHEN  S_ORGLEVEL=''9'' AND S_BANKTYPE=''100'' THEN I_PARTFORMALNUM ELSE 0 END as i_cqzknum,
  CASE WHEN  S_ORGLEVEL=''5'' AND S_BANKTYPE=''100'' THEN I_PARTFORMALNUM ELSE 0 END as i_xknum,
  CASE WHEN  S_ORGLEVEL=''3'' AND S_BANKTYPE>''100'' THEN I_PARTFORMALNUM ELSE 0 END as i_dlzxzknum,
  CASE WHEN  S_ORGLEVEL=''4'' AND S_BANKTYPE>''100'' THEN I_PARTFORMALNUM ELSE 0 END as i_dlxzknum,
  CASE WHEN  S_ORGLEVEL=''5'' AND S_BANKTYPE>''100'' THEN I_PARTFORMALNUM ELSE 0 END as i_dlxknum '
||V_STMT_FROM
||' UNION ALL
SELECT  ''4'' as S_TRETYPE,
  CASE WHEN  S_ORGLEVEL=''1'' AND S_BANKTYPE=''100'' THEN I_PARTENGAGENUM ELSE 0 END as i_zknum,
  CASE WHEN  S_ORGLEVEL=''2'' AND S_BANKTYPE=''100'' THEN I_PARTENGAGENUM ELSE 0 END as i_fknum,
  CASE WHEN  S_ORGLEVEL=''3'' AND S_BANKTYPE=''100'' THEN I_PARTENGAGENUM ELSE 0 END as i_zxzknum,
  CASE WHEN  S_ORGLEVEL=''4'' AND S_BANKTYPE=''100'' THEN I_PARTENGAGENUM ELSE 0 END as i_xzknum,
  CASE WHEN  S_ORGLEVEL=''9'' AND S_BANKTYPE=''100'' THEN I_PARTENGAGENUM ELSE 0 END as i_cqzknum,
  CASE WHEN  S_ORGLEVEL=''5'' AND S_BANKTYPE=''100'' THEN I_PARTENGAGENUM ELSE 0 END as i_xknum,
  CASE WHEN  S_ORGLEVEL=''3'' AND S_BANKTYPE>''100'' THEN I_PARTENGAGENUM ELSE 0 END as i_dlzxzknum,
  CASE WHEN  S_ORGLEVEL=''4'' AND S_BANKTYPE>''100'' THEN I_PARTENGAGENUM ELSE 0 END as i_dlxzknum,
  CASE WHEN  S_ORGLEVEL=''5'' AND S_BANKTYPE>''100'' THEN I_PARTENGAGENUM ELSE 0 END as i_dlxknum '
||V_STMT_FROM||') GROUP BY S_TRETYPE ORDER BY S_TRETYPE ';--
 
PREPARE S FROM v_Stmt;--
EXECUTE S;--
END IF;--

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

END P1;