CREATE PROCEDURE P_UpdateLRights
(
pBrokerID VARCHAR(50),
pProductID VARCHAR(50),
pCustomerID VARCHAR(50),
pOpenTime DATE,
OUT ret INT
)
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
SET ret = -1;
END; START TRANSACTION; INSERT INTO M_Product_Customer_His
(ProductID, BrokerID, CustomerID, OpenTime, UpdateTime)
VALUES
(pProductID, pBrokerID, pCustomerID, pOpenTime, NOW()); UPDATE FROM t1
USING m_product_customer t1
INNER JOIN t_product t2 ON t1.ProductID = t2.ID
SET
t1.Deadline = date_add(t1.Deadline, interval pOpenTime day),
t1.UpdateTime = NOW()
WHERE t1.ProductID = pProductID
AND t1.CustomerID = pCustomerID AND t1.BrokerID = pBrokerID; UPDATE m_product_broker t1
SET
t1.AssignedSum = t1.AssignedSum + 1,
t1.UpdateTime = NOW()
WHERE t1.BrokerID = pProductID; COMMIT;
SET ret = 1;
END;
(
pBrokerID VARCHAR(50),
pProductID VARCHAR(50),
pCustomerID VARCHAR(50),
pOpenTime DATE,
OUT ret INT
)
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
SET ret = -1;
END; START TRANSACTION; INSERT INTO M_Product_Customer_His
(ProductID, BrokerID, CustomerID, OpenTime, UpdateTime)
VALUES
(pProductID, pBrokerID, pCustomerID, pOpenTime, NOW()); UPDATE FROM t1
USING m_product_customer t1
INNER JOIN t_product t2 ON t1.ProductID = t2.ID
SET
t1.Deadline = date_add(t1.Deadline, interval pOpenTime day),
t1.UpdateTime = NOW()
WHERE t1.ProductID = pProductID
AND t1.CustomerID = pCustomerID AND t1.BrokerID = pBrokerID; UPDATE m_product_broker t1
SET
t1.AssignedSum = t1.AssignedSum + 1,
t1.UpdateTime = NOW()
WHERE t1.BrokerID = pProductID; COMMIT;
SET ret = 1;
END;
CREATE PROCEDURE xxx AS
BEGIN
foo1;
foo2;
foo3;
END;
在建存储过程的时候,这一整句是单独一句SQL。
在执行xxx()的时候,foo1~foo3是各自独立的3句,要区别这两个不同的分割符(CREATE的结束,和运行时,每句的结束),
DELIMITER $CREATE PROCEDURE xxx AS
BEGIN
foo1;
foo2;
foo3;
END$DELIMITER;
亦即,用$代表CREATE的结束,存储过程运行时每句的结尾,还是使用分号。
DELIMITER $$
CREATE PROCEDURE P_UpdateLRights
(
pBrokerID VARCHAR(50),
pProductID VARCHAR(50),
pCustomerID VARCHAR(50),
pOpenTime DATE,
OUT ret INT
)
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
SET ret = -1;
END; START TRANSACTION; INSERT INTO M_Product_Customer_His
(ProductID, BrokerID, CustomerID, OpenTime, UpdateTime)
VALUES
(pProductID, pBrokerID, pCustomerID, pOpenTime, NOW()); UPDATE m_product_customer t1
INNER JOIN t_product t2 ON t1.ProductID = t2.ID
SET
t1.Deadline = DATE_ADD(t1.Deadline, INTERVAL pOpenTime DAY),
t1.UpdateTime = NOW()
WHERE t1.ProductID = pProductID
AND t1.CustomerID = pCustomerID AND t1.BrokerID = pBrokerID; UPDATE m_product_broker t1
SET
t1.AssignedSum = t1.AssignedSum + 1,
t1.UpdateTime = NOW()
WHERE t1.BrokerID = pProductID; COMMIT;
SET ret = 1;
END;$$
DELIMITER ;
BEGIN
ROLLBACK;
SET ret = -1;
EN' at line 26
mysql> DROP PROCEDURE IF EXISTS P_UpdateLRights$$
Query OK, 0 rows affected (0.00 sec)mysql> CREATE PROCEDURE P_UpdateLRights
-> (
-> pBrokerID VARCHAR(50),
-> pProductID VARCHAR(50),
-> pCustomerID VARCHAR(50),
-> pOpenTime DATE,
-> OUT ret INT
-> )
-> BEGIN
-> DECLARE EXIT HANDLER FOR SQLEXCEPTION
-> BEGIN
-> ROLLBACK;
-> SET ret = -1;
-> END;
->
-> START TRANSACTION;
->
-> INSERT INTO M_Product_Customer_His
-> (ProductID, BrokerID, CustomerID, OpenTime, UpdateTime)
-> VALUES
-> (pProductID, pBrokerID, pCustomerID, pOpenTime, NOW());
->
-> UPDATE m_product_customer t1
-> INNER JOIN t_product t2 ON t1.ProductID = t2.ID
-> SET
-> t1.Deadline = DATE_ADD(t1.Deadline, INTERVAL pOpenTime DAY),
-> t1.UpdateTime = NOW()
-> WHERE t1.ProductID = pProductID
-> AND t1.CustomerID = pCustomerID AND t1.BrokerID = pBrokerID;
->
-> UPDATE m_product_broker t1
-> SET
-> t1.AssignedSum = t1.AssignedSum + 1,
-> t1.UpdateTime = NOW()
-> WHERE t1.BrokerID = pProductID;
->
-> COMMIT;
-> SET ret = 1;
-> END;$$
Query OK, 0 rows affected (0.00 sec)mysql> DELIMITER ;
mysql>
delimiter $ 否则当MYSQL命令行工具看到第一个;就直接提交语句,这时你的语句当然还是不完整的。