MySQL - 存储函数

你好,有抱负的数据库爱好者!今天,我们将深入MySQL存储函数的迷人世界。作为你友好的邻居计算机老师,我非常兴奋能引导你开启这段旅程,即使你之前从未编写过一行代码。所以,拿起你最喜欢的饮料,让我们开始吧!

MySQL - Stored Functions

MySQL存储函数是什么?

想象你有一本神奇的食谱书,在你需要的时候可以立刻做出你最喜欢的菜肴。MySQL存储函数对数据库来说就像是这样的东西!它们就像是预先写好的食谱(或函数),在你需要在数据库中执行特定任务时可以随时调用。

为什么使用存储函数?

  1. 可重用性:写一次,用多次!
  2. 一致性:确保到处都应用相同的逻辑。
  3. 安全性:控制谁能访问和修改你的函数。
  4. 性能:它们可能比重复编写相同的代码要快。

现在,让我们卷起袖子,创建我们的第一个存储函数!

创建你的第一个存储函数

这里有一个简单的例子来开始:

DELIMITER //

CREATE FUNCTION HelloWorld()
RETURNS VARCHAR(50)
DETERMINISTIC
BEGIN
RETURN '你好,世界!';
END //

DELIMITER ;

让我们分解一下:

  1. DELIMITER //:这告诉MySQL使用//作为语句分隔符,而不是默认的分号(;)。我们这样做是因为我们的函数中包含分号,我们不希望MySQL混淆。

  2. CREATE FUNCTION HelloWorld():这行声明我们正在创建一个名为"HelloWorld"的函数。

  3. RETURNS VARCHAR(50):这指定我们的函数将返回一个最多50个字符的字符串(VARCHAR)。

  4. DETERMINISTIC:这个关键字告诉MySQL,对于相同的输入,函数将总是返回相同的输出。

  5. BEGINEND:这些关键字包装了我们的函数体。

  6. RETURN '你好,世界!';:这是我们函数的实际代码。它简单地返回字符串"你好,世界!"。

  7. DELIMITER ;:这将分隔符设置回默认的分号。

现在,让我们调用我们的函数:

SELECT HelloWorld();

然后你就会看到"你好,世界!"作为结果。恭喜你,你已经创建并调用了你的第一个存储函数!

存储函数中的参数和变量

让我们通过添加参数和变量使我们的函数更有趣。

DELIMITER //

CREATE FUNCTION Greet(name VARCHAR(50))
RETURNS VARCHAR(100)
DETERMINISTIC
BEGIN
DECLARE greeting VARCHAR(100);
SET greeting = CONCAT('你好,', name, '!欢迎来到MySQL函数!');
RETURN greeting;
END //

DELIMITER ;

在这个例子中:

  1. 我们向函数添加了一个参数name VARCHAR(50)
  2. 我们使用DECLARE关键字声明了一个变量greeting
  3. 我们使用SET关键字给我们的变量赋值。
  4. 我们使用了CONCAT函数来组合字符串。

让我们调用我们的新函数:

SELECT Greet('Alice');

这将返回:"你好,Alice!欢迎来到MySQL函数!"

使用存储函数与数据库数据交互

现在,让我们创建一个实际上与我们的数据库交互的函数。假设我们有一个名为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 = '青铜';
ELSEIF employee_salary < 50000 THEN
SET bonus_tier = '白银';
ELSE
SET bonus_tier = '黄金';
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 '员工姓名',
emp_bonus AS '奖金金额',
emp_bonus_tier AS '奖金等级';
END //

DELIMITER ;

这个存储过程:

  1. 接受一个员工ID作为输入。
  2. 检索员工的完整姓名。
  3. 调用我们的CalculateBonus函数来获取奖金金额。
  4. 调用我们的CalculateBonusTier函数来获取奖金等级。
  5. 返回包含员工姓名、奖金金额和奖金等级的报告。

要调用这个存储过程:

CALL EmployeeBonusReport(1);

就这样!我们已经涵盖了MySQL存储函数的基础,从创建到在存储过程中的使用。记住,熟能生巧,所以不要害怕尝试你自己的函数。快乐编码!

Credits: Image by storyset