Creates a temporary or persistent base table definition. A table
definition consists of a list of definitions of columns that make
up a row in the table.
Persistent base tables are tables whose metadata and data are
stored in the database beyond an SQL session. The data can be
shared by all users attached to the database.
Temporary tables are tables whose data is automatically deleted
when an SQL session or module ends. The tables only materialize
when you refer to them in an SQL session and the data is local
to an SQL session. You can also specify whether the data is
preserved or deleted at the end of a transaction within the
session; the default is to delete the data. The data in temporary
tables is private to the user. There are three types of temporary
tables:
o Global temporary tables
o Local temporary tables
o Declared local temporary tables (see the DECLARE LOCAL_
TEMPORARY_TABLE for additional information)
The metadata for a global temporary table is stored in the
database and persists beyond the SQL session. Different SQL
sessions can share the same metadata. The data stored in the
table cannot be shared between SQL sessions. However, the data
can be shared between modules in a single SQL session. The data
does not persist beyond an SQL session.
The metadata for a local temporary table is stored in the
database and persists beyond the SQL session. Different SQL
sessions can share the same metadata. The data stored in the
table cannot be shared between different modules in a single SQL
session or between SQL sessions. The data does not persist beyond
an SQL session or module.
Because temporary tables are used only to hold the user's data,
which is not shared among users, no locks are needed and the data
can be modified in a read-only transaction.
See the Oracle Rdb Guide to Database Design and Definition for
more information on temporary tables.
Information tables are special read-only tables that can be
used to retrieve database attributes that are not stored in the
existing relational tables. Information tables allow interesting
database information, which is currently stored in an internal
format, to be displayed as a relational table.
When you define a table, you can also define table constraints. A
constraint specifies a condition that restricts the values that
can be stored in a table. Constraints can specify that columns
contain:
- Only certain values
- Primary key values
- Unique values
- Values that cannot be null
There are several ways to specify a table definition in the
CREATE TABLE statement:
o Directly by naming the table, its columns and associated
data types, default values (optional), constraint definitions
(optional), and formatting clauses.
You can define constraints on persistent base tables and
global temporary tables only.
o Indirectly by providing a path name for a repository record
definition that specifies the table name, columns, and data
types.
o Indirectly by providing another table as a model in inheriting
the columns, datatypes and NOT NULL constraints.
SQL allows you to specify the default character data type or the
national character data type when defining table columns.
Additional Information:
explode
extract