sql%notfound or when data_not_found

[rbl]

A stupid question that has always bugged me.
Within procedures, most times I want to do a select into a variable and if no result is returned I don't want anything to happen except return a null value. Typically I handle this with something like "select max(whatever_col) into MyVariable from something_table". Surely there is a better way of handling this?

 

[Marco Kalter]

The obvious solution is this:

begin
  select dname
    into v_dname
    from dept
   where deptno = v_deptno;
exception
  when no_data_found then
    v_dname := null;
end;

This is more code than a max() of course.

------------------
Marco Kalter
Allround Automations

[This message has been edited by mkalter (edited 06 February 2003).]