華閱文章網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;。
轉載請注明出處華閱文章網 » mysqlif條件語句
