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

De wikiserver
Dreceres ràpides: navegació, cerca
(→‎T15- Creació de triggers)
(→‎T15- Creació de triggers)
Línia 503: Línia 503:
 
A las taules EMP i DEPT se'ls aplica una sèrie de regles de negoci.
 
A las taules EMP i DEPT se'ls aplica una sèrie de regles de negoci.
  
−
a) Decideix com implementat cada regla: mitjançant constraints o triggers. ¿quines restricciones o triggers es necessitaran i quins problemes podem esperar?
+
Implementa les regles de negoci mitjançant triggers.
−
 
 
−
b) Implementa les regles de negoci mitjançant triggers.
 
  
 
Reglas de negocio:
 
Reglas de negocio:
  
−
1. Els venedors sempre haurien de rebre una comissió. Els empleats que no són venedors mai haurien de rebre una comissió. Implementa aquesta regla amb una constraint.
+
1. La taula EMP hauria de contenir exactament un PRESIDENT. Comproveu la resposta.
−
 
 
−
2. La taula EMP hauria de contenir exactament un PRESIDENT. Comproveu la resposta.
 
  
−
3. Els salaris només podran augmentar-se, no disminuir-se.
+
2. Els salaris només podran augmentar-se, no disminuir-se.
  
−
4. Si un departament es trasllada a una altra ubicació, cada empleat d'aquest departament tindrà automàticament un increment de salari del 2%.
+
3. Si un departament es trasllada a una altra ubicació, cada empleat d'aquest departament tindrà automàticament un increment de salari del 2%.
  
 
==Solucions exercicis ==
 
==Solucions exercicis ==

Revisió del 18:47, 20 maig 2015

T3- Declaració de variables

  • Exercici 1
Avalua les següents declaracions de variables determinant quines no son correctes (donen error), explica perquè.
a) DECLARE v_id NUMBER(4);
b) DECLARE v_x, v_y,v_z VARCHAR2(10);
c) DECLARE v_data_naixament DATE NOT NULL;
d) DECLARE v_en_stock BOOLEAN :=1
  • Exercici 2
Determina el tipus de dades de les expressions resultants en les següents assignacions
a) v_dies_que_falten := v_date - SYSDATE;
b) v_sender := USER || ':' || TO_CHAR(v_deptno);
c) v_sum := $100,000+$250,000;
d) v_bandera := TRUE;
e) v_n1 := v_n2 > (2*v_n3);
f) v_valor := NULL;
  • Exercici 3
Crea un bloc anònim per imprimir la frase “Benvingut a PL/SQL”
  • Exercici 4
Crea un bloc que declari dues variables. Assigneu el valor d'aquestes variables PL/SQL a variables SQL*PLUS i imprimeix el valor d'aquestes dues variables per pantalla. Guarda el bloc PL/SQL en un arxiu anomenat p2q4.spl.
V_CHAR Character (variable length)
V_Number
Assigna els següents valors a les variables:
Variable Value
--------- ---------------------------------------------
V_CHAR The literal '42 is the answer'
V_NUM The first two characters form V_CHAR

­

T4- Sentencies executables

  • Exercici 1
Observa el següent bloc PL/SQL i determina els valors de les variables següents d'acord amb les regles d'àmbit
DECLARE
v_weight NUMBER(3):=600;
v_message VARCHAR2(255):='Product 10012';
BEGIN
DECLARE
v_weight NUMBER(3):=1;
v_message VARCHAR2(255):='Product 11001';
v_new_locn VARCHAR2(50):='Europe';
BEGIN
v_weight:= v_weight+1;
v_new_locn:='Western'|| v_new_locn;
END;
v_weight:= v_weight+1;
v_message:=v_message||'is in stock';
v_new_locn:='Western'|| v_new_locn;
END;


a) El valor de v_weight en el subbloc és:
b) El valor de v_new_locn en el subbloc és:
c) El valor de v_weight en el bloc principal és:
d) El valor de v_message en el bloc principal és:
e) El valor de v_new_locn en el bloc principal es
  • Exercici 2

Suposem que incloem un subbloc en un bloc tal com es mostra tot seguit. Determina els valors en els casos següents:

DECLARE
v_customer VARCHAR2(50):='Womansport';
v_credit_rating VARCHAR2(50):='excellent';
BEGIN
DECLARE
v_customer NUMBER(7):=201;
v_name VARCHAR2(25):='unisports';
BEGIN
vcustomer v_name v_credit_rating
END;
vcustomer v_name v_credit_rating
END;
a) El valor de v_customer en el subbloc és:
b) El valor de v_name en el subbloc és:
c) El valor de v_credit_rating en el subbloc és:
d) El valor de v_customer en el bloc principal és:
e) El valor de v_name en el bloc principal es
f) El valor de v_credit_rating en el bloc principal és:
  • Exercici 3

Crea i executa un bloc PL/SQL anomenat “T3E3.sql” que accepti dos números mitjançant variables SQL*PLUS. S'ha de dividir el primer número pel segon i després afegir el segon número al resultat. El resultat s'ha d'escriure en una variable PL/SQL i s'ha d'imprimir en pantalla amb una variable SQL*PLUS.

Please enter the first number: 2
Please enter the second number: 4
PL/SQL procedure successfully completed.
V_RESULT
--------
4.5
  • Exercici 4
Genereu un bloc PL/SQL que calculi la compensació total per un any. El salari anual i el percentatge anual de bonificacions es passaran al bloc PL/SQL mitjançant variables de substitució SQL*PLUS i l'import de bonificació haurà de ser convertit de número enter a decimal (per exemple 15 a 0,15). Si el salari és nul assigneu-li zero abans de calcula la compensació. Utilitzeu la funció NVL per gestionar els valors nuls.
Please enter the salary amount: 50000
Please enter the bonus percentage: 10
PL/SQL procedure successfully completed.
G_TOTAL
--------
55000

T5- Interacció amb el servidor Oracle

  • Exercici 1

Crea un bloc PL/SQL que seleccioni el departament amb el número més alt de la taula DEPT i mostri els resultats per pantalla.

  • Exercici 2

Crea bloc PL/SQL que insereixi un nou departament en la taula DEPT, com a número de departament utilitza el recuperat en l'apartat anterior més 10.

Utilitza una variable de substitució per el nom de departament i deixa la ubicació com a nula. Finalment mostra el nou departament creat.

  • Exercici 3

Crea un bloc plsql que actualitzi la ubicació d'un departament existent. Guarda el bloc en un arxiu anomenat “t5p3.sql”.

a) crea un paràmetre per a guardar el número de departament introduit

b) crea un altre paràmetre per a guardar la nova ubicació del departament

c) executa el bloc i actualitza la ubicació d'un departament

e) comprova que s'ha actualitzat correctament.

  • Exercici 4

Crea un bloc pl/sql que suprimeixi el departament creat en l'exercici 2.Guarda el bloc en un arxiu anomenat “t5p4.sql”

a) crea un paràmetre per a guardar el número de departament introduit

b) imprimeix per pantalla el número de files afectades

c) què passa si introduïu un número de departament inexistent?

d) confirma que el departament s'ha suprimit.


T6- Creació d'estructures de control

  • Exercici 1

crear una nova taula anomenada MESSAGES per emmagatzemar en una columna anomenada RESULTS cadenes de caràcters de longitud màxima 60 caràcters.

a) crea un bloc per insertar els números del 1 al 10 excepte el 6 i el 8

b) fes un COMMIT al final del bloc


  • Exercici 2

Crear un nou bloc plsql per a calcular l'import de la comissió d'un empleat, aquesta comissió dependrà del sou de l'empleat.

a) Inserta un nou empleat en la taula EMP que tingui un sou NULL.

b) Crea una variable per tal d'emmagatzemar el número de l'empleat introduït per teclat.

c) si el sou d'aquest empleat és inferior a 1000$ l'import de la comissió serà un 10% d'aquest sou.

d) si el sou d'aquest empleat està entre 1000$ i 1500$ l'import de la comissió serà un 15% d'aquest sou.

e) si el sou d'aquest empleat és superior a 1500$ l'import de la comissió serà un 20% d'aquest sou.

f) si el sou d'aquest empleat és NULL la comissió serà un 0.

g) comprova el funcionament d'aquest bloc per cadadascun dels casos anteriors.

  • Exercici 3

Modifica l'exercici 1 per afegir al costat del número parell o imparell en funció de si aquest és parell o imparell.

  • Exercici 4

Afegiu una nova columna a la taula EMP per afegirs asteriscs “*”

  • Exercici 5

Crea un bloc PL/SQL que introdueixi en la columna creada anteriorment un asterisc per cada 100$ d'un empleat seleccionat per teclat al introduir el seu número d'empleat. Arrodoniu el sou fins el número enter més proper.

T7- Tipus de dades compostes

Crea una nova taula per emmagatzemar salaris i sous. 

SQL> CREATE TABLE top_dogs
  2  (name    VARCHAR2(25),
  3   salary  NUMBER(11,2));
  • Exercici 1

Escriu un bloc PL/SQL que inicialitzi les 3 primeres posicions d'una taula de registres (nom, salari) i a continuació actualitzi la taula top_dogs amb aquesta informació.

  • Exercici 2

Escriu un bloc PL/SQL per recuperar el nom i el sou d'un empleat concret de la taula EMP a partir de la introducció del seu número d'empleat.

a. Declara dues taules: rev_salary_table per emmagatzemar el percentatge de augment de salari. i emp_table per guardar l'empleat tractat.

b. Inicialitza les posicions, corresponents als números de departament de la taula dept, amb els diferents percentatges: 

      10 -->  2; 20 --> 3; 30 --> 4; 40 --> 3 ...

c. Guarda el nom i salari en la taula emp_table en la posició corresponent al departament de l'empleat.

d. Finalment guarda en la taula top_dogs el nom de l'empleat i el seu salari revisat en funció del departament al que pertany.

T8- Cursors explícits

  • Exercici 1

Previ: buida la taula TOP_DOGS.

Crea un bloc pl/sql que determini els n empleats amb sous més alts (suposeu que no hi ha dos empleats o més amb el mateix sou):

a. obtenir un numero "n" entrat per teclat per l'usuari amb un paràmetre de substitució.

b. en un bucle, obtenir els cognoms i sous d'aquests "n" empleats de la taula EMP

c. guarda aquestes dades en la taula TOP_DOGS.

d. Suposa que no hi ha dos empleats amb al mateix sou.

e. comprova varis casos: n=0 i n més gran que el número màxim d'empleats.

  • Exercici 2

Considera el cas de que varis empleats tinguin el mateix salari. Al igual que l'exercici anterior crea un bloc pl/sql que determini els n empleats amb sous més alts, però en aquest cas si se cal mostrar totes les persones que tinguin un mateix sou.

a. Si l'usuari introdueix n=2 han d'aparèixer tres registres (king, ford i scott, aquest últims tenen el mateix sou.

b. Si l'usuari introdueix n=3 han d'aparèixer 4 registres (king, ford i scott i jones).

c. Buida totes les files de la taula TOP_DOGS abans de prova l'exercici.

T9- Conceptes avançats de cursors explícits

  • Exercici 1

Ejecute una consulta pera recuperar todos los departamentos y empleados de cada departamento. Inserte los resultados en la tabla MESSAGES.

Utilice un cursor per a recuperar el número de departament i transfieralo a un cursor para recuperar los empleados de este departamento. 

  RESULTS
  -------------------------
  KING - Department 10
  CLARK - Department 10
  MILLER - Department 10
  JONES - Department 20
  FORD - Department 20
  SMITH - Department 20
  SCOTT - Department 20
  ADAMS - Department 20
  BLAKE - Department 30
  MARTIN - Department 30
  ALLEN - Department 30
  TURNER - Department 30
  JAMES - Departmens 30
  WARD - Department 30
  14 rows selected.
  • Exercici 2

Modifica l'exercici p6q5 per incorporar la funcionalitat FOR UPDATE i WHERE CURRENT OF al procesament de cursors. 

  EMPNO  SAL   START
  -----  ----  ----------------
   8000  
   7900   950  **********
   7844  1500  ***************

T10- Gestió d'excepcions

  • Exercici 1

Escriu un bloc PLSQL per seleccionar el nom de l'empleat amb un valor concret de sou. Cal que demanis el sou a l'usuari mitjançant un paràmetre SQL*PLUS.

a. si el sou introduït torna més d'una fila, gestiona l'excepció amb un manegador d'excepcions apropiat i afegeix a la taula MESSAGES el texte: "Més d'un empleat amb un sou de <sou>".

b. si el sou introduït no torna cap fila, gestiona l'excepció amb un manegador d'excepcions apropiat i afegeix a la taula MESSAGES el texte: "Cap empleat amb un sou <sou>".

c. si el sou introduït només torna una fila, afegeix a la taula MESSAGES el nom del empleat i l'import del sou.

d. Si es produeix qualsevol altra excepció, gestiona-la amb un manegador d'excepcions apropiat i afegeix a la taula MESSAGES el texte: "S'ha produït algun altre error".

e. Prova el bloc amb diferents cassos i assegurat de que funciona correctament.

  • Exercici 2

Modifica l'exercici 3 del tema 5 t5p3.sql per afegir manegadors d'excepcions definida per l'usuari.

a. escriu un manegador per a que comuniqui missatge a l'usuari dient que el departament especificat no existeix, si és el cas.

b. fes la comprovació amb un departament que no existeixi.

  • Exercici 3

Escriu un bloc PLSQL que imprimeixi els nombre d'empleats que guanyen 100$ més o menys que un valor introduït per teclat.

a. si no hi ha cap empleat dins aquest rang mostra un missatge al usuari indicant l'error que s'ha produït.

b. si hi ha un o més empleats dins aquests rang el missatge hauria d'indicar quants empleats hi ha en aquest rang de sous.

c. gestiona qualsevol altra excepció indicant que s'ha produït un altre tipus d'error.

T11- Creació de procediments

  • Exercici 1

a) Crea un procediment anomenat ADD_PROD per insertar un producte nou a la taula PRODUCT

b) Compila el codi, cridar al procediment i consulta la taula PRODUCT per veure els resultats.

c) Crida de nou el procediment, passat l'identificador de producte 100860 ¿què passa i per què?

  • Exercici 2

a) Crea un procediment anomenat UPD_PROD per actualitzar la descripció d'un producte de la taula PRODUCT. Inclou la manipulació d'excepcions necessària.

b) Compila el codi, crida al procediment i consulta la taula PRODUCT per veure els resultats. Comprova el control d'excepcions, intentant modificar un producte que no existeix.

  • Exercici 3

a) Crea un procediment anomenat DEL_PROD per esborrar un producte de la taula PRODUCT. Inclou la manipulació d'excepcions necessària.

b) Compila el codi, crida al procediment i consulta la taula PRODUCT per veure els resultats. Verifica també el control d'excepcions intentant esborrar un producte que no existeix.

  • Exercici 4

a) Crea un procediment que a partir del codi d'un empleat (empno), recuperi de la taula EMP el seu salari i ofici.

b) Compila el codi, crida al procediment i visualitza el salari i l'ofici per a l'empleat amb codi 7839·

c) Crida una altra vegada al procediment passant ara el número de empleat 9898. Què passa? Per què?

T12- Creació de funcions

  • Exercici 1

a) Crea una funció anomenada Q_PROD que retorni la descripció d'un producte a una variable host (una variable global).

b) Compila el codi, crida a la funció i tot seguit consulta la variable host per a veure el resultat.

  • Exercici 2

a) Crea una funció emmagatzemada ANNUAL_COMP que ens retorni el salari anual quan li passem el salari mensual i la comissió d'un empleat. La funció ha de retornar el salari anual definit per (sal*12)+comm. Assegura't que la funció controla valors NULL.

b) Utilitza la funció anterior en una sentència SELECT contra la taula EMP.

  • Exercici 3

Crea un procediment, NEW_EMP, per inssertar un empleat nou dins de la taula EMP. El procediment haurà de contenir una crida a la funció VALID_DEPTNO per comprovar si existeix en la taula del departament especificat per al nou empleat.

a) Crea la funció VALID_DEPNO per tal que ens validi un número de departament especificat. La funció ha de retornar un BOOLEÀ.

b) Crea el procediment NEW_EMP per afegir un nou empleat a la taula EMP. S'hauria d'afegir un nou registre a la taula EMP si la funció retorna TRUE. Si retorna FALSE, el procediment hauria d'alertar a l'usuari amb un missatge apropiat. Defineix valors DEFAULT per a la majoria d'arguments. La comissió per defecte és 0, el salari per defecte és 1000, el número de departament per defecte és 30, l'ofici per defecte és SALESMAN i el cap per defecte és 7839.

c) Comprova el procediment NEW_EMP afegint un nou empleat (7777) al departament 99 (HARRIS). Deixar la resta de paràmetres amb el seus valors per defecte. Quin és el resultat?

d) Comprovar el nou procediment NEW_EMP afegint un nou empleat (8888) al departament 30 (MIKEL). Deixar la resta de paràmetres amb els seus valors per defecte. Quin és el resultat?

T13- Creació de paquets

  • Exercici 1

Crea la especificació i el cos d'un paquet anomenat PROD_PACK que contingui els procediments creats ADD_PROD, UPD_PROD i DEL_PROD i la funció Q_PROD.

a) Fer tots els programes públics. (considereu si seguiu necessitant els procediments i funcions empaquetats com objectes independents)

b) Crida al procediment DEL_PROD.

c) Consulta la taula PRODUCT per veure el resultat.

  • Exercici 2

a) Crea l'especificació i el cos d'un paquet anomenat EMP_PACK que contingui els procediment NEW_EMP com a construcció pública i la funció VALID_DEPTNO com a construcció privada.

b) Crida al procediment NEW_EMP utiitzant el valor 99 com a número de departament.

c) Crida al procediment NEW_EMP utiitzant el valor 30 com a número de departament.

  • Exercici 3

Crea un paquet anomenat CHK_PACK que contingui els procediments CHK_HIREDATE i CHK_DEPT_MGR. Fes-los com a programes públics.

a) El procediment CHK_HIREDATE comprova si la data de contractació d'un empleat està dins el rang següent (sysdate-50 years, sysdate+3months)

Nota: si la data és invalida ha d'aparèixer un missatge d'error indicant perquè no s'accepta aquesta data. Utilitza una constant per a referir-se al límit de 50 anys. Si el valor de la data de contractació és un valor nul, aquesta serà considerada una data de contractació invalida.

b) El procediment CHK_DEPT_MGR comprova la combinació del departament i del director per a un empleat donat. Això significa que el número de director facilitat ha de ser igual al número de director que supervisa el departament de l'empleat.

Nota: Si la combinació número/director és invalida ha d'aparèixer un missatge d'error. Assegura't de controlar el cas en el que no hi ha director pel departament.

c) Prova el procediment CHK_HIREDATE mitjançant la comanda següent: 

   SQL> execute chk_pack.chk_hiredate('01-ENE-47')

d) Prova el procediment CHK_HIREDATE mitjançant la comanda següent: 

   SQL> execute chk_pack.chk_hiredate(NULL)

e) Prova el procediment CHK_HIREDATE mitjançant la comanda següent: 

   SQL> execute chk_pack.chk_hiredate('01-ENE-98')

T15- Creació de triggers

  • Exercici 1

Les operacions DML sobre taules només es permetran en hores de feina, es a dir, entre les 8:45 del matí i las 5:50 de la tarda; de dilluns a divendres.

a) crea un procediment emmagatzemat anomenat SECURE_DML que mostri un missatge d'error, en cas de no complir la regla anterior, com "Només es pot modificar dades en horari de feina".

  • Exercici 2

Crea un trigger en la taula PRODUCT que cridi al procediment anterior.

a) prova el procediment modificant la franja horària proposada anteriorment e intentant afegir un nou registre en la taula PRODUCT. Després de la comprovació restableix els valors originals.

  • Exercici 3

La comissió d'un venedor canviaria amb qualsevol comanda nova o canvis en les comandes existents. La seva comissió s'emmagatzema en la columna COMM de la taula EMP. En la taula CUSTOMER s'assigna un venedor a un client particular.

a) crea un procediment que actualitzarà la comissió del venedor. Utilitzaràs paràmetres per enviar l'identificador del client, el total antic de la comanda i el total nou de la comanda, des del trigger que es fa la crida. El procediment necessitarà localitzar el codi del empleat en la taula CUSTOMER i actualitzar el registre del venedor en la taula EMP, afegint una nova comissió al valor existent (percentatge de comissió el 5% del total de la comanda).

b) Crea un trigger en la taula ORD que cridi al procediment, passant els paràmetres necessaris.

c) Modifica la comanda 601 per assignar-li un total de 3$. Comprova que la comissión de WARD s'ha incrementat en 0.03. La comissió original era de 500.

  • Exercici 4

A las taules EMP i DEPT se'ls aplica una sèrie de regles de negoci.

Implementa les regles de negoci mitjançant triggers.

Reglas de negocio:

1. La taula EMP hauria de contenir exactament un PRESIDENT. Comproveu la resposta.

2. Els salaris només podran augmentar-se, no disminuir-se.

3. Si un departament es trasllada a una altra ubicació, cada empleat d'aquest departament tindrà automàticament un increment de salari del 2%.

Solucions exercicis

Obtingut de «http://wikiserver.infomerce.es/index.php?title=M2_-_Bases_de_dades_/_Exercicis_UF3:_PL-SQL&oldid=4874»