El Projecte | Sobre nosaltres | Contribuir | Donacions | Llicència
HOME Els meus cursos Finalitzats
◄ Anterior
◄

Sentència FORALL

Següent ►
►

La sentència FORALL en PL/SQL és una característica poderosa que permet executar operacions DML (Data Manipulation Language) en lots, millorant significativament el rendiment en comparació amb l'execució de les mateixes operacions dins d'un bucle FOR. Aquesta sentència és especialment útil quan es treballa amb grans volums de dades.

Conceptes Clau

  • Execució en Lots: La sentència FORALL permet executar múltiples operacions DML (INSERT, UPDATE, DELETE) en una sola instrucció, reduint el nombre de commutacions de context entre PL/SQL i SQL.
  • Índexs de Col·lecció: La sentència FORALL utilitza col·leccions (com ara arrays associatius, taules indexades o VARRAYs) per emmagatzemar els valors que s'utilitzaran en les operacions DML.
  • Millora del Rendiment: En reduir el nombre de commutacions de context, la sentència FORALL pot millorar significativament el rendiment de les operacions DML massives.

Sintaxi Bàsica

FORALL index IN lower_bound..upper_bound
    sql_statement;
  • index: És una variable que recorre els elements de la col·lecció.
  • lower_bound i upper_bound: Defineixen el rang d'índexs de la col·lecció que es recorreran.
  • sql_statement: És l'operació DML que s'executarà per a cada element de la col·lecció.

Exemple Pràctic

Suposem que tenim una taula employees i volem actualitzar els salaris de diversos empleats. Utilitzarem la sentència FORALL per fer-ho de manera eficient.

Creació de la Taula i Inserció de Dades

CREATE TABLE employees (
    employee_id NUMBER PRIMARY KEY,
    salary NUMBER
);

INSERT INTO employees (employee_id, salary) VALUES (1, 5000);
INSERT INTO employees (employee_id, salary) VALUES (2, 6000);
INSERT INTO employees (employee_id, salary) VALUES (3, 7000);
COMMIT;

Actualització de Salaris amb FORALL

DECLARE
    TYPE salary_array IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
    new_salaries salary_array;
BEGIN
    -- Emplenem la col·lecció amb els nous salaris
    new_salaries(1) := 5500;
    new_salaries(2) := 6500;
    new_salaries(3) := 7500;

    -- Utilitzem FORALL per actualitzar els salaris
    FORALL i IN new_salaries.FIRST..new_salaries.LAST
        UPDATE employees
        SET salary = new_salaries(i)
        WHERE employee_id = i;

    COMMIT;
END;
/

Explicació del Codi

  1. Declaració de la Col·lecció: Declarem una col·lecció salary_array per emmagatzemar els nous salaris.
  2. Emplenament de la Col·lecció: Assignem els nous salaris als elements de la col·lecció.
  3. Sentència FORALL: Utilitzem FORALL per actualitzar els salaris en la taula employees de manera eficient.

Exercici Pràctic

Enunciat

Crea una taula products amb les columnes product_id i price. Utilitza la sentència FORALL per actualitzar els preus de diversos productes.

Solució

-- Creació de la taula
CREATE TABLE products (
    product_id NUMBER PRIMARY KEY,
    price NUMBER
);

-- Inserció de dades
INSERT INTO products (product_id, price) VALUES (1, 100);
INSERT INTO products (product_id, price) VALUES (2, 200);
INSERT INTO products (product_id, price) VALUES (3, 300);
COMMIT;

-- Actualització de preus amb FORALL
DECLARE
    TYPE price_array IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
    new_prices price_array;
BEGIN
    -- Emplenem la col·lecció amb els nous preus
    new_prices(1) := 110;
    new_prices(2) := 220;
    new_prices(3) := 330;

    -- Utilitzem FORALL per actualitzar els preus
    FORALL i IN new_prices.FIRST..new_prices.LAST
        UPDATE products
        SET price = new_prices(i)
        WHERE product_id = i;

    COMMIT;
END;
/

Errors Comuns i Consells

  • Índexs No Contigus: Assegura't que els índexs de la col·lecció siguin contigus. Si no ho són, utilitza la clàusula INDICES OF o VALUES OF.
  • Gestió d'Errors: Utilitza la clàusula SAVE EXCEPTIONS per capturar i gestionar errors durant l'execució de la sentència FORALL.

Exemple amb SAVE EXCEPTIONS

DECLARE
    TYPE price_array IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
    new_prices price_array;
    errors EXCEPTION;
    PRAGMA EXCEPTION_INIT(errors, -24381);
BEGIN
    -- Emplenem la col·lecció amb els nous preus
    new_prices(1) := 110;
    new_prices(2) := 220;
    new_prices(3) := 330;

    -- Utilitzem FORALL amb SAVE EXCEPTIONS
    FORALL i IN new_prices.FIRST..new_prices.LAST SAVE EXCEPTIONS
        UPDATE products
        SET price = new_prices(i)
        WHERE product_id = i;

    COMMIT;
EXCEPTION
    WHEN errors THEN
        FOR i IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
            DBMS_OUTPUT.PUT_LINE('Error en l\'índex: ' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
            DBMS_OUTPUT.PUT_LINE('Codi d\'error: ' || SQL%BULK_EXCEPTIONS(i).ERROR_CODE);
        END LOOP;
END;
/

Conclusió

La sentència FORALL és una eina essencial per a l'optimització de les operacions DML en PL/SQL. Permet executar operacions en lots, millorant el rendiment i reduint el temps d'execució. Amb la pràctica i la comprensió dels seus conceptes clau, podràs utilitzar FORALL per gestionar grans volums de dades de manera eficient.

◄ Anterior
◄
Següent ►
►
El Projecte | Sobre nosaltres | Contribuir | Donacions | Llicència
© Copyright 2024. Tots els drets reservats