M2 - Bases de dades / Practiques UF3 : PL-SQL
Pràctica PL/SQL Cal lliurar un fitxer (.sql) amb totes les comandes SQL emprades i tot el codi PL/SQL, degudament comentat.
Data màxima lliurament: 20/05/2022
Especificació
Per a la implementació d'una base de dades que suporti anàlisi multidimensional és desitja crear una taula que emmagatzemi les diferents característiques referides a una data; això és: dia, dia de la setmana,mes, any, trimestre... L'esquema relacional d'aquesta taula seria els següent:
TEMPS (data, dia, dia_setmana, mes, trimestre, any)
1.- Crea la taula TEMPS en la base de dades atenent a l'esquema relacional anterior; considerant l'atribut data com a clau principal. La resta dels atributs no admeten valors nuls.
2.- Crea una funció PL/SQL que donat un nombre enter obtingui en format text el corresponent mes. Per exemple:
EXECUTE convertir_mes(1) → Gener
3.- Crea un procediment emmagatzemat PL/SQL que rebi com a paràmetres un rang de dates i afegeixi en la taula TEMPS un registre per cada dia comprès entre aquestes dates. Per exemple:
EXECUTE afegir_temps('01/01/2019', '10/01/2019');
Per realitzar el procediment sol·licitat en el punt 3, has d'emprar la següent funció, que a partir d'una data et retorna el dia següent a aquesta data.
CREATE OR REPLACE FUNCTION dia_seguent (p_data_actual DATE)
RETURN DATE IS
BEGIN
RETURN TO_DATE(TO_CHAR(p_data_actual + 1));
END;Desprès de cridar al procediment «afegir_temps» de l'exemple anterior, la taula TEMPS hauria de contenir la següent informació:
| data | dia | dia_setmana | mes | trimestre | any |
|---|---|---|---|---|---|
| 01/01/2019 | 1 | Dimarts | Gener | T1 | 2019 |
| 02/01/2019 | 2 | Dimecres | Gener | T1 | 2019 |
| 03/01/2019 | 3 | Dijous | Gener | T1 | 2019 |
| 04/01/2019 | 4 | Divendres | Gener | T1 | 2019 |
| 05/01/2019 | 5 | Dissabte | Gener | T1 | 2019 |
| 06/01/2019 | 6 | Diumenge | Gener | T1 | 2019 |
| 07/01/2019 | 7 | Dilluns | Gener | T1 | 2019 |
| 08/01/2019 | 8 | Dimarts | Gener | T1 | 2019 |
| 09/01/2019 | 9 | Dimecres | Gener | T1 | 2019 |
| 10/01/2019 | 10 | Dijous | Gener | T1 | 2019 |
Ajuda: Pots ajudar-te de la funció TO_CHAR, que passant-li com a paràmetre una data i un atribut de temps com dia, mes, any... retorni el corresponent dia, mes, any de la data rebuda com a primer paràmetre.
4.- Executa el procediment creat per a que generi els registres corresponents als anys 2019 i 2020.
La taula TEMPS creada anteriorment la utilitzarem per datar les vendes realitzades per una empresa especialitzada en la venda d'impressores. Per tant l'esquema relacional de la base de dades quedaria de la següent forma:
TEMPS (data, dia, dia_setmana, mes, trimestre, any)
PRODUCTES (ref, nom_producte, unitats, preu, reposicio)
VENDES (num, data, producte, unitats, import) ON producte REFERENCIA A ref de la taula PRODUCTES
5.- Crea les taules VENDES i PRODUCTES atenent a l'esquema relacional anterior; considerant l'atribut num com a clau principal de la taula VENDES i ref la clau principal de la taula PRODUCTES. La resta dels atributs de la relació no admeten valors nuls.
6.- Crea dos procediments emmagatzemats: «registrar_producte» que rebent una referencia de producte, el nom, les unitats en magatzem del producte , el preu i las unitats de reposició afegeixi els valors corresponents en la taula de productes; i «registrar_venda» que rebent una data, la referencia d'un producte i les unitats venudes, utilitzi una seqüència per obtenir el número de la venda, calculi l'import (segons taula producte) i afegeixi els valors corresponents en la taula de vendes.
Nota: el procediment «registrar_venda» utilitzarà la funció existeix_producte, que a partir d'una referencia de producte retornara «true» si aquest existeix i «false» en cas contrari. Si el producte no existeix no s'haurà d'afegir la venda.
7.- Utilitza els procediments emmagatzemats creats en l'apartat 6 per afegir els següents productes i realitzar les següents vendes:
Productes
| ref | nom_producte | unitats | preu | reposicio |
|---|---|---|---|---|
| CA2535 | IMPRESORA CANON PIXMA IP2850 | 10 | 45,48 | 6 |
| CA2542 | IMPRESORA CANON PIXMA IX6850 A3 | 8 | 234,38 | 4 |
| HP8053 | IMPRESORA HP PAGEWIDE PRO 452DW | 5 | 573,00 | 3 |
| CA2704 | IMPRESORA CANON PIXMA MX495 | 6 | 64,90 | 5 |
| HP8143 | MULTIFUNCION TINTA HP ENVY 4524 | 4 | 95,00 | 3 |
| CA2950 | IMPRESORA CANON PIXMA TS8050 | 3 | 169,90 | 2 |
Vendes
| num | data | produce | unitats | import |
|---|---|---|---|---|
| 1 | 07/01/2019 | CA2535 | 4 | 181,92 |
| 2 | 14/01/2019 | CA2542 | 2 | 468,76 |
| 3 | 06/05/2019 | CA2704 | 10 | 649,00 |
| 4 | 16/05/2019 | HP8053 | 1 | 573,00 |
| 5 | 25/07/2019 | CA2950 | 4 | 679,60 |
| 6 | 02/08/2019 | HP8143 | 8 | 760,00 |
| 7 | 13/09/2019 | CA2704 | 2 | 129,80 |
| 8 | 09/12/2019 | CA2542 | 3 | 703,14 |
| 9 | 11/12/2019 | CA2950 | 10 | 1699,00 |
| 10 | 10/02/2020 | HP8143 | 1 | 95,00 |
| 11 | 11/05/2020 | HP8053 | 1 | 573,00 |
| 12 | 12/06/2020 | CA2542 | 10 | 2343,80 |
| 13 | 14/08/2020 | CA2535 | 12 | 545,76 |
| 14 | 21/08/2020 | HP8053 | 2 | 1146,00 |
| 15 | 05/10/2020 | CA2704 | 1 | 64,90 |
| 16 | 26/10/2020 | HP8143 | 3 | 285,00 |
| 17 | 23/11/2020 | CA2950 | 5 | 849,50 |
| 18 | 07/12/2020 | CA2535 | 8 | 363,84 |
| 19 | 21/12/2020 | CA2950 | 6 | 1019,40 |
8.- Crea un procediment «mostrar_estadistiques» que generi la següent informació, considerant que la major venda es refereix a les vendes amb major valor econòmic, no amb major nombre d'unitats venudes:
- Valor total de les vendes.
- Valor total de les vendes en 2019.
- Valor total de les vendes en 2020.
- Llistat ordenat de les vendes per dia de la setmana.
- Llistat ordenat de les vendes per mes de l'any.
- Llistat ordenat de les vendes per trimestre.
UTILITZA UN CURSOR QUE RECOLLÍ TOTES LES DADES DE LA TAULA VENDA I LES MATRIUS ASSOCIATIVES QUE NECESSITIS PER ANAR ACUMULANT EL TOTAL DE VENDES PER DIA DE LA SETMANA, MES I TRIMESTRE.
Ajuda: A continuació es mostra un exemple de la sortida produïda per el procediment creat:
EXECUTE mostrar_estadistiques
Estadístiques
---------------------- Totals --------------------------- Vendes Totals: 13130,42 € En 2019: 5844,22 € En 2020: 7286,20 €
Llistats
------------Valor de les vendes per dies ------------ Dilluns: 5253,46 € Dimarts: 0,00 € Dimecres: 1699,00 € Dijous: 1252,60 € Divendres: 4925,36 €
------------Valor de les vendes per mesos----------- Gener: 650,68 € Febrer: 95,00 € Març: 0,00 € Abril: 0,00 € Maig: 1795,00 € Juny: 2343,80 € Juliol: 679,60 € Agost: 2451,76 € Setembre: 129,80 € Octubre: 349,90 € Novembre: 849,50 € Desembre: 3785,38 €
------------Valor de les vendes per trimestres------ T1: 745,68 € T2: 4138,80 € T3: 3261,16 € T4. 4984,78 €
Triggers
9.- Crea el trigger «unitats_suficients» que abans d'una venda comprovi si hi ha unitats suficients.
En cas afirmatiu es produïra la venda i s'actualitzarà el nombre d'unitats del magatzem. En cas contrari es generarà una excepció, el tractament de la qual informarà de que no ha estat possible la venda i el motiu.
10.- Crea el trigger «nova_comanda» que desprès d'una actualització de les unitats de producte en la taula producte, comprovi si aquestes han arribat al nivell de reposició i afegeixi una línia al fitxer de comandes indicant quin és la compra que cal fer, tenint en compte que, quan es fa una compra sempre s'arriba al doble de les unitats de reposició (Per exemple: si reposició es igual a 5 i queden 3, cal comprar 7.
11.- Crea el procediment «vendes_impressora» per elaborar un informe que mostri com han anat les vendes de les diferents impressores. En concret es volen les següents dades:
- Total venut per impressora i any.
- Total venut per impressora, tenint en compte els dos anys.
- Total venut per totes les impressores per any.
- Total venut per impressora, tenint en compte els dos anys.
- Impressora que més unitats s'ha venut per any i unitats.
- Impressora que més ha facturat per any i import.
- Impressora que més unitats s'ha venut tenint en compte els dos anys i unitats.
- Impressora que més ha facturat tenint en compte els dos anys i import.
- Impressora de la comanda que més unitats s'ha venut i unitats tenint en compte els dos anys.
- Impressora de la comanda que més s'ha facturat i import tenint en compte els dos anys.
Ajuda: podeu considerar que només hi ha una impressora en cada cas. Si ni hagués dos o més només mostreu una.
Per aquest apartat, cal que utilitzeu els següents cursors:
CURSOR c_producte_cursor IS
SELECT ref, nom_producte
FROM producte ;
CURSOR c_venda_cursor (ref_prod VARCHAR2) IS
SELECT to_char(data,’YYYY’), producte, unitats, import
FROM vendes
WHERE producte = ref_prod
ORDER BY to_char(data,’YYYY’);Ajuda: A continuació es mostra un exemple de la sortida produïda per el procediment creat:
======== Informe de vendes d'impressores ========
--IMPRESORA CANON PIXMA IP2850--
En 2019: 181,92 €
En 2020: 909,60 €
Total: 1091,52 €
--IMPRESORA CANON PIXMA IX6850 A3--
En 2019: 1171,90 €
En 2020: 2343,80 €
Total: 3515,70 €
...
============ Resum ===============
En 2019: 5844,22 €
En 2020: 7286,20 €
Total: 13130,42 €
============ Dades destacades ===============
----------- Impressora que més unitats s'ha venut -----------
En 2019: IMPRESORA CANON PIXMA TS8050 Unitats: 14
En 2020: IMPRESORA CANON PIXMA IP2850 Unitats: 20
En Total: IMPRESORA CANON PIXMA TS8050 Unitats: 25
--------------- Impressora que més ha facturat ---------------
En 2019: IMPRESORA CANON PIXMA TS8050 Import: 2378,60 €
En 2020: IMPRESORA CANON PIXMA IX6850 A3 Import: 2343,80 €
En Total: IMPRESORA CANON PIXMA TS8050 Import: 4247.50 €
--------- Comanda de més unitats i més facturació ---------
Impressora: IMPRESORA CANON PIXMA IP2850 Unitats: 12
Impressora: IMPRESORA CANON PIXMA IX6850 A3 Import: 2343,80 €
