Salta el contingut
 

Exercici: PL/pgSQL

Joan Puigcerver Ibáñez

j.puigcerveribanez@edu.gva.es

Llicència: CC BY-NC-SA 4.0

(Reconeixement - NoComercial - CompartirIgual) 🅭

License

Exercici: PL/pgSQL

Exercici 1: Procediments i paràmetres

  1. Crea un procediment anomenat salari_mig_departament que reba:

    • Un identificador de departament (IN)
    • El salari mitjà del departament (OUT)

  2. Dins del procediment:

    • Calcula el salari mitjà dels empleats del departament indicat.
    • Retorna el valor mitjançant el paràmetre d'eixida.

  3. Escriu una sentència CALL per:

    • Cridar el procediment.
    • Mostrar el salari mitjà obtingut.
Solució 
CREATE OR REPLACE PROCEDURE salari_mig_departament(
    p_dept_id  IN NUMERIC,
    p_sal_mig  OUT NUMERIC
) AS $$
BEGIN
    SELECT AVG(salary)
    INTO p_sal_mig
    FROM employees
    WHERE center_id = p_dept_id;
END;
$$ LANGUAGE plpgsql;

Crida del procediment:

CALL salari_mig_departament(1, NULL);

Exercici 2: Funcions i ús en SQL

  1. Crea una funció anomenada calcular_bonus que:

    • Reba un salari com a paràmetre (IN).
    • Retorne un bonus segons la següent regla:
      • Menor de 30000 → 10%
      • Major o igual a 30000 → 5%

  2. Escriu una sentència SELECT sobre la taula employees que mostre:

    • Nom de l'empleat
    • Salari actual
    • Bonus calculat per la funció
    • Salari total (salari + bonus)
Solució 
CREATE OR REPLACE FUNCTION calcular_bonus(
    p_salary NUMERIC
) RETURNS NUMERIC AS $$
BEGIN
    IF p_salary < 30000 THEN
        RETURN p_salary * 0.10;
    ELSE
        RETURN p_salary * 0.05;
    END IF;
END;
$$ LANGUAGE plpgsql;

Consulta SQL:

SELECT
    first_name,
    salary AS salari_actual,
    calcular_bonus(salary) AS bonus,
    salary + calcular_bonus(salary) AS salari_total
FROM employees;

Exercici 3: Cursors i control de flux

  1. Crea un bloc amb un cursor explícit que:

    • Recórrega tots els empleats de la taula employees.

  2. Per a cada empleat:

    • Mostra el nom i el salari.
    • Si el salari és inferior a 25000, mostra el missatge: "Salari baix"
    • En cas contrari, mostra: "Salari adequat"

  3. Al final del procés, mostra quants empleats tenen salari baix.

Solució 
DO $$
DECLARE
    c_emps CURSOR FOR
        SELECT first_name, salary FROM employees;
    r_emp      RECORD;
    v_comptador NUMERIC := 0;
BEGIN
    OPEN c_emps;
    LOOP
        FETCH c_emps INTO r_emp;
        EXIT WHEN NOT FOUND;

        IF r_emp.salary < 25000 THEN
            RAISE NOTICE '% - Salari baix', r_emp.first_name;
            v_comptador := v_comptador + 1;
        ELSE
            RAISE NOTICE '% - Salari adequat', r_emp.first_name;
        END IF;
    END LOOP;
    CLOSE c_emps;

    RAISE NOTICE 'Total empleats amb salari baix: %', v_comptador;
END;
$$;

Exercici 4: Cas pràctic integrador (administració)

  1. Crea un procediment anomenat revisar_salaris que:

    • No reba paràmetres.

  2. El procediment haurà de:

    • Recórrer els empleats amb un cursor.
    • Incrementar un 5% el salari dels empleats amb salari inferior a 20000.
    • Mostrar per pantalla:
      • Nom de l'empleat
      • Salari anterior
      • Salari nou

  3. Inclou:

    • Control d'errors amb EXCEPTION.
    • Confirmació dels canvis amb COMMIT.

    Els procediments (a diferència de les funcions) poden executar COMMIT/ROLLBACK en PostgreSQL

Solució 
CREATE OR REPLACE PROCEDURE revisar_salaris() AS $$
DECLARE
    c_emps CURSOR FOR
        SELECT employee_id, first_name, salary
        FROM employees;
    r_emp        RECORD;
    v_nou_salari NUMERIC;
BEGIN
    OPEN c_emps;
    LOOP
        FETCH c_emps INTO r_emp;
        EXIT WHEN NOT FOUND;

        IF r_emp.salary < 20000 THEN
            v_nou_salari := r_emp.salary * 1.05;

            UPDATE employees
            SET salary = v_nou_salari
            WHERE employee_id = r_emp.employee_id;

            RAISE NOTICE '% | Anterior: % | Nou: %',
                r_emp.first_name, r_emp.salary, v_nou_salari;
        END IF;
    END LOOP;
    CLOSE c_emps;

    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE NOTICE 'Error: %', SQLERRM;
END;
$$ LANGUAGE plpgsql;

CALL revisar_salaris();
📌 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.
 
CC BY-NC-SA 4.0 (Reconeixement - NoComercial - CompartirIgual) 🅭 Joan Puigcerver Ibáñez