DML (lenguaje de manipulación de datos)
Inserción de datos
La adición de datos a una tabla se realiza mediante la instrucción INSERT. Su sintaxis fundamental es:
INSERT INTO tabla [(listaDeCampos)]
VALUES (valor1 [,valor2 ...])
La tabla representa la tabla a la que queremos añadir el registro y los valores que siguen a VALUES son los valores que damos a los distintos campos del registro. Si no se especifica la lista de campos, la lista de valores debe seguir el orden de las columnas según fueron creados (es el orden de columnas según las devuelve el comando DESCRIBE).
La lista de campos a rellenar se indica si no queremos rellenar todos los campos. Los campos no rellenados explícitamente con la orden INSERT, se rellenan con su valor por defecto (DEFAULT) o bien con NULL si no se indicó valor alguno. Si algún campo tiene restricción de tipo NOT NULL, ocurrirá un error si no rellenamos el campo con algún valor.
Por ejemplo, supongamos que tenemos una tabla de clientes cuyos campos son: dni, nombre, apellido1, apellido2, localidad y dirección; supongamos que ese es el orden de creación de los campos de esa tabla y que la localidad tiene como valor por defecto Palencia y la dirección no tiene valor por defecto. En ese caso estas dos instrucciones son equivalentes:
INSERT INTO clientes
VALUES('11111111','Pedro','Gutiérrez', 'Crespo',DEFAULT,NULL);
INSERT INTO clientes(dni,nombre,apellido1,apellido2)
VALUES('11111111','Pedro','Gutiérrez', 'Crespo');
Son equivalentes puesto que en la segunda instrucción los campos no indicados se rellenan con su valor por defecto y la dirección no tiene valor por defecto. La palabra DEFAULT fuerza a utilizar ese valor por defecto. El uso de los distintos tipos de datos debe de cumplir los requisitos ya comentados en temas anteriores.
Relleno de registros a partir de filas de una consulta
Hay un tipo de consulta, llamada de adición de datos, que permite rellenar datos de una tabla copiando el resultado de una consulta. Ese relleno se basa en una consulta SELECT que poseerá los datos a añadir. Lógicamente el orden de esos campos debe de coincidir con la lista de campos indicada en la instrucción INDEX. Sintaxis:
INSERT INTO tabla (campo1, campo2,...)
SELECT campoCompatibleCampo1, campoCompatibleCampo2,...
FROM tabla(s)
[...otras cláusulas del SELECT...]
Ejemplo:
INSERT INTO clientes2004 (dni, nombre, localidad, direccion)
SELECT dni, nombre, localidad, direccion
FROM clientes
WHERE problemas=0;
Actualización de registros
La modificación de los datos de los registros lo implementa la instrucción UPDATE. Sintaxis:
UPDATE tabla
SET columna1=valor1 [,columna2=valor2...]
[WHERE condición]
Se modifican las columnas indicadas en el apartado SET con los valores indicados. La cláusula WHERE permite especificar qué registros serán modificados.Ejemplos:
UPDATE clientes SET provincia='Ourense'
WHERE provincia='Orense';
UPDATE productos SET precio=precio*1.16;
El primer dato actualiza la provincia de los clientes de Orense para que aparezca como Ourense. El segundo UPDATE incrementa los precios en un 16%. La expresión para el valor puede ser todo lo compleja que se desee:
UPDATE partidos SET fecha= NEXT_DAY(SYSDATE,'Martes')
WHERE fecha=SYSDATE;
Incluso se pueden utilizar subconsultas:
UPDATE empleados
SET puesto_trabajo=(SELECT puesto_trabajo
FROM empleados
WHERE id_empleado=12)
WHERE seccion=23;
Esta consulta coloca a todos los empleados de la sección 23 el mismo puesto de trabajo que el empleado número 12. Este tipo de actualizaciones sólo son válidas si el subselect devuelve un único valor, que además debe de ser compatible con la columna que se actualiza. Hay que tener en cuenta que las actualizaciones no pueden saltarse las reglas de integridad que posean las tablas.
Borrado de registros
Se realiza mediante la instrucción DELETE:
DELETE [FROM] tabla
[WHERE condición]
Es más sencilla que el resto, elimina los registros de la tabla que cumplan la condición indicada. Ejemplos:
DELETE FROM empleados
WHERE seccion=23;
DELETE FROM empleados
WHERE id_empleado IN (SELECT id_empleado FROM errores_graves);
Hay que tener en cuenta que el borrado de un registro no puede provocar fallos de integridad y que la opción de integridad ON DELETE CASCADE, hace que no sólo se borren los registros indicados en el SELECT, sino todos los relacionados.
Comando MERGE
Sin duda alguna el comando más poderoso del lenguaje de manipulación de Oracle es MERGE. Este comando sirve para actualizar los valores de los registros de una tabla a partir de valores de registros de otra tabla o consulta. Permite pues combinar los datos de dos tablas a fin de actualizar la primera Supongamos que poseemos una tabla en la que queremos realizar una lista de localidades con su respectiva provincia.
Las localidades están ya rellenadas, nos faltan las provincias. Resulta que tenemos otra tabla llamada clientes en la que tenemos datos de localidades y provincias, gracias a esta tabla podremos rellenar los datos que faltan en la otra. La situación se muestra en la ilustración siguiente.
La sintaxis del comando MERGE es:
MERGE INTO tabla alias
USING (instrucción SELECT) alias
ON (condición de unión)
WHEN MATCHED THEN
UPDATE SET col1=valor1 [col2=valor2]
WHEN NOT MATCHED THEN
INSERT (listaDeColumnas)
VALUES (listaDeValores)
MERGE compara los registros de ambas tablas según la condición indicada en el apartado ON. Compara cada registro de la tabla con cada registro del SELECT. Los apartados de la sintaxis significan lo siguiente:
- tabla es el nombre de la tabla que queremos modificar
- USING. En esa cláusula se indica una instrucción SELECT tan compleja como queramos que muestre una tabla que contenga los datos a partir de los cuales se modifica la tabla
- ON. permite indicar la condición que permite relacionar los registros de la tabla con los registros de la consulta SELECT
- WHEN MATCHED THEN. El UPDATE que sigue a esta parte se ejecuta cuandol a condición indicada en el apartado ON sea cierta para los dos registros actuales.
- WHEN NOT MATCHED THEN. El INSERT que sigue a esta parte se ejecuta para cada registro de la consulta SELECT que no pudo ser relacionado con ningún registro de la tabla.
Para el ejemplo descrito antes la instrucción MERGE sería:
MERGE INTO localidades l
USING (SELECT * FROM clientes) c
ON (l.localidad=clientes.localidad)
WHEN MATCHED THEN
UPDATE SET l.provincia=c.provincia
WHEN NOT MATCHED THEN
INSERT (localidad, provincia)
VALUES (c.localidad, c.provincia);
El resultado es la siguiente tabla de localidades:
Transacciones
Como se ha comentado anteriormente, una transacción está formada por una serie de instrucciones DML. Una transacción comienza con la primera instrucción DML que se ejecute y finaliza con alguna de estas circunstancias:
- Una operación COMMIT o ROLLBACK
- Una instrucción DDL (como ALTER TABLE por ejemplo)
- Una instrucción DCL (como GRANT)
- El usuario abandona la sesión
- Caída del sistema
Hay que tener en cuenta que cualquier instrucción DDL o DCL da lugar a un COMMIT implícito, es decir todas las instrucciones DML ejecutadas hasta ese instante pasan a ser definitivas.
COMMIT
La instrucción COMMIT hace que los cambios realizados por la transacción sean definitivos, irrevocables. Sólo se debe utilizar si estamos de acuerdo con los cambios, conviene asegurarse mucho antes de realizar el COMMIT ya que las instrucciones ejecutadas pueden afectar a miles de registros. Además el cierre correcto de la sesión da lugar a un COMMIT, aunque siempre conviene ejecutar explícitamente esta instrucción a fin de asegurarnos de lo que hacemos.
ROLLBACK
Esta instrucción regresa a la instrucción anterior al inicio de la transacción, normalmente el último COMMIT, la última instrucción DDL o DCL o al inicio de sesión. Anula definitivamente los cambios, por lo que conviene también asegurarse de esta operación. Un abandono de sesión incorrecto o un problema de comunicación o de caída del sistema dan lugar a un ROLLBACK implícito.
SAVEPOINT
Esta instrucción permite establecer un punto de ruptura. El problema de la combinación ROLLBACK/COMMIT es que un COMMIT acepta todo y un ROLLBACK anula todo. SAVEPOINT permite señalar un punto intermedio entre el inicio de la transacción y la situación actual. Su sintaxis es:
...instrucciones DML...
SAVEPOINT nombre
....instrucciones DML...
Para regresar a un punto de ruptura concreto se utiliza ROLLBACK TO SAVEPOINT seguido del nombre dado al punto de ruptura. Cuando se vuelve a un punto marcado, las instrucciones que siguieron a esa marca se anulan definitivamente. estado de los datos durante la transacción.
Si se inicia una transacción usando comandos DML hay que tener en cuenta que:
- Se puede volver a la instrucción anterior a la transacción cuando se desee
- Las instrucciones de consulta SELECT realizadas por el usuario que inició la transacción muestran los datos ya modificados por las instrucciones DML
- El resto de usuarios ven los datos tal cual estaban antes de la transacción, de hecho los registros afectados por la transacción aparecen bloqueados hasta que la transacción finalice. Esos usuarios no podrán modificar los valores de dichos registros.
Tras la transacción todos los usuarios ven los datos tal cual quedan tras el fin de transacción. Los bloqueos son liberados y los puntos de ruptura borrados.
