Procedimientos almacenados (III)

• INSERT INTO .... SELECT.

Hay un tipo de consulta, llamada de adición de datos, que permite rellenar datos de una tabla copiando el resultado de una consulta. Se hace mediante la instrucción INSERT y, en definitiva, permite copiar datos de una consulta a otra.
Ese relleno se basa en una consulta SELECT que poseerá los datos a añadir. Lógicamente el orden de esos campos debe de coincidir con la lista de campos indicada en la instrucción INSERT. Sintaxis:

INSERT INTO tabla (campo1, campo2,...)
SELECT campoCompatibleCampo1, campoCompatibleCampo2,...
FROM lista DeTablas
[...otras cláusulas del SELECT...]
Ejemplo:

Lógicamente las columnas del SELECT se tienen que corresponder con las columnas a rellenar mediante INSERT (observar las flechas).

• EXCEPCIONES

Se llama excepción a todo hecho que le sucede a un programa que causa que la ejecución del mismo finalice. Lógicamente eso causa que el programa termine de forma anormal.

Las excepciones se debe a:

♦ Que ocurra un error detectado por Oracle (por ejemplo si un SELECT no devuelve datos ocurre el error ORA-01403 llamado NO_DATA_FOUND).
♦ Que el propio programador las lance (comando RAISE).
Las excepciones se pueden capturar a fin de que el programa controle mejor la existencia de las mismas.

 

captura de excepciones
La captura se realiza utilizando el bloque EXCEPTION que es el bloque que está justo antes del END del bloque. Cuando una excepción ocurre, se comprueba el bloque EXCEPTION para ver si ha sido capturada, si no se captura, el error se propaga a Oracle que se encargará de indicar el error existente.
Las excepciones pueden ser de estos tipos:
♦ Excepciones predefinidas de Oracle. Que tienen ya asignado un nombre de excepción.
♦ Excepciones de Oracle sin definir. No tienen nombre asignado pero se les puede asignar.
♦ Definidas por el usuario. Las lanza el programador.
La captura de excepciones se realiza con esta sintaxis:
DECLARE
sección de declaraciones
BEGIN
instrucciones
EXCEPTION
WHEN excepción1 [OR excepción2 ...] THEN
instrucciones que se ejcutan si suceden esas excepciones
[WHEN excepción3 [OR...] THEN
instrucciones que se ejcutan si suceden esas excepciones]

[WHEN OTHERS THEN
instrucciones que se ejecutan si suceden otras
excepciones]
END;

Cuando ocurre una determinada excepción, se comprueba el primer WHEN para comprobar si el nombre de la excepción ocurrida coincide con el que dicho WHEN captura; si es así se ejecutan las instrucciones, si no es así se comprueba el siguiente WHEN y así sucesivamente.
Si existen cláusula WHEN OTHERS, entonces las excepciones que no estaban reflejadas en los demás apartados WHEN ejecutan las instrucciones del WHEN OTHERS. Ésta cláusula debe ser la última.

Ejemplo:

DECLARE
x NUMBER :=0;
y NUMBER := 3;
res NUMBER;
BEGIN
res:=y/x;
DBMS_OUTPUT.PUT_LINE(res);
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE('No se puede dividir por cero') ;
WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('Error inesperado') ;
END;

• TRANSACCIONES

Una transacción está formada por una serie de instrucciones DML. Una transacción comienza con la primera instrucción DML que se ejecute y finaliza con alguna de estas circunstancias:

♦ Una operación COMMIT o ROLLBACK
♦ Una instrucción DDL (como ALTER TABLE por ejemplo)
♦ Una instrucción DCL (como GRANT)
♦ El usuario abandona la sesión
♦ Caída del sistema
Hay que tener en cuenta que cualquier instrucción DDL o DCL da lugar a un COMMIT implícito, es decir todas las instrucciones DML ejecutadas hasta ese instante pasan a ser definitivas.

COMMIT

La instrucción COMMIT hace que los cambios realizados por la transacción sean definitivos, irrevocables. Sólo se debe utilizar si estamos de acuerdo con los cambios, conviene asegurarse mucho antes de realizar el COMMIT ya que las instrucciones ejecutadas pueden afectar a miles de registros.
Además el cierre correcto de la sesión da lugar a un COMMIT, aunque siempre conviene ejecutar explícitamente esta instrucción a fin de asegurarnos de lo que hacemos.

ROLLBACK

Esta instrucción regresa a la instrucción anterior al inicio de la transacción, normalmente el último COMMIT, la última instrucción DDL o DCL o al inicio de sesión. Anula definitivamente los cambios, por lo que conviene también asegurarse de esta operación.
Un abandono de sesión incorrecto o un problema de comunicación o de caída del sistema dan lugar a un ROLLBACK implícito.

estado de los datos durante la transacción

Si se inicia una transacción usando comandos DML hay que tener en cuenta que:
♦ Se puede volver a la instrucción anterior a la transacción cuando se desee
♦ Las instrucciones de consulta SELECT realizadas por el usuario que inició la transacción muestran los datos ya modificados por las instrucciones DML
♦ El resto de usuarios ven los datos tal cual estaban antes de la transacción, de hecho los registros afectados por la transacción aparecen bloqueados hasta que la transacción finalice. Esos usuarios no podrán modificar los valores de dichos registros.
♦ Tras la transacción todos los usuarios ven los datos tal cual quedan tras el fin de transacción. Los bloqueos son liberados y los puntos de ruptura borrados.

El siguiente ejemplo muestra una supuesta transacción bancaria.

DECLARE
  importe NUMBER;
   ctaOrigen VARCHAR2(23);
   ctaDestino VARCHAR2(23);
BEGIN
     importe := 100;
     ctaOrigen  := '2530 10 2000 1234567890';
     ctaDestino := '2532 10 2010 0987654321';
     UPDATE CUENTAS SET SALDO = SALDO - importe
     WHERE CUENTA = ctaOrigen;
     UPDATE CUENTAS SET SALDO = SALDO + importe
     WHERE CUENTA = ctaDestino;
     INSERT INTO MOVIMIENTOS
     (CUENTA_ORIGEN, CUENTA_DESTINO,IMPORTE, FECHA_MOVIMIENTO)
     VALUES
     (ctaOrigen, ctaDestino, importe*(-1), SYSDATE);
     INSERT INTO MOVIMIENTOS
     (CUENTA_ORIGEN, CUENTA_DESTINO,IMPORTE, FECHA_MOVIMIENTO)
     VALUES
     (ctaDestino,ctaOrigen, importe, SYSDATE);
     COMMIT;
EXCEPTION
WHEN OTHERS THEN
     dbms_output.put_line('Error en la transaccion:'||SQLERRM);
     dbms_output.put_line('Se deshacen las modificaciones);
     ROLLBACK;
END;

• Utilidad de los procedimientos almacenados.

Los usos 'típicos' de los procedimientos almacenados se aplican en la validación de datos, integrados dentro de la estructura del banco de datos. Los procedimientos almacenados usados con tal propósito se llaman comúnmentedisparadores, o triggers. Otro uso común es la 'encapsulación' de un API para un proceso complejo o grande que podría requerir la 'ejecución' de varias consultas SQL, tales como la manipulación de un 'dataset' enorme para producir un resultado resumido.

También pueden ser usados para el control de gestión de operaciones, y ejecutar procedimientos almacenados dentro de una transacción de tal manera que las transacciones sean efectivamente transparentes para ellos.