Oracle PL/SQL Tutorial/Table/Create Table

Cascade delete setting

SQL>
SQL>
SQL> CREATE TABLE EMP (EMPNO NUMBER(4) primary key,
  2                    ENAME VARCHAR2(10),
  3                    JOB VARCHAR2(9),
  4                    MGR NUMBER(4),
  5                    HIREDATE DATE,
  6                    SAL NUMBER(7, 2),
  7                    COMM NUMBER(7, 2),
  8                    DEPTNO NUMBER(2));
Table created.
SQL>
SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK",    7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7521, "WARD",  "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER",  7839, TO_DATE("2-APR-1981",  "DD-MON-YYYY"), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10);
1 row created.
SQL>
SQL> create table addresses
  2  ( empno     number(4) references emp(empno) on delete cascade,
  3    addr_type varchar2(10),
  4    street    varchar2(20),
  5    city      varchar2(20),
  6    state     varchar2(2),
  7    zip       number,
  8    primary key (empno,addr_type)
  9  )
 10  ORGANIZATION INDEX
 11  /
Table created.
SQL>
SQL>
SQL> drop table addresses;
Table dropped.
SQL> drop table emp;
Table dropped.
SQL>

Create an external table

SQL> create table myTable
  2  (c1 varchar2(80) )
  3  organization external
  4  (
  5   type oracle_loader
  6   default directory infile
  7   access parameters
  8      (
  9        records delimited by newline
 10        fields
 11        (c1 char(80) )
 12      )
 13   location ("xml.lst")
 14   )
 15  /

SQL>
SQL>
SQL> select * from myTable
  2  where rownum <= 5;

SQL> drop table myTable;

Create a table based on a hash cluster

SQL>
SQL>
SQL> create cluster hash_cluster
  2  ( hash_key number )
  3  hashkeys 1000
  4  size 8192
  5  /
Cluster created.
SQL>
SQL> create table hashed_table
  2  (x number, data1 varchar2(4000), data2 varchar2(4000) )
  3  cluster hash_cluster(x);
Table created.
SQL>
SQL>
SQL> drop cluster hash_cluster;
drop cluster hash_cluster
*
ERROR at line 1:
ORA-00951: cluster not empty

SQL>
SQL> drop table hashed_table;
Table dropped.

Create a table from one or more other tables

SQL>
SQL> CREATE TABLE person (
  2       person_code VARCHAR2(3) PRIMARY KEY,
  3       first_name  VARCHAR2(15),
  4       last_name   VARCHAR2(20),
  5       hire_date   DATE
  6       );
Table created.
SQL>
SQL>
SQL> INSERT INTO person VALUES ("CA", "Chase", "At", "01-FEB-02");
1 row created.
SQL> INSERT INTO person VALUES ("GA", "Gary", "Talor", "15-FEB-02");
1 row created.
SQL> INSERT INTO person VALUES ("BB", "Bob", "Bark", "28-FEB-02");
1 row created.
SQL> INSERT INTO person VALUES ("LB", "Laren", "Baby", "01-MAR-02");
1 row created.
SQL> INSERT INTO person VALUES ("LN", "Linda", "Norman", "01-JUN-03");
1 row created.
SQL>
SQL> CREATE TABLE product_order (
  2       product_name  VARCHAR2(25),
  3       salesperson   VARCHAR2(3),
  4       order_date DATE,
  5       quantity      NUMBER(4,2)
  6       );
Table created.
SQL>
SQL>
SQL> INSERT INTO product_order VALUES ("Product 1", "CA", "14-JUL-03", 1);
1 row created.
SQL> INSERT INTO product_order VALUES ("Product 2", "BB", "14-JUL-03", 75);
1 row created.
SQL> INSERT INTO product_order VALUES ("Product 3", "GA", "14-JUL-03", 2);
1 row created.
SQL> INSERT INTO product_order VALUES ("Product 4", "GA", "15-JUL-03", 8);
1 row created.
SQL> INSERT INTO product_order VALUES ("Product 5", "LB", "15-JUL-03", 20);
1 row created.
SQL> INSERT INTO product_order VALUES ("Product 6", "CA", "16-JUL-03", 5);
1 row created.
SQL> INSERT INTO product_order VALUES ("Product 7", "CA", "17-JUL-03", 1);
1 row created.
SQL>
SQL> CREATE TABLE product (
  2       product_name     VARCHAR2(25) PRIMARY KEY,
  3       product_price    NUMBER(4,2),
  4       quantity_on_hand NUMBER(5,0),
  5       last_stock_date  DATE
  6       );
Table created.
SQL>
SQL> INSERT INTO product VALUES ("Product 1", 99,  1,    "15-JAN-03");
1 row created.
SQL> INSERT INTO product VALUES ("Product 2", 75,  1000, "15-JAN-02");
1 row created.
SQL> INSERT INTO product VALUES ("Product 3", 50,  100,  "15-JAN-03");
1 row created.
SQL> INSERT INTO product VALUES ("Product 4", 25,  10000, null);
1 row created.
SQL> INSERT INTO product VALUES ("Product 5", 9.95,1234, "15-JAN-04");
1 row created.
SQL> INSERT INTO product VALUES ("Product 6", 45,  1,    TO_DATE("December 31, 2008, 11:30 P.M.","Month dd, YYYY, HH:MI P.M."));
1 row created.
SQL>
SQL> CREATE TABLE product_order_log2 AS
  2       SELECT purc.order_date,
  3              prod.product_name,
  4              prod.product_price,
  5              purc.quantity,
  6              pers.first_name,
  7              pers.last_name
  8       FROM   product  prod,
  9              person   pers,
 10              product_order purc
 11       WHERE  prod.product_name = purc.product_name
 12              AND
 13              pers.person_code = purc.salesperson;
Table created.
SQL>
SQL> SELECT * FROM product_order_log2;
ORDER_DAT PRODUCT_NAME              PRODUCT_PRICE   QUANTITY FIRST_NAME      LAST_NAME
--------- ------------------------- ------------- ---------- --------------- --------------------
14-JUL-03 Product 1                            99          1 Chase           At
14-JUL-03 Product 2                            75         75 Bob             Bark
14-JUL-03 Product 3                            50          2 Gary            Talor
15-JUL-03 Product 4                            25          8 Gary            Talor
15-JUL-03 Product 5                          9.95         20 Laren           Baby
16-JUL-03 Product 6                            45          5 Chase           At
6 rows selected.
SQL>
SQL> drop table product_order;
Table dropped.
SQL>
SQL> drop table product;
Table dropped.
SQL>
SQL> drop table person;
Table dropped.
SQL>
SQL> drop table product_order_log2;
Table dropped.

Create copy table

SQL>
SQL>
SQL> -- create demo table
SQL> create table Employee(
  2    ID                 VARCHAR2(4 BYTE)         NOT NULL primary key,
  3    First_Name         VARCHAR2(10 BYTE),
  4    Last_Name          VARCHAR2(10 BYTE),
  5    Start_Date         DATE,
  6    End_Date           DATE,
  7    Salary             Number(8,2),
  8    City               VARCHAR2(10 BYTE),
  9    Description        VARCHAR2(15 BYTE)
 10  )
 11  /
Table created.
SQL>
SQL> -- prepare data
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2               values ("01","Jason",    "Martin",  to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto",  "Programmer")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values("02","Alison",   "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values("03","James",    "Smith",   to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values("04","Celia",    "Rice",    to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values("05","Robert",   "Black",   to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                values("06","Linda",    "Green",   to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York",  "Tester")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                values("07","David",    "Larry",   to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York",  "Manager")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                values("08","James",    "Cat",     to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
  3  /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
  2  /

ID   FIRST_NAME           LAST_NAME            START_DAT END_DATE      SALARY CITY       DESCRIPTION
---- -------------------- -------------------- --------- --------- ---------- ---------- ---------------
01   Jason                Martin               25-JUL-96 25-JUL-06    1234.56 Toronto    Programmer
02   Alison               Mathews              21-MAR-76 21-FEB-86    6661.78 Vancouver  Tester
03   James                Smith                12-DEC-78 15-MAR-90    6544.78 Vancouver  Tester
04   Celia                Rice                 24-OCT-82 21-APR-99    2344.78 Vancouver  Manager
05   Robert               Black                15-JAN-84 08-AUG-98    2334.78 Vancouver  Tester
06   Linda                Green                30-JUL-87 04-JAN-96    4322.78 New York   Tester
07   David                Larry                31-DEC-90 12-FEB-98    7897.78 New York   Manager

ID   FIRST_NAME           LAST_NAME            START_DAT END_DATE      SALARY CITY       DESCRIPTION
---- -------------------- -------------------- --------- --------- ---------- ---------- ---------------
08   James                Cat                  17-SEP-96 15-APR-02    1232.78 Vancouver  Tester
8 rows selected.
SQL>
SQL> create table emp_copy as
  2  select *
  3  from employee;
Table created.
SQL>
SQL>
SQL> select * from emp_copy;

ID   FIRST_NAME           LAST_NAME            START_DAT END_DATE      SALARY CITY       DESCRIPTION
---- -------------------- -------------------- --------- --------- ---------- ---------- ---------------
01   Jason                Martin               25-JUL-96 25-JUL-06    1234.56 Toronto    Programmer
02   Alison               Mathews              21-MAR-76 21-FEB-86    6661.78 Vancouver  Tester
03   James                Smith                12-DEC-78 15-MAR-90    6544.78 Vancouver  Tester
04   Celia                Rice                 24-OCT-82 21-APR-99    2344.78 Vancouver  Manager
05   Robert               Black                15-JAN-84 08-AUG-98    2334.78 Vancouver  Tester
06   Linda                Green                30-JUL-87 04-JAN-96    4322.78 New York   Tester
07   David                Larry                31-DEC-90 12-FEB-98    7897.78 New York   Manager

ID   FIRST_NAME           LAST_NAME            START_DAT END_DATE      SALARY CITY       DESCRIPTION
---- -------------------- -------------------- --------- --------- ---------- ---------- ---------------
08   James                Cat                  17-SEP-96 15-APR-02    1232.78 Vancouver  Tester
8 rows selected.
SQL> drop table emp_copy;
Table dropped.
SQL>
SQL> -- clean the table
SQL> drop table Employee
  2  /
Table dropped.
SQL>
SQL>

Create intermediate table for calculation

SQL> create table emp(
  2           emp_no                 integer         primary key,
  3           lastname               varchar2(20)    not null,
  4           firstname              varchar2(15)    not null,
  5           midinit                varchar2(1),
  6           street                 varchar2(30),
  7           city                   varchar2(20),
  8           state                  varchar2(2),
  9           zip                    varchar2(5),
 10           shortZipCode                   varchar2(4),
 11           area_code              varchar2(3),
 12           phone                  varchar2(8),
 13           salary                 number(5,2),
 14           birthdate              date,
 15           startDate              date,
 16           title                  varchar2(20),
 17           dept_no                integer,
 18           mgr                    integer,
 19           region                 number,
 20           division               number,
 21           total_sales            number
 22  );
Table created.
SQL> -- emp Table Inserts:
SQL> insert into emp(emp_no, lastname, firstname, midinit, street, city, state, zip,shortZipCode, area_code, phone, birthdate, title)values
  2                      (1,"Z","Joy","R","1 Ave","New York","NY","12122","2333","212","200-1111","12-nov-1976","President");
1 row created.
SQL> insert into emp(emp_no, lastname, firstname, midinit, street, city, state, zip,shortZipCode, area_code, phone, salary, birthdate, startDate,title, dept_no, mgr, region, division, total_sales)valu
es
  2                      (2,"X","Lucy","J","1 Street","New York","NY","43552","6633","212","234-4444",7.75,"21-mar-1976","1-feb-1994","Sales Manager",2,1,100,10,40000);
1 row created.
SQL> insert into emp(emp_no, lastname, firstname, midinit, street, city, state, zip,shortZipCode, area_code, phone, salary, birthdate, startDate,title, dept_no, mgr, region, division, total_sales)valu
es
  2                      (3,"Y","Jordan","E","1 Drive","New York","NY","76822","8763","212","222-2222",7.75,"14-feb-1963","15-mar-1995","Sales Clerk",2,2,100,10,10000);
1 row created.
SQL>
SQL> create table avg_sal
  2  as select avg(salary) AS avg_Sal from emp;

SQL>
SQL>
SQL> select lastname, salary,
  2  CASE WHEN salary > avg_sal THEN "> Average of " || to_char(avg_sal, "99.99")
  3       WHEN salary < avg_sal THEN "< Average of " || to_char(avg_sal, "99.99")
  4       ELSE "= Average of " || to_char(avg_sal, "99.99")
  5       END
  6  from emp, avg_sal
  7  /

Create table with "organization index"

SQL>
SQL> create table t1
  2  (  x int primary key,
  3     y varchar2(25),
  4     z date
  5  )
  6  organization index;
Table created.
SQL>
SQL> drop table t1;
Table dropped.

Create table with "organization index OVERFLOW"

SQL>
SQL> create table t2
  2  (  x int primary key,
  3     y varchar2(25),
  4     z date
  5  )
  6  organization index
  7  OVERFLOW;
Table created.
SQL>
SQL> drop table t2;
Table dropped.
SQL>
SQL>

Create table with storage setting

SQL> create table t ( x int ) storage ( FREELISTS 2 );
Table created.
SQL>
SQL> alter table t storage ( FREELISTS 2 );
Table altered.
SQL>
SQL>
SQL>
SQL> drop table t;
Table dropped.

Create table with storage setting 2

SQL>  create table my_hash_table (
  2      name       varchar2(30),
  3      value   varchar2(4000) )
  4    tablespace users
  5    storage (
  6      initial     1M
  7      next        512K
  8      pctincrease 0
  9      minextents  2
 10      maxextents  unlimited )
 11    /
Table created.
SQL> drop table my_hash_table;
Table dropped.

Creating a Table

The simplified syntax for the CREATE TABLE statement is as follows:

CREATE [GLOBAL TEMPORARY] TABLE table_name (
  column_name type [CONSTRAINT constraint_def DEFAULT default_exp]
  [, column_name type [CONSTRAINT constraint_def DEFAULT default_exp]...]
)
[ON COMMIT {DELETE | PRESERVE} ROWS]
TABLESPACE table_space;

where

1. GLOBAL TEMPORARY specifies that the table"s rows are temporary and such tables are known as temporary tables.
2. The duration of the contents are specified by the ON COMMIT clause.
3. A temporary table is visible to all sessions, but rows are specific to a session.
4. type specifies the type of a column.
5. constraint_def specifies the definition of a constraint on a column.
6. default_exp specifies the expression used to assign a default value to a column.
7. ON COMMIT controls the duration of the rows in a temporary table.
8. DELETE specifies the rows are deleted at the end of a transaction.
9. PRESERVE specifies the rows are deleted at the end of a session.
10. If you omit ON COMMIT for a temporary table, the default is DELETE.

Demonstrate a simple External table

SQL>
SQL> create directory external_tables as "C:\Oracle\oradata\external";
Directory created.
SQL>
SQL>
SQL> -- Create the external table:
SQL> create table student_emails_ext
  2   (id         char,
  3   firstname    varchar(40),
  4    lastname     varchar(40),
  5    email        varchar(80) )
  6  organization external
  7  (
  8    type oracle_loader
  9    default directory external_tables
 10    location ("students_test.txt")
 11  ) reject limit unlimited
 12  /
Table created.


SQL>
SQL>
SQL>
SQL> drop directory external_tables;
Directory dropped.
SQL>
SQL>
SQL> drop table student_emails_ext;
Table dropped.

Make myCode a CACHE table.

SQL>
SQL> create table myCode
  2  (
  3    codeValue  VARCHAR2(1) primary key,
  4    Description VARCHAR2(25)
  5  );
Table created.
SQL>
SQL> alter table myCode cache;
Table altered.
SQL>
SQL> drop table myCode;
Table dropped.

One column with three constraints

SQL>
SQL> create table departments
  2  ( dname  VARCHAR2(10)  constraint D_DNAME_NN
  3                         not null
  4                         constraint D_DNAME_UN
  5                         unique
  6                         constraint D_DNAME_CHK
  7                         check (dname = upper(dname))
  8  ) ;
Table created.
SQL>
SQL> drop table departments;
Table dropped.
SQL>

Rename a table

SQL>
SQL> CREATE TABLE product (
  2       product_name     VARCHAR2(25) PRIMARY KEY,
  3       product_price    NUMBER(4,2),
  4       quantity_on_hand NUMBER(5,0),
  5       last_stock_date  DATE
  6       );
Table created.
SQL>
SQL> INSERT INTO product VALUES ("Product 1", 99,  1,    "15-JAN-03");
1 row created.
SQL> INSERT INTO product VALUES ("Product 2", 75,  1000, "15-JAN-02");
1 row created.
SQL> INSERT INTO product VALUES ("Product 3", 50,  100,  "15-JAN-03");
1 row created.
SQL> INSERT INTO product VALUES ("Product 4", 25,  10000, null);
1 row created.
SQL> INSERT INTO product VALUES ("Product 5", 9.95,1234, "15-JAN-04");
1 row created.
SQL> INSERT INTO product VALUES ("Product 6", 45,  1,    TO_DATE("December 31, 2008, 11:30 P.M.","Month dd, YYYY, HH:MI P.M."));
1 row created.
SQL>
SQL> RENAME product TO log;
Table renamed.
SQL>
SQL> select * from log;
PRODUCT_NAME              PRODUCT_PRICE QUANTITY_ON_HAND LAST_STOC
------------------------- ------------- ---------------- ---------
Product 1                            99                1 15-JAN-03
Product 2                            75             1000 15-JAN-02
Product 3                            50              100 15-JAN-03
Product 4                            25            10000
Product 5                          9.95             1234 15-JAN-04
Product 6                            45                1 31-DEC-08
6 rows selected.
SQL>
SQL> drop table log;
Table dropped.
SQL>

Use referencing columns

SQL>
SQL> create table I1(n number primary key, v varchar2(10));

SQL> create table I2(n number primary key, v varchar2(10));

SQL>
SQL> create table MAP
  2  (n number primary key,
  3   i1 number referencing I1(n),
  4   i2 number referencing I2(n));
SQL>
SQL> create unique index IDX_MAP on MAP(i1, i2);
SQL>
SQL> insert into i1
  2  select rownum, rpad("*",10,"*") from all_objects;
12651 rows created.
SQL>
SQL> insert into i2
  2  select rownum, rpad("*",10,"*") from all_objects;
12651 rows created.
SQL>
SQL> insert into map
  2  select rownum, rownum, rownum from all_objects;
12651 rows created.
SQL>
SQL>
SQL>
SQL> select *
  2    from i1, map, i2
  3   where  i1.n = map.i1
  4     and i2.n = map.i2
  5     and i1.v = "x"
  6     and i2.v = "y";
no rows selected
SQL>
SQL>
SQL> drop table i1 cascade constraint;
Table dropped.
SQL> drop table map cascade constraint;
Table dropped.
SQL> drop table i2 cascade constraint;
Table dropped.