mysql 完整的存储过程

最新推荐文章于 2022-07-29 18:51:30 发布

rsredwolf

最新推荐文章于 2022-07-29 18:51:30 发布

阅读量6.4k

点赞数

分类专栏： MySQL 文章标签：存储 mysql insert table 2010

本文链接：https://blog.csdn.net/rsredwolf/article/details/7580777

版权

MySQL 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

完整的存储过程(写的注释发不出来可能是加了'//'的原因吧)

DROP PROCEDURE if EXISTS pro_rep;

delimiter $$

create PROCEDURE pro_rep(s varchar(6), e varchar(6), d varchar(20))
begin

declare rep_id int;

declare rep_JournalNumber       varchar(20);

declare rep_RcName              varchar(10);

declare rep_HouseNumber          varchar(100);

declare rep_HCName               varchar(10);

declare rep_OwnerName            varchar(50);

declare rep_FeeYearMonth         varchar(6);

declare rep_EfsNum               decimal(10,0);

declare rep_EfeNum               decimal(10,0);

declare rep_EssNum               decimal(10,0);

declare rep_EseNum               decimal(10,0);

declare rep_EtsNum               decimal(10,0);

declare rep_EteNum               decimal(10,0);

declare rep_Etotal               decimal(10,0);

declare rep_EPrice               decimal(10,4);

declare rep_WfsNum               decimal(10,0);

declare rep_WfeNum               decimal(10,0);

declare rep_WssNum               decimal(10,0);

declare rep_WseNum               decimal(10,0);

declare rep_WtsNum               decimal(10,0);

declare rep_WteNum               decimal(10,0);

declare rep_Wtotal               decimal(10,0);

declare rep_WPrice               decimal(10,2);

declare rep_Dkm                  decimal(10,2);

declare rep_Dkmje                decimal(15,2);

declare rep_HouseArea             decimal(10,4);

declare rep_PropertyExpensePrice decimal(10,2);

declare rep_Wyje                 decimal(15,2);

declare rep_LateFee               decimal(15,2);

declare rep_UltimoBalance         decimal(15,2);

declare rep_InstantBalance       decimal(15,2);

declare rep_AmountReceivable    decimal(15,2);

declare rep_RealReceivable       decimal(15,2);

declare rep_RepairFee             decimal(15,2);

declare rep_OtherFee              decimal(15,2);

declare sum_EfsNum               decimal(10,0) default 0;

declare sum_EfeNum               decimal(10,0) default 0;

declare sum_EssNum               decimal(10,0) default 0;

declare sum_EseNum               decimal(10,0) default 0;

declare sum_EtsNum               decimal(10,0) default 0;

declare sum_EteNum               decimal(10,0) default 0;

declare sum_Etotal               decimal(10,0) default 0;

declare sum_WfsNum               decimal(10,0) default 0;

declare sum_WfeNum               decimal(10,0) default 0;

declare sum_WssNum               decimal(10,0) default 0;

declare sum_WseNum               decimal(10,0) default 0;

declare sum_WtsNum               decimal(10,0) default 0;

declare sum_WteNum               decimal(10,0) default 0;

declare sum_Wtotal               decimal(10,0) default 0;

declare sum_Dkm                  decimal(10,2) default 0.00;

declare sum_Dkmje                decimal(15,2) default 0.00;

declare sum_HouseArea             decimal(10,4) default 0.0000;

declare sum_Wyje                 decimal(15,2) default 0.00;

declare sum_LateFee               decimal(15,2) default 0.00;

declare sum_UltimoBalance         decimal(15,2) default 0.00;

declare sum_InstantBalance       decimal(15,2) default 0.00;

declare sum_AmountReceivable    decimal(15,2) default 0.00;

declare sum_RealReceivable       decimal(15,2) default 0.00;

declare sum_RepairFee             decimal(15,2) default 0.00;

declare sum_OtherFee              decimal(15,2) default 0.00;

declare rep_count int default 0;

declare stop int default 0;

declare cur_rep cursor
for
select Id,JournalNumber,RcName,HouseNumber,HCName,OwnerName,FeeYearMonth,
EfsNum,EfeNum,EssNum,EseNum,EtsNum,EteNum,Etotal,EPrice,

WfsNum,WfeNum,WssNum,WseNum,WtsNum,WteNum,Wtotal,WPrice,
Dkm,Dkmje,HouseArea,PropertyExpensePrice,Wyje,
LateFee,UltimoBalance,InstantBalance,AmountReceivable,RealReceivable,RepairFee,OtherFee
from detailstatement
where FeeYearMonth between s and e
and (housenumber like CONCAT('%区',d,'号%'));

declare CONTINUE HANDLER FOR SQLSTATE '02000' SET stop=1;

open cur_rep;

fetch cur_rep into
rep_id,rep_JournalNumber,rep_RcName,rep_HouseNumber,rep_HCName,rep_OwnerName,rep_FeeYearMonth,
rep_EfsNum,rep_EfeNum,rep_EssNum,rep_EseNum,rep_EtsNum,rep_EteNum,rep_Etotal,rep_EPrice,
rep_WfsNum,rep_WfeNum,rep_WssNum,rep_WseNum,rep_WtsNum,rep_WteNum,rep_Wtotal,rep_WPrice,
rep_Dkm,rep_Dkmje, rep_HouseArea,rep_PropertyExpensePrice,rep_Wyje,rep_LateFee, rep_UltimoBalance,
rep_InstantBalance,rep_AmountReceivable,rep_RealReceivable,rep_RepairFee,rep_OtherFee;

while stop<>1 do

select rep_id,rep_JournalNumber,rep_RcName,rep_HouseNumber,rep_HCName,rep_OwnerName,rep_FeeYearMonth,
rep_EfsNum,rep_EfeNum,rep_EssNum,rep_EseNum,rep_EtsNum,rep_EteNum,rep_Etotal,rep_EPrice,
rep_WfsNum,rep_WfeNum,rep_WssNum,rep_WseNum,rep_WtsNum,rep_WteNum,rep_Wtotal,rep_WPrice,

       rep_Dkm,rep_Dkmje,rep_HouseArea,rep_PropertyExpensePrice,rep_Wyje,
       rep_LateFee,rep_UltimoBalance,rep_InstantBalance,rep_AmountReceivable,
       rep_RealReceivable,rep_RepairFee,rep_OtherFee;

set rep_count = rep_count + 1 ;

set sum_EfsNum = sum_EfsNum + rep_EfsNum ;

set sum_EfeNum = sum_EfeNum + rep_EfeNum ;

set sum_EssNum = sum_EssNum + rep_EssNum ;

set sum_EseNum = sum_EseNum + rep_EseNum ;

set sum_EtsNum = sum_EtsNum + rep_EtsNum ;

set sum_EteNum = sum_EteNum + rep_EteNum ;

set sum_Etotal = sum_Etotal + rep_Etotal ;

set sum_WfsNum = sum_WfsNum + rep_WfsNum ;

set sum_WfeNum = sum_WfeNum + rep_WfeNum ;

set sum_WssNum = sum_WssNum + rep_WssNum ;

set sum_WseNum = sum_WseNum + rep_WseNum ;

set sum_WtsNum = sum_WtsNum + rep_WtsNum ;

set sum_WteNum = sum_WteNum + rep_WteNum ;

set sum_Wtotal = sum_Wtotal + rep_Wtotal ;

set sum_Dkm = sum_Dkm + rep_Dkm ;

set sum_Dkmje = sum_Dkmje + rep_Dkmje ;

set sum_HouseArea = sum_HouseArea + rep_HouseArea ;

set sum_Wyje = sum_Wyje + rep_Wyje ;

set sum_LateFee = sum_LateFee + rep_LateFee ;

set sum_UltimoBalance = sum_UltimoBalance + rep_UltimoBalance ;

set sum_InstantBalance = sum_InstantBalance + rep_InstantBalance ;

set sum_AmountReceivable = sum_AmountReceivable + rep_AmountReceivable ;

set sum_RealReceivable = sum_RealReceivable + rep_RealReceivable ;

set sum_RepairFee = sum_RepairFee + rep_RepairFee ;

set sum_OtherFee = sum_OtherFee + rep_OtherFee ;

insert into cx values(
rep_id,rep_JournalNumber,rep_RcName,rep_HouseNumber,rep_HCName,rep_OwnerName,rep_FeeYearMonth,
rep_EfsNum,rep_EfeNum,rep_EssNum,rep_EseNum,rep_EtsNum,rep_EteNum,rep_Etotal,rep_EPrice,

rep_WfsNum,rep_WfeNum,rep_WssNum,rep_WseNum,rep_WtsNum,rep_WteNum,rep_Wtotal,rep_WPrice,
rep_Dkm,rep_Dkmje,rep_HouseArea,rep_PropertyExpensePrice,rep_Wyje,rep_LateFee,rep_UltimoBalance,
rep_InstantBalance,rep_AmountReceivable,rep_RealReceivable,rep_RepairFee,rep_OtherFee);

end while;

insert into cx values(
'合计',rep_count ,'--','--','--','--','--',

sum_EfsNum,sum_EfeNum,sum_EssNum,sum_EseNum,sum_EtsNum,sum_EteNum,sum_Etotal,'--',
   sum_WfsNum,sum_WfeNum,sum_WssNum,sum_WseNum,sum_WtsNum,sum_WteNum,sum_Wtotal,'--',
   sum_Dkm,sum_Dkmje,sum_HouseArea,'--',sum_Wyje,sum_LateFee,sum_UltimoBalance,
   sum_InstantBalance,sum_AmountReceivable,sum_RealReceivable,sum_RepairFee,sum_OtherFee);

close cur_rep;

end;
$$

delimiter ;

drop procedure if exists pro_test;
delimiter $$
create procedure pro_test(s varchar(6),e varchar(6))
begin

declare rep_count int default 0;

declare rep_num varchar(20);

declare stop int default 0;

declare cur_rep1 cursor
for
select DISTINCT substring_index(substring(housenumber,5),'号',1)
from detailstatement
where FeeYearMonth between s and e ;

declare CONTINUE HANDLER FOR SQLSTATE '02000' SET stop=1;

open cur_rep1;

drop temporary table if exists cx;

CREATE temporary table if not exists cx (
Id                    varchar(20),
JournalNumber         varchar(20),
RcName                varchar(10),
HouseNumber           varchar(100),
HCName                varchar(10),
OwnerName             varchar(50),
FeeYearMonth          varchar(6),
EfsNum                varchar(20),
EfeNum                varchar(20),
EssNum                varchar(20),
EseNum                varchar(20),
EtsNum                varchar(20),
EteNum                varchar(20),
Etotal                varchar(20),
EPrice                varchar(20),
WfsNum                varchar(20),
WfeNum                varchar(20),
WssNum                varchar(20),
WseNum                varchar(20),
WtsNum                varchar(20),
WteNum                varchar(20),
Wtotal                varchar(20),
WPrice                varchar(20),
Dkm                   varchar(20),
Dkmje                 varchar(20),
HouseArea             varchar(20),
PropertyExpensePrice varchar(20),
Wyje                  varchar(20),
LateFee               varchar(20),
UltimoBalance         varchar(20),
InstantBalance        varchar(20),
AmountReceivable      varchar(20),
RealReceivable        varchar(20),
RepairFee             varchar(20),
OtherFee              varchar(20)
);

insert into cx values(
       '编号','单据编号','所属小区','房号','类型','姓名','所属年月',
      '一表','一表','二表','二表','三表','三表','用电量','电价',
      '一表','一表','二表','二表','三表','三表','用水量','水价',
      '电控门','电控金额','物业面积','物业单价','物业金额','滞纳金','上月结转','结转下月','本月应收',
      '本月实收','维修费用','其他费用'
);

fetch cur_rep1 into rep_num;

while stop<>1 do
call pro_rep(s,e,rep_num);
fetch cur_rep1 into rep_num;
end while;
close cur_rep1;

select count(Id),
       sum(EfsNum),sum(EfeNum),sum(EssNum),sum(EseNum),sum(EtsNum),sum(EteNum),sum(Etotal),
       sum(WfsNum),sum(WfeNum),sum(WssNum),sum(WseNum),sum(WtsNum),sum(WteNum),sum(Wtotal),
       sum(Dkm),sum(Dkmje),sum(HouseArea),sum(Wyje),
       sum(LateFee),sum(UltimoBalance),sum(InstantBalance),sum(AmountReceivable),
       sum(RealReceivable),sum(RepairFee),sum(OtherFee)
into rep_count,
       sum_EfsNum ,sum_EfeNum, sum_EssNum,sum_EseNum,sum_EtsNum,sum_EteNum ,sum_Etotal,

       sum_WfsNum ,sum_WfeNum,sum_WssNum,sum_WseNum,sum_WtsNum,sum_WteNum,sum_Wtotal,
       sum_Dkm,sum_Dkmje,sum_HouseArea,sum_Wyje,sum_LateFee,sum_UltimoBalance,
       sum_InstantBalance,sum_AmountReceivable,sum_RealReceivable,sum_RepairFee,sum_OtherFee
from cx where Id not in('合计','总计','编号') ;

insert into cx values(
'总计',rep_count,'--','--','--','--','--',
sum_EfsNum ,sum_EfeNum,sum_EssNum,sum_EseNum,sum_EtsNum,sum_EteNum,sum_Etotal,'--',

      sum_WfsNum ,sum_WfeNum,sum_WssNum,sum_WseNum,sum_WtsNum,sum_WteNum,sum_Wtotal,'--',
      sum_Dkm,sum_Dkmje,sum_HouseArea,'--',sum_Wyje,sum_LateFee,sum_UltimoBalance,
      sum_InstantBalance,sum_AmountReceivable,sum_RealReceivable,sum_RepairFee,sum_OtherFee
);

end;

$$
delimiter ;

call pro_test('201002','201002');

select Id,JournalNumber,RcName,HouseNumber,HCName,OwnerName,FeeYearMonth,
EfsNum,EfeNum,EssNum,EseNum,EtsNum,EteNum,Etotal,EPrice,

WfsNum,WfeNum,WssNum,WseNum,WtsNum,WteNum,Wtotal,WPrice,
Dkm,Dkmje,HouseArea,PropertyExpensePrice,Wyje,
LateFee,UltimoBalance,InstantBalance,AmountReceivable,RealReceivable,RepairFee,OtherFee from cx

into outfile 'd:\\河滨小区2010年02月汇总表.xls';

rsredwolf

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql 完整的存储过程

完整的存储过程(写的注释发不出来可能是加了'//'的原因吧)DROP PROCEDURE if EXISTS pro_rep;delimiter $$create PROCEDURE pro_rep(s varchar(6), e varchar(6), d varchar(20))begindeclare rep_id
复制链接

扫一扫

专栏目录