UD09 · Programación de la base de datos con PL/SQL

Resumen del tema

Hasta ahora, la lógica de nuestras tareas vivía fuera de la base de datos: en un guion .sql que lanzábamos a mano o en la aplicación que consulta Oracle. Eso funciona mientras la tarea sea una secuencia de sentencias. Deja de funcionar cuando hace falta decidir (¿tiene el alumno faltas suficientes para perder la evaluación continua?), repetir (recorrer todos los alumnos de un grupo), reaccionar a un error (¿qué hago si el módulo no existe?) o garantizar una regla que nadie pueda saltarse, entre ellas las que en la UD03 llamamos restricciones que el modelo lógico no puede expresar.

La solución es programar dentro del servidor. Oracle incorpora para ello el lenguaje PL/SQL (Procedural Language / SQL): SQL más variables, condiciones, bucles, cursores y excepciones, organizado en bloques. Con él se construyen los objetos que guarda la propia base de datos: funciones, procedimientos, paquetes y disparadores, y con el planificador DBMS_SCHEDULER se ejecutan solos a la hora que se indique.

La unidad avanza en cinco pasos: primero qué formas hay de automatizar y con qué herramientas se trabaja (§1 y §2); después el lenguaje (bloques, variables, control de flujo, funciones del gestor y cursores, §3 a §7); a continuación los subprogramas almacenados y el tratamiento de errores (§8 a §10); luego los disparadores, con la auditoría de notas, la integridad que las restricciones no alcanzan y el famoso error de la tabla mutante (§11); y por último las tareas programadas, los paquetes y la comparación con otros gestores (§12 a §14).

Todos los ejemplos se ejecutan sobre el esquema de referencia EDUGEST con los scripts del proyecto cargados (32 alumnos, 143 matrículas, 46 faltas) y usan la sintaxis de Oracle AI Database 26ai Free. Cada ejemplo indica la salida que debes obtener. Cuando algo es propio de un gestor concreto se señala con una etiqueta o una nota.

Objetivos de aprendizaje

Al terminar esta unidad serás capaz de:

• Distinguir una consulta, un guion, un bloque anónimo, una función, un procedimiento, un paquete, un disparador y una tarea programada, y elegir la forma de automatización adecuada para cada necesidad (RA5.a).
• Editar y ejecutar guiones con SQL Developer, SQLcl o SQL*Plus, usando variables de sustitución y la salida de DBMS_OUTPUT (RA5.b, RA5.c).
• Escribir bloques PL/SQL con variables, constantes y tipos %TYPE y %ROWTYPE, y distinguir las variables de sustitución del cliente de las variables PL/SQL del servidor.
• Aplicar estructuras de control de flujo (IF, CASE, LOOP, WHILE, FOR) y las funciones predefinidas del gestor (RA5.e, RA5.g).
• Recorrer conjuntos de filas con cursores implícitos, explícitos, de bucle FOR y con parámetros (RA5.i).
• Definir funciones y procedimientos de usuario con parámetros IN, OUT e IN OUT (RA5.f).
• Tratar errores con excepciones predefinidas y propias, PRAGMA EXCEPTION_INIT y RAISE_APPLICATION_ERROR (RA5.j).
• Implementar con disparadores la auditoría de cambios y las reglas de integridad que el modelo lógico no puede declarar, y resolver el error de tabla mutante con un disparador compuesto (RA5.h, RA4.h, RA6.h).
• Programar tareas periódicas con DBMS_SCHEDULER, agrupar subprogramas en paquetes y reconocer los equivalentes en MySQL/MariaDB, PostgreSQL y SQL Server (RA5.a, RA5.d, RA4.d).

Temporalización

La unidad ocupa 7 horas de aula (4 de teoría y 3 de práctica). Es la unidad con más código del curso: conviene escribirlo a mano y recompilarlo, no copiarlo y pegarlo. Las prácticas largas (la tabla mutante, las tareas programadas, el paquete de secretaría y el proyecto) se completan fuera del horario de aula.

Trabaja siempre conectado como EDUGEST y con la salida activada (SET SERVEROUTPUT ON). Todo el código de esta unidad se prueba con un método fijo:

1. Predice qué debe mostrar o devolver antes de ejecutarlo.
2. Ejecuta y compara con la salida que figura en los apuntes.
3. Si falla, lee el error de arriba abajo: el primer mensaje (PLS- en compilación, ORA- en ejecución) es la causa; los siguientes solo dicen dónde ocurrió.
4. Guarda cada subprograma en su propio fichero .sql terminado en /: así se recompila y se versiona.

1. Programar dentro del servidor

1.1 Qué problema resuelve

SQL es un lenguaje declarativo: dices qué quieres, no cómo obtenerlo. Es excelente para consultar y modificar conjuntos de filas, pero no tiene variables, ni condiciones, ni bucles, ni forma de reaccionar a un error. En cuanto una tarea necesita alguna de esas cosas, hay dos opciones: sacar los datos a una aplicación y programar allí la lógica, o programarla dentro de la base de datos.

Piensa en la matrícula de un alumno en EduGest. Antes de insertar la fila hay que comprobar que el alumno existe y tiene grupo, que el módulo es de su ciclo, que no está ya matriculado ese curso y que no ha agotado las cuatro convocatorias. Son cuatro consultas, tres decisiones y un INSERT, y esas reglas deben cumplirse siempre, venga la matrícula de la aplicación web, de un script de la secretaría o de una herramienta gráfica.

La UD03 ya identificó las reglas de negocio de EduGest que el modelo lógico no puede expresar. Esta unidad las implementa:

La regla R6 está en la tabla a propósito: no todo se resuelve con un disparador. Si una restricción declarativa (CHECK, UNIQUE, clave ajena) puede expresar la regla, es siempre preferible: es más rápida, más clara y el optimizador la conoce.

1.2 Formas de automatizar tareas

El criterio RA5.a pide identificar las formas de automatizar una tarea en un gestor. En Oracle son estas:

Fuera del servidor existen otras formas de automatizar, también válidas: el programador de tareas del sistema operativo (cron, Programador de tareas de Windows) lanzando SQLcl, o la propia aplicación. La diferencia es dónde vive la lógica: dentro, viaja con la base de datos y la protegen sus privilegios; fuera, depende de otro sistema.

1.3 Consulta, guion, bloque, función, procedimiento, paquete y disparador

Es la confusión más frecuente al empezar. Esta tabla las separa por sus propiedades:

Tres ideas para quedarse con la tabla:

1. Lo que se guarda en la base de datos se compila una sola vez y se ejecuta muchas. Un guion o un bloque anónimo se analizan cada vez que se envían.
2. Una función devuelve un valor y se puede usar en una consulta; un procedimiento hace algo. Si necesitas llamarla desde un SELECT, es una función.
3. Un disparador nadie lo llama: se ejecuta cuando ocurre su evento. Por eso hay que escribirlos con mucho cuidado.

1.4 Cliente o servidor: dónde poner la lógica

Cada vez que una aplicación lanza una sentencia SQL hay un viaje de ida y vuelta por la red. Si la matrícula de un alumno en cinco módulos se programa en la aplicación, son decenas de viajes; si se programa en un procedimiento almacenado, es una llamada.

 Lógica en la aplicación                       Lógica en el servidor
 ───────────────────────                       ──────────────────────
 App ──SELECT alumno──────────► Oracle         App ──pr_matricular(1, 2, '2026-27')──► Oracle
 App ◄─────────────filas──────  Oracle                                                  │ valida
 App ──SELECT módulo──────────► Oracle                                                  │ calcula
 App ◄─────────────filas──────  Oracle                                                  │ inserta
 App ──INSERT matricula───────► Oracle         App ◄──────────────────────OK / error─────┘
 App ◄──────────────filas─────  Oracle
 (3 viajes como mínimo)                        (1 viaje)

Regla práctica: pon en el servidor lo que protege la integridad de los datos y lo que es una operación atómica de negocio (matricular, promocionar, anular). Deja en la aplicación la presentación, los flujos de usuario y todo lo que no afecte a la corrección de los datos.

1.5 Qué es PL/SQL

PL/SQL es la extensión procedimental de SQL de Oracle. Un programa PL/SQL se envía al servidor y lo ejecuta el motor PL/SQL, que se encarga de las instrucciones procedimentales (asignaciones, condiciones, bucles) y entrega al motor SQL las sentencias SQL que encuentra (SELECT, INSERT, UPDATE…).

       Cliente (SQL Developer, SQLcl, aplicación)
                       │  bloque PL/SQL
                       ▼
        ┌─────────────────────────────────┐
        │          SERVIDOR ORACLE        │
        │   Motor PL/SQL  ──sentencias──► Motor SQL ──► Datos
        │   (variables, IF, bucles,      │  (SELECT, INSERT...)
        │    excepciones)  ◄──resultado──┘
        └─────────────────────────────────┘

Cada paso de un motor al otro (context switch) tiene un coste pequeño pero medible. Por eso, cuando una tarea se puede escribir como una sola sentencia SQL, esa es casi siempre la mejor versión (§7.7).

PL/SQL es propio de Oracle. Cada gestor tiene su lenguaje: PostgreSQL usa PL/pgSQL, MySQL y MariaDB usan el lenguaje de rutinas almacenadas SQL/PSM, SQL Server usa Transact-SQL (T-SQL). Los conceptos (variables, condiciones, bucles, cursores, excepciones, disparadores) son los mismos; la sintaxis cambia. El apartado 14 los compara.

2. Herramientas y ejecución de guiones

2.1 Herramientas para editar guiones

El criterio RA5.c pide identificar las herramientas disponibles. Un guion PL/SQL es texto: se puede escribir en cualquier editor, pero las herramientas especializadas aportan resaltado, ejecución directa y acceso al diccionario de datos.

En el contenedor Oracle AI Database 26ai Free el servicio al que se conecta el alumnado es FREEPDB1: sql edugest@localhost:1521/FREEPDB1.

2.2 Métodos de ejecución de guiones

El criterio RA5.b pide reconocer los métodos de ejecución de guiones. Un guion puede ejecutarse de varias maneras:

Un guion puede recibir parámetros posicionales (&1, &2…), que son sustituidos por el cliente antes de enviar nada al servidor:

-- informe_grupo.sql · Uso: @informe_grupo.sql 2DAW
SET VERIFY OFF
WHENEVER SQLERROR EXIT FAILURE ROLLBACK
SPOOL informe_&1..txt

SELECT nia, apellidos, nombre
FROM   alumno
WHERE  cod_grupo = '&1'
ORDER  BY apellidos;

SPOOL OFF

Al lanzar @informe_grupo.sql 2DAW se crea el fichero informe_2DAW.txt (el segundo punto de &1..txt es el literal; el primero termina el nombre de la variable) con:

5 filas

Las órdenes de un guion son de dos clases que conviene no confundir:

2.3 Cómo se termina cada cosa: ; y /

El cliente necesita saber dónde acaba cada sentencia para enviarla al servidor. La regla es simple pero es la causa de muchos errores de principiante:

BEGIN
    DBMS_OUTPUT.PUT_LINE('Hola');   -- este ; cierra la instrucción PL/SQL
END;                                 -- este ; cierra el bloque
/                                    -- esta / lo envía al servidor

Si olvidas la /, SQLcl se queda esperando sin ejecutar nada (a veces muestra un número de línea, 2 3 4...). Si la escribes de más, ejecutas otra vez el último bloque. Un CREATE OR REPLACE repetido es inofensivo; un bloque con un INSERT, no.

2.4 La salida del servidor: DBMS_OUTPUT

PL/SQL no tiene una instrucción PRINT. El paquete DBMS_OUTPUT escribe texto en un búfer del servidor, y es el cliente quien lo vuelca por pantalla cuando el bloque termina, si se le ha pedido:

SET SERVEROUTPUT ON

En SQL Developer, además de SET SERVEROUTPUT ON, debe estar abierto el panel Ver → Salida de DBMS y habilitada la conexión con el botón +.

DBMS_OUTPUT es una herramienta de depuración y de prácticas, no un mecanismo de producción: el texto solo existe mientras dura la ejecución, se muestra al final (no mientras el bloque trabaja) y un procedimiento que se ejecuta desde una aplicación o una tarea programada no tiene a nadie delante para leerlo. Para dejar constancia hay que escribir en una tabla de registro (práctica 9.7).

2.5 Errores de compilación

Un procedimiento, función o disparador se compila al crearlo. Si hay errores, Oracle lo crea igualmente pero en estado INVALID y avisa con Warning: Procedure created with compilation errors. Los errores de compilación empiezan por PLS-:

SHOW ERRORS
-- o, desde cualquier cliente:
SELECT line, position, text
FROM   user_errors
WHERE  name = 'FN_EDAD'
ORDER  BY sequence;

3. Estructura de un bloque PL/SQL

3.1 Las cuatro secciones

Todo programa PL/SQL se construye con bloques. Un bloque tiene hasta cuatro secciones, y solo BEGIN ... END es obligatoria:

DECLARE        -- 1. Declaraciones (opcional): variables, constantes, cursores, excepciones
    ...
BEGIN          -- 2. Instrucciones (obligatoria): el programa en sí
    ...
EXCEPTION      -- 3. Tratamiento de errores (opcional)
    ...
END;           -- 4. Fin del bloque

SET SERVEROUTPUT ON
DECLARE
    c_centro  CONSTANT VARCHAR2(20) := 'EduGest';      -- constante
    v_hoy     DATE := SYSDATE;                          -- variable inicializada
BEGIN
    DBMS_OUTPUT.PUT_LINE('Centro:  ' || c_centro);
    DBMS_OUTPUT.PUT_LINE('Usuario: ' || USER);
    DBMS_OUTPUT.PUT_LINE('Fecha:   ' || TO_CHAR(v_hoy, 'DD/MM/YYYY'));
END;
/

Centro:  EduGest
Usuario: EDUGEST
Fecha:   07/10/2026

PL/SQL procedure successfully completed.

La fecha es la de tu sistema. La última línea la escribe el cliente, no el bloque: confirma que el servidor lo ha ejecutado sin errores.

3.2 Qué se puede escribir dentro de un bloque

3.3 Bloques anidados y ámbito

Un bloque puede contener otros bloques en su sección ejecutable o en la de excepciones. Una variable es visible en el bloque donde se declara y en los bloques que contiene; si un bloque interior declara una con el mismo nombre, oculta a la exterior mientras dura.

DECLARE
    v_x  NUMBER := 1;                                    -- bloque exterior
BEGIN
    DECLARE
        v_x  NUMBER := 2;                                -- oculta a la exterior
        v_y  NUMBER := 10;
    BEGIN
        DBMS_OUTPUT.PUT_LINE('Interior: x=' || v_x || ' y=' || v_y);
    END;
    DBMS_OUTPUT.PUT_LINE('Exterior: x=' || v_x);
    -- DBMS_OUTPUT.PUT_LINE(v_y);   -- PLS-00201: v_y no existe fuera de su bloque
END;
/

Interior: x=2 y=10
Exterior: x=1

Los bloques anidados no sirven solo para organizar el código: son la herramienta para limitar el alcance de una excepción (§9.7).

3.4 Comentarios, mayúsculas y convenciones de nombres

PL/SQL no distingue mayúsculas de minúsculas en el código (begin es BEGIN), pero sí en los literales de texto ('EduGest' y 'EDUGEST' son distintos). Los comentarios son de línea (-- texto) o de varias líneas (/* texto */).

Un prefijo en cada nombre evita el error más común: que una variable se llame igual que una columna. Si en WHERE id_alumno = id_alumno ambos son la columna, la condición es siempre verdadera. EduGest usa estas convenciones:

4. Variables, constantes y tipos

4.1 Declaración

nombre  [CONSTANT]  tipo  [NOT NULL]  [:= valor_inicial | DEFAULT valor_inicial];

Dos reglas valen para todos: una variable sin valor inicial vale NULL, y una constante debe tener valor inicial.

4.2 Asignar valores: := y SELECT ... INTO

Una variable recibe valor de dos formas: con el operador := o leyendo de una consulta con INTO.

DECLARE
    v_nia     alumno.nia%TYPE;
    v_nombre  VARCHAR2(130);
BEGIN
    SELECT nia, apellidos || ', ' || nombre
    INTO   v_nia, v_nombre
    FROM   alumno
    WHERE  id_alumno = 20;

    DBMS_OUTPUT.PUT_LINE(v_nia || ' · ' || v_nombre);
END;
/

10450740 · Sala Brotons, Mateo

SELECT ... INTO exige que la consulta devuelva exactamente una fila:

El laboratorio del apartado 7.6 permite verlo paso a paso con los tres casos.

Un SELECT ... INTO con una función de agregado sin GROUP BY devuelve siempre una fila, aunque no haya datos: SELECT AVG(nota_final) INTO v_media FROM matricula WHERE id_alumno = 30; no lanza NO_DATA_FOUND; v_media queda a NULL. Es una diferencia importante entre «la consulta no encuentra nada» y «el agregado de nada».

4.3 %TYPE y %ROWTYPE: heredar el tipo de la tabla

Escribir v_nia CHAR(8) funciona hasta el día en que alguien amplía la columna. Los atributos %TYPE y %ROWTYPE hacen que la variable herede el tipo directamente del diccionario de datos:

DECLARE
    r_mod  modulo%ROWTYPE;                              -- un campo por columna de MODULO
BEGIN
    SELECT * INTO r_mod FROM modulo WHERE id_modulo = 2;

    DBMS_OUTPUT.PUT_LINE(r_mod.codigo || ' ' || r_mod.nombre ||
                         ' (' || r_mod.cod_ciclo || ', curso ' || r_mod.curso ||
                         ', ' || r_mod.horas || ' h)');
END;
/

0484 Bases de datos (DAM, curso 1, 160 h)

Se accede a cada campo con registro.columna. Ventajas de %ROWTYPE: el SELECT * no necesita una variable por columna, y si se añade una columna a MODULO el bloque sigue compilando. Su inconveniente: trae todas las columnas, aunque solo uses dos.

Cuando interesa un registro con campos elegidos, se define un tipo propio con RECORD:

DECLARE
    TYPE t_resumen IS RECORD (
        nia     alumno.nia%TYPE,
        nombre  VARCHAR2(130),
        media   NUMBER(4,2)
    );
    r  t_resumen;
BEGIN
    SELECT a.nia, a.apellidos || ', ' || a.nombre, ROUND(AVG(m.nota_final), 2)
    INTO   r
    FROM   alumno a
           JOIN matricula m ON m.id_alumno = a.id_alumno
    WHERE  a.id_alumno = 20
    GROUP  BY a.nia, a.apellidos, a.nombre;

    DBMS_OUTPUT.PUT_LINE(r.nia || ' · ' || r.nombre || ' · media ' || r.media);
END;
/

10450740 · Sala Brotons, Mateo · media 7.13

Los números se convierten a texto con el separador decimal de la sesión (NLS_NUMERIC_CHARACTERS). En el contenedor de prácticas es el punto, como en las salidas de estos apuntes; con una configuración regional española verías 7,13. Para controlarlo, usa TO_CHAR(x, '990D00').

4.4 Variables de sustitución frente a variables PL/SQL

Es la confusión más frecuente de la unidad. Observa este bloque:

SET VERIFY ON
DEFINE g_grupo = 2DAW
BEGIN
    DBMS_OUTPUT.PUT_LINE('Grupo: &g_grupo');
END;
/

old   2:     DBMS_OUTPUT.PUT_LINE('Grupo: &g_grupo');
new   2:     DBMS_OUTPUT.PUT_LINE('Grupo: 2DAW');
Grupo: 2DAW

Las dos líneas old y new las escribe el cliente (SQLcl, SQL*Plus, SQL Developer): ha sustituido &g_grupo por 2DAW en el texto del bloque y solo después se lo ha enviado a Oracle. El servidor jamás ha visto el &. Una variable de sustitución no es una variable: es un marcador de «buscar y reemplazar» del cliente.

Como &variable es texto pegado en el código, escribir &id_alumno en un guion que recibe datos de una persona es una vía de inyección de código. Sirve para prácticas con un guion que ejecutas tú; en una aplicación se usan siempre variables de enlace (:nombre) o parámetros de un procedimiento. Y al revés: si tus datos contienen un & ('Gómez & Pérez'), el cliente intentará sustituirlo y preguntará por una variable; el script de datos de EduGest empieza con SET DEFINE OFF por esa razón.

Las variables de enlace (bind variables) se declaran en el cliente con VARIABLE y se leen con :nombre. Sí las entiende el servidor, que las recibe como valores:

VARIABLE v_total NUMBER
BEGIN
    SELECT COUNT(*) INTO :v_total FROM alumno WHERE cod_grupo = '2DAW';
END;
/
PRINT v_total

   V_TOTAL
----------
         5

4.5 Variables del sistema y del contexto de la sesión

PL/SQL no tiene variables globales del sistema como tales: se obtienen con funciones. Estas son las más útiles:

Estas funciones son las que permiten registrar quién hizo un cambio y desde dónde: se usan en la auditoría (§11.2).

5. Estructuras de control de flujo

El criterio RA5.g pide utilizar estructuras de control. PL/SQL tiene las tres familias habituales: condicionales (IF, CASE), repetitivas (LOOP, WHILE, FOR) y de salto (EXIT, CONTINUE, GOTO).

5.1 IF, ELSIF y ELSE

IF condición1 THEN
    instrucciones;
[ELSIF condición2 THEN
    instrucciones;]
[ELSE
    instrucciones;]
END IF;

Se escribe ELSIF (sin la E de else if) y se cierra con END IF;. Las condiciones se evalúan en orden y se ejecuta la primera rama verdadera.

DECLARE
    v_nota   matricula.nota_final%TYPE := 6.5;
    v_texto  VARCHAR2(15);
BEGIN
    IF v_nota IS NULL THEN
        v_texto := 'NC';
    ELSIF v_nota < 5 THEN
        v_texto := 'Insuficiente';
    ELSIF v_nota < 6 THEN
        v_texto := 'Suficiente';
    ELSIF v_nota < 7 THEN
        v_texto := 'Bien';
    ELSIF v_nota < 9 THEN
        v_texto := 'Notable';
    ELSE
        v_texto := 'Sobresaliente';
    END IF;

    DBMS_OUTPUT.PUT_LINE(v_nota || ' -> ' || v_texto);
END;
/

6.5 -> Bien

Una condición con NULL no es verdadera ni falsa: es desconocida (lógica trivaluada, UD06), y IF solo ejecuta la rama THEN cuando la condición es verdadera. Con v_nota a NULL, IF v_nota >= 5 THEN ... ELSE ejecutaría el ELSE y marcaría como suspenso a quien no se ha presentado. Por eso en el ejemplo la primera pregunta es IS NULL. Y nunca se escribe v_nota = NULL: no es verdadera jamás.

5.2 CASE: sentencia y expresión

CASE es la alternativa más legible cuando hay muchas ramas. Existe en dos formas, y cada una en dos usos:

DECLARE
    v_turno  grupo.turno%TYPE;
BEGIN
    SELECT turno INTO v_turno FROM grupo WHERE cod_grupo = '2DAW';

    -- CASE simple como sentencia
    CASE v_turno
        WHEN 'M' THEN DBMS_OUTPUT.PUT_LINE('2DAW: turno de mañana');
        WHEN 'T' THEN DBMS_OUTPUT.PUT_LINE('2DAW: turno de tarde');
        ELSE          DBMS_OUTPUT.PUT_LINE('2DAW: turno desconocido');
    END CASE;

    -- CASE con condiciones como expresión
    DBMS_OUTPUT.PUT_LINE('Hora de entrada: ' ||
        CASE WHEN v_turno = 'M' THEN '08:00' ELSE '15:00' END);
END;
/

2DAW: turno de tarde
Hora de entrada: 15:00

Si ninguna rama de una sentencia CASE se cumple y no hay ELSE, Oracle lanza CASE_NOT_FOUND (ORA-06592). La expresión CASE sin ELSE devuelve NULL en silencio. Escribe siempre el ELSE.

5.3 Bucles

Un ejemplo con cada uno, sobre las convocatorias que le quedan a un alumno que va por la segunda:

DECLARE
    v_conv  PLS_INTEGER := 2;                      -- convocatoria actual
BEGIN
    -- LOOP básico: la salida está en medio
    LOOP
        EXIT WHEN v_conv > 4;
        DBMS_OUTPUT.PUT_LINE('Queda la convocatoria ' || v_conv);
        v_conv := v_conv + 1;
    END LOOP;

    -- WHILE: equivalente, con la condición al principio
    v_conv := 2;
    WHILE v_conv <= 4 LOOP
        DBMS_OUTPUT.PUT_LINE('(while) convocatoria ' || v_conv);
        v_conv := v_conv + 1;
    END LOOP;
END;
/

Queda la convocatoria 2
Queda la convocatoria 3
Queda la convocatoria 4
(while) convocatoria 2
(while) convocatoria 3
(while) convocatoria 4

El bucle FOR numérico declara él mismo su contador, que solo existe dentro del bucle y no se puede modificar:

DECLARE
    v_nombre  modulo.nombre%TYPE;
BEGIN
    FOR i IN 1..3 LOOP
        SELECT nombre INTO v_nombre FROM modulo WHERE id_modulo = i;
        DBMS_OUTPUT.PUT_LINE(i || '. ' || v_nombre);
    END LOOP;

    FOR i IN REVERSE 1..3 LOOP                       -- cuenta atrás
        DBMS_OUTPUT.PUT_LINE('Cuenta atrás: ' || i);
    END LOOP;
END;
/

1. Sistemas informáticos
2. Bases de datos
3. Programación
Cuenta atrás: 3
Cuenta atrás: 2
Cuenta atrás: 1

5.4 EXIT, CONTINUE, etiquetas y GOTO

BEGIN
    <<externo>>
    FOR g IN 1..3 LOOP
        FOR a IN 1..3 LOOP
            CONTINUE WHEN a = 2;                     -- se salta la vuelta a = 2
            EXIT externo WHEN g = 2;                 -- sale de los DOS bucles
            DBMS_OUTPUT.PUT_LINE('g=' || g || ' a=' || a);
        END LOOP;
    END LOOP externo;
END;
/

g=1 a=1
g=1 a=3

5.5 Errores habituales con los bucles

DECLARE
    r_mod  modulo%ROWTYPE;
BEGIN
    FOR i IN 1..5 LOOP
        SELECT * INTO r_mod FROM modulo WHERE id_modulo = i;
        DBMS_OUTPUT.PUT_LINE(
            CASE WHEN r_mod.horas > 150 THEN '* ' ELSE '  ' END ||
            r_mod.codigo || ' ' || r_mod.nombre || ' (' || r_mod.horas || ' h)');
    END LOOP;
END;
/

* 0483 Sistemas informáticos (160 h)
* 0484 Bases de datos (160 h)
* 0485 Programación (256 h)
  0487 Entornos de desarrollo (96 h)
  0373 Lenguajes de marcas y sistemas de gestión de información (128 h)

6. Funciones proporcionadas por el sistema gestor

El criterio RA5.e pide hacer uso de las funciones del gestor. Ya las conoces de SQL (UD06); en PL/SQL son las mismas y se usan igual en las expresiones.

6.1 Catálogo de funciones útiles

Un bloque que las combina sobre un alumno real:

DECLARE
    r_alu  alumno%ROWTYPE;
BEGIN
    SELECT * INTO r_alu FROM alumno WHERE id_alumno = 20;

    DBMS_OUTPUT.PUT_LINE('Ficha:     ' || UPPER(r_alu.apellidos) || ', ' || r_alu.nombre);
    DBMS_OUTPUT.PUT_LINE('Iniciales: ' || SUBSTR(r_alu.nombre, 1, 1) || SUBSTR(r_alu.apellidos, 1, 1));
    DBMS_OUTPUT.PUT_LINE('Nació:     ' ||
        TO_CHAR(r_alu.fecha_nacimiento, 'fmDD "de" month "de" YYYY', 'NLS_DATE_LANGUAGE=SPANISH'));
    DBMS_OUTPUT.PUT_LINE('Edad:      ' ||
        TRUNC(MONTHS_BETWEEN(DATE '2026-10-06', r_alu.fecha_nacimiento) / 12) || ' años');
    DBMS_OUTPUT.PUT_LINE('Dominio:   ' ||
        SUBSTR(r_alu.email, INSTR(r_alu.email, '@') + 1));
END;
/

Ficha:     SALA BROTONS, Mateo
Iniciales: MS
Nació:     9 de abril de 2003
Edad:      23 años
Dominio:   alu.edugest.es

Fíjate en que la edad se calcula con MONTHS_BETWEEN(...) / 12 y TRUNC, no con (fecha1 - fecha2) / 365. Restar fechas da días y los años bisiestos desajustan el resultado. Esa expresión es el núcleo de la función fn_edad (§8.3).

6.2 Qué funciones son de Oracle y cuáles estándar

No todas las funciones existen en otros gestores. Si el código debe ser portable, conviene saberlo:

Las funciones de agregado (SUM, AVG, COUNT…), las analíticas y DECODE solo pueden usarse dentro de una sentencia SQL; no en una instrucción procedimental. Para obtener un total, hay que escribirlo en un SELECT ... INTO; para decidir, usa CASE.

6.3 Funciones propias de PL/SQL

Además de las de SQL, PL/SQL ofrece funciones y atributos que solo existen en el lenguaje procedimental:

7. Cursores

7.1 Qué es un cursor y cuál es el implícito

Cuando Oracle ejecuta una sentencia SQL reserva una zona de memoria privada (el área de contexto) con la sentencia analizada y las filas del resultado. Un cursor es el puntero a esa zona. SELECT ... INTO solo puede traer una fila; para recorrer un resultado de varias filas hace falta un cursor.

Tras cada INSERT, UPDATE, DELETE o MERGE, el cursor implícito SQL informa de lo que ha pasado:

BEGIN
    UPDATE falta_asistencia
    SET    justificada = 'S'
    WHERE  justificada = 'N' AND fecha < DATE '2025-11-01';

    DBMS_OUTPUT.PUT_LINE('Faltas justificadas: ' || SQL%ROWCOUNT);   -- hay que leerlo YA

    IF SQL%NOTFOUND THEN
        DBMS_OUTPUT.PUT_LINE('No había ninguna falta que justificar');
    END IF;
    ROLLBACK;                                                         -- es una prueba
END;
/

Faltas justificadas: 7

SQL%ROWCOUNT se sobrescribe con cada sentencia SQL que se ejecute después, incluido un SELECT INTO. Léelo en la instrucción inmediatamente posterior al DML, o guárdalo en una variable. Y recuerda: un UPDATE que no encuentra filas no lanza error (SQL%NOTFOUND vale TRUE); solo SELECT ... INTO lanza NO_DATA_FOUND.

7.2 Cursores explícitos

Un cursor explícito sigue cuatro pasos:

DECLARE
    CURSOR c_notas IS
        SELECT mo.codigo, m.nota_final
        FROM   matricula m JOIN modulo mo ON mo.id_modulo = m.id_modulo
        WHERE  m.id_alumno = 20
        ORDER  BY mo.codigo;
    v_codigo  modulo.codigo%TYPE;
    v_nota    matricula.nota_final%TYPE;
    v_filas   PLS_INTEGER := 0;
BEGIN
    OPEN c_notas;
    LOOP
        FETCH c_notas INTO v_codigo, v_nota;
        EXIT WHEN c_notas%NOTFOUND;                  -- justo después del FETCH
        v_filas := v_filas + 1;
        DBMS_OUTPUT.PUT_LINE(v_codigo || '  ' || v_nota);
    END LOOP;
    CLOSE c_notas;
    DBMS_OUTPUT.PUT_LINE('Módulos leídos: ' || v_filas);
END;
/

0612  6.5
0613  5.25
0614  7.5
0615  9.25
Módulos leídos: 4

Los atributos del cursor permiten saber en qué punto está:

Dos errores clásicos: (1) poner el EXIT WHEN c%NOTFOUND después de usar las variables. El último FETCH, el que no encuentra fila, no cambia las variables, y la última fila se procesaría dos veces. (2) Olvidar el CLOSE: el cursor queda abierto hasta el final de la sesión y, en un bucle que lo abre una y otra vez, se alcanza el límite OPEN_CURSORS (ORA-01000).

7.3 Cursor FOR: la forma habitual

El bucle FOR de cursor hace los cuatro pasos por ti: abre el cursor, lee fila a fila, declara el registro y cierra el cursor al terminar, incluso si se sale por una excepción. Es más corto, más seguro y se debe preferir salvo que se necesite el control fino del cursor explícito.

BEGIN
    FOR r IN (SELECT g.cod_grupo, COUNT(a.id_alumno) AS alumnos
              FROM   grupo g
                     LEFT JOIN alumno a ON a.cod_grupo = g.cod_grupo
              GROUP  BY g.cod_grupo
              ORDER  BY g.cod_grupo) LOOP
        DBMS_OUTPUT.PUT_LINE(RPAD(r.cod_grupo, 7) || LPAD(r.alumnos, 2) || ' alumnos');
    END LOOP;
END;
/

1ASIR   5 alumnos
1DAM    7 alumnos
1DAW    6 alumnos
2ASIR   0 alumnos
2DAM    6 alumnos
2DAW    5 alumnos

Compara con el apartado anterior: ya no hay OPEN, FETCH, CLOSE, ni variables que declarar, ni EXIT WHEN. El registro r tiene un campo por cada columna del SELECT (r.cod_grupo, r.alumnos) y solo existe dentro del bucle. Observa también que el LEFT JOIN conserva el grupo 2ASIR, que no tiene alumnado (UD07).

Si la consulta es larga o se reutiliza, puede declararse con nombre y usarse en el FOR:

DECLARE
    CURSOR c_grupos IS SELECT cod_grupo, turno FROM grupo ORDER BY cod_grupo;
BEGIN
    FOR r IN c_grupos LOOP
        DBMS_OUTPUT.PUT_LINE(r.cod_grupo || ' · ' || r.turno);
    END LOOP;
END;
/

7.4 Cursores con parámetros

Un cursor puede recibir parámetros para reutilizar la misma consulta con valores distintos. Los parámetros se declaran sin longitud y se pasan al abrir el cursor:

DECLARE
    CURSOR c_modulos (p_ciclo  modulo.cod_ciclo%TYPE,
                      p_curso  modulo.curso%TYPE) IS
        SELECT codigo, nombre, horas
        FROM   modulo
        WHERE  cod_ciclo = p_ciclo AND curso = p_curso
        ORDER  BY codigo;
    v_total  PLS_INTEGER := 0;
BEGIN
    DBMS_OUTPUT.PUT_LINE('Módulos de DAW · segundo curso');
    FOR r IN c_modulos('DAW', 2) LOOP
        DBMS_OUTPUT.PUT_LINE(r.codigo || '  ' || RPAD(r.nombre, 36) || LPAD(r.horas, 4) || ' h');
        v_total := v_total + r.horas;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('Total: ' || v_total || ' h');
END;
/

Módulos de DAW · segundo curso
0612  Desarrollo web en entorno cliente     140 h
0613  Desarrollo web en entorno servidor    160 h
0614  Despliegue de aplicaciones web         80 h
0615  Diseño de interfaces web              120 h
Total: 500 h

Con un cursor explícito se escribiría OPEN c_modulos('DAW', 2);. Dentro de la consulta, el parámetro se usa como cualquier valor; el prefijo p_ evita que se confunda con una columna.

7.5 FOR UPDATE, WHERE CURRENT OF y REF CURSOR

Cuando el recorrido va a modificar las filas que lee, se bloquean con FOR UPDATE (bloqueo pesimista, UD08 §8.5) y se actualiza la fila actual con WHERE CURRENT OF:

-- Ejemplo de sintaxis: sube 0,25 puntos a las notas del módulo 16 (con tope 10)
DECLARE
    CURSOR c_notas IS
        SELECT id_matricula, nota_final
        FROM   matricula
        WHERE  id_modulo = 16 AND nota_final IS NOT NULL
        FOR UPDATE OF nota_final;
BEGIN
    FOR r IN c_notas LOOP
        UPDATE matricula
        SET    nota_final = LEAST(r.nota_final + 0.25, 10)
        WHERE CURRENT OF c_notas;
    END LOOP;
    ROLLBACK;                                      -- es solo una demostración
END;
/

Un REF CURSOR (SYS_REFCURSOR) es un cursor cuya consulta se decide en ejecución y que se puede devolver a quien llama. Es el mecanismo para entregar un resultado a una aplicación:

CREATE OR REPLACE FUNCTION fn_alumnos_grupo (p_cod_grupo IN grupo.cod_grupo%TYPE)
RETURN SYS_REFCURSOR
IS
    c_res  SYS_REFCURSOR;
BEGIN
    OPEN c_res FOR
        SELECT nia, apellidos, nombre
        FROM   alumno
        WHERE  cod_grupo = p_cod_grupo
        ORDER  BY apellidos;
    RETURN c_res;                  -- quien llama es responsable de leerlo y cerrarlo
END fn_alumnos_grupo;
/

7.6 Laboratorio: depurar bloques paso a paso

El siguiente depurador reproduce, línea a línea, tres de los bloques de esta unidad sobre datos reales de EduGest. Muestra la línea en ejecución, el valor de cada variable (resaltada cuando cambia) y el contenido del búfer de DBMS_OUTPUT. Las trazas están precalculadas: es una simulación didáctica, no un intérprete de PL/SQL.

Experimento 1 · Una consulta, tres desenlaces. Elige el bloque «SELECT … INTO y la sección EXCEPTION» y ejecútalo con sus tres entradas:

1. Con el NIA 10450740 avanza hasta el final. Comprueba que INTO copia los dos valores y que la sección EXCEPTION no llega a ejecutarse.
2. Con 10459999 (un NIA que no existe) observa a qué línea salta la ejecución cuando la consulta no devuelve filas, y qué valen entonces v_nia y v_nombre.
3. Con 104507% la consulta coincide con tres alumnos. Fíjate en que Oracle no asigna ni siquiera la primera fila, y en que se salta el manejador de NO_DATA_FOUND.

Experimento 2 · ¿Cuándo se entera el cursor de que no quedan filas? Cambia al bloque «Cursor explícito: OPEN, FETCH, CLOSE» y avanza paso a paso hasta el quinto FETCH:

1. Después de OPEN, ¿cuánto valen %ROWCOUNT y %NOTFOUND? ¿Se ha leído ya alguna fila?
2. En el quinto FETCH, observa qué le pasa a %NOTFOUND, a %ROWCOUNT y a v_codigo/v_nota.
3. Cambia ahora al bloque 1 («Bucle FOR») y compáralo: ¿qué instrucciones del cursor explícito han desaparecido y quién las hace?

7.7 Cuándo no usar un cursor

Un cursor procesa fila a fila, y cada vuelta cuesta un cambio de contexto entre el motor PL/SQL y el motor SQL. Si la tarea se puede escribir como una sola sentencia, el motor SQL la hace en conjunto y mucho más rápido:

-- Lento y largo: una sentencia UPDATE por cada fila
BEGIN
    FOR r IN (SELECT id_falta FROM falta_asistencia
              WHERE justificada = 'N' AND fecha < DATE '2025-11-01') LOOP
        UPDATE falta_asistencia SET justificada = 'S' WHERE id_falta = r.id_falta;
    END LOOP;
END;
/

-- Mejor: una sola sentencia SQL que hace lo mismo
UPDATE falta_asistencia
SET    justificada = 'S'
WHERE  justificada = 'N' AND fecha < DATE '2025-11-01';

Un cursor es la herramienta adecuada cuando, para cada fila, hay que hacer algo que SQL no puede hacer en conjunto: escribir un informe, llamar a un procedimiento, aplicar una lógica con varias decisiones, tratar cada fila con su propio control de errores. Cuando de verdad hay que procesar muchas filas con PL/SQL, BULK COLLECT lee un lote de golpe en una colección y FORALL envía todas sus modificaciones al motor SQL en una sola llamada; son optimizaciones que conviene conocer, no un punto de partida.

8. Funciones de usuario

8.1 Qué es una función y cuándo crearla

Una función de usuario es un subprograma almacenado en la base de datos que recibe parámetros y devuelve un único valor. El criterio RA5.f pide definirlas. Se crea una función cuando hay un cálculo que se repite y que se quiere escribir una sola vez: la calificación en texto de una nota, la edad a partir de una fecha, la media de un alumno.

Su gran ventaja sobre un procedimiento es que se puede usar dentro de una consulta SQL, como cualquier función del gestor:

SELECT nia, fn_edad(fecha_nacimiento) FROM alumno WHERE cod_grupo = '2DAW';

8.2 Sintaxis

CREATE [OR REPLACE] FUNCTION nombre
    [(parámetro [IN] tipo [DEFAULT valor], ...)]
RETURN tipo_devuelto
[DETERMINISTIC]
IS | AS
    -- declaraciones locales
BEGIN
    ...
    RETURN valor;
[EXCEPTION
    ...]
END [nombre];

8.3 Dos funciones de EduGest: fn_calificacion y fn_edad

La primera convierte una nota en su calificación en texto. Es la regla que ya vimos en la vista v_acta (UD07), ahora escrita una sola vez:

CREATE OR REPLACE FUNCTION fn_calificacion (p_nota IN NUMBER)
RETURN VARCHAR2
DETERMINISTIC
IS
BEGIN
    RETURN CASE
               WHEN p_nota IS NULL THEN 'NC'
               WHEN p_nota < 5     THEN 'Insuficiente'
               WHEN p_nota < 6     THEN 'Suficiente'
               WHEN p_nota < 7     THEN 'Bien'
               WHEN p_nota < 9     THEN 'Notable'
               ELSE                     'Sobresaliente'
           END;
END fn_calificacion;
/

La segunda calcula los años cumplidos. Tiene un parámetro con valor por defecto: si no se indica la fecha de referencia, usa la de hoy.

CREATE OR REPLACE FUNCTION fn_edad (
    p_fecha_nacimiento  IN DATE,
    p_fecha_ref         IN DATE DEFAULT SYSDATE
) RETURN NUMBER
IS
BEGIN
    IF p_fecha_nacimiento IS NULL THEN
        RETURN NULL;
    END IF;
    RETURN TRUNC(MONTHS_BETWEEN(p_fecha_ref, p_fecha_nacimiento) / 12);
END fn_edad;
/

Si todo va bien, SQLcl responde Function FN_CALIFICACION compiled. Si hay errores de compilación, los muestra con SHOW ERRORS (§2.5).

8.4 Usar una función

Desde SQL, en el SELECT, el WHERE o el ORDER BY:

SELECT a.nia,
       fn_edad(a.fecha_nacimiento, DATE '2026-10-06')  AS edad,
       ROUND(AVG(m.nota_final), 2)                      AS media,
       fn_calificacion(ROUND(AVG(m.nota_final), 2))     AS calificacion
FROM   alumno a
       JOIN matricula m ON m.id_alumno = a.id_alumno
WHERE  a.cod_grupo = '2DAW'
GROUP  BY a.nia, a.fecha_nacimiento
ORDER  BY a.nia;

5 filas

Desde PL/SQL, en una asignación o una expresión:

DECLARE
    v_nota  NUMBER := 9.25;
BEGIN
    DBMS_OUTPUT.PUT_LINE(v_nota || ' = ' || fn_calificacion(v_nota));
    DBMS_OUTPUT.PUT_LINE('Edad de Mateo en 2027: ' ||
                         fn_edad(DATE '2003-04-09', DATE '2027-04-09'));
END;
/

9.25 = Sobresaliente
Edad de Mateo en 2027: 24

Los argumentos se pueden pasar por posición (en el orden de la declaración) o por nombre con =>, que permite saltarse los opcionales y mejora la legibilidad:

SELECT fn_edad(p_fecha_ref => DATE '2026-10-06',
               p_fecha_nacimiento => DATE '2003-04-09') AS edad
FROM   dual;

8.5 Una función con consulta: fn_nombre_modulo

Una función puede leer de las tablas. Esta devuelve el nombre de un módulo, o NULL si no existe, en lugar de propagar el error al llamador:

CREATE OR REPLACE FUNCTION fn_nombre_modulo (p_id_modulo IN modulo.id_modulo%TYPE)
RETURN modulo.nombre%TYPE
IS
    v_nombre  modulo.nombre%TYPE;
BEGIN
    SELECT nombre INTO v_nombre FROM modulo WHERE id_modulo = p_id_modulo;
    RETURN v_nombre;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN NULL;                       -- decisión de diseño: «no existe» no es un error
END fn_nombre_modulo;
/

SELECT fn_nombre_modulo(2) AS existe, fn_nombre_modulo(999) AS no_existe FROM dual;

1 fila

Decidir qué hace una función cuando no encuentra el dato es una decisión de diseño que debes documentar: devolver NULL (cómodo en una consulta) o lanzar un error (obliga al llamador a tratarlo). En la práctica 9.2, fn_nombre_completo devuelve NULL; en el procedimiento de matrícula (§10.5), un alumno inexistente es un error.

8.6 Restricciones de las funciones llamadas desde SQL

Una función que se ejecuta dentro de un SELECT está sujeta a reglas, porque Oracle debe poder evaluarla muchas veces y en el orden que más le convenga:

Marca con DETERMINISTIC las funciones puras como fn_calificacion; así Oracle puede reutilizar el resultado para argumentos repetidos y, además, es requisito para usarlas en un índice basado en funciones.

8.7 Gestionar las funciones

-- ¿Qué funciones tengo y en qué estado están?
SELECT object_name, status FROM user_objects WHERE object_type = 'FUNCTION' ORDER BY object_name;

-- Ver el código fuente almacenado
SELECT text FROM user_source WHERE name = 'FN_EDAD' ORDER BY line;

-- Eliminarla
DROP FUNCTION fn_edad;

Si una función depende de una tabla y ésta cambia (por ejemplo, ALTER TABLE), Oracle la marca INVALID y la recompila sola en el siguiente uso; para forzarlo: ALTER FUNCTION fn_edad COMPILE;.

9. Excepciones

9.1 Qué es una excepción

Una excepción es un error que ocurre durante la ejecución de un bloque. Cuando se produce, el flujo normal se interrumpe: Oracle abandona las instrucciones restantes de la sección BEGIN y busca un manejador en la sección EXCEPTION.

BEGIN
    instrucción 1;          ← se ejecuta
    instrucción 2;          ← ¡error! se lanza una excepción
    instrucción 3;          ← NO se ejecuta
EXCEPTION
    WHEN una_excepcion THEN ← Oracle busca aquí, de arriba abajo, el primer WHEN que coincida
        tratamiento;
    WHEN OTHERS THEN        ← comodín: cualquier otra excepción
        tratamiento;
END;

El criterio RA5.j pide utilizar excepciones. Existen tres clases:

9.2 Excepciones predefinidas

Un bloque que calcula las horas de falta por alumno de un grupo. El grupo 2ASIR existe, pero no tiene alumnado:

DECLARE
    v_alumnos  PLS_INTEGER;
    v_horas    PLS_INTEGER;
    v_media    NUMBER;
BEGIN
    SELECT COUNT(*) INTO v_alumnos FROM alumno WHERE cod_grupo = '2ASIR';

    SELECT NVL(SUM(f.horas), 0)
    INTO   v_horas
    FROM   falta_asistencia f
           JOIN matricula m ON m.id_matricula = f.id_matricula
           JOIN alumno a    ON a.id_alumno    = m.id_alumno
    WHERE  a.cod_grupo = '2ASIR';

    v_media := v_horas / v_alumnos;                             -- 0 / 0 → ZERO_DIVIDE
    DBMS_OUTPUT.PUT_LINE('Horas de falta por alumno: ' || v_media);
EXCEPTION
    WHEN ZERO_DIVIDE THEN
        DBMS_OUTPUT.PUT_LINE('2ASIR no tiene alumnado: no se puede calcular la media');
END;
/

2ASIR no tiene alumnado: no se puede calcular la media

Y un INSERT que viola la clave primaria de CICLO:

BEGIN
    INSERT INTO ciclo (cod_ciclo, nombre, grado, horas_totales)
    VALUES ('DAW', 'Desarrollo de Aplicaciones Web', 'SUPERIOR', 2000);
EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN
        DBMS_OUTPUT.PUT_LINE('El ciclo DAW ya existe');
END;
/

El ciclo DAW ya existe

El caso de NO_DATA_FOUND y TOO_MANY_ROWS lo tienes paso a paso en el laboratorio del apartado 7.6.

9.3 OTHERS, SQLCODE y SQLERRM

Dentro de un manejador, dos funciones describen el error que se está tratando:

DECLARE
    v_cero  NUMBER := 0;
BEGIN
    DBMS_OUTPUT.PUT_LINE(10 / v_cero);
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('SQLCODE = ' || SQLCODE);
        DBMS_OUTPUT.PUT_LINE('SQLERRM = ' || SQLERRM);
END;
/

SQLCODE = -1476
SQLERRM = ORA-01476: divisor is equal to zero

WHEN OTHERS THEN NULL; es el peor código que se puede escribir en PL/SQL. Traga cualquier error, incluso uno inesperado, y deja la base de datos en un estado incierto sin dejar rastro. Un manejador OTHERS solo es aceptable si registra el error y lo vuelve a lanzar con RAISE;:

EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error ' || SQLCODE || ': ' || SQLERRM);   -- o, mejor, a una tabla de registro
        RAISE;                                                          -- el MISMO error sigue su camino

SQLERRM está limitado a 512 bytes y no incluye dónde ocurrió el error. Para depurar, DBMS_UTILITY.FORMAT_ERROR_STACK devuelve la pila completa y DBMS_UTILITY.FORMAT_ERROR_BACKTRACE indica los números de línea de cada subprograma por el que pasó.

9.4 Excepciones definidas por el usuario

Cuando la «anomalía» es de negocio y no del gestor, se declara una excepción propia en la sección DECLARE, se lanza con RAISE y se trata como las demás:

DECLARE
    e_sin_notas  EXCEPTION;                     -- 1. se declara
    v_notas      PLS_INTEGER;
BEGIN
    SELECT COUNT(nota_final) INTO v_notas FROM matricula WHERE id_alumno = 30;

    IF v_notas = 0 THEN
        RAISE e_sin_notas;                      -- 2. se lanza
    END IF;
    DBMS_OUTPUT.PUT_LINE('El alumno 30 tiene ' || v_notas || ' notas');
EXCEPTION
    WHEN e_sin_notas THEN                       -- 3. se trata
        DBMS_OUTPUT.PUT_LINE('El alumno 30 no tiene ninguna nota');
END;
/

El alumno 30 no tiene ninguna nota

Una excepción declarada así solo existe dentro de su bloque. Si no se trata, al llegar al cliente se muestra como ORA-06510: PL/SQL: unhandled user-defined exception, un mensaje que no ayuda a nadie: por eso, para errores que deben llegar a la aplicación, se prefiere RAISE_APPLICATION_ERROR (§9.6).

9.5 PRAGMA EXCEPTION_INIT: ponerle nombre a un error de Oracle

Hay muchos errores ORA- que no tienen nombre predefinido. PRAGMA EXCEPTION_INIT asocia un nombre propio a un código, para poder tratarlo en un WHEN. Un caso típico de EduGest: borrar un profesor que todavía imparte módulos (ORA-02292, clave ajena sin ON DELETE):

DECLARE
    e_tiene_hijos  EXCEPTION;
    PRAGMA EXCEPTION_INIT(e_tiene_hijos, -2292);        -- ORA-02292: child record found
BEGIN
    DELETE FROM profesor WHERE id_profesor = 105;
    DBMS_OUTPUT.PUT_LINE('Profesor borrado');
EXCEPTION
    WHEN e_tiene_hijos THEN
        DBMS_OUTPUT.PUT_LINE('No se puede borrar al profesor 105: imparte módulos (tabla IMPARTE)');
END;
/

No se puede borrar al profesor 105: imparte módulos (tabla IMPARTE)

El segundo argumento es el código con signo negativo (-2292, no 2292). Otros códigos que se asocian con frecuencia:

9.6 RAISE_APPLICATION_ERROR: errores de negocio hacia el cliente

RAISE_APPLICATION_ERROR(código, mensaje) detiene el subprograma y devuelve al llamador un error con el código y el mensaje que tú decidas. Es la forma estándar de comunicar a una aplicación que se ha incumplido una regla de negocio.

BEGIN
    RAISE_APPLICATION_ERROR(-20010, 'El módulo 12 no pertenece al ciclo del alumno');
END;
/

ORA-20010: El módulo 12 no pertenece al ciclo del alumno
ORA-06512: at line 2

Reserva un catálogo de códigos para el proyecto y documéntalo, igual que una tabla de errores HTTP. Así la aplicación puede reaccionar al código (-20013 → «ya estás matriculado») y no analizar el texto. EduGest usa:

Código	Significado	Dónde
-20010	El módulo no es del ciclo del alumno (R2)	pr_matricular
-20011	Convocatorias agotadas (máximo 4)	pr_matricular
-20012	Alumno o módulo inexistente, o alumno sin grupo	pr_matricular
-20013	Alumno ya matriculado del módulo en ese curso	pr_matricular
-20020	El jefe no pertenece al departamento (R1)	trg_jefe_departamento
-20021	Profesor jefe que cambia de departamento (R1)	trg_profesor_cambio_dpto
-20022	Falta anterior a la fecha de matrícula (R3)	trg_falta_fecha
-20030	Un profesor supera las 20 horas semanales (R4)	trg_imparte_max_horas
-20040	Un grupo supera los 30 alumnos (R5)	trg_grupo_max_alumnos

9.7 Bloques anidados: limitar el alcance de un error

Un SELECT ... INTO que no encuentra datos en medio de un bucle aborta todo el bucle. Si lo que se quiere es tratar el caso y continuar con la fila siguiente, la solución es envolver la parte arriesgada en su propio bloque:

DECLARE
    TYPE t_ids IS TABLE OF PLS_INTEGER;
    v_ids     t_ids := t_ids(20, 999, 30);
    v_nombre  VARCHAR2(130);
BEGIN
    FOR i IN 1..v_ids.COUNT LOOP
        BEGIN                                              -- bloque interior, uno por vuelta
            SELECT apellidos || ', ' || nombre
            INTO   v_nombre
            FROM   alumno
            WHERE  id_alumno = v_ids(i);
            DBMS_OUTPUT.PUT_LINE(v_ids(i) || ': ' || v_nombre);
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                DBMS_OUTPUT.PUT_LINE(v_ids(i) || ': no existe');
        END;
    END LOOP;
END;
/

20: Sala Brotons, Mateo
999: no existe
30: Iborra Valero, Zoe

Dos particularidades que sorprenden:

1. Una excepción en la sección DECLARE no la trata el manejador del mismo bloque, sino el del bloque exterior. Con v_corto VARCHAR2(3) := 'Sala'; el bloque falla con ORA-06502 aunque tenga un WHEN VALUE_ERROR: aún no había empezado a ejecutarse.
2. Una excepción dentro de un manejador tampoco la trata el mismo bloque: sube al exterior.

9.8 Excepciones y transacciones

Un error no hace ROLLBACK de la transacción. Lo que ocurre depende de si se trata o no:

BEGIN
    SAVEPOINT antes_de_borrar;
    DELETE FROM falta_asistencia WHERE id_matricula = 10060;
    DELETE FROM profesor WHERE id_profesor = 105;          -- ORA-02292
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK TO antes_de_borrar;                        -- deshace también el primer DELETE
        RAISE;
END;
/

No dependas del comportamiento implícito. Un subprograma que hace varias modificaciones y trata sus propios errores debe decidir explícitamente qué se conserva y qué se deshace, y quien lo llama debe saberlo. En la práctica 9.8 el paquete de secretaría usa exactamente esta técnica.

10. Procedimientos almacenados

10.1 Qué es un procedimiento

Un procedimiento almacenado es un subprograma con nombre, guardado y compilado en la base de datos, que realiza una acción y no devuelve valor (la información de salida viaja por los parámetros OUT). Se invoca desde un cliente, desde otro subprograma o desde una tarea programada.

CREATE [OR REPLACE] PROCEDURE nombre
    [(parámetro [IN | OUT | IN OUT] tipo [DEFAULT valor], ...)]
[AUTHID DEFINER | CURRENT_USER]
IS | AS
    -- declaraciones locales
BEGIN
    -- instrucciones
[EXCEPTION
    -- manejadores]
END [nombre];

La estructura es la del bloque anónimo, con la cabecera CREATE PROCEDURE en lugar de DECLARE: las declaraciones locales se escriben entre IS y BEGIN, sin la palabra DECLARE.

10.2 Parámetros IN, OUT e IN OUT

Un procedimiento que devuelve datos de un módulo mediante parámetros OUT:

CREATE OR REPLACE PROCEDURE pr_datos_modulo (
    p_id_modulo  IN  modulo.id_modulo%TYPE,
    p_codigo     OUT modulo.codigo%TYPE,
    p_nombre     OUT modulo.nombre%TYPE,
    p_horas      OUT modulo.horas%TYPE
)
IS
BEGIN
    SELECT codigo, nombre, horas
    INTO   p_codigo, p_nombre, p_horas
    FROM   modulo
    WHERE  id_modulo = p_id_modulo;
END pr_datos_modulo;
/