mysql 存储过程调用实例

╰+攻爆jí腚メ 2021-06-24 14:00 482阅读 0赞

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)