MySQL自定义存储过程

BEGIN

DECLARE customerId Long;#客户ID
DECLARE totalBuy LONG;#当前购买产品数
DECLARE cumTotalBuy LONG;#累计购买产品数
DECLARE redemptionPro LONG;#已到期赎回产品数
DECLARE expiredPro LONG;#当天到期产品数
DECLARE witAmount double(18,6) DEFAULT 0;#申请提现金额
  DECLARE assetsid long;

## DECLARE reqredeemAmount double(18,6) ;#申请赎回金额
DECLARE reqredeemPro LONG;#申请赎回数量
DECLARE done INT DEFAULT 0;#游标执行标识符
DECLARE buyRecordId LONG;#客户购买记录ID
DECLARE v_err_op VARCHAR(1); #异常信息标识符
DECLARE v_err_msg VARCHAR(255); #错误信息
DECLARE cur_buyRecord  cursor  for  select customer.id,customer.BUY_RECORD_ID,ASSETS_ID from T_CUSTOM customer,T_BIND_BANK custbank 
                                            where customer.BIND_BANK_ID  = custbank.ID ;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;


open cur_buyRecord;

loop_curBuyRecord:loop

FETCH cur_buyRecord INTO customerId,buyRecordId,assetsid;


if done=1 then 

leave loop_curBuyRecord; 
end if;
##########################################当前购买产品数###########################################################

SELECT IFNULL(COUNT(*),0) INTO totalBuy FROM T_TRAN_RECORDS WHERE TRADE_TYPE='CUSTOMBOUGHY' AND CUSTOM_ID=customerId;

##########################################累计购买产品数###########################################################

SELECT IFNULL(COUNT(*),0) INTO cumTotalBuy FROM T_TRAN_RECORDS WHERE ( TRADE_TYPE='CUSTOMBOUGHY' OR  TRADE_TYPE='CUSTOMERREQREDEEM' or  TRADE_TYPE='CUSTOMERREDEEM') AND CUSTOM_ID=customerId;

##########################################已到期赎回产品数###########################################################

SELECT IFNULL(COUNT(*),0) INTO redemptionPro FROM T_TRAN_RECORDS WHERE TRADE_TYPE='CUSTOMERREDEEM' AND CUSTOM_ID=customerId;

##########################################当天到期产品数###########################################################

SELECT IFNULL(COUNT(*),0) INTO expiredPro FROM T_TRAN_RECORDS tran,T_PRODUCT p WHERE p.PROD_TYPE_ID = 1 and p.DEADLIME=To_Days(NOW())-To_Days(tran.OCC_DATE) and tran.PRODUCT_ID=p.ID and tran.CUSTOM_ID =customerId and tran.TRADE_TYPE='CUSTOMBOUGHY';

##########################################申请提现金额#############################################################

SELECT SUM(IFNULL(TRADE_AMOUNT,0)) INTO witAmount FROM T_TRAN_RECORDS WHERE  TRADE_TYPE='CUSTOMERREQWITHDRAW' AND CUSTOM_ID=customerId  and ( WITHDRAWFLAG = '0'  or WITHDRAWFLAG is null ) ;

                        ##########################################申请赎回金额#####################################################

##SELECT SUM(IFNULL(TRADE_AMOUNT,0)) INTO reqredeemAmount FROM T_TRAN_RECORDS WHERE  TRADE_TYPE='CUSTOMERREQREDEEM' AND CUSTOM_ID=customerId;

##########################################申请赎回数量##########################################

SELECT IFNULL(COUNT(*),0) INTO reqredeemPro FROM T_TRAN_RECORDS WHERE TRADE_TYPE='CUSTOMERREQREDEEM' AND CUSTOM_ID=customerId;


###################################################witAmount如果交易表中状态是提现 ，那么  资材表的 闲置金额 以及总资产 减去 提现金额
  if  witAmount > 0 then
update  T_CUSTOM_ASSETS set TOTAL_AMOUNT = TOTAL_AMOUNT - witAmount ,ASSETS_AMOUNT = ASSETS_AMOUNT - witAmount
  where     T_CUSTOM_ASSETS.ID = assetsid ;

  update  T_TRAN_RECORDS  set T_TRAN_RECORDS.WITHDRAWFLAG = '1'   WHERE  T_TRAN_RECORDS.TRADE_TYPE='CUSTOMERREQWITHDRAW' AND T_TRAN_RECORDS.CUSTOM_ID=customerId;

  end if;



######################################################

#验证用户购买记录信息是否存在

if ISNULL( expiredPro) THEN
SET expiredPro=0;

END IF;

if ISNULL( witAmount) THEN
SET witAmount=0;
END IF;
/*
if ISNULL( reqredeemAmount) THEN
SET reqredeemAmount=0;
END IF;
*/

if ISNULL(buyRecordId) THEN

set v_err_msg = 'insert';

INSERT into T_CUSTOM_BUY_RECORD(TOTAL_BUY,CUM_TOTAL_BUY,REDEMPTION_PRO,EXPIRED_PRO,WIT_AMOUNT,REQREDEEM_PRO,UPDATE_DATE)
VALUES(totalBuy,cumTotalBuy,redemptionPro,expiredPro,witAmount,reqredeemPro,NOW());



SELECT max(id) into buyRecordId from T_CUSTOM_BUY_RECORD;

UPDATE T_CUSTOM SET BUY_RECORD_ID=buyRecordId where ID = customerId;

ELSE

set v_err_msg = 'update';

UPDATE T_CUSTOM_BUY_RECORD set TOTAL_BUY=totalBuy,
CUM_TOTAL_BUY=cumTotalBuy,
REDEMPTION_PRO=redemptionPro,
EXPIRED_PRO=expiredPro,
WIT_AMOUNT=witAmount,
REQREDEEM_PRO=reqredeemPro,
UPDATE_DATE=NOW()
where ID=buyRecordId;
END IF;

commit;
END  loop;

CLOSE cur_buyRecord;

END