最简单的例子:
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的存储过程?本人没有测试过,从朋友那里借鉴过实例,如下:
立即学习“PHP免费学习笔记（深入）”；
$sql = "call test.sp_GetMaxNumber(8,@c);"; mysql_query($sql);//调用sp_GetMaxNumber的存储过程 $result = mysql_query('select @c;'); $array = mysql_fetch_array($result); echo '<pre class="brush:php;toolbar:false">';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如何调用的小例子的内容，更多相关内容请关注PHP中文网（www.php.cn）！
© 版权声明
文章版权归作者所有，未经允许请勿转载。
THE END
