一.mysql存储过程语法:
mysql> delimiter // (分割符)
mysql> create procedure 方法名(参数类型参数 参数的数据类型)
-> begin(表示sql语句的开始)
-> select p_in_out as p_01; (查询输入参数)
-> set 参数名 = 参数值(修改参数值)
-> select p_in_out as p_02; (查询修改后的参数值)
-> end;(sql语句的结束)
-> //
mysql> delimiter; (分割符)
参数赋值
mysql> set @设定的参数名=参数值;
查看赋值后的所有结果
mysql> call 方法名(@赋值时的参数名);
查看返回值
mysql> select @赋值时的参数名;
注:存储过程的语法与sql语句一样,不区分大小写
二.参数类型有三类,分别为:in(输入参数);out(输入输出参数);inout(输入输出参数)
in(输入参数):参数值可以在存储过程内部指定,为默认值,在存储过程中修改该参数的值不能被返回
如:
mysql> create procedure test_in(in p_in int)
-> begin
-> select p_in as p_01;
-> set p_in=100;
-> select p_in as p_02;
-> end;
-> //
Query OK, 0 rows affected
mysql> delimiter;
mysql> set @p_in=30;
Query OK, 0 rows affected
mysql> call test_in(@p_in);
+------+
| p_01 |
+------+
| 30 |
+------+
1 row in set
+------+
| p_02 |
+------+
| 100 |
+------+
1 row in set
Query OK, 0 rows affected
mysql> select @p_in as p_03;
+------+
| p_03 |
+------+
| 30 |
+------+
1 row in set
由此可看出:p_in的值在存储过程中的改变不影响最终结果
out(输出参数):该值不能初始化赋值,可在存储过程内部被改变,并可返回;
如:
mysql> delimiter //
mysql> create procedure test_out(outp_out int)
-> begin
-> select p_out as p_01;
-> set p_out=100;
-> select p_out as p_02;
-> end;
-> //
Query OK, 0 rows affected
mysql> delimiter;
mysql> set @p_out=1000;
Query OK, 0 rows affected
mysql> call test_out(@p_out);
+------+
| p_01 |
+------+
| NULL |
+------+
1 row in set
+------+
| p_02 |
+------+
| 100 |
+------+
1 row in set
Query OK, 0 rows affected
mysql> select @p_out as p_03;
+------+
| p_03 |
+------+
| 100 |
+------+
1 row in set
由此可看出:p_out的初始化赋值没有返回,最终返回的结果是存储过程中参数值的修改
inout(输入输出参数):调用时指定,可被改变和返回
如:
mysql> delimiter //
mysql> create procedure test_inout(inoutp_inout int)
-> begin
-> select p_inout as p_01;
-> set p_inout=20;
-> select p_inout as p_02;
-> end;
-> //
Query OK, 0 rows affected
mysql> delimiter;
mysql> set @p=10000;
Query OK, 0 rows affected
mysql> call test_inout(@p);
+-------+
| p_01 |
+-------+
| 10000 |
+-------+
1 row in set
+------+
| p_02 |
+------+
| 20 |
+------+
1 row in set
Query OK, 0 rows affected
mysql> select @p as p_03;
+------+
| p_03 |
+------+
| 20 |
+------+
1 row in set
由此可看出:p_inout的初始化参数赋值和存储过程中的修改参数值均被执行并返回,最终的返回结果是存储过程的修改值