MySQL基础知识（六)——存储过程

一 .存储过程

1.定义：

存储过程是SQL语句和控制语句的预编译集合，以一个名称存储并作为一个单元处理。

2.SQL命令的执行过程：

（1）输入SQL命令；

（2）MySQL引擎对输入的SQL命令进行语法分析。

（3）引擎分析MySQL的语法正确后；

（4）编译成MySQL引擎可以识别的命令。

（5）最后开始执行结果；

（6）并将执行结果返回给客户端。

    省略语法分析和编译环节，MySQL执行效率就可以提高。

3.存储过程的优点：

• 增强SQL语句的功能和灵活性；

      （ 解释：在存储过程内可以写控制语句，那么就有很强的灵活性完成复杂的判断以及较为复杂的运算；

                     存储过程存储在数据库内，可以由应用程序调用执行，而且允许用户声明变量以及进行流程控制；

                     存储过程可以接收参数，可以接收输入类型的参数，也可以接收输出类型的参数，并且可以存在多个返回值。）

• 实现较快的执行速度；

     （ 解释：存储过程是预编译的，只有当第一次调用存储过程时，进行语法分析和编译的过程，并将编译结果存储在内存中，                      以后再调用直接从内存中使用编译后的结果，加快了执行速度；存储过程的效率要比单一SQL的执行效率高。）

• 减少网络流量；

     （解释：如果通过客户端单独发送SQL语句让服务器执行，那么通过http协议所提交的数据量相对来说较大，

                   使用存储过程只需要传递存储过程名字和参数，传递給服务器的数据量相对来说较小。)

4.语法

          CREATE  [DEFINER = { user | CURRENT_USER }]

          PROCEDURE sp_name ([proc_parameter[,...]])

          [characteristic ...] routine_body

          [DEFINER = { user | CURRENT_USER }]省略掉表示默认为当前用户，sp_name是存储过程的名字。

      （1）参数proc_parameter:

           [ IN | OUT | INOUT ] param_name type

•   IN，表示该参数的值必须在调用存储过程时指定；
•   OUT，表示该参数的值可以被存储过程改变，并且可以返回；
•   INOUT，表示该参数的调用时指定，并且可以被改变和返回。

      （2）特性

               COMMENT ' String ' | {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY {  DEFINER | INVOKER}

•   COMMENT:注释
•   CONTAINS SQL:包含SQL语句，但不包含读或写数据的语句
•   NO SQL:不包含SQL语句
•   READS SQL DATA: 包含读数据的语句
•   MODIFIES SQL DATA:包含写数据的语句
•   SQL SECURITY {DEFINER | INVOKER}:指名谁有权限来执行

      （3）过程体

•   过程体由合法的SQL语句构成；
•   过程体可以是任意SQL语句；
•   过程体如果为复合结构则使用BEGIN...END语句；
•   复合结构可以包含声明，循环，控制结构。

 5.调用存储过程

  语法：

• CALL sp_name([parameter[,...]])
• CALL sp_name[()]

6.创建一个无参的存储过程

# 创建一个不带参数的存储过程
CREATE PROCEDURE spl() SELECT VERSION();
# 调用存储过程--方法一
CALL spl();
# 调用存储过程--方法二
CALL spl;

6.创建带有IN类型参数的存储过程 

# 创建带有IN类型参数的存储过程 ，根据userId删除用户
# 参数名不能和数据表中名称相同
CREATE PROCEDURE removeUserById(IN p_id INT UNSIGNED)
BEGIN 
DELETE FROM inserttest WHERE id = p_id;
END 
# 调用存储过程
CALL removeUserById(15);

7.创建带有IN和OUT类型的存储过程

# 删除指定id的用户，并且返回剩余的记录数
CREATE PROCEDURE removeUserAndReturnUserNum(IN p_id INT UNSIGNED,OUT userNums INT UNSIGNED)
BEGIN 
DELETE FROM inserttest WHERE id = p_id;
SELECT COUNT(id) FROM inserttest INTO userNums;
END 
# 使用存储过程,第二个参数为接收参数，所以使用变量来接收
CALL removeUserAndReturnUserNum(18,@nums);
# 查看返回的结果-->变量
SELECT @nums;
# 这样是设置用户变量，只对当前登录的用户所使用客户端有有效
SET @i = 7;

8.创建带有多个OUT类型的存储过程

# 系统函数，得到插入删除以及更新到被影响的记录总数
SELECT ROW_COUNT();
# 根据用户名删除用户，并返回删除的用户和剩余的用户
CREATE PROCEDURE removeUserByNameAndReturnUserNum(IN p_username VARCHAR(20),OUT deleteUsers INT UNSIGNED,OUT userCounts INT UNSIGNED)
BEGIN 
DELETE FROM inserttest WHERE username = p_username;
SELECT ROW_COUNT() INTO deleteUsers; 
SELECT COUNT(id) FROM inserttest INTO userCounts;
END 
# 使用存储过程,第二个参数为接收参数，所以使用变量来接收
CALL removeUserByNameAndReturnUserNum('111',@deleteUsers,@userCounts);
# 查看删除的记录数
SELECT @deleteUsers;
# 查看剩余的记录数
SELECT @userCounts;

9.修改存储过程

  语法：ALTER  PROCEDURE sp_name [characteristic ...] COMMENT 'string' | {CONTAINS SQL |  NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY {  DEFINER | INVOKER }

 只能修改简单的特性，不能修改过程体，如果需要修改过程体，只能删除存储过程，再重新创建。

10.删除存储过程

  语法：DROP PROCEDURE [IF EXISTS] sp_name

# 删除存储过程
DROP PROCEDURE removeUserByNameAndReturnUserNum;

11.存储过程与自定义函数的区别

• 存储过程实现的功能要复杂一些；而函数的针对性更强；
• 实际使用中经常使用存储过程对表进行操作；很少使用函数对表进行操作；
• 存储过程可以返回多个值；函数只能有一个返回值；
• 存储过程一般独立的来执行；而函数可以作为其他SQL语句的组成部分来出现。