Oracle基礎 自訂函數

標籤：

一、函數

　　函數與預存程序相似，也是資料庫中儲存的已命名PL-SQL程式塊。函數的主要特徵是它必須有一個傳回值。通過return來指定函數的傳回型別。在函數的任何地方可以通過return expression語句從函數返回，傳回型別必須和聲明的傳回型別一致。

　　文法：

　　create [or replace] function function_name

　　[(parameter_list)]

　　return datatype

　　{is/as}

　　[local_declarations]

　　begin

　　　　executable_statements;

　　[exception

　　　　exception_handlers;]

　　end;

　　說明：

　　function_name：函數名稱。

　　parameter_list：函數列表，可選。

　　return 自居：指定函數的傳回型別，不能指定大小。

　　local_declarations：局部變數聲明，可選。

　　executable_statements：要執行的PL-SQL語句。

　　exception_handlers：異常處理，可選。

　　or repalce：是否覆蓋，可選。

 

　　例：根據員工編號獲得員工薪水。　　

--根據no查詢salCREATE OR REPLACE FUNCTION fun_getSal(eno NUMBER)RETURN NUMBER   --傳回型別ASv_sal emp.sal%TYPE;BEGIN  SELECT sal INTO v_sal FROM emp WHERE empno = eno;  RETURN v_sal;  EXCEPTION    WHEN OTHERS THEN      raise_application_error(-20012,‘該僱員不存在‘);END fun_getSal;

　　調用函數：

--調用函數，自訂異常處理DECLARE   v_sal NUMBER;  emp_20012 EXCEPTION;  PRAGMA EXCEPTION_INIT(emp_20012,-20012);BEGIN  v_sal:=fun_getsal(7788);  dbms_output.put_line(‘sal:‘||v_sal);  EXCEPTION    WHEN emp_20012 THEN      dbms_output.put_line(‘該僱員不存在‘);END;

　　注意：

　　1、函數參數和傳回型別只聲明參數，不指定大小。

　　2、可執行部分至少有一條return語句。

　　3、調用函數時，不能將函數作為單獨的語句存在，可以作為運算式的一部分。

 

 

二、函數和預存程序的優點：

　　1、共同使用的代碼可以只需要被編寫一次，而被需要該代碼的任何應用程式調用（.net,c++,java，也可以使DLL庫）。

　　2、這種幾種編寫、幾種維護更新、大家共用的方法，簡化了應用程式的開發維護，提高了效率和效能。

　　3、這種模組化的方法使得一個複雜的問題、大的程式逐步簡化成幾個簡單的、小的程式部分，進行分別編寫，因此程式的結構更加清晰，簡單，也容易實現。

　　4、可以在各個開發人員之間提供處理資料、控制流程程、提示資訊等方面的一致性。

　　5、節省記憶體空間。它們以一種壓縮的形式被儲存在外存中，當被調用時才被放入記憶體進行處理。而且多個使用者在調用同一個預存程序或函數時，只需要載入一次即可。

　　6、提高資料的安全性和完整性。通過把一些對資料的操作方到預存程序或函數中，就可以通過是否授予使用者有執行該語句的許可權，來限制某些使用者對資料庫進行這些操作。

 

三、函數和預存程序的區別：

　　1、預存程序使用者在資料庫中完成特定操作或者任務（如插入，刪除等），函數用於返回特定的資料。

　　2、預存程序聲明用procedure，函數用function。

　　3、預存程序不需要傳回型別，函數必須要傳回型別。

　　4、預存程序可作為獨立的pl-sql執行，函數不能作為獨立的plsql執行，必須作為運算式的一部分。

　　5、預存程序只能通過out和in/out來傳回值，函數除了可以使用out，in/out以外，還可以使用return傳回值。

　　6、sql語句（DML或SELECT)中不可用調用預存程序，而函數可以。

 

四、適用場合：

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

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

　　3、可以再SQL內部調用函數來完成複雜的計算問題，但不能調用預存程序。

Oracle基礎 自訂函數