Oracle資料庫資料對象分析 (下)

oracle|對象|資料|資料庫 　過程和函數

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

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事件
　　資料庫事件

　　DML事件觸發程序可以是語句或行級觸發器。DML語句觸發器在觸發語句之前或之後觸發DML行級觸發器在語句影響的行變化之前或之後觸發。使用者可以給單一事件和類型定義多個觸發器，但沒有任何方法可以增強多觸發器觸發的命令。下表列出了使用者可以利用的觸發事件：

事件觸發程序描述INSERT 當向表或視圖插入一行時觸發觸發器UPDATE 更新表或視圖中的某一行時觸發觸發器DELETE從表或視圖中刪除某一行時觸發觸發器CREATE當使用CREATE語句為資料庫或項目增加一個對象時觸發觸發器ALTER當使用ALTER語句為更改一個資料庫或項目的對象時觸發觸發器DROP當使用DROP語句刪除一個資料庫或項目的對象時觸發觸發器START開啟資料庫時觸發觸發器，在事件後觸發SHUTDOWN 關閉資料庫時觸發，事件前觸發LOGON當一個會話建立時觸發，事件前觸發LOGOFF當關閉會話時觸發，事件前觸發SERVER伺服器錯誤發生時觸發觸發器，事件後觸發
　　建立觸發器的文法如下：

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_視圖。

視圖家族(View Family)描述COL_PRIVS包含了表的列許可權，包括授予者、被授予者和許可權EXTENTS 資料範圍資訊，比如資料檔案，資料區段名(segment_name)和大小INDEXES索引資訊，比如類型、唯一性和被涉及的表IND_COLUMNS 索引列資訊，比如索引上的列的排序方式OBJECTS 對象資訊，比如狀態和DDL timeROLE_PRIVS角色許可權，比如GRANT和ADMIN選項SEGMENTS 表和索引的資料區段資訊，比如tablespace和storageSEQUECNCES序列資訊，比如序列的cache、cycle和ast_numberSOURCE 除觸發器之外的所有內建過程、函數、包的原始碼SYNONYMS 別名資訊，比如引用的對象和資料庫連結db_linkSYS_PRIVS 系統許可權，比如grantee、privilege、admin選項TAB_COLUMNS 表和視圖的列資訊，包括列的資料類型TAB_PRIVS 表許可權，比如授予者、被授予者和許可權TABLES表資訊，比如資料表空間(tablespace),儲存參數(storage parms)和資料行的數量TRIGGERS 觸發器資訊，比如類型、事件、觸發體(trigger body)USERS使用者資訊，比如臨時的和預設的資料表空間VIEWS視圖資訊，包括視圖定義
　　在Oracle中還有一些不常用的資料字典表，但這些表不是真正的字典家族，他們都是一些重要的單一的視圖。

VIEW NAME描述USER_COL_PRIVS_MADE使用者授予他人的列許可權USER_COL_PRIVS_RECD 使用者獲得的列許可權USER_TAB_PRIVS_MADE 使用者授予他人的表許可權USER_TAB_PRIVS_RECD使用者獲得的表許可權
　　其他的字典視圖中主要的是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
　　這個查詢的結果將被離線處理到一個檔案中，然後在執行：

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;