Conditional Default Value – How to set different default Salary for Managers, Clerks and Others? (not in the Great SQL Quiz at OOW)

0

In about a week’s time, I am presenting the Great Oracle SQL Quiz, at Oracle Open World in San Francisco. Thursday morning, 8.30am (it’s session S290735 – Hilton Continental Ballroom 5), is your chance not only to win a book of your choice, but also to see 15 interesting features and functions in Oracle, that you may have seen before, but most likely have not all seen before. Anyway, usually it is good fun this quiz.

One question that might very well have been on the quiz – but is not, so I can let you in on it right away –  is this one: what can I do to implement a conditonal default value? That is: when a new record gets inserted, and the insert statement does not specify a value for a certain column, we want to automatically provide a value for that column. However, the default value should depend on other columns.

For example, the default value for the SAL column in the EMP table should depend on the JOB: when the JOB is MANAGER, the default value for SAL should be 3100. For CLERKs the default SAL is 500 and for all others, it is 2000. Now which of the following code snippets will get me this "conditional default" functionality – in the most compact way?....

 

A. Using a CASE Expression in the Default Value

alter table emp<br />modify (sal number(10,2) default case job <br />                                 when 'MANAGER' then 3100<br />                                 when 'CLERK'   then 500<br />                                 else 2000<br />                                 end<br />       )<br />/<br />&nbsp;

B. Using a simple, straightforward Before Row Insert trigger

create or replace trigger emp_bri <br />before insert on emp<br />for each row<br />begin<br />  if :new.sal = null<br />  then<br />    :new.sal := case :new.job <br />                when 'MANAGER' then 3100<br />                when 'CLERK'   then 500<br />                else 2000<br />                end;<br />  end if;<br />end;<br />/<br />&nbsp;

C. Using a PL/SQL Function that is called in the Default Value Expression

create or replace <br />function get_default_sal(p_job in varchar2)<br />return number<br />is<br />begin<br />  return case p_job <br />         when 'MANAGER' then 3100<br />         when 'CLERK'   then 500<br />         else 2000<br />         end;<br />end;<br />/<br /><br />alter table emp<br />modify (sal number(10,2) default get_default_sal(job)<br />       )<br />/<br />&nbsp;

D. Using a Default Value based on a Application Context that is set from a Before Insert row level trigger

CREATE OR REPLACE CONTEXT EMP_CTX USING EMP_CTX_MGR<br />/<br /><br />create or replace package EMP_CTX_MGR<br />is<br />procedure set_default_sal<br />( p_sal in varchar2<br />);<br /><br />end;<br />/<br /><br />create or replace package body EMP_CTX_MGR<br />is<br />procedure set_default_sal<br />( p_sal in varchar2<br />) is<br />begin<br />  dbms_session.set_context('EMP_CTX' , 'DEFAULT_SAL', p_sal);<br />end set_default_sal;<br /><br />end;<br />/<br /> <br />alter table emp<br />modify (sal number(10,2) default sys_context('EMP_CTX', 'DEFAULT_SAL')<br />       )<br />/<br /> <br />create or replace trigger emp_bri <br />before insert on emp<br />for each row<br />declare<br />  l_sal number(10,2);<br />begin<br />  if :new.sal = null<br />  then<br />    l_sal := case :new.job <br />             when 'MANAGER' then 3100<br />             when 'CLERK'   then 500<br />             else 2000<br />             end;<br />    emp_ctx_mgr.set_default_sal(l_sal);<br />  end if;<br />end;<br />/<br />&nbsp;

E. Using a marker value and a row level before insert trigger

alter table emp<br />modify (sal number(10,2) default -1234.56<br />       )<br />/<br /><br />create or replace trigger emp_bri <br />before insert on emp<br />for each row<br />begin<br />  if :new.sal = -1234.56<br />  then<br />    :new.sal := case :new.job <br />                when 'MANAGER' then 3100<br />                when 'CLERK'   then 500<br />                else 2000<br />                end;<br />  end if;<br />end;<br />/<br />&nbsp;

The correct answer is E. Since the default value is set before the before row (insert) trigger is executed, this trigger has no chance to first set the application context before the default is derived. Using a CASE in a default value definition is allowed, but referring to other columns is not. Using a trigger that checks whether :NEW.SAL IS NULL is not correct, as we have no way of telling whether the NULL indicates that the user chose to ignore the field (and a default should be applied) or a deliberate ‘set to NULL’ on behalf of the user.

Share.

About Author

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director for Fusion Middleware. Consultant, trainer and instructor on diverse areas including Oracle Database (SQL & PLSQL), Service Oriented Architecture, BPM, ADF, Java in various shapes and forms and many other things. Author of the Oracle Press book: Oracle SOA Suite 11g Handbook. Frequent presenter on conferences such as JavaOne, Oracle OpenWorld, ODTUG Kaleidoscope, Devoxx and OBUG. Presenter for Oracle University Celebrity specials.

Comments are closed.