mysql 預存程序 儲存函數

MySQL是一種流行的關係型資料庫管理系統，支援預存程序和儲存函數等資料庫物件的建立和使用。在本文中，我們將深入探討MySQL預存程序和儲存函數的概念、用途、語法和範例。

一、什麼是預存程序和儲存函數？

預存程序和儲存函數是MySQL中的兩種資料庫物件。它們都可以在MySQL伺服器端創建，並且可以被其他客戶端程式呼叫和執行。它們的共同點是都可以封裝一系列的SQL語句，實現一些複雜的資料庫操作或業務邏輯。

預存程序（Stored Procedure）是一組預先定義SQL語句的集合，類似其他程式語言中的子程式或函數。它們被編寫成單一SQL語句並儲存在伺服器上，可以在需要時被呼叫。預存程序可以用來實現一些特定的業務需求，例如批次處理資料、執行事務、快速查詢等等。預存程序在執行時可以接受參數，執行後也可以傳回結果。

儲存函數（Stored Function）是一個可以在SQL查詢中呼叫的過程，它接受輸入參數並傳回單一值。和預存程序類似，儲存函數也是一組SQL語句的集合。儲存函數通常用於計算、轉換或操作值，並將結果傳回給呼叫者。與預存程序不同的是，儲存函數只能傳回一個單一的值，而不能傳回一組結果。儲存函數被廣泛用於查詢和報表等方面。

二、預存程序和儲存函數的優點

1. 可提高資料庫效能：預存程序和儲存函數可以在伺服器上運行，減少了資料傳輸的負載和網路通訊的開銷，從而提高了資料庫的效能。
2. 更好的安全性：預存程序和儲存函數可以靠存取控制來控制對錶的存取。因為SQL程式碼儲存在資料庫伺服器中，使用者無法直接存取它們，只有在使用特定的權限之後才能使用。
3. 簡化程式碼：預存程序和儲存函數可以重複使用SQL程式碼，減少了重複的SQL查詢和邏輯程式碼編寫，程式碼量減少，更具維護性。
4. 可移植性：預存程序和儲存函數可以在不同的應用程式中重複使用，並且可以在不同的作業系統和資料庫平台之間移植。

三、建立與使用預存程序

1. 建立預存程序：

建立預存程序的語法如下：

CREATE PROCEDURE procedure_name ([IN | OUT | INOUT] parameter datatype [, …])
BEGIN
SQL statements;
END;

• procedure_name：預存程序的名稱
• parameter：預存程序的參數，它可以是IN（預設）輸入參數、OUT輸出參數和INOUT（同時輸入輸出）參數。
• datatype：參數的資料類型
• SQL statements：預存程序的主體語句

例如，下面的預存程序將傳入金額和客戶ID，並將金額加到客戶帳戶餘額中：

CREATE PROCEDURE add_amount(IN amt INT, IN cust_id INT)
BEGIN
UPDATE customers SET balance = balance + amt WHERE id = cust_id;
END;

2. 呼叫預存程序：

呼叫預存程序的語法如下：

CALL procedure_name([parameter_value]);

例如，以下程式碼將呼叫剛剛建立的add_amount() 預存程序：

CALL add_amount(100, 1);

以上程式碼將在customers表中將ID為1的客戶餘額增加100元。

四、建立和使用儲存函數

1. 建立儲存函數：

#建立儲存函數的語法如下：

CREATE FUNCTION function_name ([parameter datatype [, …]])
RETURNS datatype
BEGIN
SQL statements;
RETURN return_value;
END;

• function_name：儲存函數的名稱
• parameter：儲存函數的參數
• datatype：參數的資料類型
##SQL statements：儲存函數的主體語句
• return_value：儲存函數的回傳值

例如，以下範例建立了一個用於計算平均值的儲存函數：

CREATE FUNCTION average (a INT, b INT)
RETURNS INT
BEGIN
DECLARE result INT;
SET result = (a + b) / 2;
RETURN result;
END;

呼叫儲存函數：

呼叫儲存函數的語法和呼叫預存程序相似：

SELECT function_name([parameter_value]);

例如，以下程式碼將呼叫剛剛建立的average()儲存函數：

SELECT average(10, 20);

以上程式碼將返回15，即(10 20)/2的結果。

五、預存程序和儲存函數的範例

預存程序的範例

下面的預存程序將自動計算所有客戶的平均年齡，並將結果放入另一個表中：

CREATE PROCEDURE calc_avg_age()
BEGIN
DECLARE avg_age FLOAT;
SELECT AVG(YEAR(CURDATE()) - YEAR(birth_date)) INTO avg_age FROM customers;
INSERT INTO statistics (name, value) VALUES ('avg_age', avg_age);
END;

儲存函數的範例

#以下儲存函數將傳回兩個日期之間的天數：

CREATE FUNCTION days_between (date1 DATE, date2 DATE)
RETURNS INT
BEGIN
DECLARE num_days INT;
SET num_days = DATEDIFF(date2, date1);
RETURN num_days;
END;

現在，我想知道2022年聖誕節和新年之間的天數，我們可以使用以下程式碼來呼叫上面的儲存函數：

SELECT days_between('2022-12-25', '2023-01-01');

以上程式碼將會傳回7，即兩個日期之間的天數。

六、結論

到此為止，我們已經了解了MySQL預存程序和儲存函數的概念、語法和範例。這些物件可以幫助我們更好地組織SQL程式碼，提高資料庫效能和可維護性。在實際應用中，預存程序和儲存函數被廣泛應用於資料倉儲、商業智慧、應用程式等領域。但是，也要注意不要過度使用它們，否則可能會降低程式碼的可讀性和可維護性。

以上是mysql 預存程序 儲存函數的詳細內容。更多資訊請關注PHP中文網其他相關文章！