過程與函數(shù)

過程與函數(shù)（另外還有包與觸發(fā)器）是命名的PL/SQL塊（也是用戶的方案對象），被編譯后存儲在數(shù)據(jù)庫中，以備執(zhí)行。因此，其它PL/SQL塊可以按名稱來使用他們。所以，可以將商業(yè)邏輯、企業(yè)規(guī)則寫成函數(shù)或過程保存到數(shù)據(jù)庫中，以便共享。

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

1． 創(chuàng)建存儲過程和函數(shù)。
2． 正確使用系統(tǒng)級的異常處理和用戶定義的異常處理。
3． 建立和管理存儲過程和函數(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 expression
EXCEPTION
異常處理部分
END function_name; 　　
　　l IN,OUT,IN OUT是形參的模式。若省略，則為IN模式。IN模式的形參只能將實(shí)參傳遞給形參，進(jìn)入函數(shù)內(nèi)部，但只能讀不能寫，函數(shù)返回時實(shí)參的值不變。OUT模式的形參會忽略調(diào)用時的實(shí)參值（或說該形參的初始值總是NULL），但在函數(shù)內(nèi)部可以被讀或?qū)?，函?shù)返回時形參的值會賦予給實(shí)參。IN OUT具有前兩種模式的特性，即調(diào)用時，實(shí)參的值總是傳遞給形參，結(jié)束時，形參的值傳遞給實(shí)參。調(diào)用時，對于IN模式的實(shí)參可以是常量或變量，但對于OUT和IN OUT模式的實(shí)參必須是變量。

　　l 一般，只有在確認(rèn)function_name函數(shù)是新函數(shù)或是要更新的函數(shù)時，才使用OR REPALCE關(guān)鍵字，否則容易刪除有用的函數(shù)。
例1. 獲取某部門的工資總和：
--獲取某部門的工資總和
SQL> create or replace
2 function f_get_salary(
3 Dept_no NUMBER,
4 Emp_count OUT NUMBER)
5 RETURN NUMBER
6 is
7 V_sum NUMBER;
8 BEGIN
9 SELECT SUM(SALARY), count(*) INTO V_sum, emp_count
10 FROM EMPLOYEES WHERE DEPARTMENT_ID=dept_no;
11 RETURN v_sum;
12 EXCEPTION
13 WHEN NO_DATA_FOUND THEN
14 DBMS_OUTPUT.PUT_LINE('你需要的數(shù)據(jù)不存在!');
15 WHEN OTHERS THEN
16 DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
17 END f_get_salary;
18 /
Function created.

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

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

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

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

　　格式為：

argument_value1[,argument_value2 …]
　　第二種參數(shù)傳遞格式：名稱表示法。

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

　　格式為：

argument => parameter [,…]
　　其中：argument 為形式參數(shù)，它必須與函數(shù)定義時所聲明的形式參數(shù)名稱相同parameter 為實(shí)際參數(shù)。
　　在這種格式中，形勢參數(shù)與實(shí)際參數(shù)成對出現(xiàn)，相互間關(guān)系唯一確定，所以參數(shù)的順序可以任意排列。
第三種參數(shù)傳遞格式：組合傳遞。

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

SQL> DECLARE
2 V_num NUMBER;
3 V_sum NUMBER;
4 BEGIN
5 V_sum :=f_get_salary(emp_count => v_num, dept_no => 10);
6 DBMS_OUTPUT.PUT_LINE('10號部門工資總和：'||v_sum||'，人數(shù)：'||v_num);
7 end;
8 /
10號部門工資總和：4400，人數(shù)：1

PL/SQL procedure successfully completed.
SQL> var V_sum NUMBER;
SQL> var V_num NUMBER;
SQL> exec :V_sum:=f_get_salary(:dept_no,:V_num);

PL/SQL procedure successfully completed.

SQL> print :V_sum :V_num

 V_SUM

  4400

 V_NUM

     1
SQL> select :V_sum ,:V_num from dual;

:V_SUM     :V_NUM

  4400          1
        SQL> run

1 begin
2 DBMS_OUTPUT.PUT_LINE('10號部門工資總和：'||:v_sum||'，人數(shù)：'||:v_num);
3* end;
10號部門工資總和：4400，人數(shù)：1
SQL> DECLARE
2 V_num NUMBER;
3 V_sum NUMBER;
4 BEGIN
5 V_sum :=f_get_salary(10,emp_count => v_num);
6 DBMS_OUTPUT.PUT_LINE('10號部門工資總和：'||v_sum||'，人數(shù)：'||v_num);
7 end;
8 /
10號部門工資總和：4400，人數(shù)：1

PL/SQL procedure successfully completed.

3. 參數(shù)默認(rèn)值
   在CREATE OR REPLACE FUNCTION 語句中聲明函數(shù)參數(shù)時可以使用DEFAULT關(guān)鍵字為輸入?yún)?shù)指定默認(rèn)值。
   例2：
   CREATE OR REPLACE FUNCTION f_demo_fun(
   Name VARCHAR2,
   Age INTEGER,
   Sex VARCHAR2 DEFAULT '男')
   RETURN VARCHAR2
   IS
   V_var VARCHAR2(32);
   BEGIN
   V_var := name||'：'||TO_CHAR(age)||'歲，'||sex;
   RETURN v_var;
   END;
   具有默認(rèn)值的函數(shù)創(chuàng)建后，在函數(shù)調(diào)用時，如果沒有為具有默認(rèn)值的參數(shù)提供實(shí)際參數(shù)值，函數(shù)將使用該參數(shù)的默認(rèn)值。但當(dāng)調(diào)用者為默認(rèn)參數(shù)提供實(shí)際參數(shù)時，函數(shù)將使用實(shí)際參數(shù)值。在創(chuàng)建函數(shù)時，只能為輸入?yún)?shù)設(shè)置默認(rèn)值，而不能為輸入/輸出參數(shù)設(shè)置默認(rèn)值。
   SQL> DECLARE
   2 Var VARCHAR(32);
   3 BEGIN
   4 Var := f_demo_fun('user1', 30);
   5 DBMS_OUTPUT.PUT_LINE(var);
   6 Var := f_demo_fun('user2', age => 40);
   7 DBMS_OUTPUT.PUT_LINE(var);
   8 Var := f_demo_fun('user3', sex => '女', age => 20);
   9 DBMS_OUTPUT.PUT_LINE(var);
   10 END;
   11 /
   user1：30歲，男
   user2：40歲，男
   user3：20歲，女

PL/SQL procedure successfully completed.
3 存儲過程
3.1 創(chuàng)建過程

　　建立存儲過程

　　在 ORACLE SERVER上建立存儲過程,可以被多個應(yīng)用程序調(diào)用,可以向存儲過程傳遞參數(shù),也可以向存儲過程傳回參數(shù).

　　創(chuàng)建過程語法:

CREATE [OR REPLACE] PROCEDURE procedure_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 ]
{ IS | AS }
<聲明部分>
BEGIN
<執(zhí)行部分>
EXCEPTION
<可選的異常錯誤處理程序>
END procedure_name;
　　說明：相關(guān)參數(shù)說明參見函數(shù)的語法說明。
例3．刪除指定員工記錄；
CREATE OR REPLACE PROCEDURE P_DelEmployees
(v_empno IN employees.employee_id%TYPE)
AS
No_result EXCEPTION;
BEGIN
DELETE FROM employees WHERE employee_id = v_empno;
IF SQL%NOTFOUND THEN
RAISE no_result;
END IF;
DBMS_OUTPUT.PUT_LINE('編碼為'||v_empno||'的員工已被刪除!');
EXCEPTION
WHEN no_result THEN
DBMS_OUTPUT.PUT_LINE('溫馨提示:你需要的數(shù)據(jù)不存在!');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END P_DelEmployees;
/
3.2 調(diào)用存儲過程

　　 存儲過程建立完成后，只要通過授權(quán)，用戶就可以在SQLPLUS 、ORACLE開發(fā)工具或第三方開發(fā)工具中來調(diào)用運(yùn)行。對于參數(shù)的傳遞也有三種：按位置傳遞、按名稱傳遞和組合傳遞，傳遞方法與函數(shù)的一樣。ORACLE 使用EXECUTE 語句來實(shí)現(xiàn)對存儲過程的調(diào)用：

EXEC[UTE] procedure_name( parameter1, parameter2…);
SQL> exec P_DelEmployees(300);
溫馨提示:你需要的數(shù)據(jù)不存在!

PL/SQL procedure successfully completed.
SQL> declare
2 v_Employeesid number;
3 begin
4 v_Employeesid:=300;
5 P_DelEmployees(v_Employeesid);
6 end;
7 /
溫馨提示:你需要的數(shù)據(jù)不存在!

PL/SQL procedure successfully completed.
3.3 AUTHID

過程中的AUTHID 指令可以告訴ORACLE ，這個過程使用誰的權(quán)限運(yùn)行．默任情況下，存儲過程會作為調(diào)用者的過程運(yùn)行，但是具有設(shè)計(jì)者的特權(quán)．這稱為設(shè)計(jì)者權(quán)利運(yùn)行．
與過程相關(guān)數(shù)據(jù)字典

USER_SOURCE, ALL_SOURCE, DBA_SOURCE, USER_ERRORS,

ALL_PROCEDURES,USER_OBJECTS,ALL_OBJECTS,DBA_OBJECTS

相關(guān)的權(quán)限:

CREATE ANY PROCEDURE
DROP ANY PROCEDURE
在SQL*PLUS 中，可以用DESCRIBE 命令查看過程的名字及其參數(shù)表。

DESC[RIBE] Procedure_name;
刪除過程和函數(shù)

　　1．刪除過程

　　可以使用DROP PROCEDURE命令對不需要的過程進(jìn)行刪除，語法如下：

DROP PROCEDURE [user.]Procudure_name;
　　2．刪除函數(shù)

　　可以使用DROP FUNCTION 命令對不需要的函數(shù)進(jìn)行刪除，語法如下：

DROP FUNCTION [user.]Function_name;
使用過程與函數(shù)具有如下優(yōu)點(diǎn)：

1、共同使用的代碼可以只需要被編寫和測試一次，而被需要該代碼的任何應(yīng)用程序（如：.NET、C++、JAVA、VB程序，也可以是DLL庫）調(diào)用。

2、這種集中編寫、集中維護(hù)更新、大家共享（或重用）的方法，簡化了應(yīng)用程序的開發(fā)和維護(hù)，提高了效率與性能。

3、這種模塊化的方法，使得可以將一個復(fù)雜的問題、大的程序逐步簡化成幾個簡單的、小的程序部分，進(jìn)行分別編寫、調(diào)試。因此使程序的結(jié)構(gòu)清晰、簡單，也容易實(shí)現(xiàn)。

4、可以在各個開發(fā)者之間提供處理數(shù)據(jù)、控制流程、提示信息等方面的一致性。

5、節(jié)省內(nèi)存空間。它們以一種壓縮的形式被存儲在外存中，當(dāng)被調(diào)用時才被放入內(nèi)存進(jìn)行處理。并且，如果多個用戶要執(zhí)行相同的過程或函數(shù)時，就只需要在內(nèi)存中加載一個該過程或函數(shù)。

6、提高數(shù)據(jù)的安全性與完整性。通過把一些對數(shù)據(jù)的操作放到過程或函數(shù)中，就可以通過是否授予用戶有執(zhí)行該過程或的權(quán)限，來限制某些用戶對數(shù)據(jù)進(jìn)行這些操作。

過程與函數(shù)的相同功能有：

1、 都使用IN模式的參數(shù)傳入數(shù)據(jù)、OUT模式的參數(shù)返回數(shù)據(jù)。
2、 輸入?yún)?shù)都可以接受默認(rèn)值，都可以傳值或傳引導(dǎo)。
3、 調(diào)用時的實(shí)際參數(shù)都可以使用位置表示法、名稱表示法或組合方法。
4、 都有聲明部分、執(zhí)行部分和異常處理部分。
5、 其管理過程都有創(chuàng)建、編譯、授權(quán)、刪除、顯示依賴關(guān)系等。
使用過程與函數(shù)的原則：

1、如果需要返回多個值和不返回值，就使用過程；如果只需要返回一個值，就使用函數(shù)。

2、過程一般用于執(zhí)行一個指定的動作，函數(shù)一般用于計(jì)算和返回一個值。

3、可以SQL語句內(nèi)部（如表達(dá)式）調(diào)用函數(shù)來完成復(fù)雜的計(jì)算問題，但不能調(diào)用過程。所以這是函數(shù)的特色。