Reasonable SELECT ... INTO Oracle solution for case of multiple OR no rows

Question

I just want to SELECT values into variables from inside a procedure.

SELECT blah1,blah2 INTO var1_,var2_ FROM ...

Sometimes a large complex query will have no rows sometimes it will have more than one -- both cases lead to exceptions. I would love to replace the exception behavior with implicit behavior similiar to:

No rows = no value change, Multiple rows = use last

I can constrain the result set easily enough for the "multiple rows" case but "no rows" is much more difficult for situations where you can't use an aggregate function in the SELECT.

Is there any special workarounds or suggestions? Looking to avoid significantly rewriting queries or executing twice to get a rowcount before executing SELECT INTO.

Answer 1

Whats wrong with using an exception block?

  create or replace
procedure p(v_job VARCHAR2) IS
 v_ename VARCHAR2(255);
begin

 select ename into v_ename 
 from (
  select ename
  from scott.emp 
  where job = v_job
  order by v_ename desc )
 where rownum = 1;

 DBMS_OUTPUT.PUT_LINE('Found Rows Logic Here -> Found ' || v_ename);
 EXCEPTION WHEN NO_DATA_FOUND THEN
  DBMS_OUTPUT.PUT_LINE('No Rows found logic here');

end;


SQL>  begin
   p('FOO');
   p('CLERK');
  end;  2    3    4  
  5  /
No Rows found logic here
Found Rows Logic Here -> Found SMITH


PL/SQL procedure successfully completed.

SQL>