Procedimientos PL/SQL y cursores en Oracle: ejercicios resueltos de empleados y departamentos

Ejercicios de procedimientos almacenados

Ejercicio 1

Codificar un procedimiento que muestre el nombre de cada departamento y el número de empleados que tiene. Realizar el programa 1º sin utilizar un registro y otro utilizando registros.

Versión sin registros

create or replace procedure ejeuno
as
cursor c_emple IS
select dnombre, count(EMP_NO) from emple e, depart d
where d.dept_no=e.dept_no(+)
group by dnombre;
v_dnombre depart.dnombre%type;
v_num_emple NUMBER;

begin
open c_emple;
fetch c_emple into v_dnombre, v_num_emple;
while c_emple%found loop
dbms_output.put_line(v_dnombre||'*'||v_num_emple);
fetch c_emple into v_dnombre, v_num_emple;
END loop;
close c_emple;
end;

Versión utilizando registros

/* Utilizando registros */
create or replace procedure ejeuno
as
cursor c_emple IS
select dnombre, count(EMP_NO) from emple e, depart d
where d.dept_no=e.dept_no(+)
group by dnombre;
reg_emple c_emple%rowtype;

begin
open c_emple;
fetch c_emple into reg_emple;
while c_emple%found loop
dbms_output.put_line(reg_emple.dnombre||'*'||reg_emple.num_emple);
fetch c_emple into reg_emple;
END loop;
close c_emple;
end;

Ejercicio 2

Subir el salario a todos los empleados del departamento (conocido su nombre) indicando en la llamada. El porcentaje de subida se indicará también en la llamada.

Ejercicio 3

Realizar un procedimiento que incremente las comisiones de un empleado cualquiera. Si el empleado no tiene comisiones, aparecerá un mensaje de error ("El empleado no tiene comisiones"). Utilizar las excepciones definidas por el usuario.

create or replace procedure p_increment (v_comision number, v_empleado number)
AS
va_comision number(10);
begin
select comision into va_comision from emple where emp_no=v_empleado;

if va_comision is null then
raise_application_error(-20000, 'El empleado no tiene comisiones');
else
update emple set comision = (comision * v_comision)+comision where emp_no=v_empleado;
end if;

end;

Solución de la profesora

create or replace procedure subir_comisiones (emple number, subir_comision number)
is
euros_comision number;
begin
select comision into euros_comision from empleados
where emp_no=emple;
if euros_comision is null then
raise_application_error(-20010, 'No tiene comisión');
else
update empleados set comision=euros_comision+subir_comision where id_empleado=emple;
end if;
end;

Procedimiento MySQL con cursor

delimiter $$
DROP PROCEDURE IF EXISTS almacenar$$
CREATE PROCEDURE almacenar ()
BEGIN
DECLARE Contador, Done, ID int default 0;
DECLARE v_apellidos varchar(30);
DECLARE v_funcion varchar(30);
DECLARE v_salario int;
DECLARE cursor1 CURSOR FOR SELECT DNI, APELLIDOS, FUNCION, SALARIO FROM PERSONAL;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;

OPEN cursor1;
REPEAT
FETCH cursor1 INTO ID, v_apellidos, v_funcion, v_salario;
SET v_salario=v_salario+200;
IF NOT done and v_funcion='PROFESOR'
then
INSERT INTO PROFESORES VALUES (ID, v_apellidos, v_funcion, v_salario);
SET Contador=contador+1;
END IF;
UNTIL done end repeat;
close cursor1;

select contador;
END;
$$