SQL Capitulo 9 : Manipulación de Datos en SQL Developer

Sean bienvenidos a esta sección donde se revisa acerca de la manipulación de datos en SQL, recuerden estas sentencias estan siendo ejecutadas en SQL developer administrador de la Base de datos Oracle.

Objetivos de la publicación:
Al finalizar esta lección, debería estar capacitado para lo siguiente:

• Describir cada sentencia de lenguaje de manipulación de datos (DML)
• Insertar filas en una tabla
• Actualizar filas en una tabla
• Suprimir filas de una tabla
• Controlar transacciones

En esta lección aprenderá a utilizar las sentencias de lenguaje de manipulación de datos (DML) para insertar filas en una tabla, actualizar filas existentes en una tabla y suprimir filas existentes de una tabla. También aprenderá a controlar transacciones con las sentencias COMMIT, SAVEPOINT y ROLLBACK.

Adición de nuevas filas a una tabla
Sentencia INSERT

Lenguaje de Manipulación de Datos

Las sentencias DML se ejecutan al:

• Agregar nuevas filas a una tabla
• Modificar filas existentes en una tabla
• Eliminar filas existentes de una tabla
• Una transacción consta de una recopilación de sentencias DML que forman una unidad lógica de trabajo.

El lenguaje de manipulación de datos (DML) es una parte fundamentalmente de SQL. Para agregar, actualizar o suprimir los datos de la base de datos, ejecute una sentencia DML. La 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 su cuenta de ahorro a una cuenta corriente, la transacción puede constar de las siguientes tres acciones diferentes: reducir la cuenta de ahorro, aumentar la cuenta corriente y registrar la transacción en el diario de transacciones. El servidor de Oracle debe garantizar que se ejecuten las tres sentencias SQL para mantener el balance correcto de las cuentas. 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.

Nota :La mayoría de las sentencias DML de esta lección asumen que no se ha violado ninguna restricción de esta tabla. Las restricciones se tratarán más adelante. 

En SQL Developer, haga clic en el icono Run Script o pulse [F5] para ejecutar las sentencias DML. Los mensajes de comentarios se mostrarán en la página con separadores Script Output.

Adición de una Nueva Fila a una Tabla

El gráfico ilustra un nuevo departamento a la tabla DEPARTMENTS.

Sintaxis de las Sentencias INSERT

Agregar nuevas filas a una tabla mediante la sentencia INSERT:

Con esta sintaxis, sólo se inserta una fila cada vez.

Puede agregar nuevas filas a una tabla emitiendo la sentencia INSERT. 

En la sintaxis:

table es el nombre de la tabla.

column es el nombre de la columna de la tabla que se debe 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.

Inserción de Filas

Insertar una nueva fila que contenga los valores de cada columna.

Mostrar valores en el orden por defecto de las columnas de la tabla.

Opcionalmente, mostrar la lista de columnas en la cláusula INSERT.

Encerrar valores de caracteres y de fecha entre comillas simples.

Puesto que puede insertar una nueva fila que contenga los valores de cada columna, no es necesaria la lista de columnas en la cláusula INSERT. Sin embargo, si no utiliza la lista de columnas, los valores se deben mostrar según el orden por defecto de las columnas en la tabla y se debe proporcionar un valor para cada columna. 

DESCRIBE departments

Para mayor claridad, utilice la lista de columnas en la cláusula INSERT. Encierre los valores de caracteres y fecha entre comillas simples; sin embargo, no se recomienda encerrar valores numéricos entre comillas simples. 

Inserción de Filas con Valores Nulos

Método implícito: omitir la columna de la lista de columnas.

Método explícita: especificar la palabra clave NULL en la cláusula VALUES.

Asegúrese de que puede utilizar valores nulos en la columna de destino mediante la verificación del estado Null con el comando DESCRIBE.

El servidor de Oracle aplica automáticamente todos los tipos de dato, rangos de datos y restricciones de integridad de los datos. Las columnas que no se muestran explícitamente obtienen un valor nulo en la nueva fila. 

Los errores comunes que se producen durante la entrada del usuario se comprueban en el siguiente orden: 

• Valor obligatorio que falta para una columna NOT NULL
• Valor duplicado que viola cualquier restricción de clave única o primaria
• Cualquier valor que viole una restricción CHECK
• Mantenimiento de la integridad referencial para la restricción de clave ajena
• No coincidencias de tipo de dato o valores demasiado anchos para la columna

Nota: se recomienda el uso de la lista de columnas porque hace la sentencia INSERT más legible y fiable o menos proclive a errores.

Inserción de Valores Especiales

La función SYSDATE registra la fecha y hora actuales.

Puede utilizar funciones para introducir valores especiales en la tabla. 

En el ejemplo se registra información sobre el empleado Popp en la tabla EMPLOYEES. Proporciona la fecha y hora actuales en la columna HIRE_DATE. Utiliza la función SYSDATE que devuelve la fecha y hora actuales del servidor de base de datos. También puede utilizar la función CURRENT_DATE para obtener la fecha actual en la zona horaria de la sesión. Además puede utilizar la función USER al insertar filas en la tabla. La función USER registra el nombre de usuario actual.

Confirmación de Adiciones a la Tabla

SELECT employee_id, last_name, job_id, hire_date, commission_pct

FROM   employees

WHERE  employee_id = 113;

Inserción de Valores de Fecha y Hora Específicos

Agregar un nuevo empleado.

Verificar la adición.

El formato DD-MON-RR se suele utilizar para insertar el valor de fecha. Con el formato RR, el sistema proporciona el siglo correcto automáticamente.

También puede especificar el valor de fecha en formato DD-MON-YYYY. Se recomienda hacerlo así porque especifica de forma clara el siglo y no depende de la lógica de formato RR interna de la especificación del siglo correcto.

Si necesita introducir una fecha en un formato diferente al formato por defecto (por ejemplo, con otro siglo o con una hora concreta), debe utilizar la función TO_DATE.

En el ejemplo se registra información del empleado Raphealy en la tabla EMPLOYEES. Define la columna HIRE_DATE en el 3 de febrero de 1999. 

Creación de un Script 

Utilizar el carácter de sustitución & en una sentencia SQL para solicitar valores.

& es un marcador de posición del valor de la variable.

Puede guardar comandos con variables de sustitución en un archivo y ejecutar los comandos en el archivo. En el ejemplo se registra información sobre un departamento de la tabla DEPARTMENTS. 

Ejecute el script y se le solicitará una entrada para cada una de las variables de sustitución (&). Después de introducir un valor para la variable de sustitución, haga clic en el botón OK. Los valores que introduce el usuario son los que se sustituyen en la sentencia. Esto permite ejecutar el mismo script una y otra vez, pero proporcionar un juego de valores diferente cada vez que lo ejecute.

Copia de Filas de Otra Tabla

Escribir la sentencia INSERT con una subconsulta:

No utilizar la cláusula VALUES.

Hacer coincidir el número de columnas de la cláusula INSERT con el de la subconsulta.

Inserta todas las filas devueltas por la subconsulta en la tabla, sales_reps.

Puede utilizar la sentencia INSERT para agregar filas a la tabla en la que se derivan los valores de las tablas existentes. En el ejemplo, para que la sentencia INSERT INTO funcione, debe haber creado antes la tabla sales_reps mediante la sentencia CREATE TABLE. CREATE TABLE se trata en la lección titulada “Uso de Sentencias DDL para Crear y Gestionar Tablas”. 

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 debe rellenar.

subquery         es la subconsulta que devuelve filas a la tabla.

El número de columnas y sus tipos de dato de la lista de columnas de la cláusula INSERT deben coincidir con el número de valores y sus tipos de dato en la subconsulta. Se agregan cero o más filas en función del número de filas devueltas por la subconsulta. Para crear una copia de la filas de una tabla, utilice SELECT * en la subconsulta:

INSERT INTO copy_emp

    SELECT * 

    FROM   employees;

Cambio de datos en la tabla

Sentencia UPDATE

Cambio de Datos en la Tabla

La ilustra muestra el cambio de número de departamento para los empleados del departamento 60 al departamento 80.

Sintaxis de Sentencias UPDATE

Modificar los valores existentes en una tabla con la sentencia UPDATE:

Actualizar más de una fila cada vez (si es necesario).

Puede modificar los valores existentes en una tabla mediante la sentencia UPDATE.

En la sintaxis:

table es el nombre de la tabla.

column es el nombre de la columna de la tabla que se debe rellenar.

value es el valor o subconsulta correspondiente para la columna.

condition     identifica las filas que se deben actualizar y se compone de nombres de columna, expresiones, constantes, subconsultas y operadores de comparación.

Para confirmar la operación de actualización, consulte la tabla para visualizar las filas actualizadas. 

Para obtener más información, consulte la sección sobre “UPDATE” en Oracle Database SQL Language Reference (Referencia sobre Lenguaje SQL de Oracle Database).

Nota: en general, la primera columna de clave primaria se utiliza en la cláusula WHERE para identificar una única fila para la actualización. El uso de otras columnas puede provocar una actualización inesperada de varias filas. Por ejemplo, identificar una fila de la tabla EMPLOYEES por nombre es peligroso, ya que puede que más de un empleado tengan el mismo nombre. 

Actualización de Filas en una Tabla

Si se especifica la cláusula WHERE, se modifican los valores de una fila o varias filas específicas:

Si se omite la cláusula WHERE, se modifican los valores de todas las filas de la tabla:

Especificar SET column_name= NULL para actualizar un valor de columna a NULL.

La sentencia UPDATE modifica los valores de una fila o varias filas específicas si se especifica la cláusula WHERE. El ejemplo muestra la transferencia del empleado 113 (Popp) al departamento 50. 

Si omite la cláusula WHERE, se modifican todas las filas de la tabla. Examine las filas actualizadas en la tabla COPY_EMP.

   SELECT last_name, department_id

   FROM   copy_emp;

Por ejemplo, un empleado que era SA_REP ahora ha cambiado su puesto a IT_PROG. Por lo tanto, se debe actualizar su JOB_ID y el campo de comisiones se debe definir en NULL.

UPDATE employees

SET job_id = 'IT_PROG', commission_pct = NULL

WHERE employee_id = 114;

Nota: la tabla COPY_EMP tiene los mismos datos que la tabla EMPLOYEES.

Actualización de Dos Columnas con una Subconsulta

Actualizar el cargo y el salario del empleado 113 para que coincida con los del empleado 205.

Puede actualizar varias columnas en la cláusula SET de una sentencia UPDATE mediante la escritura de varias subconsultas. La sintaxis es la siguiente: 

 UPDATE table

 SET     column  = 

(SELECT column

                 FROM table

               WHERE condition) [ ,

                                         column  = 

              (SELECT column

               FROM table

               WHERE condition)]

 [WHERE  condition ] ;

El ejemplo  también se puede escribir del siguiente modo:

UPDATE employees 

SET (job_id, salary)  = (SELECT  job_id, salary 

                    FROM    employees 

                    WHERE   employee_id = 205)

WHERE    employee_id    =  113;

Actualización de Filas Basada en Otra Tabla

Utilizar subconsultas en las sentencias UPDATE para actualizar los valores de fila de una tabla según los valores de otra tabla:

Puede utilizar las subconsultas UPDATE para actualizar las filas de una tabla. El ejemplo de la diapositiva actualiza la tabla COPY_EMP según los valores de la tabla EMPLOYEES. Cambia el número de departamento de todos los empleados con el ID de cargo del empleado 200 al número de departamento actual del empleado 100.

Actualización de Filas Basada en Otra Tabla

Utilizar subconsultas en las sentencias UPDATE para actualizar los valores de fila de una tabla según los valores de otra tabla:

Puede utilizar las subconsultas UPDATE para actualizar las filas de una tabla. El ejemplo de la diapositiva actualiza la tabla COPY_EMP según los valores de la tabla EMPLOYEES. Cambia el número de departamento de todos los empleados con el ID de cargo del empleado 200 al número de departamento actual del empleado 100.

Eliminación de filas de una tabla:

Sentencia DELETE

Sentencia TRUNCATE

Eliminación de Filas de Tablas

El departamento de contratos se ha eliminado de la tabla DEPARTMENTS (siempre que no haya violado ninguna restricción en la tabla DEPARTMENTS), como se muestra en el gráfico.

Sentencia DELETE

Puede eliminar filas existentes de una tabla mediante la sentencia DELETE:

Puede eliminar filas existentes de una tabla mediante la sentencia DELETE.

En la sintaxis:

table es el nombre de la tabla.

condition    identifica las filas que se deben suprimir y se compone de nombres de columna, expresiones, constantes, subconsultas y operadores de comparación.

Nota: si no se suprime ninguna fila, se devuelve el mensaje “0 rows deleted” (en el separador Script Output de SQL Developer).

Para obtener más información, consulte la sección sobre “DELETE” en Oracle Database SQL Language Reference (Referencia sobre Lenguaje SQL de Oracle Database).

Supresión de Filas de Tablas

Se suprimen filas concretas si se especifica la cláusula WHERE:

Se suprimen todas las filas de la tabla si omite la cláusula WHERE:

Puede suprimir filas concretas mediante la especificación de la cláusula WHERE en la sentencia DELETE. En el primer ejemplo se suprime el departamento Accounting de la tabla DEPARTMENTS. Para confirmar la operación de supresión, visualice las filas suprimidas con la sentencia SELECT. 

    SELECT  *

    FROM    departments

    WHERE  department_name = 'Finance';

Si omite la cláusula WHERE, se suprimen todas las filas de la tabla. En el segundo ejemplo se suprimen todas las filas de la tabla COPY_EMP, porque no se ha especificado ninguna cláusula WHERE.

Ejemplo:

Se eliminan las filas identificadas en la cláusula WHERE.

DELETE FROM  employees WHERE employee_id = 114;

DELETE FROM  departments WHERE department_id IN (30, 40);

Supresión de Filas Basada en Otra Tabla

Utilizar subconsultas en las sentencias DELETE para eliminar filas de una tabla según los valores de otra tabla:

Puede utilizar subconsultas para suprimir filas de una tabla según los valores de otra tabla. En el ejemplo se suprimen todos los empleados de un departamento, donde el nombre del departamento consta de la cadena Public.

La subconsulta busca en la tabla DEPARTMENTS el número de departamento según el nombre de departamento que contenga la cadena Public. A continuación, la subconsulta introduce el número de departamento en la consulta principal, con lo que se suprimen las filas de datos de la tabla EMPLOYEES en base a este número de departamento.

Sentencia TRUNCATE

Elimina todas las filas de una tabla, dejando la tabla vacía y la estructura de la misma intacta.

Es una sentencia de lenguaje de definición de datos (DDL) en lugar de una sentencia DML; no se puede deshacer fácilmente.

Sintaxis:

Ejemplo:

Una forma más eficaz de vaciar una tabla es con la sentencia TRUNCATE. Puede utilizar la sentencia TRUNCATE para eliminar fácilmente todas las filas de una tabla o un cluster. La eliminación de filas con las sentencia TRUNCATE es más rápida que la eliminación con la sentencia DELETE por las siguientes razones:

La sentencia TRUNCATE es una sentencia de lenguaje de definición de datos (DDL) y no genera ninguna información de rollback. La información de rollback se trata más adelante en esta lección.

El truncamiento de una tabla no arranca los disparadores de supresión de la tabla. 

Si la tabla es el principal de una restricción de integridad referencial, no puede truncarla. Debe desactivar la restricción antes de emitir la sentencia TRUNCATE. La desactivación de restricciones se trata en la lección titulada “Uso de Sentencias DDL para Crear y Gestionar Tablas”.

Control de transacciones de base de datos mediante COMMIT, ROLLBACK y SAVEPOINT

Transacciones de Base de Datos

Una transacción de base de datos consiste en una de las siguientes opciones:

• Sentencias DML que constituyen un cambio consistente de los datos
• Una sentencia DDL
• Una sentencia de lenguaje de control de datos (DCL)

El servidor de Oracle garantiza la consistencia de los datos basada en transacciones. Las transacciones proporcionan más flexibilidad y control al cambiar los datos y garantizan la consistencia de los datos en caso de fallo de proceso de usuario o del sistema.

Las transacciones constan de sentencias DML que suponen un cambio en los datos. Por ejemplo, una transferencia de fondos entre dos cuentas debe incluir un débito en una cuenta y un crédito en otra por la misma cantidad. Ambas acciones deben ser correctas o incorrectas por igual; el crédito no se debe confirmar sin el débito.

Transacciones de la Base de Datos: Inicio y Fin

Empezar cuando se ejecute la primera sentencia SQL de DML.

Terminar con uno de los siguientes eventos:

Se emite una sentencia COMMIT o ROLLBACK.

• Se ejecuta una sentencia DDL o DCL (confirmación automática).
• El usuario sale de SQL Developer o SQL*Plus.
• El sistema falla.

¿Cuándo se inicia y termina una transacción de la base de datos? 

Una transacción empieza cuando se encuentra la primera sentencia DML y termina cuando se produce una de las siguientes condiciones:

• Se emite una sentencia COMMIT o ROLLBACK.
• Se emite una sentencia DDL, como CREATE.
• Se emite una sentencia DCL.
• El usuario sale de SQL Developer o SQL*Plus.
• Falla una máquina o el sistema.

Tras la finalización de una transacción, la siguiente sentencia SQL ejecutable inicia automáticamente la próxima transacción.

Se confirma automáticamente una nueva sentencia DDL o DCL y, por lo tanto, finaliza de forma implícita una transacción.

Ventajas de las Sentencias COMMIT y ROLLBACK

Con las sentencias COMMIT y ROLLBACK, puede:

• Garantizar la consistencia
• Visualizar una presentación preliminar de los cambios de los datos antes de hacerlos permanentes
• Agrupar componentes relacionados de forma lógica

Con las sentencias COMMIT y ROLLBACK, puede controlar cuándo hacer permanentes los cambios realizados a los datos.

Sentencias de Control de Transacciones Explícitas

Puede controlar la lógica de las transacciones mediante las sentencias COMMIT, SAVEPOINT y ROLLBACK.

Rollback de los Cambios de un Marcador

Crear un marcador en una transacción actual mediante la sentencia SAVEPOINT.

Realizar rollback en dicho marcador mediante la sentencia ROLLBACK TO SAVEPOINT.

Puede crear un marcador en la transacción actual mediante la sentencia SAVEPOINT, que divide la transacción en secciones más pequeñas. Puede descartar los cambios pendientes hasta dicho marcador con la sentencia ROLLBACK TO SAVEPOINT. 

Si crea un segundo punto de grabación con el mismo nombre que un punto de grabación anterior, éste se suprime.

Procesamiento de Transacciones Implícitas

Una transacción automática se produce en las siguientes circunstancias:

• Se emite una sentencia DDL.
• Se emite una sentencia DCL.
• Salida normal de SQL Developer o SQL*Plus, sin emitir explícitamente las sentencias COMMIT o ROLLBACK.

Un rollback automático se produce con la terminación anormal de SQL Developer o SQL*Plus o por un fallo del sistema.

Nota: en SQL*Plus, el comando AUTOCOMMIT se puede activar o desactivar. Si se define en ON, cada sentencia DML individual se confirma en cuanto se ejecuta. No puede realizar rollback de los cambios. Si se define en OFF, la sentencia COMMIT aún se puede emitir explícitamente. Además, la sentencia COMMIT se emite al emitir una sentencia DDL o al salir de SQL*Plus. El comando SET AUTOCOMMIT ON/OFF se salta en SQL Developer. DML se confirma en una salida normal de SQL Developer sólo si tiene activada la preferencia Autocommit. Para activar Autocommit, realice los siguientes pasos:

En el menú Tools, seleccione Preferences. En el cuadro de diálogo Preferences, amplíe Database y seleccione Worksheet Parameters.

En el panel de la derecha, seleccione la opción “Autocommit in SQL Worksheet”. Haga clic en OK.

Fallos del Sistema

Cuando se interrumpe una transacción debido a un fallo del sistema, se realiza un rollback automático de toda la transacción. Esto impide que el error provoque cambios no deseados en los datos y que devuelva las tablas a su estado en el momento de la última confirmación. De esta forma, el servidor de Oracle protege la integridad de las tablas.

En SQL Developer, la salida normal de la sesión se realiza seleccionando Exit en el menú File. Con SQL*Plus, la salida normal se realiza introduciendo el comando EXIT en el símbolo del sistema. Si se cierra la ventana, se interpreta como una salida anormal.

Estado de los Datos antes de COMMIT o ROLLBACK

• Se puede recuperar el estado anterior de los datos.
• El usuario actual puede revisar los resultados de las operaciones DML mediante la sentencia SELECT.
• Los demás usuarios no pueden ver los resultados de las sentencias DML del usuario actual.
• Las filas afectadas están bloqueadas; otros usuarios no pueden cambiar los datos de las filas afectadas.

Todos los cambios de datos realizados durante la transacción son temporales hasta que se confirma la transacción.

El estado de los datos antes de emitir las sentencias COMMIT o ROLLBACK se describe a continuación:

• Las operaciones de manipulación de datos afectan principalmente al buffer de la base de datos y, 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 mediante la consulta de las tablas.
• Los demás usuarios no pueden ver los resultados de las operaciones de manipulación realizadas por el usuario actual. El servidor de Oracle establece la consistencia de lectura para garantizar que todos los usuarios ven los datos tal y como estaban en el momento de la última confirmación.
• Las filas afectadas están bloqueadas; otros usuarios no pueden cambiar los datos de las filas afectadas.

Estado de los Datos después de COMMIT

• Los cambios de datos se guardan en la base de datos.
• Se sobrescribe el estado anterior de los datos.
• Todos los usuarios pueden ver los resultados.
• Los bloqueos de las filas afectadas se liberan y dichas filas quedan disponibles para que las manipulen otros usuarios.
• Se borran todos los puntos de grabación.

Para convertir todos los cambios pendientes en permanentes, utilice la sentencia COMMIT. A continuación, se muestra lo que ocurre después de ejecutar una sentencia COMMIT:

• Los cambios de 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.
• Los bloqueos de las filas afectadas se liberan y las filas quedan ahora disponibles para que otros usuarios realicen nuevos cambios en los datos.
• Se borran todos los puntos de grabación.

Confirmación de Datos

Realice estos cambios:

Confirme los cambios:

En el ejemplo, se suprime una fila de la tabla EMPLOYEES y se inserta una nueva en la tabla DEPARTMENTS. Los cambios se guardan emitiendo la sentencia COMMIT.

Ejemplo:

Elimine los departamentos 290 y 300 de la tabla DEPARTMENTS y actualice una fila en la tabla EMPLOYEES. Guarde el cambio de datos.

   DELETE FROM departments

   WHERE   department_id IN (290, 300);

    

   UPDATE employees

     SET    department_id = 80

     WHERE employee_id = 206;

  COMMIT;

Estado de los Datos después de ROLLBACK

Desechar todos los cambios pendientes mediante la sentencia ROLLBACK:

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

Estado de los Datos después de ROLLBACK: Ejemplo

Al intentar eliminar un registro de la tabla TEST, puede borrar accidentalmente la tabla. Puede corregir el error, volver a emitir la sentencia correcta y hacer permanentes los cambios de datos.

Rollback a Nivel de Sentencias

• Si falla una sentencia DML durante la ejecución, sólo se realiza un rollback de dicha sentencia.
• El servidor de Oracle implanta un punto de grabación implícito.
• Los demás cambios se retienen.
• El usuario debe terminar las transacciones explícitamente con la ejecución de una sentencia COMMIT o ROLLBACK.

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, su efecto se deshace mediante un rollback a nivel de sentencia, pero los cambios realizados por las sentencias DML anteriores en la transacción no se desechan. El usuario los puede omitir o realizar un rollback explícito.

El servidor de Oracle emite una confirmación implícita antes y después de cualquier sentencia DDL. Por lo tanto, si la sentencia DDL no se ejecuta correctamente, no podrá realizar rollback de la sentencia anterior porque el servidor ha emitido una confirmación.

El usuario debe terminar las transacciones explícitamente con la ejecución de una sentencia COMMIT o ROLLBACK.

Consistencia de lectura

• La consistencia de lectura garantiza una vista consistente de los datos en todo momento.
• Los cambios realizados por un usuario no entran en conflicto con los cambios realizados por otro usuario.
• La consistencia de lectura garantiza que en los mismos datos:
• Los lectores no esperen los escritores.
• Los escritores no esperen a los lectores.
• Los escritores esperen a los escritores.

Los usuarios de la base de datos pueden acceder a la base de datos de dos formas:

• Operaciones de lectura (sentencia SELECT)
• Operaciones de escritura (sentencias INSERT, UPDATE, DELETE)

La consistencia de lectura es necesaria para lo siguiente:

• Que el lector y el escritor de la base de datos tengan garantizada una vista consistente de los datos.
• Que los lectores no visualicen datos en proceso de cambio.
• Que los escritores tengan garantizado que los cambios en la base de datos se realizarán de forma consistente.
• Los cambios realizados por un escritor no entran en conflicto con los cambios realizados por otro escritor.

El objetivo de la consistencia es garantizar que todos los usuarios vean los datos como existían en el momento de la confirmación, antes de iniciar una operación DML.

Nota: el mismo usuario se puede conectar a diferentes sesiones. Cada sesión mantiene la consistencia de lectura tal y como se describe anteriormente, incluso si son los mismos usuarios.

Implementación de Consistencia de Lectura

La consistencia de lectura es una implantación automática. Mantiene una copia parcial de la base de datos en los segmentos de deshacer. La imagen de lectura consistente se crea a partir de los datos confirmados de la tabla y de los datos antiguos que se van a cambiar y que aún no se han confirmado del segmento de deshacer. 

Al realizar una operación de inserción, actualización o supresión en la base de datos, el servidor de Oracle realiza una copia de los datos antes de cambiarlos y los escribe en un segmento de deshacer.

Todos los lectores, excepto el que haya emitido el cambio, seguirán viendo la base de datos como existía antes de que comenzaran los cambios; verán una “instantánea” de los datos del segmento de deshacer.

Antes de confirmar los cambios en la base de datos, sólo el usuario que modifica los datos ve la base de datos con modificaciones. Todos los demás verán la instantánea en el segmento de deshacer. Esto garantiza que los lectores de los datos lean datos consistentes en los que no se esté realizando actualmente ningún cambio.

Al confirmar una sentencia DML, el cambio realizado en la base de datos se hace visible para todos aquéllos que emiten una sentencia SELECT después de realizar la confirmación. El espacio ocupado por los datos antiguos en el archivo de segmentos de deshacer se libera para volver a utilizarlo.

Si se realiza un rollback de la transacción, los cambios se deshacen:

• La versión original anterior de los datos del segmento de deshacer se vuelve a escribir en la tabla.
• Todos los usuarios ven la base de datos como existía antes de comenzar la transacción.

Cláusula FOR UPDATE en una sentencia SELECT

Bloquea las filas de la tabla EMPLOYEES en las que job_id sea SA_REP.

El bloqueo sólo se libera si emite ROLLBACK o COMMIT.

Si la sentencia SELECT intenta bloquear una fila bloqueada por otro usuario, la base de datos espera a que la fila esté disponible y, a continuación, devuelve los resultados de la sentencia SELECT.

Cuando se emite una sentencia SELECT en la base de datos para consultar algunos registros, no se coloca ningún bloqueo en las filas seleccionadas. En general, se debe a que el número de registros bloqueados en un momento determinado se mantiene (por defecto) en el mínimo absoluto: sólo se bloquean aquellos registros que se han cambiado, pero que aún no se han confirmado. Aún así, otros usuarios podrán leer dichos registros tal y como aparecían antes del cambio (la “imagen anterior” de los datos). Hay ocasiones, sin embargo, en las que puede que desee bloquear un juego de registros incluso antes de cambiarlos en el programa. Oracle ofrece la cláusula FOR UPDATE de la sentencia SELECT para realizar este bloqueo. 

Cuando emite una sentencia SELECT...FOR UPDATE, el sistema de gestión de bases de datos relacionales (RDBMS) obtiene automáticamente los bloqueos a nivel de fila exclusivos de todas las filas identificadas por la sentencia SELECT, reteniendo así los registros “sólo para sus cambios”. Nadie más podrá cambiar ninguno de estos registros hasta que realice una operación ROLLBACK o COMMIT. 

Puede adjuntar la palabra clave opcional NOWAIT a la cláusula FOR UPDATE para indicar al servidor de Oracle que no espere si otro usuario ha bloqueado la tabla. En este caso, el control volverá inmediatamente al programa o al entorno de SQL Developer para que pueda realizar otro trabajo o simplemente esperar un período de tiempo antes de volver a intentarlo. Sin la cláusula NOWAIT, el proceso se bloqueará hasta que la tabla esté disponible, cuando otro usuario libere los bloqueos a través de la emisión un comando COMMIT o ROLLBACK.

Cláusula FOR UPDATE: Ejemplos

Puede utilizar la cláusula FOR UPDATE en una sentencia SELECT en varias tablas. 

Las filas de las tablas EMPLOYEES y DEPARTMENTS están bloqueadas.

Utilizar FOR UPDATE OF column_name para cualificar la columna que desea cambiar, de modo que sólo se bloqueen las filas de esa tabla específica.

En el ejemplo, la sentencia bloquea las filas de la tabla EMPLOYEES con JOB_ID definido en ST_CLERK y LOCATION_ID definido en 1500 y bloquea las filas de la tabla DEPARTMENTS con los departamentos de LOCATION_ID definidos en 1500.

Puede utilizar FOR UPDATE OF column_name para cualificar la columna que desea cambiar. La lista OF de la cláusula FOR UPDATE no limita el cambio sólo a aquellas columnas de las filas seleccionadas. Aún se mantienen los bloqueos en todas las filas; si simplemente declara FOR UPDATE en la consulta y no incluye una o más columnas después de la palabra clave OF, la base de datos bloqueará todas las filas identificadas en todas las tablas incluidas en la cláusula FROM. 

La siguiente sentencia sólo bloquea las filas de la tabla EMPLOYEES con ST_CLERK ubicado en LOCATION_ID 1500. No se bloquea ninguna fila en la tabla DEPARTMENTS:

SELECT e.employee_id, e.salary, e.commission_pct 

FROM employees e JOIN departments d 

USING (department_id) 

WHERE job_id = 'ST_CLERK' AND location_id = 1500 

FOR UPDATE OF e.salary 

ORDER BY e.employee_id; 

En el siguiente ejemplo, se indica a la base de datos que espere cinco segundos hasta que la fila esté disponible y, a continuación, se devuelve el control al usuario.

SELECT employee_id, salary, commission_pct, job_id

FROM employees  

WHERE job_id = 'SA_REP'

FOR UPDATE WAIT 5

ORDER BY employee_id;

Resumen

En esta lección debe haber aprendido a utilizar las siguientes sentencias:

En esta lección, debe haber aprendido a manipular datos en Oracle Database mediante las sentencias INSERT, UPDATE, DELETE y TRUNCATE, así como a controlar los cambios de datos mediante las sentencias COMMIT, SAVEPOINT y ROLLBACK. También ha aprendido a utilizar la cláusula FOR UPDATE de la sentencia SELECT para bloquear filas sólo para sus cambios.

Recuerde que el servidor de Oracle garantiza una vista consistente de los datos en todo momento.