Story Transcript
9 Manipulación de Datos
Copyright Oracle Corporation, 1998. All rights reserved.
Objetivos Al ón, deber ía ser Al completar completar esta esta lecci lección, debería ser capaz capaz de de hacer hacer lo lo siguiente: siguiente:
• • • • • 9-2
Describir cada sentencia DML Insertar filas nuevas en una tabla. Modificar filas de una tabla. Eliminar filas de una tabla. Explicar el control de transacciones y su importancia. Copyright Oracle Corporation, 1998. All rights reserved.
El Lenguaje de Manipulación de Datos •• Una Una sentencia sentencia DML DML se se ejecuta ejecuta cuando: cuando: –– Añade Añade nuevos nuevos registros registros aa una una tabla tabla –– Modifica Modifica registros registros existentes existentes –– Elimina Elimina registros registros existentes existentes
•• Una Una transacción transacción consiste consiste en en una una colección colección de de sentencias sentencias DML DML que que forman forman una una unidad unidad lógica lógica de de trabajo. trabajo.
9-3
Copyright Oracle Corporation, 1998. All rights reserved.
Añadir un Nuevo Registro a una Tabla 50 DEVELOPMENT DETROIT
Nuevo Registro DEPT DEPTNO -----10 20 30 40
DNAME ---------ACCOUNTING RESEARCH SALES OPERATIONS
LOC -------NEW YORK DALLAS CHICAGO BOSTON
“…insertar un nuevo registro en la tabla DEPT...”
DEPT DEPTNO -----10 20 30 40
DNAME ---------ACCOUNTING RESEARCH SALES OPERATIONS
LOC -------NEW YORK DALLAS CHICAGO BOSTON
50 DEVELOPMENT DETROIT 9-4
Copyright Oracle Corporation, 1998. All rights reserved.
La Sentencia INSERT •• Añadimos Añadimos nuevos nuevos registros registros aa una una tabla, tabla, por por medio medio de de la la sentencia sentencia INSERT. INSERT. INSERT INSERT INTO INTO VALUES VALUES
table table [(column [(column [, [, column...])] column...])] (value (value [, [, value...]); value...]);
•• Mediante Mediante esta esta sintaxis, sintaxis, sólo sólo se se inserta inserta un un registro registro al al mismo mismo tiempo. tiempo.
9-5
Copyright Oracle Corporation, 1998. All rights reserved.
Inserción de Nuevos Registros •
Insertar una nueva fila conteniendo los valores para cada columna.
•
Opcionalmente, se pueden listar las columnas en la cláusula INSERT.
SQL> INSERT INTO 2 VALUES 1 row created.
dept (deptno, dname, loc) (50, 'DEVELOPMENT', 'DETROIT');
•
Colocar los valores en el orden que las columnas tienen en la tabla por defecto.
•
Encerrar los valores de datos de tipo carácter y fecha entre comillas simples.
9-6
Copyright Oracle Corporation, 1998. All rights reserved.
Inserción de Registros con Valores Nulos •
Método Implícito: Omitir la columna en la lista.
SQL> INSERT INTO 2 VALUES 1 row created.
dept (deptno, dname ) (60, 'MIS');
Método Explícito: Especificar la palabra clave NULL o el string vacío ('') en la lista de VALUES. SQL> INSERT INTO 2 VALUES 1 row created.
9-7
dept (70, 'FINANCE', NULL);
Copyright Oracle Corporation, 1998. All rights reserved.
Inserción de Valores Especiales La ón SYSDATE La funci función SYSDATE registra registra la la fecha fecha yy hora hora actual. actual. SQL> INSERT INTO 2 3 4 VALUES 5 6 1 row created.
9-8
emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) (7196, 'GREEN', 'SALESMAN', 7782, SYSDATE, 2000, NULL, 10);
Copyright Oracle Corporation, 1998. All rights reserved.
Insertando Valores de Fecha Específicos •• Añadimos Añadimos un un nuevo nuevo empleado: empleado: SQL> INSERT INTO 2 VALUES 3 4 1 row created.
emp (2296,'AROMANO','SALESMAN',7782, TO_DATE('FEB 3,97', 'MON DD, YY'), 1300, NULL, 10);
•• Verificamos Verificamos la la inserción: inserción: EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ------- -------- ---- --------- ---- ----- ----2296 AROMANO SALESMAN 7782 03-FEB-97 1300 10
9-9
Copyright Oracle Corporation, 1998. All rights reserved.
Inserción de Valores por medio de Variables de Sustitución Creamos Creamos un un script script interactivo interactivo usando usando par ámetros de ón de parámetros de sustituci sustitución de SQL*Plus: SQL*Plus: SQL> INSERT INTO 2 VALUES 3
dept (deptno, dname, loc) (&department_id, '&department_name', '&location');
Enter value for department_id: 80 Enter value for department_name: EDUCATION Enter value for location: ATLANTA 1 row created.
9-10
Copyright Oracle Corporation, 1998. All rights reserved.
Creación de un Script con PROMPTS Específicos •• ACCEPT ACCEPT almacena almacena el el valor valor en en una una var. var. •• PROMPT PROMPT visualiza visualiza el el texto texto especificado. especificado. ACCEPT ACCEPT ACCEPT INSERT INTO VALUES
9-11
department_id PROMPT 'Please enter the department number:' department_name PROMPT 'Please enter the department name:' location PROMPT 'Please enter the location:' dept (deptno, dname, loc) (&department_id, '&department_name', &location); Copyright Oracle Corporation, 1998. All rights reserved.
Copiando Registros de Otra Tabla •
Escribir el comando INSERT con una subconsulta (subquery).
SQL> INSERT INTO managers(id, name, salary, hiredate) 2 SELECT empno, ename, sal, hiredate 3 FROM emp 4 WHERE job = 'MANAGER'; 3 rows created.
• •
9-12
No usar la cláusula VALUES. Observar que coincida el número de columnas en la cláusula INSERT con las de la subconsulta. Copyright Oracle Corporation, 1998. All rights reserved.
Modificación de Datos en una Tabla EMP EMPNO ENAME 7839 7698 7782 7566 ...
KING BLAKE CLARK JONES
JOB
...
DEPTNO
PRESIDENT MANAGER MANAGER MANAGER
10 30 10 20
“…Modificación de un registro en la Tabla EMP…”
EMP EMPNO ENAME 7839 7698 7782 7566 ... 9-13
KING BLAKE CLARK JONES
JOB PRESIDENT MANAGER MANAGER MANAGER
Copyright Oracle Corporation, 1998. All rights reserved.
...
DEPTNO 10 30 20 10 20
La Sentencia UPDATE •• Modificamos Modificamos registros registros existentes existentes con con la la sentencia sentencia UPDATE UPDATE UPDATE UPDATE SET SET [WHERE [WHERE
table table column column == value value [, [, column column == value] value] condition]; condition];
•• Puede Puede afectar afectar aa más más de de un un registro registro al al mismo mismo tiempo. tiempo.
9-14
Copyright Oracle Corporation, 1998. All rights reserved.
Modificación de Registros en una Tabla •• Los Los registros registros aa modificar modificar se se especifican especifican por por medio medio de de la la cláusula cláusula WHERE WHERE SQL> UPDATE emp 2 SET deptno = 20 3 WHERE empno = 7782; 1 row updated.
•• Si Si se se omite omite la la cláusula cláusula WHERE WHERE se se modificarían modificarían todos todos los los registros registros de de la la tabla. tabla. SQL> SQL> UPDATE UPDATE employee employee 22 SET deptno SET deptno == 20; 20; 14 14 rows rows updated. updated. 9-15
Copyright Oracle Corporation, 1998. All rights reserved.
Proceso de Modificación con Subconsultas Multi-Columna Modificar Modificar el el oficio oficio yy departamento departamento del del empleado empleado 7698, 7698, con con los los valores valores correspondientes correspondientes actualmente actualmente al al empleado empleado 7499: 7499: SQL> UPDATE emp 2 SET (job, deptno) = 3 (SELECT job, deptno 4 FROM emp 5 WHERE empno = 7499) 6 WHERE empno = 7698; 1 row updated.
9-16
Copyright Oracle Corporation, 1998. All rights reserved.
Modificación de Registros Basados en Otra Tabla Utilice Utilice subconsultas subconsultas en en sentencias sentencias UPDATE, UPDATE, para para modificar modificar registros registros de de una una tabla, tabla, basados basados en en valores valores de de otra otra tabla. tabla. SQL> SQL> UPDATE UPDATE employee employee 22 SET deptno SET deptno == (SELECT (SELECT 33 FROM FROM 44 WHERE WHERE 55 WHERE job == (SELECT WHERE job (SELECT 66 FROM FROM 77 WHERE WHERE 22 rows rows updated. updated.
9-17
deptno deptno emp emp empno empno == job job emp emp empno empno ==
Copyright Oracle Corporation, 1998. All rights reserved.
7788) 7788) 7788); 7788);
Modificación de Registros: Error de Integridad (Constraints) ee t t s iis == 55 x 55 x ee == 10; o 10; o n n 5555 o eerro m m ú ú nn o tto UPDATE UPDATE emp emp n n ee ** m m a a t ERROR at line 1: t rr ERROR at line 1: a a pp ORA-02291: constraint ORA-02291: integrity integrity constraint (USR.EMP_DEPTNO_FK) (USR.EMP_DEPTNO_FK) e e D D violated violated -llparent parent key key not not found found EE SQL> SQL> 22 33
9-18
UPDATE UPDATE SET SET WHERE WHERE
emp emp deptno deptno deptno deptno
Copyright Oracle Corporation, 1998. All rights reserved.
Eliminación de Registros de una Tabla DEPT DEPTNO -----10 20 30 40 50 60 ...
9-19
DNAME ---------ACCOUNTING RESEARCH SALES OPERATIONS
LOC -------NEW YORK DALLAS CHICAGO BOSTON DEVELOPMENT DETROIT MIS
“…Borramos un registro de la tabla DEPT…” DEPT DEPTNO -----10 20 30 40 60 ...
DNAME ---------ACCOUNTING RESEARCH SALES OPERATIONS MIS
Copyright Oracle Corporation, 1998. All rights reserved.
LOC -------NEW YORK DALLAS CHICAGO BOSTON
La Sentencia DELETE Puede Puede eliminar eliminar registros registros existentes existentes de de una una tabla, tabla, por por medio medio de de la la sentencia sentencia DELETE. DELETE. DELETE DELETE [FROM] [FROM] [WHERE [WHERE
9-20
table table condition]; condition];
Copyright Oracle Corporation, 1998. All rights reserved.
Eliminación de Registros de una Tabla •• Los Los registros registros aa eliminar eliminar se se especifican especifican por por medio medio de de la la cláusula cláusula WHERE WHERE SQL> SQL> DELETE DELETE FROM FROM 22 WHERE WHERE 11 row row deleted. deleted.
department department dname dname == 'DEVELOPMENT'; 'DEVELOPMENT';
•• Si Si omite omite la la cláusula cláusula WHERE WHERE se se borrarán borrarán todos todos los los registros registros de de la la tabla tabla SQL> SQL> DELETE DELETE FROM FROM 44 rows rows deleted. deleted.
9-21
department; department;
Copyright Oracle Corporation, 1998. All rights reserved.
Eliminación de Registros Basados en Otra Tabla Utilice Utilice subconsultas subconsultas en en sentencias sentencias DELETE, DELETE, para para eliminar eliminar registros registros de de una una tabla, tabla, basados basados en en valores valores de de otra otra tabla. tabla. SQL> DELETE FROM 2 WHERE 3 4 5 6 rows deleted.
9-22
employee deptno = (SELECT FROM WHERE
deptno dept dname ='SALES');
Copyright Oracle Corporation, 1998. All rights reserved.
Eliminación de Registros: Error de Integridad (Constraints) ee aa n n iiee errnn t t nn xxtte SQL> dept o SQL> DELETE DELETE FROM FROM dept o cc e ee 22 WHERE deptno = 10; e e vve WHERE deptno = 10; u u q llaa q c o c rro oo t t s iis om m g g o e c rre aa c DELETE DELETE FROM FROM dept dept n n aadd u u ** rr uss a a n ERROR ERROR at at line line 1: 1: iin iaa,, u i m m aarconstraint i r i ORA-02292: (USR.EMP_DEPTNO_FK) l l ORA-02292: integrity integrity constraint (USR.EMP_DEPTNO_FK) e e m iim ee rrecord violated found violated --echild child record found r d d p p e ppuu laavvee bbllaa NNoo aa ccl a ttaa uunn oottrra eenn
9-23
Copyright Oracle Corporation, 1998. All rights reserved.
Transacciones en la Base de Datos Contienen Contienen una una de de las las siguientes siguientes sentencias: sentencias: •• Sentencias Sentencias DML DML que que deciden deciden un un cambio cambio consistente consistente sobre sobre los los datos datos •• Una Una sentencia sentencia DDL DDL •• Una Una sentencia sentencia DCL DCL
9-24
Copyright Oracle Corporation, 1998. All rights reserved.
Transacciones en la Base de Datos • •
Comienzan Comienzan cuando cuando se se ejecuta ejecuta el el primer primer comando comando SQL. SQL. Finalizan Finalizan con con uno uno de de los los siguientes siguientes eventos: eventos: –– COMMIT COMMIT o o ROLLBACK. ROLLBACK. –– ––
9-25
Ejecución Ejecución de de un un comando comando DDL DDL o o DCL DCL (commit (commit automático). automático). Errores, Errores, salida salida o o caída caída del del sistema. sistema. Copyright Oracle Corporation, 1998. All rights reserved.
Ventajas de COMMIT y ROLLBACK • •
Aseguran la consistencia de los datos.
•
Agrupan lógicamente tareas relacionadas entre sí.
9-26
Pueden visualizar los cambios sobre los datos antes de hacerlos permanentes.
Copyright Oracle Corporation, 1998. All rights reserved.
Control de Transacciones Transacci ón Transacción INSERT COMMIT
UPDATE
Savepoint A
INSERT
DELETE
Savepoint B
ROLLBACK to Savepoint B
ROLLBACK to Savepoint A
ROLLBACK 9-27
Copyright Oracle Corporation, 1998. All rights reserved.
Proceso Implícito de Transacciones •
Un commit automático ocurre bajo una de las siguientes circunstancias:
– Se emite un comando DDL, tal como CREATE.
– Se emite un comando DCL, tal como GRANT.
– Una salida normal desde SQL*Plus, sin realizar un COMMIT o ROLLBACK explícitamente.
•
9-28
Un rollback automático ocurre bajo una terminación anormal de SQL*Plus o una falla del sistema. Copyright Oracle Corporation, 1998. All rights reserved.
Estado de los Datos Antes de COMMIT o ROLLBACK •
El estado previo de los datos puede ser recuperado porque el afectado es el búfer de la base de datos.
•
El usuario actual puede revisar los resultados de sus operaciones DML usando la sentencia SELECT.
•
Otros usuarios no pueden ver los resultados de las sentencias DML ejecutadas por el usuario actual.
•
Las filas afectadas son bloqueadas (locked); otros usuarios no pueden cambiar los datos pertenecientes a esas filas. 9-29 Copyright Oracle Corporation, 1998. All rights reserved.
Estado de los Datos Después del COMMIT •
Los cambios en los datos son escritos en la base de datos.
•
Los datos anteriores se pierden definitivamente.
• •
Todos los usuarios pueden ver los resultados.
•
Se borran todos los savepoints.
9-30
Se liberan los bloqueos aplicados a las filas afectadas; esas filas están ahora disponibles para que otros usuarios las usen.
Copyright Oracle Corporation, 1998. All rights reserved.
Commit de Datos •• Realizar Realizar los los cambios: cambios: SQL> SQL> UPDATE UPDATE emp emp 22 SET deptno SET deptno == 10 10 33 WHERE empno WHERE empno == 7782; 7782; 11 row row updated. updated.
•• Hacer Hacer Commit Commit de de los los cambios: cambios: SQL> COMMIT; Commit complete.
9-31
Copyright Oracle Corporation, 1998. All rights reserved.
Estado de los Datos Después de Hacer ROLLBACK • • • •
Se descartan todos los cambios pendientes. Los cambios en los datos se deshacen. Se restaura el estado previo de los datos. Se levantan todos los bloqueos sobre las filas afectadas. SQL> DELETE FROM 14 rows deleted. SQL> ROLLBACK; Rollback complete.
9-32
employee;
Copyright Oracle Corporation, 1998. All rights reserved.
Hacer ROLLBACK Hasta un Punto de Salvaguarda (Marca) •
Crear una marca dentro de una transacción usando el comando SAVEPOINT (marca).
•
Hacer un rollback hacia la marca usando el comando ROLLBACK TO SAVEPOINT.
SQL> UPDATE... SQL> SAVEPOINT update_done; Savepoint created. SQL> INSERT... SQL> ROLLBACK TO update_done; Rollback complete. 9-33
Copyright Oracle Corporation, 1998. All rights reserved.
Rollback a Nivel de Sentencia •
Si una única sentencia DML falla durante su ejecución, entonces se hace rollback sobre esa sentencia solamente.
•
Oracle8 implementa una marca de seguridad o savepoint implícito.
• •
Todos los otros cambios se mantienen.
9-34
El usuario debería terminar explícitamente las transacciones usando una sentencia COMMIT o ROLLBACK. Copyright Oracle Corporation, 1998. All rights reserved.
Consistencia en Lectura •• La La consistencia consistencia en en lectura lectura garantiza garantiza una una vista vista consistente consistente de de los los datos datos en en cualquier cualquier circunstancia. circunstancia. •• Los Los cambios cambios realizados realizados por por otro otro usuario usuario no no crean crean conflictos conflictos con con los los cambios cambios realizados realizados por por otro otro usuario. usuario. •• Asegura Asegura que que sobre sobre los los mismos mismos datos: datos: –– Las Las lecturas lecturas no no esperan esperan aa las las
escrituras escrituras yy viceversa. viceversa. 9-35
Copyright Oracle Corporation, 1998. All rights reserved.
Implementación de la Consistencia en Lectura Bloques de Datos
update emp set sal = 2000 where ename = 'SCOTT'
Segmentos de Rollbck
Usuario A select * from emp
Imagen Consistente en Lectura
Usuario B 9-36
Copyright Oracle Corporation, 1998. All rights reserved.
Datos cambiados y no cambiados Antes de cambiar los datos “antiguos”
Bloqueos Bloqueos Bloqueos en en Oracle8: Oracle8: •• Previenen Previenen la la intereacción intereacción destructiva destructiva entre entre transacciones transacciones concurrentes concurrentes •• No No requieren requieren acciones acciones por por parte parte del del usuario usuario •• Automáticamente Automáticamente utilizan utilizan el el nivel nivel más más bajo bajo de de restricción restricción •• Se Se mantienen mantienen mientras mientras dura dura la la transacción transacción •• Tienen Tienen básicamente básicamente dos dos modos: modos: –– Exclusive Exclusive (exclusivos) (exclusivos) –– Shared Shared (compartidos) (compartidos) 9-37
Copyright Oracle Corporation, 1998. All rights reserved.
Resumen Comando
Descripción
INSERT
Agrega una fila nueva a la tabla.
UPDATE
Modifica filas de una tabla.
DELETE
Elimina filas de una tabla.
COMMIT
Hace permanentes todos los cambios pendientes.
SAVEPOINT
Permite hacer un rollback hasta la marca de savepoint.
ROLLBACK
Descarta todos los cambios pendientes de los datos.
9-38
Copyright Oracle Corporation, 1998. All rights reserved.
Visión General de la Práctica • • •
9-39
Insertar filas en tablas. Actualizar y eliminar filas en tablas. Controlar transacciones.
Copyright Oracle Corporation, 1998. All rights reserved.