華閱文章網mysql里面的if語句,到底哪里錯了
mysql 里面， 變量不需要加 @ 這個符號的呀
mysql> DELIMITER //
mysql> CREATE PROCEDURE TestIfElse
-> (
-> p_val INT
-> )
-> BEGIN
-> IF (p_val = 1) THEN
-> SELECT '1' AS A;
-> ELSEIF (p_val = 2) THEN
-> SELECT '2' AS A;
-> ELSE
-> SELECT 'other' AS A;
-> END IF;
-> END//
Query OK, 0 rows affected (0.05 sec)
mysql> call TestIfElse(1);
-> //
+---+
| A |
+---+
| 1 |
+---+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> call TestIfElse(2);
-> //
+---+
| A |
+---+
| 2 |
+---+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> call TestIfElse(3);
-> //
+-------+
| A |
+-------+
| other |
+-------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql 觸發器 if 語句 update
@sqlz ='select asd from flkg';
EXECUTE(@sqlz);
----------
首先，mysql下動態語句不是這樣寫的，另外，觸發器里不能允許返回結果集的.
這里我是希望他能中斷更新！
---------------------
你的意思是不是要中斷后面的
"
SET amount =@amountmm
WHERE CODE=* AND whcode = * ;
"
這部分？
如果是的話，那這樣改下：
DELIMITER $$
USE `eerp`$$
DROP TRIGGER /*!50032 IF EXISTS */ `material_update`$$
CREATE TRIGGER `material_update` AFTER UPDATE ON `sf_material`
FOR EACH ROW BEGIN
IF *ng = 'Y' THEN
SET @amountmm = (SELECT * FROM warehouse WHERE CODE=* AND whcode = *);
IF @amountmm >=0 THEN
UPDATE warehouse
SET amount =@amountmm
WHERE CODE=* AND whcode = * ;
END IF ;
END IF;
END;
$$
DELIMITER ;
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
"我是要中斷更新。。不是后面。。。嚴格說就是這個表sf_material更新和后面的那個表warehouse更新也一起中斷！"
---------------------------------------------------------------
因為mysql下觸發器內不支持rollback，所以，變換一個方式來處理，如下：
那觸發器部分這么寫（注意：一定要使表TB_error_rollback在你的庫中不允許存在）：
CREATE TRIGGER `material_update`
BEFORE UPDATE
ON `sf_material`
FOR EACH ROW
BEGIN
IF *ng = 'Y' THEN
SET @amountmm = (SELECT amount- * FROM warehouse WHERE CODE=* AND whcode = *);
IF @amountmm <0 THEN
update TB_error_rollback set name=1;
END IF;
UPDATE warehouse
SET amount =@amountmm
WHERE CODE=* AND whcode = *;
END IF;
END;
