代码之家  ›  专栏  ›  技术社区  ›  Rocketq

为SQLEXCEPTION添加SQL出口处理程序会导致语法错误和意外文本

  •  0
  • Rocketq  · 技术社区  · 6 年前

    我想向该过程添加几个处理程序,简化的当前版本如下:

    REPLACE PROCEDURE DM.AZ_SP ( IN IN_S_DATE DATE, IN IN_E_DATE DATE, OUT sqlMsg_result VARCHAR(133) )
    SQL SECURITY INVOKER
    BEGIN
    
        DECLARE V_S_DATE DATE;
        DECLARE V_E_DATE DATE;
        DECLARE V_REPORT_DATE DATE;
        DECLARE sted_id INTEGER DEFAULT 0;
    
        SET V_S_DATE = IN_S_DATE;
        SET V_E_DATE = IN_E_DATE;
        SET sted_id = 0;
    
    
    
        SET sted_id = 1;
        DECLARE EXIT HANDLER FOR SQLEXCEPTION
                BEGIN
                    insert into DM.t_sp_logs values (
                        current_timestamp
                    ,   'AZ_SP'
                    ,   sted_id
                    ,   'ERROR code: '  || :SQL_CODE
                    );   -- Missing/Invalid SQL statement'E(3707):Syntax error, expected something like an 'END' keyword between ';' and the 'DECLARE' keyword.'.
                END; 
    END; -- Unexpected text 'END'.
    

    这里有什么问题,如何解决?

    1 回复  |  直到 6 年前
        1
  •  1
  •   dnoeth    6 年前

    声明的顺序如下:

    DECLARE variables/condition handlers
    DECLARE cursors
    DECLARE handlers
    

    SET 结束声明部分。

    你需要移动电视机 处理者。

    REPLACE PROCEDURE DM.AZ_SP ( IN IN_S_DATE DATE, IN IN_E_DATE DATE, OUT sqlMsg_result VARCHAR(133) )
    SQL SECURITY INVOKER
    BEGIN
    
        DECLARE V_S_DATE DATE;
        DECLARE V_E_DATE DATE;
        DECLARE V_REPORT_DATE DATE;
        DECLARE sted_id INTEGER DEFAULT 0;
    
        DECLARE EXIT HANDLER FOR SQLEXCEPTION
                BEGIN
                    insert into DM.t_sp_logs values (
                        current_timestamp
                    ,   'AZ_SP'
                    ,   sted_id
                    ,   'ERROR code: '  || :SQL_CODE
                    );   -- Missing/Invalid SQL statement'E(3707):Syntax error, expected something like an 'END' keyword between ';' and the 'DECLARE' keyword.'.
                END; 
    
        SET V_S_DATE = IN_S_DATE;
        SET V_E_DATE = IN_E_DATE;
        SET sted_id = 0;
    
    
    
        SET sted_id = 1;
    
    END; 
    

    或者使用嵌套的BEGIN/END,在每个BEGIN之后都有一个新的声明部分

    REPLACE PROCEDURE DM.AZ_SP ( IN IN_S_DATE DATE, IN IN_E_DATE DATE, OUT sqlMsg_result VARCHAR(133) )
    SQL SECURITY INVOKER
    BEGIN
    
        DECLARE V_S_DATE DATE;
        DECLARE V_E_DATE DATE;
        DECLARE V_REPORT_DATE DATE;
        DECLARE sted_id INTEGER DEFAULT 0;
    
        SET V_S_DATE = IN_S_DATE;
        SET V_E_DATE = IN_E_DATE;
        SET sted_id = 0;
    
    
    
        SET sted_id = 1;
        BEGIN
            DECLARE EXIT HANDLER FOR SQLEXCEPTION
                BEGIN
                    insert into DM.t_sp_logs values (
                        current_timestamp
                    ,   'AZ_SP'
                    ,   sted_id
                    ,   'ERROR code: '  || :SQL_CODE
                    );   -- Missing/Invalid SQL statement'E(3707):Syntax error, expected something like an 'END' keyword between ';' and the 'DECLARE' keyword.'.
                END;
            ..... 
        END;
    END;