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

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.

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

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 les que imprimeixen 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

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.- Crea la taula descrita a continuació:

CREATE TABLE TEMP(
  NUM_STORE NUMBER(7,2),
  CHAR_STORE VARCHAR2(35),
  DATE_STORE DATE);

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:

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.

  CREATE TABLE messages(
    results VARCHAR2(100));

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.

   CREATE TABLE employees2
   AS
   SELECT *
   FROM employees;

   ALTER TABLE employees2 
   ADD (stars VARCHAR2(50));

a) En la secció de declaracions del bloc, declara una variable v_empno del tipus employees2.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:

Treballar amb tipus de dades compostes

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

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

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

c) 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:

d) 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:

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 el identificador de su superior (manager_id) 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) Obre 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) Obre 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.

CREATE TABLE top_salaries(
    salary NUMBER(7,2));

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, obre 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.

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:

Creació i us de procediments emmagatzemats

En aquesta pràctica, modificaràs scripts existents per crear i utilitzar procediments emmagatzemats.

1.- A partir de l'exercici 4 del tema2:

   DECLARE
     v_today DATE:=SYSDATE;
     v_tomorrow v_today%TYPE;
   BEGIN
     v_tomorrow:=v_today +1;
     DBMS_OUTPUT.PUT_LINE(' Hello World ');
     DBMS_OUTPUT.PUT_LINE('TODAY IS : '|| v_today);
     DBMS_OUTPUT.PUT_LINE('TOMORROW IS : ' || v_tomorrow);
   END;

a) Modifica el script per convertir el bloc anònim en un procediment anomenat greet. (Indicació: elimina també l'ordre SET SERVEROUTPUT ON).

b) Executa el script per crear el procediment. La sortida resultant ha de ser com la següent:

c) Guarda aquest script com lab_09_01_soln.sql.

d) Prem el botó Clear per netejar l'espai de treball.

e) Crea i executa un bloc anònim per cridar al procediment greet.

(Indicació:' assegura't d'activa SERVEROUTPUT al principi del bloc).

La sortida ha de ser similar a la següent:

2.- Modifica el script lab_09_01_soln.sql de la següent forma:

a) Elimina el procediment greet amb la següent ordre:

     DROP PROCEDURE greet;

b) Modifica el procediment per acceptar un argument de tipus VARCHAR2

anomena al argument p_name.

c) Imprimeix Hello <name> (es a dir, el contingut de l'argument) en lloc de Hello World.

d) Guarda el script com lab_09_02_soln.sql.

e) Executa el script per crear el procediment. La sortida resultant ha de ser com la següent:

f) Crea i executa un bloc anònim per cridar el procediment greet amb un valor de paràmetre. El bloc també ha de produir la sortida.

La sortida del exemple ha de ser similar a la següent:

Solucions exercicis

Conceptes fonamentals

• Introducció PL/SQL

• Declaració de variables

• Escriptura de sentencies executables

• Interacció amb Oracle Server

• Estructures de control

• Tipus de dades compostes

• Ús de cursors explícits

• Manegament d'excepcions

• Creació i us de procediments emmagatzemats