M2 - Bases de dades / Exercicis consolidació UF3: PL-SQL

Exercici Cursors Explícits

Exercici 1

Creeu un bloc PL/SQL que actualitzi el salari dels empleats amb el salari promig del seu departament, per a aquells empleats que tinguin un salari inferior a aquest salari promig.

Consideracions:

• Utilitzeu una taula per guardar el salari promig de cada departament (posicions respectives al número de departament).

• Utilitzeu dos cursors: el primer amb el salari promig de tots els departaments i el segon per actualitzar el salari dels empleats que cobren menys que el promig del seu departament.

Exercici 2: Cursors

• Feu un programa que visualitzi els dos empleats que guanyen menys de cada ofici.

  Nota: En el SELECT associat al cursor NO s'ha de limitar el nombre de files amb %ROWNUM 

• Feu un programa que mostri,en un format similar a les ruptures de control vistes en SQL*plus les següents dades:

- Per a cada empleat: cognom i salari.

- Per a cada departament: Nombre d'empleats i suma dels salaris del departament.

- Al final del llistat: Nombre total d'empleats i suma de tots els salaris.

 Nota: En el SELECT associat al cursor s'han de recorre simplement les files de la taula de empleats

• Escriu un programa PL-SQL parametritzat que rebi un nom de ciutat i dos valors de salaris i mostri el nom dels empleats i ofici de cada departament d'aquells que tinguin una comissió en aquest rang. Només cal considerar els departaments localitzats en la ciutat indicada.

  Nota: haureu d'utilitzar dos cursors parametritzats:
 -El primer que tingui com a paràmetre una ciutat paràmetre una ciutat
 -El segon que tingui com a paràmetre un rang de comissions i un departament

• Escriu un programa PL-SQL que incrementi en un 10% el salari dels empleats més antics en tots els departaments. Definir un cursor amb la clàusula 'FOR UPDATE'

• Escriu un programa PL-SQL parametritzat que rebi un nom de departament i nombre positiu i acomiadi a aquest nombre d'empleats amb menor antiguitat d'aquest departament.

Nota: Cal que utilitzeu un cursor parametritzat. Definiu un cursor amb paràmetres que tingui la clàusula 'FOR UPDATE'.

Exercici 3

Dissenyeu una aplicació que simuli un llistat de liquidació dels empleats atenent a les següents especificacions:

- Feu una funció que rebi com a paràmetres dues dates i retorni el número de trienis.

- El llistat tindrà el següent format per a cada empleat:

************************************************************************************************************
Liquidació del empleat:...................(1) 	Departament:.................(2) 	Ofici:...........(3)

Salari		        : ............(4)
Trienis 		:.............(5)
Comp. Resp.     	:.............(6)
Comissió		:.............(7)
 			----------------
Total		        :.............(8)
************************************************************************************************************

- On: 1 ,2, 3 y 4 Corresponen al cognom, departament, ofici i salari de l'empleat.

5 És l'import en concepte de trienis. Cada trieni són tres anys complerts des de la data d'alta fins la d'emissió i suposen 50 €. Utilitzeu la funció anterior.

6 És el complement per responsabilitat. Serà de 100 € per a cada empleat que tingui a carreg.

7 És la comissió. Els valors nuls seran substituïts per zeros.

8 Suma de tots els conceptes anteriors.

El llistat anirà ordenat per cognom.

Exercici 4

Creeu les següents taules:

Taula Servei_Comanda: recull la data de la comanda, els dies transcorreguts entre la petició de comanda (orderdate) i la venda de la mateixa (shipdate) i quants productes s'han demanat en la comanda.

    
Create Table Servei_Comanda(
       data_com date,
       dies_com number(2),
       num_prod number(2));

Taula Qtat_Producte: recull el nom del producte, en quantes comandes s'ha demanat el productei en total quantes unitats del producte s'han demanat.

Alter table Product add constraint product_uq Unique(descrip);

Create Table Qtat_Producte(
       Producte varchar2(30),
       num_com number(3),
       unit_prod number(4),
       Constraint qtat_producte_producte_pk Primary Key(Producte),
       Constraint qtat_producte_producte_fk Foreign key(Producte) References 
       Product(Descrip));

Insert Into qtat_producte Values('ACE TENNIS RACKET I',3,18);

Feu una funció que validi l'existencia d'un client a partir del seu codi.

Programa:

• En cas de no existencia del client, informeu a l'usuari amb un missatge.
• Recupereu totes les comandes del client en un cursor.
• Per a cada comanda, recupereu tots els items en un altra cursor. S'executará tantes vegades com comandes hagi fet el client.
• Actualitzeu la taula Servei_Comanda.
• Actualitzeu la taula Qtat_Producte.