# MySQL function and do ifelse logic calculation

goodlook0123 2022-02-13 08:17:16 阅读数:124

mysql function ifelse logic calculation

Why use functions ：

1、 Functions allow standard component-based programming , Improved SQL Statement reusability 、 Sharing and portability

2、 Functions can be used as a security mechanism .

3、 Function can achieve faster execution speed , Can reduce network traffic ( Compile multiple times at a time ).

``````CREATE DEFINER=`root`@`%` FUNCTION `arbitrationFees`(a decimal) RETURNS decimal(65,2)
BEGIN
DECLARE _k decimal(65,2) DEFAULT 0.00;
DECLARE _v decimal(65,2) DEFAULT 0.00;
DECLARE _total decimal(65,2) DEFAULT 0.00;
DECLARE _interest_rate decimal(65,2) DEFAULT 0.7;
DECLARE _op_rate decimal(65,3) DEFAULT 0.105;
if a < 1000 THEN
set _k = 49;
set _v = 210;
ELSEIF (a<50000 AND a>=1000) THEN
set _k = (70 + (a - 1000) * 0.045) * _interest_rate;
set _v = 210;
ELSEIF (a<100000 AND a>=50000) THEN
set _k = (2275 + (a - 50000) * 0.035) * _interest_rate;
set _v = _k * 0.105;
ELSEIF (a<200000 AND a>=100000) THEN
set _k = (4025 + (a - 100000) * 0.025) * _interest_rate;
set _v = _k * 0.105;
ELSEIF (a<500000 AND a>=200000) THEN
set _k = (6525 + (a - 200000) * 0.015) * _interest_rate;
set _v = _k * 0.105;
ELSEIF (a<1000000 AND a>=500000) THEN
set _k = (11025 + (a - 500000) * 0.007) * _interest_rate;
set _v = _k * 0.105;
ELSEif a>=1000000 THEN
set _k = (14525 + (a - 1000000) * 0.004) * _interest_rate;
set _v = _k * _op_rate;
END IF;
set _total = convert((_k + _v),decimal(65,2));
return _total;
END``````