Mysql----淺入淺出之視圖、預存程序、觸發器

標籤：style   使用   os   資料   for   ar   new   size   

一、視圖 VIEW

視圖是虛擬表，本身不儲存任何資料。只有運行時，才包含動態檢索出來的資料。

eg：SELECT sid, name, sex, s_num FROM  student, school WHERE sid = 2 AND student.sid = scholl.sid ;

這個簡單的查詢涉及到兩個表。所以任何需要這個資料的人都必須熟悉兩個表以及之間的關係。想檢索其他學生資訊，就必須修改WHERE子句。如果可以把整個查詢封裝成一個虛擬表studentinfo，那麼就可以這樣得到資料：

eg：SELECT sid, name, sex, s_num FROM studentinfo WHERE sid = 2;

使用視圖可以重用sql語句。對於使用的人可以不必知道細節。對未經處理資料也有保護作用。

視圖也有一些限制，比如不能索引，不能有關聯的觸發器。名字必須唯一。

建立視圖：

eg：CREATE VIEW studentinfo AS SELECT sid name, sex, s_num FROM student, school WHERE student.sid = school.sid;

視圖也可以更新，但是只在特定的情況下。如果視圖有這些定義，則不能更新:分組、連接、子查詢、並、聚集合函式DISTINCT、計算資料行。

二、預存程序

當需要處理的商務邏輯很複雜的時候，可以一條條的寫sql語句，而且需要考慮到所有的需要處理的細節、資料完整性。可以建立預存程序來代替。它就像一個批處理，包含預先定義好的一條或多條sql語句的集合。但它的作用可不僅限於此。

建立：

eg：CREAT PROCEDURE prostudent() 

BEGIN

SELECT Max(score) AS scoremax FROM student;

END;

刪除：

eg：DROP PROCEDURE prostudent；

使用參數：

eg：CREATE PROCEDURE prostudent(OUT scorehigh DECIMAL(8, 2), OUT scorelow DECIMAL(8, 2))

BEGIN

SELECT Max(score) INTO scorehigh FROM student;

SELECT Min(score) INTO scorelow FROM student;

END;

執行：

eg：CALL prostudent(@scorelow, @scorehigh);

SELECT @scorehigh, @scorelow;

執行了名為prostudent的預存程序，並返回了最高成績和最低成績。

此外，預存程序還可以寫注釋、定義臨時變數、IN傳入參數、流程式控制制語句。

SHOW CREATE PROCEDURE ****; 可以查看建立預存程序的語句。

SHOW PROCEDURE STATUS;可以查看這個預存程序是何時、由誰建立的。

三、遊標

SELECT語句返回的是一個結果集，可能是滿足條件的多個航。那我們想對這個結果集的每一行進行一些處理時，或者在第一行、最後一行、前一行等特殊要求時，怎麼辦呢？這裡就用到裡遊標。mysql中的遊標只能用於預存程序，這是與其他資料庫的不同。

使用遊標需要先定義DECLARE *** CURSOR FOR、然後開啟OPEN ***、使用、關閉CLOSE ***。遊標的生命週期只在預存程序中，也就是如果你不關閉它，當預存程序END時，會自動關閉。

遊標開啟後，可以用FETCH取出一行，並在內部指向下一行的位置，當再次FETCH的時候，將取出下一行。

舉個例子：現在要將所有sid大於3的學生的成績加和。

eg:DELIMITER //

CREATE PROCEDURE sumofscore(OUT sum INT)

BEGIN

DECLARE done BOOLEEAN DEFAULT 0;

DELCARE tmp INT;

DECLARE s INT DEFAULT 0;

DECLARE yb CURSOR FOR SELECT score FROM student WHERE sid > 3;

DECLARE CONTINUE HANDLER FOR SQLSTATE ‘02000‘ SET done = 1;

OPEN yb;

REPEAT

FETCH yb INTO tmp;

SET s = s + tmp;

UNTIL done END REPEAT;

CLOSE yb;

SELECT s INTO sum;

END

//

DELIMITER ;

DELIMITER 重定義mysql的結束符。02000是資料未找到的錯誤碼，利用它來判斷是否遍曆完所有資料。

四、觸發器

在某些需求下，想要某些語句在特定事件發生時，自動執行，那麼就用到了觸發器。mysql觸發器只能響應delete、insert和update語句。

建立觸發器：

eg：CRESTE TRIGGER newstudent AFTER INSERT ON student FOR EACH ROW SELECT new.sid INTO @s;

觸發器名字是newstudent，INSERT指定響應事件是插入操作。AFTER/BEFORE指定是在事件執行前還是事件執行後觸發。FOR EACH ROW指定對插入的每一行都進行操作，所以每插入一行，就把這行的sid傳給變數s。每個表每個事件只允許有一個觸發器，所以每個表最多有6個觸發器。一個觸發器也只能響應一個事件。

刪除觸發器：

eg：DROP TRIGGER newstudent;

使用觸發器：

在INSERTZHONG ,可引用一個名為NEW的虛擬表，訪問被插入的行。在BEFROE INSERT中，也可以引用NEW，甚至可以更新資料，以改變插入資料的內容。

DELETE觸發器中，可以引用名為OLD的虛擬表，訪問被刪除的行。