实例一：无参的存储过程 复制代码 代码如下: $conn = mysql_connect('localhost','root','root') or die ("数据连接错误!!!"); mysql_select_db('test',$conn); $sql = " create procedure myproce() begin INSERT INTO user (id, username, sex) VALUES (NUL
MySQL支持IN(传递给存储过程)、OUT(从存储过程传出)、INOUT(对存储过程传入和传出) 所有MySQL变量必须以@开始！ 最简单的例子:[html]
mysql> DELIMITER $$ //用delimiter命令来把语句定界符从 ;变为//。这样就允许在程序体用;定界符传递到服务器，而不是被mysql自己来解释。
mysql> USE test $$
Databasechanged
mysql>DROPPROCEDUREIF EXISTS `sp_add`$$
Query OK, 0 rowsaffected (0.00 sec)
mysql>CREATEPROCEDUREsp_add(a INT, b INT,OUTcINT)
->BEGIN
->SETc=a+ b;
->END$$
Query OK, 0 rowsaffected (0.00 sec)
mysql> DELIMITER ;
mysql> CALL sp_add (1,2,@c);
Query OK, 0 rowsaffected (0.00 sec)
mysql> SELECT@c;
+------+
| @c |
+------+
| 3 |
+------+
1 row inset(0.00 sec)
一个稍微复杂的例子:
mysql> show createtablet_BillNo;
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|Table |CreateTable |
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t_BillNo | CREATETABLE`t_billno` (
`SaleNo`bigint(20)DEFAULTNULL,
`bmh`varchar(20)DEFAULTNULL
) ENGINE=InnoDB DEFAULTCHARSET=utf8 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC|
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row inset(0.00 sec)
mysql>select*fromt_BillNo;
+--------+------+
| SaleNo | bmh |
+--------+------+
| 1 | 2 |
| 4 | 3 |
| 4 | 5 |
| 7 | 7 |
| 12 | 8 |
+--------+------+
5rowsinset (0.00 sec)
mysql>
mysql> DELIMITER $$
mysql> USE test $$
Databasechanged
mysql>DROPPROCEDUREIF EXISTS `sp_GetMaxNumber`$$
Query OK, 0 rowsaffected (0.01 sec)
DELIMITER $$
USE test $$
DROPPROCEDUREIF EXISTS `sp_GetMaxNumber`$$
CREATEPROCEDUREsp_GetMaxNumber (INv_bmhVARCHAR(6),OUTv_MaxNoINT)
BEGIN
STARTTRANSACTION;
UPDATEt_BillNo
SETSaleNo = IFNULL(SaleNo,0)+1
WHEREbmh = v_bmh;
IF @@error_count = 0 THEN
BEGIN
SELECTSalenoINTOv_MaxNoFROMt_BillNoWHEREbmh = v_bmh;
COMMIT;
END;
ELSE
BEGIN
ROLLBACK;
SETv_MaxNo = 0;
END;
ENDIF;
END$$
DELIMITER ;
mysql>CREATEPROCEDUREsp_GetMaxNumber (INv_bmhVARCHAR(6),OUTv_MaxNoINT)
->BEGIN
-> START TRANSACTION;
->UPDATEt_BillNo
->SETSaleNo = IFNULL(SaleNo,0)+1
->WHEREbmh = v_bmh;
-> IF @@error_count = 0 THEN
->BEGIN
-> SELECTSalenoINTOv_MaxNoFROMt_BillNoWHEREbmh = v_bmh;
->COMMIT;
->END;
->ELSE
->BEGIN
-> ROLLBACK;
-> SETv_MaxNo = 0;
->END;
->ENDIF;
->END$$
Query OK, 0 rowsaffected (0.00 sec)
mysql> DELIMITER ;
mysql>
mysql> call sp_GetMaxNumber(8,@v_MaxNo);
Query OK, 0 rowsaffected (0.00 sec)
mysql>select@v_MaxNo;
+----------+
| @v_MaxNo |
+----------+
| 12 |
+----------+
1 row inset(0.00 sec)
?
