Oracle數(shù)據(jù)庫數(shù)據(jù)對象分析(下)
2024-08-29 13:46:24
供稿：網(wǎng)友
過程和函數(shù)
過程和函數(shù)都以編譯后的形式存放在數(shù)據(jù)庫中，函數(shù)可以沒有參數(shù)也可以有多個(gè)參數(shù)并有一個(gè)返回值。過程有零個(gè)或多個(gè)參數(shù)，沒有返回值。函數(shù)和過程都可以通過參數(shù)列表接收或返回零個(gè)或多個(gè)值，函數(shù)和過程的主要區(qū)別不在于返回值，而在于他們的調(diào)用方式。過程是作為一個(gè)獨(dú)立執(zhí)行語句調(diào)用的：
pay_involume(invoice_nbr,30,due_date);
函數(shù)以合法的表達(dá)式的方式調(diào)用：
order_volumn:=open_orders(SYSDATE,30);
創(chuàng)建過程的語法如下：
CREATE [ OR REPLACE] PROCEDURE [schema.]procedure_name
[parameter_lister]
{ASIS}
declaration_section
BEGIN
executable_section
[EXCEPTION
exception_section]
END [procedure_name]
每個(gè)參數(shù)的語法如下：
paramter_name mode datatype [(:=DEFAULT) value]
mode有三種形式：IN、OUT、INOUT。
IN表示在調(diào)用過程的時(shí)候，實(shí)際參數(shù)的取值被傳遞給該過程，形式參數(shù)被認(rèn)為是只讀的，當(dāng)過程結(jié)束時(shí)，控制會返回控制環(huán)境，實(shí)際參數(shù)的值不會改變。
OUT在調(diào)用過程時(shí)實(shí)際參數(shù)的取值都將被忽略，在過程內(nèi)部形式參數(shù)只能是被賦值，而不能從中讀取數(shù)據(jù)，在過程結(jié)束后形式參數(shù)的內(nèi)容將被賦予實(shí)際參數(shù)。
INOUT這種模式是IN和OUT的組合；在過程內(nèi)部實(shí)際參數(shù)的值會傳遞給形式參數(shù)，形勢參數(shù)的值可讀也可寫，過程結(jié)束后，形勢參數(shù)的值將賦予實(shí)際參數(shù)。
創(chuàng)建函數(shù)的語法和過程的語法基本相同，唯一的區(qū)別在于函數(shù)有RETUREN子句
CREATE [ OR REPLACE] FINCTION [schema.]function_name
[parameter_list]
RETURN returning_datatype
{ASIS}
declaration_section
BEGIN
executable_section
[EXCEPTION]
exception_section
END [procedure_name]
在執(zhí)行部分函數(shù)必須有喲個(gè)或多個(gè)return語句。
在創(chuàng)建函數(shù)中可以調(diào)用單行函數(shù)和組函數(shù)，例如：
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
包
包是一種將過程、函數(shù)和數(shù)據(jù)結(jié)構(gòu)捆綁在一起的容器；包由兩個(gè)部分組成：外部可視包規(guī)范，包括函數(shù)頭，過程頭，和外部可視數(shù)據(jù)結(jié)構(gòu)；另一部分是包主體(package body),包主體包含了所有被捆綁的過程和函數(shù)的聲明、執(zhí)行、異常處理部分。
打包的PL/SQL程序和沒有打包的有很大的差異，包數(shù)據(jù)在用戶的整個(gè)會話期間都一直存在，當(dāng)用戶獲得包的執(zhí)行授權(quán)時(shí)，就等于獲得包規(guī)范中的所有程序和數(shù)據(jù)結(jié)構(gòu)的權(quán)限。但不能只對包中的某一個(gè)函數(shù)或過程進(jìn)行授權(quán)。包可以重載過程和函數(shù)，在包內(nèi)可以用同一個(gè)名字聲明多個(gè)程序，在運(yùn)行時(shí)根據(jù)參數(shù)的數(shù)目和數(shù)據(jù)類型調(diào)用正確的程序。
創(chuàng)建包必須首先創(chuàng)建包規(guī)范，創(chuàng)建包規(guī)范的語法如下：
CREATE [OR REPLACE] PACKAGE package_name
{ASIS}
public_variable_declarations
public_type_declarations
public_exception_declarations
public_cursor_declarations
function_declarations
procedure_specifications
END [package_name]
創(chuàng)建包主體使用CREATE PACKAGE BODY語句：
CREATE [OR REPLACE] PACKAGE BODY package_name
{ASIS}
private_variable_declarations
private_type_declarations
private_exception_declarations
private_cursor_declarations
function_declarations
procedure_specifications
END [package_name]
私有數(shù)據(jù)結(jié)構(gòu)是那些在包主體內(nèi)部，對被調(diào)用程序而言是不可見的。
觸發(fā)器(Triggers)
觸發(fā)器是一種自動執(zhí)行響應(yīng)數(shù)據(jù)庫變化的程序。可以設(shè)置為在觸發(fā)器事件之前或之后觸發(fā)或執(zhí)行。能夠觸發(fā)觸發(fā)器事件的事件包括下面幾種：
DML事件
DDL事件
數(shù)據(jù)庫事件
DML事件觸發(fā)器可以是語句或行級觸發(fā)器。DML語句觸發(fā)器在觸發(fā)語句之前或之后觸發(fā)DML行級觸發(fā)器在語句影響的行變化之前或之后觸發(fā)。用戶可以給單一事件和類型定義多個(gè)觸發(fā)器，但沒有任何方法可以增強(qiáng)多觸發(fā)器觸發(fā)的命令。下表列出了用戶可以利用的觸發(fā)器事件：
事件觸發(fā)器描述INSERT 當(dāng)向表或視圖插入一行時(shí)觸發(fā)觸發(fā)器UPDATE 更新表或視圖中的某一行時(shí)觸發(fā)觸發(fā)器DELETE從表或視圖中刪除某一行時(shí)觸發(fā)觸發(fā)器CREATE當(dāng)使用CREATE語句為數(shù)據(jù)庫或項(xiàng)目增加一個(gè)對象時(shí)觸發(fā)觸發(fā)器ALTER當(dāng)使用ALTER語句為更改一個(gè)數(shù)據(jù)庫或項(xiàng)目的對象時(shí)觸發(fā)觸發(fā)器DROP當(dāng)使用DROP語句刪除一個(gè)數(shù)據(jù)庫或項(xiàng)目的對象時(shí)觸發(fā)觸發(fā)器START打開數(shù)據(jù)庫時(shí)觸發(fā)觸發(fā)器，在事件后觸發(fā)SHUTDOWN 關(guān)閉數(shù)據(jù)庫時(shí)觸發(fā)，事件前觸發(fā)LOGON當(dāng)一個(gè)會話建立時(shí)觸發(fā)，事件前觸發(fā)LOGOFF當(dāng)關(guān)閉會話時(shí)觸發(fā)，事件前觸發(fā)SERVER服務(wù)器錯(cuò)誤發(fā)生時(shí)觸發(fā)觸發(fā)器，事件后觸發(fā)
創(chuàng)建觸發(fā)器的語法如下：
CREATE [OR REPLACE] TRIGGER trigger_name
{beforeafterinstead of} event
ON {table_or_view_nameDATABASE}
[FOR EACH ROW[WHEN condition]]
trigger_body
只有DML觸發(fā)器（INSERT、UPDATE、DELETE)語句可以使用INSTEAD OF觸發(fā)器并且只有表的DML觸發(fā)器可以是BEFORE或AFTER觸發(fā)器。
象約束一樣觸發(fā)器可以被設(shè)置為禁用或啟用來關(guān)閉或打開他們的執(zhí)行體(EXECUTE),將觸發(fā)器設(shè)置為禁用或啟用使用ALTER TRIGGER語句：
ALTER TRIGGER trigger_name ENABLE;
ALTER TRIGGER trigger_name DISABLE;
要禁用或啟用表的所有觸發(fā)器，使用ALTER TABLE語句
ALTER TRIGGER table_name DISABLE ALL TRIGGER;
ALTER TRIGGER table_name ENABLE ALL TRIGGER;
刪除觸發(fā)器使用DROP TRIGGER
DROP TRIGGER trigger_name;
數(shù)據(jù)字典
Oracle數(shù)據(jù)字典包含了用戶數(shù)據(jù)庫的元數(shù)據(jù)。帶下劃線的表名稱中帶OBJ$、UET$、SOURCE$，這些表是在執(zhí)行CREATE DATABASE語句期間由sql.bsq腳本創(chuàng)建的，一般情況下用戶很少訪問這些表。腳本catalog.sql（通常位于$oracle_home/rdbms/admin）在CREATE DATABASE語句之后立即運(yùn)行，創(chuàng)建數(shù)據(jù)字典視圖。
數(shù)據(jù)字典視圖大致可以分為三類：
.前綴為USER_的數(shù)據(jù)字典視圖，包含了用戶擁有的對象的信息。
.前綴為ALL_的數(shù)據(jù)字典視圖，包含了用戶當(dāng)前可以訪問的全部對象和權(quán)限的信息。
.前綴為DBA_的數(shù)據(jù)字典視圖，包含了數(shù)據(jù)庫擁有的所有對象和權(quán)限的信息。
在絕大多數(shù)數(shù)據(jù)字典視圖中都有象DBA_TABLES,ALL_TABLES和USER_TABLES這樣的視圖家族。Oracle中有超過100個(gè)視圖家族，所以要全面介紹這些視圖家族是單調(diào)乏味的而且沒有多大的意義。在下表中列出了最重要和最常用的視圖家族，需要注重的是每個(gè)視圖家族都有一個(gè)DBA_,一個(gè)ALL_一個(gè)USER_視圖。
視圖家族(View Family)描述COL_PRIVS包含了表的列權(quán)限，包括授予者、被授予者和權(quán)限EXTENTS 數(shù)據(jù)范圍信息，比如數(shù)據(jù)文件，數(shù)據(jù)段名(segment_name)和大小INDEXES索引信息，比如類型、唯一性和被涉及的表IND_COLUMNS 索引列信息，比如索引上的列的排序方式OBJECTS 對象信息，比如狀態(tài)和DDL timeROLE_PRIVS角色權(quán)限，比如GRANT和ADMIN選項(xiàng)SEGMENTS 表和索引的數(shù)據(jù)段信息，比如tablespace和storageSEQUECNCES序列信息，比如序列的cache、cycle和ast_numberSOURCE 除觸發(fā)器之外的所有內(nèi)置過程、函數(shù)、包的源代碼SYNONYMS 別名信息，比如引用的對象和數(shù)據(jù)庫鏈接db_linkSYS_PRIVS 系統(tǒng)權(quán)限，比如grantee、privilege、admin選項(xiàng)TAB_COLUMNS 表和視圖的列信息，包括列的數(shù)據(jù)類型TAB_PRIVS 表權(quán)限，比如授予者、被授予者和權(quán)限TABLES表信息，比如表空間(tablespace),存儲參數(shù)(storage parms)和數(shù)據(jù)行的數(shù)量TRIGGERS 觸發(fā)器信息，比如類型、事件、觸發(fā)體(trigger body)USERS用戶信息，比如臨時(shí)的和缺省的表空間VIEWS視圖信息，包括視圖定義
在Oracle中還有一些不常用的數(shù)據(jù)字典表，但這些表不是真正的字典家族，他們都是一些重要的單一的視圖。
VIEW NAME描述USER_COL_PRIVS_MADE用戶授予他人的列權(quán)限USER_COL_PRIVS_RECD 用戶獲得的列權(quán)限USER_TAB_PRIVS_MADE 用戶授予他人的表權(quán)限USER_TAB_PRIVS_RECD用戶獲得的表權(quán)限
其他的字典視圖中主要的是V$視圖，之所以這樣叫是因?yàn)樗麄兌际且訴$或GV$開頭的。V$視圖是基于X$虛擬視圖的。V$視圖是SYS用戶所擁有的，在缺省狀況下，只有SYS用戶和擁有DBA系統(tǒng)權(quán)限的用戶可以看到所有的視圖，沒有DBA權(quán)限的用戶可以看到USER_和ALL_視圖，但不能看到DBA_視圖。與DBA_,ALL,和USER_視圖中面向數(shù)據(jù)庫信息相反，這些視圖可視的給出了面向?qū)嵗男畔ⅰ?
在大型系統(tǒng)上化幾周時(shí)間手工輸入每一條語句
手工輸入帶用戶名變量的語句，然后再輸入每一個(gè)用戶名，這需要花好幾個(gè)小時(shí)的時(shí)間
寫一條SQL語句，生成需要的ALTER USER語句，然后執(zhí)行他，這只需要幾分鐘時(shí)間
很明顯我們將選擇生成SQL的方法：
例：
SELECT 'ALTER USER'username
'TEMPORARY TABLESPACE temp;'
FROM DBA_USERS
WHERE username<>'SYS'
AND temporary_tablespace<>'TEMP';
這個(gè)查詢的結(jié)果將被脫機(jī)處理到一個(gè)文件中，然后在執(zhí)行：
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;
