Diferència entre revisions de la pàgina «M2 - Bases de dades / Exercicis UF3: PL-SQL»
(→T3- Declaració de variables) |
(→T3- Declaració de variables) |
||
| Línia 35: | Línia 35: | ||
*'''Exercici 4''' | *'''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. | + | :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 | ||
:*[[M2 - Bases de dades / Exercicis UF3: Llenguatges SQL / Solucions PL-SQL T3 | Solucions declaració de variables]] | :*[[M2 - Bases de dades / Exercicis UF3: Llenguatges SQL / Solucions PL-SQL T3 | Solucions declaració de variables]] | ||
Revisió del 11:58, 3 abr 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
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
:* Solucions declaració de variables
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
END;
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 NUMEROS PER MITJÀ DE VARIABLES SQL*PLUS. S'HA DE DIVIDIR EL PRIMER NÚMERO PEL SEGON I DESPRÉS AFEGIR EL SEGON 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.
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 PASARAN AL BLOC PL/SQL A TRAVÉS DE VARIABLES DE SUBSTITUCIÓ SQL*PLUS I L'IMPORT DE BONIFICACIÓ HAURÀ DE SER CONVERTIT EN NUMERO ENTER A DECIMAL (per exemple 15 a 0,15). SI EL SALARI ES NUL ASIGNEU-LI ZERO ABANS DE CALCULAR LA COMPENSACIÓ TOTAL. UTILITZEU LA FUNCIÓ NVL PER A GESTIONAR ELS VALORS NULS.
T5- Interacció amb el servidor Oracle
EXERCICI 1
CREAR UN BLOC PLSQL QUE SELECCIONI EL DEPARTAMENT AMB EL NÚMERO MÉS ALT DE LA TAULA DEPT I IMPRIMEIXI ELS RESULTATS PER PANTALLA.
EXERCICI 2
CREAR UN BLOC PLSQL QUE INSERTI UN NOU DEPARTAMENT EN LA TAULA DEPT. COM A NOU NUMERO D E DEPARTAMENT UTILITZA EL NÚMERO DE DEPARTAMENT RECUPERAT EN L'EXERCICI ANTERIOR I AFEIGEIX 10 AL NUMERO COM A NOU NÚMERO DE DEPARTAMENT.
RECULLI EL NOM DEL NOU DEPARTAMENT PER TECLAT. DEIXA LA UBICACIÓ COM A NULA. AL FINAL MOSTRA EL NOU DEPARTAMENT QUE S'HA 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
REGISTRES
TYPE nom_tipus IS RECORD ( campo1 tipo1, campo2 tipo2, campo3 tipo3...)
REGISTRO |camp1|camp2|camp3|
--Ejemplo: definimos un tipo de registro PL/SQL para contener un nombre, un oficio y un salario.
TYPE tipo_registro_emp (ename VARCHAR2(10), job VARCHAR2(9), sal NUMBER(7,2));
TYPE tipo_registro_emp (ename emp.ename%TYPE, job emp.job%TYPE, sal emp.sal%TYPE);
--Declaramos una varible (reg_emp1) de este tipo:
reg_emp1 tipo_registro_emp;
--reg_emp1 |ename|job|sal|
--para hacer referencia o acceder a un campo determinado de un registro se escribe NOMBRE_REGISTRO.NOMBRE_CAMPO
--reg_emp1.ename :='Pedro';
%ROWTYPE
– Podemos declarar un registro basándonos en una colección de columnas de una tabla o vista de datos.
registro_emp emp%ROWTYPE;
– un cop definit podem omplir-lo amb el resultat d'una select
SELECT *
INTO registro_emp
FROM emp
WHERE ename='Scott';
el registre definit anteriorment estarà format pels camps que pertanyen a la taula EMP (ename, job, sal, comm)
És molt útil per a guardar el valor de una fila d'una taula amb la sentència SELECT TAULES PL/SQL
--S'anomenen així als objectes de tipus TABLE
--utilitzen una clau primaria per oferir accés de tipus vector a les files.
--poden agumentar dinàmicament de mida (és a dir créixer) doncs no tenen restriccions
--anem a definir un tipus taula amb una sola columna. Les files s'identifiquen mitjançant enters:
TYPE tipus_taula_noms IS TABLE OF emp.ename%TYPE
INDEX BY BINARI_INTEGER; --aquest tipus de dades accepta també enters negatius així doncs la indexació no té perquè començar amb 1.
taula_noms tipus_taula_noms; --definim una variable d'aquest tipus.
--l'estructura d'aquest tipus de taula seria
|clau principal|columna |
|1 |Jones |
|2 |Smith |
|3 |Pepito |
--per referenciar a un registre de la taula concret es fa:
taula_noms(4):='Anna';
--podem consultar si existeix una fila concreta d'una taula o no, per exemple el registre o fila 6:
taula_noms.EXISTS(6)
--si l'expressio anterior retorna TRUE és que existeix aquesta fila si retorna FALSE és que no existeix. TAULES DE REGISTRES
--es defineixen com una taula on el tipus de variable són registres.
TYPE tipus_taula_dept IS TABLE OF dept%ROWTYPE
INDEX BY BINARI_INTEGER;
taula_dept tipus_taula_dept;
--els seus elements es referencien per la fila.columna
taula_dept(2).dname:='Ventas'; EXERCICI 1
Crea una nova taula per tal d'emmagatzemar empleats i els seus sous..
CREATE TABLE emp_sal (
name varchar2(25),
salary number(11,2)
);
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.
T8- Cursors explícits
Cursores Implícitos
Cuando se ejecuta una sentencia DML (son aquellas utilizadas para insertar, borrar, modificar y consultar los datos en una base de datos) un cursor implícito es lanzado. La principal característica es que no se declara con la palabra reservada CURSOR y que se suele utilizar para trabajar con un registro especifico. Los cursores implicitos se utilizan para realizar consultas SELECT...INTO que devuelven un único registro.
Deben tenerse en cuenta los siguientes puntos cuando se utilizan cursores implicitos:
- Con cada cursor implicito debe existir la palabra clave INTO.
- Las variables que reciben los datos devueltos por el cursor tienen que contener el mismo tipo de dato que las columnas de la tabla.
- Los cursores implicitos solo pueden devolver una única fila. En caso de que se devuelva más de una fila (o ninguna fila) se producirá una excepcion.
Cursors explícits
http://www.devjoker.com/print/Tutorial-PLSQL/39/Tutorial-PLSQL.aspx
S'utilitzen aquests cursors per a processar individualment les files retornades per una sentència SELECT que retorna varies files (joc de resultats).
Amb PL/SQL podem obrir un cursor, processar les files retornades i finalment tancar el cursor.
Control dels cursors explícits.
Cal declarar el cursor amb la paraula CURSOR (posar-li nom i indicar l'estructura de la consulta de la que guardarà informació) OBRIR el cursor amb la sentència OPEN. Recuperem les dades de cada fila del cursor amb FETCH...INTO i les guardem en variables. Cada recuperació o FETCH apunta a una fila diferent retornada per la consulta. Si no troba mes files tanca el cursor. La sentència CLOSE allibera el cursor i podem si volem tornar-lo a obrir.
CURSOR nom_cursor IS SELECT ...;
exemple:
DECLARE
CURSOR c1 IS
SELECT * FROM emp WHERE sal >2000;
BEGIN
OPEN c1; FETCH c1 INTO v_emp; ... CLOSE c1;
END;
EXERCICI 1
Cree un bloque plsql que determine los n empleados con sueldos mas altos (suposem que no hi ha dos empleats o mes 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) comprova varis casos: n=0 i n>que el número màxim d'empleats.
f) buida la taula TOP_DOGS després de cada prova
SET SERVEROUTPUT ON; SET VERIFY OFF; ACCEPT num_emp PROMPT 'Introdueixi el número de empleats amb sou més alt: ' DECLARE
v_numemp number(3) :=&num_emp;
--declarem un cursor que guardarà totes les dades de la taula empleats CURSOR c_emp IS SELECT * FROM emp ORDER BY sal DESC;
--declarem una variable registre capaç de guardar totes les dades de una fila del cursor anterior
v_emp c_emp%ROWTYPE;
BEGIN
OPEN c_emp; --obrim el cursor, aquest es posiciona apuntant el primer registre del cursor FETCH c_emp INTO v_emp; --carreguem el valor de la primera fila en la variablle c_emp, el cursor se situa
--en el seguent registre
FOR i IN 1..v_numemp LOOP --usem un bucle per repetir la càrrega tantes vegades com v_numemp
IF c_emp%NOTFOUND THEN EXIT; --condició de sortida del for, si c_emp està buida, el fetch no
--retorna cap valor
END IF;
INSERT INTO top_dogs VALUES (v_emp.ename,v_emp.sal); --inserim una nova fila amb el valors carregats
FETCH c_emp INTO v_emp; --fem que el cursor carregui de nou i passi al següent registre
END LOOP;
CLOSE c_emp;--tanquem el cursor, alliberem memòria
END;
EXERCICI 2
Considere el caso de que varios empleados tengan el mismo salario. Al igual que el ejercicio anterior cree un bloque plsql que determine los n empleados con sueldos mas altos pero en este caso si se muestra una persona con el mismo sueldo se tendran que mostrar todos los que tengan este sueldo.
a) Si el usuario introduce n=2 deben aparecer tres registros (king, ford i scott, estos últimos tienen el mismo sueldo.
b) Si el usuario introduce n=3 deben aparecer 4 registros (king, ford i scott i jones)
c) vacía todas la filas de la tabla TOP_DOGS antes de probar el ejercicio.
SET SERVEROUTPUT ON;
SET VERIFY OFF;
ACCEPT num_emp PROMPT 'Introdueixi el número de empleats amb sou més alt: '
DECLARE
v_numemp number(3) :=&num_emp; v_sal emp.sal%TYPE :=-1;
CURSOR c_emp IS SELECT * FROM emp ORDER BY NVL(sal,0) DESC;
BEGIN
DELETE FROM top_dogs; --buidem la taula top_dogs
FOR v_emp IN c_emp LOOP
DBMS_OUTPUT.PUT_LINE(v_numemp);
EXIT WHEN (c_emp%ROWCOUNT > v_numemp AND v_sal<>v_emp.sal) OR (v_numemp=0);
INSERT INTO top_dogs VALUES (v_emp.ename,v_emp.sal);
IF (v_sal=v_emp.sal) THEN v_numemp:=v_numemp+1;
ELSE v_sal:=v_emp.sal;
END IF;
END LOOP;
END;
/
--Versión 2 de este ejericio
ACCEPT p_num PROMPT 'introduce el máximo numero de sueldos distintos: '
DECLARE
v_num NUMBER(3):= &p_num;
v_ename emp.ename%TYPE;
v_salari_actual emp.sal%TYPE;
v_ultimo_salario emp.sal%TYPE:= 1;
CURSOR emp_cursor IS SELECT ename, sal FROM emp WHERE sal IS NOT NULL ORDER BY sal DESC;
BEGIN
open emp_cursor;
FETCH emp_cursor INTO v_ename, v_salari_actual;
WHILE (emp_cursor%ROWCOUNT <= v_num OR v_salari_actual=v_ultimo_salario) AND emp_cursor%FOUND LOOP
INSERT INTO top_dogs(name, salary)
VALUES (v_ename,v_salari_actual);
v_ultimo_salario:=v_salari_actual;
FETCH emp_cursor INTO v_ename, v_salari_actual;
END LOOP;
close emp_cursor;
COMMIT;
END;
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.
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%.
