Oracle PL/SQL Tutorial/Table/Create Table

Материал из SQL эксперт
Перейти к: навигация, поиск

Cascade delete setting

   <source lang="sql">

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></source>


Create an external table

   <source lang="sql">

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;</source>


Create a table based on a hash cluster

   <source lang="sql">

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.</source>


Create a table from one or more other tables

   <source lang="sql">

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.</source>


Create copy table

   <source lang="sql">

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></source>


Create intermediate table for calculation

   <source lang="sql">

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  /</source>
   
  

Create table with "organization index"

   <source lang="sql">

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.</source>


Create table with "organization index OVERFLOW"

   <source lang="sql">

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></source>


Create table with storage setting

   <source lang="sql">

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.</source>


Create table with storage setting 2

   <source lang="sql">

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.</source>


Creating a Table

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



   <source lang="sql">

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;</source>


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

   <source lang="sql">

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.</source>


Make myCode a CACHE table.

   <source lang="sql">

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.</source>


One column with three constraints

   <source lang="sql">

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></source>


Rename a table

   <source lang="sql">

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></source>


Use referencing columns

   <source lang="sql">

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.</source>