Manipulando de Datos con SQL

Objetivos

Describir cada una de las sentencias DML (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, cuando se modifican filas existentes de una tabla y cuando se eliminan 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...]);

En la sintaxis

table es el nombre de la tabla.
column es el nombre de la columna de la tabla que se va a rellenar.
value es 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 datos NUMBER si 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.
Puedes ver la estructura de la tabla con el comando: DESCRIBE.

DESCRIBE departments;|| DESC departments;

Errores habituales con el INSERT

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 una restricción (Constraint) CHECK.
Incongruencia en el tipo de dato.
Valor demasiado ancho que no cabe en la columna.

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','moquete@magicplsql.com',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',
        'Lucas@magicplsql.com',
        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'||'@magicplsql.com');

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.

INSERT de Multiples Filas

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

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

En la Sintaxis

table es el nombre de la tabla.
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.
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];

En la Sintaxis

table es el nombre de la tabla.
column es el nombre de la columna de la tabla que se va a rellenar.
value es el valor correspondiente o la subconsulta para la columna.
condition identifica 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 PEONa 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];

En la Sintaxis:

table es el nombre de la tabla de la cual se quiere eliminar.
condition identifica 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 sentencia DDL (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;

En la Sintaxis

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 daremos ejemplos. Por otro lado 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 sentencia DDL.
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 sentencia COMMIT o ROLLBACK.
Se emite una sentencia DDL, como CREATE.
Se emite una sentencia DCL.
El usuario sale de la Consola: iSQL*Plus/SQL Developer/Toad etc.
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.

*Sentencia*	*Descripción*
*COMMIT*	Termina la transacción actual haciendo que todos los cambios pendientes sean permanentes.
*SAVEPOINT* name	Marca un punto de grabación dentro de la transacción actual.
ROLLBACK	Termina la transacción actual descartando todos los cambios pendientes.
*ROLLBACK TO SAVEPOINT* name	Hace rollback de la transacción actual hasta el punto de grabación especificado, con lo que se descarta cualquier cambio o punto de grabación que se haya creado después del punto de grabación hasta el que está haciendo rollback. Si omite la cláusula *TO SAVEPOINT, la sentencia ROLLBACK* hace rollback de toda la transacción. Como los puntos de grabación son lógicos, no hay forma de mostrar los puntos de grabación que ha creado.

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.

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 Server instituye 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 consultas SQL normales.
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 (sentencia SELECT).
Operaciones de escritura (sentencias INSERT, 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 valido sus cambios HR no puede ver mas 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.