我想写一个mysql函数。。。但我得到的语法错误是
错误1064(42000):您的sql语法有错误;查看与您的mysql服务器版本对应的手册,以获得正确的语法来使用');
我的代码如下,
delimiter //
CREATE FUNCTION calculateNetItemStandardDiscountAmount(productId INT,quantity INT) RETURNS DOUBLE
BEGIN
DECLARE discountPer DOUBLE;
DECLARE unitPrice DOUBLE;
DECLARE netItemDiscountAmount DOUBLE;
DECLARE netItemAmount DOUBLE;
SELECT `SellingUnitPrice` into unitPrice
FROM `TBL_PRODUCT_MASTER`
WHERE `Id` = productId;
SET netItemAmount = quantity * unitPrice ;
insert into debugtable select concat('ACTUAL AMOUNT ', netItemAmount);
SELECT discDetail.`DiscountPercentage` into discountPer
FROM `TBL_DISCOUNT_DETAIL` AS discDetail , `TBL_DISCOUNT` AS disc, TBL_PRODUCT_DISCOUNT AS prodDisc
WHERE disc.`DiscountStartDate` < NOW()
AND disc.`DiscountEndDate` > NOW()
AND disc.`SchemeType` = 'AMOUNT'
AND discDetail.DiscountId = disc.Id
AND prodDisc.productId = productId
AND prodDisc.discountId = disc.Id
AND prodDisc.`IsEnabled` = 1
AND disc.`IsEnabled` = 1
AND (quantity * unitPrice) >= discDetail.LowerLimit and (quantity * unitPrice) <= discDetail.HigherLimit);
insert into debugtable select concat('STD DISCOUNT PER BY AMOUNT ', discountPer);
IF (discountPer IS NOT NULL) THEN
SET netItemDiscountAmount = ((netItemAmount) * (discountPer * 0.01));
insert into debugtable select concat('APPLY VOLUME DISCOUNT ', netItemDiscountAmount);
END IF;
SELECT discDetail.`DiscountPercentage` into discountPer
FROM `TBL_DISCOUNT_DETAIL` AS discDetail , `TBL_DISCOUNT` AS disc, TBL_PRODUCT_DISCOUNT AS prodDisc
WHERE disc.`DiscountStartDate` < NOW()
AND disc.`DiscountEndDate` > NOW()
AND disc.`SchemeType` = 'VOLUME'
AND discDetail.DiscountId = disc.Id AND prodDisc.productId = productId
AND prodDisc.discountId = disc.Id
AND prodDisc.`IsEnabled` = 1
AND disc.`IsEnabled` = 1
AND (Quantity >= discDetail.LowerLimit and Quantity <= discDetail.HigherLimit);
insert into debugtable select concat('STD DISCOUNT PER BY VOLUME ', discountPer);
IF (discountPer IS NOT NULL) THEN
SET netItemDiscountAmount = netItemDiscountAmount + ((netItemAmount) * (discountPer * 0.01));
insert into debugtable select concat('APPLY AMOUNT DISCOUNT ', netItemDiscountAmount);
END IF;
RETURN netItemDiscountAmount;
END//
delimiter ;
任何帮助都将不胜感激。。。提前谢谢。。。
1条答案
按热度按时间yvt65v4c1#
看起来这行的末尾有未打开的括号