CREATE PROCEDURE `new_proc`(IN tableId1 INTEGER(11), IN tableId2 INTEGER(11))
NOT DETERMINISTIC
SQL SECURITY DEFINER
BEGIN
declare perNum,oId1,oId2 INTEGER;
select personNum into perNum from tb_Order as ot where
ot.tableId =tableId2
and ot.isPay = 0;
select id into oId1 from tb_Order as ot where
ot.tableId = tableId1
and ot.isPay = 0;
select id into oId2 from tb_Order as ot where
ot.tableId = tableId2
and ot.isPay = 0; update tb_Order
set personNum = personNum + perNum
where id = oId2;
update tb_OrderDetail set orderId = oId2 where orderId = oId1;
update tb_Table set flag = 0 where id = tableId1;
delete from tb_Order where id = oId1;END;创建了这样一个存储过程,可是在declare那句一直报错,不知道错在哪里,求指教!
DROP PROCEDURE IF EXISTS `new_proc`$$
CREATE PROCEDURE `new_proc`(IN tableId1 INT, IN tableId2 INT)
BEGIN
DECLARE perNum,oId1,oId2 INTEGER;
SELECT personNum INTO perNum FROM tb_Order AS ot WHERE
ot.tableId =tableId2
AND ot.isPay = 0;
SELECT id INTO oId1 FROM tb_Order AS ot WHERE
ot.tableId = tableId1
AND ot.isPay = 0;
SELECT id INTO oId2 FROM tb_Order AS ot WHERE
ot.tableId = tableId2
AND ot.isPay = 0; UPDATE tb_Order
SET personNum = personNum + perNum
WHERE id = oId2;
UPDATE tb_OrderDetail SET orderId = oId2 WHERE orderId = oId1;
UPDATE tb_Table SET flag = 0 WHERE id = tableId1;
DELETE FROM tb_Order WHERE id = oId1;END ;$$
DELIMITER ;