存储过程可以理解成程序中封装的代码块,并且可以相互调用
由于下面的例子会使用变量,那么先了解一下变量。
变量分为用户变量和系统变量
用户变量变量名以@开头
创建用户变量
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)