mysql存储过程写法

drop procedure if exists sp_statis_subject_mouth;

CREATE PROCEDURE sp_statis_subject_mouth(IN month int)
begin

/**科目id**/
DECLARE _uid decimal(19,0);
/**借总额**/
DECLARE _addTotalMoney decimal(19,4);
/**贷总额**/
DECLARE _reduceTotalMoney decimal(19,4);
/** 余额 **/
DECLARE _totalMoney decimal(19,4);

/** 开始时间 **/
DECLARE _startTime datetime;

/** 结束时间 **/
DECLARE _endTime datetime;

/** 按月份 计算开始时间,结束时间 **/
/** 年份 **/
Declare _year int;
/**开始日期**/
Declare startDayStr varchar(50);
/**结束日期**/
Declare endDayStr varchar(50);
DECLARE no_more_departments INT DEFAULT 0;

/**遍历所有的科目 **/
declare baseInfoCur CURSOR FOR
select uid from bas_base_info;

/** 如果希望应用获得异常，需要将下面这一句，以及启动事务和提交事务的语句全部去掉 **/
declare exit handler for sqlexception rollback;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_departments=1;

SET no_more_departments=0;
set _year = DATE_FORMAT(now(),'%Y');
set _startTime = STR_TO_DATE(concat(_year,'-',month,'-1'),'%Y-%m-%d');
set _endTime = STR_TO_DATE(LAST_DAY(_startTime),'%Y-%m-%d');


/** 清理原来的数据 **/
delete from FIN_ACCOUNT_SETTLEMENT where SETTLEMENT_MONTH = _startTime;

/**打开游标**/
open baseInfoCur;

/***读取游标数据***/
FETCH baseInfoCur INTO _uid;
while no_more_departments <> 1 do

/** 获取科目 借类型的总金额 **/
set _addTotalMoney = ( select sum(balance) from FIN_VOUCHER_DETAIL where IN_OR_OUT = 1 and SUBJECT_TYPE =_uid and GMT_CREATE>=_startTime and GMT_CREATE<=_endTime );
/** 获取科目 贷类型的总金额 **/
set _reduceTotalMoney = ( select sum(balance) from FIN_VOUCHER_DETAIL where IN_OR_OUT = 2 and SUBJECT_TYPE = _uid and GMT_CREATE>=_startTime and GMT_CREATE<=_endTime );

/**余额**/
set _totalMoney = _addTotalMoney - _reduceTotalMoney;
/**select _addTotalMoney,_reduceTotalMoney ,_totalMoney; **/
if _totalMoney is null then
set _totalMoney =0 ;
end if;



/** 插入到数据库中 **/
insert into FIN_ACCOUNT_SETTLEMENT (SETTLEMENT_MONTH,MONEY,STATUS,GMT_CREATE,SUBJECT_ID)
values (_startTime,_totalMoney,1,now(),_uid);

FETCH baseInfoCur INTO _uid;
end while;

CLOSE baseInfoCur;
/**运行没有异常，提交事务 **/
commit;
/** DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_departments=1; **/


end;


call sp_statis_subject_mouth(1);

select count(*) from FIN_ACCOUNT_SETTLEMENT ;


------------------------------------------------------------------------
规则 set设值操作, 都要写在declared 之后