Diferència entre revisions de la pàgina «M2 - Bases de dades / Exercicis consolidació UF3: PL-SQL»
(→Exercici 3) |
(→Exercici 4) |
||
(Hi ha 8 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: | ||
Línia 91: | Línia 93: | ||
- 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 101: | Línia 107: | ||
Creeu les següents taules: | Creeu les següents taules: | ||
− | + | ||
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 115: | 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 123: | 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); | ||
− | + | ||
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 137: | 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
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.