update procedure 2

[slok]

given, if I have a table as follows:

create table test (
fieldA date not null,
fieldB char (6) not null,
fieldC char (30) not null,
fieldD char (10) not null,
constraint pk_test primary key (fieldA, fieldB))


How can I write a update procedure where the update
statement is dynamic?

eg.
I may pass in to the update procedure fieldA to fieldD as
parameters.

1. But fieldA and fieldB will not be modifiable.

2. But fieldC and fieldD, they may/may not be updated.
So, sometimes I may pass a NULL for fieldC or fieldD.
Under those circumstances, how can I build an update
statment that take care of fieldC and filedD when they
are NULL?

Thanks

 

[Bveoracle]

Such as this?
This has not been tested, there may be typing mistakes.

declare
l_cmd varchar2(256); --Increase size as needed
begin
l_cmd := 'UPDATE TEST SET '
if fieldC is not null
then
l_cmd := l_cmd||'fieldC = '''||fieldC||'''';
end if;
if fieldD is not null
then
if fieldC is not null
then
l_cmd := l_cmd||','
end if;
l_cmd := l_cmd||'fieldD = '''||fieldD||'''';
end if;

-- If no fields are filled there won't be
-- a valid update statement
if ( fieldC is not null ) or
( fieldD is not null )
then
-- Add condition
l_cmd := l_cmd||' WHERE fieldA = '''||fieldA||'''';
l_cmd := l_cmd||' AND fieldB = '''||fieldB||'''';

-- Execute
execute immediate l_cmd
end if;
end;

 

[sem]

procedure update_test (pfieldA in date,
pfieldB in char (6) ,
pfieldC in char (30),
pfieldD in char (10)
)
is
begin
UPDATE test SET
fieldC=NVL(pfieldC,fieldC),
fieldD=NVL(pfieldD,fieldD)
where fieldA=pfieldA and fieldB=pfieldB;
end;