首页 文章

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

提问于
浏览 次
0

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

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

例如，如果我插入2行具有重复值，则必须引发 dup_val_on_index exception 并且不应插入第1行 .

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

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;
oracle plsql

2 回答

  • 0

    希望，我理解你的问题 . 请尝试使用下面的块 .

    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;
    回复于
  • 0

    从我如何理解你的问题来看，它应该更简单：

    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
            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()
            );

    END LOOP;

    SELECT
        COUNT(*)
    INTO p_count
    FROM
        uk_depots;

EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK TO s1;
END;

    这个块相当无用，无论你得到哪个错误，都要执行回滚 .

    EXCEPTION
        WHEN dup_val_on_index THEN
            ROLLBACK TO s1;
            EXIT;
        WHEN OTHERS THEN
            ROLLBACK TO s1;
            EXIT;
    END;
    回复于

相关问题