Dear Tom,
In my practice I usually see an approach of creating an "update" stored procedure, where parameters are used like this:
CREATE OR REPLACE PROCEDURE make_new_payment(p_param_1 NUMBER, p_param_2 NUMEBR)
AS
BEGIN
UPDATE my_table
SET col1 = NVL(p_param_1, col1),
col2 = NVL(p_param_2, col2)
...
END;
This is not a "table setter", there might be complecated logic in the stored procedure.
My concern is that with this approach side effects can happen, like unnecessary trigger events, and addidtional redo generated.
Could you please suggest better method to handle such a goal - update only columns for which parameters are passed? Or this one is good enough?
Thank you!
------
Tom,
Probably previous example was not good. The basic idea is to update a column is a corresponding parameter was passed, and not to update if the parameter was not passed. Like in the following example:
CREATE OR REPLACE PROCEDURE test_me(p_id NUMBER,
p_attr1 NUMBER DEFAULT BINARY_FLOAT_INFINITY,
p_attr2 VARCHAR2 DEFAULT CHR(0),
p_attr3 DATE DEFAULT TO_DATE('01/01/1900'))
IS
BEGIN
UPDATE my_table
SET attr1 = DECODE(p_attr1, BINARY_FLOAT_INFINITY, attr1, p_attr1)
,attr2 = DECODE(p_attr2, CHR(0), attr2, p_attr2)
,attr3 = DECODE(p_attr3, TO_DATE('01/01/1900'), attr3, p_attr3)
WHERE id = p_id;
END;
In the procedure we provide default values which are never supposed to exist in my table. If the caller omit a parameter, then the column is updated with itself - so the value is not changed. Does it make sense (say, with large number of parameters)? Actually, this approach is rather flexible.
well, we are squarely back to square one.
You started by saying "this is not a setter function":
This is not a "table setter", there might be complecated logic in the stored procedure.
but this IS A SETTER function. It is in fact a setter function and I wrote about what I think about that already (below).
"Flexible" is in the eye of the beholder isn't it. I'm a fan of specific code, not hugely generic code. This is less than maintainable. When I hear "my code is flexible" these days, my 'red alert' scanners go off in my head. When I was in my 20's, I loved to say "my code is flexible". Now I like to say "my code does what it is supposed to do, efficiently, provable, safely".
The use of CHR(0) is somewhat dangerous in todays multiple character set world.
I have to think the invoker of this function would be the one that is best suited to issue the UPDATE - the correct update - the static update - to update the fields they need to update.
---------------------------------------------------------------------
The only other method would be to use specific SQL for specific tasks. This does look like a 'table setter' function to me - either that routine 'make new payment' takes a set of inputs - OR IT DOES NOT. It seems to me that all of the attributes would be updated. The use of NVL is disturbing - for how do you set any of the attributes to NULL now????
I'm in general, not a fan of this approach. I do not have a problem with updating the table "specifically" in different places in the code. In my experience it is rare to have this need as coded above except in the "generic I'll do 5,000 things for everyone" type of routine.
Can you give me a real world example here - then I can comment - in general, this is a really bad (generic) approach.