Five Oracle Functions You Might Find Useful

Most of an enterprise application developer’s exposure to the database is primarily restricted to basic DDL, CRUD operations and occasional SQL tuning. So when I recently got a chance to use some not-so-common Oracle functions/statements in a data-migration project, I thought they might be worth sharing. Here they are:

1. Merge

Also referred to as UPSERT, this Oracle PL/SQL statement can be used when it is not sure if you need to make an update or insert into the table. With Merge statement, you can insert into a table when the row does not exist or update it if it does. The syntax is:

MERGE <hint>
INTO
<table_name>
USING
<table_view_or_query>
ON ( <condition>)
WHEN MATCHED THEN <update>
WHEN NOT MATCHED THEN <insert>

So, the following Merge statement will add 1000 dollars to pending payment to the employee, if there is a payment already due for that employee in the Pending_Payments table. And if there is no payment due to the employee, it will create one for 1000 dollars for him/her.

Merge into PENDING_PAYMENTS p
using (select employee_id from employee) e
on (e.employee_id = p.employee_id)
When Matched then
  update set p.payment_due = p.payment_due + 1000
When not matched
  then insert (p.employee_id, p.amount)
  values (e.employee_id, 1000);

2. Delete multiple rows from a table that has a composite primary key

This is a bit confusing and I will try my best to explain it with an example. Assume, you have two tables and you have to delete multiple rows from one of them using data from the other. So that is easy and the SQL statement would be:

Delete from A
where id in (select a_id from B where <condition1>)

Now assume table A has a composite primary key and we have to achieve the same result. Would the following work?

Delete from A where id_col1 in
  (select a_id_col1 from B where <condition1>)
and id_col2 in
  (select a_id_col2 from B where <condition1>)

No, but the desired result could be achieved with following statement:

Delete from A where (id_col1, id_col2)
in (select a_id_col1, a_id_col2 from B where id in ( <condition1>))

3. Minus Vs. “not in”

Even though following statements give the same result, Oracle processes them differently:

Select col1 from table1
minus select col1 from table2
select col1 from table1
where col1 not in (select col1 from table2)

Minus does a full scan on both tables and removes the result from table2 from table1. “not in”, however, compares each row from table1 result with result from table2, resulting in far more reads than minus. So in most cases, it is preferred to use minus over “not in” for this performance reason.

4. NVL

The NVL function is used to substitute null values with a value. The following query would substiute a null middle_name with the literal ‘not available’ in the result rows:

Select first_name,
  NVL(middle_name, ‘not availabale’),
  last_name from employee

5. Decode

The decode is like an “if-then-else” statement, and the syntax is:

decode(expression, search, result [,search, result]….[,default])

So following statement would add an appropriate honorific while giving out the full names of all the employees.

Select decode (gender, ‘M’, ‘Mr.’, ‘F’, ‘Ms.’)
  || ‘ ‘ || full_name from employee

Custom Development