May 1, 2011

Clasificación o tipos de sentencias de base de datos en Oracle (DML, DDL,DCL,TCL, etc.)

http://www.felipedonoso.cl/
felipe@felipedonoso.cl
RC6016
1NF3RNU5


Este fué un trabajo que desarrollé para la Universidad Chilena en la cuál estoy estudiando, Escuela de Postgrado, Federico Santa María, quiero compartirlo con ustedes.

El  Instituto Nacional Estadounidense de Estándares o más conocido como ANSI  (American National Standards Institute) encargado de muchos de los patrones relacionados en la Industria de la informática, realizó copiosas modificaciones en las revisiones sobre el lenguaje SQL, en una de ellas definió que las sentencias se clasifican en diferentes grupos:
1.       DDL (Lenguaje de definición de datos)
2.       DML (Lenguaje de manipulación de datos)
3.       DCL  (lenguaje de control de datos)
4.       TCL  (lenguaje de control de transacciones)

Observación: Existe una quinta clasificación, DQL (lenguaje de consulta de datos) formalmente no fue definida por el ANSI sin embargo en mucha de la bibliografía relacionada a motores de base de datos se suele nombrar. A esta clasificación pertenece la sentencia SELECT.

Las anteriores sentencias son descritas por Oracle de la siguiente manera:

DDL


                Esta clasificación es conocida por Data Definition Language, y permite ingresar en la base de datos sentencias que definen o modifican la estructura de una base de datos o de un esquema. Este tipo de sentencias en Oracle poseen commit implícito, por lo cual luego de ejecutarlas no es necesario confirmar, es decir, los cambios quedan hechos permanentemente al contrario de las sentencias DML.
1.       CREATE: Esta sentencia permite la creación de objetos como tablas, índices, vistas, sinónimos o usuarios en la base de datos, entre otros objetos más. Ejemplo:
SQL> create user testing identified by testing;

User created.

SQL> create table alumnos (rut varchar2(100),nombre varchar2(100));

Table created.

2.       ALTER: Con esta sentencia podemos alterar objetos como índices, tablas, etc. Por ejemplo es posible reconstruir índices, agregar columnas a cierta tabla, cambiar contraseña de usuario, entre otras, por ejemplo:
SQL> alter user testing identified by q1w2e3r4t5; --cambia contraseña usuario testing

User altered.

SQL> alter index idx_alumno rebuild; --reconstruye el indice idx_alumno

Index altered.


3.       DROP: Con esta sentencia podemos eliminar objetos permanentemente, o en su defecto enviarlos hacia la RECYCLEBIN:
SQL> drop user testing cascade; -- Borrara al usuario TESTING

User dropped.

SQL> drop table alumnos; --Borra la table alumnos

Table dropped.


4.       RENAME: Con esta sentencia podemos cambiar el nombre de objetos de base de datos como tablas, sinónimos, vistas o sequencias, Ejemplo:

SQL> rename tabla_chao to tabla_hola;

Table renamed.


5.       TRUNCATE: Esta sentencia es del tipo DDL, trabaja de un modo distinto que la sentencia dml DELETE, de hecho pertenencen a grupos distintos. A diferencia de esta última, trúncate es una sentencia que borra TODA la información de una tabla, pero, que no genera información de UNDO (por lo que el cambio no puede deshacerse con rollback) y que naturalmente como las sentencias de este tipo posee un commit implícito. Ejemplo:
SQL> truncate table alumnos_old;
SQL> --recuperar la informacion de la table no sera possible con rollback

6.       COMMENT: A través de este comando se nos permite comentar o documentar objetos de base de datos como una tabla o columna, así de esta manera almacenamos documentación de dicho objeto.
SQL> comment on table alumnos is 'comentario de prueba';

SQL> select table_name, comments from user_tab_comments where table_name='ALUMNOS';

TABLE_NAME                     COMMENTS
------------------------------ --------------------------------------------------
ALUMNOS                        comentario de prueba



DML


                Esta clasificación es conocida como Data Manipulation Language, en esta agrupación recaen sentencias que requieren de confirmación para hacer definitivos los cambios hechos a través de este tipo de sentencias, es decir, no tienen commit implícito. Las sentencias de este tipo son:
1.       SELECT: Si bien en mucha bibliografía se deja esta sentencia fuera de este tipo de clasificación es considerada como una sentencia DML y nos permite visualizar la información contenida en una tabla, es decir, obtener filas a través de la consulta de las columnas de alguna tabla. Esta sentencia nos provee en pocas palabras la facilidad para utilizar el projection (selección de columnas), el  selection (selección de filas) y el Joining Ejemplo:
SQL> select empno,ename from emp;

     EMPNO ENAME
---------- ----------
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7844 TURNER
      7876 ADAMS

     EMPNO ENAME
---------- ----------
      7900 JAMES
      7902 FORD
      7934 MILLER

14 rows selected.

Observación: Algunos autores incluyen a la sentencia SELECT como parte de un clasificación anexa llamada DQL (Data Query Language) dónde solo estaría este comando que permiten consultar información de una base de datos.
2.       INSERT: Esta sentencia permite insertar nuevas filas en una tabla. Solamente puede insertar una fila por vez, ya que para realizar una inserción masiva de varias filas debe ocuparse una combinación del comando INSERT y SELECT como por ejemplo: insert into dept_80 (select * from employees where department_id = 80) entre otras alternativas. Esta sentencia utiliza la KEYWORD VALUES para indicar los nuevos valores a ser insertados en la tabla.
Ejemplo de un insert normal:

SQL> insert into alumnos (rut,nombre) values ('13243451-4','testing');

1 row created.


3.       UPDATE: permite actualizar el valor de las columnas de una tabla que pertenezcan a una fila en particular, si no a todas en caso de que no se utilice filtro alguno en la clausula WHERE. Ejemplo:

SQL> update alumnos set rut='46321654-7' where nombre='testing';

1 row updated.


4.       DELETE: Esta sentencia permite eliminar filas de una tabla en particular que cumplan con la condición indicada en la cláusula WHERE. En caso de que no se indique un filtro asociado la sentencia borrará todo el contenido de la tabla, de forma similar al TRUNCATE (a excepción de que DELETE si permite deshacer la sentencia con ROLLBACK, trúncate no permite esto). Ejemplo:

SQL> delete from emp_aux where EMPNO=7900;

1 row deleted.


5.       MERGE: Esta sentencia permite hacer dos cosas a la vez, actualizar  e insertar registros en una tabla. En el siguiente ejemplo podemos ver lo siguiente:

Crearemos una tabla de ejemplo llamada test1 a partir de una vista de sistema ya existente (all_objects) que muestra todos nuestros objetos.
SQL> CREATE TABLE test1 AS
SELECT object_id,status
FROM   user_objects
WHERE  1=2;  2    3    4

Table created.

Luego definiremos un Join entre la tabla test1 recién creada y la vista all_objects utilizando como filtro para el resultado del join la condición a.object_id = b.object_id. Si se cumplió la condición de anterior entonces se procederá a actualizar los valores de la primera tabla indicada después de la keyword INTO, es decir sobre la tabla test1. De lo contrario cuando no se cumpla la condición se procederá a insertar sobre la tabla test1. Un ejemplo de código del comando MERGE quedaría de la siguiente manera:
MERGE INTO test1 a
  USING all_objects b
    ON (a.object_id = b.object_id)
  WHEN MATCHED THEN
    UPDATE SET a.status = b.status
  WHEN NOT MATCHED THEN
    INSERT (object_id, status)
    VALUES (b.object_id, b.status);
  2    3    4    5    6    7    8


40780 rows merged.




DCL


                Esta clasificación es conocida como Data Control Language, y permite configurar controles de seguridad sobre objetos de la base de datos. Básicamente a este grupo de sentencias pertenecen los comandos GRANT y REVOKE, los que respectivamente otorgan y quitan privilegios de operaciones DDL o DML sobre ciertos objetos de la base de datos de algún esquema en particular. Este tipo de comandos no requieren confirmación del usuario (COMMIT).
1.       GRANT: Permite otorgar algún privilegio de sistema o de operación sobre algún objeto en particular, por ejemplo en el siguiente script le asignaremos al usuario TEST1 el privilegio de select  sobre la tabla DEPT del esquema SCOTT, por lo tanto ahora el usuario test1 podría seleccionar las filas de la tabla DEPT que le pertenece al usuario Scott, un ejemplo muy común es utilizar GRANT para otorgar los roles (conjunto de privilegios) CONNECT y RESOURCE para que el usuario pueda respectivamente conectarse y hacer operaciones normales en su esquema, otro ejemplo puede ser el siguiente:

SQL> conn test1/test1
Connected.


SQL> select dname from scott.dept;
ERROR at line 1:
ORA-00942: table or view does not exist

-- el usuario test1 no tiene privilegios para scott.dept


-- otorgamos el privilegio para consultar
-- los datos de scottt.dept para el usuario test1

SQL> conn scott/tiger
Connected.

SQL> grant select on dept  to test1;

Grant succeeded.


SQL> conn test1/test1
Connected.

-- el select debe funcionar
SQL> select dname from scott.dept;

DNAME
--------------
ACCOUNTING
RESEARCH
SALES
OPERATIONS



2.       REVOKE: Permite quitar algún privilegio de sistema o de operación sobre algún objeto en particular, hace lo contrario a la opción GRANT, ejemplo limitaremos al usuario test1 para que no vuelva a consultar datos de la tabla detp del esquema SCOTT:
SQL> revoke select on  scott.dept from test1;

Revoke succeeded.





TCL


                Esta clasificación es conocida como Transaction Control Language, y permite  confirmar y deshacer cambios efectuados por las sentencias DML. Las sentencias de este tipo son COMMIT, ROLLBACK y SAVEPOINT.
Observación: En algunas bibliografías de base de datos se suele agregar como parte de esta clasificación la sentencia: SET TRANSACTION, la cual es una sentencia que nos permite configurar el nivel de Aislamiento de una transacción.

1.       COMMIT: Permite configurar los cambios efectuados por una transacción o un comando DML. Recordemos los comandos DML una vez que se ejecutan satisfactoriamente no confirman los cambios, estos requieren de la ejecución de commit, para hacer que los cambios queden permanentes en la base de datos. Por ejemplo el usuario test1 insertara un nuevo registro en tabla1 sin confirmar, por lo tanto otra sesión del mismo usuario en otra  aplicación sqlplus debería ver la tabla vacía, ya que aún la primera sesión no ha confirmado los cambios.
La primera sesión

SQL> conn test1/test1
Connected.
SQL> create table tabla1(campo1 varchar2(100));

Table created.

SQL> insert into tabla1 values ('hola mundo');

1 row created.


La segunda sesión
[oracle@labbasedatos ~]$ sqlplus "test1/test1"

SQL*Plus: Release 10.2.0.3.0 - Production on Tue Apr 11 13:06:05 2011

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Release 10.2.0.3.0 - 64bit Production

SQL> select * from tabla1;

no rows selected
1 row created.


El usuario no pudo seleccionar ninguna fila debido a que no se ha confirmado cambio alguno desde la primera sesión. Si el primer usuario confirma:

SQL> commit;

Commit complete.


La segunda sesión podría visualizar la fila insertada:
SQL> select * from tabla1;

no rows selected

SQL> /

CAMPO1
--------------------------------------------------------------------------------
hola mundo

Observación: Recordar que el comando exit para salir de SQLPLUS tiene commit implícito. Por lo tanto al ejecutar exit; estaremos ejecutando también un commit.

2.    ROLLBACK: Permite deshacer un cambio al interior de una transacción o de un comando DML. Por ejemplo: El usuario test1 hará una nueva inserción, luego se arrepentirá del valor insertado deshaciendo el cambio con el comando rollback.
SQL> insert into tabla1 values ('hola alumnos federico santa maria');

1 row created.

SQL> select * from tabla1;

CAMPO1
--------------------------------------------------------------------------------
hola alumnos federico santa maria

--ahora necesito deshacer mi sentencia DML (el insert)
SQL> rollback;

Rollback complete.


--por lo tanto ahora la consulta debería mostrarnos la tabla vacia
SQL> select * from tabla1;

no rows selected
3.    SAVEPOINT: Nos permite establecer en una transacción un punto (o puntero si se prefiere) de referencia al cual volver en el caso de que ejecutemos un rollback parcial. Por ejemplo siguiendo con el punto expuesto en la explicación del comando ROLLBACK anterior:

SQL> insert into tabla1 values ('hola de nuevo alumnos del diplomado');

1 row created.

SQL> select * from tabla1;

CAMPO1
--------------------------------------------------------------------------------
hola de nuevo alumnos del diplomado

--aquí guardamos lo realizado hasta ahora en una referencia llamada paso1
SQL> savepoint paso1;

Savepoint created.

SQL> insert into tabla1 values ('hola de nuevo a los mejores alumnos de la universidad');

1 row created.

SQL> select * from tabla1;

CAMPO1
--------------------------------------------------------------------------------
hola de nuevo alumnos del diplomado
hola de nuevo a los mejores alumnos de la universidad

--aquí guardamos lo realizado hasta ahora en una referencia llamada paso2,
--recordar que tenemos dos filas hasta ahora.
SQL> savepoint paso2;

Savepoint created.

SQL> insert into tabla1 values ('hola de nuevo a todos los profesores del diplomado');

1 row created.

SQL> select * from tabla1;

CAMPO1
--------------------------------------------------------------------------------
hola de nuevo alumnos del diplomado
hola de nuevo a los mejores alumnos de la universidad
hola de nuevo a todos los profesores del diplomado

--aquí guardamos lo realizado hasta ahora en una referencia llamada paso3
--recordar que tenemos 3 registros insertados
SQL> savepoint paso3;

Savepoint created.





--ahora bien si por algún motivo necesitamos hacer un rollback parcial
--es decir por ejemplo dejar la tabla como estaba después de la primera inserción,
--debemos hacer rollback indicando el punto de referencia al que deseamos volver,
--es decir utilizando el savepoint PASO1:

SQL> rollback to savepoint paso1;

Rollback complete.

-- por lo tanto ahora deberíamos consultar la tabla,
-- y esta debe mostrar solo el primer registro que insertamos,
-- que es al fin y al cabo al punto de referencia al que volvimos.
SQL> select * from tabla1;

CAMPO1
--------------------------------------------------------------------------------
hola de nuevo alumnos del diplomado

--finalmente no olvidar hacer commit para guardar registro insertado
--o simplemente hacer exit (commit implicito):
SQL> commit;

Commit complete.

---------------------








No comments:

Post a Comment