MySQL 存储过程基础

简介

在这个实验中，你将学习 MySQL 存储过程的基础知识。目标是了解如何创建、调用和修改存储过程，以管理 MySQL 数据库中的数据。

你将首先创建一个数据库和一个名为 employees 的表。然后，你将编写一个名为 insert_employee 的存储过程，用于向 employees 表中插入数据。你将学习如何使用 CALL 语句调用此过程，以及如何向过程中添加输入参数。最后，你将学习如何使用 DROP PROCEDURE 语句删除该过程。

Skills Graph

编写插入数据的存储过程

在这一步中，你将学习如何在 MySQL 中创建一个向表中插入数据的存储过程。存储过程是存储在数据库中的预编译 SQL 语句。可以通过名称执行它们，这有助于提高性能和安全性。

首先，让我们创建一个简单的表来进行操作。打开你的终端，并使用以下命令连接到 MySQL 服务器：

mysql -u root -p

系统会提示你输入 root 用户的密码。输入密码后按回车键。

现在，创建一个名为 testdb 的数据库：

CREATE DATABASE IF NOT EXISTS testdb;
USE testdb;

接下来，创建一个名为 employees 的表，其结构如下：

CREATE TABLE IF NOT EXISTS employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    department VARCHAR(255)
);

现在我们已经有了一个表，让我们创建一个存储过程来向其中插入数据。存储过程使用 CREATE PROCEDURE 语句创建。我们将定义一个名为 insert_employee 的过程，用于插入一条新的员工记录。

以下是该存储过程的 SQL 代码：

DELIMITER //
CREATE PROCEDURE insert_employee (IN employee_name VARCHAR(255), IN employee_department VARCHAR(255))
BEGIN
    INSERT INTO employees (name, department) VALUES (employee_name, employee_department);
END //
DELIMITER ;

让我们来详细分析一下这段代码：

• DELIMITER //：这将语句分隔符从 ; 更改为 //。这是必要的，因为过程本身包含分号，我们需要告诉 MySQL 将整个过程定义视为一个单独的语句。
• CREATE PROCEDURE insert_employee：这声明了要创建一个名为 insert_employee 的存储过程。
• (IN employee_name VARCHAR(255), IN employee_department VARCHAR(255))：这定义了该过程的输入参数。employee_name 和 employee_department 是参数的名称，VARCHAR(255) 是它们的数据类型。IN 关键字表示这些是输入参数。
• BEGIN ... END：这个代码块包含了在调用该过程时将执行的 SQL 语句。
• INSERT INTO employees (name, department) VALUES (employee_name, employee_department);：这是一条 SQL 语句，用于向 employees 表中插入一行新数据，使用作为输入参数传入的值。
• DELIMITER ;：这将语句分隔符重置回 ;。

要执行这段代码，你可以直接将其复制并粘贴到你的 MySQL 终端中。

执行代码后，你可以通过运行以下命令来验证该过程是否已创建：

SHOW PROCEDURE STATUS WHERE db = 'testdb' AND name = 'insert_employee';

此命令将显示 insert_employee 过程的相关信息，包括其名称、所属数据库和创建日期。

你现在已经成功创建了一个向 employees 表中插入数据的存储过程。在下一步中，你将学习如何调用这个过程。

使用 CALL 语句调用存储过程

在上一步中，你创建了一个名为 insert_employee 的存储过程。在这一步，你将学习如何使用 CALL 语句调用这个存储过程。

首先，确保你已连接到 MySQL 服务器并使用 testdb 数据库。如果你还未连接，请使用以下命令：

mysql -u root -p

系统提示时输入密码。

USE testdb;

CALL 语句用于执行存储过程，其语法如下：

CALL procedure_name(argument1, argument2, ...);

在我们的例子中，存储过程名为 insert_employee，它接受两个参数：员工姓名和员工所在部门。

让我们调用 insert_employee 存储过程，插入一名名为“Alice Smith”、部门为“Engineering”的新员工：

CALL insert_employee('Alice Smith', 'Engineering');

这条语句将使用指定的参数执行 insert_employee 存储过程。

为了验证数据是否已正确插入，你可以查询 employees 表：

SELECT * FROM employees;

你应该会在表中看到新的一行，姓名为“Alice Smith”，部门为“Engineering”。id 会自动分配。

让我们再插入一名员工，“Bob Johnson”，部门为“Marketing”：

CALL insert_employee('Bob Johnson', 'Marketing');

再次通过查询 employees 表来验证插入情况：

SELECT * FROM employees;

现在你应该会在表中看到两行数据，一行是“Alice Smith”的，另一行是“Bob Johnson”的。

你现在已经成功使用 CALL 语句调用了存储过程 insert_employee，并验证了数据已正确插入。这展示了如何使用存储过程来封装和复用 SQL 逻辑。

为存储过程添加输入参数

在前面的步骤中，你创建并调用了一个名为 insert_employee 的存储过程，该过程接受两个输入参数：employee_name 和 employee_department。在这一步，你将学习如何为该存储过程添加另一个输入参数。

让我们为 insert_employee 存储过程添加一个 employee_salary 参数。这样，在插入新记录时，我们就可以指定员工的薪水了。

首先，你需要删除现有的存储过程。如果你不删除它，在尝试创建同名的存储过程时会出错。如果你还没有连接到 MySQL，请进行连接：

mysql -u root -p

系统提示时输入密码。

USE testdb;

删除现有的存储过程：

DROP PROCEDURE IF EXISTS insert_employee;

现在，让我们创建带有新输入参数的修改后的存储过程。

DELIMITER //
CREATE PROCEDURE insert_employee (IN employee_name VARCHAR(255), IN employee_department VARCHAR(255), IN employee_salary DECIMAL(10, 2))
BEGIN
    INSERT INTO employees (name, department) VALUES (employee_name, employee_department);
    -- 插入后更新薪水
    UPDATE employees SET salary = employee_salary WHERE name = employee_name AND department = employee_department;
END //
DELIMITER ;

让我们来分析一下这些更改：

• 我们在存储过程定义中添加了一个新的输入参数 IN employee_salary DECIMAL(10, 2)。DECIMAL(10, 2) 是薪水的数据类型，它允许最多 10 位数字，其中包括 2 位小数。
• 我们在 employees 表中添加了一个新列 salary。你需要使用以下 SQL 语句手动添加该列：

ALTER TABLE employees ADD COLUMN salary DECIMAL(10, 2);

• 我们添加了一条 UPDATE 语句，用于更新新插入员工的薪水。由于 INSERT 语句不直接支持设置薪水，所以我们先插入员工信息，然后根据姓名和部门更新薪水。

现在，让我们调用修改后的 insert_employee 存储过程，插入一名名为“Charlie Brown”、部门为“Finance”、薪水为 60000.00 的新员工：

CALL insert_employee('Charlie Brown', 'Finance', 60000.00);

为了验证数据是否已正确插入，你可以查询 employees 表：

SELECT * FROM employees;

你应该会在表中看到新的一行，姓名为“Charlie Brown”，部门为“Finance”，薪水为 60000.00。

你现在已经成功为存储过程 insert_employee 添加了一个输入参数，并验证了数据已正确插入。这展示了如何修改存储过程以满足新的需求。

删除存储过程

在这最后一步，你将学习如何从数据库中删除一个存储过程。删除存储过程会将其从数据库中移除，使其无法再被执行。

首先，确保你已连接到 MySQL 服务器并使用 testdb 数据库。如果你还未连接，请使用以下命令：

mysql -u root -p

系统提示时输入密码。

USE testdb;

DROP PROCEDURE 语句用于移除存储过程，其语法如下：

DROP PROCEDURE [IF EXISTS] procedure_name;

IF EXISTS 子句是可选的，但建议使用。如果存储过程不存在，它可以避免出错。

在我们的例子中，存储过程名为 insert_employee。让我们删除这个存储过程：

DROP PROCEDURE IF EXISTS insert_employee;

这条语句将从 testdb 数据库中移除 insert_employee 存储过程。

为了验证该存储过程是否已被删除，你可以再次尝试查看存储过程的状态：

SHOW PROCEDURE STATUS WHERE db = 'testdb' AND name = 'insert_employee';

这个命令应该返回一个空结果集，表明该存储过程已不存在。

或者，如果你尝试调用该存储过程，会收到一个错误：

CALL insert_employee('Test', 'Test', 1000);

这将产生类似如下的错误消息：ERROR 1305 (42000): PROCEDURE testdb.insert_employee does not exist。

你现在已经成功删除了存储过程 insert_employee。至此，关于在 MySQL 中创建、调用、修改和删除存储过程的实验练习就结束了。

总结

在这个实验中，你学习了 MySQL 存储过程的基础知识，从创建数据库和名为 employees 的表开始。然后，你使用 CREATE PROCEDURE 语句定义了一个名为 insert_employee 的存储过程，该过程将数据插入到 employees 表中。DELIMITER 命令用于处理存储过程定义中的分号。

本实验还介绍了如何为存储过程定义输入参数，指定它们的名称和数据类型。这使你在调用存储过程时能够传入值，从而使存储过程更加灵活和可复用。