MySQL預存程序及觸發器

MySQL預存程序及觸發器

一、預存程序

預存程序的基本格式如下：

-- 聲明結束符
-- 建立預存程序
DELIMITER $  -- 聲明預存程序的結束符
CREATE PROCEDURE pro_test()    --預存程序名稱(參數列表)
BEGIN
    -- 可以寫多個sql語句;      -- sql語句+流程式控制制
    SELECT * FROM employee;
END $  -- 結束 結束符

-- 執行預存程序
CALL pro_test();  -- CALL 預存程序名稱(參數);

-- 刪除預存程序
DROP PROCEDURE pro_test;

參數：
IN：  表示輸入參數，可以攜帶資料帶預存程序中
OUT： 表示輸出參數，可以從預存程序中返回結果
INOUT： 表示輸入輸出參數，既可以輸入功能，也可以輸出功能

1. 帶有輸入參數的預存程序

    需求：傳入一個員工的id，查詢員工資訊

DELIMITER $
CREATE PROCEDURE pro_findById(IN eid INT)  -- IN: 輸入參數
BEGIN
    SELECT * FROM employee WHERE id=eid;
END $

-- 調用
CALL pro_findById(4);

2. 帶有輸出參數的預存程序

DELIMITER $
CREATE PROCEDURE pro_testOut(OUT str VARCHAR(20))  -- OUT：輸出參數
BEGIN
        -- 給參數賦值
    SET str='hellojava';
END $

如何接受返回參數的值呢？這裡涉及到MySQL的變數

MySQL變數一共有三種：

全域變數

全域變數又叫內建變數，是mysql資料庫內建的變數 ，對所有串連都起作用。

查看所有全域變數： show variables

查看某個全域變數： select @@變數名

修改全域變數： set 變數名=新值

character_set_client: mysql伺服器的接收資料的編碼

character_set_results：mysql伺服器輸出資料的編碼

會話變數

只存在於當前用戶端與資料庫伺服器端的一次串連當中。如果串連斷開，那麼會話變數全部丟失！

定義會話變數: set @變數=值

查看會話變數： select @變數

局部變數

在預存程序中使用的變數就叫局部變數。只要預存程序執行完畢，局部變數就丟失。

回到上面這個預存程序，如何接受返回參數的值呢？

定義一個會話變數name, 使用name會話變數接收預存程序的傳回值

CALL pro_testOut(@NAME);

查看變數值

SELECT @NAME;

3. 帶有輸入輸出參數的預存程序

DELIMITER $
CREATE PROCEDURE pro_testInOut(INOUT n INT)  -- INOUT： 輸入輸出參數
BEGIN
  -- 查看變數
  SELECT n;
  SET n =500;
END $

-- 調用
SET @n=10;

CALL pro_testInOut(@n);

SELECT @n;

4. 帶有條件判斷的預存程序

需求：輸入一個整數，如果1，則返回“星期一”,如果2，返回“星期二”,如果3，返回“星期三”。其他數字，返回“錯誤輸入”;

DELIMITER $
CREATE PROCEDURE pro_testIf(IN num INT,OUT str VARCHAR(20))
BEGIN
    IF num=1 THEN
        SET str='星期一';
    ELSEIF num=2 THEN
        SET str='星期二';
    ELSEIF num=3 THEN
        SET str='星期三';
    ELSE
        SET str='輸入錯誤';
    END IF;
END $

--調用
CALL pro_testIf(4,@str);
 
SELECT @str;

5. 帶有迴圈功能的預存程序

需求： 輸入一個整數，求和。例如，輸入100，統計1-100的和

DELIMITER $
CREATE PROCEDURE pro_testWhile(IN num INT,OUT result INT)
BEGIN
    -- 定義一個局部變數
    DECLARE i INT DEFAULT 1;
    DECLARE vsum INT DEFAULT 0;
    WHILE i<=num DO
          SET vsum = vsum+i;
          SET i=i+1;
    END WHILE;
    SET result=vsum;
END $

--調用
CALL pro_testWhile(100,@result);

SELECT @result;

6. 使用查詢的結果賦值給變數（INTO）

DELIMITER $
CREATE PROCEDURE pro_findById2(IN eid INT,OUT vname VARCHAR(20) )
BEGIN
    SELECT empName INTO vname FROM employee WHERE id=eid;
END $

--調用
CALL pro_findById2(1,@NAME);

SELECT @NAME;

二、 觸發器

觸發器作用：當操作了某張表時，希望同時觸發一些動作/行為，可以使用觸發器完成。

需求： 當向員工表插入一條記錄時，希望mysql自動同時往日誌表插入資料

CREATE TRIGGER tri_empAdd AFTER INSERT ON employee FOR EACH ROW    -- 當往員工表插入一條記錄時
    INSERT INTO test_log(content) VALUES('員工表插入了一條記錄');

以上觸發器是基於insert的，以下兩個是基於update和delete的。

-- 建立觸發器(修改)
CREATE TRIGGER tri_empUpd AFTER UPDATE ON employee FOR EACH ROW    -- 當往員工表修改一條記錄時
    INSERT INTO test_log(content) VALUES('員工表修改了一條記錄');
 
-- 建立觸發器(刪除)
CREATE TRIGGER tri_empDel AFTER DELETE ON employee FOR EACH ROW    -- 當往員工表刪除一條記錄時
    INSERT INTO test_log(content) VALUES('員工表刪除了一條記錄');

說明：

1. MySQL預設將分號，即“;”作為語句的分隔字元。如果是這樣的話，則一個預存程序將很難正常建立，因為它的BEGIN和END之間可以是任意數量的SQL語句，而每條SQL語句都以分號結束。所以，在建立預存程序時，必須重新定義分隔字元。在上述用例中，均將“$”作為新的分隔字元，注意，在建立完預存程序後，必須重新設定回原來的分隔字元“;”。

本文永久更新連結地址：