mysql存储过程参数输入输出IN/OUT/INOUT

mysql存储过程中，IN用来输入参数，OUT用来输出参数，INOUT输入和输出。

1. IN参数
   创建存储过程

mysql> delimiter //
mysql> create procedure proce1(IN count int)
    -> begin
    -> select count;
    -> set count = 2;
    -> select count;
    -> end
    -> //

执行存储过程

mysql> set @count = 1//
Query OK, 0 rows affected (0.00 sec)

mysql> call proce1(@count)//
+-------+
| count |
+-------+
|     1 |
+-------+
1 row in set (0.00 sec)

+-------+
| count |
+-------+
|     2 |
+-------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> select @count
    -> //
+--------+
| @count |
+--------+
|      1 |
+--------+
1 row in set (0.00 sec)

mysql> 

在上面存储过程中，我们定义了一个参数@count，赋值为1，传入到存储过程中，在存储过程中赋值为2，当存储过程结束后，打印@count的值依旧是1.

2. OUT参数
   创建存储过程

mysql> create procedure proce_out(OUT count int)
    -> begin
    -> select count;
    -> set count = 2;
    -> select count;
    -> end//
Query OK, 0 rows affected (0.05 sec)

执行存储过程

mysql> set @count=1;
    -> call proce_out(@count);
    -> select @count;
    -> //
Query OK, 0 rows affected (0.00 sec)

+-------+
| count |
+-------+
|  NULL |
+-------+
1 row in set (0.00 sec)

+-------+
| count |
+-------+
|     2 |
+-------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

+--------+
| @count |
+--------+
|      2 |
+--------+
1 row in set (0.00 sec)

mysql> 

@count在外面赋值为1，因为参数是OUT类型，所以存储过程中打印出来为null，存储过程中赋值为2，存储过程结束，查看@count的值，依旧为2。

3. INOUT 输入输出参数
   创建存储过程

mysql> create procedure proce_inout(INOUT count int)
    -> begin
    -> select count;
    -> set count = 2;
    -> select count;
    -> end;
    -> //

执行存储过程

mysql> set @count = 1;
    -> call proce_inout(@count);
    -> select @count;
    -> //
Query OK, 0 rows affected (0.00 sec)

+-------+
| count |
+-------+
|     1 |
+-------+
1 row in set (0.00 sec)

+-------+
| count |
+-------+
|     2 |
+-------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

+--------+
| @count |
+--------+
|      2 |
+--------+
1 row in set (0.00 sec)

我们在外面把@count赋值为1，存储过程里面获取到了值，然后赋值为2，存储过程结束后，打印出来的值依旧是2

实时内容请关注微信公众号，公众号与博客同时更新：程序员星星