存盤程序

概念

存盤程序（Stored Procedure），是為了完成特定功能的SQL陳述句集，

優點

存盤程序可以理解為shell腳本這型別的命令集輸出工具，但是在底層，存盤程序擁有更多的優點：

• 語言的靈活性跟功能性更強，在原有基礎之上可以插入控制陳述句、回圈陳述句等讓SQL陳述句的功能更強，能夠完成更復雜的運算跟判斷，
• 封裝性，存盤程序被創建后，可以在被多次呼叫，同時可以進行修改，對程式原始碼不造成影響，
• 執行速度快，MySQL的陳述句在執行程序中會有一個[[MySQL語言的編譯|轉譯程序]]，當資料庫體量到達一定的級別的時候，對性能的影響很大，而使用存盤程序的時候，執行過一次以后，產生的二進制代碼就會被預留在快取區，不需要再次編譯，
• 減少網路流量，因為不需要進行轉譯了所以在日常使用中會減少對網路流量的使用，
• 提高資料庫的完整性和安全性，基于上述的封裝性，可以對用戶的使用權限進行更好的限制，不需要給到更多的權限的同時也能夠進行所需的查詢，從而也保護了資料的完整性，

創建&呼叫 存盤程序

呼叫語法

CALL 程序名稱（引數1,引數2...）

創建語法

 CREATE PROCEDURE <存盤程序別名> ( [程序引數[,…] ] ) <程序體>

程序引數的格式

[ IN | OUT | INOUT ] <引數名> <型別>

示例

[[99-MySQL補充#MySQL分隔符|修改分隔符]]，再寫存盤程序

DELIMITER :

CREATE PROCEDURE SE()
BEGIN
SELECT * FROM learn.books;
END ：

DELIMITER ;
CALL SE();

結束的時候在記得將分隔符修改回默認的，

MySQL變數

定義&呼叫變數

在MySQL界面中使用 SET 定義變數，用 SELECT 呼叫變數，

SET @a=1;
SELECT @a;

此處定義的變數為臨時變數，僅該次連接可用，當需要全域長期使用時，需要修改全域變數，

存盤程序與變數

MySQL存盤程序中使用 DECLARE 定義變數，變數僅作用于本次存盤程序內，屬于區域變數，同時需要對變數定義欄位屬性，呼叫時直接用宣告了的變數名呼叫即可，

變數的傳遞

1. IN：將變數傳入存盤程序
2. OUT：將存盤程序內的變數傳出到MySQL變數中
3. INOUT：變數進入存盤程序又出來到MySQL變數中

示例IN

DELIMITER :

CREATE PROCEDURE getone(in id INT)
BEGIN
SELECT bname,bid FROM books WHERE bid=id;
END :

DELIMITER ;

CALL getone(1)；

1. 這里的變數是IN進行傳遞到存盤程序之中，并定義了變數型別為整數，
2. 這里將引數傳入存盤程序，所以在呼叫的時候需要給到傳入引數，

示例OUT

DELIMITER :

CREATE PROCEDURE outone(OUT nu INT)
BEGIN
SET nu=1;
SELECT nu;
END:

DELIMITER ;

CALL outone(@num); 
SELECT @num;

這里將內部引數傳遞到了外部，并用num進行了接收，

示例INOUT

DELIMITER :
CREATE PROCEDURE inoutone(IN bookid INT,OUT bookname VARCHAR(255))

BEGIN
	SELECT bname into bookname FROM learn.books WHERE bid=bookid;
END:

DELIMITER ;
CALL inoutone(1,@a); 

INOUT的引數傳遞需要注意呼叫跟輸入的對應關系

存盤程序的回圈

WHILE 回圈

• 創建測驗表　
  create table pwhile(id int);
• 在存盤程序中while的結構為

WHILE DO ... END WHILE

• 示例

DELIMITER :

CREATE PROCEDURE pwhile()
BEGIN
	DECLARE i INT;
	SET i = 0;
	
	WHILE i < 10 DO
		INSERT INTO test VALUES(i) ;
		SET i = i + 1;
	END WHILE;
	
	SELECT * FROM test;
END :

DELIMITER ;
CALL pwhile();

REPEAT回圈

• 創建測驗表
  `create table ptest(id int
• REPEAT回圈結構為

REPEAT...UNTIL...END REPEAT

• 示例

DELIMITER :

CREATE PROCEDURE arepeat()
BEGIN
	DECLARE i INT;
	SET i = 0;
	
	REPEAT
		INSERT INTO ptest VALUES(i) ;
		SET i = i + 1;
	UNTIL i > 10 END REPEAT;
	
	SELECT * FROM ptest;
END :

DELIMITER ;
CALL arepeat();

loop回圈

• 創建測驗表　
  cerate table ltest
• loop回圈結構　

lp:loop ... if ... end if ... end loop

• 示例　

DELIMITER :

CREATE PROCEDURE tloop()
BEGIN
	DECLARE i INT;
	SET i = 0;
	
LP:LOOP
	INSERT INTO ltest VALUES(i) ;
	SET i = i + 1;
	IF i > 10 THEN
	LEAVE LP
END LOOP

	SELECT * FROM ltest;
END :

DELIMITER ;
CALL tloop();

LP:LOOP 是對LOOP取了一個別名為LP

查詢&洗掉 存盤程序

指定庫名稱查詢SELECT

SELECT name FROM mysql.proc WHERE db='[資料庫名]' AND type='procedure';

指定存盤程序的名稱查詢SHOW

SHOW procedure STATUS LIKE '[存盤程序名稱]' \G

洗掉存盤程序DROP

DROP PROCEDURE IF EXISTS [存盤程序名稱];

MySQL觸發器 TRIGRRER

概念

觸發器是一個特殊的存盤程序，區別點在于存盤程序需要使用CALL陳述句來呼叫，觸發器的執行不需要，也不需要手動啟動，只需要一個預定義事件就會被MySQL自動呼叫，

場景

主要用于保護資料，尤其是多表相互鏈接的時候，觸發器能夠讓被鏈接的表之間保持一致性，

預定義事件

即是指觸發觸發器的場景，能夠觸發觸發器的事件，有且只有 INSERT/UPDATE/DELETE 操作時才能觸發，

觸發器種類

根據預定義事件的分類，觸發也分為以下三種：　

1. INSERT 觸發器
2. UPDATE 觸發器
3. DELETE 觸發器
   此三類觸發器可指定在對應命令執行前或后激活觸發器，

創建觸發器

語法

CREATE TRIGGER <觸發器名稱> 
<觸發時機 AFTER|BEFORE>
<觸發事件 UPDATE|DELETE|INSERT> 
ON <需觸發的表 路徑> FOR EACH ROW <觸發器主體>;

補充點

1. 同一個表中的不能有觸發時機和觸發事件相同的觸發器
2. 觸發器關聯的表要有永久性，不能將觸發器與臨時表、視圖關聯，
3. FOR EACH ROW：行級觸發
4. 觸發器主體：指的是觸發器激活時執行的陳述句，要執行多個陳述句時，可以使用BEGIN END復合陳述句結構，

查看觸發器

SHOW TRIGGERS;

觸發器中NEW和OLD

關于NEW和OLD，可以理解為一個特殊的表，定位了發生變化的資料型別，

在INSER中

NEW 用來表示將要（BEFORE）或已經（AFTER）插入的新資料；

在UPDATE中

NEW 用來表示將要（BEFORE）或已經（AFTER）插入的新資料；

在DELETE中

OLD 用來表示將要或已經被洗掉的原資料；

創建觸發器

簡單觸發器

觸發器的創建程序其實也是一個存盤程序的創建

DELIMITER :

CREATE TRIGGER delcategory AFTER DELETE ON category FOR EACH ROW
	
	DELETE FROM books WHERE btypeid=3;
	:

DELIMITER ;

NEW/OLD 的觸發器

OLD 示例

當category表發生洗掉操作后，books表也會根據btypeid進行洗掉操作

DELIMITER :

CREATE TRIGGER cbook AFTER DELETE 
ON category FOR EACH ROW

	BEGIN
	DELETE FROM books WHERE books.btypeid=old.btypeid;
	END :

DELIMITER ;

old.btypeid 表示觸發器所在表category中發生洗掉的btypeid

NEW 示例

阻止對students表的更新操作，但洗掉跟插入不受影響，

DELIMITER :

CREATE TRIGGER ubook BEFORE UPDATE
ON students FOR EACH ROW

	BEGIN
	
	IF old.name is NOT NULL THEN
		SET new.name=old.name,new.id=old.id,new.age=old.age;
	END IF
	
	END :
	
DELIMITER ;

IF old.name is NOT NULL 如果 old表中name欄位不為空　　

• 此處new.name 是一個并不存在的值，而 old.name 是一個只讀的資料存在于原資料中，即是將發生更新前的資料賦值給到了old.name

利用Pessimistic Lock的觸發器

CREATE TRIGGER tr_MyTrigger ON tablename FOR UPDATE AS 

UPDATE table1 SET column1 = data1
INNER JOIN deleted d ON table1.id = d.id 

UPDATE table2 SET column2 = data2 
INNER JOIN deleted d ON table2.id = d.id 

UPDATE table3 SET column3 = data3 
INNER JOIN deleted d ON table3.id = d.id

/* etc. */ GO

說明

1. FOR UPDATE AS UPDATE 是一種在MySQL資料庫中實作悲觀鎖（Pessimistic Lock）的技術，它可以保證同一個記錄多執行緒下同時訪問時不會產生更新并發問題，
2. /* etc. */ GO 是在SQL中常用的一個命令，它用于告訴SQL服務器要啟動對SQL腳本的執行，

事務

概念

• MySQL資料庫事務（datebase transaction）:MySQL事務是用來保證資料庫資料一致性和完整性的一種機制，　
• 事務可以讓用戶將一系列的SQL陳述句保存在一個組中，并這些SQL陳述句作為一個單一的邏輯作業單元來執行，　
• MySQL事務由4個指令定義：BEGIN開始事務；COMMIT提交事務；ROLLBACK回滾事務；SAVEPOINT設定一個事務保存點，
• MySQL為每個會話維護了一個隱形的transaction id，以及一個當前正在執行的transaction的id以及一個超時時間，這些都將決定當前正在作業的事務是否需要提交或回滾，
• 僅支持INNODB和BDB兩種存盤引擎，

事務的特性ACID

原子性（Autmic）

指的是在事務操作的不可分割，僅有0和1，執行全部成功或者全部失敗，沒有部分成功部分失敗，

一致性（Consistency）

指的是在事務開始的前后，整個資料庫的一致性不受影響，資料完整性不受影響，

隔離性（Isolation）

指的是事務的執行是并行且獨立的，在事務完成之后才會將結果進行發布，整個程序中彼此是不可見的，避免事務的一個混亂，

持久性（Durability）

指的是事務執行完成后，所存盤的資料應該存盤在資料庫中，即使系統發送故障資料本身不受影響，

創建一個簡單事務

創建一個提交的

SELECT bname FROM books WHERE bid=1 OR bid=2;

SET AUTOCOMMIT=0;
DELIMITER :

START TRANSACTION;
	UPDATE books SET bname="cc" WHERE bid=1;
	UPDATE books SET bname="dd" WHERE bid=2;
	COMMIT;
:

DELIMITER ;

SET AUTOCOMMIT=0; 關閉自動提交事務
COMMIT; 提交事務

測驗回滾

回滾命令：rollback

當引擎不符合時無法回滾

SHOW CREATE TABLE books\G
SELECT bname WHERE bid=1 or bid=2;
ROLLBACK;
SELECT bname WHERE bid=1 or bid=2;

修改引擎后重新提交事務

引擎符合時

ALTER TABLE books ENGINE=INNODB;
SHOW CREATE TABLE books\G
SELECT bname WHERE bid=1 or bid=2;

DELIMITER :

START TRANSACTION;
	UPDATE books SET bname="cc" WHERE bid=1;
	UPDATE books SET bname="dd" WHERE bid=2;
:
DELIMITER ;


ROLLBACK;
SELECT bname WHERE bid=1 or bid=2;

回滾成功　
　

標籤：MySQL

上一篇：hive 從入門到精通

下一篇：返回列表