oracle怎么抛错误,oracle – 如何确定PL / SQL语句中的行/值抛出错误？

使用SAVE EXCEPTIONS子句的解决方案：

SQL> create table my_table (my_column)

2 as

3 select level from dual connect by level <= 9

4 /

Table created.

SQL> create function do_something

2 ( p_my_column in my_table.my_column%type

3 ) return my_table.my_column%type

4 is

5 begin

6 return 10 + p_my_column;

7 end;

8 /

Function created.

SQL> alter table my_table add check (my_column not in (12,14))

2 /

Table altered.

SQL> declare

2 e_forall_error exception;

3 pragma exception_init(e_forall_error,-24381)

4 ;

5 type t_my_columns is table of my_table.my_column%type;

6 a_my_columns t_my_columns := t_my_columns()

7 ;

8 begin

9 select my_column

10 bulk collect into a_my_columns

11 from my_table

12 ;

13 forall i in 1..a_my_columns.count save exceptions

14 update my_table

15 set my_column = do_something(a_my_columns(i))

16 where my_column = a_my_columns(i)

17 ;

18 exception

19 when e_forall_error then

20 for i in 1..sql%bulk_exceptions.count

21 loop

22 dbms_output.put_line(a_my_columns(sql%bulk_exceptions(i).error_index));

23 end loop;

24 end;

25 /

2

4

PL/SQL procedure successfully completed.

对于非常大的数据集,您可能不希望炸毁PGA内存,因此在这种情况下一定要使用LIMIT子句.