错误 1064 (42000)： SQL 语法;检查与您的 MySQL 服务器版本对应的手册，了解在"附近使用的正确语法");

Question

我正在尝试编写一个 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 ;

任何帮助将不胜感激...提前感谢...

Answer 1

看起来您在此行的末尾有未打开的括号

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)