ORACLE數據庫PL/SQL編程之把過程與函數說透
過程和函數統稱為PL/SQL子程序，他們是被命名的PL/SQL塊，均存儲在數據庫中，并通過輸入、輸出參數或輸入/輸出參數與其調用者交換信息。過程和函數的唯一區別是函數總向調用者返回數據，而過程則不返回數據。在本文中，主要介紹：
1、創建存儲過程和函數。
2、正確使用系統級的異常處理和用戶定義的異常處理。
3、建立和管理存儲過程和函數。
創建函數
1. 創建函數
語法如下：
- 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
執行部分
RETURN expressionEXCEPTION
異常處理部分END function_name;
IN,OUT,IN OUT是形參的模式。若省略，則為IN模式。IN模式的形參只能將實參傳遞給形參，進入函數內部，但只能讀不能寫，函數返回時實參的值不變。OUT模式的形參會忽略調用時的實參值（或說該形參的初始值總是NULL），但在函數內部可以被讀或寫，函數返回時形參的值會賦予給實參。IN OUT具有前兩種模式的特性，即調用時，實參的值總是傳遞給形參，結束時，形參的值傳遞給實參。調用時，對于IN模式的實參可以是常量或變量，但對于OUT和IN OUT模式的實參必須是變量。
一般，只有在確認function_name函數是新函數或是要更新的函數時，才使用OR REPALCE關鍵字，否則容易刪除有用的函數。
#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('你需要的數據不存在!');
- WHEN OTHERS THEN
- DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);END get_salary;
2. 函數的調用
函數聲明時所定義的參數稱為形式參數，應用程序調用時為函數傳遞的參數稱為實際參數。應用程序在調用函數時，可以使用以下三種方法向函數傳遞參數：
第一種參數傳遞格式：位置表示法。
即在調用時按形參的排列順序，依次寫出實參的名稱，而將形參與實參關聯起來進行傳遞。用這種方法進行調用，形參與實參的名稱是相互獨立，沒有關系，強調次序才是重要的。
格式為：argument_value1[,argument_value2 …]
例2：計算某部門的工資總和：
- DECLARE V_num NUMBER;
- V_sum NUMBER;BEGIN
- V_sum :=get_salary(10, v_num);
- DBMS_OUTPUT.PUT_LINE('部門號為:10的工資總和：'||v_sum||'，人數為：'||v_num);END;
第二種參數傳遞格式：名稱表示法。
即在調用時按形參的名稱與實參的名稱，寫出實參對應的形參，而將形參與實參關聯起來進行傳遞。這種方法，形參與實參的名稱是相互獨立的，沒有關系，名稱的對應關系才是最重要的，次序并不重要。
格式為： argument => parameter [,…]
其中：argument 為形式參數，它必須與函數定義時所聲明的形式參數名稱相同parameter 為實際參數。
在這種格式中，形勢參數與實際參數成對出現，相互間關系唯一確定，所以參數的順序可以任意排列。
例3：計算某部門的工資總和：
- DECLARE V_num NUMBER;
- V_sum NUMBER;BEGIN
- V_sum :=get_salary(emp_count => v_num, dept_no => 10);
- DBMS_OUTPUT.PUT_LINE('部門號為:10的工資總和：'||v_sum||'，人數為：'||v_num);END;
#p#
第三種參數傳遞格式：組合傳遞。
即在調用一個函數時，同時使用位置表示法和名稱表示法為函數傳遞參數。采用這種參數傳遞方法時，使用位置表示法所傳遞的參數必須放在名稱表示法所傳遞的參數前面。也就是說，無論函數具有多少個參數，只要其中有一個參數使用名稱表示法，其后所有的參數都必須使用名稱表示法。
例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);
無論采用哪一種參數傳遞方法，實際參數和形式參數之間的數據傳遞只有兩種方法：傳址法和傳值法。所謂傳址法是指在調用函數時，將實際參數的地址指針傳遞給形式參數，使形式參數和實際參數指向內存中的同一區域，從而實現參數數據的傳遞。這種方法又稱作參照法，即形式參數參照實際參數數據。輸入參數均采用傳址法傳遞數據。
傳值法是指將實際參數的數據拷貝到形式參數，而不是傳遞實際參數的地址。默認時，輸出參數和輸入/輸出參數均采用傳值法。在函數調用時，ORACLE將實際參數數據拷貝到輸入/輸出參數，而當函數正常運行退出時，又將輸出形式參數和輸入/輸出形式參數數據拷貝到實際參數變量中。
3. 參數默認值
在CREATE OR REPLACE FUNCTION 語句中聲明函數參數時可以使用DEFAULT關鍵字為輸入參數指定默認值。
例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;
具有默認值的函數創建后，在函數調用時，如果沒有為具有默認值的參數提供實際參數值，函數將使用該參數的默認值。但當調用者為默認參數提供實際參數時，函數將使用實際參數值。在創建函數時，只能為輸入參數設置默認值，而不能為輸入/輸出參數設置默認值。
- 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;
關于PL/SQL編程中函數和過程的相關知識就介紹到這里，如果想了解更多Oracle數據庫的知識請到我們網站的Oracle專欄：http://database.51cto.com/oracle/，謝謝大家的支持！
【編輯推薦】
