M2 - Bases de dades / Online UF3 : Prova PL/SQL

Exercicis OnLine – Prova PL/SQL

Un cop acabada aquesta part de la prova pengeu un document (PL_SQL_Nom_i_PrimerCognom) en l’apartat respectiu:

PL/SQL

Fes un bloc PL/SQL anònim, que presenti un informe sobre la facturació per categories de productes (veure final del document).

1.(5 punts) Crea un paquet que contingui:

1.1. La declaració de la constant quantitat (en el ex. quantitat = 5000).

1.2. Una taula de registres indexada pel nom de cada categoria que contindrà en cada registre el total facturat d’aquella categoria, el nom del producte que més s'ha facturat, el total facturat per a aquest producte, el nombre de productes dels que s'ha facturat més d'una determinada quantitat i dels que s'ha facturat menys que aquesta mateixa quantitat.

1.3. Un procediment privat que inicialitzi la taula amb totes les categories i les facturacions a 0. Utilitzeu un cursor que recuperi el NOMBRECATEGORIA de la taula CATEGORIAS per conèixer les categories de productes actuals.

1.4 Un procediment que rebi com a paràmetre el NOMBRECATEGORIA, obtingui les dades respectives a tots els productes d’aquesta categoria i actualitzi convenientment la taula. En cas de que no existeixi generarà l’excepció am el codi: -20011 i el missatge: 'No existeix cap categoria amb aquest nom'. Utilitza un cursor per recuperar les dades de facturació dels productes de la categoria de les taules PEDIDOS i DETALLES_PEDIDOS.

1.5. Un procediment que recorrerà la taula de registres i mostrarà per pantalla un informe com el mostra't al final d'aquesta pàgina(*).

1.6. Quan es faci referencia per primera vegada al paquet s’inicialitzarà amb l’execució del procediment privat.

(*) exemple de recorregut d’una matriu associativa indexada per varchar2:

declare
  type t_vector is table of number(3) index by varchar2(10);
  v_vector t_vector;
  subind varchar2(10);
begin
  v_vector('Cuarenta') := 40;
  v_vector('cuarenta') := -40;
  v_vector('Cinco') := 5;
  v_vector('Quince') := 15;

  dbms_output.put_line('count: '|| v_vector.count);

  subind := v_vector.first;
  while subind is not NULL loop
    dbms_output.put_line(subind ||': '|| v_vector(subind));
    subind := v_vector.next(subind);
  end loop;
end;

1.7. Pel bloc PL/SQL utilitzi els procediments del paquet per generar l'informe. Utilitzeu un cursor que recuperi el IDCATEGORIA I NOMBRECATEGORIA de la taula CATEGORIAS.

L'informe tindrà el següent aspecte:

                     INFORME FACTURACIÓ PER CATEGORIES         

===== Bebidas===== 

 Total facturat : 194306
 Nom producte més facturació: Vino Côte de Blaye
 Facturat producte: 93279
 Productes que han facturat més de 5000$ : 10
 Productes que han facturat menys de 5000$ : 2

===== Carnes===== 

 Total facturat : 128406
 Nom producte més facturació: Salchicha Thüringer
 Facturat producte: 69322
 Productes que han facturat més de 5000$ : 5
 Productes que han facturat menys de 5000$ : 1

...

**Podeu suposar que no hi ha dos productes amb la mateixa facturació.

2. (2,5 punts) Fes un trigguer per actualitzar la taula ENVIOS.

- Executa el script envios.sql*. El script crea la taula ENVIOS amb els següents camps:

• Companyia: Nom de la companyia d'enviament.
• Pais: País de destinació de l'enviament.
• Num_vegades: Nombre de vegades que s'ha enviat una comanda a aquest país mitjançant aquesta companyia.

- El triguer ha d'actualitzar la taula ENVIOS convenientment. Ha de tenir en compte tots els cassos possibles.

• script envios.sql

CREATE TABLE envios AS
SELECT c.nombrecompanyia companyia, p.paisdestinatario pais, count(idpedido) num_vegades
FROM companyias_envios c join  pedidos p on c.idcompanyia envios = p.formaenvio
GROUP BY c.nombrecompanyia, p.paisdestinatario
ORDER BY c.nombrecompanyia;

3. (2,5 punts) Fes un bloc anònim per actualitzar el PRECIOUNIDAD dels productes dels proveïdors d'un determinat país, segons el següent criteri:

• Producte suspès: No cal actualitzar.
• Producte amb PRECIOUNIDAD de menys de 20 € : 5% .
• Producte amb PRECIOUNIDAD entre 20 € i 40 € : 10% .
• Producte amb PRECIOUNIDAD de més de 40 € : 15% .

- Cal emprar un cursor per actualitzar els productes de la categoria que bloquegi les files.

- Has d'obtenir un llistat amb totes les categories que han tingut algun producte amb algun canvi de preu i el total de l'augment de preu dels seus productes.

* Pots crear les funcions i procediments que estimis oportuns.

FEU EN TOTS ELS EXERCICIS UN JOC DE PROVES ADIENT