Diferència entre revisions de la pàgina «M2 - Bases de dades / Exercicis consolidació UF3: PL-SQL»
(→Exercici 1) |
(→Exercici 4) |
||
(17 revisions intermèdies per 2 usuaris que no es mostren) | |||
Línia 2: | Línia 2: | ||
===Exercici 1=== | ===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. | ||
+ | |||
+ | <!-- | ||
+ | <pre> | ||
+ | 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; | ||
+ | </pre> | ||
+ | --> | ||
+ | |||
+ | ===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: | ||
+ | <pre> | ||
+ | ************************************************************************************************************ | ||
+ | Liquidació del empleat:...................(1) Departament:.................(2) Ofici:...........(3) | ||
+ | |||
+ | Salari : ............(4) | ||
+ | Trienis :.............(5) | ||
+ | Comp. Resp. :.............(6) | ||
+ | Comissió :.............(7) | ||
+ | ---------------- | ||
+ | Total :.............(8) | ||
+ | ************************************************************************************************************ | ||
+ | </pre> | ||
+ | - 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: | 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 18: | 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 26: | 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 40: | 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.