Programación Avanzada en PL/SQL para Oracle: Ejercicios y Conceptos Esenciales

Introducción a PL/SQL y Gestión de Datos

Este documento presenta una serie de ejercicios y preguntas teóricas enfocadas en la programación con PL/SQL y la gestión de bases de datos Oracle. Abarca desde la creación de funciones y procedimientos hasta el manejo de excepciones y la implementación de triggers.

Ejercicio de Comparación de Precios

Si el precio del producto en la tabla coincide con el precio proporcionado, se retorna el mensaje "PRECIO EXACTO".
Si es mayor, debe retornar "PRECIO SUPERIOR".
Y si es menor, "PRECIO INFERIOR".
Se debe controlar que el producto exista en la tabla y que el precio proporcionado no sea negativo, retornando mensajes de error adecuados.

Implementación de la Función tipo_producto

CREATE OR REPLACE FUNCTION tipo_producto(
    p_numero_producto NUMBER,
    p_precio NUMBER
) RETURN VARCHAR2
IS
    v_precio_tabla NUMBER;
    v_mensaje VARCHAR2(50);
BEGIN
    -- Buscar el precio del producto en la tabla
    SELECT precio_actual INTO v_precio_tabla
    FROM productos
    WHERE producto_no = p_numero_producto;

    -- Validar que el precio no sea negativo
    IF p_precio < 0 THEN
        RAISE_APPLICATION_ERROR(-20001, 'El precio no puede ser negativo');
    END IF;

    -- Comparar precios y devolver mensaje apropiado
    IF p_precio = v_precio_tabla THEN
        v_mensaje := 'PRECIO EXACTO';
    ELSIF p_precio > v_precio_tabla THEN
        v_mensaje := 'PRECIO SUPERIOR';
    ELSE
        v_mensaje := 'PRECIO INFERIOR';
    END IF;

    RETURN v_mensaje;

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RAISE_APPLICATION_ERROR(-20002, 'Producto no encontrado');
    WHEN OTHERS THEN
        RAISE;
END tipo_producto;
/

Preguntas Teóricas sobre PL/SQL y Bases de Datos

1. Cuando se produce una excepción en un programa PL/SQL:

• C) Salta a la sección EXCEPTION donde buscará un manejador WHEN para la excepción o uno genérico.

2. Los bloques anónimos:

• D) Son bloques de código sin nombre que se ejecutan en el servidor Oracle.

3. Los disparadores de base de datos:

• C) Son programas.

Ejercicios Prácticos de PL/SQL

2a) Localización de Errores en el Código

A continuación, se presenta una lista de errores identificados en un fragmento de código PL/SQL (no incluido en este documento):

ERRORES IDENTIFICADOS:

• Línea 1: Falta la palabra clave "PROCEDURE" y los paréntesis correctos.
• Línea 6: Falta punto y coma después de PPRECIO(1).
• Línea 7: Sintaxis incorrecta: debe ser INTO en lugar de IN.
• Línea 8: Sintaxis incorrecta del bucle FOR: debe ser IN en lugar de EN, y falta LOOP.
• Línea 11: Sintaxis incorrecta de asignación: debe ser := en lugar de =.
• Línea 12: Condición WHILE infinita (I<=I siempre verdadero).
• Línea 15: Sintaxis incorrecta de asignación.
• Línea 16: Debe ser END LOOP en lugar de END WHILE.
• Líneas 10 y 14: El campo ID no existe en la tabla PRODUCTOS.

Procedimiento actualiza_precio_stock

CREATE OR REPLACE PROCEDURE actualiza_precio_stock(
    p_precio NUMBER(7,2),
    p_stock NUMBER(5)
)
IS
    i NUMBER(3) DEFAULT 30;
    v_numero NUMBER(5);
BEGIN
    DELETE FROM productos WHERE precio_actual > p_precio;

    SELECT COUNT(*) INTO v_numero 
    FROM productos;

    FOR i IN 1..v_numero LOOP
        UPDATE productos SET
        stock_disponible = stock_disponible - p_stock 
        WHERE producto_no = (SELECT producto_no FROM productos WHERE rownum = i);
    END LOOP;

    i := 1;
    WHILE i <= v_numero LOOP
        UPDATE productos SET
        stock_disponible = stock_disponible - p_stock 
        WHERE producto_no = (SELECT producto_no FROM productos WHERE rownum = i);
        i := i + 1;
    END LOOP;
END actualiza_precio_stock;
/

2b) Resultado de la Ejecución

Si ejecutamos: EXEC actualiza_precio_stock(450,100);

Resultado esperado: El procedimiento eliminará todos los productos con precio superior a 450 (productos 10, 20, 50, 70, 80), y luego reducirá el stock de los productos restantes en 200 unidades (100 por cada bucle).

5a) Añadir Atributo a la Tabla DEPART

Escriba una sentencia SQL que añada un atributo num_emple a la tabla DEPART:

ALTER TABLE depart 
ADD num_emple NUMBER DEFAULT 0;

5b) Bloque Anónimo para Asignar Número de Empleados

Posteriormente, escriba un bloque anónimo que asigne a ese atributo el número de empleados correspondiente a cada departamento:

DECLARE
    CURSOR c_departamentos IS
        SELECT dept_no FROM depart;

    v_contador NUMBER;
BEGIN
    FOR dept IN c_departamentos LOOP
        SELECT COUNT(*) INTO v_contador
        FROM emple
        WHERE dept_no = dept.dept_no;

        UPDATE depart 
        SET num_emple = v_contador
        WHERE dept_no = dept.dept_no;
    END LOOP;

    COMMIT;

    DBMS_OUTPUT.PUT_LINE('Actualización completada');
END;
/

5c) Trigger de Integridad para num_emple

Escriba un trigger de integridad para modificar el atributo num_emple del apartado anterior cada vez que se añada o elimine un empleado de un departamento:

CREATE OR REPLACE TRIGGER integridad_num_emple
    AFTER INSERT OR DELETE ON emple
    FOR EACH ROW
BEGIN
    IF INSERTING THEN
        UPDATE depart 
        SET num_emple = num_emple + 1
        WHERE dept_no = :NEW.dept_no;

        DBMS_OUTPUT.PUT_LINE('Empleado añadido al departamento ' || :NEW.dept_no);
    END IF;

    IF DELETING THEN
        UPDATE depart 
        SET num_emple = num_emple - 1
        WHERE dept_no = :OLD.dept_no;

        DBMS_OUTPUT.PUT_LINE('Empleado eliminado del departamento ' || :OLD.dept_no);
    END IF;
END integridad_num_emple;
/

5d) Trigger de Auditoría para Subida de Salario

El siguiente código crea el trigger audit_subida_salario que se disparará después de cada modificación de la columna salario de la tabla EMPLE, insertando en la tabla auditaremple el código del empleado al que se le ha subido el salario:

CREATE OR REPLACE TRIGGER audit_subida_salario
    AFTER UPDATE OF salario ON emple
    FOR EACH ROW
WHEN (NEW.salario > OLD.salario)  -- Solo cuando hay subida
BEGIN
    INSERT INTO auditaremple 
    VALUES ('SUBIDA SALARIO EMPLEADO ' || :NEW.emp_no || 
            ' DE ' || :OLD.salario || ' A ' || :NEW.salario);
END audit_subida_salario;
/

5e) Trigger de Auditoría para Borrado de Empleado

El siguiente ejemplo crea el trigger audit_borrado_emple que se disparará cada vez que se borre un empleado, guardando su número de empleado, apellido y departamento en una fila de la tabla auditaremple:

CREATE OR REPLACE TRIGGER audit_borrado_emple
    BEFORE DELETE ON emple
    FOR EACH ROW
BEGIN
    INSERT INTO auditaremple
    VALUES ('BORRADO EMPLEADO ' || :OLD.emp_no || ' ' || 
            :OLD.apellido || ' Dpto.' || :OLD.dept_no);
END audit_borrado_emple;
/

Más Preguntas Teóricas sobre PL/SQL

4. Los procedimientos y funciones en PL/SQL:

• D) Todas son correctas.

5. La declaración v_importe VARCHAR2 es:

• D) Incorrecta porque hay que especificar la longitud máxima.

6. La declaración v_estado CHAR es:

• D) Incorrecta porque hay que especificar la longitud máxima.

7. %ROWTYPE se usa para definir una variable...

• B) Del mismo tipo que una fila de la base de datos.

8. Los parámetros formales de tipo IN se comportan dentro del subprograma como:

• C) Constantes.

9. Los parámetros pueden ser:

• C) a) y b) son correctas.

10. Los procedimientos de PL/SQL:

• A) Pueden retornar valores a través de parámetros de salida.

11. La declaración v_true_false BOOLEAN es:

• A) Correcta.

12. Indique cuál de las siguientes afirmaciones es falsa:

• C) Siendo c un cursor y y una variable del mismo tipo que c, la orden: FETCH y INTO c es correcta.

14. Indique cuál de las siguientes afirmaciones es correcta:

• C) Cuando una sentencia SELECT...INTO no afecta a ninguna fila, se levanta la excepción NO_DATA_FOUND.

15. Indique cuál de las afirmaciones es cierta:

• A) :NEW se utiliza en triggers que actualizan o borran tuplas.

16. %TYPE se usa para definir una variable...

• A) Del mismo tipo que una columna de la base de datos.

17. Cuando necesitemos una estructura alternativa múltiple para evaluar el valor de una única expresión, optaremos por:

• B) CASE <expression> WHEN ... THEN ...

18. En los bloques PL/SQL:

• B) La sección ejecutable es obligatoria.

19. Un identificador:

• D) Las respuestas b) y c) son correctas.

20. Si después de ejecutar un programa que visualiza datos mediante DBMS_OUTPUT.PUT_LINE los datos no aparecen en la terminal:

• C) Usaremos SET SERVEROUTPUT ON y volveremos a ejecutar el programa.

21. En PL/SQL, para asignar un valor a una variable se hará:

• D) variable := valor

22. Respecto al uso de triggers, indique la afirmación falsa:

• C) Hay que llamarlos como a los procedimientos.

23. Para ejecutar una función calculadora hay que hacer:

• D) b) y c) son correctas.

24. Respecto a las excepciones de usuario, elija la respuesta correcta:

• B) Se levantan con RAISE miexcepcion;

25. La palabra RETURN en una función...

• D) Las dos primeras son correctas.

26. DBMS_OUTPUT.PUT_LINE(v_num) escribirá:

• A) 6

27. Si cambiamos el SELECT COUNT(*) INTO v_num FROM productos WHERE Producto_no=30; escribirá:

• B) 1

28. Si cambiamos DBMS_OUTPUT.PUT_LINE(v_num) por RETURN(v_num); en el ejercicio original, devolverá:

• D) Fallará.

29. Añadimos una variable numérica v_precio y hacemos SELECT Precio_actual INTO v_precio FROM productos WHERE Producto_no=70;

• C) Se produce la excepción NO_DATA_FOUND.

30. Añadimos una variable v_nombre productos.Nombre%TYPE y hacemos SELECT Nombre INTO v_nombre FROM productos WHERE Nombre LIKE 'Producto_%';

• D) Se produce la excepción TOO_MANY_ROWS.

Ejercicios Adicionales de PL/SQL

3. Procedimiento para Insertar Nuevos Departamentos

Desarrolle un procedimiento que permita insertar nuevos departamentos según las siguientes especificaciones:
Se pasará al procedimiento el nombre del departamento y la localidad (se pasará un valor por defecto 'MURCIA').
El procedimiento insertará la nueva fila asignando como número de departamento la decena siguiente al número mayor existente en la tabla.

CREATE OR REPLACE PROCEDURE insertar_departamento(
    p_nombre_depto VARCHAR2,
    p_localidad VARCHAR2 DEFAULT 'MURCIA'
)
IS
    v_nuevo_numero NUMBER(2);
BEGIN
    -- Obtener el siguiente número de departamento (decena siguiente)
    SELECT NVL(MAX(dept_no), 0) + 10 INTO v_nuevo_numero
    FROM depart;

    -- Insertar el nuevo departamento
    INSERT INTO depart (dept_no, dnombre, loc)
    VALUES (v_nuevo_numero, p_nombre_depto, p_localidad);

    COMMIT;

    DBMS_OUTPUT.PUT_LINE('Departamento insertado con número: ' || v_nuevo_numero);
    DBMS_OUTPUT.PUT_LINE('Nombre: ' || p_nombre_depto);
    DBMS_OUTPUT.PUT_LINE('Localidad: ' || p_localidad);

EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR(-20003, 'Error al insertar departamento: ' || SQLERRM);
END insertar_departamento;
/

4. Procedimiento para Encontrar Empleado con Cursor

Escriba un procedimiento encuentra que reciba un código de empleado y muestre si ese empleado está o no en la tabla EMPLE. En caso de estar en la tabla, debe indicar qué posición ocupa; en caso contrario, mostrará el mensaje: "El empleado no está en la lista". Utilice un cursor.

CREATE OR REPLACE PROCEDURE encuentra(p_emp_no NUMBER)
IS
    CURSOR c_empleado IS
        SELECT emp_no, apellido, oficio, 
               ROW_NUMBER() OVER (ORDER BY emp_no) as posicion
        FROM emple
        ORDER BY emp_no;

    v_empleado c_empleado%ROWTYPE;
    v_encontrado BOOLEAN := FALSE;
    v_posicion_actual NUMBER := 0;
BEGIN
    OPEN c_empleado;

    LOOP
        FETCH c_empleado INTO v_empleado;
        EXIT WHEN c_empleado%NOTFOUND;

        v_posicion_actual := v_posicion_actual + 1;

        IF v_empleado.emp_no = p_emp_no THEN
            v_encontrado := TRUE;
            DBMS_OUTPUT.PUT_LINE('El empleado está en la tabla:');
            DBMS_OUTPUT.PUT_LINE('Número: ' || v_empleado.emp_no);
            DBMS_OUTPUT.PUT_LINE('Apellido: ' || v_empleado.apellido);
            DBMS_OUTPUT.PUT_LINE('Oficio: ' || v_empleado.oficio);
            DBMS_OUTPUT.PUT_LINE('Posición en la tabla: ' || v_posicion_actual);
            EXIT;
        END IF;
    END LOOP;

    IF NOT v_encontrado THEN
        DBMS_OUTPUT.PUT_LINE('El empleado no está en la lista');
    END IF;

    CLOSE c_empleado;

EXCEPTION
    WHEN OTHERS THEN
        IF c_empleado%ISOPEN THEN
            CLOSE c_empleado;
        END IF;
        RAISE_APPLICATION_ERROR(-20004, 'Error en procedimiento encuentra: ' || SQLERRM);
END encuentra;
/