MySQL stored procedure with in and out parameters _ MySQL-mysql tutorial

MySQL stored procedure with the in and out parameters bitsCN.com

MySQL stored procedure with in and out parameters

The simplest example:

[html] 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 ;  

[html] 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)

A slightly complex example:

[html] 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)  

BitsCN.com