, , ,

Create TABLE

The CREATE TABLE statement allows you to create and define a table.
The basic syntax for a CREATE TABLE statement is:
    CREATE TABLE table_name
    ( column1 datatype null/not null,
      column2 datatype PRIMARY KEY,
      column3 datatype PRIMARY KEY,
      …
    );

Each column must have a datatype. The column should either be defined as “null” or “not null” and if this value is left blank, the database assumes “null” as the default.
Example 1:
CREATE TABLE XX_PO_HEADERS_ALL
(
PO_ID         NUMBER(12) PRIMARY KEY,
PO_NUMBER     NUMBER(12),
SUPPLIER_NAME VARCHAR2(12) NOT NULL,
SUPPLIER_SITE VARCHAR2(12),
SHIP_TO       VARCHAR2(12),
BILL_TO       VARCHAR2(12)
)
Example 2:
CREATE TABLE XX_PO_LINES_ALL
(
PO_ID         NUMBER(12),
LINE_NUMBER   NUMBER(12),
ITEM_NAME     VARCHAR2(12),
QUANTITY      NUMBER(12),
ITEM_PRICE    NUMBER(12),
ITEM_TAX      NUMBER(12),
LINE_PRICE    NUMBER(12),
ORDER_DATE    DATE,
NEED_DATE     VARCHAR2(12),
BILL_TO       VARCHAR2(12)
)

Column Default Values

You can define tables to populate columns with default values as well using the default clause in a create table command. This clause is included as part of the column definition to tell Oracle what the default value for that column should be. When a row is added to the table and no value is defined for the column in the row being added, Oracle populates the column value for that row using the default value for the column. The following code block illustrates this point:
SQL> create table display
  2  (col1 varchar2(10),
  3   col2 number default 0);

Table created.
CREATE a table from another table
You can also create a table from an existing table by copying the existing table’s columns.
The basic syntax is:
CREATE TABLE table_name
  AS (SELECT * FROM old_table);

Example1:
CREATE TABLE XX_PO_HEADERS_ALL_COPY
AS (Select * From XX_PO_HEADERS_ALL)

The above statement ‘ll create a new table that is just an exact copy of XX_PO_HEADERS_ALL.
Example 2:  Copying selected columns from another table
The basic syntax is:
    CREATE TABLE new_table
      AS (SELECT column_1, column2, … column_n FROM old_table);

Example 3: Copying selected columns from multiple tables
The basic syntax is:
    CREATE TABLE new_table
      AS (SELECT column_1, column2, … column_n
              FROM old_table_1, old_table_2, … old_table_n);

It is important to note that when creating a table in this way, the new table will be populated with the records from the existing table (based on the SELECT Statement). If you want to create a blank table then use a condition which is always false in the where clause of the select statement.
Creating Temporary Tables
Most of the time, when you create a table in Oracle, the records that eventually populate that table will live inside your database forever (or at least until someone removes them). However, there might be situations where you want records in a table to live inside the database only for a short while. In this case, you can create temporary tables in Oracle, where the data placed into the tables persists for only the duration of the user session, or for the length of your current transaction.
A temporary table is created using the create global temporary table command. Why does a temporary table have to be global? So that the temporary table’s definition can be made available to every user on the system. However, the contents of a temporary table are visible only to the user session that added information to the temporary table, even though everyone can see the definition. Temporary tables are a relatively new feature in Oracle, and Oracle hasn’t had enough time yet to implement “local” temporary tables (that is, temporary tables that are only available to the user who owns them). Look for this functionality in later database releases. The appropriate create global temporary table command is shown in the following code block:

Create global temporary table XXX_PO_HEADERS_ALL as
Select *
From PO_HEADERS_ALL
Where 10=11

The purpose of writing the where clause is to make the temporary table blank. If we dont put the where clause the temporary table would contain all the rows of XXX_PO_HEADERS_ALL
0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply