DAI-C8-EC: Transaccions. Exercicis proposats

Objectius

Unitat Didàctica: UD 4. Transaccons

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

Codi vist a classe:

Control de Transaccions
=======================
Transacció: conjunt d’operacions que es realitzen a la base de dades.
Oracle garanteix la consistencia de les dades en una transacció en termes de TOT VAL o NO VAL RES, és a dir, s’executen totes les operacions que composen una transacció o bé no se n’executa ninguna.

La transacció finalitza amb un COMMIT, un ROLLBACK, una ordre SQL del tipus DDL (definició de dades), o bé quan acaba la sessió.

COMMIT: dóna per acabada la transacció actual i fa definitius els canvis efectuats, alliberant les files bloquejades. Només després del Commit els usuaris tenen accés a les dades modificades.

ROLLBACK: Dóna per conclosa la transacció actual i desfà els canvis que es puguin haver produït en la mateixa, alliberant les files bloquejades. S’utilitza especialment quan no es pot concloure una transacció perque s’ha aixecat una excepció.

ROLLBACK implícit: Quan un subprograma falla i no es controla l’excepció que produeix l’errada, es realitza un Rollback automàtic de tots els canvis que ha prodiït el subprograma, a no ser quehi hagi un commit per confirmar els canvis produïts.

SAVEPOINT: S’utilitza per posar marques o punts de salvaguarda en procesar transaccions. S’utilitza juntament amb el ROLLBACK TO, i serveix per a desfer una part d’una transacció.
Executar el següeNt procediment (crear prèviament la taula temp1):


CREATE TABLE temp1(col1 VARCHAR2(15));

create or replace procedure prova_savepoint(num_files positive)
As
Begin
	Savepoint ninguna;
	Insert into temp1(col1) values ('primera fila');
	Savepoint una;
	Insert into temp1(col1) values ('segona fila');
	Savepoint dos;
	
	If num_files=1 then
		Rollback to una;
	Elsif num_files=2 then
		Rollback to dos;
	Else
		Rollback to ninguna;
	End if;
	Commit;
Exception
	When others then
		Rollback;
End;
/

delete from temp1;
execute prova_savepoint(1)
select * from temp1;

delete from temp1;
execute prova_savepoint(2)
select * from temp1;

delete from temp1;
execute prova_savepoint(3)
select * from temp1;

Recordem que fer un DELETE, INSERT o UPDATE en la consola del sqlplus no és cap garantia de què les noves dades estiguin disponibles. No serà la primera vegada que un alumne fa un INSERT, vol veure la fila amb una SELECT, i es torna mico perquè no la veu (no és habitual, però passa...).

Fés la següent prova:

select count(*) from emple;
delete from emple;
select count(*) from emple;
rollback;
select count(*) from emple;

En aquest cas, en fer el delete from emple has obert una transacció implícita, que no està tancada, i per tant tens temps de fer un ROLLBACK.

solucions

1. Escribir un procedimiento que suba el sueldo de todos los empleados que ganen menos que el salario medio de su oficio. La subida será de el 50% de la diferencia entre el salario del empleado y la media de su oficio. Se deberá asegurar que la transacción no se quede a medias, y se gestionarán los posibles errores.

CREATE OR REPLACE PROCEDURE pujada_50pct
AS
	CURSOR c_ofi_sal IS
		SELECT ofici, AVG(salari) salari FROM emple
	GROUP BY ofici;
CURSOR c_emp_sal IS
		SELECT ofici, salari FROM emple E1
		WHERE salari < 

(SELECT AVG(salari) FROM emple E2
	WHERE E2.ofici = E1.ofici)
	ORDER BY ofici, salari FOR UPDATE OF salari;
	
vr_ofi_sal c_ofi_sal%ROWTYPE;
vr_emp_sal c_emp_sal%ROWTYPE;	
v_increment emple.salari%TYPE;

	
BEGIN
	COMMIT;
 	OPEN c_emp_sal;
	FETCH c_emp_sal INTO vr_emp_sal;
OPEN c_ofi_sal;
	FETCH c_ofi_sal INTO vr_ofi_sal;
	WHILE c_ofi_sal%FOUND AND c_emp_sal%FOUND LOOP	

		/* calcular increment */

v_increment :=
 (vr_ofi_sal.salari - vr_emp_sal.salari) / 2;

		/* actualitzar */		
UPDATE emple SET salari = salari + v_increment
  WHERE CURRENT OF c_emp_sal;		

		/* següent empleat */		
FETCH c_emp_sal INTO vr_emp_sal;


/* comprovar si és un altre ofici */
IF c_ofi_sal%FOUND and 
	  vr_ofi_sal.ofici <> vr_emp_sal.ofici THEN
FETCH c_ofi_sal INTO vr_ofi_sal;
		END IF;
	END LOOP; 
	CLOSE c_emp_sal;
	CLOSE c_ofi_sal;

	COMMIT;
EXCEPTION
	WHEN OTHERS THEN
	  	ROLLBACK WORK;
		RAISE;
END pujada_50pct;
/

2. Crear la tabla T_liquidacio con las columnas apellido, departamento, oficio, salario, trienios, comp_responsabilidad, comisión y total; y modificar la aplicación anterior para que en lugar de realizar el listado directamente en pantalla, guarde los datos en la tabla. Se controlarán todas las posibles incidencias que puedan ocurrir durante el proceso.

CREATE TABLE t_liquidacio (
 COGNOM  		VARCHAR2(10),
 DEPARTAMENT		NUMBER(2),
 OFICI    		VARCHAR2(10),
 SALARI   		NUMBER(10),
 TRIENIS		NUMBER(10),
 COMP_RESPONSABILITAT	NUMBER(10),
 COMISSIO  		NUMBER(10),

 TOTAL 			NUMBER(10)
);

CREATE OR REPLACE FUNCTION anys_dif (
data1 DATE,
	data2 DATE)
RETURN NUMBER
AS
	v_anys_dif NUMBER(6);
BEGIN
	v_anys_dif := ABS(TRUNC(MONTHS_BETWEEN(data2,data1)
 / 12));

	RETURN v_anys_dif;
END anys_dif;
/

CREATE OR REPLACE FUNCTION trienis (
data1 DATE, data2 DATE)
RETURN NUMBER
AS
	v_trienis NUMBER(6);
BEGIN
	v_trienis := TRUNC(anys_dif(data1,data2) / 3);

 	RETURN v_trienis;
END;
/

CREATE OR REPLACE PROCEDURE liquidar2
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
	COMMIT WORK;
	FOR vr_emp in c_emp LOOP

		/* Calcular trienis. Crida a la funció trienis creada en un exercici anterior */ 

	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;


		/* Insertar les dades en la tabla T_liquidacio */
		INSERT INTO t_liquidacio (COGNOM,OFICI, SALARI, TRIENIS,  COMP_RESPONSABILITAT, COMISSIO, TOTAL)   	
		  VALUES (vr_emp.cognom, vr_emp.ofici, vr_emp.salari, v_trien, v_comp_r, vr_emp.comissio, v_total);

	END LOOP;
EXCEPTION
	WHEN OTHERS THEN
	  ROLLBACK WORK;
END liquidar2;
/

Entrega

• entregar els fitxers DAI2AXX_script_NA4.sql i DAI2AXX_NA4.log. Si has treballat per parelles: DAI2AXXXX_script_NA4.sql i DAI2AXXXX_NA4.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