El Proyecto | Sobre nosotros | Contribuir | Donaciones | Licencia

HOME

En este módulo, vamos a aplicar los conocimientos adquiridos en los módulos anteriores para construir una aplicación simple utilizando PL/SQL. Este ejercicio te permitirá ver cómo se integran los diferentes componentes de PL/SQL en una aplicación funcional.

Objetivos del Módulo

Crear una base de datos simple.
Desarrollar procedimientos almacenados y funciones.
Implementar disparadores.
Manejar excepciones.
Optimizar y depurar el código.

Paso 1: Crear la Base de Datos

Primero, necesitamos crear una base de datos simple. Para este ejemplo, vamos a crear una base de datos de "Gestión de Empleados".

Estructura de la Base de Datos

La base de datos tendrá las siguientes tablas:

employees: Almacena información sobre los empleados.
departments: Almacena información sobre los departamentos.

Script de Creación de Tablas

CREATE TABLE departments (
    department_id NUMBER PRIMARY KEY,
    department_name VARCHAR2(50) NOT NULL
);

CREATE TABLE employees (
    employee_id NUMBER PRIMARY KEY,
    first_name VARCHAR2(50),
    last_name VARCHAR2(50),
    email VARCHAR2(100) UNIQUE NOT NULL,
    hire_date DATE NOT NULL,
    job_id VARCHAR2(10) NOT NULL,
    salary NUMBER(8, 2),
    department_id NUMBER,
    CONSTRAINT fk_department
        FOREIGN KEY (department_id)
        REFERENCES departments(department_id)
);

Insertar Datos de Prueba

INSERT INTO departments (department_id, department_name) VALUES (1, 'HR');
INSERT INTO departments (department_id, department_name) VALUES (2, 'IT');
INSERT INTO departments (department_id, department_name) VALUES (3, 'Finance');

INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, job_id, salary, department_id) 
VALUES (1, 'John', 'Doe', '[email protected]', SYSDATE, 'HR_MAN', 5000, 1);
INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, job_id, salary, department_id) 
VALUES (2, 'Jane', 'Smith', '[email protected]', SYSDATE, 'IT_PROG', 6000, 2);

Paso 2: Desarrollar Procedimientos Almacenados y Funciones

Procedimiento para Agregar un Nuevo Empleado

CREATE OR REPLACE PROCEDURE add_employee (
    p_first_name IN employees.first_name%TYPE,
    p_last_name IN employees.last_name%TYPE,
    p_email IN employees.email%TYPE,
    p_hire_date IN employees.hire_date%TYPE,
    p_job_id IN employees.job_id%TYPE,
    p_salary IN employees.salary%TYPE,
    p_department_id IN employees.department_id%TYPE
) IS
BEGIN
    INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, job_id, salary, department_id)
    VALUES (employees_seq.NEXTVAL, p_first_name, p_last_name, p_email, p_hire_date, p_job_id, p_salary, p_department_id);
    COMMIT;
END;
/

Función para Obtener el Salario de un Empleado

CREATE OR REPLACE FUNCTION get_employee_salary (
    p_employee_id IN employees.employee_id%TYPE
) RETURN NUMBER IS
    v_salary employees.salary%TYPE;
BEGIN
    SELECT salary INTO v_salary
    FROM employees
    WHERE employee_id = p_employee_id;
    RETURN v_salary;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN NULL;
END;
/

Paso 3: Implementar Disparadores

Disparador para Auditar Cambios en la Tabla de Empleados

CREATE OR REPLACE TRIGGER trg_audit_employee_changes
AFTER INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW
BEGIN
    IF INSERTING THEN
        INSERT INTO audit_log (log_date, user_id, operation, table_name, row_id)
        VALUES (SYSDATE, USER, 'INSERT', 'employees', :NEW.employee_id);
    ELSIF UPDATING THEN
        INSERT INTO audit_log (log_date, user_id, operation, table_name, row_id)
        VALUES (SYSDATE, USER, 'UPDATE', 'employees', :OLD.employee_id);
    ELSIF DELETING THEN
        INSERT INTO audit_log (log_date, user_id, operation, table_name, row_id)
        VALUES (SYSDATE, USER, 'DELETE', 'employees', :OLD.employee_id);
    END IF;
END;
/

Paso 4: Manejar Excepciones

Procedimiento con Manejo de Excepciones

CREATE OR REPLACE PROCEDURE update_employee_salary (
    p_employee_id IN employees.employee_id%TYPE,
    p_new_salary IN employees.salary%TYPE
) IS
BEGIN
    UPDATE employees
    SET salary = p_new_salary
    WHERE employee_id = p_employee_id;
    COMMIT;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Employee not found.');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
END;
/

Paso 5: Optimizar y Depurar el Código

Optimización

Utiliza índices en las columnas que se usan frecuentemente en las cláusulas WHERE.
Evita el uso de cursores explícitos cuando sea posible.

Depuración

Utiliza DBMS_OUTPUT.PUT_LINE para imprimir mensajes de depuración.
Revisa los planes de ejecución para identificar cuellos de botella.

Ejercicio Práctico

Ejercicio

Crea un procedimiento almacenado que elimine un empleado por su ID.
Crea una función que devuelva el nombre completo de un empleado dado su ID.
Implementa un disparador que registre en una tabla de auditoría cada vez que se actualice el salario de un empleado.

Soluciones

Procedimiento para Eliminar un Empleado

CREATE OR REPLACE PROCEDURE delete_employee (
    p_employee_id IN employees.employee_id%TYPE
) IS
BEGIN
    DELETE FROM employees
    WHERE employee_id = p_employee_id;
    COMMIT;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Employee not found.');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
END;
/

Función para Obtener el Nombre Completo

CREATE OR REPLACE FUNCTION get_employee_full_name (
    p_employee_id IN employees.employee_id%TYPE
) RETURN VARCHAR2 IS
    v_full_name VARCHAR2(100);
BEGIN
    SELECT first_name || ' ' || last_name INTO v_full_name
    FROM employees
    WHERE employee_id = p_employee_id;
    RETURN v_full_name;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN NULL;
END;
/

Disparador para Auditar Cambios en el Salario

CREATE OR REPLACE TRIGGER trg_audit_salary_changes
AFTER UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (log_date, user_id, operation, table_name, row_id, old_value, new_value)
    VALUES (SYSDATE, USER, 'UPDATE', 'employees', :OLD.employee_id, :OLD.salary, :NEW.salary);
END;
/

Conclusión

En este módulo, hemos construido una aplicación simple utilizando PL/SQL. Hemos cubierto la creación de tablas, procedimientos almacenados, funciones, disparadores y el manejo de excepciones. Además, hemos discutido técnicas de optimización y depuración. Este ejercicio te proporciona una base sólida para desarrollar aplicaciones más complejas en PL/SQL. ¡Sigue practicando y explorando más características avanzadas de PL/SQL!

Construyendo una Aplicación Simple