過程和函數(shù)統(tǒng)稱為PL/SQL子程序，他們是被命名的PL/SQL塊，均存儲(chǔ)在數(shù)據(jù)庫中，并通過輸入、輸出參數(shù)或輸入/輸出參數(shù)與其調(diào)用者交換信息。過程和函數(shù)的唯一區(qū)別是函數(shù)總向調(diào)用者返回?cái)?shù)據(jù)，而過程則不返回?cái)?shù)據(jù)。在本文中，主要介紹：

1、創(chuàng)建存儲(chǔ)過程和函數(shù)。

2、正確使用系統(tǒng)級(jí)的異常處理和用戶定義的異常處理。

3、建立和管理存儲(chǔ)過程和函數(shù)。

創(chuàng)建函數(shù)

1. 創(chuàng)建函數(shù)

語法如下：

CREATE [OR REPLACE] FUNCTION function_name  
 
(arg1 [ { IN | OUT | IN OUT }] type1  
 
[DEFAULT value1],  
 
[arg2 [ { IN | OUT | IN OUT }] type2 [DEFAULT value1]],  
 
......  
 
[argn [ { IN | OUT | IN OUT }] typen [DEFAULT valuen]])  
 
[ AUTHID DEFINER | CURRENT_USER ]RETURN return_type  
 
IS | AS  
 
<類型.變量的聲明部分> BEGIN 

執(zhí)行部分

RETURN expressionEXCEPTION

異常處理部分END function_name;

IN,OUT,IN OUT是形參的模式。若省略，則為IN模式。IN模式的形參只能將實(shí)參傳遞給形參，進(jìn)入函數(shù)內(nèi)部，但只能讀不能寫，函數(shù)返回時(shí)實(shí)參的值不變。OUT模式的形參會(huì)忽略調(diào)用時(shí)的實(shí)參值（或說該形參的初始值總是NULL），但在函數(shù)內(nèi)部可以被讀或?qū)?，函?shù)返回時(shí)形參的值會(huì)賦予給實(shí)參。IN OUT具有前兩種模式的特性，即調(diào)用時(shí)，實(shí)參的值總是傳遞給形參，結(jié)束時(shí)，形參的值傳遞給實(shí)參。調(diào)用時(shí)，對(duì)于IN模式的實(shí)參可以是常量或變量，但對(duì)于OUT和IN OUT模式的實(shí)參必須是變量。

一般，只有在確認(rèn)function_name函數(shù)是新函數(shù)或是要更新的函數(shù)時(shí)，才使用OR REPALCE關(guān)鍵字，否則容易刪除有用的函數(shù)。

#p#

例1、獲取某部門的工資總和：

--獲取某部門的工資總和

CREATE OR REPLACEFUNCTION get_salary(  
 
Dept_no NUMBER,  
 
Emp_count OUT NUMBER)  
 
RETURN NUMBER IS  V_sum NUMBER;BEGIN  SELECT SUM(SALARY), count(*) INTO V_sum, emp_count  
 
FROM EMPLOYEES WHERE DEPARTMENT_ID=dept_no;  
 
RETURN v_sum;EXCEPTION  
 
WHEN NO_DATA_FOUND THEN  
 
DBMS_OUTPUT.PUT_LINE('你需要的數(shù)據(jù)不存在!');  
 
WHEN OTHERS THEN  
 
DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);END get_salary; 

2. 函數(shù)的調(diào)用

函數(shù)聲明時(shí)所定義的參數(shù)稱為形式參數(shù)，應(yīng)用程序調(diào)用時(shí)為函數(shù)傳遞的參數(shù)稱為實(shí)際參數(shù)。應(yīng)用程序在調(diào)用函數(shù)時(shí)，可以使用以下三種方法向函數(shù)傳遞參數(shù)：

第一種參數(shù)傳遞格式：位置表示法。

即在調(diào)用時(shí)按形參的排列順序，依次寫出實(shí)參的名稱，而將形參與實(shí)參關(guān)聯(lián)起來進(jìn)行傳遞。用這種方法進(jìn)行調(diào)用，形參與實(shí)參的名稱是相互獨(dú)立，沒有關(guān)系，強(qiáng)調(diào)次序才是重要的。

格式為：argument_value1[,argument_value2 …]

例2：計(jì)算某部門的工資總和：

DECLARE  V_num NUMBER;  
 
V_sum NUMBER;BEGIN  
 
V_sum :=get_salary(10, v_num);  
 
DBMS_OUTPUT.PUT_LINE('部門號(hào)為:10的工資總和：'||v_sum||'，人數(shù)為：'||v_num);END; 

第二種參數(shù)傳遞格式：名稱表示法。

即在調(diào)用時(shí)按形參的名稱與實(shí)參的名稱，寫出實(shí)參對(duì)應(yīng)的形參，而將形參與實(shí)參關(guān)聯(lián)起來進(jìn)行傳遞。這種方法，形參與實(shí)參的名稱是相互獨(dú)立的，沒有關(guān)系，名稱的對(duì)應(yīng)關(guān)系才是最重要的，次序并不重要。

格式為： argument => parameter [,…]

其中：argument 為形式參數(shù)，它必須與函數(shù)定義時(shí)所聲明的形式參數(shù)名稱相同parameter 為實(shí)際參數(shù)。

在這種格式中，形勢(shì)參數(shù)與實(shí)際參數(shù)成對(duì)出現(xiàn)，相互間關(guān)系唯一確定，所以參數(shù)的順序可以任意排列。

例3：計(jì)算某部門的工資總和：

DECLARE  V_num NUMBER;  
 
V_sum NUMBER;BEGIN  
 
V_sum :=get_salary(emp_count => v_num, dept_no => 10);  
 
DBMS_OUTPUT.PUT_LINE('部門號(hào)為:10的工資總和：'||v_sum||'，人數(shù)為：'||v_num);END; 

 #p#

第三種參數(shù)傳遞格式：組合傳遞。

即在調(diào)用一個(gè)函數(shù)時(shí)，同時(shí)使用位置表示法和名稱表示法為函數(shù)傳遞參數(shù)。采用這種參數(shù)傳遞方法時(shí)，使用位置表示法所傳遞的參數(shù)必須放在名稱表示法所傳遞的參數(shù)前面。也就是說，無論函數(shù)具有多少個(gè)參數(shù)，只要其中有一個(gè)參數(shù)使用名稱表示法，其后所有的參數(shù)都必須使用名稱表示法。

例4：

CREATE OR REPLACE FUNCTION demo_fun(  
 
Name VARCHAR2,--注意VARCHAR2不能給精度，如：VARCHAR2(10)，其它類似  
 
Age INTEGER,  
 
Sex VARCHAR2)  
 
RETURN VARCHAR2 AS  
 
V_var VARCHAR2(32);BEGIN  
 
V_var := name||'：'||TO_CHAR(age)||'歲.'||sex;  RETURN v_var;END;DECLARE  
 
Var VARCHAR(32);BEGIN  Var := demo_fun('user1', 30, sex => '男');  
 
DBMS_OUTPUT.PUT_LINE(var);  Var := demo_fun('user2', age => 40, sex => '男');  
 
DBMS_OUTPUT.PUT_LINE(var);  Var := demo_fun('user3', sex => '女', age => 20); 

無論采用哪一種參數(shù)傳遞方法，實(shí)際參數(shù)和形式參數(shù)之間的數(shù)據(jù)傳遞只有兩種方法：傳址法和傳值法。所謂傳址法是指在調(diào)用函數(shù)時(shí)，將實(shí)際參數(shù)的地址指針傳遞給形式參數(shù)，使形式參數(shù)和實(shí)際參數(shù)指向內(nèi)存中的同一區(qū)域，從而實(shí)現(xiàn)參數(shù)數(shù)據(jù)的傳遞。這種方法又稱作參照法，即形式參數(shù)參照實(shí)際參數(shù)數(shù)據(jù)。輸入?yún)?shù)均采用傳址法傳遞數(shù)據(jù)。

傳值法是指將實(shí)際參數(shù)的數(shù)據(jù)拷貝到形式參數(shù)，而不是傳遞實(shí)際參數(shù)的地址。默認(rèn)時(shí)，輸出參數(shù)和輸入/輸出參數(shù)均采用傳值法。在函數(shù)調(diào)用時(shí)，ORACLE將實(shí)際參數(shù)數(shù)據(jù)拷貝到輸入/輸出參數(shù)，而當(dāng)函數(shù)正常運(yùn)行退出時(shí)，又將輸出形式參數(shù)和輸入/輸出形式參數(shù)數(shù)據(jù)拷貝到實(shí)際參數(shù)變量中。

3. 參數(shù)默認(rèn)值

在CREATE OR REPLACE FUNCTION 語句中聲明函數(shù)參數(shù)時(shí)可以使用DEFAULT關(guān)鍵字為輸入?yún)?shù)指定默認(rèn)值。

例5：

CREATE OR REPLACE FUNCTION demo_fun(  Name VARCHAR2,  
 
Age INTEGER,  
 
Sex VARCHAR2 DEFAULT '男')  
 
RETURN VARCHAR2 AS  
 
V_var VARCHAR2(32);BEGIN  
 
V_var := name||'：'||TO_CHAR(age)||'歲.'||sex;  
 
RETURN v_var;END; 

具有默認(rèn)值的函數(shù)創(chuàng)建后，在函數(shù)調(diào)用時(shí)，如果沒有為具有默認(rèn)值的參數(shù)提供實(shí)際參數(shù)值，函數(shù)將使用該參數(shù)的默認(rèn)值。但當(dāng)調(diào)用者為默認(rèn)參數(shù)提供實(shí)際參數(shù)時(shí)，函數(shù)將使用實(shí)際參數(shù)值。在創(chuàng)建函數(shù)時(shí)，只能為輸入?yún)?shù)設(shè)置默認(rèn)值，而不能為輸入/輸出參數(shù)設(shè)置默認(rèn)值。

DECLARE var VARCHAR(32);BEGIN Var := demo_fun('user1', 30);  
 
DBMS_OUTPUT.PUT_LINE(var); Var := demo_fun('user2', age => 40);  
 
DBMS_OUTPUT.PUT_LINE(var); Var := demo_fun('user3', sex => '女',  
 
age => 20); DBMS_OUTPUT.PUT_LINE(var);END; 

關(guān)于PL/SQL編程中函數(shù)和過程的相關(guān)知識(shí)就介紹到這里，如果想了解更多Oracle數(shù)據(jù)庫的知識(shí)請(qǐng)到我們網(wǎng)站的Oracle專欄：http://database.51cto.com/oracle/，謝謝大家的支持！

【編輯推薦】

1. 因?yàn)镺racle推EF for Oracle引發(fā)的口水戰(zhàn)
2. Oracle數(shù)據(jù)庫使用OMF來簡化數(shù)據(jù)文件的管理
3. 淺談禁用以操作系統(tǒng)認(rèn)證方式登錄Oracle數(shù)據(jù)庫
4. Oracle數(shù)據(jù)導(dǎo)入MySQL的快捷工具：MySQL Migration Toolkit
5. 淺析64位win7下使用PL/SQL Developer連接遠(yuǎn)程Oracle數(shù)據(jù)庫