DAI-C8-EC: Excepcions. Exercicis proposats

De wikijoan
Salta a la navegació Salta a la cerca

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:

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:
**********************************************************************
Liquidación del empleado:...................(1) 	Dpto:.................(2) 	Oficio:...........(3)

Salario		: ............(4)
Trienios		:.............(5)
Comp. Responsabil	:.............(6)
Comisión		:.............(7)
 			------------
Total		           :.............(8)
**********************************************************************
  • 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. 

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;
/

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 

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;
/

3. 

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. */

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


creat per Joan Quintana Compte, octubre 2009

Obtingut de «http://wiki.joanillo.org/index.php?title=DAI-C8-EC:_Excepcions._Exercicis_proposats&oldid=247969»