MYSQL:在插入时使用语法创建过程错误

Question

所以我试图在使用过程插入数据库之前计算总成本。整个想法是检查延迟付款是否为空然后不为空以及值是否为 0。

到目前为止我有这个:

DELIMITER;;
    CREATE OR REPLACE PROCEDURE add_transaction
    (IN accTransactionId INT(11), IN auctionId INT(11), IN chittyAccNo INT(11), IN `date` DATETIME, IN amount DOUBLE, IN description VARCHAR(50),
    IN transref VARCHAR(50), IN tranStat TINYINT, IN clearDate DATETIME, IN methodPaid DATETIME)
    BEGIN
        DECLARE latePayment int(11);
        DECLARE total AS DOUBLE;

        SELECT `LatePaymentFee`
        INTO `latePayment`
        FROM chittyusers
        WHERE ChittyAccNo = NEW.ChittyAccNo;

        CASE 
            WHEN latePayment IS NULL 
        THEN
            SET total = amount ;

            WHEN latePayment IS NOT NULL
        THEN
            SET total = amount + latePayment;
        ELSE
            SET total = amount;
        END;
        END CASE;

        INSERT INTO `chittytransactions` (`ChittyTransactionID`, `AuctionID`, `ChittyAccNo`, `Date`, `Amount`,
         `Description`, `TransRefence`, `TransStatus`, `ClearanceDate`, `PaymentMethod`)
        VALUES (accTransactionId, auctionId, chittyAccNo, `date`, amount, description, transref, tranStat, clearDate, methodPaid)

    END;;
DELIMiTER;

当我尝试创建这个过程时，出现了 sql 语法错误:

MySQL said: Documentation

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 5

我做错了什么，可以改变什么。刚开始使用这些，所以我还不完全了解它们是如何工作的。

此外，如果您需要数据库，就是这样:

    CREATE TABLE `chittytransactions` (
  `ChittyTransactionID` int(11) NOT NULL,
  `AuctionID` int(11) NOT NULL,
  `ChittyAccNo` int(11) DEFAULT NULL,
  `Date` datetime DEFAULT NULL,
  `Amount` double DEFAULT NULL,
  `Description` varchar(50) DEFAULT NULL,
  `TransRefence` varchar(50) DEFAULT NULL COMMENT 'Reference from actual Bank transaction',
  `TransStatus` tinyint(1) DEFAULT NULL COMMENT 'If Transaction Pending or Cleared',
  `ClearanceDate` datetime DEFAULT NULL,
  `PaymentMethod` int(1) DEFAULT NULL COMMENT '0- Cash, 1- bank transfer, 2- personal credit etc'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `chittyusers` (
  `ChittyAccNo` int(11) NOT NULL,
  `UserId` int(11) NOT NULL,
  `ChittyID` int(11) NOT NULL,
  `LatePaymentFee` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

谢谢任何帮助都会很棒

Answer 1

• 需要一个分隔符
• “创建或替换”似乎不被接受
• 在total变量声明中多了一个“as”
• 在“end case”之前多了一个“end”
• 最后插入的末尾少了一个分号

这似乎被接受了:

drop procedure if exists add_transaction;
delimiter $$
CREATE PROCEDURE add_transaction
(IN accTransactionId INT(11), IN auctionId INT(11), IN chittyAccNo INT(11), IN `date` DATETIME, IN amount DOUBLE, IN description VARCHAR(50),
IN transref VARCHAR(50), IN tranStat TINYINT, IN clearDate DATETIME, IN methodPaid DATETIME)
BEGIN
    DECLARE latePayment int(11);
    DECLARE total DOUBLE;

    SELECT `LatePaymentFee`
    INTO `latePayment`
    FROM chittyusers
    WHERE ChittyAccNo = NEW.ChittyAccNo;

    CASE 
        WHEN latePayment IS NULL 
    THEN
        SET total = amount ;

        WHEN latePayment IS NOT NULL
    THEN
        SET total = amount + latePayment;
    ELSE
        SET total = amount;
    END CASE;

    INSERT INTO `chittytransactions` (`ChittyTransactionID`, `AuctionID`, `ChittyAccNo`, `Date`, `Amount`,
     `Description`, `TransRefence`, `TransStatus`, `ClearanceDate`, `PaymentMethod`)
    VALUES (accTransactionId, auctionId, chittyAccNo, `date`, amount, description, transref, tranStat, clearDate, methodPaid);

END;
$$