最简单的例子:
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 ;
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)mysql>
一个稍微复杂的例子:
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)
如何在php中调用mysql的存储过程?本人没有测试过,从朋友那里借鉴过实例,如下:
$sql = "call test.sp_getmaxnumber(8,@c);";
mysql_query($sql);//调用sp_getmaxnumber的存储过程
$result = mysql_query('select @c;');
$array = mysql_fetch_array($result);
echo '<pre>';print_r($array);
但是在pb中调用,报错如下(来自itpub的网友的例子),有类似经历的朋友请分享下经验啊:
在pb w_main窗体的cb_1.click事件中：
string ls_bmh, ls_errtext
int li_maxno
ls_bmh = '0901'
sp_getmaxnumber(ls_bmh, li_maxno)
ls_errtext = sqca.sqlerrtext
messagebox('error',string(li_maxno)+' ' +sqlca.sqlerrtext)
但pb显示显示错误：
0 sqlstate = 37000
[mysql] [odbc 5.1 driver]you have an error in your sql syntax;check the manual that corresponds to your mysql server version for the right syntax to use
near '?=call sp_getmaxnumber(0,_gbk'0901') at line 1.
以上就是mysql 存储过程带in和out参数以及php,pb如何调用的小例子的内容。