mysql存储过程参数类型

存储过程可以理解成程序中封装的代码块，并且可以相互调用

由于下面的例子会使用变量，那么先了解一下变量。

变量分为用户变量和系统变量

用户变量变量名以@开头

创建用户变量

mysql> select 'hello' into @a;
Query OK, 1 row affected (0.00 sec)

mysql> set @b=123;
Query OK, 0 rows affected (0.00 sec)

具有IN、OUT、INOUT三种参数类型

IN输入参数，该参数必须在调用存储过程前指定，并且在过程中修改的值不可返回

mysql> create procedure pro_int(IN a int)
    -> begin
    -> select a;
    -> set a = 111;
    -> select a;
    -> end;
    -> $$
Query OK, 0 rows affected (0.00 sec)

mysql> call pro_int(@b);
    -> $$
+------+
| a    |
+------+
|  123 |
+------+
1 row in set (0.00 sec)

+------+
| a    |
+------+
|  111 |
+------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.02 sec)

mysql> select @b;
    -> $$
+------+
| @b   |
+------+
|  123 |
+------+
1 row in set (0.00 sec)

OUT输出参数，值可在内部修改和返回

mysql> create procedure pro_out(OUT a int)
    -> begin
    -> select a;
    -> set a = 111;
    -> select a;
    -> end;
    -> $$
Query OK, 0 rows affected (0.00 sec)

mysql> call pro_out(@b);
    -> $$
+------+
| a    |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

+------+
| a    |
+------+
|  111 |
+------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> select @b $$
+------+
| @b   |
+------+
|  111 |
+------+
1 row in set (0.00 sec)

INOUT 调用时指定，可修改和返回

mysql> create procedure pro_inout(INOUT a int)
    -> begin
    -> select a;
    -> set a = 111;
    -> select a;
    -> end;
    -> $$
Query OK, 0 rows affected (0.00 sec)
mysql> set @b=222;
    -> $$
Query OK, 0 rows affected (0.00 sec)

mysql> call pro_inout(@b);
    -> $$
+------+
| a    |
+------+
|  222 |
+------+
1 row in set (0.00 sec)

+------+
| a    |
+------+
|  111 |
+------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.02 sec)
mysql> select @b $$
+------+
| @b   |
+------+
|  111 |
+------+
1 row in set (0.00 sec)