MySQL存储过程

一、存储过程

1.1 存储过程简介

• 是一组为了完成特定功能的SQL语句集合
• 比传统SQL速度更快、执行效率更高

存储过程的优点

• 执行一次后，会将生成的二进制代码驻留缓冲区，提高执行效率
• SQL语句加上控制语句的集合，灵活性高
• 在服务器端存储，客户端调用时，降低网络负载
• 可多次重复被调用，可随时修改，不影响客户端调用
• 可完成所有的数据库操作，也可控制数据库的信息访问权限

注： 要创建存储过程,必须要具有CREATE ROUTINE权限。

优点解释：

1. 存储过程执行一次后，生成的二进制代码就驻留在缓冲区，之后如果再次调用的话，将直接调用二进制代码，使得存储过程的执行效率和性能得到大幅提升。
2. 存储过程是SQL语句加上控制语句的集合，有很强的灵活性，可以完成复杂的运算。
3. 存储过程存储在服务器端，客户端调用时，直接在服务器端执行，客户端只是传输的调﹑用语句，从而可以降低网络负载。
4. 存储过程被创建后，可以多次重复调用，它将多条SQL封装到了一起，可随时针对SQL.语句进行修改，不影响调用它的客户端。
5. 存储过程可以完成所有的数据库操作，也可以通过编程的方式控制数据库的信息访问权限。

二、创建存储过程

使用CREATE PROCEDURE语句创建存储过程

创建存储过程的语法结构

CREATE PROCEDURE<过程名>([过程参数[....]])<过程体>   #尽量避免与内置的函数或字段重名
[过程参数[....]]格式
[IN|OUT|INOUT ]<参数名><类型>

参数分为：

• 输入参数:IN
• 输出参数:OUT
• 输入/输出参数: INOUT

存储过程的主体部分，被称为过程体

以BEGIN开始,以END结束，若只有一条SQL语句,则可以省略BEGIN-END

以DELIMITER开始和结束

mysql> DELIMITER$$  #$$是用户自定义的结束符
#省略存储过程其他步骤
mysql> DELIMITER ;  #分号前有空格

示例：创建存储过程

修改结束符

mysql> delimiter //

mysql> show databases //
+--------------------+
| Database           |
+--------------------+
| information_schema |
| aaa                |
| auth               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)
//：成为结束符

不带参数的存储过程

mysql> use aaa;
Database changed
mysql> delimiter //
mysql> create procedure c2()
    -> begin
    -> create table t5(name varchar(64),score int(3));
    -> insert into t5 values('zhangsan',70),('lisi',80);
    -> select * from t5;
    -> end //  
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call c2();
+----------+-------+
| name     | score |
+----------+-------+
| zhangsan |    70 |
| lisi     |    80 |
+----------+-------+
2 rows in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

带参数的存储过程

mysql> delimiter $$
mysql> use aaa $$
Database changed
mysql> create procedure getscore(IN a varchar(64))
    -> begin
    -> select * from t5 where name=a;
    -> end $$
Query OK, 0 rows affected (0.00 sec)

mysql> call getscore('zhangsan');
+----------+-------+
| name     | score |
+----------+-------+
| zhangsan |    70 |
+----------+-------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

三、修改存储过程

存储过程的修改分为特征修改和内容修改

特征修改的方法

ALTER PROCEDURE<过程名>[<特征>...]

内容修改可先删除原有存储过程，之后再创建的方法

示例：修改存储过程

删除存储体
mysql> drop procedure c2;
Query OK, 0 rows affected (0.00 sec)

mysql> call c2();
ERROR 1305 (42000): PROCEDURE aaa.c2 does not exist #查询无果，因c2被删除

存储过程in、out、inout参数配置比较

配置流程：

ysql> use tree;  #所有数据库
mysql> set @numa=1,@numb=2,@numc=3;  #设置三个全局变量 

mysql> delimiter $$ #定义存储结束符
mysql> create procedure p(in numa int,out numb int,inout numc int)
#创建存储过程，名称p，参数类型in，名称numa，类型int......
    -> begin  #开始
    -> select numa,numb,numc;
#先查询numa....值，查看是否可以在存储过程中获取全局变量的参数值
    -> set numa=11,numb=12,numc=13;
#在存储体内设置变量参数值
    -> select numa,numb,numc;
#获取变量值
    -> end $$  结束

mysql> delimiter ; #结束符号还原
mysql> call p(@numa,@numb,@numc); #调用存储过程
+------+------+------+
| num1 | num2 | num3 |
+------+------+------+
|    1 | NULL |    3 |
+------+------+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> select @num1,@num2,@num3;
+-------+-------+-------+
| @num1 | @num2 | @num3 |
+-------+-------+-------+
|     1 |    20 |    30 |
+-------+-------+-------+

总结

• in和inout参数会将全局变量的值传入存储过程中，而out参数不会将全局变量的值传入存储过程中。在存储过程使用中，参数值in、out、inout都会发生改变。
• 调用完存储过程后，发现in参数不会对全局变量的值引起变化，而out和inout参数调用完存储过程后，会对全局变量的值产生变化，会将存储过程引用后的值赋值给全局变量。
• in参数赋值类型可以是变量还有定值，而out和inout参数赋值类型必须是变量。