一、存储过程
1、介绍
MySQL 中的存储过程(Stored Procedure)是一组为了完成特定功能的 SQL 语句集合。用户可以通过指定的存储过程名称和参数(如果有的话)来调用并执行它。存储过程可以在数据库中创建、存储和管理,它提供了封装重复代码的能力,使得这些代码可以在多个地方被重复调用,而不需要每次都重写。
2、特点
- 封装性:将复杂的 SQL 逻辑封装在存储过程中,隐藏了实现细节。
- 可复用性:存储过程可以被多次调用,而不需要重复编写相同的 SQL 代码。
- 安全性:通过权限设置,可以限制对数据的访问,只允许特定的用户或角色调用存储过程。
- 提高性能:存储过程在首次执行时会被编译并存储在数据库中,之后的调用不需要再次编译,从而提高了执行效率。
- 支持事务:存储过程可以支持事务处理,确保数据的完整性和一致性。
3、创建语法
创建存储过程的基本语法如下:
DELIMITER //
CREATE PROCEDURE procedure_name (IN param1 datatype, OUT param2 datatype, ...)
BEGIN
-- SQL 语句
END //
DELIMITER ;
- DELIMITER:用于更改默认的语句结束符(默认为分号),因为在存储过程中可能会包含多个分号。
- CREATE PROCEDURE:用于创建存储过程。
- procedure_name:存储过程的名称。
- IN、OUT:用于指定参数是输入参数还是输出参数。
- BEGIN … END:存储过程的主体,包含要执行的 SQL 语句。
注意:
- 参数列表包含三部分:参数模式 参数名 参数类型。
- IN:该参数可以作为输入,也就是该参数需要调用放传入值。
- OUT:该参数可以作为输出,也就是该参数可以作为返回值。
- INOUT:该参数既可以作为输入又可以作为输出,也就是该参数既需要传入值,又可以返回值。
- 如果存储过程体仅仅一句话,BEGIN END可以省略,存储过程体中的每条SQL语句的结尾要求必须加分号。存储过程的结尾可以使用DELIMITER重新设置;语法DELLIMITER 结束标记。
4、调用语法
(1)语法
调用存储过程的基本语法如下:
CALL procedure_name(param1, param2, ...);
(2)示例
- 空参列表
DELIMITER //
CREATE PROCEDURE myp1()
BEGIN
INSERT INTO admin(username, password) values
('john1', '0000'),
('john2', '0000'),
('john3', '0000'),
('john4', '0000'),
('john5', '0000');
END //
DELIMITER ;
-- 调用
CALL myp1();
- 带in模式参数的存储过程
以下是一个简单的示例,展示如何创建一个存储过程来插入一条记录,并返回新记录的 ID:
DELIMITER //
CREATE PROCEDURE InsertUser(IN username VARCHAR(50), IN email VARCHAR(100), OUT user_id INT)
BEGIN
INSERT INTO users (username, email) VALUES (username, email);
SET user_id = LAST_INSERT_ID();
END //
DELIMITER ;
-- 调用存储过程
CALL InsertUser('John Doe', 'johndoe@example.com', @user_id);
-- 查询输出参数的值
SELECT @user_id;
在这个示例中,InsertUser 存储过程接受两个输入参数(username 和 email)和一个输出参数(user_id)。它首先向 users 表中插入一条新记录,然后使用 LAST_INSERT_ID() 函数获取新记录的 ID,并将其赋值给输出参数 user_id。最后,我们可以通过调用 CALL 语句来执行存储过程,并使用用户定义的变量 @user_id 来接收输出参数的值。
- 创建带out模式的存储过程
在MySQL中,当你想从存储过程返回一个或多个值给调用者,但不想让调用者直接修改这些值时,你可以使用OUT模式的参数。以下是一个创建带有OUT模式参数的存储过程的示例:
DELIMITER //
CREATE PROCEDURE GetEmployeeCount(OUT employeeCount INT)
BEGIN
-- 假设我们有一个名为employees的表,我们想计算其中的员工数量
SELECT COUNT(*) INTO employeeCount FROM employees;
END //
DELIMITER ;
在这个例子中,我们创建了一个名为GetEmployeeCount的存储过程,它接受一个OUT模式的参数employeeCount。在存储过程内部,我们使用了一个SELECT … INTO语句来从employees表中计算员工数量,并将结果存储在employeeCount变量中。由于employeeCount是OUT模式的参数,所以当存储过程执行完毕后,它的值将被返回给调用者。
要调用这个存储过程并查看OUT参数的值,你可以这样做:
SET @count = 0; -- 初始化一个用户定义的变量来接收返回值
CALL GetEmployeeCount(@count); -- 调用存储过程,并传入@count作为OUT参数
SELECT @count; -- 查看变量@count的值,它现在包含了从存储过程中返回的员工数量
在这个调用中,我们首先设置了一个用户定义的变量@count的值为0。然后,我们调用了GetEmployeeCount存储过程,并将@count作为参数传递给它。由于@count是OUT模式的参数,存储过程将把计算得到的员工数量存储在@count中。最后,我们查询了@count的值,它现在包含了从存储过程中返回的员工数量。
- 创建带inout模式参数的存储过程:
在MySQL中,INOUT 是存储过程参数的一种模式,它允许你传递一个值给存储过程,并在存储过程中修改这个值,然后将修改后的值传回给调用者。
以下是一个创建带有 INOUT 模式参数的存储过程的示例:
DELIMITER //
CREATE PROCEDURE SetAndIncrement(INOUT counter INT)
BEGIN
-- 在这里,我们对counter进行了一些操作,比如增加它的值
SET counter = counter + 1;
-- 你也可以添加其他逻辑来处理counter
END //
DELIMITER ;
在这个例子中,我们创建了一个名为 SetAndIncrement 的存储过程,它接受一个 INOUT 类型的参数 counter。在存储过程内部,我们将 counter 的值增加了1。
要调用这个存储过程并查看 INOUT 参数的变化,你可以这样做:
SET @myCounter = 10; -- 初始化一个用户定义的变量
CALL SetAndIncrement(@myCounter); -- 调用存储过程,并传入@myCounter作为参数
SELECT @myCounter; -- 查看变量@myCounter的值,现在它应该是11
在这个调用中,我们首先设置了一个用户定义的变量 @myCounter 的值为10。然后,我们调用了 SetAndIncrement 存储过程,并将 @myCounter 作为参数传递给它。由于 counter 是 INOUT 类型的参数,存储过程可以修改它的值。最后,我们查询了 @myCounter 的值,现在它应该是11,因为存储过程已经将它的值增加了1。
5、删除语法
在MySQL中,要删除一个已经存在的存储过程,你可以使用DROP PROCEDURE语句。以下是删除存储过程的基本语法:
DROP PROCEDURE IF EXISTS procedure_name;
在这里,procedure_name是你想要删除的存储过程的名称。IF EXISTS子句是可选的,但它是一个好习惯,因为它会检查存储过程是否存在,如果存储过程不存在,则不会抛出错误。
例如,如果你有一个名为SetAndIncrement的存储过程,并且你想要删除它,你可以使用以下SQL语句:
DROP PROCEDURE IF EXISTS SetAndIncrement;
执行这条语句后,如果SetAndIncrement存储过程存在,它将被删除;如果不存在,则不会有任何错误发生。
6、查看语法
在MySQL中,要查看存储过程的定义或相关信息,你可以使用不同的方法。以下是一些常见的方法来查看存储过程:
- 使用SHOW CREATE PROCEDURE语句
你可以使用SHOW CREATE PROCEDURE语句来查看存储过程的创建语句。例如:
SHOW CREATE PROCEDURE SetAndIncrement;
这将返回SetAndIncrement存储过程的完整创建语句。
- 从information_schema.ROUTINES表中查询
information_schema是一个包含数据库元数据的特殊数据库。你可以从ROUTINES表中查询存储过程的信息。例如:
SELECT ROUTINE_NAME, ROUTINE_TYPE, DATA_TYPE, ROUTINE_DEFINITION
FROM information_schema.ROUTINES
WHERE ROUTINE_SCHEMA = 'your_database_name' AND ROUTINE_NAME = 'SetAndIncrement';
在上面的查询中,你需要将your_database_name替换为你的数据库名称。ROUTINE_DEFINITION列将包含存储过程的创建语句。
7、修改语法
存储过程不能修改,若涉及到修改,可以先删除,然后重建。