针对多行或无行的情况,将…合理地选择到Oracle解决方案中

使用异常块有什么问题?

  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> 

您可以使用for循环。for循环不会对没有返回的行执行任何操作,如果有多行,则会应用于返回的每一行。您可以调整选择,使其仅返回最后一行。

begin   
    for ARow in (select *
                  from tableA ta
                  Where ta.value = ???) loop                           
        -- do something to ARow
    end loop;   
end;