mysql預存程序和觸發器的應用

標籤：

***********【mysql 預存程序和觸發器 -- 別安駒】*********************

1、什麼情況下使用預存程序？

完成一些比較麻煩的邏輯，比如多表在mysql端的cpu很閒置情況下，用預存程序是不錯的選擇，

1.1、簡單的預存程序樣本：簡單寫入

DELIMITER $$USE `curl_test`$$DROP PROCEDURE IF EXISTS `data_s`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `data_s`()BEGINDECLARE i INT DEFAULT 0;WHILE i < 1000 DOINSERT INTO album(`artist_id`,`name`,`url`)VALUES(i,‘別安駒‘,‘2015-10-01‘);SET i = i + 1;END WHILE;END$$DELIMITER ;

 

1.2、簡單的預存程序樣本：關聯更新

DROP PROCEDURE IF EXISTS testProcedure;CREATE PROCEDURE testProcedure()BEGINDECLARE flag INT DEFAULT 0;DECLARE tID INT;DECLARE tDept CHAR(255);DECLARE tAlias CHAR(20);DECLARE cur CURSOR FOR SELECT id,dept FROM users;DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag = 1;OPEN cur;FETCH cur INTO tID,tDept;WHILE flag<>1 DOSELECT alias FROM dept WHERE name = tDept INTO tAlias;UPDATE users SET dept_alias=tAlias WHERE id=tID;FETCH cur INTO tID,tDept;END WHILE;CLOSE cur;END

 

預存程序的優點：

先行編譯，相對於直接的SQL效率會高點，同時可以降低SQL語句傳輸過程中消耗的流量；

簡化商務邏輯，可以把需求轉化給專業的DBA(如果有的話)；

更方便的使用MySQL資料庫事物的處理，尤其是購物類網站；

安全、使用者權限更容易管理；

修改預存程序基本上不需要修改程式碼，而直接寫SQL修改SQL一般都要修改相關的程式；

2、mysql觸發器

觸發器(trigger)：監視某種情況，並觸發某種操作。

觸發器建立文法四要素：

2.1.監視地點(table)

2.2.監視事件(insert/update/delete)

2.3.觸發時間(after/before)

2.4.觸發事件(insert/update/delete)

文法：

create trigger triggerName

after/before insert/update/delete on 表名

for each row #這句話在mysql是固定的

begin

sql語句;

end;

觸發器樣本："在album表新增資料的時候更新artist表的num欄位減3"

DELIMITER $$USE `curl_test`$$DROP TRIGGER /*!50032 IF EXISTS */ `change_num`$$CREATE/*!50017 DEFINER = ‘root‘@‘localhost‘ */TRIGGER `change_num` AFTER INSERT ON `album` FOR EACH ROW BEGINUPDATE `artist` SET num=num-3;END;$$DELIMITER ;

 

mysql預存程序和觸發器的應用