Clase 26 de Mayo

-- El Cursor

set serveroutput on
declare
cursor crCuentaEmpleados is
  select department_id, department_name, count(*) as total
  from employees natural join departments
  group by department_id, department_name
  order by department_name;
msg char(1);
cnt integer := 0;
begin
  for recCur in crCuentaEmpleados loop
      if recCur.total mod 2 = 0 then
         msg := '*';
         cnt := cnt + 1;
      else
        msg := ' ';
      end if;
      dbms_output.put_line (recCur.department_id||' '||recCur.department_name||' '||recCur.total||' '||msg);
  end loop;
  dbms_output.put_line ('El Total de departamentos con cantidad de empleados par es: '||cnt);
exception
when others then
  dbms_output.put_line ('Error desconocido');
end;


-- El Procedimiento
Create or Replace PROCEDURE actualizaFono (pid Employees.employee_id%type, pfono Employees.phone_number%type) is

cnt integer;
EMPLEADO_NO_EXISTE EXCEPTION;

begin

   select count(*) into cnt
   from employees
   where employee_id = pid;
   if cnt = 0 then
      raise EMPLEADO_NO_EXISTE;
   end if;

   update employees
   set phone_number = pfono
   where employee_id = pid;
   dbms_output.put_line ('Empleado '||pid||' Actualizado');
  
exception

    when EMPLEADO_NO_EXISTE then
        dbms_output.put_line ('Empleado '||pid||' No existe');

    when others then
        dbms_output.put_line ('Error desconocido');   
       
end;

-- La funcion

CREATE OR REPLACE FUNCTION CUENTAEMPLEADOS
(
  pDepto IN employees.department_id%type 
) RETURN NUMBER AS

totalEmpleados integer;
BEGIN
  select count(*) into totalEmpleados
  from Employees
  where department_id = pDepto;
  RETURN totalEmpleados;
END CUENTAEMPLEADOS;