Oracle 資料庫/表

架構

Oracle 架構考慮每個伺服器一個數據庫，其中我們可以找到多個表空間，等效於 MySQL 和 MS-SQL 資料庫物件，包含表和儲存過程。

在 Windows Express 版本中，這些資料儲存在 C:\oraclexe\app\oracle\oradata\XE 中。

這些變數和關鍵字對大小寫不敏感。

建立表空間

連線後，就可以直接在預設表空間中開始建立一些表。但是之前，我們可以將一些表空間新增到一些定義的檔案中

   CREATE TABLESPACE Wikibooks
   DATAFILE 'C:\oraclexe\app\oracle\oradata\XE\Wikibooks.dbf' size 10M reuse
   DEFAULT STORAGE (INITIAL 10K NEXT 50K MINEXTENTS 1 MAXEXTENTS 999) 
   ONLINE;

建立模式

模式是授予一組元素 ^[1] 的許可權，例如表和儲存過程。關鍵字 AUTHORIZATION 指定使用者名稱

CREATE SCHEMA AUTHORIZATION root
  CREATE TABLE TableName1...
  CREATE TABLE TableName2...
;

建立表

示例

CREATE TABLE client1 (last VARCHAR(10), first VARCHAR(10), address VARCHAR(20));

Table created.

在 SQL Developer 中，右鍵單擊表，選擇“新建表...”，我們可以以陣列的形式生成和執行此建立，該陣列在 DDL 選項卡中轉換為 PL/SQL

CREATE TABLE client1
( id INT NOT NULL 
, last VARCHAR2(50) 
, first VARCHAR2(50) 
, address VARCHAR2(255) 
, CONSTRAINT client1_PK PRIMARY KEY (ID) ENABLE
) TABLESPACE Wikibooks;

我們也可以透過在 GUI 中選擇表空間或在建立語句中使用關鍵字 TABLESPACE 來設定表表空間。

可用資料型別

可能的列型別有：^[2]

字元
1. CHAR：2 kB。
2. VARCHAR：4 kB。
3. VARCHAR2：4 kB，與 VARCHAR 同義。
4. NCHAR：2 kB。
5. NVARCHAR2：4 kB。
數字
1. NUMBER.
2. BINARY_INTEGER.
3. BINARY_FLOAT.
4. BINARY_DOUBLE.
日期
1. DATE.
2. TIMESTAMP.
RAW.
LONG RAW.
BLOB.
CLOB.
NCLOB.
ROWID.
UROWID.
BFILE.
XMLType.
UriType.

列出表

以下系統檢視可以顯示系統表和使用者表

SELECT owner, table_name FROM all_tables;

管理表內容

插入行

INSERT INTO client1 (id, last, first, address) VALUES (1, 'Doe', 'Jane', 'UK');

1 line created.

多行

INSERT ALL
 INTO client1 (id, last, first, address) VALUES (2, 'Doe', 'Jack', 'US')
 INTO client1 (id, last, first, address) VALUES (3, 'Doe', 'John', 'US')
 SELECT 1 FROM DUAL;

2 lines created.

DUAL 表是 Oracle 中預設存在的特殊的一行一列表，因為它用於 SELECT 語句需要 FROM 語句，但一些查詢不需要任何表。

更新行

UPDATE client1 SET address = 'US' WHERE id = 1;

刪除行

DELETE client1 WHERE ID = 2;

讀取表

結構

要獲取其結構，我們可以使用函式 desc（描述）或系統檢視 ALL_TAB_COLUMNS。

desc

desc client1;

Name    NULL     Type          
------- -------- ------------- 
ID      NOT NULL NUMBER(38)    
LAST             VARCHAR2(10)  
FIRST            VARCHAR2(10)  
ADDRESS          VARCHAR2(20)

如果表不存在，則會發生以下錯誤：ORA-00923: 缺少預期位置的 FROM 關鍵字。

ALL_TAB_COLUMNS

 SELECT * FROM ALL_TAB_COLUMNS WHERE TABLE_NAME = "client1"

OWNER
--------------------------------------------------------------------------------
TABLE_NAME
--------------------------------------------------------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
DATA_TYPE
--------------------------------------------------------------------------------
DAT
---
DATA_TYPE_OWNER
--------------------------------------------------------------------------------
DATA_LENGTH DATA_PRECISION DATA_SCALE N  COLUMN_ID DEFAULT_LENGTH
----------- -------------- ---------- - ---------- --------------
DATA_DEFAULT
--------------------------------------------------------------------------------
NUM_DISTINCT
------------
LOW_VALUE
--------------------------------------------------------------------------------
HIGH_VALUE
--------------------------------------------------------------------------------
   DENSITY  NUM_NULLS NUM_BUCKETS LAST_ANA SAMPLE_SIZE
---------- ---------- ----------- -------- -----------
CHARACTER_SET_NAME                           CHAR_COL_DECL_LENGTH GLO USE
-------------------------------------------- -------------------- --- ---
AVG_COL_LEN CHAR_LENGTH C V80 DAT HISTOGRAM       DEF IDE
----------- ----------- - --- --- --------------- --- ---
EVALUATION_EDITION
--------------------------------------------------------------------------------
UNUSABLE_BEFORE
--------------------------------------------------------------------------------
UNUSABLE_BEGINNING
--------------------------------------------------------------------------------

內容

要獲取其內容

SELECT * from client1;

LAST        FIRST     ADDRESS
---------- ---------- --------------------
Doe         Jane      UK

連字元的數量表示欄位大小。

索引

使用 CREATE TABLE 語句建立索引

建立基於函式的索引

約束

本質上，約束保護並驗證資料。

主鍵 (PK) 和唯一約束都確保資料不重複。PK 還確保資料不為空。Oracle 會自動為 PK 和唯一約束生成索引。一張表只能有一個 PK，但可以有多個唯一約束。

外部索引鍵 (FK) 確保資料存在於它所引用的父表列中。每個父記錄可以有多個子記錄，但每個子記錄只能關聯到一個父記錄。帶有 FK 的列不一定需要索引。

FK 只能引用具有 PK 或唯一約束的列。示例

    create table tblA (colX number, colY char);
    create table tblB (colX number);

    alter table tblB add (constraint colX_FK foreign key (colX) references tblA(colX));
    -- ORA-02270: no matching unique or primary key for this column-list

    alter table tblA add (constraint colX_PK primary key (colX));
    alter table tblB add (constraint colX_FK foreign key (colX) references tblA(colX));
    -- alter table success.

一張表只能有一個主鍵，但可以有多個唯一鍵。如果子表需要引用主鍵以外的列，那麼父表上的該列必須具有唯一約束。

    alter table tblA add (constraint colY_PK primary key (colY));
    -- ORA-02260: table can have only one primary key

不能在包含重複資料的列上建立 PK 或唯一鍵。

    insert into tblA values(1,'A');
    insert into tblA values(2,'A');
    alter table tblA add (constraint colY_UK unique (colY));
    -- ORA-02299: cannot validate (HR.COLY_UK) - duplicate keys found


    delete from tblA where colx = 2;
    alter table tblA add (constraint colY_UK unique (colY));
    -- alter table success.

    create table tblC (colY char);
    alter table tblC add (constraint colY_FK foreign key (colY) references tblA(colY));
    -- alter table success.

向帶有 FK 的列中插入資料時，該值必須已經存在於 FK 所引用的列中。

    insert into tblC values ('B');
    -- ORA-02291: integrity constraint (HR.COLY_FK) violated - parent key not found

    insert into tblC values ('A');
    -- 1 rows inserted

只要存在外部索引鍵，父表就可以截斷/刪除資料或停用 PK 或唯一約束。

    truncate table tblA;
    -- ORA-02266: unique/primary keys in table referenced by enabled foreign keys

在 Oracle 中查詢約束資訊

    desc all_constraints;

    select
      a.owner, a.table_name, a.constraint_name,
      a.constraint_type, a.status, a.r_owner, a.r_constraint_name,
      b.table_name as r_table_name, b.status as r_status
    from all_constraints a
      left join all_constraints b on a.owner = b.owner and a.r_constraint_name = b.constraint_name
    where a.table_name like 'TBL%';

    select *
    from all_cons_columns
    where table_name like 'TBL%';

停用有外部索引鍵引用的約束是不允許的，要執行此操作，必須先停用外部索引鍵。

    alter table tblA disable constraint colX_PK;
    -- ORA-02297: cannot disable constraint (HR.COLX_PK) - dependencies exist
    alter table tblA disable constraint colY_UK;
    -- ORA-02297: cannot disable constraint (HR.COLY_UK) - dependencies exist

    alter table tblC disable constraint colY_FK;
    alter table tblB disable constraint colX_FK;

    alter table tblA disable constraint colX_PK;
    alter table tblA disable constraint colY_UK;
    truncate table tblA;

如果父表中的資料被刪除，則不允許重新啟用包含對丟失資料引用的資料的 FK。

    select * from tblC;
    alter table tblA enable constraint colY_UK;
    alter table tblC enable constraint colY_FK;
    -- ORA-02298: cannot validate (HR.COLY_FK) - parent keys not found

生成 SQL 語句以停用指定表上的所有 FK

    select
      'alter table '||a.owner||'.'||a.table_name||
      ' disable constraint '||a.constraint_name||';' as STMT
    from all_constraints a, all_constraints b
    where a.constraint_type = 'R'
      and a.r_constraint_name = b.constraint_name
      and a.r_owner = b.owner
      and b.table_name = 'TBLA';

修改表結構

重新命名的示例

ALTER TABLE client1 RENAME to client2

新增第一個欄位值約束

ALTER TABLE client1 CHECK id > 1;

新增主鍵

ALTER TABLE client1 ADD CONSTRAINT client1_pk PRIMARY KEY (id);

刪除主鍵

ALTER TABLE client1 ADD PRIMARY KEY (id) DISABLE;

新增外部索引鍵

ALTER TABLE client1
ADD CONSTRAINT fk_client2
  FOREIGN KEY (client2_id)
  REFERENCES client2(id);

刪除表

DROP TABLE client1;

分割槽

Oracle 分割槽是一個將大型表拆分成多個較小表的流程，以提高效能。

範圍

示例

CREATE TABLE t_range 
( t1      VARCHAR2(10) NOT NULL,
  t2      NUMBER       NOT NULL,
  t3      NUMBER  
) 
PARTITION BY RANGE (t2) 
( PARTITION part1 VALUES LESS THAN (1),
  PARTITION part2 VALUES LESS THAN (11),
  PARTITION part3 VALUES LESS THAN (MAXVALUE)
);

雜湊

示例

CREATE TABLE t_hash 
( t1      VARCHAR2(10) NOT NULL,
  t2      NUMBER       NOT NULL,
  t3      NUMBER  
PARTITION BY HASH (t2)
PARTITIONS 4
;

列表

示例

CREATE TABLE t_list 
( ort     VARCHAR2(30) NOT NULL,
  t2      NUMBER,
  t3      NUMBER  
)
PARTITION BY LIST(ort) 
( PARTITION part_nord VALUES IN ('Hamburg','Berlin'),
  PARTITION part_sued VALUES IN ('Muenchen', 'Nuernberg'),
  PARTITION part_west VALUES IN ('Koeln','Duesseldorf'),
  PARTITION part_ost VALUES IN ('Halle'),
  PARTITION part_def VALUES (DEFAULT)
);

間隔

示例

CREATE TABLE t_interval 
( buchungs_datum  DATE NOT NULL,
  buchungs_text   VARCHAR2(100),
  betrag          NUMBER(10,2)
)  
PARTITION BY RANGE (buchungs_datum)
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
( PARTITION p_historie VALUES LESS THAN (TO_DATE('2014.01.01', 'YYYY.MM.DD')),
  PARTITION p_2014_01  VALUES LESS THAN (TO_DATE('2014.02.01', 'YYYY.MM.DD')),
  PARTITION p_2014_02  VALUES LESS THAN (TO_DATE('2014.03.01', 'YYYY.MM.DD'))
);

刪除列並設定列為 UNUSED

執行 FLASHBACK 操作

建立和使用外部表

參考文獻

[1] ttps://docs.oracle.com/cd/B12037_01/server.101/b10759/statements_6013.htm

[2] ttps://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm

[1]

[2]