Is there a way of creating a dynamic update statement while retaining null values?
ProblemTo avoid overloading for every variation required, we can add 'default null' to the input params so they can be omitted when called.
However, this then nulls columns on update. If instead we check for null using nvl and set to current column values on update we then can't pass null to clear the column value:
procedure example_procedure(
p_example_id number,
p_example_param number default null
) as
begin
update
example_table
set
example_column = nvl(p_example_param, example_column)
where
example_id = p_example_id;
end;
ThoughtsIs there anything like an 'ignore' type that can be set instead of 'default null' so that the procedures and update statements know to omit that param/column for update while still setting columns to null when null is passed in:
procedure example_procedure(
p_example_id number,
p_example_param number default ignore
) as
begin
update
example_table
set
example_column = p_example_param
where
example_id = p_example_id;
end;
No update is performed as all 'set' columns referenced are ignored until null or a real value is passed in via param. This could also be useful for select statements and omitting parts of a where clause.
No, there's no option like this for parameters that then cascade to SQL statements.
One way to achieve something similar is the UPDATE SET ROW extension in PL/SQL. This updates all the columns in a table to the corresponding values in a record variable. If any of these are NULL, the update sets them to null.
You can do this with %rowtype variables, which means you update every row in the table:
create table t (
c1 int, c2 date, c3 varchar2(10)
);
insert into t values ( 1, date'2023-04-12', 'stuff' );
declare
rec t%rowtype;
begin
update t
set row = rec
where t.c1 = 1;
end;
/
select * from t;
C1 C2 C3
<null> <null> <null>
This is often undesirable because it means you're changing the primary key. This is something you want to avoid. There are often other columns you want to avoid accidentally setting to null or updating in general.
You can overcome this by defining a record of all the columns the update could potentially change. Then run a set row update that acts on a subquery selecting only these columns:
insert into t values ( 2, date'2023-05-04', 'more stuff' );
declare
type rec is record (
c2 date, c3 varchar2(10)
);
rec2 rec;
begin
update (
select c2, c3 from t
where c1 = 2
)
set row = rec2;
end;
/
select * from t;
C1 C2 C3
<null> <null> <null>
2 <null> <null> Either way, you must ensure you fully initialize the record before the update to avoid setting columns to null by accident. I discussed this in more detail in my Office Hours session all about update: