MySQL存储过程

MySQL存储

简介

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

存储过程的优点

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

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

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

创建存储过程

创建存储过程的语法结构

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

存储过程的主体部分，被称为过程体
以BEGIN开始,以END结束，若只有一条SQL语句,则可以省略BEGIN-END
以DELIMITER开始和结束

不带参数存储过程

mysql> use cj;
Database changed
mysql> delimiter //
mysql> create procedure z1()
    -> begin
    -> create table x2(name varchar(64),score int(3));
    -> insert into x2 values('aa',11),('bb',55);
    -> select * from x2;
    -> end //
Query OK, 0 rows affected (0.02 sec)

mysql> delimiter ;
mysql> call z1();
+------+-------+
| name | score |
+------+-------+
| aa   |    11 |
| bb   |    55 |
+------+-------+
2 rows in set (0.04 sec)

Query OK, 0 rows affected (0.04 sec)

带参数的存储过程

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

mysql> delimiter ;
mysql> call getscore('aa');
+------+-------+
| name | score |
+------+-------+
| aa   |    11 |
+------+-------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

修改存储过程

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

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

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

过程

删除存储体

mysql> drop procedure z1;
Query OK, 0 rows affected (0.01 sec)

mysql> call z1();
ERROR 1305 (42000): PROCEDURE cj.z1 does not exist

in、out、inout参数配置区别

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

mysql> use cj;
Database changed
mysql> set @num1=1,@num2=2,@num3=3;
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter $$
mysql> create procedure p(in num1 int,out num2 int,inout num3 int)
    -> begin
    -> select num1,num2,num3;
    -> set num1=10,num2=20,num3=30;
    -> end $$
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;
mysql> call p(@num1,@num2,@num3);
+------+------+------+
| 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 |
+-------+-------+-------+
1 row in set (0.00 sec)