Teradata父存储过程调用子存储过程时如何实现失败不中断

API 探路者

2026-6-30

Teradata父存储过程实现子过程失败不中断执行

要实现父存储过程在某个子存储过程执行失败时不终止，继续执行后续子过程，核心是为每个子过程调用单独添加异常捕获逻辑，将错误局部化处理，避免异常扩散导致整个父过程终止。

解决方案1：逐个包裹异常处理块

为每个CALL语句单独创建异常捕获块，捕获执行中的错误并处理（比如记录日志），之后继续执行后续调用：

REPLACE PROCEDURE DP_SI.LOAD_GEBAYA_SI_TABLE_PROCEDURE ()
BEGIN
    -- 调用第一个子过程，异常捕获
    BEGIN
        CALL DP_SI.LOAD_SI_GEBAYA_PROCEDURE_INTERNAL ('STG_GEBAYA_TRANSACTIONS');
    EXCEPTION
        WHEN OTHERS THEN
            -- 可选：输出错误信息到客户端
            MESSAGE '执行STG_GEBAYA_TRANSACTIONS失败，错误码: ' || SQLCODE || ', 错误信息: ' || SQLSTATE TO CLIENT;
    END;

    -- 调用第二个子过程，异常捕获
    BEGIN
        CALL DP_SI.LOAD_SI_GEBAYA_PROCEDURE_INTERNAL ('STG_GEBAYA_TRANSACTIONSTATUS');
    EXCEPTION
        WHEN OTHERS THEN
            MESSAGE '执行STG_GEBAYA_TRANSACTIONSTATUS失败，错误码: ' || SQLCODE || ', 错误信息: ' || SQLSTATE TO CLIENT;
    END;

    -- 以下重复上述模式处理剩余子过程调用
    BEGIN
        CALL DP_SI.LOAD_SI_GEBAYA_PROCEDURE_INTERNAL ('STG_GEBAYA_REGIONS');
    EXCEPTION
        WHEN OTHERS THEN
            MESSAGE '执行STG_GEBAYA_REGIONS失败，错误码: ' || SQLCODE || ', 错误信息: ' || SQLSTATE TO CLIENT;
    END;

    BEGIN
        CALL DP_SI.LOAD_SI_GEBAYA_PROCEDURE_INTERNAL ('STG_GEBAYA_PAYMENTSTATUS');
    EXCEPTION
        WHEN OTHERS THEN
            MESSAGE '执行STG_GEBAYA_PAYMENTSTATUS失败，错误码: ' || SQLCODE || ', 错误信息: ' || SQLSTATE TO CLIENT;
    END;

    BEGIN
        CALL DP_SI.LOAD_SI_GEBAYA_PROCEDURE_INTERNAL ('STG_GEBAYA_REQUESTHISTORY');
    EXCEPTION
        WHEN OTHERS THEN
            MESSAGE '执行STG_GEBAYA_REQUESTHISTORY失败，错误码: ' || SQLCODE || ', 错误信息: ' || SQLSTATE TO CLIENT;
    END;

    BEGIN
        CALL DP_SI.LOAD_SI_GEBAYA_PROCEDURE_INTERNAL ('STG_GEBAYA_PRODUCTIONCOST');
    EXCEPTION
        WHEN OTHERS THEN
            MESSAGE '执行STG_GEBAYA_PRODUCTIONCOST失败，错误码: ' || SQLCODE || ', 错误信息: ' || SQLSTATE TO CLIENT;
    END;

    BEGIN
        CALL DP_SI.LOAD_SI_GEBAYA_PROCEDURE_INTERNAL ('STG_GEBAYA_REQUESTS');
    EXCEPTION
        WHEN OTHERS THEN
            MESSAGE '执行STG_GEBAYA_REQUESTS失败，错误码: ' || SQLCODE || ', 错误信息: ' || SQLSTATE TO CLIENT;
    END;

    BEGIN
        CALL DP_SI.LOAD_SI_GEBAYA_PROCEDURE_INTERNAL ('STG_GEBAYA_REQUESTSTATUS');
    EXCEPTION
        WHEN OTHERS THEN
            MESSAGE '执行STG_GEBAYA_REQUESTSTATUS失败，错误码: ' || SQLCODE || ', 错误信息: ' || SQLSTATE TO CLIENT;
    END;

    BEGIN
        CALL DP_SI.LOAD_SI_GEBAYA_PROCEDURE_INTERNAL ('STG_GEBAYA_REQUESTSTYPES');
    EXCEPTION
        WHEN OTHERS THEN
            MESSAGE '执行STG_GEBAYA_REQUESTSTYPES失败，错误码: ' || SQLCODE || ', 错误信息: ' || SQLSTATE TO CLIENT;
    END;

    BEGIN
        CALL DP_SI.LOAD_SI_GEBAYA_PROCEDURE_INTERNAL ('STG_GEBAYA_TYPEPRICES');
    EXCEPTION
        WHEN OTHERS THEN
            MESSAGE '执行STG_GEBAYA_TYPEPRICES失败，错误码: ' || SQLCODE || ', 错误信息: ' || SQLSTATE TO CLIENT;
    END;

    BEGIN
        CALL DP_SI.LOAD_SI_GEBAYA_PROCEDURE_INTERNAL ('STG_GEBAYA_USERDETAILS');
    EXCEPTION
        WHEN OTHERS THEN
            MESSAGE '执行STG_GEBAYA_USERDETAILS失败，错误码: ' || SQLCODE || ', 错误信息: ' || SQLSTATE TO CLIENT;
    END;

    BEGIN
        CALL DP_SI.LOAD_SI_GEBAYA_PROCEDURE_INTERNAL ('STG_GEBAYA_USERITEMS');
    EXCEPTION
        WHEN OTHERS THEN
            MESSAGE '执行STG_GEBAYA_USERITEMS失败，错误码: ' || SQLCODE || ', 错误信息: ' || SQLSTATE TO CLIENT;
    END;

    BEGIN
        CALL DP_SI.LOAD_SI_GEBAYA_PROCEDURE_INTERNAL ('STG_GEBAYA_ANAAMSUBTYPES');
    EXCEPTION
        WHEN OTHERS THEN
            MESSAGE '执行STG_GEBAYA_ANAAMSUBTYPES失败，错误码: ' || SQLCODE || ', 错误信息: ' || SQLSTATE TO CLIENT;
    END;

    BEGIN
        CALL DP_SI.LOAD_SI_GEBAYA_PROCEDURE_INTERNAL ('STG_GEBAYA_ANAAMTYPES');
    EXCEPTION
        WHEN OTHERS THEN
            MESSAGE '执行STG_GEBAYA_ANAAMTYPES失败，错误码: ' || SQLCODE || ', 错误信息: ' || SQLSTATE TO CLIENT;
    END;

    BEGIN
        CALL DP_SI.LOAD_SI_GEBAYA_PROCEDURE_INTERNAL ('STG_GEBAYA_ATTACHMENTS');
    EXCEPTION
        WHEN OTHERS THEN
            MESSAGE '执行STG_GEBAYA_ATTACHMENTS失败，错误码: ' || SQLCODE || ', 错误信息: ' || SQLSTATE TO CLIENT;
    END;

    BEGIN
        CALL DP_SI.LOAD_SI_GEBAYA_PROCEDURE_INTERNAL ('STG_GEBAYA_CITIES');
    EXCEPTION
        WHEN OTHERS THEN
            MESSAGE '执行STG_GEBAYA_CITIES失败，错误码: ' || SQLCODE || ', 错误信息: ' || SQLSTATE TO CLIENT;
    END;

    BEGIN
        CALL DP_SI.LOAD_SI_GEBAYA_PROCEDURE_INTERNAL ('STG_GEBAYA_PAYMENTS');
    EXCEPTION
        WHEN OTHERS THEN
            MESSAGE '执行STG_GEBAYA_PAYMENTS失败，错误码: ' || SQLCODE || ', 错误信息: ' || SQLSTATE TO CLIENT;
    END;

    BEGIN
        CALL DP_SI.LOAD_SI_GEBAYA_PROCEDURE_INTERNAL ('STG_GEBAYA_PRICEREGIONS');
    EXCEPTION
        WHEN OTHERS THEN
            MESSAGE '执行STG_GEBAYA_PRICEREGIONS失败，错误码: ' || SQLCODE || ', 错误信息: ' || SQLSTATE TO CLIENT;
    END;

    BEGIN
        CALL DP_SI.LOAD_SI_GEBAYA_PROCEDURE_INTERNAL ('STG_GEBAYA_PAYMENTTYPES');
    EXCEPTION
        WHEN OTHERS THEN
            MESSAGE '执行STG_GEBAYA_PAYMENTTYPES失败，错误码: ' || SQLCODE || ', 错误信息: ' || SQLSTATE TO CLIENT;
    END;

    BEGIN
        CALL DP_SI.LOAD_SI_GEBAYA_PROCEDURE_INTERNAL ('STG_GEBAYA_WORKERS');
    EXCEPTION
        WHEN OTHERS THEN
            MESSAGE '执行STG_GEBAYA_WORKERS失败，错误码: ' || SQLCODE || ', 错误信息: ' || SQLSTATE TO CLIENT;
    END;
END;

解决方案2：使用循环简化代码（推荐）

由于需要调用的子过程参数都是表名，可通过定义表名数组，用循环遍历调用，减少重复代码：

REPLACE PROCEDURE DP_SI.LOAD_GEBAYA_SI_TABLE_PROCEDURE ()
BEGIN
    -- 定义表名数组
    DECLARE tbl_names VARCHAR(100) ARRAY[20];
    DECLARE i INTEGER DEFAULT 1;

    -- 初始化表名数组
    SET tbl_names[1] = 'STG_GEBAYA_TRANSACTIONS';
    SET tbl_names[2] = 'STG_GEBAYA_TRANSACTIONSTATUS';
    SET tbl_names[3] = 'STG_GEBAYA_REGIONS';
    SET tbl_names[4] = 'STG_GEBAYA_PAYMENTSTATUS';
    SET tbl_names[5] = 'STG_GEBAYA_REQUESTHISTORY';
    SET tbl_names[6] = 'STG_GEBAYA_PRODUCTIONCOST';
    SET tbl_names[7] = 'STG_GEBAYA_REQUESTS';
    SET tbl_names[8] = 'STG_GEBAYA_REQUESTSTATUS';
    SET tbl_names[9] = 'STG_GEBAYA_REQUESTSTYPES';
    SET tbl_names[10] = 'STG_GEBAYA_TYPEPRICES';
    SET tbl_names[11] = 'STG_GEBAYA_USERDETAILS';
    SET tbl_names[12] = 'STG_GEBAYA_USERITEMS';
    SET tbl_names[13] = 'STG_GEBAYA_ANAAMSUBTYPES';
    SET tbl_names[14] = 'STG_GEBAYA_ANAAMTYPES';
    SET tbl_names[15] = 'STG_GEBAYA_ATTACHMENTS';
    SET tbl_names[16] = 'STG_GEBAYA_CITIES';
    SET tbl_names[17] = 'STG_GEBAYA_PAYMENTS';
    SET tbl_names[18] = 'STG_GEBAYA_PRICEREGIONS';
    SET tbl_names[19] = 'STG_GEBAYA_PAYMENTTYPES';
    SET tbl_names[20] = 'STG_GEBAYA_WORKERS';

    -- 循环遍历表名，逐个调用子过程并处理异常
    WHILE i <= CARDINALITY(tbl_names) DO
        BEGIN
            CALL DP_SI.LOAD_SI_GEBAYA_PROCEDURE_INTERNAL (tbl_names[i]);
        EXCEPTION
            WHEN OTHERS THEN
                MESSAGE '执行表' || tbl_names[i] || '失败，错误码: ' || SQLCODE || ', 错误信息: ' || SQLSTATE TO CLIENT;
        END;
        SET i = i + 1;
    END WHILE;
END;

关键说明

异常捕获逻辑：使用BEGIN...EXCEPTION...END块包裹每个子过程调用，WHEN OTHERS会捕获所有类型的异常，避免单个子过程失败导致父过程终止。
错误记录：通过MESSAGE语句将错误信息输出到客户端，也可以将错误插入到自定义的日志表中，方便后续排查问题。
循环优化：第二种方案使用数组存储表名，通过循环批量处理，大幅减少代码冗余，后续新增表只需在数组中添加元素即可。

内容的提问来源于stack exchange，提问作者wasiq

Teradata父存储过程调用子存储过程时如何实现失败不中断

解决方案1：逐个包裹异常处理块 #

解决方案2：使用循环简化代码（推荐） #

关键说明 #

解决方案1：逐个包裹异常处理块

解决方案2：使用循环简化代码（推荐）

关键说明