mysql存储过程中,IN
用来输入参数,OUT
用来输出参数,INOUT
输入和输出。
- 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.
- 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。
- 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
实时内容请关注微信公众号,公众号与博客同时更新:程序员星星