Objetivos:

• Describir cada una de las sentenciasDML(Lenguaje de Manipulación de Datos).
• Insertar filas en una tabla.
• Actualizar las filas de una tabla.
• Suprimir filas de una tabla.
• Controlar transacciones.

NOTA: Usamos como ejemplo la base de datos: ORCL, la cual viene por defecto en cualquier versión de ORACLE.

Lenguaje de Manipulación de Datos.

DML (Lenguaje de manipulación de datos o Data Manipulation Language) es una parte fundamental de SQL. Cuando desee agregar, actualizar o suprimir datos en la base de datos, debe ejecutar una sentencia DML. Una recopilación de sentencias DML que forman una unidad lógica de trabajo se denomina transacción. 

Piense en una base de datos bancaria. Cuando un cliente del banco transfiere dinero de una cuenta de ahorros a una cuenta corriente, la transacción podría consistir en tres operaciones diferentes: extraer de la cuenta de ahorros, sumar a la cuenta corriente y registrar la transacción en los asientos diarios de transacciones. Oracle Server debe garantizar que se realicen las tres sentencias SQL para mantener las cuentas con el saldo adecuado. Si algo impide que una de las sentencias de la transacción se ejecute, las demás sentencias de la transacción se deben deshacer.

• Una sentencia DML se ejecuta al:

–Agregar nuevas filas a una tabla

–Modificar filas existentes de una tabla

–Eliminar filas existentes de una tabla

• Una transacción consiste en una recopilación de sentencias DML que forman una unidad lógica de trabajo.

Sentencia INSERT.

Para agregar o insertar una o varias filas de datos a una tabla usamos la sentencia INSERT.

Sintaxis para una Fila:

INSERT INTO table [(column [, column...])] 
VALUES (value [, value...]);

Definición:

• tablees el nombre de la tabla.
• columnes el nombre de la columna de la tabla que se va a rellenar.
• valuees el valor correspondiente para la columna.

Nota: Esta sentencia con la cláusula VALUES agrega sólo una fila cada vez a la tabla.

Algunas Directrices:

• Como puede insertar una nueva fila que contiene valores para cada columna, la lista de columnas no es necesaria en la cláusula INSERT. Sin embargo, si no utiliza la lista de columnas, los valores se deben mostrar de acuerdo con el orden por defecto de las columnas de la tabla y se debe proporcionar un valor para cada columna.
• Para mejorar la claridad, utilice la lista de columnas en la cláusula INSERT.
• Ponga los valores de caracteres y de fecha entre comillas simples; no se recomienda poner valores numéricos entre paréntesis entre comillas simples. 
• Los valores numéricos no se deben poner entre comillas simples, ya que se puede producir una conversión implícita de los valores numéricos que se asignan a las columnas del tipo de datosNUMBERsi se incluyen comillas simples.
• Puede utilizar funciones para introducir valores especiales en la tabla.
• Puede usar variables de sustitución en la cláusula VALUES.
• Oracle Server fuerza automáticamente todos los tipos de datos, los rangos de datos y las restricciones de integridad de datos. Cualquier columna que no se muestre explícitamente obtiene un valor nulo en la fila nueva.
• Errores habituales que se pueden producir durante la entrada de usuario: 

-Falta de un valor obligatorio para una columna NOT NULL.

-Un valor duplicado viola la restricción de unicidad.

-Violación de la restricción de clave primaria.

-Violación de la restricción CHECK.

-Incongruencia en el tipo de dato.

-Valor demasiado ancho que no cabe en la columna.

• Puedes ver la estructura de la tabla con el comando: DESCRIBE:

 DESCRIBE departments;|| DESC departments;

Nota: Algunos ejemplos a continuación dependen de su predecesor. Es también prudente que consultes la tabla luego de cada instrucción DML para que vemos los resultados.

Ejemplos:

INSERT INTO departments(department_id,
       department_name, manager_id, location_id)
VALUES (370, 'Department Ejemplo 1', 101, 1400);

/*El anterior ejemplo inserta un nuevo departamento llamado Department Ejemplo 1 con el código 370, manager 101 y en la locación 1400; Aquí enumeramos todos los campos de la tabla y proporcionamos un valor para cada uno.*/

INSERT INTO employees(employee_id, last_name, email, hire_date)
VALUES (1,'Moquete','Esta dirección de correo electrónico está protegida contra spambots. Necesita activar JavaScript para visualizarla.';,TO_DATE('06/12/2016','MM/DD/YYYY'));

/*En este ejemplo insertamos un empleado, notamos que solo especificamos 4 campos y aportamos valores para cada uno de ellos en la cláusula VALUES, también vemos el uso de la función TO_DATE para así formatear la fecha.*/

INSERT INTO departments
VALUES (371,'Humo y Grasa', 1, 1400);

/*En este ejemplo insertamos el departamento 'Humo y Grasa' con el código 371 y como manager el empleado 1(previamente insertado); notamos que no enumeramos los nombre de cada columna en la tabla, pero proporcionamos todos los valores en su orden correspondiente.*/

INSERT INTO employees(employee_id,
                      first_name, last_name,
                          email, hire_date,
                          phone_number, salary,
                          job_id, manager_id,
                          department_id)
VALUES (500,
        NULL, 'Lucas',
        'Esta dirección de correo electrónico está protegida contra spambots. Necesita activar JavaScript para visualizarla.';,
        TO_DATE('06/14/2016','MM/DD/YYYY'),
        '8095570000', NULL,
        NULL, 1, NULL);

/*En este ejemplo vemos como se especifican varias columnas a las cuales se le pasa un valor nulo en la cláusula VALUES; esto es equivalente a no especificar dicha columna y no pasarle valor en la cláusula VALUES.*/

INSERT INTO employees(employee_id,first_name,last_name,hire_date,email)
VALUES (501,'&primer_nombre','&segundo_nombre',SYSDATE,'&correo'||Esta dirección de correo electrónico está protegida contra spambots. Necesita activar JavaScript para visualizarla.');

/*En este ejemplo vemos que es posible usar variables de sustitución en una instrucción INSERT; Al ejecutar este SCRIPT se le pide al usuario introducir un valor para la variable &primer_nombre, &segundo_nombre y &correo; también vemos que es posible usar funciones como SYSDATE para insertar la fecha actual y que es posible usar la concatenación de cadenas de caracteres.*/

Puede usar 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 para varias Filas:

INSERT INTO table [ column (, column) ]
subquery;

Definición:

• tablees el nombre de la tabla.
• columnes el nombre de la columna de la tabla que se va a rellenar.
• subqueryes la subconsulta que devuelve filas a la tabla.
• No utilice la cláusula VALUES.
• El número de columnas y los tipos de datos de la lista de columnas deben corresponder con el número de valores y los tipos de datos de la subconsulta.

Ejemplos:

INSERT INTO sales_reps(id, name, salary, commission_pct)
  SELECT employee_id, last_name, salary, commission_pct
  FROM   employees
  WHERE  job_id LIKE '%REP%';

/*Suponiendo que tenemos una tabla llamada sales_reps con los campos id, name, salary y commission_pct, podemos insertar en ella con el Query anterior, dicho Query extrae todos los empleados que tengan una puesto de trabajo que contenga la cadena REP y los los inserta en sales_reps.*/

INSERT INTO jobs
SELECT
    CASE WHEN job_id LIKE '%VP' THEN 'PEON'
         WHEN job_id LIKE '%PRES' THEN 'JEFE_MAN' END AS JOB_ID,
    CASE WHEN job_id LIKE '%VP' THEN 'Subalterno'
         WHEN job_id LIKE '%PRES' THEN 'El Manda Mas' END AS JOB_TITLE,
    0,
    salary
FROM employees
WHERE job_id LIKE '%VP'
OR job_id LIKE '%PRES';

/*En la sentencia anterior se insertan dos registros, uno con el job_id: 'PEON' y otro como JEFE_MAN; En el Subquery se evalúa el job_id de los empleados con job_id que terminen con 'VP' o 'PRES'(uso estos porque se que solo son dos), si es 'VP' se insertara un registro en la tabla jobs con job_id: 'PEON',  job_title 'Subalterno' y con un rango de salario de 0 al salario del empleado extraído de la tabla employees(17000) y si es 'PRES' se inserta un job_id: 'JEFE_MAN', job_title: 'El Manda Mas' y un rango de salario de 0 al salario del empleado extraído de la tabla employees(24000)*/

Sentencia UPDATE.

Una vez insertados los datos se darán situaciones en las cuales tengas que modificar la data. Para ello puedes modificar filas existentes mediante la sentencia UPDATE.

Sintaxis:

UPDATE table
SET column = value [, column = value, ...]
[WHERE condition];

Definición:

• tablees el nombre de la tabla.
• columnes el nombre de la columna de la tabla que se va a rellenar.
• valuees el valor correspondiente o la subconsulta para la columna.
• conditionidentifica las filas que se actualizarán y se compone de nombres de columna, expresiones, restricciones, subconsultas y operadores de comparación. Si omite la cláusula WHERE, todas la filas de dicha tabla se actualizarían.
• En general, utilice la clave primaria para identificar una única fila. Si utiliza otras columnas, puede provocar que se actualicen varias filas inadvertidamente. Por ejemplo, identificar una sola fila de la tabla EMPLOYEES por el nombre es peligroso, ya que es posible que haya más de un empleado con el mismo nombre.

Nota: El fin de los ejemplos a continuación es de orientar acerca del uso de la Sentencia UPDATE, por lo cual puede que no se perciba su aplicación.

Ejemplos:

UPDATE employees
SET manager_id = 1
WHERE employee_id = 501;

/*En este ejemplo se le cambia el numero de departamento de NULL a 1 al empleado 501; Estos datos los insertamos en los ejemplos anteriores.*/

UPDATE employees
SET salary = 4001,
        department_id  = 371,
        job_id = 'PEON'
WHERE employee_id in (500, 501);

/*En este ejemplo vemos como modificar mas de un campo a mas de un registro; Le cambiamos el numero de departamento de NULL a 371, los salarios de NULL a 4001 y el tipo de trabajo de NULL a PEON a los empleados 500 y 5001*/

UPDATE employees
SET first_name = 'Armando',
    last_name = 'Cadenas',
    salary = 11000,
    job_id = 'JEFE_MAN'
WHERE employee_id =
           (SELECT manager_id
            FROM departments
            WHERE department_id = 371);

/*En este ejemplo le cambiamos el apellido de Moquete a Cadenas el nombre de NULL a Armando, el puesto de NULL a JEFE_MAN y el salario de NULL 11000 al empleado manager del departamento 371; como vemos es posible usar subconsultas para actualizar campos de una tabla.*/

UPDATE employees
SET hire_date=
            (SELECT MIN(start_date)
             FROM job_history),
    commission_pct =
               (SELECT MAX(commission_pct)
                FROM employees)
WHERE department_id IN
                 (SELECT department_id
                  FROM employees
                  WHERE UPPER(email) LIKE '%MAGICPL%');

/*En este ejemplo usamos 3 subqueries, dos para buscar los valores que se quieren asignar y el otro para establecer la condición de a cuales empleados se le cambiara esos datos; el primer subquery busca la fecha mas vieja de la tabla job_history, el segundo busca la mayor comisión de la tabla employees y ambos valores son asignados(3rd Query) a los empleados con un algún código de departamento igual que algún empleado con un email que contenga la cadena: MAGICPL*/

Sentencia DELETE.

Puede eliminar filas existentes mediante la sentencia DELETE.

Sintaxis:

DELETE [FROM] table 
[WHERE condition];

Definición:

• tablees el nombre de la tabla de la cual se quiere eliminar.
• conditionidentifica las filas que se suprimirán y se compone de nombres de columna, expresiones, restricciones, subconsultas y operadores de comparación. Si omite la cláusula WHERE, todas la filas de dicha tabla se eliminarían.

Nota: Si no se suprime ninguna fila, se devuelve el mensaje “0 rows deleted”.

Ejemplos:

DELETE FROM employees
WHERE employee_id BETWEEN 500 AND 501;

/*El ejemplo anterior elimina los empleados con los códigos 500 y 501.*/

DELETE FROM departments
WHERE manager_id IN
                (SELECT employee_id
                 FROM employees
                 WHERE LOWER(last_name) = 'cadenas');

/*Este ejemplo elimina los departamentos(en este caso 1) que tengan como manager algún empleado que tenga como apellido: cadenas; para ello usamos un subquery en la cláusula WHERE.*/

Sentencia TRUNCATE.

Un método más sencillo para vaciar una tabla es la sentencia TRUNCATE.

Puede utilizar esta sentencia para eliminar rápidamente todas las filas de una tabla o de un agrupamiento. Eliminar filas con la sentencia TRUNCATE es más rápido que hacerlo con la sentencia DELETE por estos motivos: 

• La sentencia TRUNCATE es una sentenciaDDL(Lenguaje de Definición de Datos) y no genera información de ROLLBACK. La información de ROLLBACK se trata más adelante en esta Publicación.
• Al truncar una tabla no se arrancan los disparadores de supresión de la tabla.
• Si la tabla es la tabla principal de una restricción de integridad referencial, no se puede truncar. Debe desactivar la restricción antes de emitir la sentencia TRUNCATE. La desactivación de restricciones se trata en una publicación posterior.

Sintaxis:

TRUNCATE TABLE table_name;

Definición:

• table_name: Es la tabla a Truncar.
• Como notan no se puede especificar una condición de eliminación, Por ello elimina todos lo datos de la tabla.

Nota: como aun no hemos tratado el Tema de creación de Objectos(Sentencias DDL), no limitaremos a dar un ejemplo; Otra razón es que las tablas por defecto de ORCL tienen restricciones(Constraints) que no nos permitirían Truncarlas.

Transacciones de la Base de Datos.

Oracle Server asegura la consistencia de datos basándose en transacciones. Las transacciones le proporcionan más flexibilidad y control al cambiar datos y aseguran la consistencia de los datos en caso de un fallo de proceso de usuario o del sistema. 

Las transacciones constan de sentencias DML que constituyen un cambio consistente en los datos. Por ejemplo, una transferencia de fondos entre dos cuentas debería incluir el débito en una cuenta y el crédito en otra en la misma cantidad. Ambas acciones deben fallar o tener éxito al mismo tiempo, el crédito no se debería validar sin el débito.

Una transacción de base de datos consta de: 

• Sentencias DML que constituyen un cambio consistente en los datos.
• Una sentenciaDDL.
• Una sentencia DCL (Lenguaje de Control de Datos).

Una transacción comienza cuando se encuentra la primera sentencia DML y termina cuando ocurre alguna de estas cosas: 

• Se emite una sentenciaCOMMITo ROLLBACK. 
• Se emite una sentencia DDL, comoCREATE. 
• Se emite una sentenciaDCL. 
• El usuario sale de la Consola: iSQL*Plus/SQL Developer/Toadetc. 
• Falla una máquina o se bloquea el sistema. 

Cuando termina una transacción, la siguiente sentencia SQL ejecutable inicia automáticamente la siguiente transacción. 

Una sentencia DDL o una sentencia DCL se validan automáticamente, con lo que terminan implícitamente una transacción.

Sentencias COMMIT Y ROLLBACK.

Una vez realizamos transacciones DML los datos se encuentra en memoria, no en los archivos(Data Files) de la base de datos. Para asentar en los cambios en los Data Files es necesario hacer COMMIT, o puede realizar un ROLLBACK para deshacerlos.

El uso de dichas sentencias podemos:

• Garantizar la consistencia de datos.
• Realizar una presentación preliminar de los cambios de datos antes de hacer que éstos sean permanentes.
• Agrupar operaciones relacionadas lógicamente.
• Controlar la lógica de las transacciones.

Nota: SAVEPOINT no es SQL del estándar ANSI.

Ejemplos:

INSERT INTO jobs
VALUES('BAGO','La Mejor Vida', -500,0);
ROLLBACK;

/*En este ejemplo un nuevo código de trabajo(BAGO) es insertado y luego usamos la sentencia ROLLBACK; si consultamos la tabla notamos que dicho registro no aparece y por lo tanto podemos ejecutar el insert nuevamente sin recibir ningún error. 

Nota: debido al ROLLBACK, todos los cambios de los ejemplos anteriores fueron descartados de modo que los registros no aparecen en la tablas.*/

INSERT INTO jobs
VALUES('FIX_TUBOS','Plomero', 500,1000);
COMMIT;

/*En este ejemplo se inserta un nuevo código de trabajo(FIX_TUBOS) y luego usamos la sentencia COMMIT; si consultamos la tabla notamos que el registro aparece y por lo tanto no podemos ejecutar el insert nuevamente ya que dicha tabla tiene un restricción UNIQUE en el campo job_id. Este registro permanecería en la tabla incluso después de realizar un ROLLBACK.*/

INSERT INTO employees(employee_id,last_name,email,hire_date)
VALUES((SELECT MAX(employee_id)+1
                  FROM employees),'Ejemplo SavePoint1','save@',SYSDATE);

SAVEPOINT SP1;

INSERT INTO employees(employee_id,last_name,email,hire_date)
VALUES((SELECT MAX(employee_id)+1

                 FROM employees),'Ejemplo SavePoint2','save2@',SYSDATE);

INSERT INTO departments(department_id,department_name)
VALUES(400,'La Esquina');

ROLLBACK TO SAVEPOINT SP1;

/*En la secuencia de ejemplos anteriores se hace lo siguiente: Se inserta un nuevo empleado con el código mas alto de la tabla employees mas 1 (subquery en la cláusula Values); luego creamos el SAVEPOINT SP1 y seguido de eso insertamos otro empleado y luego un departamento; por último hacemos ROLLBACK TO SAVEPOINT SP1; esta ultima sentencia descarta el segundo inserta en la tabla employees y el insert realizado a la tabla departments. Como no hicimos COMMIT, los datos insertados en el primer ejemplo(Ejemplo SavePoint1) están solo en la memoria Buffer.*/

Procesamiento de Transacciones Implícito.

• Se produce una validación(COMMIT) automática en las siguientes circunstancias:

–Se emite una sentencia DDL.

–Se emite una sentencia DCL.

–Salida normal de la consola(iSQL*Plus, SQL Developer...), sin emitir explícitamente sentencias COMMIT o ROLLBACK.

• Se produce un rollback automático tras una terminación anormal de la consola o un fallo del sistema.

Ejemplo:

/*Si insertamos un registro en X tabla y luego creamos algún objeto de base de datos(tabla, trigger...) se realiza un COMMIT Implícito.*/

Los Datos antes de COMMIT o ROLLBACK.

Todos los cambios de datos realizados durante la transacción son temporales hasta la validación de la transacción. 

El estado de los datos antes de que se emitan las sentencias COMMIT o ROLLBACK se puede describir así: 

• Las operaciones de manipulación de datos afectan principalmente al buffer de la base de datos; por lo tanto, se puede recuperar el estado anterior de los datos. 
• El usuario actual puede revisar los resultados de las operaciones de manipulación de datos consultando las tablas. 
• Los demás usuarios no pueden ver los resultados de las operaciones de manipulación de datos realizadas por el usuario actual.Oracle Serverinstituye la consistencia de lectura para garantizar que cada usuario vea los datos como existían en el momento de la última validación. 
• Las filas afectadas están bloqueadas; los demás usuarios no pueden cambiar los datos dentro de las filas afectadas.

Estado de los Datos después de COMMIT.

Puede hacer que todos los cambios pendientes sean permanentes mediante la sentencia COMMIT. Esto es lo que sucede después de una sentencia ROLLBACK: 

• Los cambios en los datos se escriben en la base de datos. 
• El estado anterior de los datos ya no está disponible con las consultasSQLnormales. 
• Todos los usuarios pueden ver los resultados de la transacción. 
• Se liberan los bloqueos en las filas afectadas; las filas ya no están disponibles para que otros usuarios realicen nuevos cambios en los datos. 
• Se borran todos los puntos de grabación.

Estado de los Datos después de ROLLBACK.

Deseche todos los cambios pendientes mediante la sentencia ROLLBACK, lo que da esto como resultado: 

• Se deshacen los cambios de datos.
• Se restaura el estado anterior de los datos.
• Se liberan los bloqueos en las filas afectadas.

Rollback de Nivel de Sentencia.

Se puede desechar parte de una transacción mediante un rollback implícito si se detecta un error de ejecución de sentencia. Si falla una única sentencia DML durante la ejecución de una transacción, se deshace su efecto mediante un rollback de nivel de sentencia, pero los cambios realizados por las sentencias DML de la transacción no se desechan. El usuario puede validarlos o hacer rollback en ellos explícitamente. 

Oracle Server emite una validación implícitamente antes y después de cualquier sentencia DDL. Así pues, si la sentencia DDL no se ejecuta correctamente, no puede hacer rollback en la sentencia anterior porque el servidor emitió una validación. 

Termine las transacciones explícitamente ejecutando una sentencia COMMIT o ROLLBACK.

Consistencia de Lectura.

Los usuarios de bases de datos acceden a la base de datos de dos formas: 

• Operaciones de lectura (sentenciaSELECT) 
• Operaciones de escritura (sentenciasINSERT,UPDATE, DELETE) 

Necesita la consistencia de lectura para que se produzca lo siguiente: 

• Se garantiza una visualización consistente de los datos al lector y al escritor de base de datos. 
• Los lectores no ven los datos que están en proceso de cambio. 
• Se garantiza a los escritores que los cambios en la base de datos se realizan de forma consistente. 
• Los cambios que realiza un escritor no molestan ni entran en conflicto con los que realice otro escritor. 

El objetivo de la consistencia de datos es que cada usuario vea los datos tal como existían en el momento de la última validación, antes de iniciarse una operación DML.

Ejemplo:

/*El usuario SCOTT con acceso a la Tabla employees de HR, realiza algunos cambios en ella(INSERT, DELETE, UPDATE) pero no hace COMMIT; como SCOTT no validó sus cambios HR no puede ver más que la data como estaba antes de SCOTT tocarla; como SCOTT realizó cambios que están en Buffer, HR no puede hacer ningún tipo de modificación a los mismo campos no validados, solo puede ver la version anterior de ellos.*/

Link con un Archivo de ejercicios de práctica!!!

Fuente: Base de Datos Oracle 10g: Conceptos Fundamentales de SQL 1