MySQL - 儲存函數

你好,有志於數據庫的愛好者們!今天,我們將進入MySQL 儲存函數的迷人世界。作為你們親切鄰居的計算機老師,我很興奮能夠引導你們開展這次旅程,即使你們從未寫過一行代碼。所以,拿起你們喜歡的飲料,讓我們開始吧!

MySQL - Stored Functions

MySQL 儲存函數是什麼?

想像一下你有一本神奇的食譜書,可以隨時為你烹製你喜歡的菜肴。MySQL 儲存函數對於數據庫來說就有點像這樣!它們就像事先寫好的食譜(或函數),當你在數據庫中需要執行特定任務時,可以隨時調用它們。

為什麼使用儲存函數?

  1. 可重用性:寫一次,使用多次!
  2. 一致性:確保到處應用相同的邏輯。
  3. 安全性:控制誰可以訪問和修改你的函數。
  4. 性能:它們可能比重複寫入相同的代碼更快。

現在,讓我們捋起袖子,創建我們的第一個儲存函數!

創建你的第一個儲存函數

這裡有一個簡單的例子來讓我們開始:

DELIMITER //

CREATE FUNCTION HelloWorld()
RETURNS VARCHAR(50)
DETERMINISTIC
BEGIN
RETURN 'Hello, World!';
END //

DELIMITER ;

讓我們分解一下:

  1. DELIMITER //:這告訴MySQL使用 // 作為語句分隔符,而不是默認的分號(;)。我們這樣做是因為我們的函數中包含分號,我們不希望MySQL混淆。

  2. CREATE FUNCTION HelloWorld():這一行聲明我們正在創建一個名為 "HelloWorld" 的函數。

  3. RETURNS VARCHAR(50):這指定我們的函數將返回一個最多50個字符的字符串(VARCHAR)。

  4. DETERMINISTIC:這個關鍵字告訴MySQL,對於相同的輸入,函數將總是返回相同的輸出。

  5. BEGINEND:這些關鍵字包裹我們函數的體。

  6. RETURN 'Hello, World!';:這是我們函數的實際代碼。它簡單地返回字符串 "Hello, World!"。

  7. DELIMITER ;:這將分隔符設置回默認的分號。

現在,讓我們調用我們的函數:

SELECT HelloWorld();

瞧!你應該會看到 "Hello, World!" 作為結果。恭喜你,你剛剛創建並調用了你的第一個儲存函數!

在儲存函數中添加參數和變量

讓我們通過添加參數和變量來使事情更有趣。

DELIMITER //

CREATE FUNCTION Greet(name VARCHAR(50))
RETURNS VARCHAR(100)
DETERMINISTIC
BEGIN
DECLARE greeting VARCHAR(100);
SET greeting = CONCAT('Hello, ', name, '! Welcome to MySQL functions!');
RETURN greeting;
END //

DELIMITER ;

在這個例子中:

  1. 我們向函數中添加了一個參數 name VARCHAR(50)
  2. 我們使用 DECLARE 關鍵字聲明了一個變量 greeting
  3. 我們使用 SET 關鍵字為我們的變量分配了一個值。
  4. 我們使用了 CONCAT 函數來組合字符串。

讓我們調用我們的新函數:

SELECT Greet('Alice');

這應該返回: "Hello, Alice! Welcome to MySQL functions!"

使用儲存函數與數據庫數據交互

現在,讓我們創建一個實際與數據庫交互的函數。想像我們有一個名為 employees 的表,其中包含列 idfirst_namelast_namesalary

DELIMITER //

CREATE FUNCTION CalculateBonus(employee_id INT)
RETURNS DECIMAL(10,2)
READS SQL DATA
BEGIN
DECLARE employee_salary DECIMAL(10,2);
DECLARE bonus DECIMAL(10,2);

SELECT salary INTO employee_salary
FROM employees
WHERE id = employee_id;

SET bonus = employee_salary * 0.1;

RETURN bonus;
END //

DELIMITER ;

這個函數:

  1. 接受一個 employee_id 作為輸入。
  2. 聲明 employee_salarybonus 變量。
  3. employees 表中檢索員工的薪資。
  4. 根據薪資計算10%的獎金。
  5. 返回計算出的獎金。

使用這個函數:

SELECT first_name, last_name, CalculateBonus(id) AS bonus
FROM employees
WHERE id = 1;

這將返回id為1的員工的姓名和計算出的獎金。

在儲存函數中添加控制流程

讓我們為我們的獎金計算添加一些控制流程:

DELIMITER //

CREATE FUNCTION CalculateBonusTier(employee_id INT)
RETURNS VARCHAR(20)
READS SQL DATA
BEGIN
DECLARE employee_salary DECIMAL(10,2);
DECLARE bonus_tier VARCHAR(20);

SELECT salary INTO employee_salary
FROM employees
WHERE id = employee_id;

IF employee_salary < 30000 THEN
SET bonus_tier = 'Bronze';
ELSEIF employee_salary < 50000 THEN
SET bonus_tier = 'Silver';
ELSE
SET bonus_tier = 'Gold';
END IF;

RETURN bonus_tier;
END //

DELIMITER ;

這個函數使用 IFELSEIFELSE 來根據員工的薪資確定獎金等級。

從儲存過程中調用儲存函數

最後,讓我們看看如何在我們的儲存過程中使用我們的儲存函數:

DELIMITER //

CREATE PROCEDURE EmployeeBonusReport(IN emp_id INT)
BEGIN
DECLARE emp_name VARCHAR(100);
DECLARE emp_bonus DECIMAL(10,2);
DECLARE emp_bonus_tier VARCHAR(20);

SELECT CONCAT(first_name, ' ', last_name) INTO emp_name
FROM employees
WHERE id = emp_id;

SET emp_bonus = CalculateBonus(emp_id);
SET emp_bonus_tier = CalculateBonusTier(emp_id);

SELECT emp_name AS 'Employee Name',
emp_bonus AS 'Bonus Amount',
emp_bonus_tier AS 'Bonus Tier';
END //

DELIMITER ;

這個儲存過程:

  1. 接受一個員工ID作為輸入。
  2. employees 表中檢索員工的全名。
  3. 調用我們的 CalculateBonus 函數來獲取獎金金額。
  4. 調用我們的 CalculateBonusTier 函數來獲取獎金等級。
  5. 返回包含員工姓名、獎金金額和獎金等級的報告。

調用此過程:

CALL EmployeeBonusReport(1);

這裡有你!我們已經涵蓋了MySQL 儲存函數的基本知識,從創建到在儲存過程中的使用。記住,熟能生巧,所以不要害怕嘗試你自己的函數。快樂編程!

MySQL 函數方法
CREATE FUNCTION
RETURNS
DETERMINISTIC
BEGIN...END
DECLARE
SET
IF...ELSEIF...ELSE
SELECT...INTO
RETURN

Credits: Image by storyset