Joan: /* Desenvolupament */

2011-11-22T18:32:03Z

Desenvolupament

Pàgina nova

==Objectius==
Unitat Didàctica: UD 3. Excepcions

L'alumne ja haurà instal.lat l'Oracle (client o servidor) en el seu ordinador, i podrà treballar en local o en el servidor 192.168.0.10. Així mateix, s'espera que ja estigui en condicions per poder treballar a casa.

==Desenvolupament==
Altres exemples senzills explicats a classe:
*http://www.devjoker.com/contenidos/Tutorial-PLSQL/48/Excepciones-en-PLSQL.aspx

Exercicis a realitzar:

1. Desarrollar un procedimiento que permita insertar nuevos departamentos según las siguientes especificaciones:
*Se pasará al procedimiento el nombre del departamento y la localidad.
*El procedimiento insertará la fila nueva asignando como número de departamento la decena siguiente al número mayor de la tabla.
*Se incluirá gestión de posibles errores.

2. Codificar un procedimiento reciba como parámetros un numero de departamento, un importe y un porcentaje; y suba el salario a todos los empleados del departamento indicado en la llamada. La subida será el porcentaje o el importe indicado en la llamada (el que sea más beneficioso para el empleado en cada caso empleado).

3. Diseñar una aplicación que simule un listado de liquidación de los empleados según las siguientes especificaciones:

*El listado tendrá el siguiente formato para cada empleado:
<pre>
**********************************************************************
Liquidación del empleado:...................(1) Dpto:.................(2) Oficio:...........(3)

Salario : ............(4)
Trienios :.............(5)
Comp. Responsabil :.............(6)
Comisión :.............(7)
------------
Total :.............(8)
**********************************************************************
</pre>
*Donde:
**1 ,2, 3 y 4 Corresponden al apellido, departamento, oficio y salario del empleado.
**5 Es el importe en concepto de trienios. Cada trienio son tres años completos desde la fecha de alta hasta la de emisión y supone 5000 Ptas.
**6 Es el complemento por responsabilidad. Será de 10000Ptas por cada empleado que se encuentre directamente a cargo del empleado en cuestión.
**7 Es la comisión. Los valores nulos serán sustituidos por ceros.
**8 Suma de todos los conceptos anteriores.
*El listado irá ordenado por Apellido.

===Solucions===
1.
<pre>
CREATE OR REPLACE PROCEDURE insertar_depart(
nom_dep VARCHAR2,
loc VARCHAR2)
AS
CURSOR c_dep IS SELECT dnom FROM depart WHERE dnom = nom_dep;
v_dummy DEPART.DNOM%TYPE DEFAULT NULL;
v_ulti_num DEPART.DEPT_NO%TYPE;
nom_duplicat EXCEPTION;
BEGIN
/* Comprovació de que el departament no està duplicat */
OPEN c_dep;
FETCH c_dep INTO v_dummy;
CLOSE c_dep;
IF v_dummy IS NOT NULL THEN
RAISE nom_duplicat;
END IF;

/* Captura de l'últim número i càlcul del següent */
SELECT MAX(dept_no) INTO v_ulti_num FROM depart;

/* Inserció de la nova fila */
INSERT INTO depart VALUES (v_ulti_num + 10, nom_dep, loc);
EXCEPTION
WHEN nom_duplicat THEN
DBMS_OUTPUT.PUT_LINE('Err. departament duplicat');
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20005,'Err. Operació cancelada');
END insertar_depart;
/
</pre>
2. (falta millorar el control d'excepcions: mirar que existeixi el departament, import i percentatge positius)
També es pot fer amb un CURSOR FOR UPDATE
<pre>
CREATE OR REPLACE PROCEDURE pujada_sal1(
num_dep emple.dept_no%TYPE,
import NUMBER,
percentatge NUMBER)
AS
CURSOR c_sal IS SELECT salari,ROWID FROM emple WHERE dept_no = num_dep;
vr_sal c_sal%ROWTYPE;
v_imp_pct NUMBER(10);
BEGIN
OPEN c_sal;
FETCH c_sal INTO vr_sal;
WHILE c_sal%FOUND LOOP

/* Guardar en v_imp_pct l'import més gran */
v_imp_pct := GREATEST((vr_sal.salari/100)*percentatge, import);

/* Actualitzar */
UPDATE EMPLE SET SALARI = SALARI + v_imp_pct WHERE ROWID = vr_sal.rowid;

FETCH c_sal INTO vr_sal;
END LOOP;
CLOSE c_sal;

COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Err. cap fila actualitzada');
END pujada_sal1;
/
</pre>
3.
<pre>
CREATE OR REPLACE PROCEDURE liquidar
AS
CURSOR c_emp IS
SELECT cognom, emp_no, ofici, salari, NVL(comissio,0) comissio, dept_no, data_alt
FROM emple ORDER BY cognom;
vr_emp c_emp%ROWTYPE;
v_trien NUMBER(9) DEFAULT 0;
v_comp_r NUMBER(9);
v_total NUMBER(10);
BEGIN
FOR vr_emp in c_emp LOOP

/* Calcular trienis. Crida a la funció trienis creada un exercici del capítol 'Fonaments' */
v_trien := trienis(vr_emp.data_alt,SYSDATE)*5000;

/* Calcular complement de responsabilitat. Es tanca en un bloc doncs aixecarà NO_DATA_FOUND*/
BEGIN
SELECT COUNT(*) INTO v_comp_r FROM EMPLE WHERE DIR = vr_emp.emp_no;
v_comp_r := v_comp_r *10000;
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_comp_r:=0;
END;

/* Calcular el total de l'empleat */
v_total := vr_emp.salari + vr_emp.comissio + v_trien + v_comp_r;

/* Visualitzar dades de l'empleat */
DBMS_OUTPUT.PUT_LINE('*************************************');
DBMS_OUTPUT.PUT_LINE(' Liquidació de : '|| vr_emp.cognom ||' Dept: ' || vr_emp.dept_no || ' Ofici: ' || vr_emp.ofici);
DBMS_OUTPUT.PUT_LINE(RPAD('Salari:',16) ||LPAD(TO_CHAR(vr_emp.salari,'9,999,999'),12));
DBMS_OUTPUT.PUT_LINE(RPAD('Trienis: ',16) || LPAD(TO_CHAR(v_trien,'9,999,999'),12));
DBMS_OUTPUT.PUT_LINE('Comp. Respons: ' ||LPAD(TO_CHAR(v_comp_r,'9,999,999'),12));
DBMS_OUTPUT.PUT_LINE(RPAD('Comision: ' ,16) ||LPAD(TO_CHAR(vr_emp.comissio,'9,999,999'),12));
DBMS_OUTPUT.PUT_LINE('------------------');
DBMS_OUTPUT.PUT_LINE(RPAD(' Total : ',16)
||LPAD(TO_CHAR(v_total,'9,999,999') ,12));
DBMS_OUTPUT.PUT_LINE('**************************************');
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No sha trobat cap fila');
END liquidar;
/

/* Nota: También se puede utilizar una cláusula SELECT más compleja:
CURSOR c_emp IS
SELECT COGNOM, EMP_NO, OFICI,(EMP_CARREC * 10000) COM_RESPONSABILIDAD, SALARI, NVL(COMISSIO, 0) COMISION, DEPT_NO, TRIENIS(DATA_ALT, SYSDATE) * 5000 TOT_TRIENIS
FROM EMPLE,(SELECT DIR,COUNT(*) EMP_CARREC FROM EMPLE
GROUP BY DIR) DIREC
WHERE EMPLE.EMP_NO = DIREC.DIR(+)
ORDER BY COGNOM;
de esta forma se simplifica el programa y se evita la utilización de variables de trabajo. */

</pre>

==Entrega==
* entregar els fitxers DAI2AXX_script_NA5.sql i DAI2AXX_NA3.log. Si has treballat per parelles: DAI2AXXXX_script_NA5.sql i DAI2AXXXX_NA5.log, indicant a dins clarament el nom i el número de classe (comentaris amb REM). Exercici individual o per parelles.
* entregar al Moodle: http://192.168.0.15/moodle

==Recursos==
En el Moodle també pots descarregar el codi per crear les taules, i el codi que s'ha vist a classe.

==Durarda==
1,5 hores

{{Autor}}, octubre 2009
[[Categoria: IES Jaume Balmes]]
[[Categoria: Pràctiques DAI-C8-EC]]

DAI-C8-EC: Excepcions. Exercicis proposats - Historial de revisió

Joan: /* Desenvolupament */