一、存储过程简介
我们常用的操作数据库语言SQL语句在执行的时候需要要先编译,然后执行,而存储过程(Stored Procedure)是一组为了完成特定功能的SQL语句集,经编译后存储在数据库中,用户通过指定存储过程的名字并给定参数(如果该存储过程带有参数)来调用执行它。一个存储过程是一个可编程的函数,它在数据库中创建并保存。它可以有SQL语句和一些特殊的控制结构组成。当希望在不同的应用程序或平台上执行相同的函数,或者封装特定功能时,存储过程是非常有用的。数据库中的存储过程可以看做是对编程中面向对象方法的模拟,它允许控制数据的访问方式
二、存储过程的优点
1、执行一次后,会将生成的二进制代码驻留缓冲区(便于下次执行),提高执行效率
2、SQL语句加上控制语句的集合,灵活性高
3、在服务器端存储,客户端调用时,降低网络负载
4、可多次重复被调用,可随时修改,不影响客户端调用
5、 可完成所有的数据库操作,也可控制数据库的信息访问权限
三、创建存储过程
3.1.1、创建基本过程
使用create procedure语句创建存储过程 。
存储过程的主体部分,被称为过程体;以begin开始,以end$$结束
#声明语句结束符,可以自定义:
delimiter $$
#声明存储过程
create procedure 存储过程名(in 参数名 参数类型)
begin
#定义变量
declare 变量名 变量类型
#变量赋值
set 变量名 = 值
sql 语句1;
sql 语句2;
...
end$$
#恢复为原来的语句结束符
delimiter ;(有空格)
调用存储过程:
删除存储过程;
3.1.2、存储过程的参数
MySQL存储过程的参数用在存储过程的定义,共有三种参数类型,IN,OUT,INOUT,形式如:
CREATEPROCEDURE 存储过程名([[IN |OUT |INOUT ] 参数名 数据类形…])
IN 输入参数:表示调用者向过程传入值(传入值可以是字面量或变量)
mysql> delimiter $$
mysql> create procedure tet1(in in_id int(2))
-> begin
-> select * from stu.a_player where id=in_id;
-> end $$
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
#将3传递给in_id变量,执行事务
mysql> call tet1(3);
+----+---------+-------+
| id | name | score |
+----+---------+-------+
| 3 | zl | 80 |
+----+---------+-------+
1 row in set (0.00 sec)
#将6传递给in_id变量,执行事务
mysql> call tet1(6);
+----+------+-------+
| id | name | score |
+----+------+-------+
| 6 | ke | 60 |
+----+------+-------+
1 row in set (0.00 sec)
OUT 输出参数:表示过程向调用者传出值(可以返回多个值)(传出值只能是变量)
mysql> delimiter $$
mysql> create procedure tet2(out ab int)
-> begin
-> select ab;
-> set ab=2;
-> select ab;
-> end $$
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
#将@ab变量传递给tet2 事务
mysql> call tet2(@ab);
+------+
| ab |
+------+
| NULL |
+------+
#out向调用者输出参数,不接收输入的参数,所以ab为null
1 row in set (0.00 sec)
+------+
| ab |
+------+
| 2 |
+------+
事务将ab变量设置为2(设置的是全局),则可进行输出
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> select @ab;
+------+
| @ab |
+------+
| 2 |
+------+
1 row in set (0.00 sec)
#事务外查询变量,已经被修改
INOUT 输入输出参数:既表示调用者向过程传入值,又表示过程向调用者传出值(值只能是变量)