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

Revisió del 18:35, 11 març 2016 (mostra codi font) Rsort (Discussió | contribucions) (→‎T7- Maneig d'excepcions predefinides) ← Vés a l'edició anterior		Revisió del 18:59, 11 març 2016 (mostra codi font) Rsort (Discussió | contribucions) (→‎T8- Maneig d'excepcions predefinides) Vés a l'edició següent →
Línia 362:		Línia 362:
	==T8- Maneig d'excepcions predefinides==		==T8- Maneig d'excepcions predefinides==
−	[[Imatge:M2UF3_MEP_3.png |250px|center| Maneig d'excepcions predefinides]]	+	'''1.-''' En aquesta pràctica, escriuràs un bloc PL/SQL que apliqui una excepció predefinida per a processar un únic registre a la vegada. El bloc PL/SQL seleccionarà el nom de l'empleat amb un valor de salari determinat.
		+
		+	:a) Executa l'odre de l'arxiu lab_05_01.sql per tornar a crear la taula messages.
		+
		+	:b) En la secció de declaracions, declara dues variables: v_ename de tipus employees.last_name i v_emp_sal del tipus employees.salary.
		+	:Inicialitza l'última en 6000.
		+
		+	:c) En la secció executable, recupera els cognoms dels empleats amb salaris iguals al valor de v_emp_sal. Si el salari introduït torna només una fila, afegeix en la taula messages el nom i el import del salari de l'empleat.
		+	:'''Nota:' no utilitzis cursors explícits.
		+
		+	:d) Si el salari introduït no torna cap fila, manega l'excepció amb un manegador d'excepcions adequat i afegeix en la taula messages el missatge "No employee with a salary of <salary>".
		+
		+	:e) Si el salari introduït torna varies files, manega l'excepció amb un manegador d'excepcions adequat i afegeix en la taula messages el missatge "More than one employee with a salary of <salary>".
		+
		+	:f) Manega qualsevol altra excepció amb un manegador d'excepcions adequat i afegix en la taula messages el missatge "Some other error ocurred".
		+
		+	:g) Mostra les files de la taula messages per comprovar si el bloc PL/SQL s'ha executat correctament. La sortida és la següent:
		+
		+	[[Imatge:M2UF3_MEP_1.png |250px|center| Maneig d'excepcions predefinides]]
		+
		+	:h) Canvia el valor inicialitzat de v_emp_sal a 2000 i torna a executar. La sortida és la següent:
		+
		+	[[Imatge:M2UF3_MEP_2.png |250px|center| Maneig d'excepcions predefinides]]
		+
		+	'''2.-''' En aquesta pràctica, escriuràs un bloc PL/SQL que declari una excepció per a l'error de Oracle Server ORA-02292 (integrity constraint violated - child record found). El bloc comprovarà l'excepció i mostrarà el missatge d'error.
		+
		+	:a) En la secció de declaracions, declara una excepció e_childrecord_exists.
		+	:Associa l'excepció declarada al error d'Oracle Server estàndard -02292.
		+
		+	:b) En la secció executable, mostra "Deleting department 40...". Inclou una sentència DELETE per suprimir el departament amb department_id 40.
−	'''1.-'''	+	:c) Inclou una secció d'excepcions per manegar l'excepció e_childrecord_exists i mostra el missatge adequat.
−	:a)	+	:La sortida d'exemple és la següent:
−	:b)
−	:c)
−	:d)
−	:e)
−	:f)
−	:g)
−	:h)
−	'''2.-'''	+	[[Imatge:M2UF3_MEP_3.png |250px|center| Maneig d'excepcions predefinides]]
−	:a)
−	:b)
−	:c)
	==T5- Interacció amb el servidor Oracle ==		==T5- Interacció amb el servidor Oracle ==

---

Revisió del 18:59, 11 març 2016

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.

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

T3- Escriptura de Sentències Executables

En aquesta pràctica, examinaràs i escriuràs sentències executables.

1.- Avalua el bloc PL/SQL anterior i determina el tipus de dada i el valor de cada una de les següents variables, segons les regles dels àmbits:

a) Valor de v_weight en la posició 1:

b) Valor de v_new_locn en la posició 1:

c) Valor de v_weight en la posició 2:

d) Valor de v_message en la posició 2:

e) Valor de v_new_locn en la posició 2:

2.- En el bloc anterior, determina el valor i el tipus de dada en cada un dels següents cassos:

a) Valor de v_customer en el bloc anidat:

b) Valor de v_name en el bloc anidat:

c) Valor de v_credit_rating en el bloc anidat:

d) Valor de v_customer en el bloc principal:

e) Valor de v_name en el bloc principal:

f) Valor de v_credit_rating en el bloc principal:

3.- Utilitza la mateixa sessió que vas utilitzar per executar les pràctiques de la lliçó anomenada "Declaració de Variables PL/SQL". Se has obert una nova sessió, executa lab_02_05_soln.sql. A continuació, edita lab_02_05_soln.sql de la siguent forma:

a) Amb la sintaxis de comentaris d'una sola línia, comenta les línies que creen les variables d'enllaç i activa SERVEROUTPUT.

b) Amb els comentaris de varies línies, comenta en la secció executable les línies que assignen valors a les variables d'enllaç.

c) En la secció de declaracions:

1. Declara e inicialitza dues variables temporals per substituir les variables d'enllaç comentades.

2. Declara dues variables addicionals: v_fname de tipus VARCHAR2 i

tamany 15, i v_emp_sal del tipus NUMBER i tamany 10.

d) Inclou la següent sentència SQL en la secció executable:

     SELECT first_name, salary INTO v_fname, v_emp_sal
     FROM employees WHERE employee_id=110;

e) Canvia la línia que imprimeix “Hello World” per a que imprimeixi “Hello” i el nom. A continuació, comenta les línies que mostren les dates i imprimeix les variables d'enllaç.

f) Calcula l'aportació de l'empleat al fons de previsió (PF). PF és el 12% del salari bàsic, i el salari bàsic es el 45% del salari. Utilitza les variables locals per fer el càlcul. Intenta utilitza només una expressió per calcular el valor de PF. Imprimeix el salari de l'empleat i la seva aportació a PF.

g) Executa i guarda el script com lab_03_03_soln.sql. La sortida de l'exemple és la següent:

         anonymous bloc completed
           Hello Jhon
         YOUR SALARY IS : 8200
         YOUR CONTRIBUTION TOWARDS PF: 442.8

T4- Interacció amb Oracle Server

En aquesta pràctica utilitzarà codi PL/SQL per interactuar amb Oracle Server.

1.- Crea un bloc PL/SQL que seleccioni l'identificador de departament superior en la taula departments i l'emmagatzemi en la variable v_max_deptno. Mostra l'identificador de departament superior.

a) Declara una variable v_max_deptno de tipus NUMBER en la secció de declaracions.

b) Inicia la secció executable amb les paraules BEGIN e inclou una sentència SELECT per recupera el valor màxim de department_id de la taula departments.

c) Mostra v_max_deptno i finalitza el bloc executable.

d) Executa i guarda el script lab_04-01_soln.sql. La sortida és la següent:

2.- Modifica el bloc PL/SQL creat en el pas 1 per afegir un nou departament en la taula departments.

a) Carrega el script lab_04_01_soln.sql. Declara dues variables:

v_dept_name de tipus departments.department_name i v_dept_id de tipo NUMBER

Assigna 'Eduction' a v_dept_name en la secció de declaracions.

b) Ja has recuperat el número de departament superior actual de la taula departments. Suma-li 10 i assigna-li el resultat a v_dept_id.

c) Inclou una sentència INSERT per insertar dades en les columnes department_name, department_id y location_id de la taula departments.

Utilitza valors en dept_name i dept_id per a department_name i department_id, respectivament, i utilitza NULL per a location_id.

d) Utilitza l'atribut SQL SQL%ROWCOUNT per mostrar el número de files que es veuen afectades.

e) Executa una sentència SELECT per comprovar si s'ha afegit el nou departament. Acaba el bloc PL/SQL amb "/" e inclou una sentència SELECT en el script.

f) Executa i guarda el script com lab_04_02_soln.sql. La sortida d'exemple és la següent:

3.- En el pas 2, defineix location_id en NULL. Crea un bloc PL/SQL que actualitzi location_id a 3000 per al nou departament.

a) Inicia el bloc executable amb la paraula clau BEGIN. Inclou la sentència UPDATE per definir location_id en 3000 per al nou departament.

b) Acaba el bloc executable en la paraula clau END. Acaba el bloc PL/SQL amb "/" e inclou una sentència SELECT per mostrar el departament que ha actualitzat.

c) Inclou una sentència DELETE per suprimir el departament agregat.

d) Executa i guarda el script com lab_04_03_soln.sql. La sortida de l'exemple és la següent:

ADDICIONALS

EXERCICI 1

Per a aquest exercici, es necessita una taula temporal per emmagatzemar els resultats.

1.- Executa el script lab_ap_04.sql que crea la taula descrita a continuació:

2.- Escriu un bloc PL/SQL que realitzi les següents accions:

a) Declara dues variables i assigna el següents valors a aquestes variables:

b) Emmagatzema els valors d'aquestes variables en les columnes adients de la tabla TEMP.

3.- Verifica els resultats consultant la taula TEMP. La sortida resultant ha de ser com la següent:

EXERCICI 2

En aquest exercici, utilitzaràs dades de la taula employees.

1.- Escriu un bloc PL/SQL per determinar el nombre d'empleats que treballen en un determinat departament. El bloc PL/SQL haurà de:

• Utilitzar una variable de substitució per emmagatzemar un número de departament
• Imprimir el nombre de persones que treballen en el departament especificat

2.- Quan s'execute el bloc , apareixerà una finestra de variable de substitució. Introdueix un número de departament vàlid i fes clic en OK. La sortida resultant haurà de tenir un aspecte similar al següent:

T5- Escriptura de les estructures de control

En aquesta pràctica, crearàs blocs PL/SQL que incorporin bucles i estructures de control condicionals. En aquesta pràctica es comprovarà el teu coneixement de varies sentències IF i construccions L00P.

1.- Executa l'ordre en l'arxiu lab_05_01.sql per crear la taula messages. Escriu un bloc PL/SQL per afegir números en la taula messages.

a) Afegeix el números del 1 al 10, excloent el 6 i el 8.

b) Confirma abans del final de bloc.

c) Executa la sentència SELECT per verificar que el bloc PL/SQL ha funcionat.

Resultat: has de veure la següent sortida:

2.- Executa el script lab_05_=2.sql. Aquest script crea una taula emp, que és una replica de la taula employees. Modifica la taula emp per agegir una nova columna, stars, de tipus de dades VARCHAR2 i una mida de 50. Crea un bloc PL/SQL que afegeixi un asterisc en la columna stars per cada $1000 del salari de l'empleat. Guarda aquest script com lab_05_02_soln.sql.

a) En la secció de declaracions del bloc, declara una variable v_empno del tipus emp.employee_id e inicialitza-la en 176. Declara una variable v_asterisk del tipus emp.stars e inicialitza-la en NULL. Crea una variable v_sal del tipus emp.salary.

b) En la secció executable, escriu lògica per afegir un asterisc (*) a la cadena per cada 1000 dòlars del salari. Per exemple, si l'empleat guanya 8000 dòlars, la cadena d'asteriscs ha d'incloure vuit asteriscs. Si l'empleat guanya 12500 dòlars, la cadena d'asteriscs ha d'incloure 13 asteriscs.

c) Actualitza la columna stars per a l'empleat amb la cadena d'asteriscs.

Confirma abans del final del bloc.

d) Mostra la fila de la taula emp per verificar que el bloc PL/SQL s'ha executat correctament.

e) Executa i guarda el script com a lab_05_02_soln.sql. La sortida és la següent:

T6- Treballar amb tipus de dades compostes

1.- Escriu un bloc PL/SQL per imprimir informació sobre un país determinat.

a) Declara un registre PL/SQL basat en l'estructura de la taula countries.

b) Declara una variable v_countryid. Assigna-li CA a v_countryid.

c) En la secció de declaracions, utilitza l'atribut %ROWTYPE i declara la variable v_countryid_record del tipus countries.

d) En la secció executable, obté tota la informació de la taula countries mitjançant v_countryid. Mostra la informació seleccionada sobre el país. la sortida d'exemple és la següent:

e) Executa el bloc PL/SQL pels països amb els identificadors DE, UK i US.

2.- Crea un bloc PL/SQL per recuperar els noms d'alguns departaments de la taula departments e imprimir el nom de cada departament en la pantalla, incorporant una matriu associativa. Guarda el script com lab_06_02_soln.sql.

a) Declare una taula INDEX BY dept_table_type del tipus departments.department_name. Declara una variable my_dept_table del tipus dept_table_type per emmagatzema temporalment els noms dels departaments.

b) Declara dues variables: f_loop_count i v_deptno del tipus NUMBER. Assigna 10 a f_loop_count i 0 a v_deptno.

c) Amb un bucle, recupera els noms de 10 departaments i emmagatzema'ls en la matriu associativa. Comença pel department_id 10. Augmentav_deptno en 10 per a cada interacció de bucle. La següent taula mostra els valors department_id per als que cal recuperar department_name.

d) Si utilitzes un altre bucle, pots recuperar els noms dels departaments de la matriu associativa i mostrar-los.

e) Executa i guarda el script com a lab_06_02_soln.sql. La sortida és la segúent:

3.- Modifica el bloc creat en la Pràctica 2 per recuperar tota la informació de cada departament de la taula departments i mostra-la. Utilitza una matriu associativa amb el mètode de taula de registres INDEX BY.

a) Carga el script lab_06_02_soln.sql.

b) Has declarat que la matriu associativa sigui del tipus departments.department_name. Modifica la declaració de la matriu associativa per emmagatzemar temporalment el número, el nom i la ubicació de tots els departaments. Utilitza l'atribut %ROWTYPE.

c) Modifica la sentència SELECT per recuperar tota la informació del departament que està en la taula departments i emmagatzemar-la en la matriu associativa.

d) Si utilitzes un altre bucle, pots recuperar la informació dels departaments de la matriu associativa i mostrar-la.

La sortida d'exemple és la següent:

T7- Us de cursors explícits

En aquest tema, resoldràs dos exercicis:

• Primer, utilitzaràs un cursor explícit per processar un nombre de files d'una taula i omplir una altra taula amb els resultats mitjançant un bucle FOR de cursos.
• Desprès, escriuràs un bloc PL/SQL que processi la informació amb dos cursors, incloent un que utilitzi un paràmetre.

1.- Crea un bloc PL/SQL que realitzi les següents accions:

a) En la secció de declaracions, declara e inicialitza una variable anomenada v_deptno de tipus NUMBER. Assigna-li un valor de departament vàlid (consulta els valors de la taula del pas d).

b) Declara un cursor anomenat c_emp_cursor, que recuperi last_name, salary i mangager_id dels empleats que treballen en el departament especificat en v_deptno.

c) En la secció executable, utilitza el bucle FOR de cursor per realitzar operacions en les dades recuperades. Si el salari de l'empleat es menor que 5000 i si el identificador de superior es 101 0 124, apareix el missatge "<<last_name>> Due for a raise". En cas contrari, apareix el missatge "<<last_name>> Not Due for a raise"

d) Prova el bloc PL/SQL per als següents cassos:

2.- A continuació, escriu un bloc PL/SQL que declari i utilitzi dos cursors: un sense paràmetre i un altre amb paràmetre. El primer cursor recupera el número de departament i el nom del departament de la taula departaments per a tots els departaments que tinguin un identificador menor que 100. El segon cursor rep el número de departament com a paràmetre i recupera els detalls dels empleats que treballen en aquest departament i amb un employee_id menor de 120.

a) Declara un cursor c_dept_cursor per reuperar department_id i department_name per als departaments amb department_id menor que 100. Ordena per department_id.

b) Declara un altre cursor c_emp_curosr que prengui el número de departament com a paràmetre i recuperi les següents dades: last_name, job_id, hire_date i salary dels empleats que treballen en aquest departament, amb employee_id inferior a 120.

c) Declara les variables que contenen els valors recuperats de cada cursor. Utilitza l'atribut %TYPE per declara les variables.

d) Obra c_dept_cursor, utilitza un bucle simple i recupera els valors en les variables declarades. Mostra el número i el nom del departament. Utilitza l'atribut de cursor adequat per sortir del bucle.

e) Obra c_emp_cursor transferint el número de departament actual com a paràmetre. Inicia un altre bucle i recupera els valors de emp_cursor en les variables e imprimeix tots els detalls recuperats en la taula employees.

Nota

• Comprova si c_emp_cursor ja està obert abans d'obrir-lo.
• Utilitza l'atribut de cursor adequat per a la condició de sortida.
• Quan termina el bucle, imprimeix una línia desprès de haver mostrat els detalls de cada departament i tanca c_emp_cursor.

f) Finalitza el primer bucle i tanca c_dept_cursor. A continuació, finalitza la secció executable.

g) Executa el script. La sortida d'exemple és la següent:

3.- Crea un bloc PL/SQL que utilitzi un cursor explícit per determinar los n salaris més alts dels empleats.

a) Executa el scrilpt lab_07-2.sql per crear la taula top_salaries per emmagatzemar els salaris dels empleats.

b) En la secció declaracions, declara la variable v_num del tipus NUMBER que contengui el número n, que representa els n salaris més alts de la taula employees. Per exemple, per veure els cinc salaris més alts de la taula, introdueix un 5. Declara un altra variable sal del tipus employees.salary. Declara un cursor, c_emp_cursor, que recuperi els salaris dels empleats en ordre descendent.

Recorda que els salaris no han de estar duplicats.

c) En la secció executable, obra un bucle, recupera els n salaris principals e afegeix-los a la taula top_salaris. Pots utilitzar un bucle simple per realitzar operacions amb les dades. A més a més, utilitza els atributs %ROWCOUNT i %FOUND per a la condició de sortida.

Nota: assegura't d'agregar una condició de sortida per evitar un bucle infinit.

d) Desprès d'afegir-los en la taula top_salaries. mostra les files amb una sentència SELECT. La sortida que es mostra presenta els cinc salaris més alts de la taula employees.

e) Prova diferents cassos especials, com v_num = 0 o amb un v_num més gran que el nombre d'empleats de la taul employees. Buida la taula top_salaries després de cada prova.

T8- Maneig d'excepcions predefinides

1.- En aquesta pràctica, escriuràs un bloc PL/SQL que apliqui una excepció predefinida per a processar un únic registre a la vegada. El bloc PL/SQL seleccionarà el nom de l'empleat amb un valor de salari determinat.

a) Executa l'odre de l'arxiu lab_05_01.sql per tornar a crear la taula messages.

b) En la secció de declaracions, declara dues variables: v_ename de tipus employees.last_name i v_emp_sal del tipus employees.salary.

Inicialitza l'última en 6000.

c) En la secció executable, recupera els cognoms dels empleats amb salaris iguals al valor de v_emp_sal. Si el salari introduït torna només una fila, afegeix en la taula messages el nom i el import del salari de l'empleat.

Nota:' no utilitzis cursors explícits.

d) Si el salari introduït no torna cap fila, manega l'excepció amb un manegador d'excepcions adequat i afegeix en la taula messages el missatge "No employee with a salary of <salary>".

e) Si el salari introduït torna varies files, manega l'excepció amb un manegador d'excepcions adequat i afegeix en la taula messages el missatge "More than one employee with a salary of <salary>".

f) Manega qualsevol altra excepció amb un manegador d'excepcions adequat i afegix en la taula messages el missatge "Some other error ocurred".

g) Mostra les files de la taula messages per comprovar si el bloc PL/SQL s'ha executat correctament. La sortida és la següent:

h) Canvia el valor inicialitzat de v_emp_sal a 2000 i torna a executar. La sortida és la següent:

2.- En aquesta pràctica, escriuràs un bloc PL/SQL que declari una excepció per a l'error de Oracle Server ORA-02292 (integrity constraint violated - child record found). El bloc comprovarà l'excepció i mostrarà el missatge d'error.

a) En la secció de declaracions, declara una excepció e_childrecord_exists.

Associa l'excepció declarada al error d'Oracle Server estàndard -02292.

b) En la secció executable, mostra "Deleting department 40...". Inclou una sentència DELETE per suprimir el departament amb department_id 40.

c) Inclou una secció d'excepcions per manegar l'excepció e_childrecord_exists i mostra el missatge adequat.

La sortida d'exemple és la següent:

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

• Declaració de variables

• Sentencies executables

• Interacció amb el servidor Oracle

• Creació d'estructures de control

• Tipus de dades compostes

• Cursors explícits

• Conceptes avançats de cursors explícits

• Gestió d'excepcions

• Creació de procediments

• Creació de funcions

• Creació de paquets

• Creació de triggers