MySQL存储过程带in和out参数

MySQL支持IN（传递给存储过程）、OUT（从存储过程传出）、INOUT（对存储过程传入和传出）

所有MySQL变量必须以@开始！

 

最简单的例子:

[html]
mysql> DELIMITER $$  //<span style="font-family: Tahoma, Helvetica, Arial, 宋体, sans-serif; font-size: 14px; line-height: 25.2000007629395px; background-color: rgb(247, 252, 255);"><span style="line-height: 25.2000007629395px; color: rgb(255, 0, 0);">用delimiter命令来把语句定界符从 ;变为//。</span></span><span style="font-family: Tahoma, Helvetica, Arial, 宋体, sans-serif; font-size: 14px; line-height: 25.2000007629395px; background-color: rgb(247, 252, 255);">这样就允许在程序体用;定界符传递到服务器，而不是被mysql自己来解释。</span>
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)</p><p>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)  

?