Sometimes in your PL/SQL code you need to query a table which may or may not contain what you're looking for. This post will cover some different ways of doing it.
Suppose you need to find an employee's ID named
SAM. If he doesn't exist in the table then insert a new employee record.
Side note: Technically you can use a
merge statement for this functionality but want to highlight that a row may not be found.
declare l_empno emp.empno%type; begin select e.empno into l_empno from emp e where e.ename = 'SAM'; if l_empno is null then -- ... insert new emp end if; -- ... end; / -- Errors ORA-01403: no data found ORA-06512: at line 4 1. 00000 - "no data found"
An error is raised if the employee (
SAM in this case) isn't found. Here are some options to get around this.
Explicit exception block
This is the most correct way to know if the value exists or not. Some don't like to use this approach as it can clutter code (due to additional lines of code), on the positive side it is very explicit.
declare l_empno emp.empno%type; begin begin select e.empno into l_empno from emp e where e.ename = 'SAM'; exception when no_data_found then l_empno := null; end; end; /
Using an aggregate function will always return a value even if no rows are returned. For example:
select nvl(max(dummy), 'still returns value') val from dual where 1=2 -- Always false ; VAL ------------- still returns value
Using this logic with the base example we can do:
declare l_empno emp.empno%type; begin select max(e.empno) into l_empno from emp e where e.ename = 'SAM'; end; /
I've used this in the past, however it comes with a caveat. If used on a column that isn't unique it will still return one value. If multiple values are present (ex: all
MANAGER) it can lead to false positives.
Similar to the previous option, selecting from
dual (always returns a row) we can
left join to optionally find the value.
declare l_empno emp.empno%type; begin select e.empno into l_empno from dual left join emp e on 1=1 and e.ename = 'SAM'; end; /
I prefer this approach compared to the
max approach since if multiple values are possible an error is returned (which in most cases we want):
declare l_empno emp.empno%type; begin select e.empno into l_empno from dual left join emp e on 1=1 and e.job = 'MANAGER' -- Will return multiple values ; end; / -- Error ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at line 5