MySQL进阶三板斧（二）揭开“存储过程”的神秘面纱

存储过程的起源

• MySQL是最受欢迎的开源RDBMS，被社区和企业广泛使用。存储过程是MySQL在5.0.1（开天辟地一版本）中增加的三大新功能之一，另外两个师兄弟是视图与触发器。均属于相对“高级”一点的数据库必需功能。

目录

一、什么是存储过程

一、MySQL存储过程的优势

优点

缺点

三、实际应用

1. 存储过程（创建）

2. 存储过程（查看）

3. 存储过程（调用）

4. 存储过程（修改&删除）

5. 存储过程（参数类型）

6. 存储过程（具体参数解析）

一、什么是存储过程

存储过程是存储在数据库目录中的一段声明性SQL语句。 可以通过触发器、其他存储过程以及Java，Python，PHP等应用程序直接调用。

先用一个简单的查询语句描述一下存储过程；

如下是一条SELECT语句从student表（该表测试数据在实际应用模块）中返回的所有行：

select ID,SNAME,SEX,AGE,CLASS,GRADE,HOBBY from student ORDER BY SNAME

#查询结果：

当你使用MySQL Workbench（如Navicat）或mysql shell向MySQL Server发出查询时，MySQL处理查询并返回结果集。

如果要将此查询保存在数据库服务器上以供以后执行，执行此查询的一种方法是使用存储过程。

以下 create procedure 语句创建一个新的存储过程，用于包装上面的查询：

DELIMITER $$
 
CREATE PROCEDURE GetStudents()
BEGIN
    SELECT 
        ID,SNAME,SEX,AGE,CLASS,GRADE,HOBBY
    FROM
        student
    ORDER BY SNAME;    
END$$
DELIMITER ;

存储过程是存储在MySQL服务器内部的声明性SQL语句的一部分。

在此示例中，我们刚刚创建了一个名称为的存储过程GetStudents()。

保存存储过程后，可以使用以下CALL语句调用存储过程：

CALL  GetStudents();

#查询结果：

如图，该语句返回与查询相同的结果。

• 首次调用存储过程时，MySQL在数据库目录中查找名称，编译存储过程的代码，将其放置在称为缓存的存储区中，然后执行该存储过程。
• 如果你在同一会话中再次调用相同的存储过程，则MySQL将从缓存中执行存储过程，而无需重新编译它。
• 存储过程可以具有参数，因此你可以向其传递值并返回结果。

例如，你可以有一个存储过程，可以按年级和班级返回学生信息数据。在这种情况下，年级和班级是存储过程的参数。

存储过程可能包含控制流语句（例如IF、CASE，这些语句LOOP允许你以过程方式实现代码）。

存储过程可以调用其他存储过程或存储函数，这使你可以调制代码。

一、MySQL存储过程的优势

优点

• 减少网络流量

存储过程有助于减少应用程序和MySQL Server之间的网络流量。因为应用程序不必发送多个冗长的SQL语句，而仅发送存储过程的名称和参数。

• 在数据库中集中业务逻辑

你可以使用存储过程来实现可被多个应用程序重用的业务逻辑。存储过程有助于减少在许多应用程序中重复相同逻辑的工作，并使数据库更加一致。

• 使数据库更安全

数据库管理员可以为仅访问特定存储过程的应用程序授予适当的特权，而无需在基础表上授予任何特权。

缺点

• 资源使用

如果使用许多存储过程，则每个连接的内存使用量将大大增加。

此外，由于MySQL的逻辑操作设计不佳，因此在存储过程中过度使用大量逻辑操作会增加CPU使用率。

• 故障排除

调试存储过程很困难。不幸的是，MySQL没有像其他企业数据库产品（如Oracle和SQL Server）那样提供任何调试存储过程的功能。

• 维护成本高

开发和维护存储过程通常需要并非所有应用程序开发人员都具备的专门技能。这可能会导致应用程序开发和维护方面的问题。

三、实际应用

1. 存储过程（创建）

存储过程简称过程，procedure，是一种用来处理数据的方式存储过程是一种没有返回值的函数

#创建过程语法

Create procedure 过程名字（【参数列表】）

Begin

    -- 过程体

End

#创建存储过程示例

DELIMITER $$
 
CREATE PROCEDURE GetStudents()
BEGIN
    SELECT 
        ID,SNAME,SEX,AGE,CLASS,GRADE,HOBBY
    FROM
        student
    ORDER BY SNAME;    
END$$
DELIMITER ;

2. 存储过程（查看）

函数的查看方式完全适用于过程，关键字换成procedure查看所有过程：

#查询所有存储过程
Show procedure status 

#模糊匹配
Show procedure status [like 'pattern']

#示例：显示如下图
Show procedure status like 'Get%' 

# 查看该存储过程的创建语句

Show create procedure 过程名;

3. 存储过程（调用）

CALL GetStudents();

4. 存储过程（修改&删除）

过程不能直接修改，只能先删除再新增

Drop procedure 过程名；

5. 存储过程（参数类型）

函数的参数需要数据类型指定，过程比函数更严格

过程有自己的类型限定，三种类型：

• in：数据只是从外部传入内部使用（值传递）可以是数值也可以是变量
• out：只允许过程内部使用（不用外部数据），给外部使用的（引用传递，外部的数据会被先清空才会进入内部），只能是变量
• inout：外部可以在内部使用，内部修改也可以给外部使用，典型的引用传递；只能传变量

基本语法：

Create procedure 过程名（过程类型 形参名字 数据类型 ，…）

-- 过程参数
DELIMITER $$
 
create procedure demo(in int_1 int,out int_2 int,inout int_3 int)
BEGIN
		-- 先查看三个变量
    SELECT int_1,int_2,int_3;  
END$$
DELIMITER ;

调用：out和inout类型的参数必须传入 变量，而不能是数值

正确调用：

1.设置变量

2.传入变量

存储过程对于变量的操作（返回）是滞后的，是在存储过程调用结束的时候，次啊会重新将颞部修改的值赋值给外部传入的全局变量。

-- 过程参数
DELIMITER $$
 
create procedure pro2(in int_1 int,out int_2 int,inout int_3 int)
BEGIN
		-- 先查看三个变量
    SELECT int_1,int_2,int_3;  

		-- 修改局部变量
		SET @int_1 = 1;
		SET @int_2 = 2;
		SET @int_3 = 3;
		-- 先查看三个局部变量
    SELECT int_1,int_2,int_3;  
		-- 先查看三个全局变量
    SELECT @int_1,@int_2,@int_3;  
		-- 修改全局变量
		SET @int_1 = 'aaa';
		SET @int_2 = 'bbb';
		SET @int_3 = 'ccc';
		-- 先查看三个全局变量
    SELECT @int_1,@int_2,@int_3;  

END$$
DELIMITER ;

测试：传入数据1、2、3，说明局部变量与全局变量无关

最后，在存储过程调用结果结束之后，系统会将局部变量重复返回给全局变量（out和inout）

在存储过程调用结束之后：out类型和inout类型会将过程内部对应的局部变量的值重新返回给对用的传入的全局变量。

6. 存储过程（具体参数解析）

完整创建语法如下：

--------------创建存储过程-----------------
 
CREATE PROC [ EDURE ] procedure_name [ ; number ]
    [ { @parameter data_type }
        [ VARYING ] [ = default ] [ OUTPUT ]
    ] [ ,...n ]
 
[ WITH
    { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]
 
[ FOR REPLICATION ]
 
AS sql_statement [ ...n ]

创建存储过程的具体参数解析，如下

1. procedure_name ：存储过程的名称，在前面加#为局部临时存储过程，加##为全局临时存储过程。 2. number：是可选的整数，用来对同名的过程分组，以便用一条 DROP PROCEDURE 语句即可将同组的过程一起除去。例如，名为 orders 的应用程序使用的过程可以命名为 orderproc;1、orderproc;2 等。DROP PROCEDURE orderproc 语句将除去整个组。如果名称中包含定界标识符，则数字不应包含在标识符中，只应在 procedure_name 前后使用适当的定界符。 3. @parameter：存储过程的参数。可以有一个或多个。用户必须在执行过程时提供每个所声明参数的值（除非定义了该参数的默认值）。存储过程最多可以有 2100 个参数。

使用 @ 符号作为第一个字符来指定参数名称。参数名称必须符合标识符的规则。每个过程的参数仅用于该过程本身；相同的参数名称可以用在其它过程中。默认情况下，参数只能代替常量，而不能用于代替表名、列名或其它数据库对象的名称。有关更多信息，请参见 EXECUTE。 4.data_type：参数的数据类型。所有数据类型（包括 text、ntext 和 image）均可以用作存储过程的参数。不过，cursor 数据类型只能用于 OUTPUT 参数。如果指定的数据类型为 cursor，也必须同时指定 VARYING 和 OUTPUT 关键字。有关 SQL Server 提供的数据类型及其语法的更多信息，请参见数据类型。 说明 对于可以是 cursor 数据类型的输出参数，没有最大数目的限制。 5.VARYING：指定作为输出参数支持的结果集（由存储过程动态构造，内容可以变化）。仅适用于游标参数。

6.default： 参数的默认值。如果定义了默认值，不必指定该参数的值即可执行过程。默认值必须是常量或 NULL。如果过程将对该参数使用 LIKE 关键字，那么默认值中可以包含通配符（%、_、[] 和 [^]）。

7.OUTPUT：表明参数是返回参数。该选项的值可以返回给 EXEC[UTE]。使用 OUTPUT 参数可将信息返回给调用过程。Text、ntext 和 image 参数可用作 OUTPUT 参数。使用 OUTPUT 关键字的输出参数可以是游标占位符。

8.RECOMPILE: 表明 SQL Server 不会缓存该过程的计划，该过程将在运行时重新编译。在使用非典型值或临时值而不希望覆盖缓存在内存中的执行计划时，请使用 RECOMPILE 选项。

9.ENCRYPTION: 表示 SQL Server 加密 syscomments 表中包含 CREATE PROCEDURE 语句文本的条目。使用 ENCRYPTION 可防止将过程作为 SQL Server 复制的一部分发布。 说明在升级过程中，SQL Server 利用存储在 syscomments 中的加密注释来重新创建加密过程。

10.FOR REPLICATION:指定不能在订阅服务器上执行为复制创建的存储过程。.使用 FOR REPLICATION 选项创建的存储过程可用作存储过程筛选，且只能在复制过程中执行。本选项不能和 WITH RECOMPILE 选项一起使用。

11.AS:指定过程要执行的操作。

12.sql_statement:过程中要包含的任意数目和类型的 Transact-SQL 语句。但有一些限制。