ORACLE數(shù)據(jù)庫(kù)PL/SQL編程之把過(guò)程與函數(shù)說(shuō)透
過(guò)程和函數(shù)統(tǒng)稱為PL/SQL子程序，他們是被命名的PL/SQL塊，均存儲(chǔ)在數(shù)據(jù)庫(kù)中，并通過(guò)輸入、輸出參數(shù)或輸入/輸出參數(shù)與其調(diào)用者交換信息。過(guò)程和函數(shù)的唯一區(qū)別是函數(shù)總向調(diào)用者返回?cái)?shù)據(jù)，而過(guò)程則不返回?cái)?shù)據(jù)。在本文中，主要介紹：
1、創(chuàng)建存儲(chǔ)過(guò)程和函數(shù)。
2、正確使用系統(tǒng)級(jí)的異常處理和用戶定義的異常處理。
3、建立和管理存儲(chǔ)過(guò)程和函數(shù)。
創(chuàng)建函數(shù)
1. 創(chuàng)建函數(shù)
語(yǔ)法如下：
- 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)部，但只能讀不能寫(xiě)，函數(shù)返回時(shí)實(shí)參的值不變。OUT模式的形參會(huì)忽略調(diào)用時(shí)的實(shí)參值（或說(shuō)該形參的初始值總是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、獲取某部門(mén)的工資總和：
--獲取某部門(mén)的工資總和
- 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í)按形參的排列順序，依次寫(xiě)出實(shí)參的名稱，而將形參與實(shí)參關(guān)聯(lián)起來(lái)進(jìn)行傳遞。用這種方法進(jìn)行調(diào)用，形參與實(shí)參的名稱是相互獨(dú)立，沒(méi)有關(guān)系，強(qiáng)調(diào)次序才是重要的。
格式為：argument_value1[,argument_value2 …]
例2：計(jì)算某部門(mén)的工資總和：
- DECLARE V_num NUMBER;
- V_sum NUMBER;BEGIN
- V_sum :=get_salary(10, v_num);
- DBMS_OUTPUT.PUT_LINE('部門(mén)號(hào)為:10的工資總和：'||v_sum||'，人數(shù)為：'||v_num);END;
第二種參數(shù)傳遞格式：名稱表示法。
即在調(diào)用時(shí)按形參的名稱與實(shí)參的名稱，寫(xiě)出實(shí)參對(duì)應(yīng)的形參，而將形參與實(shí)參關(guān)聯(lián)起來(lái)進(jìn)行傳遞。這種方法，形參與實(shí)參的名稱是相互獨(dú)立的，沒(méi)有關(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ì)算某部門(mén)的工資總和：
- DECLARE V_num NUMBER;
- V_sum NUMBER;BEGIN
- V_sum :=get_salary(emp_count => v_num, dept_no => 10);
- DBMS_OUTPUT.PUT_LINE('部門(mén)號(hào)為:10的工資總和：'||v_sum||'，人數(shù)為：'||v_num);END;
#p#
第三種參數(shù)傳遞格式：組合傳遞。
即在調(diào)用一個(gè)函數(shù)時(shí)，同時(shí)使用位置表示法和名稱表示法為函數(shù)傳遞參數(shù)。采用這種參數(shù)傳遞方法時(shí)，使用位置表示法所傳遞的參數(shù)必須放在名稱表示法所傳遞的參數(shù)前面。也就是說(shuō)，無(wú)論函數(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);
無(wú)論采用哪一種參數(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 語(yǔ)句中聲明函數(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í)，如果沒(méi)有為具有默認(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ò)程的相關(guān)知識(shí)就介紹到這里，如果想了解更多Oracle數(shù)據(jù)庫(kù)的知識(shí)請(qǐng)到我們網(wǎng)站的Oracle專欄：http://database.51cto.com/oracle/，謝謝大家的支持！
【編輯推薦】
