in参数

只能向存储过程传值，在存储过程内对in参数的修改，存储过程外是见不到的（类似C语言函数调用中的值传）。

mysql> show procedure status;
Empty set (0.00 sec)

mysql> delimiter //
mysql> create procedure p1(in arg int)
    -> begin
    -> select arg;
    -> set arg=100;
    -> select arg;
    -> end
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> set @a = 10;
Query OK, 0 rows affected (0.00 sec)

mysql> call p1(@a);
+------+
| arg  |
+------+
|   10 |
+------+
1 row in set (0.00 sec)

+------+
| arg  |
+------+
|  100 |
+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> select @a;
+------+
| @a   |
+------+
|   10 |
+------+
1 row in set (0.00 sec)

out参数

不能向存储过程内部传值，在存储过程内部对out参数的修改，在存储过程之外是可见的。out参数只能是变量，不能是常量。

mysql> delimiter //
mysql> create procedure p2(out arg int) begin select arg; set arg = 100; end//
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> set @x = 3;
Query OK, 0 rows affected (0.00 sec)

mysql> call p2(@x);
+------+
| arg  |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> select @x;
+------+
| @x   |
+------+
|  100 |
+------+
1 row in set (0.00 sec)

inout参数

既可以向存储过程内部传值，同时在存储过程内对变量的修改，在存储过程外也是可见的。inout参数也必须是变量，不能是常量。

mysql> delimiter //
mysql> create procedure p3(inout arg int) begin select arg; set arg = arg * 2; end//
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> set @x = 100;
Query OK, 0 rows affected (0.00 sec)

mysql> call p3(@x);
+------+
| arg  |
+------+
|  100 |
+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> select @x;
+------+
| @x   |
+------+
|  200 |
+------+
1 row in set (0.00 sec)

mysql> drop procedure p1;
Query OK, 0 rows affected (0.00 sec)

mysql> drop procedure p2;
Query OK, 0 rows affected (0.00 sec)

mysql> drop procedure p3;
Query OK, 0 rows affected (0.01 sec