展开全部
CREATE PROCEDURE EVERYMONTH_CASHSEARCH(
in p_time datetime
)
begin
declare v_all_pay decimal(12,2) DEFAULT 0;
declare v_all_free decimal(12,2) DEFAULT 0;
declare v_all_transfer decimal(12,2) DEFAULT 0;
declare v_sys_account decimal(12,2) DEFAULT 0;
declare v_account_date datetime DEFAULT NULL;
declare v_start_time datetime DEFAULT NULL;
declare v_end_time datetime DEFAULT NULL;
set v_start_time=date_sub(date_sub(date_format(v_time,'%y-%m-%d'),interval extract(day from v_time)-1 day),interval 1 month);
set v_end_time=date_sub(date_sub(date_format(v_time,'%y-%m-%d'),interval extract(day from v_time)-1 day),interval 0 month) ;
set v_account_date=v_time;
select v_all_pay=ifnull(sum(AMOUNT),0.00) from T_USER_CASH where OPERATE_TYPE='0' and TRADE_STATUS='0' and UPDATE_TIME>=v_start_time and UPDATE_TIME
select v_all_transfer=ifnull(sum(AMOUNT),0.00) from T_USER_CASH where OPERATE_TYPE='1' and TRADE_STATUS='0' and UPDATE_TIME>=v_start_time and UPDATE_TIME
set v_all_free=0.00;
set v_sys_account=v_all_pay-v_all_free-v_all_transfer;
insert into T_SYS_ACCOUNT values(v_account_date,v_all_pay,v_all_transfer,v_sys_account,v_all_free,'3');
/**/
end