Procedimientos PL/SQL en Oracle: Ejercicios Resueltos con Cursores, Excepciones y Bucles

3- Desarrollar un procedimiento que permita insertar nuevos departamentos según las siguientes especificaciones: Se pasará al procedimiento el nombre del departamento y la localidad. El procedimiento insertará la fila nueva asignando como número de departamento la decena siguiente al número mayor de la tabla. Se incluirá la gestión del error para que no se duplique el nombre de departamento.

CREATE OR REPLACE PROCEDURE insertar_depart(nombre_dep VARCHAR2, loc VARCHAR2) AS
  CURSOR c_dep IS SELECT dnombre FROM depart WHERE dnombre = nombre_dep;
  v_dummy DEPART.DNOMBRE%TYPE;
  v_ulti_num DEPART.DEPT_NO%TYPE;
  nombre_duplicado EXCEPTION;
BEGIN
  OPEN c_dep;
  FETCH c_dep INTO v_dummy;
  CLOSE c_dep;

  IF v_dummy = nombre_dep THEN
    RAISE nombre_duplicado;
  END IF;

  SELECT MAX(dept_no) INTO v_ulti_num FROM depart;
  INSERT INTO depart VALUES ((TRUNC(v_ulti_num, -1) + 10), nombre_dep, loc);
EXCEPTION
  WHEN nombre_duplicado THEN
    DBMS_OUTPUT.PUT_LINE('Err. departamento duplicado');
END;
/

4. Codificar un procedimiento que muestre el nombre de cada departamento y el número de empleados que tiene.

CREATE OR REPLACE PROCEDURE ver_emple_depart AS
  CURSOR c_emple IS
    SELECT dnombre, COUNT(emp_no)
    FROM emple e, depart d
    WHERE d.dept_no = e.dept_no(+) -- no hace falta
    GROUP BY dnombre;
  v_dnombre depart.dnombre%TYPE;
  v_num_emple BINARY_INTEGER;
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 ver_emple_depart;
/

Visualizar los apellidos de los empleados pertenecientes al departamento 20 numerándolos secuencialmente

DECLARE
  CURSOR C1 IS SELECT APELLIDO FROM EMPLE WHERE DEPT_NO = 20;
  V_APE VARCHAR2(20);
BEGIN
  OPEN C1;
  LOOP
    FETCH C1 INTO V_APE;
    EXIT WHEN C1%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(TO_CHAR(C1%ROWCOUNT, '99.') || V_APE);
  END LOOP;
  CLOSE C1;
END;
/

Visualizar los empleados de un departamento mediante un procedimiento y utilizando variables de acoplamiento

Con WHILE

CREATE OR REPLACE PROCEDURE VER_EMPLE_POR_DEP (DEP NUMBER) AS
  V_DEP NUMBER(2);
  CURSOR C1 IS SELECT APELLIDO FROM EMPLE WHERE DEPT_NO = V_DEP;
  V_APE VARCHAR2(10);
BEGIN
  V_DEP := DEP;
  OPEN C1;
  FETCH C1 INTO V_APE;
  WHILE C1%FOUND LOOP
    DBMS_OUTPUT.PUT_LINE(V_APE);
    FETCH C1 INTO V_APE;
  END LOOP;
  CLOSE C1;
END;
/

Con LOOP

CREATE OR REPLACE PROCEDURE VER_EMPLE_POR_DEP (DEP NUMBER) AS
  V_DEP NUMBER(2);
  CURSOR C1 IS SELECT APELLIDO FROM EMPLE WHERE DEPT_NO = V_DEP;
  V_APE VARCHAR2(10);
BEGIN
  V_DEP := DEP;
  OPEN C1;
  LOOP
    FETCH C1 INTO V_APE;
    EXIT WHEN C1%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(V_APE);
  END LOOP;
  CLOSE C1;
END;
/

Escribir un bloque PL que visualice el apellido, el oficio y la comisión de los empleados que superen los 500 €, utilizando CURSOR FOR...LOOP

DECLARE
  CURSOR MI_CURSOR IS
    SELECT APELLIDO, OFICIO, COMISION FROM EMPLE WHERE COMISION > 500;
BEGIN
  FOR V_REG IN MI_CURSOR LOOP
    DBMS_OUTPUT.PUT_LINE(V_REG.APELLIDO || '*' || V_REG.OFICIO || '*' || V_REG.COMISION);
  END LOOP;
END;
/

Escribir un bloque PL que visualice el apellido y la fecha de alta de todos los empleados ordenados por fecha de alta, utilizando CURSOR FOR...LOOP

DECLARE
  CURSOR C1 IS
    SELECT APELLIDO, FECHA_ALT FROM EMPLE ORDER BY FECHA_ALT;
BEGIN
  FOR V_REG IN C1 LOOP
    DBMS_OUTPUT.PUT_LINE(V_REG.APELLIDO || '*' || V_REG.FECHA_ALT);
  END LOOP;
END;
/

Desarrollar un procedimiento que visualice el apellido y la fecha de alta de todos los empleados ordenados por apellido

CREATE OR REPLACE PROCEDURE ver_emple AS
  CURSOR c_emple IS
    SELECT APELLIDO, FECHA_ALT FROM EMPLE ORDER BY APELLIDO;
  v_apellido VARCHAR2(10);
  v_fecha DATE;
BEGIN
  OPEN c_emple;
  FETCH c_emple INTO v_apellido, v_fecha;
  WHILE c_emple%FOUND LOOP
    DBMS_OUTPUT.PUT_LINE(v_apellido || ' * ' || v_fecha);
    FETCH c_emple INTO v_apellido, v_fecha;
  END LOOP;
  CLOSE c_emple;
END ver_emple;
/

Escribir un procedimiento que reciba un número de empleado y una cantidad que incrementará el salario del empleado correspondiente

Utilizar dos excepciones: una definida por el usuario, salario_nulo, y otra predefinida, NO_DATA_FOUND.

CREATE OR REPLACE PROCEDURE subir_salario(num_empleado INTEGER, incremento REAL) IS
  salario_actual REAL;
  salario_nulo EXCEPTION;
BEGIN
  SELECT salario INTO salario_actual FROM emple WHERE emp_no = num_empleado;

  IF salario_actual IS NULL THEN
    RAISE salario_nulo;
  ELSE
    UPDATE emple
    SET salario = salario + incremento
    WHERE emp_no = num_empleado;
  END IF;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE(num_empleado || '* Error. Empleado no encontrado');
  WHEN salario_nulo THEN
    DBMS_OUTPUT.PUT_LINE(num_empleado || '* Error. Salario nulo');
END subir_salario;
/