過程與函數

過程與函數（另外還有包與觸發器）是命名的PL/SQL塊（也是用戶的方案對象），被編譯后存儲在數據庫中，以備執行。因此，其它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 expression
EXCEPTION
異常處理部分
END function_name; 　　
　　l IN,OUT,IN OUT是形參的模式。若省略，則為IN模式。IN模式的形參只能將實參傳遞給形參，進入函數內部，但只能讀不能寫，函數返回時實參的值不變。OUT模式的形參會忽略調用時的實參值（或說該形參的初始值總是NULL），但在函數內部可以被讀或寫，函數返回時形參的值會賦予給實參。IN OUT具有前兩種模式的特性，即調用時，實參的值總是傳遞給形參，結束時，形參的值傳遞給實參。調用時，對于IN模式的實參可以是常量或變量，但對于OUT和IN OUT模式的實參必須是變量。

　　l 一般，只有在確認function_name函數是新函數或是要更新的函數時，才使用OR REPALCE關鍵字，否則容易刪除有用的函數。
例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('你需要的數據不存在!');
15 WHEN OTHERS THEN
16 DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
17 END f_get_salary;
18 /
Function created.

2. 函數的調用

　　函數聲明時所定義的參數稱為形式參數，應用程序調用時為函數傳遞的參數稱為實際參數。應用程序在調用函數時，可以使用以下三種方法向函數傳遞參數：

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

　　即在調用時按形參的排列順序，依次寫出實參的名稱，而將形參與實參關聯起來進行傳遞。用這種方法進行調用，形參與實參的名稱是相互獨立，沒有關系，強調次序才是重要的。

　　格式為：

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

　　即在調用時按形參的名稱與實參的名稱，寫出實參對應的形參，而將形參與實參關聯起來進行傳遞。這種方法，形參與實參的名稱是相互獨立的，沒有關系，名稱的對應關系才是最重要的，次序并不重要。

　　格式為：

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

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

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||'，人數：'||v_num);
7 end;
8 /
10號部門工資總和：4400，人數：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||'，人數：'||:v_num);
3* end;
10號部門工資總和：4400，人數：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||'，人數：'||v_num);
7 end;
8 /
10號部門工資總和：4400，人數：1

PL/SQL procedure successfully completed.

3. 參數默認值
   在CREATE OR REPLACE FUNCTION 語句中聲明函數參數時可以使用DEFAULT關鍵字為輸入參數指定默認值。
   例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;
   具有默認值的函數創建后，在函數調用時，如果沒有為具有默認值的參數提供實際參數值，函數將使用該參數的默認值。但當調用者為默認參數提供實際參數時，函數將使用實際參數值。在創建函數時，只能為輸入參數設置默認值，而不能為輸入/輸出參數設置默認值。
   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 創建過程

　　建立存儲過程

　　在 ORACLE SERVER上建立存儲過程,可以被多個應用程序調用,可以向存儲過程傳遞參數,也可以向存儲過程傳回參數.

　　創建過程語法:

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
<執行部分>
EXCEPTION
<可選的異常錯誤處理程序>
END procedure_name;
　　說明：相關參數說明參見函數的語法說明。
例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('溫馨提示:你需要的數據不存在!');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END P_DelEmployees;
/
3.2 調用存儲過程

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

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

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 /
溫馨提示:你需要的數據不存在!

PL/SQL procedure successfully completed.
3.3 AUTHID

過程中的AUTHID 指令可以告訴ORACLE ，這個過程使用誰的權限運行．默任情況下，存儲過程會作為調用者的過程運行，但是具有設計者的特權．這稱為設計者權利運行．
與過程相關數據字典

USER_SOURCE, ALL_SOURCE, DBA_SOURCE, USER_ERRORS,

ALL_PROCEDURES,USER_OBJECTS,ALL_OBJECTS,DBA_OBJECTS

相關的權限:

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

DESC[RIBE] Procedure_name;
刪除過程和函數

　　1．刪除過程

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

DROP PROCEDURE [user.]Procudure_name;
　　2．刪除函數

　　可以使用DROP FUNCTION 命令對不需要的函數進行刪除，語法如下：

DROP FUNCTION [user.]Function_name;
使用過程與函數具有如下優點：

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

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

3、這種模塊化的方法，使得可以將一個復雜的問題、大的程序逐步簡化成幾個簡單的、小的程序部分，進行分別編寫、調試。因此使程序的結構清晰、簡單，也容易實現。

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

5、節省內存空間。它們以一種壓縮的形式被存儲在外存中，當被調用時才被放入內存進行處理。并且，如果多個用戶要執行相同的過程或函數時，就只需要在內存中加載一個該過程或函數。

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

過程與函數的相同功能有：

1、 都使用IN模式的參數傳入數據、OUT模式的參數返回數據。
2、 輸入參數都可以接受默認值，都可以傳值或傳引導。
3、 調用時的實際參數都可以使用位置表示法、名稱表示法或組合方法。
4、 都有聲明部分、執行部分和異常處理部分。
5、 其管理過程都有創建、編譯、授權、刪除、顯示依賴關系等。
使用過程與函數的原則：

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

2、過程一般用于執行一個指定的動作，函數一般用于計算和返回一個值。

3、可以SQL語句內部（如表達式）調用函數來完成復雜的計算問題，但不能調用過程。所以這是函數的特色。