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

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;

對于已經存在的數據行，新列的值將是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;

截短表

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

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

例：

SQL> TRUNCATE TABLE t1;

管理視圖

視圖是一個或多個表中的數據的簡化描述，用戶可以將視圖看成一個存儲查詢(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;

過程和函數

過程和函數都以編譯后的形式存放在數據庫中，函數可以沒有參數也可以有多個參數并有一個返回值。過程有零個或多個參數，沒有返回值。函數和過程都可以通過參數列表接收或返回零個或多個值，函數和過程的主要區別不在于返回值，而在于他們的調用方式。過程是作為一個獨立執行語句調用的：

pay_involume(invoice_nbr,30,due_date);

函數以合法的表達式的方式調用：

order_volumn:=open_orders(SYSDATE,30);

創建過程的語法如下：

CREATE [ OR REPLACE] PROCEDURE [schema.]procedure_name

[parameter_lister]

{AS|IS}

declaration_section

BEGIN

executable_section

[EXCEPTION

exception_section]

END [procedure_name]

每個參數的語法如下：

paramter_name mode datatype [(:=|DEFAULT) value]

mode有三種形式：IN、OUT、INOUT。

IN表示在調用過程的時候，實際參數的取值被傳遞給該過程，形式參數被認為是只讀的，當過程結束時，控制會返回控制環境，實際參數的值不會改變。

OUT在調用過程時實際參數的取值都將被忽略，在過程內部形式參數只能是被賦值，而不能從中讀取數據，在過程結束后形式參數的內容將被賦予實際參數。

INOUT這種模式是IN和OUT的組合；在過程內部實際參數的值會傳遞給形式參數，形勢參數的值可讀也可寫，過程結束后，形勢參數的值將賦予實際參數。

創建函數的語法和過程的語法基本相同，唯一的區別在于函數有RETUREN子句

CREATE [ OR REPLACE] FINCTION [schema.]function_name

[parameter_list]

RETURN returning_datatype

{AS|IS}

declaration_section

BEGIN

executable_section

[EXCEPTION]

exception_section

END [procedure_name]

在執行部分函數必須有喲個或多個return語句。

在創建函數中可以調用單行函數和組函數，例如：

CREATE OR REPLACE FUNCTION my_sin(DegreesIn IN NUMBER)

RETURN NUMBER

IS

pi NUMBER=ACOS(-1);

RadiansPerDegree NUMBER;

BEGIN

RadiansPerDegree=pi/180;

RETURN(SIN(DegreesIn*RadiansPerDegree));

END;

包

包是一種將過程、函數和數據結構捆綁在一起的容器；包由兩個部分組成：外部可視包規范，包括函數頭，過程頭，和外部可視數據結構；另一部分是包主體(package body),包主體包含了所有被捆綁的過程和函數的聲明、執行、異常處理部分。

打包的PL/SQL程序和沒有打包的有很大的差異，包數據在用戶的整個會話期間都一直存在，當用戶獲得包的執行授權時，就等于獲得包規范中的所有程序和數據結構的權限。但不能只對包中的某一個函數或過程進行授權。包可以重載過程和函數，在包內可以用同一個名字聲明多個程序，在運行時根據參數的數目和數據類型調用正確的程序。

創建包必須首先創建包規范，創建包規范的語法如下：

CREATE [OR REPLACE] PACKAGE package_name

{AS|IS}

public_variable_declarations |

public_type_declarations |

public_exception_declarations |

public_cursor_declarations |

function_declarations |

procedure_specifications

END [package_name]

創建包主體使用CREATE PACKAGE BODY語句：

CREATE [OR REPLACE] PACKAGE BODY package_name

{AS|IS}

private_variable_declarations |

private_type_declarations |

private_exception_declarations |

private_cursor_declarations |

function_declarations |

procedure_specifications

END [package_name]

私有數據結構是那些在包主體內部，對被調用程序而言是不可見的。

觸發器(Triggers)

觸發器是一種自動執行響應數據庫變化的程序。可以設置為在觸發器事件之前或之后觸發或執行。能夠觸發觸發器事件的事件包括下面幾種：

DML事件

DDL事件

數據庫事件

創建觸發器的語法如下：

CREATE [OR REPLACE] TRIGGER trigger_name

{before|after|instead of} event

ON {table_or_view_name|DATABASE}

[FOR EACH ROW[WHEN condition>

trigger_body

只有DML觸發器(INSERT、UPDATE、DELETE)語句可以使用INSTEAD OF觸發器并且只有表的DML觸發器可以是BEFORE或AFTER觸發器。

象約束一樣觸發器可以被設置為禁用或啟用來關閉或打開他們的執行體(EXECUTE),將觸發器設置為禁用或啟用使用ALTER TRIGGER語句：

ALTER TRIGGER trigger_name ENABLE;

ALTER TRIGGER trigger_name DISABLE;

要禁用或啟用表的所有觸發器，使用ALTER TABLE語句

ALTER TRIGGER table_name DISABLE ALL TRIGGER;

ALTER TRIGGER table_name ENABLE ALL TRIGGER;

刪除觸發器使用DROP TRIGGER

DROP TRIGGER trigger_name;

數據字典

Oracle數據字典包含了用戶數據庫的元數據。帶下劃線的表名稱中帶OBJ$、UET$、SOURCE$，這些表是在執行CREATE DATABASE語句期間由sql.bsq腳本創建的，一般情況下用戶很少訪問這些表。腳本catalog.sql(通常位于$oracle_home/rdbms/admin)在CREATE DATABASE語句之后立即運行，創建數據字典視圖。

數據字典視圖大致可以分為三類：

.前綴為USER_的數據字典視圖，包含了用戶擁有的對象的信息。

.前綴為ALL_的數據字典視圖，包含了用戶當前可以訪問的全部對象和權限的信息。

.前綴為DBA_的數據字典視圖，包含了數據庫擁有的所有對象和權限的信息。

在絕大多數數據字典視圖中都有象DBA_TABLES,ALL_TABLES和USER_TABLES這樣的視圖家族。Oracle中有超過100個視圖家族，所以要全面介紹這些視圖家族是單調乏味的而且沒有多大的意義。在下表中列出了最重要和最常用的視圖家族，需要注意的是每個視圖家族都有一個DBA_,一個ALL_一個USER_視圖。

其他的字典視圖中主要的是V$視圖，之所以這樣叫是因為他們都是以V$或GV$開頭的。V$視圖是基于X$虛擬視圖的。V$視圖是SYS用戶所擁有的，在缺省狀況下，只有SYS用戶和擁有DBA系統權限的用戶可以看到所有的視圖，沒有DBA權限的用戶可以看到USER_和ALL_視圖，但不能看到DBA_視圖。與DBA_,ALL,和USER_視圖中面向數據庫信息相反，這些視圖可視的給出了面向實例的信息。

在大型系統上化幾周時間手工輸入每一條語句

手工輸入帶用戶名變量的語句，然后再輸入每一個用戶名，這需要花好幾個小時的時間

寫一條SQL語句，生成需要的ALTER USER語句，然后執行他，這只需要幾分鐘時間

很明顯我們將選擇生成SQL的方法：

例：

SELECT "ALTER USER"||username||"TEMPORARY TABLESPACE temp;"

FROM DBA_USERS

WHERE username<>"SYS"

AND temporary_tablespace<>"TEMP";

這個查詢的結果將被脫機處理到一個文件中，然后在執行：

ALTER USER SYSTEM TEMPORARY TABLESPACE temp;

ALTER USER OUTLN TEMPORARY TABLESPACE temp;

ALTER USER DBSNMP TEMPORARY TABLESPACE temp;

ALTER USER SCOTT TEMPORARY TABLESPACE temp;

ALTER USER DEMO TEMPORARY TABLESPACE temp;

總結

以上是生活随笔為你收集整理的简述oracle的主要数据库对象,Oracle数据库数据对象分析的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。