mysql存储过程带in和out参数
最简单的例子:
[html] mysql> delimiter $$ mysql> use test $$ database changed mysql> drop procedure if exists `sp_add`$$ query ok, 0 rows affected (0.00 sec) mysql> create procedure sp_add(a int, b int,out c int) -> begin -> set c=a+ b; -> end$$ query ok, 0 rows affected (0.00 sec) mysql> delimiter ;
[html] mysql> call sp_add (1,2,@c); query ok, 0 rows affected (0.00 sec)
mysql> select @c; +------+ | @c | +------+ | 3 | +------+ 1 row in set (0.00 sec)
一个稍微复杂的例子:
[html] mysql> show create table t_billno; +----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | table | create table | +----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | t_billno | create table `t_billno` ( `saleno` bigint(20) default null, `bmh` varchar(20) default null ) engine=innodb default charset=utf8 checksum=1 delay_key_write=1 row_format=dynamic | +----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> select * from t_billno; +--------+------+ | saleno | bmh | +--------+------+ | 1 | 2 | | 4 | 3 | | 4 | 5 | | 7 | 7 | | 12 | 8 | +--------+------+ 5 rows in set (0.00 sec) mysql> mysql> delimiter $$ mysql> use test $$ database changed mysql> drop procedure if exists `sp_getmaxnumber`$$ query ok, 0 rows affected (0.01 sec) delimiter $$ use test $$ drop procedure if exists `sp_getmaxnumber`$$ create procedure sp_getmaxnumber (in v_bmh varchar(6), out v_maxno int) begin start transaction; update t_billno set saleno = ifnull(saleno,0)+1 where bmh = v_bmh; if @@error_count = 0 then begin select saleno into v_maxno from t_billno where bmh = v_bmh; commit; end; else begin rollback; set v_maxno = 0; end; end if; end$$ delimiter ; mysql> create procedure sp_getmaxnumber (in v_bmh varchar(6), out v_maxno int) -> begin -> start transaction; -> update t_billno -> set saleno = ifnull(saleno,0)+1 -> where bmh = v_bmh; -> if @@error_count = 0 then -> begin -> select saleno into v_maxno from t_billno where bmh = v_bmh; -> commit; -> end; -> else -> begin -> rollback; -> set v_maxno = 0; -> end; -> end if; -> end$$ query ok, 0 rows affected (0.00 sec) mysql> delimiter ; mysql> mysql> call sp_getmaxnumber(8,@v_maxno); query ok, 0 rows affected (0.00 sec) mysql> select @v_maxno; +----------+ | @v_maxno | +----------+ | 12 | +----------+ 1 row in set (0.00 sec)
bitscn.com