存储过程
简介
是一组为了完成特定功能的SQL语句集合
比传统SQL速度更快、执行效率更高
存储过程的优点
- 执行一次后,会将生成的二进制代码驻留缓冲区,提高执行效率
- SQL语句加上控制语句的集合,灵活性高
- 在服务器端存储,客户端调用时,降低网络负载
- 可多次重复被调用,可随时修改,不影响客户端调用
- 可完成所有的数据库操作,也可控制数据库的信息访问权限
注:要创建存储过程,必须要具有CREATE ROUTINE权限。
存储过程的优点
- 存储过程执行一次后,生成的二进制代码就驻留在缓冲区,之后如果再次调用的话,将直接调用二进制代码,使得存储过程的执行效率和性能得到大幅提升。
- 存储过程是SQL语句加上控制语句的集合,有很强的灵活性,可以完成复杂的运算。
- 存储过程存储在服务器端,客户端调用时,直接在服务器端执行,客户端只是传输的调﹑用语句,从而可以降低网络负载。
- 存储过程被创建后,可以多次重复调用,它将多条SQL封装到了一起,可随时针对SQL.语句进行修改,不影响调用它的客户端。
- 存储过程可以完成所有的数据库操作,也可以通过编程的方式控制数据库的信息访问权限。
创建存储过程
使用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区别
mysql> use aaa;
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.00 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)
总结
- in和inout参数会将全局变量的值传入存储过程中,而out参数不会将全局变量的值传入存储过程中。在存储过程使用中,参数值in、out、inout都会发生改变。
- 调用完存储过程后,发现in参数不会对全局变量的值引起变化,而out和inout参数调用完存储过程后,会对全局变量的值产生变化,会将存储过程引用后的值赋值给全局变量。
- in参数赋值类型可以是变量还有定值,而out和inout参数赋值类型必须是变量。