在函数内部调用过程将引发MySQL错误1422 [英] Calling a procedure inside a function throws MySQL ERROR 1422

问题描述

我正在建立一个银行"作为我正在上的数据库课程的作业.我创建了一个存储函数，该函数带有一些IN变量，例如帐户ID，客户ID和PIN码，并对此进行检查以查看所提交的数据是否有效.如果数据有效，则该过程将更新帐户余额以表示货币交易.然后，它返回"提交的数据是否有效.这是该过程的代码:

I am building a "bank" as an assignment for a database course I am taking. I have created a stored function which takes a few IN variables, such as account ID, customer ID and a PIN number, and does checks on this to see if the submitted data is valid. If the data is valid, the procedure updates the account balance to represent a monetary transaction. It then "returns" whether or not the submitted data was valid. Here is the code for the procedure:

DELIMITER //
CREATE PROCEDURE retrieveMoney (
    IN holder INT,
    IN pin VARCHAR(4),
    IN account INT,
    IN amount FLOAT,
    OUT success INT
)
BEGIN
    START TRANSACTION;
    SELECT COUNT(id) INTO success FROM account_holder WHERE id=holder AND pin=pin;
    IF success IS NOT NULL THEN
        IF (SELECT balance-amount FROM account WHERE id=account) >= 0 THEN
            UPDATE account SET balance = balance-amount WHERE id=account;
            CALL logTransaction(account,NULL,amount);
            COMMIT;
        ELSE ROLLBACK;
        END IF;
    ELSE ROLLBACK;
    END IF;
END//
DELIMITER ;

我想使查看过程输出更加容易，并且由于不允许在某个函数中使用事务，因此选择编写一个包装函数，如下所示:

I wanted to make it easier to view the output of the procedure, and as I'm not allowed to use transactions within a function I chose to write a wrapper function, as follows:

DELIMITER //
CREATE FUNCTION retrieveMoney (
    holder INT,
    pin VARCHAR(4),
    account INT,
    amount FLOAT
)
RETURNS INT
BEGIN
    CALL retrieveMoney(holder,pin,account,amount,@success);
    RETURN @success;
END//
DELIMITER ;

不幸的是，这不起作用，并且仍然出现以下错误:

Unfortunately this does not work, and I still get this following error:

ERROR 1422 (HY000): Explicit or implicit commit is not allowed in stored function or trigger.

这是因为我在函数内部调用了包含事务的过程?

Is this because I call the procedure, which contains transactions, inside the function?

推荐答案

有很多导致隐式提交的语句，这些语句都不能在存储函数或触发器或内部使用.从存储的函数或触发器中调用的存储过程，因为它的实际效果并没有什么不同.

There are a number of statements that cause an implicit commit, and none of these can be used inside a stored function or a trigger, or in a stored procedure that is called from a stored function or trigger, because that is not really any different in its net effect.

片刻的反思解释了其原因:存储的函数(和触发器)在查询运行时执行.它们始终无一例外地在查询开始之后开始执行，并在查询结束之前完成执行.它们在执行单个查询期间也可以运行多次，尤其是当查询涉及多行时.

A moment's reflection explains the reason for this: stored functions (and triggers) execute while a query is running. They always, without exception, begin executing after the query starts, and finish executing before the query finishes. They can also run multiple times during the execution of a single query, particularly when the query involves multiple rows.

鉴于此，如果在单个查询运行时可以COMMIT进行事务处理，这是没有意义的，而START TRANSACTION正是在事务运行时进行处理-它隐式地提交了当前的交易，然后开始新的交易.

In that light, it would not make sense if it were possible to COMMIT a transaction while a single query is running... and that's what START TRANSACTION does, if a transaction is running -- it implicitly commits the current transaction, and starts a new one.

这在存储过程中很好，只要您不在另一个查询中间调用它即可(通过存储函数或触发器，这是在另一个查询中间调用过程的唯一方法)但是不支持您在此处执行的操作...即使没有事务在运行，仍无法在正在运行的查询中间启动事务.

This is fine in a stored procedure, as long as you're not calling it in the middle of another query (via a stored function or trigger, which is the only way to invoke a procedure in the middle of another query) but doing what you are doing here is not supported... even if there's not a transaction running, it still isn't possible to start a transaction in the middle of a running query.

http://dev.mysql.com/doc/refman/5.6/en/implicit-commit.html

这篇关于在函数内部调用过程将引发MySQL错误1422的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！