在存储过程中声明变量时出错? [英] Errors declaring variables in stored procedure?

问题描述

我对 SQL 和存储过程有点陌生.我正在尝试设置一些变量以供稍后在事务中使用，同时还针对异常设置回滚变量，但我无法做到这一点.

I'm sort of new to SQL and stored procedures. I'm trying to set some variables to use later on in a transaction while also setting a rollback variable on an exception, but I'm not able to do this.

我不知道错误在哪里，因为当我在 _rollback 之后切换 how_many 部分时，错误发生了变化.

I don't know where the error is because when I switch the how_many section after the _rollback, The error changes.

我在这里声明变量的方式有什么问题?

What's wrong with the way I'm declaring variables here?

DELIMITER $$
DROP PROCEDURE IF EXISTS `do_thing` $$
CREATE PROCEDURE `do_thing`()
BEGIN
        DECLARE how_many INT;
        SELECT COUNT(*) FROM things INTO how_many;
        -- Prepare roleback.
        DECLARE `_rollback` BOOL DEFAULT 0;
        DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET `_rollback` = 1;
        -- Start transaction.
        START TRANSACTION;
                -- Do all the things.
        IF `_rollback` THEN
                ROLLBACK;
        ELSE
                COMMIT;
        END IF; 
END $$
DELIMITER ;

当我像这样声明变量时:

when I declare variables like this:

    DECLARE how_many INT;
    -- Prepare roleback.
    DECLARE `_rollback` BOOL DEFAULT 0;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET `_rollback` = 1;
    SELECT COUNT(*) FROM tiers INTO how_many;

好像没问题.在程序中设置了某些变量后，我是否不允许声明更多变量?

It seems to be fine. Am I not allowed to declare more variables after certain variables in a procedure are set?

推荐答案

来自手册页 DECLARE 语法:

DECLARE 只允许在 BEGIN ... END 复合语句中并且必须在它的开头，在任何其他语句之前.声明必须遵循一定的顺序.游标声明必须出现在处理程序声明之前.变量和条件声明必须出现在游标或处理程序声明之前.

DECLARE is permitted only inside a BEGIN ... END compound statement and must be at its start, before any other statements. Declarations must follow a certain order. Cursor declarations must appear before handler declarations. Variable and condition declarations must appear before cursor or handler declarations.

请注意，这些在 mysql 中称为 局部变量.它们与用户变量(以 @ 符号为前缀)不同，后者可以与您的语句一起使用并且不需要声明.

Note that these are called Local Variables in mysql. These differ from User Variables (prefixed with the @ symbol), which can be sprinkled about with your statements and do not require a DECLARE.

这篇关于在存储过程中声明变量时出错?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！