SET serveroutput ON;

DECLARE
CURSOR c_departamentos IS SELECT department_id, department_name, LOCATION_ID FROM departments;
CURSOR c_empleados(depto departments.department_id%TYPE) IS SELECT last_name, first_name FROM employees
    WHERE DEPARTMENT_ID = depto;
contador NUMBER;
ciudad nvarchar2(50);
BEGIN

    FOR reg1 IN c_departamentos LOOP
            SELECT CITY INTO ciudad
            FROM locations WHERE locations.LOCATION_ID = reg1.location_id;

            SELECT COUNT(employee_id) INTO contador
            FROM employees WHERE employees.department_id = reg1.department_id;

            IF contador > 0 THEN
            DBMS_OUTPUT.put_line(reg1.department_name || ' - ' || ciudad || ': (' || contador || ')');

            FOR reg3 IN c_empleados(reg1.department_id) LOOP
                DBMS_OUTPUT.put_line('    ' || reg3.last_name || ' ' || reg3.first_name);
            END LOOP;

            END IF;

    END LOOP;

END;

------------------------------------------

SET serveroutput ON;

DECLARE
CURSOR c_empleados (manager DEPARTMENTS.MANAGER_ID%TYPE) IS SELECT last_name, first_name, salary FROM employees
    WHERE manager_id = manager;
dinerototal NUMBER;

BEGIN

    FOR man IN (SELECT departments.manager_id, employees.last_name, employees.first_name FROM departments inner join employees
        ON departments.manager_id = employees.EMPLOYEE_ID)
    LOOP

        DBMS_OUTPUT.put_line('------------------------------------');
        DBMS_OUTPUT.put_line('Manager: ' || man.first_name || ' ' || man.last_name);
        DBMS_OUTPUT.put_line('------------------------------------');

        dinerototal := 0;

        FOR emp IN c_empleados(man.manager_id)
        LOOP

            DBMS_OUTPUT.put_line('Employee: ' || emp.first_name || ' ' || emp.last_name);
            dinerototal := dinerototal + emp.salary;

        END LOOP;

        DBMS_OUTPUT.put_line('Acumulated Salary: ' || TO_CHAR(dinerototal));

    END LOOP;

END;

---------------------------------------------------

SET serveroutput ON;

DECLARE
depto NUMBER := 230;
CURSOR c1(depto departments.department_id%TYPE) IS SELECT emp.first_name, emp.last_name FROM EMPLOYEES emp
WHERE emp.department_id = depto;

cont NUMBER;

nodepto EXCEPTION;
noemploy EXCEPTION;

BEGIN
SELECT COUNT(*) INTO cont FROM DEPARTMENTS WHERE DEPARTMENTS.department_id = depto;
IF cont = 0
THEN RAISE nodepto;
END IF;

SELECT COUNT(*) INTO cont FROM EMPLOYEES WHERE EMPLOYEES.department_id = depto;
IF cont = 0
THEN RAISE noemploy;
END IF;


FOR reg IN c1(depto)
LOOP

    DBMS_OUTPUT.put_line(reg.first_name || reg.last_name);

END LOOP;

EXCEPTION
WHEN noemploy
THEN
DBMS_OUTPUT.put_line('no employees');

WHEN nodepto
THEN
DBMS_OUTPUT.put_line('no depto');
END;