mysql - 如何避免存储过程中的递归调用？

Question

我正在使用 MySql 存储过程对我的数据库进行操作。这是示例存储过程。

CREATE DEFINER=`ntadmin`@`%` PROCEDURE `usp_user`(
    IN  cMode       VARCHAR(20),
    IN  nUserID     MEDIUMINT UNSIGNED,
    IN  cEmail      VARCHAR(50),
    IN  cFirstName  VARCHAR(20),
    IN  cLastName   VARCHAR(20),
    IN  nIsMale     TINYINT(1) UNSIGNED
)   DETERMINISTIC
BEGIN
    IF (cMode = "insert") THEN
        IF NOT EXISTS(SELECT 1 FROM user WHERE email = cEmail) THEN
            INSERT INTO user(email, firstname, lastname, ismale)
            VALUES (cEmail, cFirstName, cLastName, nIsMale);

            SET nUserID = LAST_INSERT_ID(); 

            -- Here I would like to send all details of inserted user.
            SET max_sp_recursion_depth = 1;
            CALL usp_user("select", nUserID, null, null, null, null); 
        ELSE
            SELECT -1 AS "UnSuccess", "Email already exists" AS "Error"; ;        
        END IF;  
    ELSEIF (cMode = "select") THEN
        SELECT email, firstname, lastname, ismale
        FROM user 
        WHERE userid = nUserID;
    ELSEIF (cMode = "delete") THEN
        //delete code
        SELECT 1 AS "Success";
    END IF;
END

当您仔细查看我的代码时，我将使用 max_sp_recursion_depth 在 insert 中递归调用此存储过程。有没有更好的选择来避免递归调用？

谢谢。

我应该使用Label还是GOTO语句？

Answer 1

避免递归的一种选择是更改结构，如下所示:

mysql> DELIMITER //

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

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

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

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

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

mysql> CREATE TABLE IF NOT EXISTS `user` (
    ->   `nuserid` MEDIUMINT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    ->   `firstname` VARCHAR(20) DEFAULT NULL,
    ->   `lastname` VARCHAR(20) DEFAULT NULL,
    ->   `email` VARCHAR(50) DEFAULT NULL,
    ->   `ismale` TINYINT(1) UNSIGNED DEFAULT NULL,
    ->   PRIMARY KEY (`nuserid`),
    ->   UNIQUE KEY `idx_email` (`email`)
    -> )//
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE PROCEDURE `usp_user_insert`(
    ->     IN  `cEmail` VARCHAR(50),
    ->     IN  `cFirstName` VARCHAR(20),
    ->     IN  `cLastName` VARCHAR(20),
    ->     IN  `nIsMale` TINYINT(1) UNSIGNED
    -> )
    -> BEGIN
    ->     DECLARE `_existsEmail` CONDITION FOR SQLSTATE '45000';
    ->     IF (SELECT NOT `existsEmail`(`cEmail`)) THEN
    ->         INSERT INTO `user` (
    ->         `email`,
    ->         `firstname`,
    ->         `lastname`,
    ->         `ismale`
    ->         ) VALUES (
    ->         `cEmail`,
    ->         `cFirstName`,
    ->         `cLastName`,
    ->         `nIsMale`
    ->         );
    ->         CALL `usp_user_select`(LAST_INSERT_ID());
    ->     ELSE
    ->         -- SELECT -1 'UnSuccess', 'Email already exists' 'Error';
    ->         SIGNAL `_existsEmail`
    ->             SET MESSAGE_TEXT = 'Email already exists', MYSQL_ERRNO = 4000;
    ->     END IF;
    -> END//
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE PROCEDURE `usp_user_select`(
    ->     IN `cnuserid` MEDIUMINT UNSIGNED
    -> )
    -> BEGIN
    ->     SELECT
    ->         `email`,
    ->         `firstname`,
    ->         `lastname`,
    ->         `ismale`
    ->     FROM
    ->         `user`
    ->     WHERE
    ->         `nuserid`= `cnuserid`;
    -> END//
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE PROCEDURE `usp_user_delete`()
    -> BEGIN
    ->     -- delete code
    ->     SELECT 1 'Success';
    -> END//
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE FUNCTION `existsEmail`(
    ->     `cemail` VARCHAR(50)
    -> ) RETURNS TINYINT(1)
    -> BEGIN
    ->     RETURN
    ->         EXISTS(
    ->             SELECT
    ->                 NULL
    ->             FROM
    ->                 `user`
    ->             WHERE
    ->                 `email` = `cemail`
    ->         );
    -> END//
Query OK, 0 rows affected (0.00 sec)

mysql> CALL `usp_user_insert`(
    ->     'user@example.com',
    ->     'firstname_user',
    ->     'lastname_user',
    ->     1
    -> )//
+------------------+----------------+---------------+--------+
| email            | firstname      | lastname      | ismale |
+------------------+----------------+---------------+--------+
| user@example.com | firstname_user | lastname_user |      1 |
+------------------+----------------+---------------+--------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> CALL `usp_user_insert`(
    ->     'user@example.com',
    ->     'firstname_user',
    ->     'lastname_user',
    ->     1
    -> )//
ERROR 4000 (45000): Email already exists