Oracle PL/SQL語言初級教程之過程和函數

2024-08-29 13:50:02

字體：大中小

來源：轉載

供稿：網友

　　過程和函數

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

pay_involume(invoice_nbr,30,due_date);

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

order_volumn:=open_orders(SYSDATE,30);

　　創建過程的語法如下：

CREATE [ OR REPLACE] PROCEDURE [schema.]procedure_name
[parameter_lister]
{ASIS}
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
{ASIS}
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
{ASIS}
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
{ASIS}
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
{beforeafterinstead of} event
ON {table_or_view_nameDATABASE}
[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 time
ROLE_PRIVS	角色權限，比如GRANT和ADMIN選項
SEGMENTS	表和索引的數據段信息，比如tablespace和storage
SEQUECNCES	序列信息，比如序列的cache、cycle和ast_number
SOURCE	除觸發器之外的所有內置過程、函數、包的源代碼
SYNONYMS	別名信息，比如引用的對象和數據庫鏈接db_link
SYS_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<>'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 PL/SQL語言初級教程之表和視圖

下一篇：Oracle PL/SQL語言初級教程之操作和控制語言