Salta el contingut

DAM - DAW - MP 0484 Bases de Dades - NF5

Pràctica 10 – Funcions

En aquesta pràctica, creareu funcions emmagatzemades; i les fareu servir des d’una comanda SQL o des d’un procediment emmagatzemat.

Per fer aquests exercicis cal tenir les següents taules creades (Compte que li deixem les PKs):

CREATE OR REPLACE TABLE EMPLEATS LIKE empresa.EMPLOYEES;
INSERT INTO EMPLEATS SELECT * FROM empresa.EMPLOYEES;
CREATE OR REPLACE TABLE DEPARTAMENTS AS SELECT * FROM empresa.DEPARTMENTS;
CREATE OR REPLACE TABLE FEINES LIKE empresa.JOBS;
INSERT INTO FEINES SELECT * FROM empresa.JOBS;

Canvieu el delimitador per a treballar millor (si ho feu des de la shell)

DELIMITER //

Es recomana, per possibles errades, treballar amb una transacció

START TRANSACTION //
SAVEPOINT P1 //
i així, en qualsevol moment poder desfer el que s’ha fet

ROLLBACK TO P1 //

  1. Creeu i utilitzeu la funció Q_FEINA per retornar el títol de feina a partir del codi de feina.

    1. Creeu una funció anomenada Q_FEINA per retornar el títol de feina (job_title).

    2. Utilitzeu la funció passant coma job_id el valor 'SA_REP'.

    Possible solucio 
    DELIMITER //
CREATE OR REPLACE FUNCTION Q_FEINA(p_job_id TYPE OF feines.job_id)
    RETURNS VARCHAR(35)
    READS SQL DATA
BEGIN
    DECLARE v_job_title TYPE OF feines.job_title;
    SELECT job_title INTO v_job_title
        FROM feines
        WHERE job_id = p_job_id;
    RETURN v_job_title;
END //
DELIMITER ;
SELECT Q_FEINA('SA_REP');
SELECT LAST_NAME, Q_FEINA(job_id) FROM EMPLEATS WHERE EMPLOYEE_ID < 200;

  2. Creeu una funció anomenada SOU_ANUAL per retornar el salari anual acceptant dos paràmetres: el salari i la comissió mensual.

    1. Creeu i invoqueu la funció SOU_ANUAL, passant els valors del salari mensual i comissió. El salari anual ve definit per la fórmula següent:

      (salary*12) + (commission_pct* salary*12)

    2. Utilitzeu la funció en una sentència SELECT sobre la taula EMPLEATS per al departament 80.

      +-----------+------------------------+
| LAST_NAME | SOU_ANUAL(employee_id) |
+-----------+------------------------+
| Zlotkey   |              151200.00 |
| Abel      |              171600.00 |
| Taylor    |              123840.00 |
+-----------+------------------------+
    Possible solució 
    DELIMITER //
CREATE OR REPLACE FUNCTION SOU_ANUAL(
    p_employee_id TYPE OF empleats.employee_id)
RETURNS DECIMAL(8,2)
BEGIN
    DECLARE v_salary TYPE OF empleats.salary;
    DECLARE v_commission_pct TYPE OF empleats.commission_pct;
    DECLARE v_souFinal TYPE OF empleats.salary DEFAULT 0;

    SELECT salary, commission_pct
        INTO v_salary, v_commission_pct
        FROM empleats
        WHERE employee_id = p_employee_id;
    IF v_salary IS NOT NULL THEN
        IF v_commission_pct IS NOT NULL THEN
        SET v_souFinal = v_commission_pct * v_salary * 12;
        END IF;
        SET v_souFinal = v_souFinal + (v_salary * 12);
    END IF;
    RETURN v_souFinal;
END //
DELIMITER ;
SELECT LAST_NAME, SOU_ANUAL(employee_id)
    FROM EMPLEATS
    WHERE DEPARTMENT_ID = 80;

  3. Creeu un procediment, NOU_EMP, per afegir un nou empleat a la taula EMPLEATS. El procediment ha de contenir una crida a la funció VALID_DEPTID per comprovar si l’identificador de departament especificat per al nou empleat existeix a la taula DEPARTAMENTS.

    1. Creeu la funció VALID_DEPTID per validar un identificador de departament especificat. La funció ha de retornar un valor BOOLEÀ.

    2. Creeu el procediment NOU_EMP per afegir un empleat a la taula EMPLEATS. S'ha d'afegir una nova fila a la taula EMPLEATS si la funció retorna TRUE. Si la funció retorna FALSE, s’ha d'alertar l'usuari amb un missatge d’error adequat i no es farà l’alta de l’empleat.

    3. Definiu els valors per defecte per a la majoria de paràmetres. La comissió per defecte és NULL, el salari per defecte és 1000, el número de departament predeterminat és 30, el treball per defecte és SA_REP i el valor per defecte del manager_id és de 145.

    4. Proporcioneu també el cognom i el nom. El correu electrònic de l'empleat serà la inicial del nom + el cognom sense espais i tot en majúscula amb un màxim de 25 caràcters. La data de contractació serà el dia del sistema.

      En MariaDB no podem definir valors per defecte en una funció o procediment, per tant, quan diem per defecte, serà, quan al procediment se li hagi passat un valor NULL al lloc on s’esperava un valor d’un camp específic.

    5. Proveu el procediment NOU_EMP afegint una nova empleada anomenada "Jane Harris" al departament 15. Tots els altres paràmetres són els predeterminats.

    6. Proveu el procediment de NOU_EMP afegint un nou empleat anomenat "Joe Harris" al departament 80. Tots els altres paràmetres són els predeterminats.

    Possible solució 
    DELIMITER //
CREATE OR REPLACE FUNCTION VALID_DEPTID(
    p_department_id TYPE OF departaments.department_id)
RETURNS BOOLEAN
BEGIN
    DECLARE v_department_id  TYPE OF departaments.department_id;
    SELECT department_id INTO v_department_id
        FROM departaments WHERE department_id = p_department_id;
    RETURN  v_department_id IS NOT NULL;
END;
//
CREATE OR REPLACE PROCEDURE NOU_EMP(
    p_first_name TYPE OF empleats.first_name,
    p_last_name   TYPE OF empleats.last_name,
    p_phone_number  TYPE OF empleats.phone_number,
    p_job_id          TYPE OF empleats.job_id,
    p_salary           TYPE OF empleats.salary,
    p_commission_pct  TYPE OF empleats.commission_pct,
    p_manager_id     TYPE OF empleats.manager_id,
    p_department_id TYPE OF empleats. department_id)
BEGIN
    DECLARE v_employee_id TYPE OF empleats.employee_id ;
    DECLARE v_email       TYPE OF empleats.email;
    DECLARE v_hire_date   TYPE OF empleats.hire_date DEFAULT CURRENT_DATE();

    IF p_first_name IS NULL THEN
        SET v_email = LEFT(UPPER(REPLACE(p_last_name, ' ' ,'')), 25);
    ELSE 
        SET v_email = LEFT(UPPER(CONCAT(LEFT(p_first_name, 1), 
                                        REPLACE(p_last_name, ' ' ,''))), 25);
    END IF;
    IF p_salary IS NULL THEN SET p_salary = 1000; END IF;
    --  IF p_commission_pct IS NULL THEN SET p_commission_pct = 0; END IF;
    IF p_department_id IS NULL THEN SET p_department_id = 30; END IF;
    IF p_job_id IS NULL THEN SET p_job_id = 'SA_REP'; END IF;
    IF p_manager_id IS NULL THEN SET p_manager_id = 145; END IF;
    SELECT IFNULL(MAX(EMPLOYEE_ID), 0) + 1 INTO v_employee_id
        FROM empleats;
    INSERT INTO EMPLEATS(employee_id, first_name, last_name, email, 
                        phone_number, hire_date, job_id, salary,
                        commission_pct, manager_id, department_id)
    VALUES (v_employee_id, p_first_name, p_last_name, v_email, 
            p_phone_number, CURRENT_DATE(), p_job_id, p_salary,
            p_commission_pct, p_manager_id, p_department_id);
END //
DELIMITER ;
START TRANSACTION;
CALL NOU_EMP('Jane', 'Harris', NULL, NULL, NULL, NULL, NULL, NULL);
SELECT * FROM EMPLEATS;
CALL NOU_EMP('Joe', 'Harris', NULL, NULL, NULL, NULL, NULL, NULL);
ROLLBACK;