UD09 · Programació de la base de dades amb PL/SQL

Resum del tema

Fins ara, la lògica de les nostres tasques vivia fora de la base de dades: en un guió .sql que llançàvem a mà o en l’aplicació que consulta Oracle. Això funciona mentre la tasca siga una seqüència de sentències. Deixa de funcionar quan cal decidir (té l’alumne faltes suficients per a perdre l’avaluació contínua?), repetir (recórrer tots els alumnes d’un grup), reaccionar a un error (què faig si el mòdul no existix?) o garantir una regla que ningú puga saltar-se, entre elles les que a la UD03 vam anomenar restriccions que el model lògic no pot expressar.

La solució és programar dins del servidor. Oracle incorpora per a això el llenguatge PL/SQL (Procedural Language / SQL): SQL més variables, condicions, bucles, cursors i excepcions, organitzat en blocs. Amb ell es construïxen els objectes que guarda la pròpia base de dades: funcions, procediments, paquets i disparadors, i amb el planificador DBMS_SCHEDULER s’executen sols a l’hora que s’indique.

La unitat avança en cinc passos: primer quines formes hi ha d’automatitzar i amb quines eines es treballa (§1 i §2); després el llenguatge (blocs, variables, control de flux, funcions del gestor i cursors, §3 a §7); a continuació els subprogrames emmagatzemats i el tractament d’errors (§8 a §10); després els disparadors, amb l’auditoria de notes, la integritat que les restriccions no arriben a cobrir i el famós error de la taula mutant (§11); i finalment les tasques programades, els paquets i la comparació amb altres gestors (§12 a §14).

Tots els exemples s’executen sobre l’esquema de referència EDUGEST amb els scripts del projecte carregats (32 alumnes, 143 matrícules, 46 faltes) i usen la sintaxi d’Oracle AI Database 26ai Free. Cada exemple indica l’eixida que has d’obtindre. Quan alguna cosa és pròpia d’un gestor concret se senyala amb una etiqueta o una nota.

Objectius d’aprenentatge

En acabar esta unitat seràs capaç de:

• Distingir una consulta, un guió, un bloc anònim, una funció, un procediment, un paquet, un disparador i una tasca programada, i triar la forma d’automatització adequada per a cada necessitat (RA5.a).
• Editar i executar guions amb SQL Developer, SQLcl o SQL*Plus, usant variables de substitució i l’eixida de DBMS_OUTPUT (RA5.b, RA5.c).
• Escriure blocs PL/SQL amb variables, constants i tipus %TYPE i %ROWTYPE, i distingir les variables de substitució del client de les variables PL/SQL del servidor.
• Aplicar estructures de control de flux (IF, CASE, LOOP, WHILE, FOR) i les funcions predefinides del gestor (RA5.e, RA5.g).
• Recórrer conjunts de files amb cursors implícits, explícits, de bucle FOR i amb paràmetres (RA5.i).
• Definir funcions i procediments d’usuari amb paràmetres IN, OUT i IN OUT (RA5.f).
• Tractar errors amb excepcions predefinides i pròpies, PRAGMA EXCEPTION_INIT i RAISE_APPLICATION_ERROR (RA5.j).
• Implementar amb disparadors l’auditoria de canvis i les regles d’integritat que el model lògic no pot declarar, i resoldre l’error de taula mutant amb un disparador compost (RA5.h, RA4.h, RA6.h).
• Programar tasques periòdiques amb DBMS_SCHEDULER, agrupar subprogrames en paquets i reconéixer els equivalents en MySQL/MariaDB, PostgreSQL i SQL Server (RA5.a, RA5.d, RA4.d).

Temporalització

La unitat ocupa 7 hores d’aula (4 de teoria i 3 de pràctica). És la unitat amb més codi del curs: convé escriure’l a mà i recompilar-lo, no copiar-lo i apegar-lo. Les pràctiques llargues (la taula mutant, les tasques programades, el paquet de secretaria i el projecte) es completen fora de l’horari d’aula.

Treballa sempre connectat com a EDUGEST i amb l’eixida activada (SET SERVEROUTPUT ON). Tot el codi d’esta unitat es prova amb un mètode fix:

1. Prediu què ha de mostrar o tornar abans d’executar-lo.
2. Executa i compara amb l’eixida que figura en els apunts.
3. Si falla, llig l’error d’amunt a avall: el primer missatge (PLS- en compilació, ORA- en execució) és la causa; els següents només diuen on va ocórrer.
4. Guarda cada subprograma en el seu propi fitxer .sql acabat en /: així es recompila i es versiona.

1. Programar dins del servidor

1.1 Quin problema resol

SQL és un llenguatge declaratiu: dius què vols, no com obtindre-ho. És excel·lent per a consultar i modificar conjunts de files, però no té variables, ni condicions, ni bucles, ni forma de reaccionar a un error. Tan prompte una tasca necessita alguna d’eixes coses, hi ha dues opcions: traure les dades a una aplicació i programar allí la lògica, o programar-la dins de la base de dades.

Pensa en la matrícula d’un alumne a EduGest. Abans d’inserir la fila cal comprovar que l’alumne existix i té grup, que el mòdul és del seu cicle, que no està ja matriculat eixe curs i que no ha esgotat les quatre convocatòries. Són quatre consultes, tres decisions i un INSERT, i eixes regles s’han de complir sempre, vinga la matrícula de l’aplicació web, d’un script de la secretaria o d’una eina gràfica.

La UD03 ja va identificar les regles de negoci d’EduGest que el model lògic no pot expressar. Esta unitat les implementa:

La regla R6 està en la taula a propòsit: no tot es resol amb un disparador. Si una restricció declarativa (CHECK, UNIQUE, clau aliena) pot expressar la regla, és sempre preferible: és més ràpida, més clara i l’optimitzador la coneix.

1.2 Formes d’automatitzar tasques

El criteri RA5.a demana identificar les formes d’automatitzar una tasca en un gestor. En Oracle són estes:

Fora del servidor existixen altres formes d’automatitzar, també vàlides: el programador de tasques del sistema operatiu (cron, Programador de tasques de Windows) llançant SQLcl, o la pròpia aplicació. La diferència és on viu la lògica: dins, viatja amb la base de dades i la protegixen els seus privilegis; fora, depén d’un altre sistema.

1.3 Consulta, guió, bloc, funció, procediment, paquet i disparador

És la confusió més freqüent en començar. Esta taula les separa per les seues propietats:

Tres idees per a quedar-se amb la taula:

1. El que es guarda en la base de dades es compila una sola vegada i s’executa moltes. Un guió o un bloc anònim s’analitzen cada vegada que s’envien.
2. Una funció torna un valor i es pot usar en una consulta; un procediment fa alguna cosa. Si necessites cridar-la des d’un SELECT, és una funció.
3. Un disparador ningú no el crida: s’executa quan ocorre el seu esdeveniment. Per això cal escriure’ls amb molta cura.

1.4 Client o servidor: on posar la lògica

Cada vegada que una aplicació llança una sentència SQL hi ha un viatge d’anada i tornada per la xarxa. Si la matrícula d’un alumne en cinc mòduls es programa en l’aplicació, són desenes de viatges; si es programa en un procediment emmagatzemat, és una crida.

 Lògica en l'aplicació                         Lògica en el servidor
 ───────────────────────                       ──────────────────────
 App ──SELECT alumno──────────► Oracle         App ──pr_matricular(1, 2, '2026-27')──► Oracle
 App ◄─────────────files──────  Oracle                                                  │ valida
 App ──SELECT módulo──────────► Oracle                                                  │ calcula
 App ◄─────────────files──────  Oracle                                                  │ calcula
 App ──INSERT matricula───────► Oracle         App ◄──────────────────────OK / error─────┘
 App ◄──────────────files─────  Oracle
 (3 viatges com a mínim)                       (1 viatge)

Regla pràctica: posa en el servidor el que protegix la integritat de les dades i el que és una operació atòmica de negoci (matricular, promocionar, anul·lar). Deixa en l’aplicació la presentació, els fluxos d’usuari i tot el que no afecte la correcció de les dades.

1.5 Què és PL/SQL

PL/SQL és l’extensió procedimental d’SQL d’Oracle. Un programa PL/SQL s’envia al servidor i l’executa el motor PL/SQL, que s’encarrega de les instruccions procedimentals (assignacions, condicions, bucles) i lliura al motor SQL les sentències SQL que troba (SELECT, INSERT, UPDATE…).

       Client (SQL Developer, SQLcl, aplicació)
                       │  bloc PL/SQL
                       ▼
        ┌─────────────────────────────────┐
        │          SERVIDOR ORACLE        │
        │   Motor PL/SQL  ──sentències─► Motor SQL ──► Dades
        │   (variables, IF, bucles,      │  (SELECT, INSERT...)
        │    excepcions)   ◄──resultat───┘
        └─────────────────────────────────┘

Cada pas d’un motor a l’altre (context switch) té un cost xicotet però mesurable. Per això, quan una tasca es pot escriure com una sola sentència SQL, eixa és quasi sempre la millor versió (§7.7).

PL/SQL és propi d’Oracle. Cada gestor té el seu llenguatge: PostgreSQL usa PL/pgSQL, MySQL i MariaDB usen el llenguatge de rutines emmagatzemades SQL/PSM, SQL Server usa Transact-SQL (T-SQL). Els conceptes (variables, condicions, bucles, cursors, excepcions, disparadors) són els mateixos; la sintaxi canvia. L’apartat 14 els compara.

2. Eines i execució de guions

2.1 Eines per a editar guions

El criteri RA5.c demana identificar les eines disponibles. Un guió PL/SQL és text: es pot escriure en qualsevol editor, però les eines especialitzades aporten ressaltat, execució directa i accés al diccionari de dades.

En el contenidor Oracle AI Database 26ai Free el servei al qual es connecta l’alumnat és FREEPDB1: sql edugest@localhost:1521/FREEPDB1.

2.2 Mètodes d’execució de guions

El criteri RA5.b demana reconéixer els mètodes d’execució de guions. Un guió pot executar-se de diverses maneres:

Un guió pot rebre paràmetres posicionals (&1, &2…), que són substituïts pel client abans d’enviar res al servidor:

-- informe_grupo.sql · Ús: @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

En llançar @informe_grupo.sql 2DAW es crea el fitxer informe_2DAW.txt (el segon punt de &1..txt és el literal; el primer acaba el nom de la variable) amb:

5 files

Les ordres d’un guió són de dues classes que convé no confondre:

2.3 Com s’acaba cada cosa: ; i /

El client necessita saber on acaba cada sentència per a enviar-la al servidor. La regla és simple però és la causa de molts errors de principiant:

BEGIN
    DBMS_OUTPUT.PUT_LINE('Hola');   -- este ; tanca la instrucció PL/SQL
END;                                 -- este ; tanca el bloc
/                                    -- esta / l'envia al servidor

Si oblides la /, SQLcl es queda esperant sense executar res (a vegades mostra un número de línia, 2 3 4...). Si l’escrius de més, executes una altra vegada l’últim bloc. Un CREATE OR REPLACE repetit és inofensiu; un bloc amb un INSERT, no.

2.4 L’eixida del servidor: DBMS_OUTPUT

PL/SQL no té una instrucció PRINT. El paquet DBMS_OUTPUT escriu text en un búfer del servidor, i és el client qui el bolca per pantalla quan el bloc acaba, si se li ha demanat:

SET SERVEROUTPUT ON

En SQL Developer, a més de SET SERVEROUTPUT ON, ha d’estar obert el panell Veure → Eixida de DBMS i habilitada la connexió amb el botó +.

DBMS_OUTPUT és una eina de depuració i de pràctiques, no un mecanisme de producció: el text només existix mentre dura l’execució, es mostra al final (no mentre el bloc treballa) i un procediment que s’executa des d’una aplicació o una tasca programada no té ningú al davant per a llegir-lo. Per a deixar constància cal escriure en una taula de registre (pràctica 9.7).

2.5 Errors de compilació

Un procediment, funció o disparador es compila en crear-lo. Si hi ha errors, Oracle el crea igualment però en estat INVALID i avisa amb Warning: Procedure created with compilation errors. Els errors de compilació comencen per PLS-:

SHOW ERRORS
-- o, des de qualsevol client:
SELECT line, position, text
FROM   user_errors
WHERE  name = 'FN_EDAD'
ORDER  BY sequence;

3. Estructura d’un bloc PL/SQL

3.1 Les quatre seccions

Tot programa PL/SQL es construïx amb blocs. Un bloc té fins a quatre seccions, i només BEGIN ... END és obligatòria:

DECLARE        -- 1. Declaracions (opcional): variables, constants, cursors, excepcions
    ...
BEGIN          -- 2. Instruccions (obligatòria): el programa en si
    ...
EXCEPTION      -- 3. Tractament d'errors (opcional)
    ...
END;           -- 4. Fi del bloc

SET SERVEROUTPUT ON
DECLARE
    c_centro  CONSTANT VARCHAR2(20) := 'EduGest';      -- constant
    v_hoy     DATE := SYSDATE;                          -- variable inicialitzada
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 data és la del teu sistema. L’última línia l’escriu el client, no el bloc: confirma que el servidor l’ha executat sense errors.

3.2 Què es pot escriure dins d’un bloc

3.3 Blocs aniuats i àmbit

Un bloc pot contindre altres blocs en la seua secció executable o en la d’excepcions. Una variable és visible en el bloc on es declara i en els blocs que conté; si un bloc interior en declara una amb el mateix nom, oculta l’exterior mentre dura.

DECLARE
    v_x  NUMBER := 1;                                    -- bloc exterior
BEGIN
    DECLARE
        v_x  NUMBER := 2;                                -- oculta l'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 existix fora del seu bloc
END;
/

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

Els blocs aniuats no servixen només per a organitzar el codi: són l’eina per a limitar l’abast d’una excepció (§9.7).

3.4 Comentaris, majúscules i convencions de noms

PL/SQL no distingix majúscules de minúscules en el codi (begin és BEGIN), però sí en els literals de text ('EduGest' i 'EDUGEST' són distints). Els comentaris són de línia (-- texto) o de diverses línies (/* texto */).

Un prefix en cada nom evita l’error més comú: que una variable es diga igual que una columna. Si en WHERE id_alumno = id_alumno tots dos són la columna, la condició és sempre vertadera. EduGest usa estes convencions:

4. Variables, constants i tipus

4.1 Declaració

nom  [CONSTANT]  tipus  [NOT NULL]  [:= valor_inicial | DEFAULT valor_inicial];

Dues regles valen per a tots: una variable sense valor inicial val NULL, i una constant ha de tindre valor inicial.

4.2 Assignar valors: := i SELECT ... INTO

Una variable rep valor de dues formes: amb l’operador := o llegint d’una consulta amb 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 exigix que la consulta torne exactament una fila:

El laboratori de l’apartat 7.6 permet veure-ho pas a pas amb els tres casos.

Un SELECT ... INTO amb una funció d’agregat sense GROUP BY torna sempre una fila, encara que no hi haja dades: SELECT AVG(nota_final) INTO v_media FROM matricula WHERE id_alumno = 30; no llança NO_DATA_FOUND; v_media queda a NULL. És una diferència important entre «la consulta no troba res» i «l’agregat de res».

4.3 %TYPE i %ROWTYPE: heretar el tipus de la taula

Escriure v_nia CHAR(8) funciona fins al dia en què algú amplia la columna. Els atributs %TYPE i %ROWTYPE fan que la variable herete el tipus directament del diccionari de dades:

DECLARE
    r_mod  modulo%ROWTYPE;                              -- un camp per 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)

S’accedix a cada camp amb registro.columna. Avantatges de %ROWTYPE: el SELECT * no necessita una variable per columna, i si s’afig una columna a MODULO el bloc continua compilant. El seu inconvenient: porta totes les columnes, encara que només n’uses dos.

Quan interessa un registre amb camps triats, es defineix un tipus propi amb 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

Els números es converteixen a text amb el separador decimal de la sessió (NLS_NUMERIC_CHARACTERS). En el contenidor de pràctiques és el punt, com en les eixides d’estos apunts; amb una configuració regional espanyola veuries 7,13. Per a controlar-ho, usa TO_CHAR(x, '990D00').

4.4 Variables de substitució enfront de variables PL/SQL

És la confusió més freqüent de la unitat. Observa este bloc:

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

Les dues línies old i new les escriu el client (SQLcl, SQL*Plus, SQL Developer): ha substituït &g_grupo per 2DAW en el text del bloc i només després l’ha enviat a Oracle. El servidor mai no ha vist el &. Una variable de substitució no és una variable: és un marcador de «buscar i reemplaçar» del client.

Com que &variable és text apegat en el codi, escriure &id_alumno en un guió que rep dades d’una persona és una via d’injecció de codi. Servix per a pràctiques amb un guió que executes tu; en una aplicació s’usen sempre variables d’enllaç (:nombre) o paràmetres d’un procediment. I a la inversa: si les teues dades contenen un & ('Gómez & Pérez'), el client intentarà substituir-lo i preguntarà per una variable; l’script de dades d’EduGest comença amb SET DEFINE OFF per eixa raó.

Les variables d’enllaç (bind variables) es declaren en el client amb VARIABLE i es lligen amb :nombre. Sí que les entén el servidor, que les rep com a valors:

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 i del context de la sessió

PL/SQL no té variables globals del sistema com a tals: s’obtenen amb funcions. Estes són les més útils:

Estes funcions són les que permeten registrar qui ha fet un canvi i des d’on: s’usen en l’auditoria (§11.2).

5. Estructures de control de flux

El criteri RA5.g demana utilitzar estructures de control. PL/SQL té les tres famílies habituals: condicionals (IF, CASE), repetitives (LOOP, WHILE, FOR) i de salt (EXIT, CONTINUE, GOTO).

5.1 IF, ELSIF y ELSE

IF condició1 THEN
    instruccions;
[ELSIF condició2 THEN
    instruccions;]
[ELSE
    instruccions;]
END IF;

S’escriu ELSIF (sense la E d’else if) i es tanca amb END IF;. Les condicions s’avaluen en ordre i s’executa la primera branca vertadera.

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ó amb NULL no és vertadera ni falsa: és desconeguda (lògica trivaluada, UD06), i IF només executa la branca THEN quan la condició és vertadera. Amb v_nota a NULL, IF v_nota >= 5 THEN ... ELSE executaria l’ELSE i marcaria com a suspés qui no s’ha presentat. Per això en l’exemple la primera pregunta és IS NULL. I mai s’escriu v_nota = NULL: no és vertadera mai.

5.2 CASE: sentència i expressió

CASE és l’alternativa més llegible quan hi ha moltes branques. Existix en dues formes, i cadascuna en dos usos:

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

    -- CASE simple com a sentència
    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 amb condicions com a expressió
    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 cap branca d’una sentència CASE es complix i no hi ha ELSE, Oracle llança CASE_NOT_FOUND (ORA-06592). L’expressió CASE sense ELSE torna NULL en silenci. Escriu sempre l’ELSE.

5.3 Bucles

Un exemple amb cadascun, sobre les convocatòries que li queden a un alumne que va per la segona:

DECLARE
    v_conv  PLS_INTEGER := 2;                      -- convocatòria actual
BEGIN
    -- LOOP bàsic: l'eixida està al mig
    LOOP
        EXIT WHEN v_conv > 4;
        DBMS_OUTPUT.PUT_LINE('Queda la convocatoria ' || v_conv);
        v_conv := v_conv + 1;
    END LOOP;

    -- WHILE: equivalent, amb la condició al principi
    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èric declara ell mateix el seu comptador, que només existix dins del bucle i no es pot 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                       -- compte enrere
        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, etiquetes i GOTO

BEGIN
    <<externo>>
    FOR g IN 1..3 LOOP
        FOR a IN 1..3 LOOP
            CONTINUE WHEN a = 2;                     -- se salta la volta a = 2
            EXIT externo WHEN g = 2;                 -- ix dels 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 Errors habituals amb els 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. Funcions proporcionades pel sistema gestor

El criteri RA5.e demana fer ús de les funcions del gestor. Ja les coneixes de SQL (UD06); en PL/SQL són les mateixes i s’usen igual en les expressions.

6.1 Catàleg de funcions útils

Un bloc que les combina sobre un alumne 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

Fixa’t que l’edat es calcula amb MONTHS_BETWEEN(...) / 12 i TRUNC, no amb (fecha1 - fecha2) / 365. Restar dates dona dies i els anys de traspàs desajusten el resultat. Eixa expressió és el nucli de la funció fn_edad (§8.3).

6.2 Quines funcions són d’Oracle i quines estàndard

No totes les funcions existixen en altres gestors. Si el codi ha de ser portable, convé saber-ho:

Les funcions d’agregat (SUM, AVG, COUNT…), les analítiques i DECODE només es poden usar dins d’una sentència SQL; no en una instrucció procedimental. Per a obtindre un total, cal escriure’l en un SELECT ... INTO; per a decidir, usa CASE.

6.3 Funcions pròpies de PL/SQL

A més de les de SQL, PL/SQL oferix funcions i atributs que només existixen en el llenguatge procedimental:

7. Cursors

7.1 Què és un cursor i quin és l’implícit

Quan Oracle executa una sentència SQL reserva una zona de memòria privada (l’àrea de context) amb la sentència analitzada i les files del resultat. Un cursor és el punter a eixa zona. SELECT ... INTO només pot portar una fila; per a recórrer un resultat de diverses files fa falta un cursor.

Després de cada INSERT, UPDATE, DELETE o MERGE, el cursor implícit SQL informa del que ha passat:

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

    DBMS_OUTPUT.PUT_LINE('Faltas justificadas: ' || SQL%ROWCOUNT);   -- cal llegir-ho ARA

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

Faltas justificadas: 7

SQL%ROWCOUNT se sobreescriu amb cada sentència SQL que s’execute després, inclòs un SELECT INTO. Llig-lo en la instrucció immediatament posterior al DML, o guarda’l en una variable. I recorda: un UPDATE que no troba files no llança error (SQL%NOTFOUND val TRUE); només SELECT ... INTO llança NO_DATA_FOUND.

7.2 Cursors explícits

Un cursor explícit seguix quatre passos:

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;                  -- just despré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

Els atributs del cursor permeten saber en quin punt està:

Dos errors clàssics: (1) posar l’EXIT WHEN c%NOTFOUND després d’usar les variables. L’últim FETCH, el que no troba fila, no canvia les variables, i l’última fila es processaria dues vegades. (2) Oblidar el CLOSE: el cursor queda obert fins al final de la sessió i, en un bucle que l’obri una vegada i una altra, s’arriba al límit OPEN_CURSORS (ORA-01000).

7.3 Cursor FOR: la forma habitual

El bucle FOR de cursor fa els quatre passos per tu: obri el cursor, llig fila a fila, declara el registre i tanca el cursor en acabar, fins i tot si se n’ix per una excepció. És més curt, més segur i s’ha de preferir tret que calga el control fi del cursor explícit.

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 amb l’apartat anterior: ja no hi ha OPEN, FETCH, CLOSE, ni variables que declarar, ni EXIT WHEN. El registre r té un camp per cada columna del SELECT (r.cod_grupo, r.alumnos) i només existix dins del bucle. Observa també que el LEFT JOIN conserva el grup 2ASIR, que no té alumnat (UD07).

Si la consulta és llarga o es reutilitza, pot declarar-se amb nom i usar-se 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 Cursors amb paràmetres

Un cursor pot rebre paràmetres per a reutilitzar la mateixa consulta amb valors distints. Els paràmetres es declaren sense longitud i es passen en obrir 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

Amb un cursor explícit s’escriuria OPEN c_modulos('DAW', 2);. Dins de la consulta, el paràmetre s’usa com qualsevol valor; el prefix p_ evita que es confonga amb una columna.

7.5 FOR UPDATE, WHERE CURRENT OF y REF CURSOR

Quan el recorregut va a modificar les files que llig, es bloquegen amb FOR UPDATE (bloqueig pessimista, UD08 §8.5) i s’actualitza la fila actual amb WHERE CURRENT OF:

-- Exemple de sintaxi: puja 0,25 punts a les notes del mòdul 16 (amb topall 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;                                      -- és només una demostració
END;
/

Un REF CURSOR (SYS_REFCURSOR) és un cursor la consulta del qual es decidix en execució i que es pot tornar a qui crida. És el mecanisme per a lliurar un resultat a una aplicació:

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;                  -- qui crida és responsable de llegir-lo i tancar-lo
END fn_alumnos_grupo;
/

7.6 Laboratori: depurar blocs pas a pas

El depurador següent reprodueix, línia a línia, tres dels blocs d’esta unitat sobre dades reals d’EduGest. Mostra la línia en execució, el valor de cada variable (ressaltada quan canvia) i el contingut del búfer de DBMS_OUTPUT. Les traces estan precalculades: és una simulació didàctica, no un intèrpret de PL/SQL.

Experiment 1 · Una consulta, tres desenllaços. Tria el bloc «SELECT … INTO i la secció EXCEPTION» i executa’l amb les seues tres entrades:

1. Amb el NIA 10450740 avança fins al final. Comprova que INTO copia els dos valors i que la secció EXCEPTION no arriba a executar-se.
2. Amb 10459999 (un NIA que no existix) observa a quina línia salta l’execució quan la consulta no torna files, i què valen aleshores v_nia i v_nombre.
3. Amb 104507% la consulta coincidix amb tres alumnes. Fixa’t que Oracle no assigna ni tan sols la primera fila, i que se salta el manejador de NO_DATA_FOUND.

Experiment 2 · Quan s’assabenta el cursor que no queden files? Canvia al bloc «Cursor explícit: OPEN, FETCH, CLOSE» i avança pas a pas fins al cinqué FETCH:

1. Després d’OPEN, quant valen %ROWCOUNT i %NOTFOUND? S’ha llegit ja alguna fila?
2. En el cinqué FETCH, observa què li passa a %NOTFOUND, a %ROWCOUNT i a v_codigo/v_nota.
3. Canvia ara al bloc 1 («Bucle FOR») i compara’l: quines instruccions del cursor explícit han desaparegut i qui les fa?

7.7 Quan no usar un cursor

Un cursor processa fila a fila, i cada volta costa un canvi de context entre el motor PL/SQL i el motor SQL. Si la tasca es pot escriure com una sola sentència, el motor SQL la fa en conjunt i molt més ràpid:

-- Lent i llarg: una sentència UPDATE per 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;
/

-- Millor: una sola sentència SQL que fa el mateix
UPDATE falta_asistencia
SET    justificada = 'S'
WHERE  justificada = 'N' AND fecha < DATE '2025-11-01';

Un cursor és l’eina adequada quan, per a cada fila, cal fer alguna cosa que SQL no pot fer en conjunt: escriure un informe, cridar a un procediment, aplicar una lògica amb diverses decisions, tractar cada fila amb el seu propi control d’errors. Quan de debò cal processar moltes files amb PL/SQL, BULK COLLECT llig un lot de colp en una col·lecció i FORALL envia totes les seues modificacions al motor SQL en una sola crida; són optimitzacions que convé conéixer, no un punt de partida.

8. Funcions d’usuari

8.1 Què és una funció i quan crear-la

Una funció d’usuari és un subprograma emmagatzemat en la base de dades que rep paràmetres i torna un únic valor. El criteri RA5.f demana definir-les. Es crea una funció quan hi ha un càlcul que es repetix i que es vol escriure una sola vegada: la qualificació en text d’una nota, l’edat a partir d’una data, la mitjana d’un alumne.

El seu gran avantatge sobre un procediment és que es pot usar dins d’una consulta SQL, com qualsevol funció del gestor:

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

8.2 Sintaxi

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

8.3 Dues funcions d’EduGest: fn_calificacion i fn_edad

La primera convertix una nota en la seua qualificació en text. És la regla que ja vam vore en la vista v_acta (UD07), ara escrita una sola vegada:

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 segona calcula els anys complits. Té un paràmetre amb valor per defecte: si no s’indica la data de referència, usa la d’hui.

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 tot va bé, SQLcl respon Function FN_CALIFICACION compiled. Si hi ha errors de compilació, els mostra amb SHOW ERRORS (§2.5).

8.4 Usar una funció

Des de SQL, en el SELECT, el WHERE o l’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 files

Des de PL/SQL, en una assignació o una expressió:

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

Els arguments es poden passar per posició (en l’ordre de la declaració) o per nom amb =>, que permet saltar-se els opcionals i millora la llegibilitat:

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ó amb consulta: fn_nombre_modulo

Una funció pot llegir de les taules. Esta torna el nom d’un mòdul, o NULL si no existix, en lloc de propagar l’error al qui crida:

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ó de disseny: «no existix» no és 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è fa una funció quan no troba la dada és una decisió de disseny que has de documentar: tornar NULL (còmode en una consulta) o llançar un error (obliga el qui crida a tractar-lo). En la pràctica 9.2, fn_nombre_completo torna NULL; en el procediment de matrícula (§10.5), un alumne inexistent és un error.

8.6 Restriccions de les funcions cridades des de SQL

Una funció que s’executa dins d’un SELECT està subjecta a regles, perquè Oracle ha de poder avaluar-la moltes vegades i en l’ordre que més li convinga:

Marca amb DETERMINISTIC les funcions pures com fn_calificacion; així Oracle pot reutilitzar el resultat per a arguments repetits i, a més, és requisit per a usar-les en un índex basat en funcions.

8.7 Gestionar les funcions

-- Quines funcions tinc i en quin estat estan?
SELECT object_name, status FROM user_objects WHERE object_type = 'FUNCTION' ORDER BY object_name;

-- Veure el codi font emmagatzemat
SELECT text FROM user_source WHERE name = 'FN_EDAD' ORDER BY line;

-- Eliminar-la
DROP FUNCTION fn_edad;

Si una funció depén d’una taula i esta canvia (per exemple, ALTER TABLE), Oracle la marca INVALID i la recompila sola en el pròxim ús; per a forçar-ho: ALTER FUNCTION fn_edad COMPILE;.

9. Excepcions

9.1 Què és una excepció

Una excepció és un error que ocorre durant l’execució d’un bloc. Quan es produïx, el flux normal s’interromp: Oracle abandona les instruccions restants de la secció BEGIN i busca un manejador en la secció EXCEPTION.

BEGIN
    instrucció 1;          ← s'executa
    instrucció 2;          ← error! es llança una excepció
    instrucció 3;          ← NO s'executa
EXCEPTION
    WHEN una_excepcion THEN ← Oracle busca ací, de dalt a baix, el primer WHEN que coincidisca
        tractament;
    WHEN OTHERS THEN        ← comodí: qualsevol altra excepció
        tractament;
END;

El criteri RA5.j demana utilitzar excepcions. N’hi ha tres classes:

9.2 Excepcions predefinides

Un bloc que calcula les hores de falta per alumne d’un grup. El grup 2ASIR existix, però no té alumnat:

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

I un INSERT que viola la clau primària 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 cas de NO_DATA_FOUND i TOO_MANY_ROWS el tens pas a pas en el laboratori de l’apartat 7.6.

9.3 OTHERS, SQLCODE y SQLERRM

Dins d’un manejador, dues funcions descriuen l’error que s’està tractant:

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; és el pitjor codi que es pot escriure en PL/SQL. Es traga qualsevol error, fins i tot un d’inesperat, i deixa la base de dades en un estat incert sense deixar rastre. Un manejador OTHERS només és acceptable si registra l’error i el torna a llançar amb RAISE;:

EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error ' || SQLCODE || ': ' || SQLERRM);   -- o, millor, a una taula de registre
        RAISE;                                                          -- el MATEIX error continua el seu camí

SQLERRM està limitat a 512 bytes i no inclou on ha ocorregut l’error. Per a depurar, DBMS_UTILITY.FORMAT_ERROR_STACK torna la pila completa i DBMS_UTILITY.FORMAT_ERROR_BACKTRACE indica els números de línia de cada subprograma pel qual ha passat.

9.4 Excepcions definides per l’usuari

Quan l’«anomalia» és de negoci i no del gestor, es declara una excepció pròpia en la secció DECLARE, es llança amb RAISE i es tracta com les altres:

DECLARE
    e_sin_notas  EXCEPTION;                     -- 1. es 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. es llança
    END IF;
    DBMS_OUTPUT.PUT_LINE('El alumno 30 tiene ' || v_notas || ' notas');
EXCEPTION
    WHEN e_sin_notas THEN                       -- 3. es tracta
        DBMS_OUTPUT.PUT_LINE('El alumno 30 no tiene ninguna nota');
END;
/

El alumno 30 no tiene ninguna nota

Una excepció declarada així només existix dins del seu bloc. Si no es tracta, en arribar al client es mostra com ORA-06510: PL/SQL: unhandled user-defined exception, un missatge que no ajuda ningú: per això, per a errors que han d’arribar a l’aplicació, es prefereix RAISE_APPLICATION_ERROR (§9.6).

9.5 PRAGMA EXCEPTION_INIT: posar-li nom a un error d’Oracle

Hi ha molts errors ORA- que no tenen nom predefinit. PRAGMA EXCEPTION_INIT associa un nom propi a un codi, per a poder tractar-lo en un WHEN. Un cas típic d’EduGest: esborrar un professor que encara imparteix mòduls (ORA-02292, clau aliena sense 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 segon argument és el codi amb signe negatiu (-2292, no 2292). Altres codis que s’associen amb freqüència:

9.6 RAISE_APPLICATION_ERROR: errors de negoci cap al client

RAISE_APPLICATION_ERROR(código, mensaje) detén el subprograma i torna al qui crida un error amb el codi i el missatge que tu decidisques. És la forma estàndard de comunicar a una aplicació que s’ha incomplit una regla de negoci.

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àleg de codis per al projecte i documenta’l, igual que una taula d’errors HTTP. Així l’aplicació pot reaccionar al codi (-20013 → «ja estàs matriculat») i no analitzar el text. EduGest usa:

Codi	Significat	On
-20010	El mòdul no és del cicle de l’alumne (R2)	pr_matricular
-20011	Convocatòries esgotades (màxim 4)	pr_matricular
-20012	Alumne o mòdul inexistent, o alumne sense grup	pr_matricular
-20013	Alumne ja matriculat del mòdul en eixe curs	pr_matricular
-20020	El cap no pertany al departament (R1)	trg_jefe_departamento
-20021	Professor cap que canvia de departament (R1)	trg_profesor_cambio_dpto
-20022	Falta anterior a la data de matrícula (R3)	trg_falta_fecha
-20030	Un professor supera les 20 hores setmanals (R4)	trg_imparte_max_horas
-20040	Un grup supera els 30 alumnes (R5)	trg_grupo_max_alumnos

9.7 Blocs aniuats: limitar l’abast d’un error

Un SELECT ... INTO que no troba dades enmig d’un bucle avorta tot el bucle. Si el que es vol és tractar el cas i continuar amb la fila següent, la solució és embolicar la part arriscada en el seu propi bloc:

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                                              -- bloc interior, un per volta
            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

Dues particularitats que sorprenen:

1. Una excepció en la secció DECLARE no la tracta el manejador del mateix bloc, sinó el del bloc exterior. Amb v_corto VARCHAR2(3) := 'Sala'; el bloc falla amb ORA-06502 encara que tinga un WHEN VALUE_ERROR: encara no havia començat a executar-se.
2. Una excepció dins d’un manejador tampoc la tracta el mateix bloc: puja a l’exterior.

9.8 Excepcions i transaccions

Un error no fa ROLLBACK de la transacció. El que ocorre depén de si es tracta 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;                        -- desfà també el primer DELETE
        RAISE;
END;
/

No depengues del comportament implícit. Un subprograma que fa diverses modificacions i tracta els seus propis errors ha de decidir explícitament què es conserva i què es desfà, i qui el crida ho ha de saber. En la pràctica 9.8 el paquet de secretaria usa exactament esta tècnica.

10. Procediments emmagatzemats

10.1 Què és un procediment

Un procediment emmagatzemat és un subprograma amb nom, guardat i compilat en la base de dades, que realitza una acció i no torna valor (la informació d’eixida viatja pels paràmetres OUT). S’invoca des d’un client, des d’un altre subprograma o des d’una tasca programada.

CREATE [OR REPLACE] PROCEDURE nombre
    [(parámetro [IN | OUT | IN OUT] tipo [DEFAULT valor], ...)]
[AUTHID DEFINER | CURRENT_USER]
IS | AS
    -- declaracions locals
BEGIN
    -- instruccions
[EXCEPTION
    -- manejadors]
END [nombre];

L’estructura és la del bloc anònim, amb la capçalera CREATE PROCEDURE en lloc de DECLARE: les declaracions locals s’escriuen entre IS i BEGIN, sense la paraula DECLARE.

10.2 Paràmetres IN, OUT i IN OUT

Un procediment que torna dades d’un mòdul mitjançant paràmetres 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;
/