LOOP, ИСКЛЮЧЕНИЕ, CURSOR

DECLARE
    DM DEPARTMENTS.DEPARTMENT_NAME%TYPE;
    DI EMPLOYEES.DEPARTMENT_ID%TYPE;
CURSOR ZADANIE5 IS
SELECT DEPARTMENT_NAME,COUNT(DEPARTMENT_ID) FROM DEPARTMENTS
JOIN EMPLOYEES USING(DEPARTMENT_ID)
GROUP BY DEPARTMENT_NAME;
BEGIN
    OPEN ZADANIE5;
    LOOP
        FETCH ZADANIE5 INTO DM,DI;
        IF ZADANIE5%NOTFOUND THEN
        raise_application_error(-20010, ' IN department ' || DM || ' no employees ');
        ELSE
                DBMS_OUTPUT.PUT_LINE(' In Department ' || DM || ' work ' || DI || ' employees ');
   END IF;
    END LOOP;
    CLOSE ZADANIE5;
    END;

У меня проблема, потому что программа не работает должным образом. Я хочу, чтобы это выдало исключение, когда сотрудник не работает, но цикл повторяется и снова вылетает, и я получаю ошибку, что я не работаю в бухгалтерии, но на самом деле работает два из них там

Всего 2 ответа


Я думаю, что ваш код нуждается в большем количестве модификаций. Выдает ошибку, как только находит отдел с 0 сотрудниками, а затем программа завершается. Вместо этого вы можете просто распечатать информацию.

Вы можете использовать LEFT JOIN чтобы найти все отделы и их сотрудников, даже если у них нет сотрудников (0 в этом случае).

DECLARE
    DM   DEPARTMENTS.DEPARTMENT_NAME%TYPE;
    DI   NUMBER; -- CHANGE HERE
    CURSOR ZADANIE5 IS
    SELECT D.DEPARTMENT_NAME, COUNT(E.DEPARTMENT_ID )
    FROM DEPARTMENTS D
        LEFT JOIN EMPLOYEES E ON ( D.DEPARTMENT_ID = E.DEPARTMENT_ID )
    GROUP BY DEPARTMENT_NAME;
BEGIN
    OPEN ZADANIE5;
    LOOP
        FETCH ZADANIE5 INTO
            DM, DI;
        EXIT WHEN ZADANIE5%NOTFOUND; -- CHANGE HERE
        IF DI = 0 THEN -- CHANGE HERE
--            RAISE_APPLICATION_ERROR(-20010, ' IN department '
--                                            || DM
--                                            || ' no employees ');
              DBMS_OUTPUT.PUT_LINE(' IN department '
                                            || DM
                                            || ' no employees ');
        ELSE
            DBMS_OUTPUT.PUT_LINE(' In Department '
                                 || DM
                                 || ' work '
                                 || DI
                                 || ' employees ');
        END IF;
    END LOOP;
    CLOSE ZADANIE5;
END;

Найти изменения в коде и описание изменений в строке.


DECLARE
    DM   DEPARTMENTS.DEPARTMENT_NAME%TYPE;
    DI   NUMBER; -- CHANGE HERE
    CURSOR ZADANIE5 IS
    SELECT DEPARTMENT_NAME, COUNT(DEPARTMENT_ID)
    FROM DEPARTMENTS
        LEFT JOIN EMPLOYEES USING ( DEPARTMENT_ID )
    GROUP BY DEPARTMENT_NAME;
BEGIN
    OPEN ZADANIE5;
    LOOP
        FETCH ZADANIE5 INTO
            DM, DI;
        EXIT WHEN ZADANIE5%NOTFOUND; -- CHANGE HERE
        IF DI = 0 THEN 

              DBMS_OUTPUT.PUT_LINE(' IN department '
                                            || DM
                                            || ' no employees ');
        ELSE
            DBMS_OUTPUT.PUT_LINE(' In Department '
                                 || DM
                                 || ' work '
                                 || DI
                                 || ' employees ');
        END IF;
    END LOOP;
    CLOSE ZADANIE5;
END;


In Department Sales work 34 employees 
 In Department Control And Credit work 1 employees 
 In Department Recruiting work 1 employees 
 In Department Corporate Tax work 1 employees 
 In Department IT Support work 1 employees 
 In Department Government Sales work 1 employees 
 In Department Retail Sales work 1 employees 
 In Department Marketing work 2 employees 
 In Department IT Helpdesk work 1 employees 
 In Department Administration work 1 employees 
 In Department Purchasing work 6 employees 
 In Department Contracting work 1 employees 
 In Department NOC work 1 employees 
 In Department Shipping work 45 employees 
 In Department IT work 5 employees 
 In Department Executive work 3 employees 
 In Department Finance work 6 employees 
 In Department Public Relations work 1 employees 
 In Department Shareholder Services work 1 employees 
 In Department Benefits work 1 employees 
 In Department Payroll work 1 employees 
 In Department Human Resources work 1 employees 
 In Department Accounting work 2 employees 
 In Department Treasury work 1 employees 
 In Department Manufacturing work 1 employees 
 In Department Construction work 1 employees 
 In Department Operations work 1 employees 

везде где 1 сотрудник должен печатать без сотрудников


Есть идеи?

10000