《MySQL必知必會》之快速入門游標和觸發器-有解無憂

第二十四章使用游標

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

游標

之前的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陳述句，這表示不能從觸發器內呼叫存盤程序，所需的存盤程序代碼需要復制到觸發器內，

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/539743.html

標籤：MySQL

上一篇：mysql資料庫和表的基礎操作

下一篇：SpringBoot中MongoDB聚合管道查詢操作$facet$lookup$unwind$group

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

第二十四章 使用游標

游標