Diferència entre revisions de la pàgina «M2 - Bases de dades / Exercicis consolidació UF3: PL-SQL»

Revisió del 19:07, 27 maig 2015

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.

set serveroutput on
ACCEPT p_custid 'Identificador Client: '
DECLARE

 v_id_client CUSTOMER.CUSTID%TYPE := &p_custid;
 v_ord ORD.ORDID%TYPE;
 v_date ORD.ORDERDATE%TYPE;
 v_dias NUMBER(3,0);
 v_num_prod NUMBER(2,0):=0;
 v_producto VARCHAR2(30 BYTE);
 v_unidades_prod NUMBER (4,0);
 qprod_record cantidadproducto%rowtype;

CURSOR ord_cursor IS
  SELECT ordid, orderdate, shipdate
  FROM ORD
  WHERE custid =v_id_client;

CURSOR item_cursor IS
  SELECT itemid,prodid,qty
  FROM item
  WHERE ordid =v_ord;

CURSOR qprod_cursor (v_nom_prod varchar2) IS
  SELECT *
  FROM cantidadproducto
  WHERE producto = v_nom_prod
  FOR UPDATE;

BEGIN
   FOR o_rec IN ord_cursor LOOP

   v_ord := o_rec.ordid;
   v_date := o_rec.orderdate;
   dbms_output.put_line(v_ord);
   dbms_output.put_line(v_date);
   v_dias := o_rec.shipdate - o_rec.orderdate;
   dbms_output.put_line(v_dias);
   v_num_prod := 0;
   FOR i_rec IN item_cursor LOOP
      v_num_prod := v_num_prod + 1;
      v_unidades_prod := i_rec.qty;
    
      SELECT DESCRIP
      INTO v_producto
      FROM PRODUCT
      WHERE PRODID = i_rec.prodid;
      dbms_output.put_line(v_producto);
      
      OPEN qprod_cursor(v_producto);
      FETCH qprod_cursor INTO qprod_record;
       IF qprod_cursor%FOUND THEN
         UPDATE CANTIDADPRODUCTO
         SET numcomandes=qprod_record.numcomandes + 1, totalunidades = qprod_record.totalunidades + v_unidades_prod
         WHERE CURRENT OF qprod_cursor;
        /*dbms_output.put_line(v_producto);*/
      ELSE
           INSERT INTO CANTIDADPRODUCTO 
           VALUES (v_producto,1,v_unidades_prod);
     END IF;
      CLOSE qprod_cursor;
      
 
   END LOOP;
  dbms_output.put_line(v_num_prod);
  INSERT INTO SERVICIOPEDIDO
  VALUES (v_date,v_dias,v_num_prod);
 END LOOP;
COMMIT;
END;