《MySQL必知必會》之快速入門游標和觸發器

第二十四章使用游標本章將介紹什麼是游標以及如何使用游標游標之前的select語句檢索出來的數據，沒有辦法得到第一行或者下一行有時，需要在檢索出來的行中前進或後退一行或多行。這就是使用游標的原因。游標（cursor）是一個存儲在MySQL伺服器上的資料庫查詢，它不是一條SELECT語句，而 ...

第二十四章使用游標

本章將介紹什麼是游標以及如何使用游標

游標

之前的select語句檢索出來的數據，沒有辦法得到第一行或者下一行

有時，需要在檢索出來的行中前進或後退一行或多行。這就是使用游標的原因。

游標（cursor）是一個存儲在MySQL伺服器上的資料庫查詢，它不是一條SELECT語句，而是被該語句檢索出來的結果集。

在存儲了游標之後，應用程式可以根據需要滾動或瀏覽其中的數據。

MySQL游標只能用於存儲過程（和函數）

使用游標

使用游標涉及幾個明確的步驟：

在能夠使用游標前，必須聲明（定義）它。這個過程實際上沒有檢索數據，它只是定義要使用的SELECT語句。

一旦聲明後，必須打開游標以供使用。這個過程用前面定義的SELECT語句把數據實際檢索出來。

對於填有數據的游標，根據需要取出（檢索）各行。

在結束游標使用時，必須關閉游標。

創建游標

CREATE PROCEDURE processorders()
BEGIN
	DECLARE ordernumbers CURSOR
    FOR
    SELECT order_num FROM orders;
END;

這個存儲過程並沒有做很多事情，DECLARE語句用來定義和命名游標，這裡為ordernumbers。存儲過程處理完成後，游標就消失（因為它局限於存儲過程）。

打開和關閉游標

--打開游標
OPEN ordernumbers;
--處理完成後，應當使用下句關閉游標
CLOSE ordernumbers;

CLOSE釋放游標使用的所有內部記憶體和資源，因此在每個游標不再需要時都應該關閉
MySQL會在達到END語句時自動關閉它

使用游標數據

例：從游標中檢索單個行

CREATE PROCEDURE processorders()
BEGIN
	-- Declare local variables
    DECLARE o INT;

    -- Declare the cursor
    DECLARE orderumbers CURSOR
    FRO
    SELECT order_num FROM orders;

    -- Open the cursor
    OPEN ordernumbers;

	-- Get order number
    FETCH ordernumbers INTO o;

	-- Close the cursor
    CLOSE ordernumbers;

END;

其中FETCH用來檢索當前行的order_num列（將自動從第一行開始）到一個名為o的局部聲明的變數中。對檢索出的數據不做任何處理。

例：迴圈檢索數據，從第一行到最後一行

CREATE PROCEDURE processorders()
BEGIN
	--Declare local variables
    DECLARE done BOOLEAN DEFAULT 0;
    DECLARE o INT;

    --Declare the cursor
    DECLARE ordernumbers CURSOR
    FOR
    SELECT order_num FROM orders;

	--Declare continue handler
    DECLARE CONTINUE HANDLER FRO SQLSTATE '02000' SET done=1;

	--Open the cursor
    OPEN ordernumbers;

	--Loop through all rows
    REPEAT
    	--Get order numbers
        FETCH ordernumbers INTO o;
    --End of loop
    UNTIL done END REPEAT;
    --CLose the cursor
    CLOSE ordernumbers;
END;

這個例子使用FETCH檢索當前order_num到聲明的名為o的變數中。但與前一個例子不一樣的是，這個例子中的FETCH是在REPEAT內，因此它反覆執行直到done為真（由UNTIL done END REPEAT;規定）。為使它起作用，用一個DEFAULT 0（假，不結束）定義變數done。

使用以下語句將done在結束時設置為真：

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1

這條語句定義(DECLARE)了一個CONTINUE HANDLER，它是在條件出現時被執行的代碼。

這裡，它指出當SQLSTATE '02000’出現時，SET done=1。

SQLSTATE '02000’是一個未找到條件，當REPEAT由於沒有更多的行供迴圈而不能繼續時，出現這個條件。

註意：
DECLARE語句的次序，定義局部變數時必須在游標或者句柄前定義，定義句柄必須在游標之後定義

CREATE PROCEDURE processorders()
BEGIN
	--Declare local variables
    DECLARE done BOOLEAN DEFAULT 0;
    DECLARE o INT;
    DECLARE t DECIMAL(8,2);
    --Declare the cursor
    DECLARE ordernumbers CURSOR
    FOR SELECT order_num FROM orders;
    --Declare continue handler
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
    --Create a table to store the results
    CREATE TABLE IF NOT EXISTS ordertotals
    	(order_num INT, total DECIMAL(8,2));
    --Open the cursor
    OPEN ordernumbers;
    --Loop through all rows
    REPEAT
    	--Get order number
        FETCH ordernumbers INTO o;
        --Get the total for this order
        CALL ordertoal(o, 1, t);
        --Insert order and total into ordertotals
        INSERT INTO ordertotals(order_num, total)
        VALUES(o,t);
    --End of loop
    UNTIL done END REPEAT;
    --Close the cursor
    CLOSE ordernumbers;
END;

在這個例子中，增加了另一個名為t的變數（存儲每個訂單的合計）。此存儲過程還在運行中創建了一個新表（如果它不存在的話），名為ordertotals。這個表將保存存儲過程生成的結果。FETCH像以前一樣取每個order_num，然後用CALL執行另一個存儲過程來計算每個訂單的帶稅的合計（結果存儲到t）。最後，用INSERT保存每個訂單的訂單號和合計。
次存儲過程不返回數據

第二十五章使用觸發器

本章介紹什麼是觸發器，為什麼要使用以及如何使用觸發器，還有創建和使用觸發器的語法

觸發器

想要某條語句或者某些語句在事件發生時執行就需要使用觸發器

需求：往表中新增一條員工數據，都需要檢查其電話號格式是否正確，每當訂購一個產品時，都從庫存數量中減去訂購的數量

其實也就是在某個表發生更改時自動處理

觸發器是MySQL響應一下任意語句而自動執行的一條MySQL語句

DELETE
INSERT
UPDATE
其他MySQL語句不支持觸發器

創建觸發器

創建觸發器，需要給出以下4個數據

唯一的觸發器名
觸發器關聯的表
觸發器應該響應的活動
觸發器合何時執行

使用CREATE TRIGGER語句創建。例：

CREATE TRIGGER newproduct AFTER INSERT ON products
FOR EACH ROW SELECT 'Product added';

CREATE TRIGGER用來創建名為newproduct的新觸發器。觸發器可在一個操作發生之前或之後執行，這裡給出了AFTER INSERT，所以此觸發器將在INSERT語句成功執行後執行。這個觸發器還指定FOR EACH ROW，因此代碼對每個插入行執行。在這個例子中，文本Product added將對每個插入的行顯示一次。

註意：

只有表支持觸發器，視圖不支持

觸發器按每個表每個事件每次地定義，每個表每個事件每次只允許一個觸發器。因此，每個表最多支持6個觸發器（每條INSERT、UPDATE和DELETE的之前和之後）。單一觸發器不能與多個事件或多個表關聯，所以，如果你需要一個對INSERT和UPDATE操作執行的觸發器，則應該定義兩個觸發器。

刪除觸發器

DROP TRIGGER newproduct；

觸發器不能更新或者覆蓋，想要修改，只能刪除並重建

INSERT觸發器

用在insert語句之前或者之後，需要知道以下幾點：

在INSERT觸發器代碼內，可引用一個名為NEW的虛擬表，訪問被插入的行；
在BEFORE INSERT觸發器中，NEW中的值也可以被更新（允許更改被插入的值）；
對於AUTO_INCREMENT列，NEW在INSERT執行之前包含0，在INSERT執行之後包含新的自動生成值。

CREATE TRIGGER neworder AFTER INSERT ON orders
FOR EACH ROW SELECT NEW.order_num;

此代碼創建一個名為neworder的觸發器，它按照AFTER INSERT ON orders執行。在插入一個新訂單到orders表時，MySQL生成一個新訂單號並保存到order_num中。觸發器從NEW. order_num取得這個值並返回它。此觸發器必須按照AFTER INSERT執行，因為在BEFORE INSERT語句執行之前，新order_num還沒有生成。對於orders的每次插入使用這個觸發器將總是返回新的訂單號。

INSERT INTO orders(order_date,cust_id)
VALUES(Now(),10001);

將返回一個order_num，orders表包含三列，date、id必須給出，num自動生成並返回

DELETE觸發器

觸發器在DELETE語句執行之前或之後執行。需要知道以下兩點：

在DELETE觸發器代碼內，你可以引用一個名為OLD的虛擬表，訪問被刪除的行；
OLD中的值全都是只讀的，不能更新。

CREATE TRIGGER deleteorder BEFORE DELETE ON orders
FOR EACH ROW
BEGIN
	INSERT INTO archive_orders(order_num, order_date, cust_id)
    VALUES(OLD.order_num, OLD.order_date, OLD.cust_id);
END;

在任意訂單被刪除前將執行此觸發器。它使用一條INSERT語句將OLD中的值（要被刪除的訂單）保存到一個名為archive_orders的存檔表中（為實際使用這個例子，你需要用與orders相同的列創建一個名為archive_orders的表）。

說白了就是在刪除前備份數據

UPDATE觸發器

觸發器在UPDATE語句執行之前或之後執行。需要知道以下幾點：

在UPDATE觸發器代碼中，你可以引用一個名為OLD的虛擬表訪問以前（UPDATE語句前）的值，引用一個名為NEW的虛擬表訪問新更新的值；
在BEFORE UPDATE觸發器中，NEW中的值可能也被更新（允許更改將要用於UPDATE語句中的值）；
OLD中的值全都是只讀的，不能更新。

CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors
FOR EACH ROW SET NEW.vend_state = Upper(New.vend_state);

該sql保證州名縮寫總是大寫（不管UPDATE語句中給出的是大寫還是小寫）

關於觸發器的進一步介紹

與其他DBMS相比，MySQL 5中支持的觸發器相當初級。未來的MySQL版本中有一些改進和增強觸發器支持的計劃。
創建觸發器可能需要特殊的安全訪問許可權，但是，觸發器的執行是自動的。如果INSERT、UPDATE或DELETE語句能夠執行，則相關的觸發器也能執行。
應該用觸發器來保證數據的一致性（大小寫、格式等）。在觸發器中執行這種類型的處理的優點是它總是進行這種處理，而且是透明地進行，與客戶機應用無關。
觸發器的一種非常有意義的使用是創建審計跟蹤。使用觸發器，把更改（如果需要，甚至還有之前和之後的狀態）記錄到另一個表非常容易。
遺憾的是，MySQL觸發器中不支持CALL語句。這表示不能從觸發器內調用存儲過程。所需的存儲過程代碼需要複製到觸發器內。