最簡單的例子:
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>
一個稍微複雜的例子:
如何在php中呼叫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)
但是在PB中調用,報錯如下(來自itpub的網友的例子),有類似經歷的朋友請分享下經驗啊:
$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顯示顯示錯誤: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)