mysql 存储过程调用实例

来源：互联网发布：淘宝好评返现是真的吗编辑：程序博客网时间：2024/06/13 03:01

MySQL存储过程带in和out参数

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)</p><p>mysql> SELECT @c;  +------+  | @c   |  +------+  |    3 |  +------+  1 row in set (0.00 sec)

一个稍微复杂的例子:

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)

0 0