MERGE

Sentencias MERGE 

Oracle Server soporta la sentencia MERGE para operaciones INSERT, UPDATE y DELETE. Mediante esta sentencia, puede actualizar, insertar o suprimir una fila condicionalmente en una tabla, con lo que se evitan varias sentencias DML. La decisión de actualizar, insertar o suprimir en la tabla destino se basa en una condición de la cláusula ON. 

Hay que tener privilegios de objeto INSERT y UPDATE en la tabla destino y el privilegio de objeto SELECT en la tabla origen. Para especificar la cláusula DELETE de la cláusula merge_update_clause, también debe tener el privilegio de objeto DELETE en la tabla destino. 

La sentencia MERGE es determinista. No se puede actualizar varias veces la misma fila de la tabla destino en la misma sentencia MERGE. 

Un enfoque alternativo es utilizar bucles PL/SQL y varias sentencias DML. La sentencia MERGE, sin embargo, es fácil de utilizar y se expresa de forma más sencilla como una única sentencia SQL. 

La sentencia MERGE es adecuada en diferentes aplicaciones de almacén de datos. Por ejemplo, en una aplicación de almacén de datos, es posible que necesite trabajar con datos procedentes de varios orígenes, algunos de los cuales pueden estar duplicados. Con la sentencia MERGE, puede agregar o modificar filas condicionalmente. 

• Permite actualizar o insertar datos condicionalmente en una tabla de base de datos 
• Realiza una actualización (UPDATE) si existe la fila y una inserción (INSERT) si es una fila nueva: 
• Evita actualizaciones separadas 
• Aumenta el rendimiento y la facilidad de uso 
• Es útil en aplicaciones de almacenes de datos 

El ejemplo de la diapositiva hace corresponder EMPLOYEE_ID de la tabla EMPL3 con EMPLOYEE_ID de la tabla EMPLOYEES. Si encuentra una correspondencia, la fila de la tabla EMPL3 se actualiza para que se corresponda con la de la tabla EMPLOYEES. Si no encuentra la fila, la inserta en la tabla EMPL3. 

Se evalúa la condición c.employee_id = e.employee_id. Como la tabla EMPL3 está vacía, la condición devuelve false (no hay correspondencias). La lógica recae en la cláusula WHEN NOT MATCHED y el comando MERGE inserta las filas de la tabla EMPLOYEES en la tabla EMPL3.  

Si existieran las filas en la tabla EMPL3 y los identificadores de empleado se correspondieran en las dos tablas (en las tablas EMPL3 y EMPLOYEES), las filas existentes en la tabla EMPL3 se actualizarían para corresponderse con la tabla EMPLOYEES. 

Read More

INSERT DE PIVOTING

El pivoting es una operación en la que se debe crear una transformación tal que cada registro de cualquier flujo de entrada como, por ejemplo, una tabla de base de datos no relacional, se debe convertir en varios registros para un entorno de tablas de base de datos más relacional. 

Para solucionar el problema que se menciona en la diapositiva, debe crear una transformación tal que cada registro de la tabla de base de datos no relacional original, SALES_SOURCE_ DATA, se convierta en cinco registros para la tabla SALES_INFO del almacén de datos. Esta operación se suele conocer como pivoting. 

Suponga que recibe un juego de registros de ventas de una tabla de base de datos no relacional, SALES_ SOURCE_DATA, con el siguiente formato:

EMPLOYEE_ID, WEEK_ID, SALES_MON, SALES_TUE, SALES_WED, SALES_THUR, SALES_FRI
Desea almacenar estos registros en la tabla SALES_ INFO con un formato relacional más normal:
EMPLOYEE_ID, WEEK, SALES
Mediante una sentencia INSERT de pivoting, convierta el juego de registros de ventas de la tabla de la base de datos no relacional al formato relacional. 

En la imagen se especifica la sentencia con un problema para una sentencia INSERT de pivoting. La solución a este problema se muestra en la página siguiente. 

En el ejemplo de la diapositiva, los datos de ventas se reciben de la tabla de base de datos no relacional SALES_SOURCE_DATA, que contiene los detalles de las ventas realizadas por el representante de ventas cada día de la semana, durante una semana con un identificador de semana en particular. 

    DESC SALES_SOURCE_DATA 

Read More

INSERT FIRST

INSERT Condicional: conditional_insert_clause 
Especifique la cláusula conditional_insert_clause para realizar una inserción (INSERT) condicional de varias tablas. Oracle Server filtra cada cláusula insert_into_ clause a través de la condición WHEN correspondiente, lo que determina si se ejecutará insert_into_clause. Una única sentencia INSERT de varias tablas puede contener hasta 127 cláusulas WHEN.

INSERT Condicional: FIRST 
Si especifica FIRST, Oracle Server evalúa cada cláusula WHEN en el orden en que aparece en la sentencia. Si la primera cláusula WHEN se evalúa como verdadera, Oracle Server ejecuta la cláusula INTO correspondiente y salta las cláusulas WHEN siguientes para la fila especificada.

INSERT Condicional: Cláusula ELSE 
Para una fila especificada, si no se evalúa ninguna cláusula WHEN como verdadera:

• Si ha especificado una cláusula ELSE, Oracle Server ejecuta la lista de cláusulas INTO asociadas a la cláusula ELSE. 
• Si no ha especificado una cláusula ELSE, Oracle Server no realiza ninguna acción para esa fila. 

Restricciones en Sentencias INSERT de Varias Tablas 

• Se pueden realizar sentencias INSERT de varias tablas sólo en tablas, no en vistas ni en vistas materializadas. 
• No se puede realizar una inserción (INSERT) de varias tablas en una tabla remota. 
• No se puede especificar una expresión de recopilación de tablas al realizar una inserción (INSERT) de varias tablas. 
• En una inserción (INSERT) de varias tablas, no se pueden combinar todas las cláusulas insert_into_clauses para especificar más de 999 columnas de destino. 

El ejemplo inserta filas en más de una tabla mediante una única sentencia INSERT. La sentencia SELECT recupera los detalles de identificador de departamento, salario total y fecha de contratación máxima de cada departamento de la tabla EMPLOYEES. 

Esta sentencia INSERT se conoce como FIRST INSERT condicional, ya que se realiza una excepción para los departamentos cuyo salario total sea mayor que 25.000 dólares. La condición WHEN ALL > 25000 se evalúa en primer lugar. Si el salario total de un departamento es mayor que 25.000 dólares, el registro se inserta en la tabla SPECIAL_SAL, independientemente de la fecha de contratación. Si esta primera cláusula WHEN se evalúa como verdadera, Oracle Server ejecuta la cláusula INTO correspondiente y salta las cláusulas WHEN siguientes para esta fila. 

Para las filas que no satisfacen la primera condición WHEN (WHEN SAL > 25000), el resto de las condiciones se evalúa igual que una sentencia INSERT condicional y los registros recuperados mediante la sentencia SELECT se insertan en las tablas HIREDATE_HISTORY_ 00 o HIREDATE_HISTORY_99 o HIREDATE_HISTORY, basándose en el valor de la columna HIREDATE. 

Se puede interpretar que el feedback 8 rows created significa que se realizó un total de ocho sentencias INSERT en las tablas base, SPECIAL_SAL, HIREDATE_HISTORY_00, HIREDATE_HISTORY_99 y HIREDATE_HISTORY. 

Read More

INSERT ALL CONDITIONAL

El ejemplo de la diapositiva es parecido al de la diapositiva anterior, ya que inserta filas en las tablas SAL_HISTORY y MGR_HISTORY. La sentencia SELECT recupera los detalles de identificador de empleado, fecha de contratación, salario e identificador de supervisor de los empleados cuyo identificador de empleado es mayor que 200 en la tabla EMPLOYEES. Los detalles de identificador de empleado, fecha de contratación y salario se insertan en la tabla SAL_HISTORY. Los detalles de identificador de empleado, identificador de supervisor y salario se insertan en la tabla MGR_HISTORY. 

La sentencia INSERT se conoce como ALL INSERT condicional, ya que se aplica una restricción más a las filas que se recuperan mediante la sentencia SELECT. De las filas recuperadas mediante la sentencia SELECT, sólo aquéllas en las que el valor de la columna SAL sea mayor que 10000 se insertarán en la tabla SAL_HISTORY y, de forma parecida, sólo las filas en las que el valor de la columna MGR sean mayor que 200 se insertarán en la tabla MGR_HISTORY. 

Observe que, a diferencia del ejemplo anterior, en el que se insertaron ocho filas en las tablas, en este ejemplo sólo se insertan cuatro filas. 

Se puede interpretar que el feedback 4 rows created significa que se realizó un total de cuatro inserciones en las tablas base, SAL_HISTORY y MGR_HISTORY. 

Read More

INSERT ALL INCONDITIONAL

Visión General de Sentencias INSERT de Varias Tablas 

En una sentencia INSERT de varias tablas, se insertan filas calculadas derivadas de las filas devueltas de la evaluación de una subconsulta en una o más tablas. 

Las sentencias INSERT de varias tablas pueden desempeñar un papel muy útil en el supuesto de un almacén de datos. Debe cargar el almacén de datos con regularidad para que pueda cumplir su propósito de facilitar el análisis de negocio. Para ello, se deben extraer y copiar datos de uno o más sistemas operativos al almacén de datos. El proceso de extracción de datos del sistema de origen y su transferencia al almacén de datos se suele denominar ETL (siglas de extraction, transformation, and loading, o extracción, transformación y carga). 

Durante la extracción, se deben identificar y extraer los datos deseados de diferentes orígenes como, por ejemplo, aplicaciones y sistemas de bases de datos. Después de la extracción, los datos se deben transportar físicamente al sistema de destino o a un sistema intermedio para continuar su procesamiento. Dependiendo del medio de transporte seleccionado, algunas transformaciones se pueden realizar durante este proceso. Por ejemplo, una sentencia SQL que acceda directamente a un destino remoto a través de un gateway puede concatenar dos columnas como parte de la sentencia SELECT. 

Una vez cargados los datos en la base de datos Oracle, las transformaciones de datos se pueden ejecutar mediante operaciones SQL. Una sentencia INSERT de varias tablas es una de las técnicas para implementar transformaciones de datos SQL. 

Las sentencias INSERT de varias tablas ofrecen las ventajas de la sentencia INSERT ... SELECT cuando hay varias tablas implicadas como destinos. Con la funcionalidad anterior a la base de datos Oracle9i, era necesario tratar con n sentencias INSERT ... SELECT independientes, procesando los mismos datos de origen n veces y aumentando la carga de trabajo de transformación n veces. 

Como sucede con la sentencia INSERT ... SELECT existente, la nueva sentencia se puede paralelizar y utilizar con el mecanismo de carga directa para obtener un rendimiento más rápido. 

Cada registro de cualquier flujo de entrada como, por ejemplo, una tabla de base de datos no relacional, se puede convertir ahora en varios registros para un entorno de tabla de base de datos más relacional. Para implementar esta funcionalidad de forma alternativa, había que escribir varias sentencias INSERT. 

• La sentencia INSERT…SELECT se puede utilizar para insertar filas en varias tablas como parte de una única sentencia DML. 
• Las sentencias INSERT de varias tablas se pueden utilizar en sistemas de almacenes de datos para transferir datos de uno o más orígenes operativos a un juego de tablas destino. 
• Proporcionan una mejora significativa del rendimiento en: 
• DML único frente a varias sentencias INSERT…SELECT 
• DML único frente a un procedimiento para realizar varias inserciones mediante la sintaxis IF...THEN 

INSERT ALL Incondicional 

El ejemplo de la diapositiva inserta filas en las tablas SAL_HISTORY y MGR_HISTORY. 

La sentencia SELECT recupera los detalles de identificador de empleado, fecha de contratación, salario e identificador de supervisor de los empleados cuyo identificador de empleado es mayor que 200 en la tabla EMPLOYEES. Los detalles de identificador de empleado, fecha de contratación y salario se insertan en la tabla SAL_HISTORY. Los detalles de identificador de empleado, identificador de supervisor y salario se insertan en la tabla MGR_HISTORY. 

La sentencia INSERT se conoce como INSERT incondicional, ya que no se aplican más restricciones a las filas que se recuperan mediante la sentencia SELECT. Todas las filas recuperadas mediante la sentencia SELECT se insertan en las dos tablas, SAL_HISTORY y MGR_HISTORY. La cláusula VALUES de las sentencias INSERT especifica las columnas de la sentencia SELECT que se deben insertar en cada una de las tablas. Cada fila devuelta mediante la sentencia SELECT da como resultado dos inserciones, una para la tabla SAL_HISTORY y una para la tabla MGR_HISTORY. 

Se puede interpretar que el feedback 8 rows created significa que se realizó un total de ocho inserciones en las tablas base, SAL_HISTORY y MGR_HISTORY. 

• Seleccione los valores EMPLOYEE_ID, HIRE_DATE, SALARY y MANAGER_ID de la tabla EMPLOYEES de los empleados cuyo EMPLOYEE_ID sea mayor que 200. 
• Inserte estos valores en las tablas SAL_HISTORY y MGR_HISTORY mediante una sentencia INSERT de varias tablas.
• Seleccione los valores EMPLOYEE_ID, HIRE_DATE, SALARY y MANAGER_ID de la tabla EMPLOYEES de los empleados cuyo EMPLOYEE_ID sea mayor que 200. 
• Si SALARY es mayor que 10.000 dólares, inserte estos valores en la tabla SAL_HISTORY mediante una sentencia INSERT condicional de varias tablas. 
• Si MANAGER_ID es mayor que 200, inserte estos valores en la tabla MGR_HISTORY mediante una sentencia INSERT condicional de varias tablas. 

Read More

FLASHBACK TABLE

Herramienta de reparación para modificaciones accidentales de tabla 

• Restaura una tabla a un punto anterior en el tiempo 
• Ventajas: Facilidad de uso, disponibilidad y ejecución rápida 

Utilidad de Reparación de Autoservicio 

La base de datos Oracle 10g proporciona un nuevo comando DDL de SQL, FLASHBACK TABLE, para restaurar el estado de una tabla a un punto anterior en el tiempo en el caso de que la haya suprimido o modificado de forma accidental. El comando FLASHBACK TABLE es una herramienta de reparación de autoservicio para restaurar datos de una tabla junto con los atributos asociados como, por ejemplo, índices o vistas. Esto se consigue cuando la base de datos está online haciendo rollback sólo de los cambios posteriores en la tabla en cuestión. Si se compara con mecanismos de recuperación tradicionales, esta función ofrece ventajas significativas, como la facilidad de uso, la disponibilidad y una recuperación más rápida. También libera al DBA del trabajo de encontrar y restaurar propiedades específicas de aplicación. La función de FLASHBACK en tabla no se ocupa de la corrupción física provocada por un disco en mal estado. 

Sintaxis 

Puede llamar a una operación de FLASHBACK en tabla en una o más tablas, incluso en tablas de diferentes esquemas. Para especificar el punto en el tiempo al que desea revertir, proporcione un registro de hora válido. Por defecto, los disparadores de base de datos están desactivados para todas las tablas implicadas. Para sustituir este comportamiento por defecto, especifique la cláusula ENABLE TRIGGERS. 

Nota: Para obtener más información sobre la semántica de FLASHBACK y de papelera de reciclaje, consulte Oracle Database Administrator’s Reference 10g Release 1 (10.1).

El ejemplo restaura la tabla EMP2 a un estado anterior a una sentencia DROP. 

La papelera de reciclaje es en realidad una tabla de diccionario de datos que contiene información sobre objetos borrados. Las tablas borradas y los objetos asociados, como índices, restricciones, tablas anidadas, etc., no se eliminan y siguen ocupando espacio. Siguen ocupando las cuotas de espacio de usuario, hasta que se purgan específicamente de la papelera de reciclaje o hasta que se produce una situación poco probable en la que las deba purgar la base de datos debido a restricciones de espacio de tablespace. 

Se puede considerar a cada usuario propietario de una papelera de reciclaje, ya que, a menos que un usuario tenga el privilegio SYSDBA, los únicos objetos a los que puede acceder en la papelera de reciclaje son los de su propiedad. Un usuario puede ver sus objetos en la papelera de reciclaje mediante la siguiente sentencia: 

SELECT * 

   FROM RECYCLEBIN; 

Al borrar un usuario, los objetos que pertenecen a ese usuario no se colocarán en la papelera de reciclaje y se purgarán todos los objetos de la papelera de reciclaje. 

Puede purgar la papelera de reciclaje con la siguiente sentencia: 

PURGE RECYCLEBIN;

Read More

PURGE

DROP TABLE …PURGE 

DROP TABLE EMP PURGE;

La base de datos Oracle 10g presenta una nueva función para borrar tablas. Al borrar una tabla, la base de datos no libera inmediatamente el espacio asociado a la tabla. En su lugar, la base de datos cambia el nombre de la tabla y la coloca en una papelera de reciclaje, de donde se podrá recuperar después con la sentencia FLASHBACK TABLE si se da cuenta de que borró la tabla por error. Si desea liberar de forma inmediata el espacio asociado a la tabla en el momento de emitir la sentencia DROP TABLE, incluya la cláusula PURGEcomo se muestra en la sentencia de la diapositiva. 

Especifique PURGEsólo si desea borrar la tabla y liberar el espacio asociado a ella en un solo paso. Si especifica PURGE, la base de datos no coloca la tabla y sus objetos dependientes en la papelera de reciclaje. 

La utilización de esta cláusula es equivalente a borrar primero la tabla y purgarla después de la papelera de reciclaje. Esta cláusula le ahorra un paso en el proceso. Proporciona también una seguridad mejorada si desea evitar que aparezca material sensible en la papelera de reciclaje. 

Nota: No puede hacer rollback de una sentencia DROP TABLE con la cláusula PURGE, ni tampoco puede recuperar la tabla si la ha borrado con la cláusula PURGE. Esta función no estaba disponible en versiones anteriores. 

Read More

ON DELETE CASCADE / ON DELETE SET NULL

Restricciones en Cascada

• La cláusula CASCADE CONSTRAINTS se utiliza junto con la cláusula DROP COLUMN.
• La cláusula CASCADE CONSTRAINTS borra todas las restricciones de integridad referencial que hacen referencia a las claves única y primaria definidas en las columnas borradas.
• La cláusula CASCADE CONSTRAINTS también borra todas las restricciones de varias columnas definidas en las columnas borradas.

Restricciones en Cascada

Esta sentencia ilustra el uso de la cláusula CASCADE CONSTRAINTS. Suponga que se crea la tabla TEST1 de este modo:

CREATE TABLE test1 (
 pk NUMBER PRIMARY KEY,
 fk NUMBER,
 col1 NUMBER,
 col2 NUMBER,
 CONSTRAINT fk_constraint FOREIGN KEY (fk) REFERENCES test1,
 CONSTRAINT ck1 CHECK (pk > 0 and col1 > 0),
 CONSTRAINT ck2 CHECK (col2 > 0));

Se devuelve un error para las siguientes sentencias:

ALTER TABLE test1 DROP (pk);    —pk es una clave principal.
ALTER TABLE test1 DROP (col1);  —la restricción de varias columnas ck1
            hace referencia a col1.
Ejemplo:

ALTER TABLE emp2
DROP COLUMN employee_id CASCADE CONSTRAINTS;

ALTER TABLE test1
DROP (pk, fk, col1) CASCADE CONSTRAINTS;

Al ejecutar la siguiente sentencia, se borra la columna EMPLOYEE_ID, la restricción de clave primaria y las restricciones de clave ajena que hacen referencia a la restricción de clave primaria para la tabla EMP2:

ALTER TABLE emp2 DROP COLUMN employee_id CASCADE CONSTRAINTS;

Si se borran también todas las columnas a las que hacen referencia las restricciones definidas en las columnas borradas, CASCADE CONSTRAINTS no es necesario. Por ejemplo, suponiendo que ninguna otra restricción referencial de otras tablas hace referencia a la columna PK, es válido ejecutar la siguiente sentencia sin la cláusula CASCADE CONSTRAINTS para la tabla TEST1 creada en la página anterior:

ALTER TABLE test1 DROP (pk, fk, col1);

Read More