oracle - mega - Crear tabla oráculo con confirmación automática en

toad for oracle trial

Question

Aquí está el escenario, estoy escribiendo un procedimiento que realiza operaciones DML (insertar, eliminar) en múltiples tablas. En caso de excepción, necesito deshacer los datos de la mayoría de las tablas, excepto una.

Ejemplo:

Procedimiento{

1 Insert into table1

2 Delete from table2

3 Insert into Table3

4 Insert into table4

 Commit;

Excepción

Rollback;

}

Si hay alguna excepción en 4th Insert, necesito deshacer todas las transacciones anteriores excepto la tercera. Lo sé, commit / rollback no funciona así. Pero tal vez haya alguna forma de crear Table3, puedo establecer algunos parámetros para que cada sentencia INSERT en table3 se confirme automáticamente.

Answer 1

Es posible usar una Transacción Autónoma.

CREATE TABLE t1 (x INTEGER PRIMARY KEY);
CREATE TABLE t2 (x INTEGER);
CREATE TABLE t3 (x INTEGER);
CREATE TABLE t4 (x INTEGER REFERENCES t1(x));

Luego ejecute este PL / SQL anónimo. Mire cuidadosamente, el procedimiento local upd_table_3 no se ejecuta cuando se declara. Se ejecuta cuando se llama dentro del bloque principal BEGIN :

DECLARE 
  PROCEDURE upd_table_3 IS
  PRAGMA AUTONOMOUS_TRANSACTION;
  BEGIN
    INSERT INTO t3 VALUES (3);
    COMMIT;
  END;
BEGIN
  INSERT INTO t1 VALUES (1);
  INSERT INTO t2 VALUES (2);
  upd_table_3;
  INSERT INTO t4 VALUES (4);
  COMMIT;
END;
/

El procedimiento debe fallar intencionalmente en el 4º inserto con ORA-02291 . Lo hice de esta manera. Entonces

ROLLBACK;

Todas las tablas deben retrotraerse excepto t3 , que hicimos en la inserción en una transacción autónoma.

SELECT * FROM t1;

no rows selected

SELECT * FROM t2;

no rows selected

SELECT * FROM t3;

         X
----------
         3

SELECT * FROM t4;

no rows selected

Answer 2

No hay nada que hacer. Toma tu guión:

1 Insert into table1

2 Delete from table2

3 Insert into Table3

4 Insert into table4

Commit;

Supongamos que obtiene una excepción en el enunciado 4, entonces el enunciado 4 en realidad no se ejecuta, por lo tanto, no tiene sentido revertir el enunciado 4. Un COMMIT simple solo comprometerá las declaraciones 1,2,3 en este caso.

Sin embargo, parece diferente cuando obtienes una excepción en el enunciado 3, por ejemplo (mientras que el enunciado 4 funciona bien). ¿Desea retroceder a la declaración 2 en este caso, es decir, deshacer también la declaración 4? Luego debe trabajar con SAVEPOINTS como se menciona en las otras respuestas.

Answer 3

De su explicación de publicación, lo que entendí es que la inserción n. ° 3 no tiene dependencia de otra inserción y de todos modos desea confirmarla. En ese caso, puede tenerlos en diferentes bloques de transacciones como.

begin transaction
1 Insert into table1

2 Delete from table2

4 Insert into table4

Commit;

Exception

Rollback;

Segunda parte

begin transaction
3 Insert into Table3
commit

On Exception 
Rollback;

(O) usando un save point como el que se muestra a continuación, de esa manera el inserto # 3 no se revertirá.

BEGIN
  SAVEPOINT startpoint;
3 Insert into Table3;
  SAVEPOINT startpoint2;
1 Insert into table1;
2 Delete from table2;
4 Insert into table4;

EXCEPTION
  WHEN SOMETHING THEN
    ROLLBACK TO startpoint2;
    RAISE;
END;

PD: la sintaxis puede no ser adecuada; Por lo tanto, consulte la documentación. Idea SAVEPOINT tomada de BEGIN - END transacciones atómicas de bloques en PL / SQL