mysql - 在 MySQL : #1064 - You have an error in your SQL syntax; 中创建过程

Question

我在 phpmyadmin 中创建一个 mySQL 过程，它一直抛出这个错误:

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

我已经尝试了下面带有和不带有定界符的代码，但没有用。谁能指出这段代码有什么问题？

DELIMITER \\
CREATE PROCEDURE ORDERBOOKS(IN store VARCHAR(10), IN title VARCHAR(50))
BEGIN
    DECLARE order_num VARCHAR(100); 
    DECLARE tod DATE;
    SELECT @ordered_qty := qty FROM customer_sales WHERE customer_sales.store_id = store AND customer_sales.title_id = title;
    SELECT @in_stock := qty FROM store_inventories WHERE store_inventories.stor_id=store AND store_inventories.title_id = title;
    SELECT @threshold := minStock FROM store_inventories WHERE store_inventories.stor_id=store AND store_inventories.title_id = title;
    SET order_num = CONCAT(store, title);
    SET tod = GETDATE();
    IF (@ordered_qty < (@in_stock - @threshold)) THEN UPDATE store_inventories SET qty = (@in_stock - @ordered_qty) WHERE store_inventories.stor_id = store and store_inventories.title_id = title;
    ELSE
        INSERT INTO pending_orders(stor_id, ord_num, title_id, qty, date, fulfilled) VALUES(store, order_num, title, (@ordered_qty + @threshold), tod ,1);
        INSERT INTO sales VALUES(store, order_num, tod);
        INSERT INTO salesdetail VALUES(store, order_num, title , (@ordered_qty + @threshold),0);
        UPDATE pending_orders SET fulfilled=0 WHERE pending_orders.stor_id = store AND pending_orders.title_id = title;
        UPDATE store_inventories SET qty = (@threshold + @in_stock) WHERE store_inventories.stor_id = store and store_inventories.title_id= title;
        DELETE FROM pending_orders WHERE pending_orders.stor_id = store AND pending_orders.title_id = title;
END\\

DELIMITER ;

Answer 1

试试这个:

CREATE PROCEDURE ORDERBOOKS(IN store VARCHAR(10), IN title VARCHAR(50))
BEGIN
    DECLARE order_num VARCHAR(100); 
    DECLARE tod DATE;
    SELECT @ordered_qty := qty FROM customer_sales WHERE customer_sales.store_id = store AND customer_sales.title_id = title;
    SELECT @in_stock := qty FROM store_inventories WHERE store_inventories.stor_id=store AND store_inventories.title_id = title;
    SELECT @threshold := minStock FROM store_inventories WHERE store_inventories.stor_id=store AND store_inventories.title_id = title;
    SET order_num = CONCAT(store, title);
    SET tod = GETDATE();
    IF (@ordered_qty < (@in_stock - @threshold)) THEN UPDATE store_inventories SET qty = (@in_stock - @ordered_qty) WHERE store_inventories.stor_id = store and store_inventories.title_id = title;
    ELSE
        INSERT INTO pending_orders(stor_id, ord_num, title_id, qty, date, fulfilled) VALUES(store, order_num, title, (@ordered_qty + @threshold), tod ,1);
        INSERT INTO sales VALUES(store, order_num, tod);
        INSERT INTO salesdetail VALUES(store, order_num, title , (@ordered_qty + @threshold),0);
        UPDATE pending_orders SET fulfilled=0 WHERE pending_orders.stor_id = store AND pending_orders.title_id = title;
        UPDATE store_inventories SET qty = (@threshold + @in_stock) WHERE store_inventories.stor_id = store and store_inventories.title_id= title;
        DELETE FROM pending_orders WHERE pending_orders.stor_id = store AND pending_orders.title_id = title;
    END IF
END