•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.
•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.
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.
–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.
Sintaxis para una Fila:
INSERT INTO table [(column [, column...])]
VALUES (value [, value...]);
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:
•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.
•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','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,
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.*/
_____________________________________________________________________________________
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:
•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.
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%';
Definición:
•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.
SET column = value [, column = value, ...]
[WHERE condition];
Definición:
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:
•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.
•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:
•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;
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 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.
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. |
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;
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.*/
_____________________________________________________________________________________
–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 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.
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.*/
_____________________________________________________________________________________
_____________________________________________________________________________________
