如何知道在存储过程中插入查询是否成功? [英] How to know if insert query succeeded in stored proceedure?

问题描述

我正在编写一个存储过程来创建一个播放器:

I am writing a stored procedure to create a player:

CREATE PROCEDURE `create_player` (
firstName TEXT CHARACTER SET utf8,
lastName TEXT CHARACTER SET utf8,
username TEXT CHARACTER SET utf8,
password TEXT CHARACTER SET utf8,
email TEXT CHARACTER SET utf8,
gender ENUM('m','f'),
avatar INTEGER,
OUT result INTEGER)
BEGIN
DECLARE insertVal INTEGER DEFAULT 0;

INSERT INTO `Players` 
(`PlayerFirstName`, 
`PlayerLastName`, 
`PlayerName`, 
`PlayerPassword`, 
`PlayerEmail`,
`PlayerGender`, 
`PlayerAvatar`, 
`PlayerJoinDate`) VALUES (
 firstName,lastName,username,player_hash_password(password),email,gender,avatar,NOW());
END

假设我想让用户知道这个插入是否成功，我怎么知道它是否成功(受影响的行数).我试图将一个整数变量 = 设置到插入语句中，但没有奏效.

Say I wanted to let the user know if this insert succeeded or not, how can I find out if it succeeded or not (number of rows affected). I tried to set an integer variable = to the insert statement but that did not work.

存储过程错误检查的典型方法是什么?

What is the typical way of error checking for stored procedures?

推荐答案

根据 ROW_COUNT(),

ROW_COUNT() 如果是 UPDATE、DELETE，则返回最后一条语句更改、删除或插入的行数代码>，或<代码>插入.对于其他语句，该值可能没有意义

ROW_COUNT() returns the number of rows changed, deleted, or inserted by the last statement if it was an UPDATE, DELETE, or INSERT. For other statements, the value may not be meaningful

在insert 语句之后，您可以将row_count() 读入OUT 参数result.在调用程序中使用相同的 result 值让用户知道受影响的行数.

After insert statement you can read row_count() into OUT parameter result. Use the same result value in the calling program to let the user know the number of rows affected.

按如下方式更改您的程序:

DROP PROCEDURE IF EXISTS `create_player`;

delimiter //

CREATE PROCEDURE `create_player` (
    firstName TEXT CHARACTER SET utf8,
    lastName TEXT CHARACTER SET utf8,
    username TEXT CHARACTER SET utf8,
    password TEXT CHARACTER SET utf8,
    email TEXT CHARACTER SET utf8,
    gender ENUM('m','f'),
    avatar INTEGER,
    OUT result INTEGER )
BEGIN
    DECLARE insertVal INTEGER DEFAULT 0;

    INSERT INTO `Players`(
        `PlayerFirstName`, 
        `PlayerLastName`, 
        `PlayerName`, 
        `PlayerPassword`, 
        `PlayerEmail`,
        `PlayerGender`, 
        `PlayerAvatar`, 
        `PlayerJoinDate`)
    VALUES (
        firstName, lastName, username, 
        player_hash_password( password ), 
        email, gender, avatar, NOW() );

    SELECT ROW_COUNT() INTO result;
END;

//

delimiter ;

这篇关于如何知道在存储过程中插入查询是否成功?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！