•Manipular datos mediante subconsultas.
•Describir las funciones de las inserciones de varias tablas.
•Utilizar los siguientes tipos de inserciones de varias tablas.
–INSERT incondicional.
–INSERT de pivoting.
–ALL INSERT condicional.
–FIRST INSERT condicional.
•Fusionar filas en una tabla.
NOTA: Usamos como ejemplo la base de datos: ORCL, la cual viene por defecto en cualquier versión de ORACLE.
_____________________________________________________________________________________
Puede utilizar subconsultas en sentencias DML para:
•Copiar datos de una tabla a otra.
•Recuperar datos de una vista en línea.
•Actualizar datos en una tabla basándose en los valores de otra tabla.
•Suprimir filas de una tabla basándose en filas de otra tabla.
Uso de Subconsultas para Manipular Datos.
Las subconsultas se pueden utilizar para recuperar datos de una tabla que podrá utilizar como entrada en una inserción (INSERT) en una tabla diferente. Así, le resultará fácil copiar grandes volúmenes de datos de una tabla a otra con una sola sentencia SELECT. De forma parecida, puede utilizar subconsultas para realizar actualizaciones y supresiones masivas en la cláusula WHERE de las sentencias UPDATE y DELETE. También puede utilizar subconsultas en la cláusula FROM de una sentencia SELECT. Esto se conoce como vista en línea.
Puede utilizar subconsultas en sentencias DML para:
•Copiar datos de una tabla a otra.
•Recuperar datos de una vista en línea.
•Actualizar datos en una tabla basándose en los valores de otra tabla.
•Suprimir filas de una tabla basándose en filas de otra tabla.
Puede utilizar sentencias INSERT para agregar filas a una tabla en la que los valores se deriven de tablas existentes. En lugar de la cláusula VALUES, utilice una subconsulta.
Sintaxis:
INSERT INTO table [ column (, column) ]
subquery;
En la sintaxis:
•table es el nombre de la tabla a la cual se va a insertar.
•column es el nombre de la columna de la tabla que se va a rellenar.
•subquery es la subconsulta que devuelve filas a la tabla.
En la sintaxis:
•table es el nombre de la tabla a la cual se va a insertar.
•column es el nombre de la columna de la tabla que se va a rellenar.
•subquery es la subconsulta que devuelve filas a la tabla.
El número de columnas y los tipos de datos de la lista de columnas de la cláusula INSERT se deben corresponder con el número de valores y los tipos de datos de la subconsulta. Para crear una copia de las filas de una tabla, utilice SELECT * en la subconsulta.
Ejemplo:
CREATE TABLE agentes_ventas
(
agente_id NUMBER(6),
agent_name VARCHAR2(45),
puesto VARCHAR2(20),
salario NUMBER(8,2),
comision NUMBER(2,2)
);
/*Creamos la tabla: agentes_ventas para insertar los empleados con puestos de ventas.*/
---
INSERT INTO agentes_ventas(agente_id, agent_name, salario, comision)
SELECT employee_id, first_name||' '||last_name, salary, commission_pct
FROM employees
WHERE job_id LIKE '%REP%';
/*Aquí insertamos en la tabla agentes_ventas todos los empleados con un puesto de trabajo que contenga la cadena REP.*/
---
Inserción mediante una Subconsulta como Destino.
Puede utilizar una subconsulta en lugar del nombre de tabla en la cláusula INTO de la sentencia INSERT.
La lista de selección de esta subconsulta debe tener el mismo número de columnas que la lista de columnas de la cláusula VALUES. Para que la sentencia INSERT funcione correctamente, se deben seguir las reglas de las columnas de la tabla base. Por ejemplo, no se puede agregar un identificador de empleado duplicado ni omitir un valor para una columna obligatoria NOT NULL.
Esta aplicación de las subconsultas evita tener que crear una vista únicamente para realizar una inserción (INSERT).
Ejemplo:
INSERT INTO
(SELECT agente_id, agent_name,
salario, comision
FROM agentes_ventas)
VALUES(99999, 'Julio Voltio',
8200, 0.30);
/*Acá vemos como usar una subconsulta en la cláusula INSERT INTO de lugar de la cláusula VALUES.*/
---
Recuperación de Datos con una Subconsulta como Origen.
Puede utilizar una subconsulta en la cláusula FROM de una sentencia SELECT, lo cual es muy parecido al uso de las vistas. Una subconsulta en la cláusula FROM de una sentencia SELECT se denomina también vista en línea. Una subconsulta en la cláusula FROM de una sentencia SELECT define un origen de datos para una sentencia SELECT en particular y sólo para esa sentencia SELECT.
Ejemplo:
SELECT a.last_name, a.salary,
a.department_id, b.salavg
FROM employees a, (SELECT department_id,
AVG(salary) salavg
FROM employees
GROUP BY department_id) b
WHERE a.department_id = b.department_id
AND a.salary > b.salavg;
/*El ejemplo muestra los apellidos, salarios, números de departamento y los salarios medios de todos los empleados que ganan más que el salario medio en su departamento. La subconsulta de la cláusula FROM se denomina b y la consulta externa hace referencia a la columna SALAVG con este alias.*/
---
Actualización de Columnas con Subconsultas.
Sintaxis:
Para actualizar varias columnas en la cláusula SET de una sentencia UPDATE, puede escribir varias subconsultas.
Sintaxis:
UPDATE table
SET column =
(SELECT column
FROM table
WHERE condition)
[ ,
column =
(SELECT column
FROM table
WHERE condition)]
[WHERE condition ];
Ejemplo:
UPDATE agentes_ventas
SET puesto = (SELECT job_id
FROM employees
WHERE employee_id = 205),
salario = (SELECT salary
FROM employees
WHERE employee_id = 205)
WHERE agente_id BETWEEN 150 AND 170;
/*En este ejemplo se actualiza la tabla agentes_ventas basándose en los valores de la tabla employees. Cambia el puesto y el salario de los empleados con IDs entre 150 y 170 para hacerlos corresponder con los del empleado 205.*/
---
Supresión de Filas Basándose en Otra Tabla.
Puede utilizar subconsultas para suprimir filas de una tabla basándose en valores de otra tabla.
Ejemplo:
DELETE FROM employees
WHERE employee_id NOT IN
(SELECT manager_id
FROM employees
WHERE manager_id IS NOT NULL)
AND department_id =
(SELECT department_id
FROM departments
WHERE department_name = 'IT');
/*En el ejemplo se suprimen todos los empleados que no son supervisores y que están en el departamento “IT”. La subconsulta busca en la tabla DEPARTMENTS el número de departamento basándose en el nombre de departamento “IT”. La segunda subconsulta proporciona el número de departamento a la consulta principal, que suprime filas de datos de la tabla EMPLOYEES basándose en este número de departamento.*/
---
Palabras Clave WITH CHECK OPTION.
Especifique WITH CHECK OPTION para indicar que, si se utiliza la subconsulta en lugar de una tabla en una sentencia INSERT, UPDATE o DELETE, en esa tabla no se permiten cambios que produzcan filas que no estén incluidas en la subconsulta.
Ejemplo:
INSERT INTO (SELECT employee_id, last_name, email,
hire_date, job_id, salary
FROM employees
WHERE department_id = 50
WITH CHECK OPTION)
VALUES (99998, 'Smith', 'JSMITH',
TO_DATE('07-JUN-99', 'DD-MON-RR'),
'ST_CLERK', 5000);
/*En el ejemplo, se utilizan las palabras clave WITH CHECK OPTION. La subconsulta identifica filas que están en el departamento 50, pero el identificador de departamento no está en la lista SELECT y no se proporciona un valor para dicho identificador en la lista VALUES. La inserción de esta fila da como resultado un identificador de departamento nulo, que no está en la subconsulta, por esta razón no se inserta el registro. Si eliminamos la .cláusula WHERE, la inserción se realiza.*/
_____________________________________________________________________________________
Valores por Defecto Explícitos.
La palabra clave DEFAULT se puede utilizar en sentencias INSERT y UPDATE para identificar un valor de columna por defecto. Si no existe ningún valor por defecto, se utiliza un valor nulo.
La opción DEFAULT le ahorra tener que codificar el valor por defecto en los programas o consultar el diccionario para encontrarlo, que es lo que se debía hacer antes de que se introdujese esta función. Es problemático codificar el valor por defecto si éste cambia, porque también habría que cambiar el código de forma consecuente. No se suele acceder al diccionario en un programa de aplicación, por lo que esta función es muy importante.
•La función de valor por defecto explícito le permite utilizar la palabra clave DEFAULT como valor de columna donde se desea el valor por defecto de columna.
•Esta función se ha agregado por compatibilidad con el estándar SQL:1999.
•Esto permite al usuario controlar dónde y cuándo se debe aplicar el valor por defecto a los datos.
Nota: Al crear o modificar una tabla, puede especificar un valor por defecto para una columna. La palabra clave DEFAULT no puede ser usado en la cláusula WHERE.
Ejemplos:
ALTER TABLE agentes_ventas
ADD
(
departamento NUMBER(4) DEFAULT 80
);
/*Agregamos la columna departamento a la tabla agentes_ventas con 80 como valor por defecto. Al ejecutar esta sentencia, todos los registros ya insertados en la tabla toman el valor 80.*/
---
INSERT INTO agentes_ventas(agente_id, agent_name, departamento)
VALUES (555, 'Vicente Lopez', 50);
/*Acá insertamos un registro con el # de departamento 50. Tener en cuenta que pudimos usar la función DEFAULT en lugar de 50 para así insertar el numero 80, de igual manera si omitíamos esa columna, hubiese tomado el valor por defecto. */
---
UPDATE agentes_ventas
SET departamento = DEFAULT
SET departamento = DEFAULT
WHERE departamento = 50;
/*En este ejemplo le cambiamos el # de departamento a los empleados(en este caso 1) con # de departamento 50 al valor por defecto(80).*/
_____________________________________________________________________________________
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 optimo.
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.
Los tipos de sentencias INSERT de varias tablas son:
•INSERT incondicional.
•ALL INSERT condicional.
•FIRST INSERT condicional.
•INSERT de pivoting.
Se utilizan diferentes cláusulas para indicar el tipo de inserción (INSERT) que se ejecutará.
Sintaxis:
•Incondicional_insert_clause:
INSERT [ALL] [unconditional_insert_clause][insert_into_clause values_clause] (subquery)
INSERT [ALL] [FIRST]
[WHEN condition THEN] [insert_into_clause values_clause]
[ELSE] [insert_into_clause values_clause]
INSERT de Pivoting.
Ejemplos:
CREATE TABLE emp_by_dept
(
dept_id NUMBER(6),
nombre VARCHAR2(30),
cantidad NUMBER(3)
);
---
CREATE TABLE emp_by_mgr
(
mgr_id NUMBER(6),
nombre VARCHAR2(45),
cantidad NUMBER(3)
);
/*En las sentencias anteriores creamos las tablas que usaremos en nuestros ejemplos; emp_by_dept: empleados por departamento y emp_by_mgr: empleados por manager.*/
---
INSERT ALL
INTO emp_by_dept VALUES(dept_id,nombre_dept,cant_dept)
INTO emp_by_mgr VALUES(mgr_id,nombre_mgr,cant_mgr)
SELECT
NULL AS dept_id,
NULL AS nombre_dept,
NULL AS cant_dept,
m.employee_id AS mgr_id,
m.first_name||' '||m.last_name AS nombre_mgr,
count(e.employee_id) AS cant_mgr
FROM employees m, employees e
WHERE m.employee_id = e.manager_id
GROUP BY m.employee_id, m.first_name||' '||m.last_name
UNION
SELECT
d.department_id AS dept_id,
d.department_name AS nombre_dept,
count(e.employee_id) AS cant_dept,
NULL AS mgr_id,
NULL AS nombre_mgr,
NULL AS cant_mgr
FROM employees e, departments d
WHERE e.department_id = d.department_id
GROUP BY d.department_id, d.department_name;
/*En el ejemplo anterior vemos el uso de la sentencia INSERT ALL unconditional , en la cual insertados todos los registros de la consulta, en las tablas creadas anteriormente; El primer Query(antes del UNION) retorna 3 columnas con valores nulos(para así hacerla corresponder con la consulta debajo del UNION) y la cantidad de empleados de la tabla employees ordenados por nombre y numero de manager; El segundo Query retorna la cantidad de empleados ordenados por nombre y numero de departamento mas nuestras columnas ficticias. Ya que dicha Sentencia inserta cada registro una vez por cada tabla, si consultamos nuestras tablas notamos que hay valores nulos en algunos registros(producto de nuestras columnas ficticias: NULL).*/
---
INSERT ALL
WHEN dept_id IS NULL
AND nombre_dept IS NULL THEN
INTO emp_by_mgr VALUES(mgr_id,nombre_mgr,cant_mgr)
WHEN dept_id IS NOT NULL
AND nombre_dept IS NOT NULL THEN
INTO emp_by_dept VALUES(dept_id,nombre_dept,cant_dept)
SELECT
NULL AS dept_id,
NULL AS nombre_dept,
NULL AS cant_dept,
m.employee_id AS mgr_id,
m.first_name||' '||m.last_name AS nombre_mgr,
count(e.employee_id) AS cant_mgr
FROM employees m, employees e
WHERE m.employee_id = e.manager_id
GROUP BY m.employee_id, m.first_name||' '||m.last_name
UNION
SELECT
d.department_id AS dept_id,
d.department_name AS nombre_dept,
count(e.employee_id) AS cant_dept,
NULL AS mgr_id,
NULL AS nombre_mgr,
NULL AS cant_mgr
FROM employees e, departments d
WHERE e.department_id = d.department_id
GROUP BY d.department_id, d.department_name;
[WHEN condition THEN] [insert_into_clause values_clause]
[ELSE] [insert_into_clause values_clause]
INSERT Incondicional: ALL into_clause
Especifique ALL seguido de varias cláusulas insert_into_clauses para realizar una inserción incondicional de varias tablas. Oracle Server ejecuta cada insert_into_clause una vez para cada fila devuelta por la subconsulta.
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: ALL
Si especifica ALL, Oracle Server evalúa cada cláusula WHEN independientemente de los resultados de la evaluación de cualquier otra cláusula WHEN. Para cada cláusula WHEN cuya condición se evalúe como verdadera, Oracle Server ejecuta la lista correspondiente de cláusulas INTO.
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.
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.
Ejemplos:
CREATE TABLE emp_by_dept
(
dept_id NUMBER(6),
nombre VARCHAR2(30),
cantidad NUMBER(3)
);
---
CREATE TABLE emp_by_mgr
(
mgr_id NUMBER(6),
nombre VARCHAR2(45),
cantidad NUMBER(3)
);
/*En las sentencias anteriores creamos las tablas que usaremos en nuestros ejemplos; emp_by_dept: empleados por departamento y emp_by_mgr: empleados por manager.*/
---
INSERT ALL
INTO emp_by_dept VALUES(dept_id,nombre_dept,cant_dept)
INTO emp_by_mgr VALUES(mgr_id,nombre_mgr,cant_mgr)
SELECT
NULL AS dept_id,
NULL AS nombre_dept,
NULL AS cant_dept,
m.employee_id AS mgr_id,
m.first_name||' '||m.last_name AS nombre_mgr,
count(e.employee_id) AS cant_mgr
FROM employees m, employees e
WHERE m.employee_id = e.manager_id
GROUP BY m.employee_id, m.first_name||' '||m.last_name
UNION
SELECT
d.department_id AS dept_id,
d.department_name AS nombre_dept,
count(e.employee_id) AS cant_dept,
NULL AS mgr_id,
NULL AS nombre_mgr,
NULL AS cant_mgr
FROM employees e, departments d
WHERE e.department_id = d.department_id
GROUP BY d.department_id, d.department_name;
/*En el ejemplo anterior vemos el uso de la sentencia INSERT ALL unconditional , en la cual insertados todos los registros de la consulta, en las tablas creadas anteriormente; El primer Query(antes del UNION) retorna 3 columnas con valores nulos(para así hacerla corresponder con la consulta debajo del UNION) y la cantidad de empleados de la tabla employees ordenados por nombre y numero de manager; El segundo Query retorna la cantidad de empleados ordenados por nombre y numero de departamento mas nuestras columnas ficticias. Ya que dicha Sentencia inserta cada registro una vez por cada tabla, si consultamos nuestras tablas notamos que hay valores nulos en algunos registros(producto de nuestras columnas ficticias: NULL).*/
INSERT ALL
WHEN dept_id IS NULL
AND nombre_dept IS NULL THEN
INTO emp_by_mgr VALUES(mgr_id,nombre_mgr,cant_mgr)
WHEN dept_id IS NOT NULL
AND nombre_dept IS NOT NULL THEN
INTO emp_by_dept VALUES(dept_id,nombre_dept,cant_dept)
SELECT
NULL AS dept_id,
NULL AS nombre_dept,
NULL AS cant_dept,
m.employee_id AS mgr_id,
m.first_name||' '||m.last_name AS nombre_mgr,
count(e.employee_id) AS cant_mgr
FROM employees m, employees e
WHERE m.employee_id = e.manager_id
GROUP BY m.employee_id, m.first_name||' '||m.last_name
UNION
SELECT
d.department_id AS dept_id,
d.department_name AS nombre_dept,
count(e.employee_id) AS cant_dept,
NULL AS mgr_id,
NULL AS nombre_mgr,
NULL AS cant_mgr
FROM employees e, departments d
WHERE e.department_id = d.department_id
GROUP BY d.department_id, d.department_name;
/*En este ejemplo usamos la misma consulta del ejemplo anterior, pero ahora mostrando el uso de la sentencia INSERT ALL conditional; Los registros en los cuales el campo dept_id IS NULL y nombre_dept IS NULL(Query antes del UNION) son insertados en emp_by_mgr; los registros en los cuales el campo dept_id IS NOT NULL y nombre_dept IS NOT NULL(Query después del UNION) son insertados en la tabla emp_by_dept.
Nota: si algún registro cumple con las dos condiciones, es insertado en ambas tablas(no aplica para nuestro ejemplo).*/
CREATE TABLE excelente_sal
(
numero_dept NUMBER(4),
cant_emp NUMBER(3),
total_sal NUMBER(7)
);
---
CREATE TABLE buen_sal
AS
SELECT *
FROM excelente_sal;
---
CREATE TABLE mal_sal
AS
SELECT *
FROM excelente_sal;
---
CREATE TABLE pesimo_sal
AS
SELECT *
FROM excelente_sal;
/*Las Tablas anteriores serán usadas para nuestro ejemplo.*/
INSERT FIRST
WHEN sal/cantidad > 9999 THEN
INTO excelente_sal VALUES(dept_id, cantidad, sal)
WHEN sal/cantidad > 8000 THEN
INTO buen_sal VALUES(dept_id, cantidad, sal)
WHEN sal/cantidad > 4000 THEN
INTO mal_sal VALUES(dept_id, cantidad, sal)
ELSE
INTO pesimo_sal VALUES(dept_id, cantidad, sal)
SELECT
department_id AS dept_id,
COUNT(employee_id) AS cantidad,
SUM(salary) AS sal
FROM employees
GROUP BY department_id;
/*En el INSERT FIRST cada registro se inserta una vez dependiente de la condición que satisfaga, Aunque un registro cumpla con mas de un condición solo se inserta una vez(en la primera condición WHEN THEN).*/
---
Para el ejemplo de la sentencia pivoting suponga:
•Que recibe un juego de registros de ventas, de una tabla de Base de Datos no relacional, FUENTE_DATOS_VENTAS, con el siguiente formato:
- id_emp, id_semana, ventas_lun, ventas_mar, ventas_mi, ventas_ju, ventas_vi
•Desea almacenar estos registros en la tabla INFO_VENTAS con un formato relacional más normal:
- id_emp, semana, ventas
•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.
INSERT ALL
INTO info_ventas VALUES (id_emp,id_semana,ventas_lun)
INTO info_ventas VALUES (id_emp,id_semana,ventas_mar)
INTO info_ventas VALUES (id_emp,id_semana,ventas_mi)
INTO info_ventas VALUES (id_emp,id_semana,ventas_ju)
INTO info_ventas VALUES (id_emp,id_semana,ventas_vi)
SELECT
id_emp,
id_semana,
ventas_lun,
ventas_mar,
ventas_mi,
ventas_ju,
ventas_vi
FROM fuente_datos_ventas;
/*En este ejemplo, cada registro extraído de la tabla fuente_datos_ventas es insertado 5 veces en la tabla info_ventas, pero ahora con un formato/distribución diferente.*/
_____________________________________________________________________________________
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.
En conclusion la sentencia MERGE:
•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.
Sintaxis:
MERGE INTO table_name table_aliasUSING (table|view|sub_query) alias
ON (join condition)
WHEN MATCHED THEN
UPDATE SET
col1 = col_val1,
col2 = col2_val
WHEN NOT MATCHED THEN
INSERT (column_list)
VALUES (column_values);
En la sintaxis:
•Cláusula INTO especifica la tabla destino que se va a actualizar o en la que se va a insertar.
•Cláusula USING identifica el origen de los datos que se van a actualizar o insertar; puede ser una tabla, una vista o una subconsulta.
•Cláusula ON condición según la cual la operación MERGE actualiza o inserta.
•WHEN MATCHED |WHEN NOT MATCHED indica al servidor cómo responder a los resultados de la condición de unión.
Ejemplo:
CREATE TABLE nivel_emp
AS
SELECT
employee_id AS id_emp,
first_name||' '||last_name AS nombre,
DECODE (ROWNUM,
1, 30,
2, 35,
3, 40,
50) AS edad,
department_id AS id_dept,
salary AS salario,
'Elite' AS nivel
FROM employees
WHERE salary > 12000;
/*Para los futuros ejemplos creamos la tabla nivel_emp; con los campos id_emp, nombre, edad, id_dept, salario y nivel; Al momento de su creación la tabla contendrá algunos registros que cumplen con la condición de salary > 12000; La columna edad de nuestra tabla fue poblada con el DECODE, si el numero de fila retornado en la consulta era 1 inserta 30, si es 2 -> 35, 3 -> 40 y si es mas de ahí sera 50; la columna nivel tendrá 'Elite' para cada registro insertado.*/
---
MERGE INTO nivel_emp n
USING employees e
ON (n.id_emp = e.employee_id)
WHEN MATCHED THEN
UPDATE SET
n.edad = 31,
n.id_dept = 0,
n.salario = 30000
WHEN NOT MATCHED THEN
INSERT VALUES(e.employee_id, e.first_name||' '||e.last_name,
25, 3, e.salary, 'Peon');
/*Acá vemos el uso de la sentencia MERGE, en la cual se actualizan los campos edad, id_dept y salario de la tabla nivel_emp para los id_emp que existan en la tabla employees; los registros que no existan en nivel_emp son insertados.*/
MERGE INTO nivel_emp n
USING employees e
ON (n.id_emp = e.employee_id)
WHEN MATCHED THEN
UPDATE SET
n.edad = 31,
n.id_dept = 0,
n.salario = 30000
WHEN NOT MATCHED THEN
INSERT VALUES(e.employee_id, e.first_name||' '||e.last_name,
25, 3, e.salary, 'Peon');
/*Acá vemos el uso de la sentencia MERGE, en la cual se actualizan los campos edad, id_dept y salario de la tabla nivel_emp para los id_emp que existan en la tabla employees; los registros que no existan en nivel_emp son insertados.*/
_____________________________________________________________________________________
_____________________________________________________________________________________
