Jul 7 2008, 04:44 PM
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?:
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
-- 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
something tells me there is a more "elegant|efficient" way to go about it. what would you suggest? thanks in advance for your replies.
Jul 7 2008, 05:59 PM
I would look at the CASE statement:http://www.dba-oracle.com/t_case_statement..._null_value.htm
case somedate when NULL then sysdate
Jul 8 2008, 04:07 AM
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
- and some func/proc for data manipulation
create or replace function GetRowX(bar bar_row,idx number) return number is
create or replace procedure SetRowX(id_ number,val number,idx number) is
if val is not null then
select bar_x into a from foo where id=id_;
update foo set bar_x=a where id=id_;
- 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, ...
or you may use loop too for value index
.. it's more "elegant"... ?
Jul 8 2008, 11:02 AM
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.