Когда я выполняю приведенный ниже код, он не выводит сообщение «Сотрудник не существует с идентификатором отдела» для deptno = 40.
declare
Cursor c1 is select * from dept;
Cursor c2(p_deptno number) is select * from emp where deptno=p_deptno;
Begin
For i in c1
Loop
for j in c2(i.deptno)
loop
if sql%notfound then
dbms_output.put_line('Employee doesnt exist with deartment id' || i.deptno);
else
dbms_output.put_line(i.deptno || ' ' || j.empno || ' ' || j.ename);
end if;
end loop;
end loop;
end;
/
Выход:
10 7782 CLARK
10 7839 KING
10 7934 MILLER
20 7369 SMITH
20 7566 JONES
20 7788 SCOTT
20 7876 ADAMS
20 7902 FORD
30 7499 ALLEN
30 7521 WARD
30 7654 MARTIN
30 7698 BLAKE
30 7844 TURNER
30 7900 JAMES
Содержание таблиц:
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
Возможный дубликат DBMS_OUTPUT.PUT_LINE не печатает
Вы просматриваете список строк:
BEGIN
FOR j IN (SELECT * -- fake table with no rows
FROM DUAL
WHERE 1 = 2)
LOOP
DBMS_OUTPUT.put_line ('work, work..'); -- this won't happen
END LOOP;
END;
Если у вас нет строк, вы ничего не перебираете.
Вы можете использовать sql%notfound
при выборе:
DECLARE
v_tmp NUMBER;
BEGIN
SELECT col1
INTO v_tmp
FROM (SELECT 1 col1 FROM DUAL -- select nothing from a fake table
UNION ALL
SELECT 2 col1
FROM DUAL
WHERE 1 = 2 -- change to 1=1 to get a too-many-rows exception
);
EXCEPTION
WHEN OTHERS
THEN
IF (SQL%NOTFOUND)
THEN
DBMS_OUTPUT.put_line ('I didn''t find anything..');
ELSE
DBMS_OUTPUT.put_line ('We have some other exception..');
END IF;
END;
Решением может быть установка флага и проверка его после внутреннего цикла:
DECLARE
CURSOR c1
IS
SELECT * FROM dept;
CURSOR c2 (p_deptno NUMBER)
IS
SELECT *
FROM emp
WHERE deptno = p_deptno;
v_found BOOLEAN;
BEGIN
FOR i IN c1
LOOP
v_found := FALSE; -- will be set when we find something..
FOR j IN c2 (i.deptno)
LOOP
v_found := TRUE; -- we found something!
DBMS_OUTPUT.put_line (
i.deptno || ' ' || j.empno || ' ' || j.ename);
END LOOP;
IF (NOT v_found) -- check if we did find i.deptno
THEN
DBMS_OUTPUT.put_line (
'Employee doesnt exist with deartment id' || i.deptno);
END IF;
END LOOP;
END;
Не выполняйте цикл вокруг курсора в другом цикле вокруг курсора, если вы вообще можете этого избежать. Тем самым вы заново изобрели соединение вложенных циклов, которое может быть не самым эффективным способом объединения ваших наборов результатов.
Вместо этого вы должны сначала объединить два курсора в один. Таким образом оптимизатор сможет выбрать лучший способ соединения.
В вашем случае вам нужно внешнее присоединить второй курсор к первому, что означает, что ваша процедура станет:
BEGIN
FOR i IN (SELECT d.deptno,
e.empno,
e.ename
FROM dept d
LEFT OUTER JOIN emp e
ON d.deptno = e.deptno)
LOOP
IF e.empno IS NOT NULL
THEN
dbms_output.put_line('Employee doesn''t exist with department id' || i.deptno);
ELSE
dbms_output.put_line(i.deptno || ' ' || i.empno || ' ' || i.ename);
END IF;
END LOOP;
END;
/
Вы знаете, что для этого вам не нужен PL / SQL?