PL/SQL

José Manuel Pérez Barberà

jm.perezbarbera@edu.gva.es

Llicència: CC BY-NC-SA 4.0

(Reconeixement - NoComercial - CompartirIgual) 🅭

Introducció¶

PL/SQL (Procedural Language/SQL) és el llenguatge procedural d'Oracle. Permet combinar sentències SQL amb les estructures pròpies de la programació, com les variables, els condicionals o els bucles.

Gràcies a això, es poden implementar lògica de negoci, validacions, processos repetitius i gestió d'errors directament dins de la base de dades, sense dependre d'una aplicació externa.

SQL treballa amb dades; PL/SQL treballa amb processos.

SQL i PL/SQL¶

SQL és un llenguatge declaratiu: cada sentència indica quin resultat es vol, però no com obtindre'l. PL/SQL, en canvi, és procedural: permet encadenar sentències, prendre decisions i repetir accions. La taula següent resumeix les diferències principals:

SQL	PL/SQL
Declaratiu	Procedural
Orientat a dades	Orientat a processos
Sense control de flux	`IF`, bucles, excepcions
Sense gestió d'errors	Gestió d'excepcions
Sentències independents	Blocs de codi

Exercici: SQL o PL/SQL?

Indica amb quin llenguatge resoldries cada cas:

Llistar els empleats.
Validar un salari abans d'inserir-lo.
Calcular un bonus segons diverses condicions.

Solució

SQL.
PL/SQL.
PL/SQL.

Estructura d'un bloc PL/SQL¶

Tot programa PL/SQL s'organitza en blocs, que tenen fins a quatre seccions:

DECLARE (opcional): declaració de variables.
BEGIN (obligatòria): codi executable.
EXCEPTION (opcional): gestió d'errors.
END; (obligatòria): final del bloc.

El bloc més senzill només té la part executable. Per exemple, aquest bloc mostra un missatge per pantalla amb DBMS_OUTPUT.PUT_LINE:

BEGIN
    DBMS_OUTPUT.PUT_LINE('Hola PL/SQL');
END;

Variables i tipus de dades¶

Les variables permeten emmagatzemar valors temporalment durant l'execució del programa. Es declaren en la secció DECLARE, amb un nom i un tipus de dades. Els tipus més habituals són NUMBER, VARCHAR2, DATE i BOOLEAN:

DECLARE
    v_name   VARCHAR2(50);
    v_salary NUMBER;

Tipus ancorats a la base de dades¶

Si una variable ha de guardar el valor d'una columna, és millor no escriure'n el tipus a mà. En lloc d'això, es pot ancorar el tipus a la base de dades, de manera que, si la columna canvia, la variable s'adapta automàticament:

%TYPE: pren el tipus d'una columna.
%ROWTYPE: pren l'estructura d'una fila completa d'una taula.

v_salary employees.salary%TYPE;

Exercici: Variables i assignació

Declara una variable v_nombre, assigna-li el teu nom i mostra el missatge Hola <nom>.

Solució

DECLARE
    v_nombre VARCHAR2(50);
BEGIN
    v_nombre := 'Juan';
    DBMS_OUTPUT.PUT_LINE('Hola ' || v_nombre);
END;
/

Ús de SQL dins de PL/SQL (`SELECT INTO`)¶

PL/SQL permet executar sentències SQL directament. Per guardar el resultat d'una consulta en variables, s'utilitza la clàusula INTO, que només es pot utilitzar quan la consulta retorna una sola fila:

SELECT salary
INTO v_salary
FROM employees
WHERE employee_id = 100;

Si la consulta no retorna cap fila o en retorna més d'una, es produeixen els errors NO_DATA_FOUND i TOO_MANY_ROWS, respectivament (vegeu Gestió d'excepcions).

Preparació de l'esquema dels exemples

Els exemples d'aquests apunts utilitzen l'esquema següent:

ud4/ScriptDBEmployees.sql

--------------------------------------------------
-- LIMPIEZA PREVIA (por si existen)
--------------------------------------------------

BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE employees CASCADE CONSTRAINTS';
EXCEPTION WHEN OTHERS THEN NULL;
END;
/

BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE positions CASCADE CONSTRAINTS';
EXCEPTION WHEN OTHERS THEN NULL;
END;
/

BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE work_centers CASCADE CONSTRAINTS';
EXCEPTION WHEN OTHERS THEN NULL;
END;
/

--------------------------------------------------
-- TABLA: WORK_CENTERS
--------------------------------------------------

CREATE TABLE work_centers (
    center_id      NUMBER PRIMARY KEY,
    center_name    VARCHAR2(100) NOT NULL,
    city           VARCHAR2(50),
    phone          VARCHAR2(20)
);

--------------------------------------------------
-- TABLA: POSITIONS
--------------------------------------------------

CREATE TABLE positions (
    position_id    NUMBER PRIMARY KEY,
    position_name  VARCHAR2(50) NOT NULL,
    base_salary    NUMBER(8,2)
);

--------------------------------------------------
-- TABLA: EMPLOYEES
--------------------------------------------------

CREATE TABLE employees (
    employee_id    NUMBER PRIMARY KEY,
    first_name     VARCHAR2(50),
    last_name      VARCHAR2(50),
    hire_date      DATE,
    salary         NUMBER(8,2),
    center_id      NUMBER,
    position_id    NUMBER,
    email      VARCHAR2(50),
    CONSTRAINT fk_employee_center
        FOREIGN KEY (center_id)
        REFERENCES work_centers(center_id),
    CONSTRAINT fk_employee_position
        FOREIGN KEY (position_id)
        REFERENCES positions(position_id)
);

--------------------------------------------------
-- DATOS DE EJEMPLO: WORK_CENTERS
--------------------------------------------------

INSERT INTO work_centers VALUES (1, 'Centro Madrid Norte', 'Madrid', '910000001');
INSERT INTO work_centers VALUES (2, 'Centro Barcelona Este', 'Barcelona', '930000002');
INSERT INTO work_centers VALUES (3, 'Centro Sevilla Sur', 'Sevilla', '950000003');

--------------------------------------------------
-- DATOS DE EJEMPLO: POSITIONS
--------------------------------------------------

INSERT INTO positions VALUES (1, 'Desarrollador Junior', 18000);
INSERT INTO positions VALUES (2, 'Desarrollador Senior', 32000);
INSERT INTO positions VALUES (3, 'Administrador de Sistemas', 30000);
INSERT INTO positions VALUES (4, 'Jefe de Proyecto', 38000);

--------------------------------------------------
-- DATOS DE EJEMPLO: EMPLOYEES
--------------------------------------------------

INSERT INTO employees VALUES
(100, 'Juan', 'Pérez', DATE '2020-02-15', 18500, 1, 1,'juan@work.es');

INSERT INTO employees VALUES
(101, 'Ana', 'García', DATE '2019-06-10', 33000, 1, 2,'ana@work.es');

INSERT INTO employees VALUES
(102, 'Luis', 'Martín', DATE '2021-01-20', 30000, 2, 3,'luis@work.es');

INSERT INTO employees VALUES
(103, 'María', 'López', DATE '2018-09-05', 39000, 2, 4,'maria@work.es');

INSERT INTO employees VALUES
(104, 'Carlos', 'Sánchez', DATE '2022-03-12', 18000, 3, 1,'carlos@work.es');

INSERT INTO employees VALUES
(105, 'Elena', 'Ruiz', DATE '2020-11-30', 32000, 3, 2,'elena@work.es');

COMMIT;

Exercici: SELECT INTO

Obtín el nombre total d'empleats de la taula employees.

Solució

DECLARE
    v_total NUMBER;
BEGIN
    SELECT COUNT(*)
    INTO v_total
    FROM employees;

    DBMS_OUTPUT.PUT_LINE('Total empleats: ' || v_total);
END;
/

Obtín el salari màxim de la taula employees.

Solució

DECLARE
    v_max_sal NUMBER;
BEGIN
    SELECT MAX(salary)
    INTO v_max_sal
    FROM employees;

    DBMS_OUTPUT.PUT_LINE('Salari màxim: ' || v_max_sal);
END;
/

Gestió d'excepcions¶

Les excepcions permeten controlar els errors que es produeixen en temps d'execució. Quan es produeix un error dins del bloc, l'execució salta a la secció EXCEPTION, on es pot tractar segons el tipus d'error:

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('No s''han trobat dades');
END;

Les excepcions més habituals són:

NO_DATA_FOUND: la consulta SELECT INTO no ha retornat cap fila.
TOO_MANY_ROWS: la consulta SELECT INTO ha retornat més d'una fila.
ZERO_DIVIDE: s'ha intentat dividir entre zero.
OTHERS: qualsevol altre error, amb WHEN OTHERS.

Exercici: SELECT INTO amb excepcions

Obtín el salari de l'empleat amb identificador 100 de la taula employees i tracta els casos en què no existeix o n'hi ha més d'un.

Solució

DECLARE
    v_salari NUMBER;
BEGIN
    SELECT SALARY
    INTO v_salari
    FROM EMPLOYEES
    where EMPLOYEE_ID = 100;

    DBMS_OUTPUT.PUT_LINE('Salari de l''empleat: ' || v_salari);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Empleat no trobat');
    WHEN TOO_MANY_ROWS THEN
        DBMS_OUTPUT.PUT_LINE('Hi ha més d''un empleat amb eixe ID');
END;
/

Estructures de control¶

PL/SQL permet controlar el flux del programa mitjançant estructures condicionals i bucles, que decideixen quines sentències s'executen i quantes vegades.

Condicionals (`IF`)¶

La sentència IF executa un bloc de codi o un altre segons es complisca una condició:

IF condicio THEN
   ...
ELSIF altra_condicio THEN
   ...
ELSE
   ...
END IF;

Bucles¶

Els bucles repeteixen un bloc de codi. El bucle FOR és el més utilitzat, ja que recorre un rang de valors o el resultat d'una consulta sense haver de controlar cap comptador:

FOR i IN 1..10 LOOP
    ...
END LOOP;

El bucle WHILE, en canvi, es repeteix mentre es complisca una condició:

WHILE condicio LOOP
    ...
END LOOP;

Exercici: IF/ELSE i bucles

Crea un bloc PL/SQL que recórrega els nombres de l'1 al 10 i, per a cada nombre, mostre «Parell» o «Senar», segons corresponga.

Solució

DECLARE
    v_num NUMBER;
BEGIN
    FOR v_num IN 1..10 LOOP
        IF MOD(v_num, 2) = 0 THEN
            DBMS_OUTPUT.PUT_LINE(v_num || ' és parell');
        ELSE
            DBMS_OUTPUT.PUT_LINE(v_num || ' és senar');
        END IF;
    END LOOP;
END;
/

Exercici: SELECT INTO, IF/ELSE i bucle

Crea un bloc PL/SQL que:

Obtinga el salari mitjà de la taula employees.
Recórrega tots els empleats.
Per a cada empleat, mostre el nom i el salari, i indique si està «Per damunt de la mitjana» o «Per davall de la mitjana».

Solució

DECLARE
    v_avg_salary NUMBER;
BEGIN
    -- Obtenir el salari mitjà
    SELECT AVG(salary)
    INTO v_avg_salary
    FROM employees;

    -- Recórrer els empleats
    FOR r IN (SELECT first_name, salary FROM employees) LOOP
        IF r.salary >= v_avg_salary THEN
            DBMS_OUTPUT.PUT_LINE(
                r.first_name || ' - ' || r.salary || ' (Per damunt de la mitjana)'
            );
        ELSE
            DBMS_OUTPUT.PUT_LINE(
                r.first_name || ' - ' || r.salary || ' (Per davall de la mitjana)'
            );
        END IF;
    END LOOP;
END;
/

Procediments i funcions¶

Els blocs anònims s'executen una vegada i desapareixen. Per reutilitzar el codi, PL/SQL permet crear subprogrames, que es guarden en la base de dades amb un nom: els procediments i les funcions.

Procediments (`CREATE PROCEDURE`)¶

Un procediment és un subprograma que executa accions, però no retorna cap valor. S'identifica per un nom i pot rebre paràmetres d'entrada i d'eixida:

CREATE OR REPLACE PROCEDURE saludar(p_nombre IN VARCHAR2) IS
BEGIN
  DBMS_OUTPUT.PUT_LINE('Hola ' || p_nombre);
END;

Els paràmetres poden ser de tres tipus, segons la direcció en què passen les dades:

IN: només d'entrada. És el tipus per defecte i el seu valor no es pot modificar dins del subprograma. S'utilitza tant en funcions com en procediments.
OUT: només d'eixida. Serveix per retornar valors, no té valor inicial i només s'utilitza en procediments.
IN OUT: d'entrada i d'eixida. Rep un valor i en retorna un altre, i només s'utilitza en procediments.

Funcions (`CREATE FUNCTION`)¶

Una funció és un subprograma que sempre retorna un valor. Com que retorna un valor, es pot utilitzar dins de sentències SQL:

CREATE OR REPLACE FUNCTION augment_salari(p_sal NUMBER)
RETURN NUMBER
IS
BEGIN
    RETURN p_sal * 1.10;
END;
/

Una vegada creada, la funció es pot cridar des d'un bloc PL/SQL:

DECLARE
    v_resultat NUMBER;
BEGIN
    v_resultat := augment_salari(2000);
    DBMS_OUTPUT.PUT_LINE('Salari amb augment: ' || v_resultat);
END;
/

O bé des d'una consulta SQL, com qualsevol funció predefinida:

SELECT augment_salari(2000) FROM dual;
/

SELECT
    first_name || ' ' || last_name AS empleat,
    salary AS salari_actual,
    augment_salari(salary) AS salari_amb_increment
FROM employees;
/

Exercici: Procediment o funció?

Què utilitzaries per calcular un salari anual?

Solució

Una funció, ja que ha de retornar un valor.

Cursors en PL/SQL¶

Un cursor permet treballar amb el resultat d'una consulta, fila a fila. És especialment útil per a consultes que retornen múltiples files. Hi ha diversos tipus de cursors, segons qui els gestiona.

Cursor implícit¶

PL/SQL crea automàticament un cursor implícit quan s'executa una sentència SELECT INTO. Com que només funciona amb una fila, si la consulta en retorna més d'una, es produeix l'error TOO_MANY_ROWS:

DECLARE
    v_salary NUMBER;
BEGIN
    SELECT salary
    INTO v_salary
    FROM employees
    WHERE employee_id = 100;

    DBMS_OUTPUT.PUT_LINE('Salari: ' || v_salary);
END;
/

Cursor explícit (`CURSOR`)¶

Un cursor explícit es declara amb CURSOR i permet recórrer múltiples files. En aquest cas, cal obrir-lo (OPEN), recuperar-ne les files una a una (FETCH) i tancar-lo (CLOSE). L'atribut %NOTFOUND indica que ja no queden més files:

DECLARE
    CURSOR c_emps IS
        SELECT first_name, salary FROM employees;
    r_emp c_emps%ROWTYPE;
BEGIN
    OPEN c_emps;
    LOOP
        FETCH c_emps INTO r_emp;
        EXIT WHEN c_emps%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(r_emp.first_name || ' - ' || r_emp.salary);
    END LOOP;
    CLOSE c_emps;
END;
/

Cursor `FOR LOOP`¶

El cursor FOR LOOP és una forma simplificada del cursor explícit: obri el cursor, recupera les files i el tanca automàticament. Per això, és la forma recomanada per recórrer totes les files d'una consulta:

BEGIN
    FOR r IN (SELECT first_name, salary FROM employees) LOOP
        DBMS_OUTPUT.PUT_LINE(r.first_name || ' - ' || r.salary);
    END LOOP;
END;
/

Exercici: Cursor bàsic

Crea un cursor explícit que recórrega tots els empleats de la taula employees i mostre el nom i el salari de cada empleat.

Solució

DECLARE
    CURSOR c_emps IS
        SELECT first_name, salary FROM employees;
    r_emp c_emps%ROWTYPE;
BEGIN
    OPEN c_emps;
    LOOP
        FETCH c_emps INTO r_emp;
        EXIT WHEN c_emps%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(r_emp.first_name || ' - ' || r_emp.salary);
    END LOOP;
    CLOSE c_emps;
END;
/

Exercici: Cursor amb condició i càlcul

Crea un cursor explícit que:

Recórrega tots els empleats de la taula employees.
Calcule un bonus del 10 % per als empleats amb un salari inferior a 30000.
Mostre el nom, el salari i el bonus de cada empleat.

Solució

DECLARE
    CURSOR c_emps IS
        SELECT first_name, salary FROM employees;
    r_emp c_emps%ROWTYPE;
    v_bonus NUMBER;
BEGIN
    OPEN c_emps;
    LOOP
        FETCH c_emps INTO r_emp;
        EXIT WHEN c_emps%NOTFOUND;

        IF r_emp.salary < 30000 THEN
            v_bonus := r_emp.salary * 0.10;
        ELSE
            v_bonus := 0;
        END IF;

        DBMS_OUTPUT.PUT_LINE(
            r_emp.first_name || ' - ' ||
            r_emp.salary || ' - Bonus: ' || v_bonus
        );
    END LOOP;
    CLOSE c_emps;
END;
/

Exercici: Cursor amb UPDATE

Crea un cursor explícit que:

Recórrega tots els empleats de la taula employees.
Si el salari és inferior a 30000, calcule un bonus del 10 % i actualitze la taula amb aquest bonus.
Mostre el nom, el salari i el bonus aplicat.

Solució

DECLARE
    CURSOR c_emps IS
        SELECT employee_id, first_name, salary
        FROM employees;
    r_emp c_emps%ROWTYPE;
    v_bonus NUMBER;
BEGIN
    OPEN c_emps;
    LOOP
        FETCH c_emps INTO r_emp;
        EXIT WHEN c_emps%NOTFOUND;

        IF r_emp.salary < 30000 THEN
            v_bonus := r_emp.salary * 0.10;

            -- Actualitzar la taula amb el bonus
            UPDATE employees
            SET salary = salary + v_bonus
            WHERE employee_id = r_emp.employee_id;
        ELSE
            v_bonus := 0;
        END IF;

        DBMS_OUTPUT.PUT_LINE(
            r_emp.first_name || ' - Salari: ' || r_emp.salary ||
            ' - Bonus aplicat: ' || v_bonus
        );
    END LOOP;
    CLOSE c_emps;

    -- Confirmar els canvis
    COMMIT;
END;
/

Bones pràctiques en PL/SQL¶

Un codi PL/SQL fàcil de mantindre segueix unes quantes pautes:

Noms descriptius: les variables, els paràmetres i els subprogrames han de tindre noms clars.
Documentació: el codi ha d'estar comentat, especialment la lògica menys evident.
Gestió d'excepcions: els errors previsibles s'han de tractar explícitament.
Lògica mesurada: no s'ha d'abusar de la lògica en la base de dades.
Tipus ancorats: %TYPE i %ROWTYPE eviten errors quan canvia l'estructura de les taules.

Ús pràctic en el dia a dia¶

PL/SQL és molt útil en el treball diari amb Oracle DB. Alguns dels usos més comuns són:

Automatització de tasques: processos que es repeteixen periòdicament, com generar informes diaris, enviar alertes o netejar taules temporals.
Validacions de dades: comprovar que les dades inserides compleixen les regles de negoci abans d'acceptar-les, de manera que s'eviten errors i dades inconsistents.
Regles de negoci simples: aplicar descomptes automàtics, validar rangs de preus o comprovar dates de caducitat.
Processos batch: càlcul de salaris, actualització d'estocs o exportació de dades.
Manteniment i suport: tancar sessions bloquejants o actualitzar registres de manera massiva, sense necessitat d'una intervenció manual constant.

Exercici: Procediment per finalitzar una sessió

Crea un procediment que finalitze una sessió a partir del seu SID i el seu SERIAL#.

Solució

CREATE OR REPLACE PROCEDURE kill_session (
    p_sid    IN NUMBER,
    p_serial IN NUMBER
) IS
    v_sql   VARCHAR2(200);
BEGIN
    v_sql := 'ALTER SYSTEM KILL SESSION ''' ||
            p_sid || ',' || p_serial || ''' IMMEDIATE';

    EXECUTE IMMEDIATE v_sql;

    DBMS_OUTPUT.PUT_LINE(
        'Sessió ' || p_sid || ',' || p_serial || ' finalitzada'
    );

EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/

Exercici: Cas pràctic d'administració de sessions

Consulta les sessions actives i identifica si n'hi ha alguna que en bloqueja d'altres.
Utilitza el procediment kill_session per finalitzar la sessió bloquejant.

Solució

-- 1. Consultar les sessions bloquejades
SELECT s1.sid AS bloquejada_sid,
       s1.serial# AS bloquejada_serial,
       s1.username AS usuari,
       s2.sid AS bloquejant_sid,
       s2.username AS usuari_bloquejant
FROM v$lock l1
JOIN v$session s1 ON l1.sid = s1.sid
JOIN v$lock l2 ON l1.id1 = l2.id1 AND l1.id2 = l2.id2 AND l1.block = 0 AND l2.block = 1
JOIN v$session s2 ON l2.sid = s2.sid;

-- Suposem que la sessió bloquejant té SID = 123 i SERIAL# = 456

-- 2. Invocar el procediment per finalitzar-la
BEGIN
    kill_session(123, 456);
END;
/

-- Eixida esperada
-- Sessió 123,456 finalitzada

Exercici: Reflexió final

Per què no és recomanable posar tota la lògica de negoci en la base de dades?

Solució

Dificulta el manteniment, redueix la flexibilitat i acobla massa l'aplicació a la base de dades.

📌 Aquest document pot quedar desactualitzat després d’imprimir-lo. Pots consultar la versió més recent a la pàgina web.

🌿 Abans d’imprimir aquest document, considera si és realment necessari. Redueix el consum de paper i ajuda a protegir el nostre entorn.

PL/SQL

Introducció¶

SQL i PL/SQL¶

Estructura d'un bloc PL/SQL¶

Variables i tipus de dades¶

Tipus ancorats a la base de dades¶

Ús de SQL dins de PL/SQL (SELECT INTO)¶

Gestió d'excepcions¶

Estructures de control¶

Condicionals (IF)¶

Bucles¶

Procediments i funcions¶

Procediments (CREATE PROCEDURE)¶

Funcions (CREATE FUNCTION)¶

Cursors en PL/SQL¶

Cursor implícit¶

Cursor explícit (CURSOR)¶

Cursor FOR LOOP¶

Bones pràctiques en PL/SQL¶

Ús pràctic en el dia a dia¶

Ús de SQL dins de PL/SQL (`SELECT INTO`)¶

Condicionals (`IF`)¶

Procediments (`CREATE PROCEDURE`)¶

Funcions (`CREATE FUNCTION`)¶

Cursor explícit (`CURSOR`)¶

Cursor `FOR LOOP`¶