Salta el contingut
 

Exercici: PL/SQL

José Manuel Pérez Barberá

jm.perezbarbera@edu.gva.es

Llicència: CC BY-NC-SA 4.0

(Reconeixement - NoComercial - CompartirIgual) 🅭

License

Exercici: PL/SQL

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 un bloc PL/SQL per:

    • Cridar el procediment.
    • Mostrar el salari mitjà obtingut.
Solució 
CREATE OR REPLACE PROCEDURE salari_mig_departament (
    p_dept_id IN NUMBER,
    p_sal_mig OUT NUMBER
) IS
BEGIN
    SELECT AVG(salary)
    INTO p_sal_mig
    FROM employees
    WHERE department_id = p_dept_id;
END;
/

Crida del procediment:

DECLARE
    v_mig NUMBER;
BEGIN
    salari_mig_departament(50, v_mig);
    DBMS_OUTPUT.PUT_LINE('Salari mig: ' || v_mig);
END;
/

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 IN NUMBER
) RETURN NUMBER IS
BEGIN
    IF p_salary < 30000 THEN
        RETURN p_salary * 0.10;
    ELSE
        RETURN p_salary * 0.05;
    END IF;
END;
/

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: Cursores i control de flux

  1. Crea un bloc PL/SQL 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ó 
DECLARE
    CURSOR c_emps IS
        SELECT first_name, salary FROM employees;
    r_emp c_emps%ROWTYPE;
    v_comptador NUMBER := 0;
BEGIN
    OPEN c_emps;
    LOOP
        FETCH c_emps INTO r_emp;
        EXIT WHEN c_emps%NOTFOUND;

        IF r_emp.salary < 25000 THEN
            DBMS_OUTPUT.PUT_LINE(
                r_emp.first_name || ' - Salari baix'
            );
            v_comptador := v_comptador + 1;
        ELSE
            DBMS_OUTPUT.PUT_LINE(
                r_emp.first_name || ' - Salari adequat'
            );
        END IF;
    END LOOP;
    CLOSE c_emps;

    DBMS_OUTPUT.PUT_LINE(
        '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
Solució 
CREATE OR REPLACE PROCEDURE revisar_salaris IS
    CURSOR c_emps IS
        SELECT employee_id, first_name, salary
        FROM employees;
    r_emp c_emps%ROWTYPE;
    v_nou_salari NUMBER;
BEGIN
    OPEN c_emps;
    LOOP
        FETCH c_emps INTO r_emp;
        EXIT WHEN c_emps%NOTFOUND;

        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;

            DBMS_OUTPUT.PUT_LINE(
                r_emp.first_name || 
                ' | Anterior: ' || r_emp.salary ||
                ' | Nou: ' || v_nou_salari
            );
        END IF;
    END LOOP;
    CLOSE c_emps;

    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/
📌 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) 🅭 José Manuel Pérez Barberá