MySQL - 儲存函數
你好,有志於數據庫的愛好者們!今天,我們將進入MySQL 儲存函數的迷人世界。作為你們親切鄰居的計算機老師,我很興奮能夠引導你們開展這次旅程,即使你們從未寫過一行代碼。所以,拿起你們喜歡的飲料,讓我們開始吧!
MySQL 儲存函數是什麼?
想像一下你有一本神奇的食譜書,可以隨時為你烹製你喜歡的菜肴。MySQL 儲存函數對於數據庫來說就有點像這樣!它們就像事先寫好的食譜(或函數),當你在數據庫中需要執行特定任務時,可以隨時調用它們。
為什麼使用儲存函數?
- 可重用性:寫一次,使用多次!
- 一致性:確保到處應用相同的邏輯。
- 安全性:控制誰可以訪問和修改你的函數。
- 性能:它們可能比重複寫入相同的代碼更快。
現在,讓我們捋起袖子,創建我們的第一個儲存函數!
創建你的第一個儲存函數
這裡有一個簡單的例子來讓我們開始:
DELIMITER //
CREATE FUNCTION HelloWorld()
RETURNS VARCHAR(50)
DETERMINISTIC
BEGIN
RETURN 'Hello, World!';
END //
DELIMITER ;
讓我們分解一下:
-
DELIMITER //
:這告訴MySQL使用//
作為語句分隔符,而不是默認的分號(;)。我們這樣做是因為我們的函數中包含分號,我們不希望MySQL混淆。 -
CREATE FUNCTION HelloWorld()
:這一行聲明我們正在創建一個名為 "HelloWorld" 的函數。 -
RETURNS VARCHAR(50)
:這指定我們的函數將返回一個最多50個字符的字符串(VARCHAR)。 -
DETERMINISTIC
:這個關鍵字告訴MySQL,對於相同的輸入,函數將總是返回相同的輸出。 -
BEGIN
和END
:這些關鍵字包裹我們函數的體。 -
RETURN 'Hello, World!';
:這是我們函數的實際代碼。它簡單地返回字符串 "Hello, World!"。 -
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 ;
在這個例子中:
- 我們向函數中添加了一個參數
name VARCHAR(50)
。 - 我們使用
DECLARE
關鍵字聲明了一個變量greeting
。 - 我們使用
SET
關鍵字為我們的變量分配了一個值。 - 我們使用了
CONCAT
函數來組合字符串。
讓我們調用我們的新函數:
SELECT Greet('Alice');
這應該返回: "Hello, Alice! Welcome to MySQL functions!"
使用儲存函數與數據庫數據交互
現在,讓我們創建一個實際與數據庫交互的函數。想像我們有一個名為 employees
的表,其中包含列 id
、first_name
、last_name
和 salary
。
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 ;
這個函數:
- 接受一個
employee_id
作為輸入。 - 聲明
employee_salary
和bonus
變量。 - 從
employees
表中檢索員工的薪資。 - 根據薪資計算10%的獎金。
- 返回計算出的獎金。
使用這個函數:
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 ;
這個函數使用 IF
、ELSEIF
和 ELSE
來根據員工的薪資確定獎金等級。
從儲存過程中調用儲存函數
最後,讓我們看看如何在我們的儲存過程中使用我們的儲存函數:
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 ;
這個儲存過程:
- 接受一個員工ID作為輸入。
- 從
employees
表中檢索員工的全名。 - 調用我們的
CalculateBonus
函數來獲取獎金金額。 - 調用我們的
CalculateBonusTier
函數來獲取獎金等級。 - 返回包含員工姓名、獎金金額和獎金等級的報告。
調用此過程:
CALL EmployeeBonusReport(1);
這裡有你!我們已經涵蓋了MySQL 儲存函數的基本知識,從創建到在儲存過程中的使用。記住,熟能生巧,所以不要害怕嘗試你自己的函數。快樂編程!
MySQL 函數方法 |
---|
CREATE FUNCTION |
RETURNS |
DETERMINISTIC |
BEGIN...END |
DECLARE |
SET |
IF...ELSEIF...ELSE |
SELECT...INTO |
RETURN |
Credits: Image by storyset