mysql routines

MySQL routines 是指存儲過程、函數和觸發器。這篇文章將介紹在 MySQL 中如何創建、使用、調用和刪除存儲過程、函數和觸發器。

存儲過程

存儲過程是一組可重用 SQL 語句集合，被封裝在一個名稱下，作為一個單元來執行。存儲過程可以包括輸入和輸出參數，也可以返回多個結果集。

DELIMITER //
CREATE PROCEDURE `get_customer`(IN customer_id INT)
BEGIN
SELECT * FROM customers WHERE customer_id = customer_id;
END //
DELIMITER ;

上面的例子中創建了一個名為 get_customer 的存儲過程，輸入參數為 customer_id，執行后返回與該 customer_id 匹配的顧客信息。創建存儲過程時須使用 DELIMITER，以更改 MySQL 默認的結束符號（；），以避免出錯。

調用存儲過程可使用 CALL statement:

CALL get_customer(1);

函數是一個可重用 SQL 語句集合，它接收輸入參數，處理它們并返回單一結果集。函數可以是內置的（如，SUM, AVG, COUNT等），也可以是用戶定義的。

DELIMITER //
CREATE FUNCTION `calculate_price`(price INT, discount INT)
RETURNS INT
BEGIN
RETURN price - price*discount/100;
END //
DELIMITER ;

上面的例子中，創建了一個名為 calculate_price 的函數，接收兩個輸入參數，price 和 discount，并返回價格 after discount。RETURNS 規定了函數返回值的類型，該例中為整數。DELIMITER 也需要在創建函數時使用。

調用函數的方法如下：

SELECT calculate_price(100, 10);

觸發器是一種特殊類型的存儲過程，會在特定的表上觸發某些動作（如 INSERT, UPDATE 或 DELETE）。在 MySQL 中，觸發器只能綁定在插入、更新、刪除操作之前或之后。

DELIMITER //
CREATE TRIGGER `update_salary` BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
SET NEW.salary = NEW.salary * 1.05;
END //
DELIMITER ;

在上面的例子中，一個名為 update_salary 的觸發器綁定在 employees 表的更新操作之前，會將每個員工的工資增加 5%。

在一個表上可以創建多個觸發器，可以在創建時指定是在操作之前還是之后，以及是針對每一行記錄（FOR EACH ROW）還是整個表。

刪除存儲過程、函數和觸發器很簡單：

DROP PROCEDURE IF EXISTS `get_customer`;
DROP FUNCTION IF EXISTS `calculate_price`;
DROP TRIGGER IF EXISTS `update_salary`;

使用 DROP + 類型名稱 + 名稱來刪除相應的對象。