Recomendar a un amigo. Compartir el enlace.

Create Table

La estructura de la sentencia de creación de tablas es:


CREATE [GLOBAL TEMPORARY] TABLE [esquema.]tabla
     columna datatype [DEFAULT expr] [column_constraint(s)]
   [,columna datatype [,...]]
    table_constraint
    table_ref_constraint
   [ON COMMIT {DELETE|PRESERVE} ROWS]
    storage_options  [COMPRESS int|NOCOMPRESS]
   [LOB_storage_clause][varray_clause][nested_storage_clause] [XML_type_clause]
    Partitioning_clause
   [[NO]CACHE] [[NO]ROWDEPENDENCIES] [[NO]MONITORING] [PARALLEL parallel_clause]
   [ENABLE enable_clause | DISABLE disable_clause]
   {ENABLE|DISABLE} ROW MOVEMENT
   [AS subquery]

Queda mas claro con unos ejemplos:


create table T_PRODUCTOS (
  numproduct number,
  desproduct varchar2(10)
)

Es posible definir restricciones (constraint) con la sentencia CREATE.


create table T_PEDIDOS(
  numpedido   number primary key
  fecpedido   date,
  numclient  references T_CLIENTES
)

Una clave primaria (primary key) necesita tener asociado un indice unico (unique index). Es posible especificar el tablespace donde queremos crear el indice.


create table T_PEDIDOS(
  numpedido   number primary key
  fecpedido   date,
  numclient  references T_CLIENTES
  constraint pk_pedidos (numpedido) using index tablespace ts_idx
)

Index organized tables (IOT)


create table iot_ (
  a number,
  b varchar2(10),
constraint pk_iot_ primary key (a, b)
)
organization index;

Organization external The following create table statement creates an external table.


create table (....)
organization external (
  type              oracle_loader
  default directory some_dir
  access parameters (
    records delimited  by newline
    fields  terminated by ','
    missing field are values null
  )
  location ('some_file.csv')
)
reject limit unlimited;

Nested tables


create or replace type item as object (
  item_id Number  ( 6  ),
  descr   varchar2(30  ),
  quant   Number  ( 4,2)
);
/

create or replace type items as table of item;
/

create table bag_with_items (
  bag_id                   number(7)     primary key,
  bag_name                 varchar2(30)  not null,
  the_items_in_the_bag     items         
)
nested table the_items_in_the_bag store as bag_items_nt;

Adding a unique constraint for item_id:

alter table bag_items_nt add constraint uq_item_id unique(item_id);