Joan: /* Desenvolupament */

2011-11-16T15:47:32Z

Desenvolupament

Pàgina nova

==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:
<pre>
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;
</pre>
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:
<pre>
select count(*) from emple;
delete from emple;
select count(*) from emple;
rollback;
select count(*) from emple;
</pre>
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.
<pre>
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;
/
</pre>
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.
<pre>
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)
);
</pre>
<pre>
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;
/
</pre>
<pre>
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;
/
</pre>
<pre>
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;
/
</pre>

==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

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

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

Joan: /* Desenvolupament */