Lenguaje de Definicion de Datos (DDL)

El DDL (Data Definition Language, o Data Description Language según autores), es la parte del SQL dedicada a la definición de la base de datos, consta de sentencias para definir la estructura de la base de datos, permite definir gran parte del nivel interno de la base de datos. Por este motivo estas sentencias serán utilizadas normalmente por el administrador de la base de datos.

Las principales funcionalidades de SQL como lenguaje de definición (DDL) son la creación, modificación y borrado de las tablas que componen la base de datos, así como de los índices, vistas, sinónimos, permisos, etc. que pudieran definirse sobre las mismas.

La definición de la estructura de la base de datos incluye tanto la creación inicial de los diferentes objetos que formarán la base de datos, como el mantenimiento de esa estructura. Las sentencias del DDL utilizan unos verbos que se repiten para los distintos objetos. Por ejemplo para crear un objeto nuevo el verbo será CREATE y a continuación el tipo de objeto a crear. CREATE DATABASE es la sentencia para crear una base de datos, CREATE TABLE nos permite crear una nueva tabla, CREATE INDEX crear un nuevo índice… Para eliminar un objeto utilizaremos el verbo DROP (DROP TABLE, DROP INDEX…) y para modificar algo de la definición de un objeto ya creado utilizamos el verbo ALTER (ALTER TABLE, ALTER INDEX…).
Los objetos que veremos en este tema son:

• Bases de datos
• Tablas
• Vistas
• Índices

 Ejemplos:

El comando CREATE

El CREATE es el comando que inicia al desarrollador, acá se cubrirán los usos más comunes.

Tablas

Cada diseñador de base de datos tendrá que crear una tabla alguna vez. El privilegio de sistema CREATE TABLE es necesario para ejecutar este comando. El DBA es el responsable de administrar dichos privilegios. La sintaxis para crear una tabla es la siguiente

CREATE TABLE [schema.]nombre_tabla

(

columna tipo_dato [default expression] [NOT NULL]

[column_constraint],

...

[constraint de tabla]

)

PCTFREE x PCTUSEDx

INITRANS x MAXTRANS x

TABLESPACE nombre_tablespace

STORAGE clauseCLUSTER cluster clause

ENABLE clause DISABLE clause

AS subconsulta

Ejemplos:

CREATE TABLE addresses (   ADRS_ID    NUMBER(6),   ACTIVE_DATE DATE,   BOX_NUMBER  NUMBER(6),   ADDRS_1    VARCHAR2(40),   ADDRS_2    VARCHAR2(40),   CITY        VARCHAR2(40),   STATE      VARCHAR2(2),   ZIP        VARCHAR2(10) );

Este fue un ejemplo sencillo sin utilizar varias capacidades. A continuación se muestra otro ejemplo

CREATE TABLE addresses (   ADRS_ID    NUMBER(6)    CONSTRAINT PK_ADRS PRIMARY KEY,   ACTIVE_DATE DATE        DEFAULT SYSDATE,   BOX_NUMBER  NUMBER(6)    DEFAULT NULL,   ADDRS_1    VARCHAR2(40) NOT NULL,   ADDRS_2    VARCHAR2(40) DEFAULT NULL,   CITY        VARCHAR2(40) DEFAULT NULL,   STATE      VARCHAR2(2)  DEFAULT 'NY',   ZIP        VARCHAR2(10) ) PCTFREE 5 PCTUSED 65 TABLESPACE adrs_data STORAGE (INITIAL 5140 NEXT 5140          MINEXTENTS 1 MAXEXTENTS 10          PCTINCREASE 10);

Índices

Estos son utilizados para incrementar el rendimiento de la base de datos. El comando es el CREATE INDEX y cada tabla puede tener varios índices.

CREATE INDEX [schema.]nombre_indice ON schema.table (nombre_columna ASC/DESC) CLUSTER schema.cluster INITRANS x MAXTRANS x PCTFREE X TABLESPACE nombre_tablespace STORAGE clause NOSORT

Ejemplos:

CREATE INDEX x_adrs_id ON ADDRESSES (ADRS_ID); CREATE INDEX x_city_state ON ADDRESSES (CITY,STATE)                              TABLESPACE application_indexes;

El orden de creación es importante a la hora de crear las consultas, para ellas el orden de aparición en el where debe ser el mismo del orden físico de las columnas.

Secuencias

Estas son utilizadas para la generación de llaves únicas consecutivas y enteras, de forma sencilla.

CREATE SEQUENCE [schema.]name INCREMENT BY x START WITH x [MAXVALUE x | NOMAXVALUE] [MINVALUE x | NOMINVALUE] [CYCLE | NOCYCLE] [CACHE x | NOCACHE] [ORDER | NOORDER]

Para crear una secuencia para adrs_id se puede hacer de la siguiente forma:

CREATE SEQUENCE adrs_seq INCREMENT BY 5 START WITH 100;

Para obtener el nuevo número de una secuencia se utiliza la seudo columna NEXTVAL, precedida del nombre de la secuencia, por ejemplo adrs_seq.nextval retornará 100 en la primer llamada y 105 en la segunda. Si desea obtener el valor actual de secuencia se utiliza la seudo columna CURRVAL, siempre y cuando haya accedido alguna vez al NEXTVAL.

Otros Objetos

El comando CREATE puede ser utilizado para crear otros objetos como los siguientes: CREATE xxx, donde xxx es uno de los siguientes:

CLUSTER CONTROLFILE DATABASE DATABASE LINK DATAFILE FUNCTION INDEX PACKAGE BODY PACKAGE PROCEDURE PROFILE ROLE ROLLBACK SEGMENT SCHEMA SEQUENCE SNAPSHOT SNAPSHOT LOG SYNONYM TABLE TABLESPACE TRIGGER USER VIEW

INSERTs, UPDATEs, y DELETEs

El comando INSERT es utilizado para agregar nuevas filas a la base de datos. Este puede ser utilizado para crear una fila a la vez utilizando la expresión VALUES o un conjunto de registros a la vez utilizando una subconsulta. La sintaxis es la siguiente:

INSERT INTO [schema.]nombre_tabla [(columna [,columna] ...)] {VALUES (valor [,valor] ...) | subconsulta};

Ejemplo:

INSERT INTO dept (deptno, name, loc)          VALUES (dept_seq.NEXTVAL,'CUSTOMER SERVICE', 'NEW YORK');

En este caso se insertan una fila en la tabla de departamentos. Acá se utiliza la secuencia dept_seq para obtener el siguiente valor de deptno.

INSERT INTO emp (empno, ename, deptno, hire_date,                  job, sal, mgr) SELECT emp_seq.NEXTVAL, new.ename, 30, SYSDATE,        'CUSTOMER REPRESENTATIVE', new.sal, 220 FROM candidates new WHERE new.accept = 'YES'   AND new.deptno = 30;

En este caso se insertarán todas las filas de la tabla de candidates que han sido asignados al departamento número 30. Dado que el número de departamento y mgr son conocidos se pueden utilizar constantes en la subconsulta.

UPDATE

El comando UPDATE es utilizado para cambiar la información existente en las filas de la base de datos. La sintaxis del UPDATE es

UPDATE [schema.]nombre_tabla SET columna1 = {expr | subconsulta} ... [, columnan = = {expr | subconsulta}] WHERE condicion;

Ejemplos:

UPDATE emp SET deptno = 30 WHERE ename = 'DOE';

En este ejemplo se transfiere al empleado DOE al departamento 30. Si hubiesen más de un empleado con el nombre DOE tendría que agregarse condiciones en el WHERE.

UPDATE emp SET sal = sal + (sal * .05);

Este da a cada empleado un 5 por ciento de incremento en el salario.

UPDATE emp a SET a.sal = (SELECT a.sal +                      (a.sal * DECODE(d.dname,                                      'SALES', .1,                                      'ADMIN', .07,                                      .06)                      )              FROM dept d              WHERE d.deptno = a.deptno) WHERE a.deptno = (SELECT deptno                  FROM dept y                  WHERE y.loc = 'ROCHESTER');

Este ejemplo da un incremento a los empleados de Rochester. El monto del incremento es manejado por el DECODE, evaluando el nombre del departamento. Los empleados de ventas reciben un 10%, los de administración reciben un 7% y los demás un 6%.

DELETE

El comando DELETE es utilizado para borrar registros de la base de datos. La sintaxis de DELETE es:

DELETE [FROM] [schema.]nombre_tabla WHERE condición

Ejemplos:

DELETE FROM emp e WHERE e.ename = 'DOE';

Si el empleado Doe sale de la compañía, se borra de la base de datos.

DELETE FROM dept WHERE loc IS NULL;

En este ejemplo, todos los registros en la tabla departamento serán borrados si el loc está nulo. Véase que en ningún caso el comando es permanente. Para que sea permanente debe aplicarse el comando COMMIT. Si se desea reversar los cambios realizados se debe utilizar el ROLLBACK (si fue usada una secuencia el consecutivo no se devuelve).