MySQL存储过程的深入讲解（in、out、inout）

吾爱主题阅读：162 2024-04-01 23:50:59 评论：0

一、简介

从 5.0 版本才开始支持，是一组为了完成特定功能的SQL语句集合（封装），比传统SQL速度更快、执行效率更高。

存储过程的优点

1、执行一次后，会将生成的二进制代码驻留缓冲区（便于下次执行），提高执行效率

2、SQL语句加上控制语句的集合，灵活性高

3、在服务器端存储，客户端调用时，降低网络负载

4、可多次重复被调用，可随时修改，不影响客户端调用

5、可完成所有的数据库操作，也可控制数据库的信息访问权限

为什么要用存储过程？

1.减轻网络负载；2.增加安全性

二、创建存储过程

2.1 创建基本过程

使用create procedure语句创建存储过程

存储过程的主体部分，被称为过程体；以begin开始，以end$$结束

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 #声明语句结束符，可以自定义: delimiter $$ #声明存储过程 create procedure 存储过程名（ in 参数名参数类型） begin #定义变量 declare 变量名变量类型 #变量赋值 set 变量名 = 值 sql 语句1； sql 语句2； ... end $$ #恢复为原来的语句结束符 delimiter ；（有空格）

实例：

1 2 3 4 5 6 7 mysql> delimiter $$ mysql> create procedure text() -> begin -> select * from stu.a_player; -> end $$ Query OK, 0 rows affected (0.00 sec) mysql> delimiter ;

调用存储过程

1 2 3 4 5 6 7 8 9 10 11 12 13 call 存储过程名(实际参数); mysql> call text; + ----+----------+-------+ | id | name | score | + ----+----------+-------+ | 1 | zhangsan | 88 | | 2 | lisi | 89 | | 3 | wangwu | 67 | | 4 | zhaoliu | 90 | | 5 | xuli | 80 | | 6 | keke | 75 | + ----+----------+-------+ 6 rows in set (0.00 sec)

删除存储过程

1	`mysql>` `drop` `procedure` `text;`

2.2 存储过程的参数

MySQL存储过程的参数用在存储过程的定义，共有三种参数类型,IN,OUT,INOUT,形式如：

CREATEPROCEDURE 存储过程名([[IN |OUT |INOUT ] 参数名数据类形...])

IN 输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）

OUT 输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）

INOUT 输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）

传递参数实例：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 mysql> create procedure test1( 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 ; #将4传递给in_id变量，执行事务 mysql> call test1(4); + ----+---------+-------+ | id | name | score | + ----+---------+-------+ | 4 | zhaoliu | 90 | + ----+---------+-------+ 1 row in set (0.00 sec) #将6传递给in_id变量，执行事务 mysql> call test1(6); + ----+------+-------+ | id | name | score | + ----+------+-------+ | 6 | keke | 75 | + ----+------+-------+ 1 row in set (0.00 sec)

OUT

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 mysql> delimiter $$ mysql> create procedure test2( out aa int ) -> begin -> select aa; -> set aa=2; -> select aa; -> end $$ Query OK, 0 rows affected (0.00 sec) mysql> delimiter ; #将@aa变量传递给test2 事务 mysql> call test2(@aa); + ------+ | aa | + ------+ | NULL | + ------+ # out 向调用者输出参数，不接收输入的参数，所以aa为 null 1 row in set (0.00 sec) + ------+ | aa | + ------+ | 2 | + ------+ 事务将aa变量设置为2（设置的是全局），则可进行输出 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> select @aa; + ------+ | @aa | + ------+ | 2 | + ------+ 1 row in set (0.00 sec) #事务外查询变量，已经被修改

IN 、OUT、 INOUT 对比

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 mysql> delimiter // mysql> create procedure test3( 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 test3(@num1,@num2,@num3); + ------+------+------+ | num1 | num2 | num3 | + ------+------+------+ | 1 | NULL | 3 | + ------+------+------+ 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都会发生改变。

1 2 3 4 5 6 7 mysql> select @num1,@num2,@num3; + -------+-------+-------+ | @num1 | @num2 | @num3 | + -------+-------+-------+ | 1 | 20 | 30 | + -------+-------+-------+ 1 row in set (0.00 sec)