doupa8922
doupa8922
采纳率100%
2016-07-15 07:22

它发生#1054我在mysql中调用过程时出错

已采纳
CREATE PROCEDURE test_proc (in name varchar(100),out return_msg varchar(3000))                                             
BEGIN
    BEGIN
        DECLARE v_return_msg VARCHAR(3000);   -- return message
        DECLARE v_error_flag INT DEFAULT 0; 
        DECLARE EXIT HANDLER FOR SQLEXCEPTION  SET v_error_flag = -1; 

        START TRANSACTION;

        INSERT INTO `tablename` (name, value) VALUES (name, 'Test2');
    END;

    IF v_error_flag < 0 THEN 
         ROLLBACK; 
         CALL DBMS_OUTPUT.PUT_LINE('Error : ' || mysql_error );
         SELECT * FROM INSERT_INFO; -- UPDATE_TABLE_USED
         SHOW ERRORS;
    ELSE 
         COMMIT; 
         CALL DBMS_OUTPUT.PUT_LINE('Sucess : ' || sqlerrm );
         SELECT 'Process succeed!!!';
    END IF;
 END

This is my mysql query(procedure).

However, When I CALL my procedure, It occurs error.

MySQL Message: #1054 - Unknown column 'v_error_flag' in 'field list'

I think IF statement occur error.

IF v_error_flag < 0 THEN 

However, I don't know how can I fix it.

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

1条回答

  • dongtan2603 dongtan2603 5年前

    The v_error_flag is declared with in the BEGIN ... END.

    The IF v_error_flag < 0 THEN condition is out of the block so v_error_flag is not recognized and throwing the error.

    You need to move the IF v_error_flag < 0 THEN condition inside the BEGIN ... END block.

    or

    Simply remove or comment the Second BEGIN ... END block as:

    CREATE PROCEDURE test_proc (in name varchar(100),out return_msg varchar(3000))                                             
    BEGIN
        -- BEGIN  <-- remove/comment this
        DECLARE v_return_msg VARCHAR(3000);   -- return message
        DECLARE v_error_flag INT DEFAULT 0; 
        DECLARE EXIT HANDLER FOR SQLEXCEPTION  SET v_error_flag = -1; 
    
        START TRANSACTION;
    
        INSERT INTO `tablename` (name, value) VALUES (name, 'Test2');
        -- END;  <-- remove/comment this
    
        IF v_error_flag < 0 THEN 
                ROLLBACK; 
                CALL DBMS_OUTPUT.PUT_LINE('Error : ' || mysql_error );
                SELECT * FROM INSERT_INFO; -- UPDATE_TABLE_USED
                SHOW ERRORS;
        ELSE 
                COMMIT; 
                CALL DBMS_OUTPUT.PUT_LINE('Sucess : ' || sqlerrm );
                SELECT 'Process succeed!!!';
        END IF;
    END
    
    点赞 评论 复制链接分享

相关推荐