c mysql存储过程 out_MySQL存储过程带in和out参数

实例一：无参的存储过程 复制代码 代码如下: $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)

?