DAM - DAW - MP 0484 Bases de Dades - NF5

Pràctica 4 – Condicions i Iteracions

En aquesta pràctica, creareu blocs anònims que fan servir estructures de condicionals i estructures iteratives.

Per fer aquests exercicis cal tenir les següents taules creades:

CREATE OR REPLACE TABLE MESSAGES(results INT);
CREATE OR REPLACE TABLE EMPLEATS AS SELECT * FROM empresa.DEPARTMENTS;

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 //

Per fer aquest exercici cal tenir una taula anomenada MESSAGES, creada amb la següent instrucció: CREATE OR REPLACE TABLE MESSAGES (results INT);

Escriviu un bloc anònim per inserir els números de l'1 al 10, exceptuant el 6 i el 8, a la taula MESSAGES.
Mostreu el contingut de la taula MESSAGES.

Feu-ho amb FOR, amb WHILE i amb LOOP.

Possible solució FOR

-- 
-- Exercici 1 amb FOR
-- 
TRUNCATE TABLE MESSAGES //
BEGIN NOT ATOMIC
    FOR v_i IN 1..10 DO
        IF v_i NOT IN (6, 8) THEN
            INSERT INTO MESSAGES VALUES(v_i);
        END IF;
    END FOR;
END; //
SELECT * FROM MESSAGES //

Possible solució WHILE

-- 
-- Exercici 1 amb WHILE
-- 
TRUNCATE TABLE MESSAGES //
BEGIN NOT ATOMIC
    DECLARE v_i INT DEFAULT 1;
    WHILE v_i <= 10 DO
        IF v_i NOT IN (6, 8) THEN
            INSERT INTO MESSAGES VALUES(v_i);
        END IF;
        SET v_i = v_i + 1;
    END WHILE;
END; //
SELECT * FROM MESSAGES //

Possible solució LOOP

-- 
-- Exercici 1 amb LOOP
-- 
TRUNCATE TABLE MESSAGES //
BEGIN NOT ATOMIC
    DECLARE v_i INT DEFAULT 1;
    bucle: LOOP
        IF v_i > 10 THEN LEAVE bucle; END IF;
        IF v_i NOT IN (6, 8) THEN
            INSERT INTO MESSAGES VALUES(v_i);
        END IF;
        SET v_i = v_i + 1;
    END LOOP;
END; //
SELECT * FROM MESSAGES //

Creeu un bloc anònim que calculi l'import de la comissió per a un determinat empleat a partir de la taula EMPLEATS.

Utilitzeu una variable per enregistrar l'identificador de l'empleat. (@empno = 100)
Si el sou de l'empleat és inferior a 5.000 dòlars, mostreu el salari i l'import de la bonificació (10% del salari).
Si el sou de l'empleat està entre $5.000 i $10.000, mostreu el salari i l'import de la bonificació (15% del salari).
Si el sou de l'empleat supera els 10.000 dòlars, mostreu el salari i l'import de la bonificació (20% del salari).
Si el sou l'empleat fos NULL, la bonificació serà zero.
Proveu el bloc anònim per a cada cas utilitzant els empleats de codi 100, 149 i 178.

Feu-ho amb CASE i IF/ELSEIF/ELSE.

@empno	v_salary	v_bonus
100	24000.00	4800.00
149	10500.00	2100.00
178	7000.00	1400.00

Possible solució CASE

SET @empno = 100 //
-- 
-- Exercici 2 amb CASE
-- 
BEGIN NOT ATOMIC
DECLARE v_salary, v_bonus TYPE OF EMPLEATS.salary;
SELECT SALARY INTO v_salary
    FROM EMPLEATS
    WHERE EMPLOYEE_ID = @empno;
CASE
    WHEN v_salary IS NULL THEN
    SET v_bonus = 0;
    WHEN v_salary < 5000 THEN
    SET v_bonus = v_salary * 0.10;
    WHEN v_salary BETWEEN 5000 AND 1000 THEN
    SET v_bonus = v_salary * 0.15;
    ELSE
    SET v_bonus = v_salary * 0.20;
END CASE;
SELECT @empno, v_salary, v_bonus;
END //

Possible solució IF/ELSEIF/ELSE

-- 
-- Exercici 2 amb IF/ELSEIF/ELSE
-- 

BEGIN NOT ATOMIC
DECLARE v_salary, v_bonus TYPE OF EMPLEATS.salary;
SELECT SALARY INTO v_salary
    FROM EMPLEATS
    WHERE EMPLOYEE_ID = @empno;
IF v_salary IS NULL THEN
    SET v_bonus = 0;
ELSEIF v_salary < 5000 THEN
    SET v_bonus = v_salary * 0.10;
ELSEIF v_salary BETWEEN 5000 AND 1000 THEN
    SET v_bonus = v_salary * 0.15;
ELSE
    SET v_bonus = v_salary * 0.20;
END IF;
SELECT @empno, v_salary, v_bonus;
END //

Afegiu un nou camp a la taula EMPLEATS anomenat STARS, de tipus VARCHAR(50) per emmagatzemar asteriscs (*). (DDL)
Possible solució
```
ALTER TABLE EMPLEATS ADD STARS VARCHAR(50);
```
Creeu un bloc anònim per premiar a un empleat afegint un asterisc per cada 1.000 dòlars del salary al camp STARS.
1. Utilitzeu una variable per proporcionar l'identificador de l'empleat. (@empno=104).
2. Inicialitzeu la variable local al bloc, cadena d’asteriscs a cadena buida ''. v_asteriscs DEFAULT ''
3. Afegiu un asterisc a la cadena per cada 1.000 dòlars de salari. Per exemple, si l'empleat té un salari de 8.000 dòlars, la cadena d'asteriscs hauria de ser de 8 asteriscs. Si l'empleat té un salari de 12.500 dòlars, la cadena d'asteriscs hauria de ser de 12. Aquesta part feu-la amb una iteració. No utilitzeu cap funció tipus REPEAT, LPAD o RPAD, només la funció CONCAT.
4. Actualitzeu la columna STARS amb la cadena d'asteriscs per a cada empleat.
5. Proveu el bloc per als valors següents: @empno=174, @empno=176
6. Mostreu les files de la taula EMPLEATS per verificar si el vostre bloc anònim s'ha executat amb èxit.
employee_id salary stars

104 6000.00 **

174 11000.00 *****

176 8600.00 **
Possible solució
```
SET @empno = 104 //
-- 
-- Exercici 4
-- 
BEGIN NOT ATOMIC
DECLARE v_salary TYPE OF EMPLEATS.salary;
DECLARE v_asteriscs TYPE OF EMPLEATS.stars DEFAULT '';
SELECT SALARY INTO v_salary
    FROM EMPLEATS
    WHERE EMPLOYEE_ID = @empno;
FOR v_i IN 1 .. TRUNCATE(v_salary / 1000, 0) DO
    SET v_asteriscs = CONCAT(v_asteriscs, '*');
END FOR;
UPDATE empleats SET stars = v_asteriscs WHERE employee_id = @empno;
SELECT employee_id, salary, stars
    FROM empleats
    WHERE employee_id = @empno;
END //
```

employee_id	salary	stars
104	6000.00	**
174	11000.00	*****
176	8600.00	**