一.简介
1.从 5.0 版本才开始支持,是一组为了完成特定功能的SQL语句集合(封装),比传统SQL速度更快、执行效率更高。
2.存储过程的优点:
- 执行一次后,会将生成的二进制代码驻留缓冲区(便于下次执行),提高执行效率
- SQL语句加上控制语句的集合,灵活性高
- 在服务器端存储,客户端调用时,降低网络负载
- 可多次重复被调用,可随时修改,不影响客户端调用
- 可完成所有的数据库操作,也可控制数据库的信息访问权限
3.为什么要用存储过程?
- 减.轻网络负载
- .增加安全性
二.创建存储过程
使用CREATE PROCEDURE语句创建存储过程
- 输入参数:IN
- 输出参数:OUT
- 输入/输出参数:INOUT
- 存储过程的主体部分,被称为过程体
- 以BEGIN开始,以END结束,若只有一条SQL语句
- 以DELIMITER开始和结束
创建存储过程
mysql> delimiter $$
mysql> create procedure orange()
-> begin
-> select * from vip;
-> end $$
Query OK, 0 rows affected (0.00 sec)
调用存储过程
mysql> delimiter ;
mysql> call orange;
+------+----------+--------+------+
| id | name | number | age |
+------+----------+--------+------+
| 1 | ziyang | 10 | 24 |
| 2 | xiner | 12 | 17 |
| 3 | weilai | 12 | 24 |
| 4 | keai | 17 | 20 |
| 5 | yueliang | 15 | 21 |
| 6 | meimei | 15 | 24 |
| 7 | menxin | 15 | 21 |
+------+----------+--------+------+
查询存储过程
mysql> show procedure status where db='moon';
+------+--------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db | Name | Type | Definer | Modified | Created | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+------+--------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| moon | blue | PROCEDURE | root@localhost | 2020-11-05 17:27:24 | 2020-11-05 17:27:24 | DEFINER | | utf8 | utf8_general_ci | utf8_general_ci |
| moon | orange | PROCEDURE | root@localhost | 2020-11-05 17:31:22 | 2020-11-05 17:31:22 | DEFINER | | utf8 | utf8_general_ci | utf8_general_ci |
+------+--------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
删除存储过程
mysql> drop procedure orange;
Query OK, 0 rows affected (0.00 sec)
mysql> show procedure status where db='moon';
+------+------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db | Name | Type | Definer | Modified | Created | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+------+------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| moon | blue | PROCEDURE | root@localhost | 2020-11-05 17:27:24 | 2020-11-05 17:27:24 | DEFINER | | utf8 | utf8_general_ci | utf8_general_ci |
+------+------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
三.存储过程参数传递
MySQL存储过程的参数用在存储过程的定义,共有三种参数类型,IN,OUT,INOUT,形式如:
IN 输入参数:表示调用者向过程传入值(传入值可以是字面量或变量)
OUT 输出参数:表示过程向调用者传出值(可以返回多个值)(传出值只能是变量)
INOUT 输入输出参数:既表示调用者向过程传入值,又表示过程向调用者传出值(值只能是变量)
IN
mysql> delimiter $$
mysql> create procedure edg(in inname varchar(10))
-> begin
-> select * from vip where name=inname;
-> end $$
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> call edg('yueliang');
+------+----------+--------+------+
| id | name | number | age |
+------+----------+--------+------+
| 5 | yueliang | 15 | 21 |
+------+----------+--------+------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
OUT
mysql> delimiter $$
mysql> create procedure jkl(out nt int) begin select nt; set nt=10; select nt; end$$
Query OK, 0 rows affected (0.01 sec)
mysql> delimiter ;
mysql> call jkl(@nt);
+------+
| nt |
+------+
| NULL |
+------+
1 row in set (0.00 sec)
+------+
| nt |
+------+
| 10 |
+------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter $$
mysql> create procedure zz(in num1 int,out num2 int,inout num3 int)
-> begin
-> select num1,num2,num3;
-> set num1=10,num2=20,num3=30;
-> select num1,num2,num3;
-> end $$
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> call zz(@num1,@num2,@num3);
+------+------+------+
| num1 | num2 | num3 |
+------+------+------+
| NULL | NULL | NULL |
+------+------+------+
1 row in set (0.00 sec)
+------+------+------+
| num1 | num2 | num3 |
+------+------+------+
| 10 | 20 | 30 |
+------+------+------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
- in和inout参数会将全局变量的值传入存储过程中,而out参数不会将全局变量的值传入存储过程中。在存储过程使用中,参数值in,out,inout都会发生改变
- 调用完存储过程后,发现in参数不会对全局变量的值引起变化,而out和inout参数调用完存储过程后,会对全局变量的值产生变化,会将存储过程引用后的值赋值给全局变量,in参数赋值类型可以是变量还有定值,而out和inout参数赋值类型必须是变量