Uso de las sentencias DDL

Uso de las sentencias DDL para crear y gestionar tablas

Un esquema es la colección de múltiples objetos de base de datos,que se conocen como objetos de esquema.Estos objetos tienen acceso directo por su esquema propietario.La tabla siguiente enumera los objetos de esquema.

• Tabla – para almacenar datos

• Vista – para proyectar datos en un formato deseado desde una o más tablas

• Secuencia – para generar valores numéricos

• Índice – para mejorar el rendimiento de las consultas en las tablas

• Sinónimo – nombre alternativo de un objeto

Uno de los primeros pasos en la creación de una base de datos es crear las tablas que almacenarán los datos de una organización.El diseño de la base de datos implica la identificación de los requisitos del usuario del sistema para varios sistemas organizativos, como la entrada de pedidos, la gestión de inventarios y las cuentas por cobrar. Independientemente del tamaño y la complejidad de la base de datos, cada base de datos se compone de tablas.

Creación de la tabla

Para crear una tabla en la base de datos, un DBA debe tener cierta información a mano: el nombre de la tabla, el nombre de la columna, los tipos de datos de la columna y los tamaños de la columna. Toda esta información puede ser modificada posteriormente mediante comandos DDL.

Convenciones de nomenclatura de tablas –

• El nombre que se elija para una tabla debe seguir estas reglas estándar:

• El nombre debe comenzar con una letra A-Z o a-z

• Puede contener números y guiones bajos

• Puede estar en MAYÚSCULAS o minúsculas

• Puede tener hasta 30 caracteres

• No puede utilizar el mismo nombre de otro objeto existente en su esquema

• No debe ser una palabra reservada de SQL

Siguiendo las directrices anteriores, ‘EMP85’ puede ser un nombre de tabla válido.Del mismo modo, UPDATE no puede ser elegido como un nombre de tabla, ya que es una palabra reservada de SQL.

Declaración CREATE TABLE

La sentencia CREATE TABLE es una sentencia DDL que se utiliza para crear tablas en la base de datos.La tabla se crea tan pronto como se ejecuta el script CREATE TABLE y está lista para contener los datos en adelante.El usuario debe tener el privilegio de sistema CREATE TABLE para crear la tabla en su propio esquema.Pero para crear una tabla en el esquema de cualquier usuario, el usuario debe tener el esquema CREATE ANY TABLE.

Aquí está la sintaxis de una sentencia básica CREATE TABLE.Puede haber muchas cláusulas adicionales para proporcionar explícitamente las especificaciones de almacenamiento o los valores de los segmentos.

CREATE TABLE table ( { column datatype ... | table_constraint} ... | table_constraint} ]...) 

En la sintaxis anterior, DEFAULT especifica el valor por defecto que puede ser utilizado durante la sentencia INSERT si la columna es ignorada. No puede contener referencias a otras columnas de la tabla o pseudocolumnas (CURRVAL, NEXTVAL, LEVEL y ROWNUM) excepto SYSDATE y USER, o constantes de fecha que no estén completamente especificadas.

Las restricciones son las reglas definidas opcionalmente a nivel de columna o de tabla (cubiertas más adelante en este capítulo).Estas reglas se comprueban durante cualquier acción de datos (Insertar, actualizar) en la tabla y lanzan un error para abortar la acción en caso de que se infrinja.

Por ejemplo, la sentencia CREATE TABLE a continuación crea una tabla EMP_TEST. Observe las especificaciones de la columna, el tipo de datos y la precisión.

CREATE TABLE SCOTT.EMP_TEST(EMPID NUMBER,ENAME VARCHAR2(100),DEPARTMENT_ID NUMBER,SALARY NUMBER,JOB_ID VARCHAR2(3),HIREDATE DATE,COMM NUMBER);

Un usuario puede referirse a las tablas del esquema de otro usuario anteponiendo el nombre de usuario o el esquema con el nombre de la tabla.Por ejemplo, un usuario GUEST desea consultar el nombre del empleado y el salario de la tabla EMP_TEST que es propiedad de SCOTT. Puede realizar la siguiente consulta –

SELECT ENAME, SALARY,FROM GUEST.EMP_TEST;

Una columna puede tener un valor por defecto durante la creación de la tabla, lo que ayuda a restringir la entrada de valores NULL en la columna. El valor por defecto puede deducirse de un literal, una expresión o una función SQL que debe devolver un tipo de datos compatible con la columna. En la siguiente sentencia CREATE TABLE, observe que la columna LOCATION_ID tiene el valor por defecto 100.

CREATE TABLE SCOTT.DEPARTMENT(DEPARTMENT_ID NUMBER, DNAME VARCHAR2 (100), LOCATION_ID NUMBER DEFAULT 100);

CTAS – Crear tabla usando subconsulta

Se puede crear una tabla a partir de una tabla existente en la base de datos usando una opción de subconsulta.Copia la estructura de la tabla así como los datos de la misma. Las definiciones de los tipos de datos de las columnas, incluidas las restricciones NOT NULL impuestas explícitamente, se copian en la nueva tabla.

El siguiente script CTAS crea una nueva tabla EMP_BACKUP. Los datos de los empleados del departamento 20 se copian en la nueva tabla.

CREATE TABLE EMP_BACKUPASSELECT * FROM EMP_TESTWHERE department_id=20;

Tipos de datos

Los tipos de datos se utilizan para especificar el comportamiento básico de una columna en la tabla.En términos generales, el comportamiento de la columna puede pertenecer a la familia de números, caracteres o fechas.Hay otros múltiples subtipos que pertenecen a estas familias.

Tipo de datos numérico

El tipo de datos NUMBER engloba valores numéricos enteros, de punto fijo y de punto flotante.Las primeras versiones de Oracle definían diferentes tipos de datos para cada uno de estos tipos de números, pero ahora el tipo de datos NUMBER sirve para todos estos propósitos.Elija el tipo de datos NUMBER cuando una columna deba almacenar datos numéricos que puedan utilizarse en cálculos matemáticos.Ocasionalmente,el tipo de datos NUMBER se utiliza para almacenar números de identificación cuando dichos números son generados por el SGBD como números secuenciales.

NÚMERO (p, s), donde p es la precisión hasta 38 dígitos y s es la escala (número de dígitos a la derecha del punto decimal).La escala puede oscilar entre -84 y 127.

NÚMERO (p),es un número de punto fijo con una escala de cero y una precisión de p.

FLOTA ,donde p es la precisión binaria que puede oscilar entre 1 y 126. Si no se especifica p, el valor por defecto es el binario 126.

Tipo de datos DATE

Para cada tipo de datos DATE, se almacenan en la base de datos el siglo, el año, el mes, el día, la hora, el minuto y el segundo. Cada sistema de base de datos tiene un formato de fecha por defecto que se define mediante el parámetro de inicialización NLS_DATE_FORMAT. Si no se especifica una hora, la hora predeterminada es 12:00:00 a.m.

Tipo de datos de caracteres

Oracle admite tres tipos de datos de caracteres predefinidos que incluyen CHAR, VARCHAR, VARCHAR2 y LONG.VARCHAR y VARCHAR2 son en realidad sinónimos, y Oracle recomienda utilizar VARCHAR2 en lugar de VARCHAR.Por ejemplo, un número de la Seguridad Social (SSN) en los Estados Unidos se asigna a cada ciudadano y siempre tiene 9 caracteres (aunque un SSN está estrictamente compuesto de dígitos, los dígitos se tratan como caracteres), y se especificaría como CHAR(9). Utilice el tipo de datos VARCHAR2 para almacenar datos alfanuméricos de longitud variable.Por ejemplo, el nombre o la dirección de un cliente variarán considerablemente en cuanto al número de caracteres a almacenar.El tamaño máximo de una columna VARCHAR2 es de 4.000 caracteres.

Tipo de datos LOB

Oracle proporciona varios tipos de datos LOB diferentes, incluyendo CLOB (character large object) y BLOB (binary large object).Las columnas de estos tipos de datos pueden almacenar datos no estructurados, incluyendo texto, imagen, vídeo y datos espaciales.El tipo de datos CLOB puede almacenar hasta ocho terabytes de datos de caracteres utilizando el conjunto de caracteres de la base de datos CHAR. El tipo de datos BLOB se utiliza para almacenar objetos grandes binarios no estructurados, como los asociados a los datos de imagen y vídeo, en los que los datos son simplemente un flujo de valores de «bits».Un tipo de datos BLOB puede almacenar hasta ocho terabytes de datos binarios.El tipo de datos NCLOB puede almacenar objetos grandes de carácter en conjunto de caracteres nacionales multibyte hasta 8TB a 128TB.El valor del tipo de datos BFILE funciona como un localizador de archivos o puntero a archivo en el sistema de archivos del servidor. El tamaño máximo de archivo soportado es de 8TB a 128TB.

Constraints

Las restricciones son el conjunto de reglas definidas en las tablas de Oracle para asegurar la integridad de los datos.Estas reglas se aplican colocadas para cada columna o conjunto de columnas.Siempre que la tabla participa en la acción de datos, estas reglas se validan y lanzan una excepción en caso de violación. Los tipos de restricciones disponibles son NOT NULL, Primary Key, Unique, Check y Foreign Key.

La siguiente sintaxis se puede utilizar para imponer una restricción a nivel de columna.

Sintaxis:

column constraint_type

Todas las restricciones, excepto NOT NULL, pueden definirse también a nivel de tabla. Las restricciones compuestas sólo pueden especificarse a nivel de tabla.

Restricción NOT NULL

Una restricción NOT NULL significa que una fila de datos debe tener un valor para la columna especificada como NOT NULL.Si una columna se especifica como NOT NULL, el RDBMS de Oracle no permitirá que se almacenen filas en la tabla de empleados que violen esta restricción.Sólo puede definirse a nivel de columna, y no a nivel de tabla.

Sintaxis:

COLUMN 

Restricción UNIQUE

A veces es necesario imponer la unicidad de un valor de columna que no es una columna de clave primaria.La restricción UNIQUE se puede utilizar para imponer esta regla y Oracle rechazará cualquier fila que viole la restricción única.La restricción única asegura que los valores de columna son distintos, sin duplicados.

Sintaxis:

Nivel de columna:

COLUMN 

Nivel de tabla: CONSTRAINT UNIQUE (nombre de la columna)

Nota: Oracle crea internamente un índice único para evitar la duplicación en los valores de la columna.Los índices se discutirán más adelante en PL/SQL.

CREATE TABLE TEST( ... , NAME VARCHAR2(20) CONSTRAINT TEST_NAME_UK UNIQUE, ... );

En caso de clave única compuesta, debe definirse a nivel de tabla como se indica a continuación.

CREATE TABLE TEST( ... , NAME VARCHAR2(20), STD VARCHAR2(20) , CONSTRAINT TEST_NAME_UK UNIQUE (NAME, STD) );

Clave primaria

Cada tabla debe contener normalmente una columna o conjunto de columnas que identifique de forma única las filas de datos que se almacenan en la tabla.Esta columna o conjunto de columnas se denomina clave primaria.La mayoría de las tablas tienen una sola columna como clave primaria.Las columnas de clave primaria están restringidas contra NULLs y valores duplicados.

Puntos a tener en cuenta –

• Una tabla sólo puede tener una clave primaria.

• Múltiples columnas pueden agruparse bajo una clave primaria compuesta.

• Oracle crea internamente un índice único para evitar la duplicación en los valores de las columnas.Los índices se discutirían más adelante en PL/SQL.

Sintaxis:

Nivel de columna:

COLUMN 

Nivel de tabla:

CONSTRAINT PRIMARY KEY 

El siguiente ejemplo muestra cómo utilizar la restricción PRIMARY KEY a nivel de columna.

CREATE TABLE TEST( ID NUMBER CONSTRAINT TEST_PK PRIMARY KEY, ... ); 

El siguiente ejemplo muestra cómo definir la clave primaria compuesta utilizando la restricción PRIMARY KEY a nivel de tabla.

CREATE TABLE TEST ( ..., CONSTRAINT TEST_PK PRIMARY KEY (ID) ); 

Clave foránea

Cuando dos tablas comparten la relación padre-hijo basada en una columna específica, la columna de unión en la tabla hija se conoce como clave foránea.Esta propiedad de la columna correspondiente en la tabla padre se conoce como integridad referencial.Los valores de la columna de clave extranjera en la tabla hija pueden ser nulos o deben ser los valores existentes de la tabla padre.Tenga en cuenta que sólo las columnas de clave primaria de la tabla referenciada son elegibles para hacer cumplir la integridad referencial.

Si se define una clave foránea en la columna de la tabla hija, Oracle no permite que se elimine la fila padre, si contiene alguna fila hija, pero si se da la opción ON DELETE CASCADE en el momento de definir la clave foránea, Oracle elimina todas las filas hijas mientras se elimina la fila padre.Del mismo modo, ON DELETE SET NULL indica que cuando se elimina una fila de la tabla padre, los valores de la clave foránea se establecen en null.

Sintaxis:

Nivel de columna:

COLUMN 

Nivel de tabla:

CONSTRAINT 

El siguiente ejemplo muestra cómo utilizar la restricción FOREIGN KEY a nivel de columna.

CREATE TABLE TEST(ccode varchar2(5) CONSTRAINT TEST_FK REFERENCES PARENT_TEST(ccode), ...);

Uso de la cláusula ON DELETE CASCADE

CREATE TABLE TEST(ccode varchar2(5) CONSTRAINT TEST_FK REFERENCES PARENT_TEST (ccode) ON DELETE CASCADE, ...);

Constricción de comprobación

A veces los valores de los datos almacenados en una columna específica deben estar dentro de un rango de valores aceptable.Una restricción de comprobación requiere que la condición de comprobación especificada sea verdadera o desconocida para cada fila almacenada en la tabla.La restricción de comprobación permite imponer una regla condicional en una columna, que debe ser validada antes de que los datos se inserten en la columna. La condición no debe contener una subconsulta o pseudocolumna CURRVAL NEXTVAL, LEVEL, ROWNUM o SYSDATE.

Oracle permite que una misma columna tenga más de una restricción CHECK. De hecho, no hay límite práctico al número de restricciones CHECK que se pueden definir para una columna.

Sintaxis:

Nivel de columna:

COLUMN CONSTRAINT 

Nivel de tabla:

CONSTRAINT CHECK (condition)

El siguiente ejemplo muestra cómo utilizar la restricción CHECK a nivel de columna.

CREATE TABLE TEST( ..., GRADE char (1) CONSTRAINT TEST_CHK CHECK (upper (GRADE) in ('A','B','C')), ...);

El siguiente ejemplo muestra cómo utilizar la restricción CHECK a nivel de tabla.

CREATE TABLE TEST( ..., CONSTRAINT TEST_CHK CHECK (stdate < = enddate),);

Declaración ALTER TABLE

Un DBA puede realizar cambios en la estructura de la tabla o en las definiciones de las columnas después de haber creado la tabla en la base de datos.El comando DDL ALTER TABLE se utiliza para realizar dichas acciones.El comando Alter proporciona múltiples utilidades exclusivas para los objetos del esquema.La sentencia ALTER TABLE se utiliza para añadir, eliminar, renombrar y modificar una columna de una tabla.

La siguiente sentencia ALTER TABLE cambia el nombre de la tabla EMP a EMP_NEW.

ALTER TABLE EMP RENAME TO EMP_NEW;

La siguiente sentencia ALTER TABLE añade una nueva columna TESTCOL a la tabla EMP_NEW

ALTER TABLE EMP_NEW ADD (TESTCOL VARCHAR2 (100))

La siguiente sentencia ALTER TABLE cambia el nombre de la columna TESTCOL a TESTNEW.

ALTER TABLE EMP_NEW RENAME COLUMN TESTCOL TO TESTNEW

La siguiente sentencia ALTER TABLE elimina la columna TESTNEW de la tabla EMP_NEW

ALTER TABLE EMP_NEW DROP COLUMN TESTNEW;

La siguiente sentencia ALTER TABLE añade una clave primaria a la columna EMPLOYEE_ID.

ALTER TABLE EMP_NEW ADD PRIMARY KEY (EMPLOYEE_ID)

La siguiente sentencia ALTER TABLE elimina la clave primaria.

ALTER TABLE EMP_NEW DROP PRIMARY KEY;

La siguiente sentencia ALTER TABLE cambia el modo de la tabla a sólo lectura.

ALTER TABLE EMP_NEW READ ONLY;

Tablas de sólo lectura

Las tablas de sólo lectura son una mejora de Oracle 11g. En versiones anteriores de Oracle, las tablas se hacían de sólo lectura concediendo el privilegio de SELECT a otros usuarios, pero el propietario seguía teniendo el privilegio de lectura y escritura, pero ahora, si una tabla se establece como de sólo lectura, incluso el propietario no tiene acceso a la manipulación de datos.

Sintaxis:

ALTER TALE READ ONLY

ALTER TALE READ WRITE

Ilustración

SQL>CREATE TABLE ORATEST (id NUMBER)SQL>INSERT INTO ORATEST VALUES (1);SQL>ALTER TABLE ORATEST READ ONLY;SQL> INSERT INTO ORATEST VALUES (2);INSERT INTO ORATEST VALUES (2) *ERROR at line 1:ORA-12081: update operation not allowed on table "TEST"."ORATEST"SQL> UPDATE ORATEST SET id = 2;UPDATE ORATEST SET id = 2 *ERROR at line 1:ORA-12081: update operation not allowed on table "TEST"."ORATEST"SQL> DELETE FROM ORATEST;DELETE FROM ORATEST *ERROR at line 1:ORA-12081: update operation not allowed on table "TEST"."ORATEST"SQL> TRUNCATE TABLE ORATEST;TRUNCATE TABLE ORATEST *ERROR at line 1:ORA-12081: update operation not allowed on table "TEST"."ORATEST"SQL> ALTER TABLE ORATEST ADD (description VARCHAR2 (50));ALTER TABLE ORATEST ADD (description VARCHAR2 (50))*ERROR at line 1:ORA-12081: update operation not allowed on table "TEST"."ORATEST"SQL> ALTER TABLE ORATEST READ WRITE;Table altered.SQL> DELETE FROM ORATEST;1 row deleted.

Sentencia DROP TABLE

La sentencia DROP TABLE se utiliza para eliminar una tabla de la base de datos. La tabla eliminada y sus datos ya no están disponibles para su selección.La tabla eliminada puede recuperarse utilizando la utilidad FLASHBACK, si está disponible en la papelera de reciclaje.Al eliminar una tabla se eliminan el índice y los triggers asociados a ella.

Sintaxis:

DROP TABLE 

La siguiente sentencia eliminará la tabla y la colocará en la papelera de reciclaje.

DROP TABLE emp_new;

La siguiente sentencia eliminará la tabla y la sacará de la papelera de reciclaje también.

DROP TABLE emp_new PURGE;