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

我有一个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;