SET SCHEMA TMISUSR ;
SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","TMISUSR";
CREATE PROCEDURE "TMISUSR"."PRC_TAX_TOPORG_RANK"
(IN "PI_D_DATEINIT" DATE,
IN "PI_D_DATEEND" DATE,
IN "PI_D_PRE_DATEINIT" DATE,
IN "PI_D_PRE_DATEEND" DATE,
IN "PI_S_TAXORGTYPE" CHARACTER(1),
IN "PI_S_TRECODE" VARCHAR(10),
IN "PI_S_TRELEVEL" CHARACTER(1),
IN "PI_S_STBCODE" VARCHAR(1024),
IN "PI_I_RANK" INTEGER,
OUT "PO_I_RETCODE" INTEGER,
OUT "PO_S_RETTEXT" VARCHAR(2048),
OUT "PO_S_RETINFO" VARCHAR(2048)
)
SPECIFIC "TMISUSR"."SQL160504163312997"
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_d_DateInit Date 起始查询日期(yyyy-mm-dd)
-- pi_d_DateEnd Date 终止查询日期(yyyy-mm-dd)
-- PI_d_Pre_DateInit Date, 同比日期
-- PI_d_Pre_DateEnd Date,
-- pi_s_TreCODE Char(100) 国库代码
-- pi_s_TreLevel Char(1) 国库级次
-- pi_s_GovernFlag Char(1) 辖属标志(0-全辖,1-本级)
-- pi_s_StbCode VARCHAR(1024) 预算科目代码,多个科目的列表
-- pi_s_Taxorgtype Char(10) 征收机关类型
-- pi_s_CorpId VARCHAR(40) 企业标识号
-- pi_s_TradeClassCode char(6),行业分类
-- pi_s_CorpEcoProp char(6),经济性质
-- po_i_RetCode 返回码(0-成功,其他-失败)
-- po_s_RetText 返回码文本说明
-- po_s_RetInfo 返回错误信息说明
--2.4.1 UC_23_001税收收入分企业统计表
--db2 "call PRC_TAX_TOPORG_RANK('2010-03-01','2010-12-31','2009-03-01','2009-12-31','1','0000000000','''101'',''102''',0,?,?,?)"
------------------------------------------------------------------------
P1: BEGIN
---------------------------------------------------------------------
-----------************1.变量声明*********************---------------
---------------------------------------------------------------------
DECLARE v_Stmt VARCHAR(2048) DEFAULT ' ';--
DECLARE v_Stmt_Date VARCHAR(100) DEFAULT ' ';--输入日期
DECLARE v_Stmt_PreDate VARCHAR(100) DEFAULT ' ';--输入同比日期
DECLARE v_Stmt_TRECODE VARCHAR(100) DEFAULT ' '; --国库代码
DECLARE v_Stmt_STBCODE VARCHAR(1100) DEFAULT ' ';--
DECLARE v_Stmt_Taxorgtype VARCHAR(100) DEFAULT ' ';--
DECLARE v_Stmt_Rank VARCHAR(100) DEFAULT ' ';--名次
DECLARE v_Stmt_Tbl VARCHAR(50) DEFAULT ' ';--
DECLARE v_Stmt_input_Tbl VARCHAR(50) DEFAULT ' ';--
DECLARE v_s_ErrMsg VARCHAR(1024);--错误原因描述
DECLARE d_init Date; --
-- 声明错误代码
DECLARE SQLCODE INT DEFAULT 0;--
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';--
-- 声明CURSOR
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;--
DECLARE global temporary table session.t_result(
s_TopOrgCode VARCHAR(40),
f_DayAmt DECIMAL(20, 2),
f_DayAmt_Pre DECIMAL(20, 2)
)in TS_USRTMP_32K with replace not logged on ROLLBACK preserve ROWS ON COMMIT preserve ROWS;--
---------------------------------------------------------------------
---------------------------------------------------------------------
-----------************1.查询条件**********************---------------
---------------------------------------------------------------------
--查询日期
set v_Stmt_Date =' and d_ref between '''||CHAR(PI_d_DateInit,ISO)||''' and '''||char(PI_d_DateEnd,ISO)||''' ';--
--同比查询日期
set v_Stmt_PreDate =' and d_ref between '''||CHAR(PI_d_Pre_DateInit,ISO)||''' and '''||CHAR(PI_d_Pre_DateEnd,ISO)||''' ';--
--国库级次--国库选择
CASE pi_s_TreLevel
WHEN '1' THEN
SET v_Stmt_TreCode= ' ';--
WHEN '2' THEN
SET v_Stmt_TreCode= ' AND S_OFPROVTRECODE = '''||Pi_S_TreCode||''' ';--
WHEN '3' THEN
SET v_Stmt_TreCode= ' AND S_OFCITYTRECODE = '''||Pi_S_TreCode||''' ';--
WHEN '4' THEN
SET v_Stmt_TreCode= ' AND S_OFCOUNTYTRECODE = '''||Pi_S_TreCode||''' ';--
WHEN '5' THEN
SET v_Stmt_TreCode= ' AND s_TreCode = '''||Pi_S_TreCode||''' ';--
END CASE;--
--征收机关 (0-6)
IF pi_s_Taxorgtype = '0' THEN
SET v_Stmt_Taxorgtype = ' ';--
ELSE
SET v_Stmt_Taxorgtype =' and s_Taxorgtype= '''||pi_s_Taxorgtype||''' ';--
END IF;--
--预算科目代码
IF PI_S_STBCODE = 'ALL' THEN
SET v_Stmt_STBCODE=' ';--
ELSE
SET v_Stmt_STBCODE= ' AND s_BdgSbtCode in ('|| pi_s_StbCode ||') ';--
END IF;--
--名次
IF Pi_i_Rank =0 THEN
SET v_Stmt_Rank=' ';--
ELSE
SET v_Stmt_Rank=' where s_Rank <= '||trim(char(Pi_i_Rank))||' ';--
end if;--
SET v_Stmt_Tbl=' From vw_tr_day_tax';--
SET v_Stmt_input_Tbl=' from tr_day_tax_input';--
-------------------------------------------------------------------
--输出结果集
-------------------------------------------------------------------
SET v_Stmt = 'insert into session.t_result select s_TopOrgCode,SUM(f_DayAmt),SUM(f_DayAmt_Pre) from'
||' ( select s_TopOrgCode,f_DayAmt as f_DayAmt,0 as f_DayAmt_Pre'
||v_Stmt_Tbl
||' WHERE 1=1'
||v_Stmt_Date
||v_Stmt_TRECODE
||v_Stmt_Taxorgtype
||v_Stmt_STBCODE
||' UNION ALL '
||' select s_TopOrgCode,0 as f_DayAmt,f_DayAmt as f_DayAmt_Pre'
||v_Stmt_Tbl
||' WHERE 1=1'
||v_Stmt_PreDate
||v_Stmt_TRECODE
||v_Stmt_Taxorgtype
||v_Stmt_STBCODE
||' UNION ALL '
||' select s_TopOrgCode,f_DayAmt as f_DayAmt,0 as f_DayAmt_Pre'
||v_Stmt_input_Tbl
||' WHERE 1=1'
||v_Stmt_Date
||v_Stmt_TRECODE
||v_Stmt_Taxorgtype
||v_Stmt_STBCODE
||' UNION ALL '
||' select s_TopOrgCode,0 as f_DayAmt,f_DayAmt as f_DayAmt_Pre'
||v_Stmt_input_Tbl
||' WHERE 1=1'
||v_Stmt_PreDate
||v_Stmt_TRECODE
||v_Stmt_Taxorgtype
||v_Stmt_STBCODE
||' )GROUP BY s_TopOrgCode';--
PREPARE S FROM v_Stmt;--
EXECUTE S; --
set v_Stmt =
' with TMP(s_TopOrgCode,f_DayAmt,f_DayAmt_Pre,s_Rank,s_PreRank) AS (select s_TopOrgCode,f_DayAmt,f_DayAmt_Pre,'
||'RANK() OVER (ORDER BY f_DayAmt desc) as s_Rank,'
||'RANK() OVER (ORDER BY f_DayAmt_Pre desc) as s_PreRank'
||' from session.t_result '
--||v_Stmt_Rank
||' order by s_Rank)select * from TMP '
||v_Stmt_Rank;--
PREPARE cursor1_sql FROM v_Stmt;--
-- 游标对客户机应用程序保持打开
OPEN cursor1; --
VALUES (0,v_Stmt) INTO PO_I_RETCODE,PO_S_RETTEXT;--
END P1;
SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","TMISUSR";
CREATE PROCEDURE "TMISUSR"."PRC_TAX_TOPORG_RANK"
(IN "PI_D_DATEINIT" DATE,
IN "PI_D_DATEEND" DATE,
IN "PI_D_PRE_DATEINIT" DATE,
IN "PI_D_PRE_DATEEND" DATE,
IN "PI_S_TAXORGTYPE" CHARACTER(1),
IN "PI_S_TRECODE" VARCHAR(10),
IN "PI_S_TRELEVEL" CHARACTER(1),
IN "PI_S_STBCODE" VARCHAR(1024),
IN "PI_I_RANK" INTEGER,
OUT "PO_I_RETCODE" INTEGER,
OUT "PO_S_RETTEXT" VARCHAR(2048),
OUT "PO_S_RETINFO" VARCHAR(2048)
)
SPECIFIC "TMISUSR"."SQL160504163312997"
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_d_DateInit Date 起始查询日期(yyyy-mm-dd)
-- pi_d_DateEnd Date 终止查询日期(yyyy-mm-dd)
-- PI_d_Pre_DateInit Date, 同比日期
-- PI_d_Pre_DateEnd Date,
-- pi_s_TreCODE Char(100) 国库代码
-- pi_s_TreLevel Char(1) 国库级次
-- pi_s_GovernFlag Char(1) 辖属标志(0-全辖,1-本级)
-- pi_s_StbCode VARCHAR(1024) 预算科目代码,多个科目的列表
-- pi_s_Taxorgtype Char(10) 征收机关类型
-- pi_s_CorpId VARCHAR(40) 企业标识号
-- pi_s_TradeClassCode char(6),行业分类
-- pi_s_CorpEcoProp char(6),经济性质
-- po_i_RetCode 返回码(0-成功,其他-失败)
-- po_s_RetText 返回码文本说明
-- po_s_RetInfo 返回错误信息说明
--2.4.1 UC_23_001税收收入分企业统计表
--db2 "call PRC_TAX_TOPORG_RANK('2010-03-01','2010-12-31','2009-03-01','2009-12-31','1','0000000000','''101'',''102''',0,?,?,?)"
------------------------------------------------------------------------
P1: BEGIN
---------------------------------------------------------------------
-----------************1.变量声明*********************---------------
---------------------------------------------------------------------
DECLARE v_Stmt VARCHAR(2048) DEFAULT ' ';--
DECLARE v_Stmt_Date VARCHAR(100) DEFAULT ' ';--输入日期
DECLARE v_Stmt_PreDate VARCHAR(100) DEFAULT ' ';--输入同比日期
DECLARE v_Stmt_TRECODE VARCHAR(100) DEFAULT ' '; --国库代码
DECLARE v_Stmt_STBCODE VARCHAR(1100) DEFAULT ' ';--
DECLARE v_Stmt_Taxorgtype VARCHAR(100) DEFAULT ' ';--
DECLARE v_Stmt_Rank VARCHAR(100) DEFAULT ' ';--名次
DECLARE v_Stmt_Tbl VARCHAR(50) DEFAULT ' ';--
DECLARE v_Stmt_input_Tbl VARCHAR(50) DEFAULT ' ';--
DECLARE v_s_ErrMsg VARCHAR(1024);--错误原因描述
DECLARE d_init Date; --
-- 声明错误代码
DECLARE SQLCODE INT DEFAULT 0;--
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';--
-- 声明CURSOR
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;--
DECLARE global temporary table session.t_result(
s_TopOrgCode VARCHAR(40),
f_DayAmt DECIMAL(20, 2),
f_DayAmt_Pre DECIMAL(20, 2)
)in TS_USRTMP_32K with replace not logged on ROLLBACK preserve ROWS ON COMMIT preserve ROWS;--
---------------------------------------------------------------------
---------------------------------------------------------------------
-----------************1.查询条件**********************---------------
---------------------------------------------------------------------
--查询日期
set v_Stmt_Date =' and d_ref between '''||CHAR(PI_d_DateInit,ISO)||''' and '''||char(PI_d_DateEnd,ISO)||''' ';--
--同比查询日期
set v_Stmt_PreDate =' and d_ref between '''||CHAR(PI_d_Pre_DateInit,ISO)||''' and '''||CHAR(PI_d_Pre_DateEnd,ISO)||''' ';--
--国库级次--国库选择
CASE pi_s_TreLevel
WHEN '1' THEN
SET v_Stmt_TreCode= ' ';--
WHEN '2' THEN
SET v_Stmt_TreCode= ' AND S_OFPROVTRECODE = '''||Pi_S_TreCode||''' ';--
WHEN '3' THEN
SET v_Stmt_TreCode= ' AND S_OFCITYTRECODE = '''||Pi_S_TreCode||''' ';--
WHEN '4' THEN
SET v_Stmt_TreCode= ' AND S_OFCOUNTYTRECODE = '''||Pi_S_TreCode||''' ';--
WHEN '5' THEN
SET v_Stmt_TreCode= ' AND s_TreCode = '''||Pi_S_TreCode||''' ';--
END CASE;--
--征收机关 (0-6)
IF pi_s_Taxorgtype = '0' THEN
SET v_Stmt_Taxorgtype = ' ';--
ELSE
SET v_Stmt_Taxorgtype =' and s_Taxorgtype= '''||pi_s_Taxorgtype||''' ';--
END IF;--
--预算科目代码
IF PI_S_STBCODE = 'ALL' THEN
SET v_Stmt_STBCODE=' ';--
ELSE
SET v_Stmt_STBCODE= ' AND s_BdgSbtCode in ('|| pi_s_StbCode ||') ';--
END IF;--
--名次
IF Pi_i_Rank =0 THEN
SET v_Stmt_Rank=' ';--
ELSE
SET v_Stmt_Rank=' where s_Rank <= '||trim(char(Pi_i_Rank))||' ';--
end if;--
SET v_Stmt_Tbl=' From vw_tr_day_tax';--
SET v_Stmt_input_Tbl=' from tr_day_tax_input';--
-------------------------------------------------------------------
--输出结果集
-------------------------------------------------------------------
SET v_Stmt = 'insert into session.t_result select s_TopOrgCode,SUM(f_DayAmt),SUM(f_DayAmt_Pre) from'
||' ( select s_TopOrgCode,f_DayAmt as f_DayAmt,0 as f_DayAmt_Pre'
||v_Stmt_Tbl
||' WHERE 1=1'
||v_Stmt_Date
||v_Stmt_TRECODE
||v_Stmt_Taxorgtype
||v_Stmt_STBCODE
||' UNION ALL '
||' select s_TopOrgCode,0 as f_DayAmt,f_DayAmt as f_DayAmt_Pre'
||v_Stmt_Tbl
||' WHERE 1=1'
||v_Stmt_PreDate
||v_Stmt_TRECODE
||v_Stmt_Taxorgtype
||v_Stmt_STBCODE
||' UNION ALL '
||' select s_TopOrgCode,f_DayAmt as f_DayAmt,0 as f_DayAmt_Pre'
||v_Stmt_input_Tbl
||' WHERE 1=1'
||v_Stmt_Date
||v_Stmt_TRECODE
||v_Stmt_Taxorgtype
||v_Stmt_STBCODE
||' UNION ALL '
||' select s_TopOrgCode,0 as f_DayAmt,f_DayAmt as f_DayAmt_Pre'
||v_Stmt_input_Tbl
||' WHERE 1=1'
||v_Stmt_PreDate
||v_Stmt_TRECODE
||v_Stmt_Taxorgtype
||v_Stmt_STBCODE
||' )GROUP BY s_TopOrgCode';--
PREPARE S FROM v_Stmt;--
EXECUTE S; --
set v_Stmt =
' with TMP(s_TopOrgCode,f_DayAmt,f_DayAmt_Pre,s_Rank,s_PreRank) AS (select s_TopOrgCode,f_DayAmt,f_DayAmt_Pre,'
||'RANK() OVER (ORDER BY f_DayAmt desc) as s_Rank,'
||'RANK() OVER (ORDER BY f_DayAmt_Pre desc) as s_PreRank'
||' from session.t_result '
--||v_Stmt_Rank
||' order by s_Rank)select * from TMP '
||v_Stmt_Rank;--
PREPARE cursor1_sql FROM v_Stmt;--
-- 游标对客户机应用程序保持打开
OPEN cursor1; --
VALUES (0,v_Stmt) INTO PO_I_RETCODE,PO_S_RETTEXT;--
END P1;