Help - Search - Members - Calendar
Full Version: what's the best way to do a conditional update?
Oracle DBA Forums > Oracle > Oracle Forum
sun_certified
hi forum,

please, can you help out a rookie pl/sql coder? i need to code a stored procedure that will only update particular fields in a record if the corresponding IN param is NOT NULL. how should i go about that? for example, is something like this a sound approach?:
CODE
  create or replace doUpdate( p_id IN VARCHAR2, p_new_value_1 IN VARCHAR2,  p_new_value_2 IN VARCHAR2, p_new_value_3 IN VARCHAR2, ...  , p_new_value_N IN VARCHAR2,    ) IS

   BEGIN

     -- pseudo code
       if p_new_value_1 IS NOT NULL
          update table foo set bar1 = p_new_value_1 where id = p_id


       if p_new_value_2 IS NOT NULL
          update table foo set bar2 = p_new_value_2 where id = p_id

      
       if p_new_value_3 IS NOT NULL
          update table foo set bar3 = p_new_value_3 where id = p_id

       ...

       if p_new_value_N IS NOT NULL
          update table foo set barN = p_new_value_N where id = p_id
  
   END;

something tells me there is a more "elegant|efficient" way to go about it. what would you suggest? thanks in advance for your replies.
HAL9000
I would look at the CASE statement:

http://www.dba-oracle.com/t_case_statement..._null_value.htm

case somedate when NULL then sysdate
else somedate
Littlewheat
Hi sun_certified,
if..else..end if or case is possible...

Next way is change logic:

- change val_1, val_2, val_3,.., val_N value to array
create or replace type bar_row as varray (20) of number (20 or other size)

- create table with N-column to table with this array type
create table foo
(id number,
bar_x bar_row);


- and some func/proc for data manipulation
-- get
create or replace function GetRowX(bar bar_row,idx number) return number is
begin
return bar(idx);
end;

-- set
create or replace procedure SetRowX(id_ number,val number,idx number) is
a bar_row;
begin
if val is not null then
select bar_x into a from foo where id=id_;
a(idx):=val;
update foo set bar_x=a where id=id_;
commit;
end if;
end;


- change call
doUpdate( p_id IN VARCHAR2, p_new_value_1 IN VARCHAR2, p_new_value_2 IN VARCHAR2, p_new_value_3 IN VARCHAR2, ...

to

SetRowx(ID_row,p_new_value_x,index_of_value (1..n);

for example:
SetRowx(1,123,1);
SetRowx(1,null,5);


or you may use loop too for value index

.. it's more "elegant"... ?
SteveC
How many times/places is this called? This is probably number one in terms of worst programming techniques - having to evaluate each and every parameter. If that is your situation, your only real choice is a test (if-then) for each parameter. A case statement won't help because it exits after matching (unless you use case instead of if-then for each parameter, but that is still not any different in terms of having to evaluate each parameter).

You would be better off using something reusable which is called multiple times, and that means evaluating for null prior to calling (what's the point of calling a procedure if nothing is going to be done?). You would still pass in p_id, but now add the column name and the value, and use dynamic SQL to build your update statement.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2014 Invision Power Services, Inc.