I have an oracle function that has a parameter which defines in which column the value should be inserted, e.g.
function something(p_value, p_nr)
is
...
begin
if p_nr = 1 then
insert into A (column1) (p_value);
else if p_nr = 2 then
insert into A (column2) (p_value);
...
end if;
I have a couple of values to enter in the table and only this value should be inserted dynamically. Is there an easier way to do this?
If your table structure defines default values for columns, you might also consider conditional insert:
insert all
when :p_nr = 1 then into A( col1 ) values( p_value )
when :p_nr = 2 then into A( col2 ) values( p_value )
when :p_nr = 3 then into A( col3 ) values( p_value )
select :p_value as p_value from dual
;
The advantage is that this query honors default values, look at an example:
create table A(
col1 varchar2( 10 ) default 'default 1',
col2 varchar2( 10 ) default 'default 2',
col3 varchar2( 10 ) default 'default 3'
);
variable p_nr number
variable p_value varchar2( 100 )
exec :p_nr:=2
exec :p_value:='value'
insert into A (col1, col2, col3)
values (case when :p_nr = 1 then :p_value end,
case when :p_nr = 2 then :p_value end,
case when :p_nr = 3 then :p_value end);
select * from A;
COL1 COL2 COL3
---------- ---------- ----------
value
and:
rollback;
insert all
when :p_nr = 1 then into A( col1 ) values( p_value )
when :p_nr = 2 then into A( col2 ) values( p_value )
when :p_nr = 3 then into A( col3 ) values( p_value )
select :p_value as p_value from dual
;
select * from A;
COL1 COL2 COL3
---------- ---------- ----------
default 1 value default 3
You could do:
insert into A (column1, column2)
values (case when p_nr = 1 then p_value end,
case when p_nr = 2 then p_value end);
That would put the value in one of the two columns, and null in the other; which way round depends on the flag value. (I've omitted the implied else null from both cases, but the intent might be clearer with it in).
Since that's now plain SQL it might not even need to be wrapped in a function (or procedure), depending what else you're doing.
