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

Revisió del 17:12, 7 maig 2014

Contingut

1 T3- Declaració de variables
2 T4- Sentencies executables
3 T5- Interacció amb el servidor Oracle
4 T6- Creació d'estructures de control
5 T7- Tipus de dades compostes
6 T8- Cursors explícits
7 T9- Conceptes avançats de cursors explplícits
8 T10- Gestió d'excepcions
9 T11- Creació de procediments
10 T12- Creació de funcions
11 T13- Creació de paquets
12 T15- Creació de triggers
13 Solucions exercicis

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

Exercici 1

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. Crea una nova taula per emmagatzemar salaris i sous.

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

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

c. 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 ...

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

e. 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 explplí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.

Exercici 2

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

  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.

T10- Gestió d'excepcions

EJERCICIO 1

Escribe una bloque PLSQL para seleccionar el nombre del empleado con un valor concreto de sueldo preguntado al usuario.

a) si el sueldo devulve más de una fila gestione la excepción con un manejador de excepciones apropiado i inserta dentro de la tabla MESSAGES "Más de un empleado con el mismo sueldo"

b) si el sueldo no devuelve ninguna fila, escribir "ningun empleado con este salario"

c) si el sueldo sólo devuelve una sola fila, insertar el nombre del empleado y el sueldo.

d) si se produce cualquier otra excepción introducir "se produjo algún otro error"

e) pruebe el bloque con los distintos casos y verifica que funciona correctamente.

SET VERIFY OFF; ACCEPT salari PROMPT 'Introdueixi el salari a buscar: ' DECLARE

  v_ename emp.ename%TYPE;

  v_sal emp.sal%TYPE :=&salari;

BEGIN

  SELECT ename*  INTO v_ename FROM emp WHERE emp.sal=v_sal;
  INSERT INTO messages (results) VALUES(v_ename||' - '||v_sal);

EXCEPTION

   WHEN NO_DATA_FOUND THEN   
              INSERT INTO missatges VALUES('No hi ha cap empleat amb aquest salari introduït' || TO_CHAR(v_sal));
   WHEN TOO_MANY_ROWS THEN   
             INSERT INTO missatges VALUES('Hi ha més d'un empleat amb aquest sou de ' || TO_CHAR(v_sal));
   WHEN OTHERS THEN  
              INSERT INTO missatges VALUES('algún altre error sha produit')

END; /

select * from missatges;

EJERCICIO 2

Modifica el ejercicio 3 del tema 4 p4q3.sql para añadir un manejador de excepciones.

a) escriba un manejador para que comunique un mensaje al usuario diciendo que el departamento especificado no existe si este es el caso.

b) prueba con un departamento que no exista

SET SERVEROUTPUT ON;

  ACCEPT lloc_dept PROMPT  'Introdueixi la localitat del departament:  '
  ACCEPT num_dept PROMPT  'Introdueixi el nombre del departament:  '

DECLARE

   v_llocdept dept.loc%TYPE :='&lloc_dept';
   v_numdept DEPT.DEPTNO%type:= &num_dept;
   v_dept dept%ROWTYPE;

BEGIN

     SELECT * INTO v_dept FROM dept WHERE v_dept.deptno=v_numdept;
     UPDATE dept
     SET loc=v_llocdept
     WHERE deptno=v_numdept;

EXCEPTION

   WHEN NO_DATA_FOUND THEN  
       DBMS_OUTPUT.PUT_LINE('NO EXISTEIX EL DEPARTAMENT');

END; /

EJERCICIO 3

Escriu un bloc PLSQL que imprimeixi els noms dels empleats que guanyen 100$ mes 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 produit un altre tipus d'error.

SET SERVEROUTPUT ON;

ACCEPT sal_emp PROMPT 'Introdueixi el salari: '

DECLARE

   v_sal emp.sal%TYPE :=&sal_emp;
   v_nemp number(4);
   e_ningu EXCEPTION;

BEGIN

   SELECT COUNT(*)  INTO v_nemp  
   FROM emp
   WHERE sal BETWEEN (v_sal-100) and (v_sal+100);

   IF v_nemp = 0 THEN RAISE e_ningu;
   ELSE DBMS_OUTPUT.PUT_LINE(v_nemp||' '||'COBREN AQUEST SALARI');
   END IF;

EXCEPTION

   WHEN e_ningu THEN  
       DBMS_OUTPUT.PUT_LINE('Ningú no té aquest salari');

END; /

ALTRA VERSIÓ EXERICI 1--------------------------------------------

SET VERIFY OFF;

ACCEPT salari PROMPT 'Introdueixi el salari a buscar: '

DECLARE

v_sal emp.sal%TYPE :=&salari;

v_emp emp%ROWTYPE;

ERROR2 VARCHAR2(25);

BEGIN

      RAISE_APPLICATION_ERROR(-20000,'hola');
      SELECT * INTO v_emp FROM emp WHERE emp.sal=v_sal;
      DBMS_OUTPUT.PUT_LINE(v_emp.ename);

EXCEPTION

   WHEN NO_DATA_FOUND THEN   
       INSERT INTO missatges VALUES('Ningú no compleix la condixció');
       DBMS_OUTPUT.PUT_LINE('Ningú no compleix la condixció');
   WHEN TOO_MANY_ROWS THEN   
       INSERT INTO missatges VALUES('Nhi ha més dun');
       DBMS_OUTPUT.PUT_LINE('Nhi ha més dun');
   WHEN OTHERS THEN  
        ERROR2:=SQLERRM;
       INSERT INTO missatges VALUES(ERROR2);
       DBMS_OUTPUT.PUT_LINE(SQLERRM);

END; /

T11- Creació de procediments

EJERCICIO 1

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

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

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

EJERCICIO 2

a) Crea un procediment anomenat UPD_PROD per actualitzar la descripció d'un producte de la taula PRODUCT. Incloure la manipulació d'excepcions necessaris.

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

EJERCICIO 3

a) Crea un procediment anomenat DEL_PROD per esborrar un producte de la taula PRODUCT. Incloure la manipulació d'excepcions necessaris.

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

EJERCICIO 4

a) Crea un procediment per consultar la taula EMP per tal que recuperi el salari i l'ofici d'un empleat (emp_no).

b) Compilar el codi, cridar al procediment i visualitzar el salari i l'ofici per l'empleat 7839·

c) crida una altra vegada al procediment passant ara el número d'un 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 varible global).

b) Compila el codi, crida a la funció i tot seguit consulta la variable host per a verure 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

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

EXERCICI 3

Crea un procediment NEW_EMP, per insertar 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) Crear una funció VALID_DEPNO per tal que ens validi un número de departament especificat. La funció ha de retornar un BOOLEÀ.

b) Crear un 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.

c) Comprovar el procediment NEW_EMP afegint un nou nom d'empleat 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 nom d'empleat al departament 30 (HARRIS). Deixar la resta de paràmetres amb els seus valors per defecte. Quin és el resultat?

T13- Creació de paquets

EXERCICI 1

a) 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.

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

c) Crida al procediment DEL_PROD

d)Consulta la taula PRODUCT per veure el resultat.

EXERCICI 2

a) Crea i 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

a) 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 primer procediment comprova si la data de contracctació d'oun 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 segon procediment comprova si 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-JAN-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-JAN-98')

T15- Creació de triggers

EJERCICIO 1

Las operaciones DML sobre las tablas sólo se permitirán en horas de trabajo, es decir, entre las 8:45 de la mañana i las 5:50 de la tarde; de lunes a viernes.

a) crea un procedimiento almacenado llamado SECURE_DML que muestre un mensaje de error, en caso de no cumplir la regla especificada anteriormente como "Sólo puede modificar datos durante horas oficiales de trabajo".

EJERCICIO 2

Crea un trigger en la tabla PRODUCT que llame al procedimiento anterior.

a) prueba el procedimiento modificando la franja horaria propuesta anteriormente e intentando insertar un nuevo registro en la tabla PRODUCT. Después de la comprovación reestablezca los valores originales.

EJERCICIO 3

La comisión de un vendedor cambiaría con cualquier pedido nuevo o por cambios en los pedidos existentes. Su comisión se almacena en la columna COMM de la tabla EMP. En la tabla CUSTOMER se asigna un vendedor a un cliente particular.

a) crear un procedimiento que actualizará la comisión del vendedor. Usaremos parámetros para enviar el identificador del cliente, el total antiguo del pedido i el total nuevo, desde el trigger que hace la llamada. El procedimiento necesitará localizar el número del empleado en la tabla CUSTOMER i actualizar el registro del vendedor en la tabla EMP, añadiendo una nueva comisión al valor existente (porcentaje de comisión el 5% del total del pedido)

b) Crear un trigger en la tabla ORD el cual llamará al procedimiento, pasando los parámetros necesarios.

c) Modificar el pedido 601 para asignarle un total de 3$. Verficar que la comisión de WARD se ha incrementado en 0.03. La comisión original era de 500.

EJERCICIO 4

A las tablas EMP i DEPT se les aplica una serie de reglas de negocio.

a) decidir cómo implementar cada regla por medio de constraints i triggers. ¿qué restricciones o triggers se necesitarán i qué problemas podemos esperar?

b) implemente las reglas de negocio mediante triggers.

reglas de negocio:

1. Los vendedores siempre deberían recibir una comisión. Los empleados que no son vendedores nunca deberían recibir una comisión. (implemente esta regla con una constraint) 2. La tabla EMP debería contener exactamente un PRESIDENT.

3. Los salarios solo podrían aumentarse, no disminuirse.

4. Si un departamento se traslada a otra parte, cada empleado de dicho departamento tendrá automáticamente un incremento de salario del 2%.