sql

BEGIN
DECLARE _yy varchar(4) default '';
  DECLARE _ym1,_ym2 varchar(6) default '';
  DECLARE _sort varchar(255) default '';
  DECLARE _name varchar(255) default '';
DECLARE _cclass1,_cclass2,_cclass3,_cclass4 varchar(255) default '';
  DECLARE _bn_dys_hj,_bn_dys_gk,_bn_dys_qk,_sn_dys_hj,_sn_dys_gk,_sn_dys_qk FLOAT;
  DECLARE _tbzj,_zf,_zjl FLOAT;
DECLARE cur1 CURSOR FOR (
SELECT SORT,`NAME`,
MAX(CASE WHEN IYEAR=vYear AND LEFT(DB,2)='GC' THEN CCLASS ELSE 0 END) AS CCLASS1,
MAX(CASE WHEN IYEAR=vYear AND LEFT(DB,2)='QC' THEN CCLASS ELSE 0 END) AS CCLASS2,
MAX(CASE WHEN IYEAR=vYear-1 AND LEFT(DB,2)='GC' THEN CCLASS ELSE 0 END) AS CCLASS3,
MAX(CASE WHEN IYEAR=vYear-1 AND LEFT(DB,2)='QC' THEN CCLASS ELSE 0 END) AS CCLASS4
    FROM  t_conifgure 
WHERE CODE_TYPE ='LRHZBB' AND CCLASS_ENG ='R' 
GROUP BY SORT,`NAME` ORDER BY SORT -- LIMIT 3
);
  DECLARE cur2 CURSOR FOR (
SELECT SORT,`NAME`,
MAX(CASE WHEN IYEAR=vYear AND LEFT(DB,2)='GC' THEN CCLASS ELSE 0 END) AS CCLASS1,
MAX(CASE WHEN IYEAR=vYear AND LEFT(DB,2)='QC' THEN CCLASS ELSE 0 END) AS CCLASS2,
MAX(CASE WHEN IYEAR=vYear-1 AND LEFT(DB,2)='GC' THEN CCLASS ELSE 0 END) AS CCLASS3,
MAX(CASE WHEN IYEAR=vYear-1 AND LEFT(DB,2)='QC' THEN CCLASS ELSE 0 END) AS CCLASS4
    FROM  t_conifgure 
WHERE CODE_TYPE ='LRHZBB' AND CCLASS_ENG ='C' 
GROUP BY SORT,`NAME` ORDER BY SORT DESC
);
DECLARE CONTINUE HANDLER FOR NOT FOUND SET _cclass1='-1';
  -- 删除临时表
  DROP TEMPORARY TABLE IF EXISTS t_temp_dss_fin_profit_collect;
-- 创建临时表
CREATE TEMPORARY TABLE IF NOT EXISTS `t_temp_dss_fin_profit_collect` (
`lv`  int(11) NULL,
`xm`  varchar(64) NULL,
`bn_dys_hj`  decimal(20,2) NULL,
`bn_dys_gk`  decimal(20,2) NULL,
`bn_dys_qk`  decimal(20,2) NULL,
`sn_dys_hj`  decimal(20,2) NULL,
`sn_dys_gk`  decimal(20,2) NULL,
`sn_dys_qk`  decimal(20,2) NULL,
`tbzj`       decimal(20,2) NULL,
`zf`         decimal(20,2) NULL,
`zjl`        decimal(20,2) NULL
)engine=heap;
-- 统计数据到临时表中
SET _yy=vYear-1;
  SET _ym1=convert(CONCAT(vYear,vMonth),CHAR);
SET _ym2=convert(CONCAT(_yy,vMonth),CHAR);
OPEN cur1;
Repeat
fetch cur1 into _sort,_name,_cclass1,_cclass2,_cclass3,_cclass4;
IF _cclass1<>'-1' THEN

CALL USP_DSS_P_FIN_AMOUNT_ROW('T_DSS_ACCVOUCH','CODE',_cclass1,vYear,_ym1,'GC','','',_bn_dys_gk);  
CALL USP_DSS_P_FIN_AMOUNT_ROW('T_DSS_ACCVOUCH','CODE',_cclass2,vYear,_ym1,'QC','','',_bn_dys_qk);  
SET _bn_dys_hj=_bn_dys_gk+_bn_dys_qk;

CALL USP_DSS_P_FIN_AMOUNT_ROW('T_DSS_ACCVOUCH','CODE',_cclass3,_yy,_ym2,'GC','','',_sn_dys_gk);  
CALL USP_DSS_P_FIN_AMOUNT_ROW('T_DSS_ACCVOUCH','CODE',_cclass4,_yy,_ym2,'QC','','',_sn_dys_qk);  
SET _sn_dys_hj=_sn_dys_gk+_sn_dys_qk;


SET _tbzj =_bn_dys_hj-_sn_dys_hj;
SET _zf=0;
SET _zjl=ROUND(_tbzj/_bn_dys_hj *100 ,2);
-- 插入临时表数据
INSERT INTO t_temp_dss_fin_profit_collect 
VALUES 
(_sort,_name,
         _bn_dys_hj,_bn_dys_gk,_bn_dys_qk,
         _sn_dys_hj,_sn_dys_gk,_sn_dys_qk,
_tbzj,_zf,_zjl
);
END IF;
Until _cclass1='-1' END Repeat;
CLOSE cur1;
COMMIT;


  OPEN cur2;
Repeat
fetch cur2 into _sort,_name,_cclass1,_cclass2,_cclass3,_cclass4;
IF _cclass1<>'-1' THEN

CALL USP_DSS_P_FIN_AMOUNT_COL(_cclass1,'t_temp_dss_fin_profit_collect','bn_dys_gk',_bn_dys_gk);  
CALL USP_DSS_P_FIN_AMOUNT_COL(_cclass2,'t_temp_dss_fin_profit_collect','bn_dys_qk',_bn_dys_qk);  
SET _bn_dys_hj=_bn_dys_gk+_bn_dys_qk;

CALL USP_DSS_P_FIN_AMOUNT_COL(_cclass3,'t_temp_dss_fin_profit_collect','sn_dys_gk',_sn_dys_gk);  
CALL USP_DSS_P_FIN_AMOUNT_COL(_cclass4,'t_temp_dss_fin_profit_collect','sn_dys_qk',_sn_dys_qk);  
SET _sn_dys_hj=_sn_dys_gk+_sn_dys_qk;
-- 插入临时表数据
SET _tbzj =_bn_dys_hj-_sn_dys_hj;
SET _zf=0;
SET _zjl=ROUND(_tbzj/_bn_dys_hj *100 ,2);
 
INSERT INTO t_temp_dss_fin_profit_collect 
VALUES 
(_sort,_name,
         _bn_dys_hj,_bn_dys_gk,_bn_dys_qk,
         _sn_dys_hj,_sn_dys_gk,_sn_dys_qk,
_tbzj,_zf,_zjl
);
END IF;
Until _cclass1='-1' END Repeat;
CLOSE cur2;
COMMIT;


SELECT lv,xm,
ROUND(bn_dys_hj/10000,2) as bn_dys_hj,
ROUND(bn_dys_gk/10000,2) as bn_dys_gk,
ROUND(bn_dys_qk/10000,2) as bn_dys_qk,
ROUND(sn_dys_hj/10000,2) as sn_dys_hj,
ROUND(sn_dys_gk/10000,2) as sn_dys_gk,
ROUND(sn_dys_qk/10000,2) as sn_dys_qk,
tbzj,zf,zjl
FROM t_temp_dss_fin_profit_collect
ORDER BY lv asc;

END

IN `vYear` VARCHAR(4),IN `vMonth` VARCHAR(2)

BEGIN
-- =============================================
-- =============================================
  -- T_DSS_ACCVOUCH  CODE       带%
  -- T_DSS_CASHTABLE CCASHITEM  无%(公式简单的)
  -- T_DSS_ACCSUM    CODE       带% (无借贷)
  -- T_DSS_ACCASS    CODE       带% (有借贷)
  DECLARE _code varchar(2000) default '';
  DECLARE _table varchar(40) default '';
  DECLARE _field varchar(40) default '';
  DECLARE _per varchar(4) default '%';
  DECLARE _like varchar(4) default 'LIKE';
SET _code=vCode;
SET _table=vTable;
SET _field=vField;
IF _code IS NOT NULL AND _code<>'0' AND _code<>'' THEN
IF INSTR(_code,'B')>0 OR INSTR(_code,'E')>0 THEN
IF INSTR(_code,'BD')>0 OR INSTR(_code,'BC')>0 OR INSTR(_code,'ED')>0 OR INSTR(_code,'EC')>0 THEN
SET _table='T_DSS_ACCASS';
SET _field='CODE';
ELSE
SET _table='T_DSS_ACCSUM';
SET _field='CODE';
END IF;
#去掉DC
SET _code=REPLACE(_code,'D','');
SET _code=REPLACE(_code,'C','');
END IF;
IF _table='T_DSS_ACCVOUCH' OR _table='T_DSS_ACCSUM' OR _table='T_DSS_ACCASS' THEN
SET _per ='%';
SET _like ='LIKE';
ELSEIF _table='T_DSS_CASHTABLE' THEN
SET _per ='';
SET _like ='=';
END IF;
IF _table='T_DSS_ACCVOUCH' OR _table='T_DSS_CASHTABLE' THEN
#替换D
SET _code=REPLACE(_code,'D',CONVERT(CONCAT(_per,''' THEN MD ELSE 0 END '),CHAR));
#替换C
SET _code=REPLACE(_code,'C',CONVERT(CONCAT(_per,''' THEN MC ELSE 0 END '),CHAR));
ELSEIF _table='T_DSS_ACCSUM' OR _table='T_DSS_ACCASS' THEN
#替换E
SET _code=REPLACE(_code,'E',CONVERT(CONCAT(_per,''' THEN ME ELSE 0 END '),CHAR));
#替换B
SET _code=REPLACE(_code,'B',CONVERT(CONCAT(_per,''' THEN MB ELSE 0 END '),CHAR));
END IF;
#替换+
SET _code=REPLACE(_code,'+',CONVERT(CONCAT(' )+SUM( CASE WHEN ',_field,' ',_like,' '''),CHAR));
#替换-
SET _code=REPLACE(_code,'-',CONVERT(CONCAT(' )-SUM( CASE WHEN ',_field,' ',_like,' '''),CHAR));
#替换+
SET _code=REPLACE(_code,'*',CONVERT(CONCAT(' )*SUM( CASE WHEN ',_field,' ',_like,' '''),CHAR));
#替换-
SET _code=REPLACE(_code,'/',CONVERT(CONCAT(' )/SUM( CASE WHEN ',_field,' ',_like,' '''),CHAR));
#拼接前后的SQL 至完整的列
SET _code=CONVERT(CONCAT('SUM( CASE WHEN ',_field,' ',_like,' ''',_code, ' ) AS FEE'),CHAR);
#拼接成完整的SQL
SET _code=CONVERT(CONCAT('SELECT ',_code, ' INTO @_fee FROM ',_table,' WHERE 1=1 '),CHAR); 
IF vYear <>'' THEN
SET _code=CONVERT(CONCAT(_code, ' AND IYEAR =', vYear),CHAR);
END IF;
IF vYm <>'' THEN
SET _code=CONVERT(CONCAT(_code, ' AND IYPERIOD =', vYm),CHAR);
END IF;
IF vDb <>'' THEN
SET _code=CONVERT(CONCAT(_code, ' AND LEFT(DB,2) =''', vDb, ''''),CHAR);
END IF;
IF vYmA <>'' THEN
SET _code=CONVERT(CONCAT(_code, ' AND IYPERIOD >=', vYmA),CHAR);
END IF;
IF vYmB <>'' THEN
SET _code=CONVERT(CONCAT(_code, ' AND IYPERIOD <=', vYmB),CHAR);
END IF;
    -- SELECT _code;
CALL USP_DSS_EXECUTE(_code);
ELSE
SET @_fee=0;
END IF;
-- SELECT @_fee,_code;
  SET vFee=@_fee;
END

IN `vTable` VARCHAR(40),IN `vField` VARCHAR(40),IN `vCode` VARCHAR(1000),IN `vYear` VARCHAR(4),IN `vYm` VARCHAR(6),IN `vDb` VARCHAR(2),IN `vYmA` VARCHAR(6),IN `vYmB` VARCHAR(6),OUT `vFee` decimal(20,2)

BEGIN
-- Author:        <bingbin.jiang>
-- Create date:   <2013-10-28>
-- Parameter:     <预处理SQL>
-- Description:   <预处理SQL>
  -- =============================================
-- History:  <v1:2013-10-28>
-- =============================================
SET @s=V_SQL;
PREPARE ss FROM @s;    -- 预加载sql
EXECUTE ss;            -- 执行
 DEALLOCATE PREPARE ss; -- 解除预加载
/*
eg：
建表：CREATE TABLE temp_person (number INT(11),name VARCHAR(255),birthday DATE)
    查询：select * from temp_person
删除：DROP TABLE temp_person
*/
END

IN `V_SQL` VARCHAR(4000)