Ejercicios Resueltos PL/SQL

1. Números en Orden Inverso

Escribir un script cuya salida sean los números de 1 a 5 en orden inverso:

DECLARE
  Cont NUMBER;
BEGIN
  FOR cont IN REVERSE 1..5 LOOP
    DBMS_OUTPUT.PUT_LINE(cont);
  END LOOP;
END;
/

2. Numeración de Empleados por Oficio

Escribir un script cuya salida sea la numeración de los empleados de la tabla EMPLE, numerados desde 1 hasta el último, precedido de la palabra TRABAJADOR. (TRABAJADOR 1, TRABAJADOR 2,…):

DECLARE
  Cont1 NUMBER;
  Cont2 NUMBER;
  Cont3 NUMBER;
  I NUMBER;
  J NUMBER;
  K NUMBER;
BEGIN
  SELECT COUNT(*) INTO Cont1 FROM emple WHERE oficio = 'ANALISTA';
  SELECT COUNT(*) INTO Cont2 FROM emple WHERE oficio = 'VENDEDOR';
  SELECT COUNT(*) INTO Cont3 FROM emple WHERE oficio NOT IN ('ANALISTA', 'VENDEDOR');

  FOR i IN 1..Cont1 LOOP
    DBMS_OUTPUT.PUT_LINE('Analista' || i);
  END LOOP;

  FOR j IN 1..Cont2 LOOP
    DBMS_OUTPUT.PUT_LINE('Vendedor' || j);
  END LOOP;

  FOR k IN 1..Cont3 LOOP
    DBMS_OUTPUT.PUT_LINE('Resto de trabajadores' || k);
  END LOOP;
END;
/

4. Consulta de Apellido con Manejo de Excepciones

Escribir un script que consulte el apellido de un empleado, de forma que si no existe en la tabla consultada la salida sea el mensaje “No hay datos en esta tabla”, y si ocurre algún otro error, el mensaje sea “Algo ha salido mal”.

DECLARE
  N_REG EMPLE%ROWTYPE;
BEGIN
  SELECT * INTO N_REG FROM EMPLE WHERE APELLIDO = 'L';
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RAISE_APPLICATION_ERROR(-20052, 'NO HAY DATOS EN LA TABLA');
  WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR(-20053, 'ALGO HA SALIDO MAL');
END;
/

7. Procedimiento para Contar Empleados por Oficio

Escribir un procedimiento por el que, pasando el nombre de un determinado oficio (ANALISTA, DIRECTOR,…), devuelva el número de empleados (cuántos) que tienen ese oficio.

CREATE OR REPLACE PROCEDURE EMPLEADOS_OFICIOS (EN_OFICIO IN VARCHAR2, EMPLEADOS_CONTADOS OUT NUMBER) IS
  I NUMBER;
  CURSOR C1 IS
    SELECT COUNT(*) FROM EMPLE WHERE OFICIO = EN_OFICIO;
BEGIN
  OPEN C1;
  FETCH C1 INTO I;
  EMPLEADOS_CONTADOS := I;
  CLOSE C1;
END;
/

8. Función para Contar Empleados

Escribir una función que cuente los empleados de la tabla EMPLE.

CREATE OR REPLACE FUNCTION CUENTA_EMPLEADOS RETURN NUMBER IS
  I NUMBER;
  CURSOR C1 IS
    SELECT COUNT(*) FROM EMPLE;
BEGIN
  OPEN C1;
  FETCH C1 INTO I;
  CLOSE C1;
  RETURN I;
END;
/

10. Añadir Nuevo Registro a Estudiantes

Crear un programa que añada un nuevo registro a la tabla ESTUDIANTES. El ID lo crea la secuencia del ejercicio anterior, nombre, apellido y especialidad se dan en el programa.

DECLARE
  V_NUEVA_ESPECIALIDAD VARCHAR2(30) := 'NUTRICION';
  V_NOMBRE VARCHAR2() := 'CARMEN';
  V_APELLIDO VARCHAR2() := 'ANSUREZ';
BEGIN
  UPDATE ESTUDIANTES SET ESPECIALIDAD = V_NUEVA_ESPECIALIDAD WHERE NOMBRE = V_NOMBRE AND APELLIDO = V_APELLIDO;
  IF SQL%NOTFOUND THEN
    INSERT INTO ESTUDIANTES (ID, NOMBRE, APELLIDO, ESPECIALIDAD) VALUES (SECUENCIA.NEXTVAL, V_NOMBRE, V_APELLIDO, V_NUEVA_ESPECIALIDAD);
  END IF;
END;
/

12. Inserción y Selección en Tabla Temporal

Escribir un bloque PL/SQL anónimo que inserte dos filas en la tabla TEMPORAL y posteriormente las seleccione y muestre en pantalla.

DECLARE
  V_N1 NUMBER := 3;
  V_N2 NUMBER := 4;
  V_C1 VARCHAR2(60) := 'HOY ES';
  V_C2 VARCHAR2(60) := 'JUEVES';
  V_SALECADENA1 VARCHAR2(60);
  V_SALECADENA2 VARCHAR2(60);
BEGIN
  INSERT INTO TEMPORAL (COLUMNANUMERICA, COLUMNACARACTER) VALUES (V_N1, V_C1);
  INSERT INTO TEMPORAL (COLUMNANUMERICA, COLUMNACARACTER) VALUES (V_N2, V_C2);

  SELECT COLUMNACARACTER INTO V_SALECADENA1 FROM TEMPORAL WHERE COLUMNANUMERICA = V_N1;
  DBMS_OUTPUT.PUT_LINE(V_SALECADENA1);

  SELECT COLUMNACARACTER INTO V_SALECADENA2 FROM TEMPORAL WHERE COLUMNANUMERICA = V_N2;
  DBMS_OUTPUT.PUT_LINE(V_SALECADENA2);
END;
/

14. Disparador para Valores Positivos

Construir un disparador en la tabla TEMPORAL para contrastar que sólo se introducen valores positivos en la columna numérica.

CREATE OR REPLACE TRIGGER DISPARADOR1
BEFORE INSERT OR UPDATE OF COLUMNANUMERICA ON TEMPORAL
FOR EACH ROW
WHEN (NEW.COLUMNANUMERICA < 0)
BEGIN
  IF (:NEW.COLUMNANUMERICA < 0) THEN
    RAISE_APPLICATION_ERROR(-20100, 'SOLO NUMEROS POSITIVOS');
  END IF;
END DISPARADOR1;
/

9. Procedimiento para Visualizar Dos Empleados con Menor Salario por Oficio

Codifica un procedimiento que visualice los dos empleados que ganan menos de cada oficio.

CREATE OR REPLACE PROCEDURE emp_2minsal AS
  CURSOR c_emp IS
    SELECT apellido, oficio, salario FROM emple ORDER BY oficio, salario;
  vr_emp c_emp%ROWTYPE;
  oficio_ant EMPLE.OFICIO%TYPE;
  i NUMBER;
BEGIN
  OPEN c_emp;
  oficio_ant := '*';
  FETCH c_emp INTO vr_emp;
  WHILE c_emp%FOUND LOOP
    IF oficio_ant <> vr_emp.oficio THEN
      oficio_ant := vr_emp.oficio;
      i := 1;
    END IF;
    IF i <= 2 THEN
      DBMS_OUTPUT.PUT_LINE(vr_emp.oficio || ' * ' || vr_emp.apellido || ' * ' || vr_emp.salario);
    END IF;
    FETCH c_emp INTO vr_emp;
    i := i + 1;
  END LOOP;
  CLOSE c_emp;
END emp_2minsal;
/

13. Procedimiento para Visualizar Cinco Empleados con Mayor Salario

Escribir un procedimiento que visualice el apellido y el salario de los cinco empleados que tienen el salario más alto.

CREATE OR REPLACE PROCEDURE emp_5maxsal AS CURSOR c_emp IS SELECT apellido, salario FROM emple  ORDER BY salario DESC; vr_emp   c_emp%ROWTYPE; i NUMBER;   BEGIN  i:=1; OPEN c_emp;FETCH c_emp INTO vr_emp;WHILE c_emp%FOUND AND i