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.

<--

DECLARE 

type tabla_salmedio_dep_tipo is table of emp.sal%TYPE index by binary_integer;
v_tabla_salmedio_dep tabla_salmedio_dep_tipo;

cursor cur_dep_salmedio is
  select d.deptno, avg(sal) sal_medio
  from emp e, dept d
  where e.deptno=d.deptno
  group by d.deptno;

cursor cur_emp is
  select empno, sal, deptno
  from emp
  for update of sal;

BEGIN
 
 for v_rec_cur_dep in cur_dep_salmedio loop
   v_tabla_salmedio_dep(v_rec_cur_dep.deptno):= v_rec_cur_dep.sal_medio;
 end loop;
 
 for v_emp_rec in cur_emp loop
   if v_emp_rec.sal < v_tabla_salmedio_dep(v_emp_rec.deptno) then
     update emp set sal= v_tabla_salmedio_dep(v_emp_rec.deptno)
     where current of cur_emp;
   end if;
 end loop;
 
end;

-->
===Exercici 2===

Creeu les següents taules:
<pre>
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.