oracle - ¿Cuál es el efecto de colocar el commit después de DML en el procedimiento?

oracle11g

Question

He creado un procedimiento en un paquete que está haciendo inserción / eliminación en la tabla y después de una transacción exitosa, se realiza la confirmación.

Me gusta esto:

create or replace package pac is    
procedure pr_test(emp_id number)
is
begin
-- some code
if something then
  insert
else
  delete

commit;
end pr_test;
end pac ;

¿Debo hacer esta transacción como AUTONOMOUS_TRANSACTION? ¿Cuál es el efecto de colocar el commit como este en el programa?

Answer 1

¿Cuál es el efecto de colocar el commit como este en el programa?

La documentación de Oracle describe COMMIT como:

Propósito

Use la declaración COMMIT para finalizar su transacción actual y hacer permanentes todos los cambios realizados en la transacción. Una transacción es una secuencia de sentencias SQL que Oracle Database trata como una sola unidad. Esta declaración también borra todos los puntos de guardado en la transacción y libera bloqueos de transacción.

Si tiene tres PROCEDURE y cada uno contiene una declaración de COMMIT , entonces no puede ejecutar los tres, entonces, si se produce una excepción en uno de estos últimos, ROLLBACK a todos ya que los cambios de los dos primeros ya estarán COMMIT .

Como regla general, no debe usar COMMIT en un PROCEDURE o FUNCTION sino que debe dejar que la persona que llama COMMIT la transacción para que puedan agrupar varias acciones.

Hay, por supuesto, casos de uso en los que querrá violar esta regla, pero debe considerar cada caso por separado y tomarse el tiempo para comprender completamente su lógica comercial antes de romper esta regla para que sepa lo que se COMMIT en cada caso.

¿Debo hacer esta transacción como AUTONOMOUS_TRANSACTION ?

Un caso de uso es el registro: puede tener un PROCEDURE que llama a otro PROCEDURE para registrar las acciones del usuario y, independientemente de si la acción inicial tiene éxito o no, desea mantener un registro de la acción y asegurarse de que el registro se COMMIT . En este caso, el PROCEDURE registro debe ser una AUTONOMOUS_TRANSACTION y contener una instrucción COMMIT y la instrucción de llamada no debería (probablemente) tener ninguno.

Por lo tanto, si el COMMIT de un PROCEDURE siempre se requiere y es independiente de si la persona que llama COMMIT otros datos, entonces convierte el PROCEDURE una AUTONOMOUS_TRANSACTION . Si los PROCEDURE se pueden agrupar y luego ROLLBACK como grupo, entonces no desea convertirlos en AUTONOMOUS_TRANSACTION .

Answer 2

Depende de lo que quieras hacer. Si desea que el procedimiento sea una transacción independiente, le recomendaría agregar pragma autonomous_transaction .

Ej: primer escenario

1. actualizar la tabla 1
2. pragma autonom_transaction -> actualizar tabla 2 -> commit
3. Retroceder

la tabla 2 se comprometerá y la tabla 1 se revertirá

Ej: segundo escenario

1. Actualizar tabla 1
2. actualizar tabla 2 -> commit
3. Retroceder

ambos cuadros 1 y 2 se comprometerán porque lo trata como una transacción

Answer 3

Te sugiero que consultes este artículo. Aclarará sus consultas.

AUTONOMOUS_TRANSACTION

El pragma AUTONOMOUS_TRANSACTION indica al compilador que trate el bloque pl / sql que sigue al pragma como autónomo (independiente) de la transacción de llamada.

Si lo hace, la transacción autónoma depende únicamente de los criterios de uso. En ambos sentidos, COMMIT tiene que colocarse dentro.

El siguiente ejemplo lo ilustrará claramente.

CREATE OR REPLACE
PROCEDURE testav
AS
  PRAGMA AUTONOMOUS_TRANSACTION; --Declaring is Autonomous Transaction.
BEGIN
  INSERT INTO testa VALUES
    (''1'',''2'',sysdate
    );
    commit;
END;

DECLARE
  lv_num NUMBER;
BEGIN
  testav; -- Calling the Procedure
  lv_num:=to_number(''av''); --This will fail to parse but the procedure call will be successfull even though the calling block has failed.
END;

/ /