Oracle資料庫資料物件分析(轉)

Oracle資料庫資料物件分析(轉)[@more@]

　　Oracle資料庫資料物件中最基本的是表和檢視，其他還有約束、序列、函式、儲存過程、包、觸發器等。對資料庫的操作可以基本歸結為對資料物件的操作,理解和掌握Oracle資料庫物件是學習Oracle的捷徑。

　　表和檢視

　　Oracle中表是資料儲存的基本結構。ORACLE8引入了分割槽表和物件表，ORACLE8i引入了臨時表，使表的功能更強大。檢視是一個或多個表中資料的邏輯表示式。本文我們將討論怎樣建立和管理簡單的表和檢視。

　　管理表

　　表可以看作有行和列的電子資料表，表是關聯式資料庫中一種擁有資料的結構。用CREATE TABLE語句建立表，在建立表的同時，必須定義表名，列，以及列的資料型別和大小。例如：

　　CREATE TABLE products　　( PROD_ID NUMBER(4),　　　PROD_NAME VAECHAR2(20),　　　STOCK_QTY NUMBER(5,3)　　);

　　這樣我們就建立了一個名為products的表， 關鍵詞CREATE TABLE後緊跟的表名，然後定義了三列，同時規定了列的資料型別和大小。

　　在建立表的同時你可以規定表的完整性約束，也可以規定列的完整性約束，在列上普通的約束是NOT NULL,關於約束的討論我們在以後進行。

　　在建立或更改表時，可以給表一個預設值。預設值是在增加行時，增加的資料行中某一項值為null時，oracle即認為該值為預設值。

　　下列資料字典檢視提供表和表的列的資訊：

　　. DBA_TABLES

　　. DBA_ALL_TABLES

　　. USER_TABLES

　　. USER_ALL_TABLES

　　. ALL_TABLES

　　. ALL_ALL_TABLES

　　. DBA_TAB_COLUMNS

　　. USER_TAB_COLUMNS

　　. ALL_TAB_COLUMNS

　　表的命名規則

　　表名標識一個表，所以應儘可能在表名中描述表，oracle中表名或列名最長可以達30個字串。表名應該以字母開始，可以在表名中包含數字、下劃線、#、$等。

　　從其它表中建立表

　　可以使用查詢從基於一個或多個表中建立表，表的列的資料型別和大小有查詢結果決定。建立這種形式的表的查詢可以選擇其他表中所有的列或者只選擇部分列。在CREATE TABLE語句中使用關鍵字AS，例如:

　　SQL>CREATE TABLE emp AS SELECT * FROM employeeTABLE CREATEDSQL> CREATE TABLE Y AS SELECT * FROM X WHERE no=2

　　需要注意的是如果查詢涉及LONG資料型別，那麼CREATE TABLE....AS SELECT....將不會工作。

　　更改表定義

　　在建立表後，有時候我們可能需要修改表，比如更改列的定義，更改預設值，增加新列，刪除列等等。ORACLE使用ALTER TABLE語句來更改表的定義

　　1、增加列

　　語法：

　　ALTER TABLE [schema.] table_name ADD column_definition

　　例：

　　ALTER TABLE orders ADD order_date DATE;TABLE ALTER

　　對於已經存在的資料行，新列的值將是NULL.

　　2、更改列

　　語法：

　　ALTER TABLE [schema.] table_name MODIFY column_name new_attributes;

　　例：

　　ALTER TABLE orders MODITY (quantity number(10,3),status varchar2(15));

　　這個例子中我們修改了表orders，將STATUS列的長度增加到15,將QUANTITY列減小到10,3;

　　修改列的規則如下：

　　. 可以增加字串資料型別的列的長度，數字資料型別列的精度。

　　. 減少列的長度時，該列應該不包含任何值，所有資料行都為NULL.

　　. 改變資料型別時，該列的值必須是NULL.

　　. 對於十進位制數字，可以增加或減少但不能降低他的精度。

　　3、刪除資料列

　　最佳化ORACLE資料庫，唯一的方法是刪除列，重新建立資料庫。在ORACLE8i中有很多方法刪除列，你可以刪除未用資料列或者可以標示該列為未用資料列然後刪除。

　　刪除資料列的語法是：

　　ALTER TABLE [schema.] table_name DROP {COLUM column_names | (column_names)}[CASCADE CONSTRAINS]

　　要注意的是在刪除列時關於該列的索引和完整性約束也同時刪除。注意關鍵字CASCADE CONSTRAINS，如果刪除的列是多列約束的一部分，那麼這個約束條件相對於其他列也同時刪除。

　　如果使用者擔心在大型資料庫中刪除列要花太多時間，可以先將他們標記為未用資料列，標記未用資料列的語法如下：

　　ALTER TABLE [schema.] table_name SET UNUSED {COLUM column_names | (column_names)}[CASCADE CONSTRAINS]

　　這個語句將一個或多個資料列標記為未用資料列，但並不刪除資料列中的資料，也不釋放佔用的磁碟空間。但是，未用資料列在檢視和資料字典中並不顯示，並且該資料列的名稱將被刪除，新的資料列可以使用這個名稱。基於該資料列的索引、約束，統計等都將被刪除。

　　刪除未用資料列的語句是：

　　ALTER TABLE [schema.] table_name DROP {UNUSED COLUM | COLUMN CONTINUE}刪除表和更改表名

　　刪除表非常簡單，但它是一個不可逆轉的行為。

　　語法：

　　DROP TABLE [schema.] table_name [CASCADE CONSTRAINTS]　　

　　刪除表後，表上的索引、觸發器、許可權、完整性約束也同時刪除。ORACLE不能刪除檢視，或其他程式單元，但oracle將標示他們無效。如果刪除的表涉及引用主鍵或唯一關鍵字的完整性約束時，那麼DROP TABLE語句就必須包含CASCADE CONSTRAINTS子串。

　　更改表名

　　RENAME命令用於給表和其他資料庫物件改名。ORACLE系統自動將基於舊錶的完整性約束、索引、許可權轉移到新表中。ORACLE同時使所有基於舊錶的資料庫物件，比如檢視、程式、函式等，為不合法。

　　語法：

　　RENAME old_name TO new_name;

　　例：

　　SQL> RENAME orders TO purchase_orders;TABLE RENAMED

　　截短表

　　TRUNCATE命令與DROP命令相似， 但他不是刪除整個資料表，所以索引、完整性約束、觸發器、許可權等都不會被刪除。預設情況下將釋放部分表和檢視空間，如果使用者不希望釋放表空間，TRUNCATE語句中要包含REUSE STORAGE子串。TRUNCATE命令語法如下：

　　TRUNCATE {TABLE|CLUSTER} [schema.] name {DROP|REUSE STORAGE}

　　例：

　　SQL> TRUNCATE TABLE t1;TABLE truncate.

　　管理檢視

　　檢視是一個或多個表中的資料的簡化描述，使用者可以將檢視看成一個儲存查詢（stored query)或一個虛擬表(virtual table).查詢僅僅儲存在oracle資料字典中，實際的資料沒有存放在任何其它地方，所以建立檢視不用消耗其他的空間。檢視也可以隱藏複雜查詢，比如多表查詢，但使用者只能看見檢視。檢視可以有與他所基於表的列名不同的列名。使用者可以建立限制其他使用者訪問的檢視。

　　建立檢視

　　CREATE VIEW命令建立檢視，定義檢視的查詢可以建立在一個或多個表，或其他檢視上。查詢不能有FOR UPDATE子串，在早期的ORACLE8i版本中不支援ORDER BY子串，現在的版本中CREATE VIEW可以擁有ORDER BY子串。

　　例：

　　SQL> CREATE VIEW TOP_EMP ASSELECT empno EMPLOYEE_ID,ename EMPLOYEE_NAME,salaryFROM empWHERE salary >2000

　　使用者可以在建立檢視的同時更改列名，方法是在檢視名後立即加上要命名的列名。重新定義檢視需要包含OR REPLACE子串。

　　SQL> CREATE VIEW TOP_EMP（EMPLOYEE_ID，EMPLOYEE_NAME，SALARY) ASSELECT empno ,ename ,salaryFROM empWHERE salary >2000

　　如果在建立的檢視包含錯誤在正常情況下，檢視將不會被建立。但如果你需要建立一個帶錯誤的檢視必須在CREATE VIEW語句中帶上FORCE選項。如：

　　CREATE FORCE VIEW ORDER_STATUS ASSELECT * FROM PURCHASE_ORDERSWHERE STATUS='APPPOVE';SQL>/warning :View create with compilation errors

　　這樣將建立了一個名為ORDER_STATUS的檢視，但這樣的檢視的狀態是不合法的，如果以後狀態發生變化則可以重新編譯，其狀態也變成合法的。

　　從檢視中獲得資料

　　從檢視中獲得資料與從表中獲得資料基本一樣，使用者可以在連線和子查詢中使用檢視，也可以使用SQL函式，以及所有SELECT語句的字串。

　　插入、更新、刪除資料

　　使用者在一定的限制條件下可以透過檢視更新、插入、刪除資料。如果檢視連線多個表，那麼在一個時間裡只能更新一個表。所有的能被更新的列可以在資料字典USER_UPDATETABLE_COLUMNS中查到。

　　使用者在CREATE VIEW中可以使用了WITH子串。WITH READ ONLY子串表示建立的檢視是一個只讀檢視，不能進行更新、插入、刪除操作。WITH CHECK OPTION表示可以進行插入和更新操作，但應該滿足WHERE子串的條件。這個條件就是建立檢視WHERE子句的條件，比如在上面的例子中使用者建立了一個檢視TOP_EMP,在這個檢視中使用者不能插入salary小於2000的資料行。

　　刪除檢視

　　刪除檢視使用DROP VIEW命令。同時將檢視定義從資料字典中刪除，基於檢視的許可權也同時被刪除，其他涉及到該檢視的函式、檢視、程式等都將被視為非法。

　　例：

　　DROP VIEW TOP_EMP;完整性約束

　　完整性約束用於增強資料的完整性，Oracle提供了5種完整性約束：

　　Check

　　NOT NULL

　　Unique

　　Primary

　　Foreign key

　　完整性約束是一種規則，不佔用任何資料庫空間。完整性約束存在資料字典中，在執行SQL或PL/SQL期間使用。使用者可以指明約束是啟用的還是禁用的，當約束啟用時，他增強了資料的完整性，否則，則反之，但約束始終存在於資料字典中。

　　禁用約束，使用ALTER語句

　　ALTER TABLE table_name DISABLE CONSTRAINT constraint_name;

　　或

　　ALTER TABLE policies DISABLE CONSTRAINT chk_gender

　　如果要重新啟用約束：

　　ALTER TABLE policies ENABLE CONSTRAINT chk_gender

　　刪除約束

　　ALTER TABLE table_name DROP CONSTRAINT constraint_name

　　或

　　ALTER TABLE policies DROP CONSTRAINT chk_gender;

　　Check 約束

　　在資料列上Check 約束需要 一個特殊的布林條件或者將資料列設定成TRUE，至少一個資料列的值是NULL，Check約束用於增強表中資料內容的簡單的商業規則。使用者使用Check約束保證資料規則的一致性。Check約束可以涉及該行同屬Check約束的其他資料列但不能涉及其他行或其他表，或呼叫函式SYSDATE,UID,USER,USERENV。如果使用者的商業規則需要這類的資料檢查，那麼可以使用觸發器。Check約束不保護LOB資料型別的資料列和物件、巢狀表、VARRY、ref等。單一資料列可以有多個Check約束保護，一個Check約束可以保護多個資料列。

　　建立表的Check約束使用CREATE TABLE語句，更改表的約束使用ALTER TABLE語句。

　　語法：

　　CONSTRAINT [constraint_name] CHECK (condition);

　　Check約束可以被建立或增加為一個表約束，當Check約束保護多個資料列時，必須使用表約束語法。約束名是可選的並且如果這個名字不存在，那麼oracle將產生一個以SYS_開始的唯一的名字。

　　例：

　　CREATE TABLE policies(policy_id NUMBER,holder_name VARCHAR2(40),gender VARCHAR2(1) constraint chk_gender CHECK (gender in ('M','F'),marital_status VARCHAR2(1),date_of_birth DATE,constraint chk_marital CHECK (marital_status in('S','M','D','W')));

　　NOT NULL約束

　　NOT NULL約束應用在單一的資料列上，並且他保護的資料列必須要有資料值。預設狀況下，ORACLE允許任何列都可以有NULL值。某些商業規則要求某資料列必須要有值，NOT NULL約束將確保該列的所有資料行都有值。

　　例：

　　CREATE TABLE policies(policy_id NUMBER,holder_name VARCHAR2(40) NOT NULL,gender VARCHAR2(1),marital_status VARCHAR2(1),date_of_birth DATE NOT NULL);

　　對於NOT NULL的ALTER TABLE語句與其他約束稍微有點不同。

　　ALTER TABLE policies MODIFY holder_name NOT NULL唯一性約束(Unique constraint)

　　唯一性約束可以保護表中多個資料列，保證在保護的資料列中任何兩行的資料都不相同。唯一性約束與表一起建立，在唯一性約束建立後，可以使用ALTER TABLE語句修改。

　　語法：

　　column_name data_type CONSTRAINT constraint_name UNIQUE

　　如果唯一性約束保護多個資料列，那麼唯一性約束要作為表約束增加。語法如下：

　　CONSTRAINT constraint_name (column) UNIQUE USING INDEX TABLESPACE (tablespace_name) STORAGE (stored clause)

　　唯一性約束由一個B-tree索引增強，所以可以在USING子串中為索引使用特殊特徵，比如表空間或儲存引數。CREATE TABLE語句在建立唯一性約束的同時也給目標資料列建立了一個唯一的索引。

　　CREATE TABLE insured_autos(policy_id NUMBER CONSTRAINT pk_policies PRIMARY KEY,vin VARCHAR2(10),coverage_begin DATE,coverage_term NUMBER,CONSTRAIN unique_auto UNIQUE (policy_id,vin) USING INDEX TABLESPACE index STORAGE (INITIAL 1M NEXT 10M PCTINCREASE 0));

　　使用者可以禁用未以性約束，但他仍然存在，禁用唯一性約束使用ALTER TABLE 語句

　　ALTER TABLE insured_autos DISABLE CONSTRAIN unique_name;

　　刪除唯一性約束，使用ALTER TABLE....DROP CONSTRAIN語句

　　ALTER TABLE insured_autos DROP CONSTRAIN unique_name;

　　注意使用者不能刪除在有外部鍵指向的表的唯一性約束。這種情況下使用者必須首先禁用或刪除外部鍵（foreign key)。

　　刪除或禁用唯一性約束通常同時刪除相關聯的唯一索引，因而降低了資料庫效能。經常刪除或禁用唯一性約束有可能導致丟失索引帶來的效能錯誤。要避免這樣錯誤，可以採取下面的步驟：

　　1、在唯一性約束保護的資料列上建立非唯一性索引。

　　2、新增唯一性約束

　　主鍵(Primary Key)約束

　　表有唯一的主鍵約束。表的主鍵可以保護一個或多個列，主鍵約束可與NOT NULL約束共同作用於每一資料列。NOT NULL約束和唯一性約束的組合將保證主鍵唯一地標識每一行。像唯一性約束一樣，主鍵由B-tree索引增強。

　　建立主鍵約束使用CREATE TABLE語句與表一起建立，如果表已經建立了，可以使用ALTER TABLE語句。

　　CREATE TABLE policies(policy_id NUMBER CONSTRAINT pk_policies PRIMARY KEY,holder_name VARCHAR2(40),gender VARCHAR2(1),marital_status VARCHAR2(1),date_of_birth DATE);

　　與唯一性約束一樣，如果主鍵約束保護多個資料列，那麼必須作為一個表約束建立。

　　CREATE TABLE insured_autos(policy_id NUMBER,vin VARCHAR2(40),coverage_begin DATE,coverage_term NUMBER,CONSTRAINT pk_insured_autos PRIMARY KEY (policy_id,vin)USING INDEX TABLESPACE indexSTORAGE (INITIAL 1M NEXT 10M PCTINCREASE 0));

　　禁用或刪除主鍵必須與ALTER TABLE 語句一起使用

　　ALTER TABLE policies DROP PRIMARY KEY;

　　或

　　ALTER TABLE policies DISABLE PRIMARY KEY;

　　外部鍵約束（Foreign key constraint)

　　外部鍵約束保護一個或多個資料列，保證每個資料行的資料包含一個或多個null值，或者在保護的資料列上同時擁有主鍵約束或唯一性約束。引用（主鍵或唯一性約束）約束可以保護同一個表，也可以保護不同的表。與主鍵和唯一性約束不同外部鍵不會隱式建立一個B-tree索引。在處理外部鍵時，我們常常使用術語父表（parent table)和子表（child table),父表表示被引用主鍵或唯一性約束的表，子表表示引用主鍵和唯一性約束的表。

　　建立外部鍵使用CREATE TABLE語句，如果表已經建立了，那麼使用ALTER TABLE語句。

　　CREATE TABLE insured_autos(policy_id NUMBER CONSTRAINT policy_fkREFERENCE policies(policy_idON DELETE CASCADE,vin VARCHAR2(40),coverage_begin DATE,coverage_term NUMBER,make VARCHAR2(30),model VARCHAR(30),year NUMBER,CONSTRAIN auto_fk FROEIGN KEY (make,model,year)REFERENCES automobiles (make,model,year)ON DELETE SET NULL);

　　ON DELETE子串告訴ORACLE如果父紀錄（parent record)被刪除後，子記錄做什麼。預設情況下禁止在子記錄還存在的情況下刪除父紀錄。

　　外部鍵和NULL值

　　在外部鍵約束保護的資料列中NULL值的處理可能產生不可預料的結果。ORACLE 使用ISO standar Match None規則增強外部鍵約束。這個規則規定如果任何外部鍵作用的資料列包含有一個NULL值，那麼任何保留該鍵的資料列在父表中沒有匹配值。

　　比如，在父表AUTOMOBILES中，主鍵作用於資料列MAKE，MODEL，YEAR上，使用者使用的表INSURED_AUTOS有一個外部約束指向AOTOMOBILES，注意在INSURES_AUTOS中有一資料行的MODEL列為NULL值，這一行資料已經透過約束檢查，即使MAKE列也沒有顯示在父表AUTOMOBILES中，如下表：

　　表1 AUTOMOBILES

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/10748419/viewspace-953937/，如需轉載，請註明出處，否則將追究法律責任。