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 之后