M2 - Bases de dades / Exercicis UF3: PL-SQL

De wikiserver
La revisió el 12:45, 7 març 2016 per Rsort (Discussió | contribucions) (→‎T2- Declaració de variables)
Dreceres ràpides: navegació, cerca

T1- Introducció a PL/SQL

  • 1.- Quin bloc PL/SQL dels següents s'executa correctament?
     a) BEGIN
        END;
     b) DECLARE
          v_amount INTEGER(10);
        END;
     c) DECLARE
        BEGIN
        END;
     d) DECLARE
          v_amount INTEGER(10);
        BEGIN
          DBMS_OUTPUT.PUT_LINE(v_amount);
        END;
  • 2.- Crea i executa un bloc anònim simple, la seva sortida ha de ser “Hello World”. Executa i guarda aquest sript com lab_01_02_soln.sql.
Introueix els següent codi en l'espai de treball i prem F5.
     SET SERVEROUTPUT ON
     BEGIN
       DBMS_OUTPUT.PUT_LINE(' Hello World ');
     END;
Cal que aparegui la sortida següent en el separador Sript Output:
Script Output
Prem el botó Save. Selecciona la carpeta on vols guardar l'arxiu. Introdueix lab_01_02_soln.sql com a nom de l'arxiu i prem Save.

T2- Declaració de variables

  • 1.- Especifica identificadors vàlids i no vàlids:
a) today
b) last_name
c) today’s_date
d) Number_of_days_in_February_this_year
e) Isleap$year
f) #number
g) NUMBER#
h) number1to7
  • 2.- Identifica les declaracions e inicialitzacions de variables vàlides i no vàlides:
a) number_of_copies           PLS_INTEGER;
b) PRINTER_NAME               constant VARCHAR2(10);
c) deliver_to                        VARCHAR2(10):=Johnson;
d) by_when                          DATE:= CURRENT_DATE+1;
  • 3.- Examina el següent bloc anònim i selecciona la frase que serà certa de entre les següents:
  DECLARE
    v_fname VARCHAR2(20);
    v_lname VARCHAR2(15) DEFAULT 'fernandez';
  BEGIN
    DBMS_OUTPUT.PUT_LINE(v_fname ||' ' ||v_lname);
  END;
a) El bloc s'executa correctament i s'imprimeix “fernandez”.
b) El bloc produeix un error perquè s'utilitza la variable fname sense inicialitzar-se.
c) El bloc s'executa correctament i s'imprimeix "null fernandez".
d) El bloc produeix un error perquè no es pot utilitzar la paraula clau DEFAULT per inicialitzar una variable de tipus VARCHAR2.
e) El bloc produeix un error perquè no es declara la variable v_fname.
  • 4.- Modifica un bloc anònim existent i guarda'l com un nou script.
a) Obre el script lab_01_02_soln.sql, creat en la Pràctica 1.
b) En aquest bloc PL/SQL, declara las següents variables:
1. v_today del tipus DATE. Inicialitza today amb SYSDATE.
2. v_tomorrow del tipus today. Utilitza l'atribut %TYPE per declarar aquesta variable.
c) En la secció executable:
1. Inicialitza la variable v_tomorrow amb una expressió, que calculi la data de demà (agrega un 1 al valor de today)
2. Imprimeix el valor de v_today i tomorrow desprès de imprimir “Hello World”
d) Guarda el script com a lab_02_04_soln.sql i executa'l.
La sortida de l'exemple és com la següent (els valors de v_today i v_tomorrow seran diferents per reflexar la data actual d'avui i la de demà):
         anonymous bloc completed
           Hello World
         TODAY IS : 07-MAR-16
         TOMORROW IS: 08-MAR-16
  • 5.- Edita el script lab_02_04_soln.sql.
a) Agrega codi per crear dues variables d'enllaç: b_basic_percent i

b_pf_percent. Ambdues de tipus NUMBER.

b) En la secció executable del bloc PL/SQL, assigna els valors 45 i 12 a

b_basic_percent i b_pf_percent, respectivament.

c) Termina el bloc PL/SQL amb “/” i mostra el valor de las variables d'enllaç

amb l'ordre PRINT.

d) Executa i guarda el script com a lab_02_05_soln.sql. La sortida de exemple és la següent:
­
         anonymous bloc completed
         b_basic_percent
         --
         45

         b_pf_percent
         --
         12

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=6104»