dup_val_on_index_Oracle INSERT INTO SELECT（...）DUP_VAL_ON_INDEX异常行为

I have a stored procedure which looks like this:

BEGIN

INSERT INTO result_table

(SELECT (...) FROM query_table);

EXCEPTION

WHEN DUP_VAL_ON_INDEX THEN

NULL;

END;

I'm doing it in a loop which passes multiple parameters to the SELECT statement and in some cases some of the values might duplicate that is why I have to catch the DUP_VAL_ON_INDEX exception.

My question is that if the SELECT statement returns more rows and only one from them exists already in *result_table*, f. ex.

1 'A'

2 'B'

3 'C'

And first row (1 'A') would already be in the table, would other rows which don't exist (second and third from case above) be inserted? Or none of them would be inserted at all?

I'm afraid that none of them would be inserted (and my test case partially confirms that)... If so, what option do I have to achieve desired bahavior? Is there a good way to insert the rows that don't violate the primary key using the construction above?

解决方案

You can use the MERGE statement. Insert the records if they don't exist and do nothing if they already exist.