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

De wikiserver
Dreceres ràpides: navegació, cerca
(→‎Exercici 3)
(→‎Exercici 4)
 
(Hi ha 10 revisions intermèdies del mateix usuari que no es mostren)
Línia 75: Línia 75:
  
 
Dissenyeu  una aplicació que simuli un llistat de liquidació dels empleats atenent a les següents especificacions:
 
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:
 
- El llistat tindrà el següent format per a cada empleat:
−
**********************************************************************
+
<pre>
 +
************************************************************************************************************
 
Liquidació del empleat:...................(1) Departament:.................(2) Ofici:...........(3)
 
Liquidació del empleat:...................(1) Departament:.................(2) Ofici:...........(3)
  
Línia 84: Línia 87:
 
Comp. Resp.    :.............(6)
 
Comp. Resp.    :.............(6)
 
Comissió :.............(7)
 
Comissió :.............(7)
−
  ------------
+
  ----------------
 
Total         :.............(8)
 
Total         :.............(8)
−
**********************************************************************
+
************************************************************************************************************
 +
</pre>
 
- On:
 
- On:
 
1 ,2, 3 y 4 Corresponen al cognom, departament, ofici i salari de l'empleat.
 
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 €.  
+
 
 +
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.
 
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.
 
7 És la comissió. Els valors nuls seran substituïts per zeros.
 +
 
8 Suma de tots els conceptes anteriors.
 
8 Suma de tots els conceptes anteriors.
  
Línia 99: Línia 107:
  
 
Creeu les següents taules:
 
Creeu les següents taules:
−
<pre>
+
 
 
Taula Servei_Comanda: recull la data de la comanda, els dies transcorreguts entre la petició de comanda (orderdate)  
 
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.
 
i la venda de la mateixa (shipdate) i quants productes s'han demanat en la comanda.
−
   
+
<pre>   
 
Create Table Servei_Comanda(
 
Create Table Servei_Comanda(
 
       data_com date,
 
       data_com date,
 
       dies_com number(2),
 
       dies_com number(2),
 
       num_prod number(2));
 
       num_prod number(2));
−
 
+
</pre>
 
Taula Qtat_Producte: recull el nom del producte, en quantes comandes s'ha demanat el productei en total quantes  
 
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.  
 
unitats del producte s'han demanat.  
Línia 113: Línia 121:
 
Alter table Product
 
Alter table Product
 
add constraint product_uq Unique(descrip);
 
add constraint product_uq Unique(descrip);
−
 
+
<pre>
 
Create Table Qtat_Producte(
 
Create Table Qtat_Producte(
 
       Producte varchar2(30),
 
       Producte varchar2(30),
Línia 121: Línia 129:
 
       Constraint qtat_producte_producte_fk Foreign key(Producte) References  
 
       Constraint qtat_producte_producte_fk Foreign key(Producte) References  
 
       Product(Descrip));
 
       Product(Descrip));
−
 
+
</pre>
 
Insert Into qtat_producte
 
Insert Into qtat_producte
 
Values('ACE TENNIS RACKET I',3,18);
 
Values('ACE TENNIS RACKET I',3,18);
−
</pre>
+
 
  
 
Feu una funció que validi l'existencia d'un client a partir del seu codi.  
 
Feu una funció que validi l'existencia d'un client a partir del seu codi.  
Línia 135: Línia 143:
 
*Actualitzeu la taula Servei_Comanda.
 
*Actualitzeu la taula Servei_Comanda.
 
*Actualitzeu la taula Qtat_Producte.
 
*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;
 +
-->

Revisió de 19:10, 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.


Obtingut de «http://wikiserver.infomerce.es/index.php?title=M2_-_Bases_de_dades_/_Exercicis_consolidació_UF3:_PL-SQL&oldid=4919»