Procedimientos almacenados y Packages en Oracle

PL/SQL (Procedural Language/SQL) es el lenguaje procedural de Oracle para escribir lógica de negocio dentro de la base de datos: procedimientos, funciones, cursores, manejo de excepciones y packages. En este post vamos a repasar, de lo básico a lo completo:

Bloques PL/SQL y variables
Procedimientos almacenados (parámetros IN, OUT, IN OUT)
Funciones almacenadas
Cursores explícitos
Manejo de excepciones
Packages (especificación y cuerpo)
Buenas prácticas

1. El bloque PL/SQL

Todo código PL/SQL se organiza en bloques con esta estructura:

1
2
3
4
5
6
7
DECLARE
    -- declaración de variables, constantes, cursores
BEGIN
    -- lógica del programa
EXCEPTION
    -- manejo de errores
END;

Un bloque anónimo se ejecuta una sola vez y no se guarda en la base de datos:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
DECLARE
    v_nombre  VARCHAR2(100);
BEGIN
    SELECT nombre
    INTO v_nombre
    FROM clientes
    WHERE id = 1;

    DBMS_OUTPUT.PUT_LINE('Cliente: ' || v_nombre);
END;
/

DBMS_OUTPUT.PUT_LINE imprime en consola (útil para debug en SQL*Plus/SQL Developer).
La barra / al final le indica al cliente SQL que ejecute el bloque completo.

Cuando esa lógica se quiere reutilizar, se guarda como procedimiento, función o dentro de un package.

2. Variables y tipos de datos

Además de los tipos escalares (VARCHAR2, NUMBER, DATE, BOOLEAN), PL/SQL ofrece dos atributos muy usados para evitar desincronizar tipos con la tabla:

%TYPE: toma el tipo de dato de una columna (o variable) existente.
%ROWTYPE: toma la estructura completa de una fila de una tabla o cursor.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
DECLARE
    v_id_cliente   clientes.id%TYPE;
    v_cliente      clientes%ROWTYPE;
BEGIN
    v_id_cliente := 1;

    SELECT *
    INTO v_cliente
    FROM clientes
    WHERE id = v_id_cliente;

    DBMS_OUTPUT.PUT_LINE(v_cliente.nombre);
END;
/

Usar %TYPE y %ROWTYPE evita que tu código se rompa si mañana cambia el tipo o el largo de una columna: la variable siempre queda sincronizada con la tabla.

3. Procedimientos almacenados

Un procedimiento es un bloque PL/SQL con nombre, guardado en la base de datos, que puede recibir parámetros pero no retorna un valor con RETURN (aunque sí puede devolver datos a través de parámetros OUT).

Sintaxis básica

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
CREATE OR REPLACE PROCEDURE nombre_procedimiento (
    parametro1 IN  tipo,
    parametro2 OUT tipo
)
IS
    -- declaraciones
BEGIN
    -- lógica
END nombre_procedimiento;
/

IN: el valor entra al procedimiento (por defecto, si no se indica nada es IN).
OUT: el procedimiento devuelve un valor a través de ese parámetro.
IN OUT: el parámetro entra con un valor y el procedimiento lo puede modificar.

Ejemplo con parámetro OUT

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
CREATE OR REPLACE PROCEDURE obtener_total_cliente (
    p_id_cliente IN  clientes.id%TYPE,
    p_total      OUT NUMBER
)
IS
BEGIN
    SELECT NVL(SUM(total), 0)
    INTO p_total
    FROM ordenes
    WHERE cliente_id = p_id_cliente;
END obtener_total_cliente;
/

Ejecutarlo:

1
2
3
4
5
6
7
DECLARE
    v_total NUMBER;
BEGIN
    obtener_total_cliente(p_id_cliente => 1, p_total => v_total);
    DBMS_OUTPUT.PUT_LINE('Total gastado: ' || v_total);
END;
/

Ejemplo con IN OUT

1
2
3
4
5
6
7
8
9
CREATE OR REPLACE PROCEDURE aplicar_descuento (
    p_total IN OUT NUMBER,
    p_porcentaje IN NUMBER
)
IS
BEGIN
    p_total := p_total - (p_total * p_porcentaje / 100);
END aplicar_descuento;
/

1
2
3
4
5
6
7
DECLARE
    v_total NUMBER := 200;
BEGIN
    aplicar_descuento(v_total, 10);
    DBMS_OUTPUT.PUT_LINE('Total con descuento: ' || v_total); -- 180
END;
/

Insertar/actualizar datos desde un procedimiento

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
CREATE OR REPLACE PROCEDURE registrar_orden (
    p_cliente_id IN ordenes.cliente_id%TYPE,
    p_total      IN ordenes.total%TYPE
)
IS
BEGIN
    INSERT INTO ordenes (id, cliente_id, fecha, total)
    VALUES (ordenes_seq.NEXTVAL, p_cliente_id, SYSDATE, p_total);

    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END registrar_orden;
/

El COMMIT dentro de un procedimiento es una decisión de diseño: en procesos más grandes, muchas veces conviene dejar el COMMIT a cargo de quien invoca el procedimiento, para no comprometer transacciones que abarcan varias operaciones.

4. Funciones almacenadas

Una función es igual a un procedimiento, pero siempre retorna un valor con RETURN y por lo general se usa dentro de expresiones SQL.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
CREATE OR REPLACE FUNCTION calcular_total_cliente (
    p_id_cliente IN clientes.id%TYPE
)
RETURN NUMBER
IS
    v_total NUMBER;
BEGIN
    SELECT NVL(SUM(total), 0)
    INTO v_total
    FROM ordenes
    WHERE cliente_id = p_id_cliente;

    RETURN v_total;
END calcular_total_cliente;
/

Se puede usar directo en una consulta:

1
2
3
4
SELECT
    c.nombre,
    calcular_total_cliente(c.id) AS total_gastado
FROM clientes AS c;

Regla práctica: si necesitas un valor único como resultado y quieres usarlo dentro de un SELECT, usa una función. Si necesitas ejecutar una acción (insertar, actualizar, encadenar varios pasos) o devolver varios valores, usa un procedimiento.

5. Cursores explícitos

Un cursor permite recorrer fila por fila el resultado de una consulta, cuando un simple SELECT INTO no alcanza (porque esperas más de una fila).

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
DECLARE
    CURSOR c_ordenes IS
        SELECT cliente_id, total
        FROM ordenes
        WHERE total > 100;

    v_cliente_id ordenes.cliente_id%TYPE;
    v_total      ordenes.total%TYPE;
BEGIN
    OPEN c_ordenes;
    LOOP
        FETCH c_ordenes INTO v_cliente_id, v_total;
        EXIT WHEN c_ordenes%NOTFOUND;

        DBMS_OUTPUT.PUT_LINE(v_cliente_id || ' - ' || v_total);
    END LOOP;
    CLOSE c_ordenes;
END;
/

%NOTFOUND: TRUE cuando el último FETCH no trajo fila.
%FOUND: lo contrario.
%ROWCOUNT: cantidad de filas leídas hasta el momento.

Cursor FOR loop (forma recomendada)

Cuando no necesitas control fino sobre OPEN/FETCH/CLOSE, el cursor FOR loop es más corto y Oracle se encarga de abrir y cerrar el cursor automáticamente:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
BEGIN
    FOR r_orden IN (
        SELECT cliente_id, total
        FROM ordenes
        WHERE total > 100
    )
    LOOP
        DBMS_OUTPUT.PUT_LINE(r_orden.cliente_id || ' - ' || r_orden.total);
    END LOOP;
END;
/

Cursor con parámetros

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
DECLARE
    CURSOR c_ordenes_cliente (p_cliente_id NUMBER) IS
        SELECT total
        FROM ordenes
        WHERE cliente_id = p_cliente_id;
BEGIN
    FOR r IN c_ordenes_cliente(1) LOOP
        DBMS_OUTPUT.PUT_LINE(r.total);
    END LOOP;
END;
/

6. Estructuras de control

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- IF / ELSIF / ELSE
IF v_total > 1000 THEN
    v_categoria := 'VIP';
ELSIF v_total > 100 THEN
    v_categoria := 'Regular';
ELSE
    v_categoria := 'Nuevo';
END IF;

-- LOOP básico
LOOP
    v_contador := v_contador + 1;
    EXIT WHEN v_contador > 10;
END LOOP;

-- FOR loop numérico
FOR i IN 1..10 LOOP
    DBMS_OUTPUT.PUT_LINE(i);
END LOOP;

-- WHILE
WHILE v_contador <= 10 LOOP
    v_contador := v_contador + 1;
END LOOP;

7. Manejo de excepciones

PL/SQL distingue entre excepciones predefinidas (NO_DATA_FOUND, TOO_MANY_ROWS, DUP_VAL_ON_INDEX, ZERO_DIVIDE, entre otras) y excepciones definidas por el usuario.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
DECLARE
    v_total NUMBER;
    e_total_negativo EXCEPTION;
BEGIN
    SELECT total
    INTO v_total
    FROM ordenes
    WHERE id = 999; -- id que no existe

    IF v_total < 0 THEN
        RAISE e_total_negativo;
    END IF;

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('No existe la orden.');
    WHEN e_total_negativo THEN
        DBMS_OUTPUT.PUT_LINE('El total no puede ser negativo.');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error inesperado: ' || SQLERRM);
        RAISE;
END;
/

WHEN OTHERS captura cualquier excepción no controlada explícitamente. Úsalo casi siempre junto a RAISE (para no “tragarte” el error) o registrando el error en una tabla de logs.
SQLERRM y SQLCODE dan el mensaje y código del error actual.
RAISE_APPLICATION_ERROR(-20001, 'mensaje') permite lanzar errores de aplicación personalizados (códigos entre -20000 y -20999) que llegan al cliente que invoca el procedimiento.

1
2
3
IF v_total < 0 THEN
    RAISE_APPLICATION_ERROR(-20001, 'El total no puede ser negativo');
END IF;

8. Packages: agrupando procedimientos y funciones

Un package agrupa procedimientos, funciones, variables, cursores y tipos relacionados en una sola unidad lógica. Se compone de dos partes:

Package specification (PACKAGE): la interfaz pública, lo que otros pueden ver y llamar.
Package body (PACKAGE BODY): la implementación, incluyendo elementos privados que no aparecen en la especificación.

Ventajas de usar packages

Encapsulamiento: puedes tener funciones/variables privadas en el body que no son visibles desde afuera.
Organización: agrupa lógica relacionada (todo lo de “clientes” en un mismo package) en vez de decenas de procedimientos sueltos.
Overloading: puedes tener varias versiones de un mismo procedimiento/función con distinta firma de parámetros.
Estado en memoria de sesión: variables declaradas en el package mantienen su valor durante toda la sesión, útil para caché o configuración.
Mejor rendimiento: Oracle carga el package completo en memoria la primera vez que se usa, y solo se recompila el body si cambia (sin invalidar a quienes dependen de la especificación).

Especificación del package

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
CREATE OR REPLACE PACKAGE pkg_clientes IS

    -- Constante pública
    c_categoria_vip CONSTANT VARCHAR2(10) := 'VIP';

    -- Función pública
    FUNCTION calcular_total_cliente (
        p_id_cliente IN clientes.id%TYPE
    ) RETURN NUMBER;

    -- Procedimiento público
    PROCEDURE registrar_orden (
        p_cliente_id IN ordenes.cliente_id%TYPE,
        p_total      IN ordenes.total%TYPE
    );

    -- Overload: dos procedimientos con el mismo nombre, distinta firma
    PROCEDURE actualizar_categoria (
        p_cliente_id IN clientes.id%TYPE
    );

    PROCEDURE actualizar_categoria (
        p_cliente_id IN clientes.id%TYPE,
        p_categoria  IN VARCHAR2
    );

END pkg_clientes;
/

Cuerpo del package

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
CREATE OR REPLACE PACKAGE BODY pkg_clientes IS

    -- Función PRIVADA: no está en la especificación, no se puede llamar desde afuera
    FUNCTION obtener_categoria_por_total (
        p_total IN NUMBER
    ) RETURN VARCHAR2
    IS
    BEGIN
        IF p_total > 1000 THEN
            RETURN c_categoria_vip;
        ELSIF p_total > 100 THEN
            RETURN 'Regular';
        ELSE
            RETURN 'Nuevo';
        END IF;
    END obtener_categoria_por_total;

    -- Implementación de la función pública
    FUNCTION calcular_total_cliente (
        p_id_cliente IN clientes.id%TYPE
    ) RETURN NUMBER
    IS
        v_total NUMBER;
    BEGIN
        SELECT NVL(SUM(total), 0)
        INTO v_total
        FROM ordenes
        WHERE cliente_id = p_id_cliente;

        RETURN v_total;
    END calcular_total_cliente;

    PROCEDURE registrar_orden (
        p_cliente_id IN ordenes.cliente_id%TYPE,
        p_total      IN ordenes.total%TYPE
    )
    IS
    BEGIN
        INSERT INTO ordenes (id, cliente_id, fecha, total)
        VALUES (ordenes_seq.NEXTVAL, p_cliente_id, SYSDATE, p_total);

        COMMIT;
    EXCEPTION
        WHEN OTHERS THEN
            ROLLBACK;
            RAISE;
    END registrar_orden;

    -- Overload 1: calcula la categoría automáticamente
    PROCEDURE actualizar_categoria (
        p_cliente_id IN clientes.id%TYPE
    )
    IS
        v_total    NUMBER;
        v_categoria VARCHAR2(10);
    BEGIN
        v_total := calcular_total_cliente(p_cliente_id);
        v_categoria := obtener_categoria_por_total(v_total);

        UPDATE clientes
        SET categoria = v_categoria
        WHERE id = p_cliente_id;

        COMMIT;
    END actualizar_categoria;

    -- Overload 2: la categoría se pasa manualmente
    PROCEDURE actualizar_categoria (
        p_cliente_id IN clientes.id%TYPE,
        p_categoria  IN VARCHAR2
    )
    IS
    BEGIN
        UPDATE clientes
        SET categoria = p_categoria
        WHERE id = p_cliente_id;

        COMMIT;
    END actualizar_categoria;

END pkg_clientes;
/

Usando el package

1
2
3
4
5
6
7
BEGIN
    pkg_clientes.registrar_orden(p_cliente_id => 1, p_total => 350);
    pkg_clientes.actualizar_categoria(p_cliente_id => 1);
END;
/

SELECT pkg_clientes.calcular_total_cliente(1) FROM dual;

obtener_categoria_por_total no se puede llamar desde fuera del package, por ejemplo pkg_clientes.obtener_categoria_por_total(500) fallaría, ya que no está declarada en la especificación.
Ambos actualizar_categoria conviven porque Oracle distingue el overload por el número y tipo de parámetros.

Inicialización del package

Un package puede tener un bloque de inicialización, que se ejecuta una sola vez por sesión, la primera vez que se referencia algo del package:

1
2
3
4
5
6
7
8
CREATE OR REPLACE PACKAGE BODY pkg_clientes IS

    -- ... funciones y procedimientos ...

BEGIN
    DBMS_OUTPUT.PUT_LINE('pkg_clientes inicializado para esta sesión');
END pkg_clientes;
/

Esto es útil para cargar valores de configuración en variables globales del package al inicio de la sesión.

9. Buenas prácticas al escribir PL/SQL

Agrupa en packages procedimientos/funciones relacionados, en vez de dejarlos sueltos en el esquema.
Usa %TYPE y %ROWTYPE para no desincronizar tipos de datos con las tablas.
Evita SELECT * dentro de procedimientos, declara explícitamente las columnas que necesitas.
Maneja siempre las excepciones: como mínimo un WHEN OTHERS con RAISE o log del error, nunca lo dejes “tragado” en silencio.
No mezcles COMMIT/ROLLBACK en procedimientos que son parte de una transacción más grande, deja esa responsabilidad a quien orquesta el proceso completo cuando sea posible.
Nombra los parámetros con prefijo (p_) y las variables locales con otro (v_), para diferenciarlos rápido en procedimientos largos.
Usa cursores FOR loop en vez de OPEN/FETCH/CLOSE manual cuando no necesites control fino, es menos propenso a errores (olvidar cerrar el cursor, loops infinitos).

Resumen

Objeto	Retorna valor	Se puede usar en SELECT	Encapsula lógica privada
Procedimiento	No (solo vía `OUT`/`IN OUT`)	No	No
Función	Sí (`RETURN`)	Sí	No
Package	Agrupa ambos	Sí (sus funciones)	Sí, en el body

Con procedimientos, funciones y packages bien organizados, la lógica de negocio queda centralizada en la base de datos, es reutilizable desde cualquier cliente (aplicación, reportes, otros procedimientos) y más fácil de mantener en el tiempo.