Módulo 06: Subprogramas: Procedimientos y Funciones

Objetivos del módulo

• Entender qué son los subprogramas y por qué son esenciales
• Crear procedimientos almacenados con CREATE PROCEDURE
• Crear funciones con CREATE FUNCTION
• Dominar los modos de parámetros: IN, OUT e IN OUT
• Invocar subprogramas desde bloques anónimos y desde otros subprogramas
• Eliminar y recompilar subprogramas
• Conocer los paquetes (packages) como forma de organizar código
• Aplicar buenas prácticas en el diseño de subprogramas

---

1. ¿Qué son los subprogramas?

Los subprogramas son bloques PL/SQL con nombre que se almacenan en la base de datos y se pueden reutilizar. Existen dos tipos:

Tipo	Descripción	Devuelve valor
Procedimiento	Ejecuta una acción (insertar, actualizar, calcular, etc.)	No (usa parámetros OUT si necesita devolver datos)
Función	Calcula y devuelve un único valor	Sí, con RETURN

📘 Concepto: La diferencia principal es que una función devuelve un valor y se puede usar dentro de expresiones SQL, mientras que un procedimiento realiza acciones y se invoca con EXECUTE o CALL. Si necesitas devolver un solo valor, usa una función. Si necesitas realizar operaciones complejas, usa un procedimiento.

Ventajas de los subprogramas

• Reutilización: escribe el código una vez, úsalo muchas veces
• Mantenimiento: si la lógica cambia, solo modificas un lugar
• Seguridad: puedes dar permiso de ejecución sin dar acceso directo a las tablas
• Rendimiento: se compilan y almacenan en la BD, listos para ejecutarse
• Modularidad: divide problemas complejos en piezas manejables

---

2. Procedimientos almacenados

2.1. Sintaxis de creación

CREATE [OR REPLACE] PROCEDURE nombre_procedimiento
    (parametro1 [IN | OUT | IN OUT] tipo,
     parametro2 [IN | OUT | IN OUT] tipo,
     ...)
IS | AS
    -- Declaraciones locales
BEGIN
    -- Código ejecutable
EXCEPTION
    -- Manejo de excepciones
END [nombre_procedimiento];
/

💡 Truco: Usa siempre CREATE OR REPLACE PROCEDURE en lugar de solo CREATE PROCEDURE. Así puedes modificar el procedimiento sin tener que borrarlo primero. Si no usas OR REPLACE y el procedimiento ya existe, Oracle dará un error.

2.2. Procedimiento sin parámetros

CREATE OR REPLACE PROCEDURE pr_listar_conductores
IS
BEGIN
    FOR r IN (SELECT nombre, fec_carnet FROM conductor ORDER BY nombre) LOOP
        DBMS_OUTPUT.PUT_LINE(r.nombre || ' - Carnet: ' || r.fec_carnet);
    END LOOP;
END pr_listar_conductores;
/

Ejecución:

SET SERVEROUTPUT ON;
-- Forma 1: con EXECUTE (abreviado EXEC)
EXEC pr_listar_conductores;

-- Forma 2: con bloque anónimo
BEGIN
    pr_listar_conductores;
END;
/

-- Forma 3: con CALL
CALL pr_listar_conductores();

2.3. Procedimiento con parámetros IN

Los parámetros IN son los valores de entrada (es el modo por defecto):

CREATE OR REPLACE PROCEDURE pr_buscar_conductor
    (p_nombre IN conductor.nombre%TYPE)
IS
    v_fec_nac conductor.fec_nacimiento%TYPE;
    v_fec_car conductor.fec_carnet%TYPE;
BEGIN
    SELECT fec_nacimiento, fec_carnet
    INTO v_fec_nac, v_fec_car
    FROM conductor
    WHERE UPPER(nombre) LIKE '%' || UPPER(p_nombre) || '%'
    AND ROWNUM = 1;
    
    DBMS_OUTPUT.PUT_LINE('Nacimiento: ' || v_fec_nac);
    DBMS_OUTPUT.PUT_LINE('Carnet:     ' || v_fec_car);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('No se encontró conductor con nombre: ' || p_nombre);
END pr_buscar_conductor;
/

Ejecución:

EXEC pr_buscar_conductor('García');

2.4. Procedimiento con parámetros OUT

Los parámetros OUT permiten devolver valores al programa que llama:

CREATE OR REPLACE PROCEDURE pr_estadisticas_multas
    (p_total    OUT NUMBER,
     p_promedio OUT NUMBER,
     p_maximo   OUT NUMBER)
IS
BEGIN
    SELECT COUNT(*), AVG(m.importe), MAX(m.importe)
    INTO p_total, p_promedio, p_maximo
    FROM multa m;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        p_total    := 0;
        p_promedio := 0;
        p_maximo   := 0;
END pr_estadisticas_multas;
/

Ejecución:

DECLARE
    v_total    NUMBER;
    v_promedio NUMBER;
    v_maximo   NUMBER;
BEGIN
    pr_estadisticas_multas(v_total, v_promedio, v_maximo);
    
    DBMS_OUTPUT.PUT_LINE('Total multas:    ' || v_total);
    DBMS_OUTPUT.PUT_LINE('Importe promedio: ' || ROUND(v_promedio, 2));
    DBMS_OUTPUT.PUT_LINE('Importe máximo:  ' || v_maximo);
END;
/

2.5. Procedimiento con parámetros IN OUT

Los parámetros IN OUT sirven tanto de entrada como de salida:

CREATE OR REPLACE PROCEDURE pr_formatear_nombre
    (p_nombre IN OUT VARCHAR2)
IS
BEGIN
    -- Eliminar espacios y capitalizar
    p_nombre := INITCAP(TRIM(p_nombre));
END pr_formatear_nombre;
/

Ejecución:

DECLARE
    v_nombre VARCHAR2(100) := '  juan GARCÍA  lópez  ';
BEGIN
    DBMS_OUTPUT.PUT_LINE('Antes:   [' || v_nombre || ']');
    pr_formatear_nombre(v_nombre);
    DBMS_OUTPUT.PUT_LINE('Después: [' || v_nombre || ']');
END;
/

📘 Concepto: Resumen de modos de parámetros:

Modo	¿Se puede leer?	¿Se puede modificar?	Valor por defecto
IN (default)	✅	❌	Sí
OUT	❌ (valor indefinido al entrar)	✅	No
IN OUT	✅	✅	No

2.6. Parámetros con valores por defecto

CREATE OR REPLACE PROCEDURE pr_informe_infracciones
    (p_vigente IN NUMBER DEFAULT 1,
     p_min_importe IN NUMBER DEFAULT 0)
IS
BEGIN
    DBMS_OUTPUT.PUT_LINE('=== Informe de Infracciones ===');
    DBMS_OUTPUT.PUT_LINE('Filtro: vigente=' || p_vigente || 
                         ', importe>=' || p_min_importe);
    DBMS_OUTPUT.PUT_LINE('');

    FOR r IN (SELECT descripcion, puntos, valor_economico
              FROM infraccion
              WHERE vigente = p_vigente
              AND valor_economico >= p_min_importe
              ORDER BY valor_economico DESC) LOOP
        DBMS_OUTPUT.PUT_LINE(RPAD(r.descripcion, 40) || 
                             LPAD(r.puntos || ' pts', 8) || 
                             LPAD(r.valor_economico || ' €', 10));
    END LOOP;
END pr_informe_infracciones;
/

Ejecución con y sin parámetros:

-- Usa valores por defecto (vigente=1, min_importe=0)
EXEC pr_informe_infracciones;

-- Solo cambia el importe mínimo (notación nombrada)
EXEC pr_informe_infracciones(p_min_importe => 200);

-- Cambia ambos
EXEC pr_informe_infracciones(0, 100);

💡 Truco: La notación nombrada (p_parametro => valor) permite pasar parámetros en cualquier orden y omitir los que tienen valor por defecto. Es muy recomendable cuando hay muchos parámetros.

---

3. Funciones

3.1. Sintaxis de creación

CREATE [OR REPLACE] FUNCTION nombre_funcion
    (parametro1 [IN] tipo,
     parametro2 [IN] tipo,
     ...)
RETURN tipo_retorno
IS | AS
    -- Declaraciones locales
BEGIN
    -- Código ejecutable
    RETURN valor;
EXCEPTION
    -- Manejo de excepciones
END [nombre_funcion];
/

3.2. Función básica

CREATE OR REPLACE FUNCTION fn_calcular_edad
    (p_fecha_nacimiento IN DATE)
RETURN NUMBER
IS
BEGIN
    RETURN TRUNC(MONTHS_BETWEEN(SYSDATE, p_fecha_nacimiento) / 12);
END fn_calcular_edad;
/

Uso:

-- Desde un bloque PL/SQL
DECLARE
    v_edad NUMBER;
BEGIN
    v_edad := fn_calcular_edad(TO_DATE('15/03/1990', 'DD/MM/YYYY'));
    DBMS_OUTPUT.PUT_LINE('Edad: ' || v_edad || ' años');
END;
/

-- Desde una consulta SQL
SELECT nombre, fec_nacimiento, fn_calcular_edad(fec_nacimiento) AS edad
FROM conductor;

3.3. Función que devuelve texto

CREATE OR REPLACE FUNCTION fn_clasificar_infraccion
    (p_valor_economico IN NUMBER)
RETURN VARCHAR2
IS
BEGIN
    RETURN CASE
        WHEN p_valor_economico < 100  THEN 'LEVE'
        WHEN p_valor_economico < 300  THEN 'GRAVE'
        WHEN p_valor_economico < 600  THEN 'MUY GRAVE'
        ELSE 'EXTREMA'
    END;
END fn_clasificar_infraccion;
/

Uso en SQL:

SELECT 
    descripcion, 
    valor_economico, 
    fn_clasificar_infraccion(valor_economico) AS clasificacion
FROM infraccion
WHERE vigente = 1
ORDER BY valor_economico DESC;

3.4. Función con cálculos complejos

CREATE OR REPLACE FUNCTION fn_calcular_iva
    (p_base    IN NUMBER,
     p_tipo_iva IN NUMBER DEFAULT 21)
RETURN NUMBER
IS
    v_total NUMBER(12,2);
BEGIN
    IF p_base IS NULL OR p_base < 0 THEN
        RAISE_APPLICATION_ERROR(-20001, 'La base imponible debe ser positiva');
    END IF;
    
    IF p_tipo_iva NOT IN (4, 10, 21) THEN
        RAISE_APPLICATION_ERROR(-20002, 'Tipo de IVA no válido. Use 4, 10 o 21');
    END IF;
    
    v_total := ROUND(p_base * (1 + p_tipo_iva / 100), 2);
    RETURN v_total;
END fn_calcular_iva;
/

Uso:

-- IVA general (21%)
SELECT fn_calcular_iva(1000) AS total_general FROM DUAL;
-- Resultado: 1210.00

-- IVA reducido (10%)
SELECT fn_calcular_iva(1000, 10) AS total_reducido FROM DUAL;
-- Resultado: 1100.00

⚠️ Importante: Una función usada dentro de una sentencia SQL no puede modificar datos (INSERT, UPDATE, DELETE) ni usar COMMIT o ROLLBACK. Si necesitas modificar datos, usa un procedimiento.

---

4. Eliminar y gestionar subprogramas

4.1. Eliminar subprogramas

DROP PROCEDURE pr_listar_conductores;
DROP FUNCTION fn_calcular_edad;

4.2. Recompilar manualmente

ALTER PROCEDURE pr_listar_conductores COMPILE;
ALTER FUNCTION fn_calcular_edad COMPILE;

4.3. Ver subprogramas en el diccionario de datos

-- Ver todos tus procedimientos y funciones
SELECT object_name, object_type, status, created, last_ddl_time
FROM user_objects
WHERE object_type IN ('PROCEDURE', 'FUNCTION')
ORDER BY object_type, object_name;

-- Ver el código fuente de un subprograma
SELECT line, text
FROM user_source
WHERE name = 'FN_CALCULAR_EDAD'
ORDER BY line;

-- Ver errores de compilación
SELECT line, position, text
FROM user_errors
WHERE name = 'FN_CALCULAR_EDAD'
ORDER BY line;

💡 Truco: Si un procedimiento o función tiene errores de compilación, Oracle lo marca con estado INVALID. Puedes ver los errores con SHOW ERRORS en SQL*Plus/SQLcl justo después de compilar, o consultando la vista USER_ERRORS.

---

5. Llamadas entre subprogramas

Los subprogramas pueden llamarse entre sí:

-- Función auxiliar
CREATE OR REPLACE FUNCTION fn_es_mayor_edad(p_fecha_nac IN DATE) 
RETURN BOOLEAN
IS
BEGIN
    RETURN MONTHS_BETWEEN(SYSDATE, p_fecha_nac) / 12 >= 18;
END fn_es_mayor_edad;
/

-- Procedimiento que usa la función
CREATE OR REPLACE PROCEDURE pr_verificar_conductor
    (p_nombre IN conductor.nombre%TYPE)
IS
    v_fec_nac conductor.fec_nacimiento%TYPE;
BEGIN
    SELECT fec_nacimiento INTO v_fec_nac
    FROM conductor
    WHERE UPPER(nombre) LIKE '%' || UPPER(p_nombre) || '%'
    AND ROWNUM = 1;
    
    IF fn_es_mayor_edad(v_fec_nac) THEN
        DBMS_OUTPUT.PUT_LINE(p_nombre || ' es mayor de edad');
    ELSE
        DBMS_OUTPUT.PUT_LINE(p_nombre || ' es menor de edad');
    END IF;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Conductor no encontrado: ' || p_nombre);
END pr_verificar_conductor;
/

---

6. Introducción a los paquetes (Packages)

Un paquete agrupa procedimientos, funciones, tipos, variables y cursores relacionados en una unidad lógica. Consta de dos partes:

6.1. Especificación (cabecera pública)

Define qué ofrece el paquete (la interfaz pública):

CREATE OR REPLACE PACKAGE pkg_infracciones
IS
    -- Constantes públicas
    c_iva CONSTANT NUMBER := 21;
    
    -- Procedimientos públicos
    PROCEDURE listar_vigentes;
    PROCEDURE listar_por_importe(p_min NUMBER DEFAULT 0);
    
    -- Funciones públicas
    FUNCTION obtener_total_valor RETURN NUMBER;
    FUNCTION clasificar(p_importe NUMBER) RETURN VARCHAR2;
END pkg_infracciones;
/

6.2. Cuerpo (implementación)

Define cómo funcionan los elementos declarados en la especificación:

CREATE OR REPLACE PACKAGE BODY pkg_infracciones
IS
    -- Función privada (solo visible dentro del paquete)
    FUNCTION formatear_importe(p_importe NUMBER) RETURN VARCHAR2
    IS
    BEGIN
        RETURN TO_CHAR(p_importe, '9G999D00') || ' €';
    END formatear_importe;

    -- Implementación de procedimientos públicos
    PROCEDURE listar_vigentes
    IS
    BEGIN
        FOR r IN (SELECT descripcion, valor_economico FROM infraccion WHERE vigente = 1) LOOP
            DBMS_OUTPUT.PUT_LINE(r.descripcion || ': ' || formatear_importe(r.valor_economico));
        END LOOP;
    END listar_vigentes;
    
    PROCEDURE listar_por_importe(p_min NUMBER DEFAULT 0)
    IS
    BEGIN
        FOR r IN (SELECT descripcion, valor_economico 
                  FROM infraccion 
                  WHERE vigente = 1 AND valor_economico >= p_min
                  ORDER BY valor_economico DESC) LOOP
            DBMS_OUTPUT.PUT_LINE(r.descripcion || ': ' || formatear_importe(r.valor_economico));
        END LOOP;
    END listar_por_importe;
    
    -- Implementación de funciones públicas
    FUNCTION obtener_total_valor RETURN NUMBER
    IS
        v_total NUMBER;
    BEGIN
        SELECT SUM(valor_economico) INTO v_total
        FROM infraccion
        WHERE vigente = 1;
        RETURN NVL(v_total, 0);
    END obtener_total_valor;
    
    FUNCTION clasificar(p_importe NUMBER) RETURN VARCHAR2
    IS
    BEGIN
        RETURN CASE
            WHEN p_importe < 100  THEN 'LEVE'
            WHEN p_importe < 300  THEN 'GRAVE'
            WHEN p_importe >= 300 THEN 'MUY GRAVE'
            ELSE 'DESCONOCIDA'
        END;
    END clasificar;
    
END pkg_infracciones;
/

6.3. Uso del paquete

-- Llamar a procedimientos del paquete
EXEC pkg_infracciones.listar_vigentes;
EXEC pkg_infracciones.listar_por_importe(200);

-- Usar funciones del paquete
SELECT 
    descripcion, 
    valor_economico, 
    pkg_infracciones.clasificar(valor_economico) AS tipo
FROM infraccion;

-- Usar constantes del paquete
DECLARE
    v_total NUMBER;
BEGIN
    v_total := pkg_infracciones.obtener_total_valor;
    DBMS_OUTPUT.PUT_LINE('Total: ' || v_total);
    DBMS_OUTPUT.PUT_LINE('IVA aplicable: ' || pkg_infracciones.c_iva || '%');
END;
/

📘 Concepto: Los paquetes son la forma recomendada de organizar código PL/SQL en proyectos reales. Proporcionan encapsulación (elementos privados vs públicos), evitan conflictos de nombres y permiten sobrecarga de subprogramas (mismo nombre con diferentes parámetros).

---

7. Ejercicios prácticos

Ejercicio 1: Procedimiento básico

Crea un procedimiento que muestre los vehículos de una marca determinada:

CREATE OR REPLACE PROCEDURE pr_vehiculos_por_marca
    (p_marca IN vehiculo.marca%TYPE)
IS
    v_encontrados NUMBER := 0;
BEGIN
    DBMS_OUTPUT.PUT_LINE('--- Vehículos marca: ' || UPPER(p_marca) || ' ---');
    
    FOR r IN (SELECT matricula, marca, color 
              FROM vehiculo 
              WHERE UPPER(marca) = UPPER(p_marca)) LOOP
        v_encontrados := v_encontrados + 1;
        DBMS_OUTPUT.PUT_LINE(r.matricula || ' | ' || r.marca || ' | ' || 
                             NVL(r.color, 'Sin color'));
    END LOOP;
    
    DBMS_OUTPUT.PUT_LINE('Total encontrados: ' || v_encontrados);
END pr_vehiculos_por_marca;
/

-- Prueba
EXEC pr_vehiculos_por_marca('Seat');

Ejercicio 2: Función de cálculo

Crea una función que devuelva los años de experiencia de un conductor (diferencia entre la fecha de carnet y hoy):

CREATE OR REPLACE FUNCTION fn_anios_experiencia
    (p_fec_carnet IN DATE)
RETURN NUMBER
IS
BEGIN
    RETURN TRUNC(MONTHS_BETWEEN(SYSDATE, p_fec_carnet) / 12);
END fn_anios_experiencia;
/

-- Uso en consulta
SELECT nombre, fec_carnet, fn_anios_experiencia(fec_carnet) AS experiencia
FROM conductor
ORDER BY experiencia DESC;

Ejercicio 3: Procedimiento con OUT

Crea un procedimiento que devuelva por parámetros OUT el número de conductores y la fecha del carnet más antiguo:

CREATE OR REPLACE PROCEDURE pr_resumen_conductores
    (p_total        OUT NUMBER,
     p_carnet_viejo OUT DATE)
IS
BEGIN
    SELECT COUNT(*), MIN(fec_carnet)
    INTO p_total, p_carnet_viejo
    FROM conductor;
END pr_resumen_conductores;
/

-- Prueba
DECLARE
    v_total NUMBER;
    v_fecha DATE;
BEGIN
    pr_resumen_conductores(v_total, v_fecha);
    DBMS_OUTPUT.PUT_LINE('Conductores: ' || v_total);
    DBMS_OUTPUT.PUT_LINE('Carnet más antiguo: ' || v_fecha);
END;
/

Ejercicio 4: Función booleana para validación

Crea una función que valide si una matrícula tiene formato correcto (4 números + 3 letras):

CREATE OR REPLACE FUNCTION fn_validar_matricula
    (p_matricula IN VARCHAR2)
RETURN BOOLEAN
IS
BEGIN
    RETURN REGEXP_LIKE(p_matricula, '^\d{4}[A-Z]{3}$');
END fn_validar_matricula;
/

-- Prueba
DECLARE
    v_mat VARCHAR2(10);
BEGIN
    v_mat := '1234ABC';
    IF fn_validar_matricula(v_mat) THEN
        DBMS_OUTPUT.PUT_LINE(v_mat || ' → Formato correcto');
    ELSE
        DBMS_OUTPUT.PUT_LINE(v_mat || ' → Formato incorrecto');
    END IF;
    
    v_mat := 'ABCD123';
    IF fn_validar_matricula(v_mat) THEN
        DBMS_OUTPUT.PUT_LINE(v_mat || ' → Formato correcto');
    ELSE
        DBMS_OUTPUT.PUT_LINE(v_mat || ' → Formato incorrecto');
    END IF;
END;
/

---

Resumen

Concepto	Detalle
Procedimiento	Subprograma que ejecuta una acción; no devuelve valor con RETURN
Función	Subprograma que calcula y devuelve un valor; se puede usar en SQL
CREATE OR REPLACE	Crea o reemplaza un subprograma existente
Parámetro IN	Solo lectura (entrada). Es el modo por defecto
Parámetro OUT	Solo escritura (salida). Devuelve un valor al llamador
Parámetro IN OUT	Lectura y escritura. Entra con un valor y sale modificado
Valores por defecto	DEFAULT permite omitir parámetros en la llamada
Notación nombrada	p_param => valor para pasar parámetros por nombre
RETURN	Devuelve el valor de una función y termina su ejecución
DROP PROCEDURE/FUNCTION	Elimina un subprograma de la BD
USER_OBJECTS / USER_SOURCE	Vistas del diccionario para consultar subprogramas
SHOW ERRORS	Muestra errores de compilación en SQL*Plus/SQLcl
Paquete (Package)	Agrupa subprogramas, tipos y variables en una unidad lógica
Especificación del paquete	Interfaz pública (lo que otros pueden usar)
Cuerpo del paquete	Implementación (puede incluir elementos privados)