MySql 自定义函数

drop function if exists f_get_costprice;
delimiter $$
CREATE FUNCTION `f_get_costprice`(`incrdcode` varchar(16),`amount` decimal(10,2),`balance` decimal(10,4), `incustid` int, `optype` int) RETURNS decimal(10,4)
NO SQL
BEGIN

DECLARE costPrice decimal(10,4);/*成本价*/

/*累计买入金额*/
declare totalInBalance decimal(10,4);

/*债权余额*/
declare crdAmount decimal(10,4);

/*累计卖出金额*/
declare totalOutBalance decimal(10,4);

/*本次买入金额*/
declare nowInBalance decimal(10,4);

/*本次卖出金额*/
declare nowOutBalance decimal(10,4);

select TotalBuyBalance into totalInBalance from custstock where crdcode=incrdcode and custid=incustid;
select holdamount into crdAmount from custstock where crdcode=incrdcode and custid=incustid;
select TotalSellBalance into totalOutBalance from custstock where crdcode=incrdcode and custid=incustid;

/*买入*/
IF(optype = 1) THEN
SET nowInBalance=balance;
SET nowOutBalance=0;
SET crdAmount=crdAmount+amount;

ELSE
SET nowInBalance=0;
SET nowOutBalance=balance;
SET crdAmount=crdAmount-amount;

END IF;



/*成本价 =（累计买入金额＋本次买入金额－累计卖出金额－本次卖出金额）/债权余额 )*/
SET costPrice = (IFNULL(totalInBalance,0)+IFNULL(nowInBalance,0)-IFNULL(totalOutBalance,0)-IFNULL(nowOutBalance,0))/crdAmount;

RETURN round(costPrice,4);

END $$
delimiter ;
ALTER FUNCTION f_get_costprice COMMENT '计算成本价(输入参数: 债权代码，债权数额，本次发生金额，客户ID，操作类型：1买入，2卖出)';