/*
set serveroutput on;
declare
    idDept integer;
    contor integer := 7000;
    numeAngajat emp.ename%type;
    comision emp.comm%type;
    dataAngajare emp.hiredate%type;
    lipsaComision exception;
begin
    idDept := 30;
    dbms_output.put_line(rpad('Nume', 15, ' ') ||
        rpad('Data angajare', 20, ' ') || lpad('Comision', 15, ' '));
    dbms_output.put_line(rpad('=', 15, '=') ||
        rpad('Data =', 20, '=') || lpad('=', 15, '='));
    loop
        contor := contor + 1;
        begin
            select ename, hiredate, comm
                into numeAngajat, dataAngajare, comision
                from emp where deptno = idDept and empno = contor;
            if comision is null then
                raise lipsaComision;
            end if;
            dbms_output.put_line(rpad(numeAngajat, 15, ' ') ||
                rpad(dataAngajare, 20, ' ') || lpad(comision, 15, ' '));
            exception
                when lipsaComision then
                    dbms_output.put_line(rpad(numeAngajat, 15, ' ') ||
                        rpad(dataAngajare, 20, ' ')
                        || lpad('lipsa comision', 15, ' '));
                when no_data_found then null;
                when others then null;
        end;
        exit when contor = 8000;
    end loop;
end; */

/*
type cevaTabel is table if emp%rowtype;
tabAng cevaTabel;

select *
bulk collect into tabAng
from emp

for index in tabAng.first .. tabbAng.last
    tabAng(index).ename
*/

-- sa se scrie un bloc PL SQL care pt angajatii dintr-un departament
-- a caruui ID se introduce de la tastatura, care au salariul peste media salariilor
-- pt functia pe care o detin
-- sa se genereze o exceptie definita si sa se afiseze un afisez (Angajatul din functia
-- are salariul prea mare
-- daca nu e excpetie, sa se angajeze angajatul

SET serveroutput ON;
DECLARE
    idDept INTEGER;
    contor INTEGER := 7000;
    idx INTEGER;
    numeAngajat emp.ename%TYPE;
    comision emp.comm%TYPE;
    dataAngajare emp.hiredate%TYPE;
    lipsaComision EXCEPTION;
    TYPE cevaTabel IS TABLE OF emp%ROWTYPE;
    tabAng cevaTabel;
    average INTEGER;
BEGIN
    idDept := 30;
    DBMS_OUTPUT.put_line(RPAD('Nume', 15, ' ') ||
        RPAD('Salariu', 20, ' '));

    SELECT * BULK COLLECT INTO tabAng FROM emp WHERE deptno = idDept;
    FOR idx IN tabAng.FIRST..tabAng.LAST
    LOOP
        BEGIN
            SELECT AVG(sal) INTO average FROM emp WHERE job = tabAng(idx).job;
            IF tabAng(idx).sal > average THEN
                RAISE lipsaComision;
            END IF;
            DBMS_OUTPUT.put_line(RPAD(tabAng(idx).ename, 15, ' ') || LPAD(tabAng(idx).sal, 15, ' '));
            EXCEPTION
                WHEN lipsaComision THEN
                    DBMS_OUTPUT.put_line(RPAD(tabAng(idx).ename, 15, ' ')
                        || LPAD('salariu prea mare', 20, ' '));
                WHEN NO_DATA_FOUND THEN NULL;
                WHEN OTHERS THEN NULL;
            END;
        END LOOP;
        EXCEPTION
            WHEN OTHERS THEN NULL;
END;