Diferència entre revisions de la pàgina «M2 - Bases de dades / Exercicis consolidació UF3: PL-SQL»
(→Exercici 3) |
(→Exercici 4) |
||
Línia 143: | Línia 143: | ||
*Actualitzeu la taula Servei_Comanda. | *Actualitzeu la taula Servei_Comanda. | ||
*Actualitzeu la taula Qtat_Producte. | *Actualitzeu la taula Qtat_Producte. | ||
+ | |||
+ | <pre> | ||
+ | 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; | ||
+ | </pre> |
Revisió del 17:30, 27 maig 2015
Contingut
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;