Usar declaraciones DDL

Uso de declaraciones DDL para crear y administrar tablas

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

Tabla: para almacenar datos
Ver: para proyectar datos en un formato deseado de 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 identificar los requisitos del usuario del sistema para varios sistemas organizativos, como la entrada de pedidos, la gestión de inventario 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.

Creando la mesa

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 el tamaño de la columna. Toda esta información se puede modificar posteriormente mediante comandos DDL.

Convenciones de nomenclatura de tablas -

El nombre que elija para una tabla debe seguir estas reglas estándar:
El nombre debe comenzar con una letra AZ o az
Puede contener números y guiones bajos
Puede estar en MAYÚSCULAS o en minúsculas
Puede tener hasta 30 caracteres de longitud
No se puede usar el mismo nombre de otro objeto existente en su esquema
No debe ser una palabra reservada de SQL

Siguiendo las pautas anteriores, 'EMP85' puede ser un nombre de tabla válido, pero 85EMP no lo es. De manera similar, UPDATE no puede ser elegido como nombre de tabla ya que es una palabra clave reservada de SQL.

Sentencia CREATE TABLE

CREATE TABLE es una declaración 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 almacenar los datos en adelante. El usuario debe tener el privilegio del 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 CREAR CUALQUIER TABLA.

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

CREATE TABLE [schema.]table 
          ( { column datatype [DEFAULT expr] [column_constraint] ... 
            | table_constraint} 
         [, { column datatype [DEFAULT expr] [column_constraint] ... 
            | table_constraint} ]...) 
         [AS subquery]

En la sintaxis anterior, DEFAULT especifica el valor predeterminado que se puede usar durante la instrucción INSERT si se ignora la columna. No puede contener referencias a otras columnas de 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 (tratadas más adelante en este capítulo). Estas reglas se verifican durante cualquier acción de datos (Insertar, actualizar) en la tabla y generan un error para abortar la acción tras su violación.

Por ejemplo, la instrucción CREATE TABLE a continuación crea una tabla EMP_TEST. Tenga en cuenta 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 consultar las tablas del esquema de otro usuario prefijando el nombre de usuario o esquema con el nombre de la tabla. Por ejemplo, un usuario INVITADO desea consultar el nombre del empleado y el salario de la tabla EMP_TEST que es propiedad de SCOTT. Puede emitir la siguiente consulta:

SELECT  ENAME, SALARY,
FROM 	GUEST.EMP_TEST;

Una columna puede contener un valor predeterminado durante el tiempo de creación de la tabla. Ayuda a restringir los valores NULL que ingresan a la columna. El valor predeterminado se puede deducir de un literal, una expresión o una función SQL que debe devolver un tipo de datos compatible a la columna. En la siguiente declaración CREATE TABLE, tenga en cuenta que la columna LOCATION_ID tiene el valor predeterminado 100.

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

CTAS - Crear tabla usando subconsultas

Se puede crear una tabla a partir de una tabla existente en la base de datos mediante una opción de subconsulta, que copia la estructura de la tabla y los datos de la tabla. Los datos también se pueden copiar en función de las condiciones. Las definiciones de tipos de datos de columna, 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_BACKUP
AS
SELECT * FROM EMP_TEST
WHERE 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 más generales, el comportamiento de la columna puede pertenecer a un número, carácter o una familia de fechas. Hay muchos otros subtipos que pertenecen a estas familias.

Tipo de datos numéricos

El tipo de datos NUMBER abarca valores numéricos enteros, de coma fija y de coma flotante. Las primeras versiones de Oracle definían diferentes tipos de datos para cada uno de estos diferentes 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 debe almacenar datos numéricos que se pueden usar en cálculos matemáticos. Ocasionalmente, el tipo de datos NUMBER se usa para almacenar números de identificación donde esos números son generados por el DBMS como números secuenciales.

NÚMERO (p, s), donde p es la precisión de 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.

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

FLOAT [(p)], donde p es la precisión binaria que puede oscilar entre 1 y 126. Si no se especifica p, el valor predeterminado es 126 binario.

Tipo de datos de fecha

Para cada tipo de datos FECHA, se almacenan en la base de datos Siglo, Año, Mes, Día, Hora, Minuto, Segundo. Cada sistema de base de datos tiene un formato de fecha predeterminado que se define mediante el parámetro de inicialización NLS_DATE_FORMAT. Este parámetro generalmente se establece en DD-MON-YY. Si no especifica una hora, la hora predeterminada es 12:00:00 am

Tipo de datos de caracteres

Oracle admite tres tipos de datos de caracteres predefinidos, incluidos CHAR, VARCHAR, VARCHAR2 y LONG.VARCHAR y VARCHAR2 son en realidad sinónimos, y Oracle recomienda utilizar VARCHAR2 en lugar de VARCHAR. Utilice el tipo de datos CHAR cuando la columna almacene valores de caracteres de longitud fija. Por ejemplo, un número de Seguro Social (SSN) en los Estados Unidos se asigna a cada ciudadano y siempre tiene un tamaño de 9 caracteres (aunque un SSN se compone estrictamente 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á considerablemente en términos de la cantidad de caracteres que se almacenarán. 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, incluidos CLOB (objeto grande de caracteres) y BLOB (objeto grande binario). Las columnas de estos tipos de datos pueden almacenar datos no estructurados, incluidos texto, imágenes, video y datos espaciales. El tipo de datos CLOB puede almacenar hasta ocho terabytes de datos de caracteres utilizando el juego 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 con datos de imagen y video, donde los datos son simplemente un flujo de valores de "bits". ocho terabytes de datos binarios. El tipo de datos NCLOB puede almacenar objetos grandes de caracteres en caracteres nacionales multibyte configurados de hasta 8 TB a 128 TB. El valor del tipo de datos BFILE funciona como un localizador de archivos o puntero a un archivo en el sistema de archivos del servidor. El tamaño máximo de archivo admitido es de 8 TB a 128 TB.

Restricciones

Las restricciones son el conjunto de reglas definidas en las tablas de Oracle para garantizar la integridad de los datos. Estas reglas se aplican en cada columna o conjunto de columnas. Siempre que la tabla participa en una acción de datos, estas reglas se validan y generan 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 restricciones a nivel de columna.

Sintaxis:

column [data type] [CONSTRAINT constraint_name] constraint_type

Todas las restricciones, excepto NOT NULL, también se pueden definir a nivel de tabla. Las restricciones compuestas solo se pueden especificar 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, Oracle RDBMS no permitirá que se almacenen filas en la tabla de empleados que violen esta restricción. solo se puede definir a nivel de columna y no a nivel de tabla.

Sintaxis:

COLUMN [data type] [NOT NULL]

Restricción ÚNICA

A veces es necesario aplicar la unicidad para un valor de columna que no es una columna de clave principal. La restricción ÚNICA se puede usar para hacer cumplir esta regla y Oracle rechazará cualquier fila que viole la restricción única. La restricción ÚNICA asegura que los valores de la columna sean distintos. , sin duplicados.

Sintaxis:

Column Level:

COLUMN [data type] [CONSTRAINT <name>] [UNIQUE]

Table Level: RESTRICCIÓN [nombre de la restricción] ÚNICA (nombre de la columna)

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

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

En el caso de una clave única compuesta, debe definirse a nivel de tabla como se muestra 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 un 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 conoce como la clave principal. La mayoría de las tablas tienen una sola columna como clave principal. las columnas de clave están restringidas contra NULL y valores duplicados.

Puntos a tener en cuenta:

Una tabla solo puede tener una clave principal.
Se pueden agrupar varias columnas bajo una clave primaria compuesta.
Oracle crea internamente un índice único para evitar la duplicación en los valores de columna. Los índices se analizarán más adelante en PL / SQL.

Sintaxis:

Column level:

COLUMN [data type] [CONSTRAINT <constraint name> PRIMARY KEY]

Table level:

CONSTRAINT [constraint name] PRIMARY KEY [column (s)]

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 usando la restricción PRIMARY KEY en el nivel de tabla.

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

Clave externa

Cuando dos tablas comparten la relación padre-hijo en función de una columna específica, la columna de unión en la tabla secundaria se conoce como clave externa. Esta propiedad de la columna correspondiente en la tabla principal se conoce como integridad referencial. Los valores de la columna de clave extranjera en la tabla secundaria pueden ser nulo o deben ser los valores existentes de la tabla principal. Tenga en cuenta que solo las columnas de clave principal de la tabla a la que se hace referencia son elegibles para aplicar la integridad referencial.

Si se define una clave externa en la columna de la tabla secundaria, Oracle no permite que se elimine la fila principal, si contiene filas secundarias. Sin embargo, si se da la opción ON DELETE CASCADE en el momento de definir la clave externa, Oracle elimina todas las filas secundarias mientras se elimina la fila principal. De manera similar, ON DELETE SET NULL indica que cuando se elimina una fila en la tabla principal, los valores de clave externa se establecen en nulos.

Sintaxis:

Column Level:

COLUMN [data type] [CONSTRAINT] [constraint name] [REFERENCES] [table name (column name)]

Table level:

CONSTRAINT [constraint name] [FOREIGN KEY (foreign key column name) REFERENCES] [referenced table name (referenced column name)]

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),
   ...
);

Usage of ON DELETE CASCADE clause

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

Comprobar restricción

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

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

Sintaxis:

Column level:

COLUMN [data type] CONSTRAINT [name] [CHECK (condition)]

Table level:

CONSTRAINT [name] 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 columna después de que la tabla se ha creado en la base de datos. El comando DDL ALTER TABLE se utiliza para realizar tales acciones. El comando Alter proporciona múltiples utilidades exclusivas para los objetos de esquema. para agregar, quitar, renombrar y modificar una columna en una tabla.

La siguiente instrucción ALTER TABLE cambia el nombre de la tabla EMP a EMP_NEW.

ALTER TABLE EMP RENAME TO EMP_NEW;

La siguiente instrucción ALTER TABLE agrega una nueva columna TESTCOL a la tabla EMP_NEW

ALTER TABLE EMP_NEW ADD (TESTCOL VARCHAR2 (100))

La siguiente instrucción ALTER TABLE cambia el nombre de la columna TESTCOL a TESTNEW.

ALTER TABLE EMP_NEW RENAME COLUMN TESTCOL TO TESTNEW

La siguiente instrucción ALTER TABLE elimina la columna TESTNEW de la tabla EMP_NEW

ALTER TABLE EMP_NEW DROP COLUMN TESTNEW;

La siguiente instrucción ALTER TABLE agrega la clave principal en la columna EMPLOYEE_ID.

ALTER TABLE EMP_NEW ADD PRIMARY KEY (EMPLOYEE_ID)

La siguiente instrucción ALTER TABLE elimina la clave principal.

ALTER TABLE EMP_NEW DROP PRIMARY KEY;

La siguiente instrucción ALTER TABLE cambia el modo de tabla a solo lectura.

ALTER TABLE EMP_NEW READ ONLY;

Tablas de solo lectura

Las tablas de solo lectura se incorporaron como una mejora en Oracle 11g y permiten que las tablas se utilicen con fines de solo lectura. En versiones anteriores de Oracle, las tablas se hacían de solo lectura al otorgar privilegios SELECT a los otros usuarios, pero el propietario aún tenía el privilegio de lectura y escritura. Pero ahora, si una tabla está configurada como Solo lectura, incluso el propietario no tiene acceso a la manipulación de datos. .

Sintaxis:

ALTER TALE [TABLE NAME] READ ONLY

ALTER TALE [TABLE NAME] 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.

Declaración DROP TABLE

La instrucción DROP TABLE se utiliza para eliminar una tabla de la base de datos. La tabla eliminada y sus datos ya no permanecen disponibles para la selección. La tabla eliminada se puede recuperar utilizando la utilidad FLASHBACK, si está disponible en la papelera de reciclaje. Al soltar una tabla se elimina el índice y los activadores asociados con ella.

Sintaxis:

DROP TABLE [TABLE NAME] [PURGE]

La siguiente declaración soltará la tabla y la colocará en la papelera de reciclaje.

DROP TABLE emp_new;

La siguiente declaración eliminará la tabla y la eliminará también de la papelera de reciclaje.

DROP TABLE emp_new PURGE;

↰ Previous page Next page ↱