In Oracle, given the following objects:
create table a (x number );
create table b (val number );
create table c (val number );
create sequence my_seq;
Suppose I populate table a
as follows:
insert into a (x) values (1);
insert into a (x) values (2);
insert into a (x) values (3);
insert into a (x) values (4);
Using the values in a
, I want to populate tables b
and c
as follows: when a.x
is even, generate a new number from the sequence and insert my_seq.nextval
into b.val
and c.val
; otherwise, insert my_seq.currval
(the most recently generated value but not a new one) into c.val
.
What I currently have is an insert all statement that looks like this:
insert all
when mod(x,2) = 0 then
into b (val) values (my_seq.nextval)
into c (val) values (my_seq.currval)
else
into c (val) values (my_seq.currval)
select x from a
Since Oracle's treatment of sequences in an insert all statement is to generate a new value for each row returned in the subquery, I'm getting 4 newly generated values inserted into c
. How can I attain the behavior I want, 2 newly newly generated values inserted once into b
and twice into c
?
Best Answer
This can be done in a single statement, but you have to wrap the sequence call in a function.
By calling a function in the select (as opposed to the sequence directly), you overcome the problem of getting ORA-02287 errors. However, the function will be called once for each row in A, which is not what you want.
This can be overcome by defining the function as deterministic. This allows Oracle to optimize the function so it is only executed once for each distinct parameter value passed in. To make this work in this case, you'll need to pass in
ceil(x/2)
: