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/,謝謝大家的支持!
【編輯推薦】