Топ-100
 

CREATE TABLE

Описания команд доступны в программе CerebroSQL

RDBMS Type: Oracle
Topic

CREATE [ GLOBAL TEMPORARY ] TABLE [ schema. ] table
{ relational_table | object_table | XMLType_table } ;

relational_table::=
[ (relational_properties) ]
[ ON COMMIT { DELETE | PRESERVE } ROWS ]
[ physical_properties ]
[ table_properties ]
object_table ::=
OF
[ schema. ] object_type
[ object_table_substitution ]
[ (object_properties) ]
[ ON COMMIT { DELETE | PRESERVE } ROWS ]
[ OID_clause ]
[ OID_index_clause ]
[ physical_properties ]
[ table_properties ]
XMLType_table ::=
OF XMLTYPE
[ (oject_properties) ]
[ XMLTYPE XMLType_storage ]
[ XMLSchema_spec ]
[ XMLType_virtual_columns ]
[ ON COMMIT { DELETE | PRESERVE } ROWS ]
[ OID_clause ]
[ OID_index_clause ]
[ physical_properties ]
[ table_properties ]
relational_properties::=
{ column_definition
| virtual_column_definition
| { out_of_line_constraint
| out_of_line_ref_constraint
| supplemental_logging_props
}
}
[, { column_definition
| virtual_column_definition
| { out_of_line_constraint
| out_of_line_ref_constraint
| supplemental_logging_props
}
}
]...
column_definition::=
column datatype [ SORT ]
[ DEFAULT expr ]
[ ENCRYPT encryption_spec ]
[ ( { inline_constraint }... )
| inline_ref_constraint
]
virtual_column_definition ::=
column [datatype] [GENERATED ALWAYS] AS (column_expression)
[VIRTUAL]
[ inline_constraint [inline_constraint]... ]
encryption_spec ::=
[ USING 'encrypt_algorithm' ]
[ IDENTIFIED BY password ]
[ 'integrity_algorithm' ]
[ [ NO ] SALT ]
oid_clause::=
OBJECT IDENTIFIER IS
{ SYSTEM GENERATED | PRIMARY KEY }
oid_index_clause::=
OIDINDEX [ index ]
({ physical_attributes_clause
| TABLESPACE tablespace
}...
)
physical_properties::=
{ [deferred_segment_creation] segment_attributes_clause [ table_compression ]
| [deferred_segment_creation] ORGANIZATION
{ HEAP [ segment_attributes_clause ] [ table_compression ]
| INDEX [ segment_attributes_clause ] index_org_table_clause
| EXTERNAL external_table_clause
}
| CLUSTER cluster (column [, column ]...)
}
deferred_segment_creation::=
SEGMENT CREATION { IMMEDIATE | DEFERRED }
physical_attributes_clause::=
[ { PCTFREE integer
| PCTUSED integer
| INITRANS integer
| storage_clause
}...
]
table_compression::=
{ COMPRESS [ BASIC
| FOR { OLTP
| { QUERY | ARCHIVE } [ LOW | HIGH ]
}
]
| NOCOMPRESS
}
table_properties::=
[ column_properties ]
[ table_partitioning_clauses ]
[ CACHE | NOCACHE ]
[ RESULT_CACHE ( MODE {DEFAULT | FORCE } ) ]
[ parallel_clause ]
[ ROWDEPENDENCIES | NOROWDEPENDENCIES ]
[ enable_disable_clause ]...
[ row_movement_clause ]
[ flashback_archive_clause ]
[ AS subquery ]
column_properties::=
{ object_type_col_properties
| nested_table_col_properties
| { varray_col_properties | LOB_storage_clause }
[ (LOB_partition_storage [, LOB_partition_storage ]...) ]
| XMLType_column_properties
}...
object_type_col_properties::=
COLUMN column substitutable_column_clause
substitutable_column_clause::=
{ [ ELEMENT ] IS OF [ TYPE ] ( [ONLY] type)
| [ NOT ] SUBSTITUTABLE AT ALL LEVELS
}
nested_table_col_properties ::=
NESTED TABLE
{ nested_item | COLUMN_VALUE }
[ substitutable_column_clause ]
[ LOCAL | GLOBAL ]
STORE AS storage_table
[ ( { (object_properties)
| [ physical_properties ]
| [ column_properties ]
}...
)
]
[ RETURN [ AS ] { LOCATOR | VALUE } ]

Example

CREATE TABLE employees_demo
( employee_id NUMBER(6)
, first_name VARCHAR2(20)
, last_name VARCHAR2(25)
CONSTRAINT emp_last_name_nn_demo NOT NULL
, email VARCHAR2(25)
CONSTRAINT emp_email_nn_demo NOT NULL
, phone_number VARCHAR2(20)
, hire_date DATE DEFAULT SYSDATE
CONSTRAINT emp_hire_date_nn_demo NOT NULL
, job_id VARCHAR2(10)
CONSTRAINT emp_job_nn_demo NOT NULL
, salary NUMBER(8,2)
CONSTRAINT emp_salary_nn_demo NOT NULL
, commission_pct NUMBER(2,2)
, manager_id NUMBER(6)
, department_id NUMBER(4)
, dn VARCHAR2(300)
, CONSTRAINT emp_salary_min_demo
CHECK (salary > 0)
, CONSTRAINT emp_email_uk_demo
UNIQUE (email)
)
TABLESPACE example
STORAGE (INITIAL 8M);

CREATE GLOBAL TEMPORARY TABLE today_sales
ON COMMIT PRESERVE ROWS
AS SELECT * FROM orders WHERE order_date = SYSDATE;

CREATE TABLE later (col1 NUMBER, col2 VARCHAR2(20)) SEGMENT CREATION DEFERRED;

CREATE TYPE person_t AS OBJECT (name VARCHAR2(100), ssn NUMBER)
NOT FINAL;

CREATE TABLE print_media
( product_id NUMBER(6)
, ad_id NUMBER(6)
, ad_composite BLOB
, ad_sourcetext CLOB
, ad_finaltext CLOB
, ad_fltextn NCLOB
, ad_textdocs_ntab textdoc_tab
, ad_photo BLOB
, ad_graphic BFILE
, ad_header adheader_typ
) NESTED TABLE ad_textdocs_ntab STORE AS textdocs_nestedtab;