MySQL基础学习: 第六章 存储过程和函数

一、存储过程

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、修改语法

存储过程不能修改，若涉及到修改，可以先删除，然后重建。

二、函数