Mysql存储过程不会返回ID

问题描述：

我想执行事务性插入，但我不明白问题，因为我无法看到错误。我仔细阅读了执行事务过程的mysql指令。
问题是没有返回标题ID，我该如何缓解这个问题？Mysql存储过程不会返回ID

CREATE PROCEDURE `add_payment`(IN `transaction_no` VARCHAR(50), 
    IN `transaction_type_id` MEDIUMINT(8) UNSIGNED, 
    IN `distributor_details_id` MEDIUMINT(8) UNSIGNED, 
    IN `customer_id` INT(11) UNSIGNED, 
    IN `amount` DECIMAL(18,8), 
    IN `salesman_id` INT(11) UNSIGNED, 
    IN `created_datetime` DATETIME, 
    OUT `payment_header_id` INT(11) UNSIGNED) 
BEGIN 

    DECLARE transaction_code_id INTEGER(11) UNSIGNED DEFAULT 0; 
    DECLARE transaction_x_payment_header_id INTEGER(11) UNSIGNED DEFAULT 0; 
    DECLARE payment_details_id INTEGER(11) UNSIGNED DEFAULT 0; 

    DECLARE user_id MEDIUMINT(8) UNSIGNED DEFAULT 0; 


# 
    DECLARE EXIT HANDLER FOR SQLEXCEPTION 
    BEGIN 
     SET payment_header_id = 0; 
     ROLLBACK ; 
    END; 

    START TRANSACTION; 
    # GET USER TO HAVE A REFERENCE WHOS DOING THIS 
    SET user_id = (SELECT user_id FROM salesman_x_user WHERE salesman_id = salesman_id); 

    # INSERT PAYMENT HEADER FIRST TO HAVE PAYMENT HEADER ID 
    INSERT INTO `payment_header` (`no` , `created_datetime`) VALUES(transaction_no , created_datetime); 

    SET payment_header_id = (SELECT LAST_INSERT_ID()); 
    COMMIT; 


END;

答

避免将变量和参数命名为表格的列。

参见下面的适于例如：

mysql> DROP TABLE IF EXISTS `payment_header`; 
Query OK, 0 rows affected (0.00 sec) 

mysql> CREATE TABLE IF NOT EXISTS `payment_header`(
    -> `payment_header_id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, 
    -> `no` VARCHAR(50), 
    -> `created_datetime` DATETIME, 
    -> UNIQUE KEY (`no`) 
    ->); 
Query OK, 0 rows affected (0.00 sec) 

mysql> DELIMITER // 

mysql> DROP PROCEDURE IF EXISTS `add_payment`// 
Query OK, 0 rows affected (0.00 sec) 

mysql> CREATE PROCEDURE `add_payment` (
    -> IN `_transaction_no` VARCHAR(50), 
    -> IN `_transaction_type_id` MEDIUMINT(8) UNSIGNED, 
    -> IN `_distributor_details_id` MEDIUMINT(8) UNSIGNED, 
    -> IN `_customer_id` INT(11) UNSIGNED, 
    -> IN `_amount` DECIMAL(18, 8), 
    -> IN `_salesman_id` INT(11) UNSIGNED, 
    -> IN `_created_datetime` DATETIME, 
    -> OUT `_payment_header_id` INT(11) UNSIGNED 
    ->) 
    -> BEGIN 
    -> DECLARE `transaction_code_id` INTEGER(11) UNSIGNED DEFAULT 0; 
    -> DECLARE `transaction_x_payment_header_id` INTEGER(11) UNSIGNED DEFAULT 0; 
    -> DECLARE `payment_details_id` INTEGER(11) UNSIGNED DEFAULT 0; 
    -> DECLARE `user_id` MEDIUMINT(8) UNSIGNED DEFAULT 0; 
    -> 
    -> DECLARE EXIT HANDLER FOR SQLEXCEPTION 
    ->  BEGIN 
    ->  SET `_payment_header_id` = 0; 
    ->  ROLLBACK; 
    ->  END; 
    -> 
    -> START TRANSACTION; 
    -> # GET USER TO HAVE A REFERENCE WHOS DOING THIS 
    -> /*SET `user_id` = (SELECT `user_id` 
    /*>      FROM `salesman_x_user` 
    /*>      WHERE `salesman_id` = `_salesman_id`);*/ 
    -> 
    -> # INSERT PAYMENT HEADER FIRST TO HAVE PAYMENT HEADER ID 
    -> INSERT INTO `payment_header` (`no` , `created_datetime`) 
    -> VALUES (`_transaction_no`, `_created_datetime`); 
    -> 
    -> SET `_payment_header_id` = LAST_INSERT_ID(); 
    -> COMMIT; 
    -> END// 
Query OK, 0 rows affected (0.00 sec) 

mysql> DELIMITER ; 

mysql> CALL `add_payment`(
    -> '00000000000000000000000000000000000000000000000001', 
    -> NULL, 
    -> NULL, 
    -> NULL, 
    -> NULL, 
    -> NULL, 
    -> NOW(), 
    -> @`payment_header_id` 
    ->); 
Query OK, 0 rows affected (0.00 sec) 

mysql> SELECT @`payment_header_id`; 
+----------------------+ 
| @`payment_header_id` | 
+----------------------+ 
|     1 | 
+----------------------+ 
1 row in set (0.00 sec) 

mysql> SELECT 
    -> `payment_header_id`, 
    ->  `no`, 
    ->  `created_datetime` 
    -> FROM 
    -> `payment_header`; 
+-------------------+----------------------------------------------------+---------------------+ 
| payment_header_id | no             | created_datetime | 
+-------------------+----------------------------------------------------+---------------------+ 
|     1 | 00000000000000000000000000000000000000000000000001 | 2000-01-01 00:00:01 | 
+-------------------+----------------------------------------------------+---------------------+ 
1 row in set (0.00 sec) 

mysql> CALL `add_payment`(
    -> '00000000000000000000000000000000000000000000000001', 
    -> NULL, 
    -> NULL, 
    -> NULL, 
    -> NULL, 
    -> NULL, 
    -> NOW(), 
    -> @`payment_header_id` 
    ->); 
Query OK, 0 rows affected (0.00 sec) 

mysql> SELECT @`payment_header_id`; 
+----------------------+ 
| @`payment_header_id` | 
+----------------------+ 
|     0 | 
+----------------------+ 
1 row in set (0.00 sec) 

mysql> SELECT 
    -> `payment_header_id`, 
    ->  `no`, 
    ->  `created_datetime` 
    -> FROM 
    -> `payment_header`; 
+-------------------+----------------------------------------------------+---------------------+ 
| payment_header_id | no             | created_datetime | 
+-------------------+----------------------------------------------------+---------------------+ 
|     1 | 00000000000000000000000000000000000000000000000001 | 2000-01-01 00:00:01 | 
+-------------------+----------------------------------------------------+---------------------+ 
1 row in set (0.00 sec)

db-fiddle见。

Mysql存储过程不会返回ID

相关推荐