时间戳

首页 > 解决方案 > oracle for 循环中的 ROLLBACK 不适用于 for 循环之外提到的语句

oracle - oracle for 循环中的 ROLLBACK 不适用于 for 循环之外提到的语句

问题描述

我有一个 pl sql 过程，它接受元素数组并将它们插入到表中。

在程序开始时，我从备份表中删除数据并将主表中的数据插入到备份表中。然后我从主表中删除数据并循环遍历proc的参数并插入记录。当我面对dup_val_on_index exception时，回滚发生在 proc 的起点。我的意思是异常块正在被执行。但回滚并没有发生。

例如，如果我插入具有重复值的 2 行，dup_val_on_index exception则必须提高并且不应插入第一行。

下面是我的代码。如果循环内发生任何异常，我也想回滚插入并在过程开始时执行删除操作

PROCEDURE insert_sales_data (
    p_depot_code       IN depotcode_array,
    p_depot_name       IN depotname_array,
    p_dell_split       IN dellsplit_array,
    p_sector           IN sector_array,
    p_locality         IN locality_array,
    p_tnt_depot_code   IN tntdepotcode_array,
    p_postal_code      IN postalcode_array,
    p_primary_sort     IN primarysort_array,
    p_secondary_sort   IN secondarysort_array,
    p_user             IN VARCHAR2,
    p_error_message    OUT VARCHAR2,
    p_count            OUT NUMBER
)
    IS
BEGIN
    SAVEPOINT s1;
    DELETE FROM sales_backup;

    INSERT INTO sales_backup
        SELECT
            *
        FROM
            sales;

    DELETE FROM sales;

    FOR i IN p_sector.first..p_sector.last LOOP
        BEGIN
            INSERT INTO sales (
                depot_code,
                depot_name,
                dell_split,
                sector,
                locality,
                tnt_depot_code,
                postal_code,
                primary_sort,
                secondary_sort,
                create_date,
                create_user_id,
                uuid
            ) VALUES (
                p_depot_code(i),
                p_depot_name(i),
                p_dell_split(i),
                p_sector(i),
                p_locality(i),
                p_tnt_depot_code(i),
                p_postal_code(i),
                p_primary_sort(i),
                p_secondary_sort(i),
                SYSDATE,
                p_user,
                sys_guid()
            );

        EXCEPTION
            WHEN dup_val_on_index THEN
                ROLLBACK TO s1;
                EXIT;
            WHEN OTHERS THEN
                ROLLBACK TO s1;
                EXIT;
        END;
    END LOOP;

    SELECT
        COUNT(*)
    INTO p_count
    FROM
        uk_depots;

EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK TO s1;
END;

标签： oracleplsql

解决方案

希望，我正确理解了你的问题。请尝试使用下面的块。

    PROCEDURE insert_sales_data (
        p_depot_code       IN depotcode_array,
        p_depot_name       IN depotname_array,
        p_dell_split       IN dellsplit_array,
        p_sector           IN sector_array,
        p_locality         IN locality_array,
        p_tnt_depot_code   IN tntdepotcode_array,
        p_postal_code      IN postalcode_array,
        p_primary_sort     IN primarysort_array,
        p_secondary_sort   IN secondarysort_array,
        p_user             IN VARCHAR2,
        p_error_message    OUT VARCHAR2,
        p_count            OUT NUMBER
    )
        IS
    BEGIN

        DELETE FROM sales_backup;

        INSERT INTO sales_backup
            SELECT
                *
            FROM
                sales;

        DELETE FROM sales;

        FOR i IN p_sector.first..p_sector.last LOOP
            BEGIN
                INSERT INTO sales (
                    depot_code,
                    depot_name,
                    dell_split,
                    sector,
                    locality,
                    tnt_depot_code,
                    postal_code,
                    primary_sort,
                    secondary_sort,
                    create_date,
                    create_user_id,
                    uuid
                ) VALUES (
                    p_depot_code(i),
                    p_depot_name(i),
                    p_dell_split(i),
                    p_sector(i),
                    p_locality(i),
                    p_tnt_depot_code(i),
                    p_postal_code(i),
                    p_primary_sort(i),
                    p_secondary_sort(i),
                    SYSDATE,
                    p_user,
                    sys_guid()
                );

            EXCEPTION
                WHEN dup_val_on_index THEN
                    ROLLBACK;
                    EXIT;
                WHEN OTHERS THEN
                    ROLLBACK;
                    EXIT;
            END;
        END LOOP;

        SELECT
            COUNT(*)
        INTO p_count
        FROM
            uk_depots;

    EXCEPTION
        WHEN OTHERS THEN
            ROLLBACK;
    END;

推荐阅读