【17.0】MySQL进阶知识之存储过程

【一】什么是存储过程

存储过程就类似于Python中的自定义函数

内部包含了一系列可以执行的SQL语句，存储过程存储在MySQL服务端中，可以通过调用存储过程触发内部的SQL语句

• 存储过程是在关系型数据库中存储的一组预定义的SQL语句集合，可以接收参数并返回结果。
  • 它们被封装在数据库服务器中，并由应用程序通过调用存储过程来执行特定的数据库操作。

【二】存储过程的特点

• 预编译：
  • 存储过程在首次创建时会被编译和优化，之后每次执行时都不需要再进行编译，这样可以提高数据库的执行效率。
• 数据库端执行：
  • 与应用程序中直接执行SQL语句相比，存储过程在数据库服务器端执行，减少了网络传输开销，提高了数据访问性能。
• 代码重用：
  • 存储过程可以被多个应用程序共享和重用，避免了重复编写相同的SQL语句，提高了开发效率。
• 安全性：
  • 通过存储过程，可以将对数据库的访问权限限制在一定范围内，从而提高数据的安全性。
• 事务支持：
  • 存储过程可以包含事务处理逻辑，保证数据库操作的一致性和完整性。
• 简化复杂操作：
  • 存储过程可以执行复杂的数据操作和计算，简化了应用程序的开发和维护过程。

【三】如何使用存储过程

【1】定义存储器

create procedure 存储过程的名字(形参1,形参2...)
begin
	sql 代码
end

【2】调用

call 存储过程的名字();

【3】查看存储过程具体信息

show create procedure pro1;

【4】查看所有存储过程

show procedure status;

【5】删除存储过程

drop procedure pro1;

【四】存储过程的开发模式

【1】存储过程的三种开发模式

（1）第一种(提前编好存储过程)

• 应用程序
  • 程序员写代码开发
• MySQL
  • 提前编好存储过程，供应用程序调用
• 优点
  • 开发效率提升、执行效率提升
• 缺点
  • 考虑到人为因素、跨部门沟通等问题
  • 后续的存储过程的扩展性差

（2）第二种(自己动手写据库操作)

• 应用程序
  • 程序员写代码开发之前
  • 涉及到数据库操作需要自己动手写
• 优点
  • 扩展性高
• 缺点
  • 开发效率低
  • 编写SQL语句繁琐，并且后续还需要考虑优化问题

（3）第三种(ORM框架)

• 应用程序
  • 只写程序代码
  • 不写SQL语句
  • 基于别人写好的操作MySQL的Python的框架直接调用即可(ORM框架)
• 优点
  • 开发效率比上面的两种高
• 缺点
  • 语句的扩展性差
  • 可能会出现效率低下的问题

【2】存储过程的三种开发模式参考版

（1）命令式开发模式（Imperative Development Model）

• 在这种模式下，存储过程以类似编程语言的方式编写，包括条件语句、循环结构和异常处理等。
• 这种模式下的存储过程逻辑较为灵活，可以使用各种流程控制语句，但也容易导致存储过程复杂难懂且维护困难。

（2）声明式开发模式（Declarative Development Model）

• 在这种模式下，存储过程的逻辑将更多地关注数据操作语句，如查询、插入、更新和删除等。
• 存储过程的设计主要通过声明数据操作的目标和条件来实现，而不是依赖于具体的流程控制语句。
• 这种模式下的存储过程简洁明了，更易于理解和维护。

（3）触发器开发模式（Trigger Development Model）

• 触发器是一种特殊的存储过程，它与数据库表相关联，并在预定义的事件发生时自动触发执行。
• 触发器可以用于实现数据一致性、数据验证、日志记录等功能。
• 触发器的开发模式与其他存储过程略有不同，因为触发器的执行时机和上下文是由数据库事件触发的，而不是由外部调用。

【五】开启存储过程的参考步骤

【1】创建存储过程

• 使用数据库管理工具（如MySQL Workbench、SQL Server Management Studio等），编写存储过程的SQL语句，并在数据库中创建该存储过程。
• 存储过程的创建可以包括参数的定义和逻辑的实现。

【2】参数定义

• 如果存储过程需要接收参数，可以在存储过程的创建语句中定义参数类型、名称和属性。
• 参数可以分为输入参数（用于传递数据给存储过程）和输出参数（用于存储过程返回结果给调用者）。

【3】存储过程逻辑

• 在存储过程中编写SQL语句或其他处理逻辑，用于完成特定的数据库操作。
• 这些逻辑可以包括查询、插入、更新、删除等操作，也可以包括条件判断、循环、异常处理等控制结构。

【4】调用存储过程

• 在应用程序中通过SQL语句调用存储过程。
• 调用存储过程时，提供必要的参数，并获取可能的输出结果。
• 调用存储过程可以使用特定的SQL语法，也可以通过数据库连接的API来实现。

【5】处理存储过程返回的结果

• 根据存储过程的设计，处理从存储过程返回的结果。
• 这可能包括读取查询结果、解析输出参数值等。
• 处理结果的方式与具体的应用程序和数据库访问框架有关。

【具体演示】

（1）在MySQL中

• 创建存储过程

delimiter $$

create procedure p1(
	  in m int, # in表示这个参数必须只能是传入不能被返回出去
    in n int.
    out res int, # out表示这个参数可以被返回出去，还有一个inout表示即可以传入也可以被返回出去
)
begin
    select name from emp where dep_id > m and dep_id <m;
    set res = 666 # 将res变量修改，用来标识当前的存储过程代码确实执行了
end $$

delimiter ;

• 使用存储过程

# 定义存储过程中的变量
set @res=10;  

# 查看写好的存储过程
select @res; 

# 调用存储过程
call p1(1,5,@res)  

# 查看存储过程信息
select @res; 

（2）在Python中

• 在Python中，我们通常使用数据库来存储和管理数据。存储过程是一组预定义的SQL语句集合，它们以特定的名称存储在数据库中，并可以在需要时被调用执行。在Python代码中演示存储过程的示例，我们将使用MySQL数据库。
• 首先，确保你已安装mysql-connector-python库，可以使用以下命令进行安装：

pip install mysql-connector-python

• 使用以下示例代码来演示在Python中调用MySQL存储过程：

import mysql.connector

# 连接到数据库
connection = mysql.connector.connect(
    host='<YOUR_HOST>',
    user='<YOUR_USERNAME>',
    password='<YOUR_PASSWORD>',
    database='<YOUR_DATABASE>'
)

# 调用存储过程
def call_stored_procedure():
    try:
        cursor = connection.cursor()
        cursor.callproc('<YOUR_STORED_PROCEDURE_NAME>')
        for result in cursor.stored_results():
            print(result.fetchall())
        cursor.close()

    except (mysql.connector.Error) as e:
        print(f"Error calling stored procedure: {e}")

# 调用存储过程函数
call_stored_procedure()

# 关闭数据库连接
connection.close()

• 在上述示例代码中，你需要替换以下参数：

  • <YOUR_HOST>: 数据库主机名

  • <YOUR_USERNAME>: 数据库用户名

  • <YOUR_PASSWORD>: 数据库密码

  • <YOUR_DATABASE>: 数据库名称

  • <YOUR_STORED_PROCEDURE_NAME>: 存储过程的名称

在call_stored_procedure函数中，我们使用cursor.callproc命令来调用存储过程。然后，通过cursor.stored_results方法获取存储过程的结果，并使用fetchall方法打印结果。

（3）代码演示参考

• 存储过程（Stored Procedure）是预先编译好并存储在数据库中的一组SQL语句。
  • 它可以接受参数、执行复杂的逻辑和业务操作，并可以返回结果集或输出参数。
• 下面是一个简单的存储过程代码演示及详解：

CREATE PROCEDURE GetEmployeeCountByDepartment
    @DepartmentName NVARCHAR(50),
    @EmployeeCount INT OUTPUT
AS
BEGIN
    SELECT @EmployeeCount = COUNT(*) 
    FROM Employees
    WHERE Department = @DepartmentName
END

• 这个存储过程名为"GetEmployeeCountByDepartment"，有两个参数：
  • @DepartmentName和@EmployeeCount。
  • @DepartmentName是输入参数，表示要查询的部门名称；
  • @EmployeeCount是输出参数，用于存储查询结果。
• 在存储过程体内，我们使用了SELECT语句来查询Employees表中符合条件的记录数量，并将结果赋值给@EmployeeCount。
  • 具体来说，COUNT(*)用于统计符合条件的记录数量，WHERE子句过滤部门名称等于输入参数@DepartmentName的记录。
• 使用存储过程时，可以通过以下示例代码来调用它并获取结果：

DECLARE @Result INT
EXEC GetEmployeeCountByDepartment 'IT', @Result OUTPUT
SELECT @Result AS 'EmployeeCount'

• 在这段示例代码中，首先声明了一个叫做@Result的整型变量。
  • 然后使用EXEC关键字调用存储过程，并将部门名称参数设置为'IT'，同时指定输出参数@Result。
  • 最后使用SELECT语句输出@Result的值，以获取查询结果。

【六】存储过程的优点

【1】代码复用

• 存储过程只需要编写一次，可以在多个地方被调用，提高了代码的复用性。

【2】数据库性能优化

• 存储过程在数据库中被编译、优化和缓存，可以减少网络传输开销，提高查询性能。

【3】安全性增强

• 存储过程可以限制直接对表的访问权限，通过存储过程来进行数据操作，可以为数据库提供更好的安全性。