如何显示在Oracle PLSQL中的异常块中找不到数据的员工编号

Question

declare 
    type empid_list is table of emp.empno%TYPE;
    empno empid_list;
    job emp.job%type;
    empname emp.ename%type;
begin
    empno :=empid_list(7839, 7698, 7782, 7499, 7522);
    for i in empno.first..empno.last loop
    select job, ename into job, empname
    from emp
    where emp.empno =empno(i);
    dbms_output.put_line(
    to_char(empno(i)||': '||job||', '||empname)
    );
    end loop;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
    --dbms_output.put_line('No data found for employee '||empno);
end;
/

评论行中存在错误。我想显示员工号码。如何做？

Answer 1

您可以将异常处理程序放在循环中：

BEGIN
    empno :=empid_list(7839, 7698, 7782, 7499, 7522);
    FOR i IN empno.FIRST..empno.LAST LOOP
    BEGIN
        SELECT JOB, ename INTO JOB, empname
       FROM emp
       WHERE emp.empno =empno(i);
       DBMS_OUTPUT.PUT_LINE(TO_CHAR(empno(i)||': '||JOB||', '||empname) );
     EXCEPTION
       WHEN NO_DATA_FOUND THEN
            DBMS_OUTPUT.PUT_LINE('No data found for employee '||empno(i));
     END;
    END LOOP;
END;

Answer 2

您可以简单地声明本地 act_s>存储传递的值并将其分配在loop 的中。因为，您无法在范围之外引用迭代器值。

。

例如，

SQL> set serveroutput on
SQL> DECLARE
  2  TYPE empid_list IS TABLE OF emp.empno%TYPE;
  3    empno empid_list;
  4    job emp.job%type;
  5    empname emp.ename%TYPE;
  6    v_empno emp.empno%TYPE; --> Added a variable
  7  BEGIN
  8    empno :=empid_list(7369,9999);
  9    FOR i IN empno.first..empno.last
 10    LOOP
 11      v_empno := empno(i);
 12      SELECT job, ename INTO job, empname FROM emp WHERE emp.empno =empno(i);
 13      dbms_output.put_line( TO_CHAR(empno(i)||': '||job||', '||empname) );
 14    END LOOP;
 15  EXCEPTION
 16  WHEN NO_DATA_FOUND THEN
 17    dbms_output.put_line('No data found for employee '||v_empno); --> Use the variable
 18  END;
 19  /
7369: CLERK, SMITH
No data found for employee 9999
PL/SQL procedure successfully completed.
SQL>

update 即使要在升高异常后继续继续循环，则可以 wrap select 在语句中语句strong>开始 - 远程 - 端 block。

基本上，将异常处理在for循环内部。

SQL> DECLARE
  2  TYPE empid_list IS TABLE OF emp.empno%TYPE;
  3    empno empid_list;
  4    job emp.job%type;
  5    empname emp.ename%TYPE;
  6  BEGIN
  7    empno :=empid_list(9999, 7222,7369);
  8    FOR i IN empno.first..empno.last
  9    LOOP
 10      BEGIN
 11        SELECT JOB, ename INTO JOB, empname FROM emp WHERE emp.empno =empno(i);
 12        dbms_output.put_line( TO_CHAR(empno(i)||': '||JOB||', '||empname) );
 13      EXCEPTION
 14      WHEN NO_DATA_FOUND THEN
 15        dbms_output.put_line('No data found for employee '||empno(i));
 16      END;
 17
 18    END LOOP;
 19  END;
 20  /
No data found for employee 9999
No data found for employee 7222
7369: CLERK, SMITH
PL/SQL procedure successfully completed.
SQL>

Answer 3

您是否尝试使用循环中的异常块 -

   dbms_output.put_line('No data found for employee '||empno(i));

否则您应该声明emp.empno％类型的单独变量，以存储您要访问的empno 。然后，它将有助于在异常块中打印值。