Oracle資料庫表與視圖

oracle|視圖|資料|資料庫 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 employee

TABLE CREATED

SQL> 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 AS
SELECT empno EMPLOYEE_ID,ename EMPLOYEE_NAME,salary
FROM emp
WHERE salary >2000
　　使用者可以在建立視圖的同時更改列名，方法是在視圖名後立即加上要命名的列名。重新定義視圖需要包含OR REPLACE子串。

SQL> CREATE VIEW TOP_EMP
（EMPLOYEE_ID，EMPLOYEE_NAME，SALARY) AS
SELECT empno ,ename ,salary
FROM emp
WHERE salary >2000
　　如果在建立的視圖包含錯誤在正常情況下，視圖將不會被建立。但如果你需要建立一個帶錯誤的視圖必須在CREATE VIEW語句中帶上FORCE選項。如：

CREATE FORCE VIEW ORDER_STATUS AS
SELECT * FROM PURCHASE_ORDERS
WHERE 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;