Oracle–plsql函數

• 一個儲存函數是一個命名的PL/SQL 塊，它可以接受參數並且可以被調用。一般來說，函數用於計算一個值。
• 函數和過程有相似的結構，但函數必須返回一個值到主叫環境。反之，過程可以沒有傳回值，也可以由多個值返回到它的主叫環境。
• 象過程一樣，函數有一個頭，一個聲明部分，一個執行部分和一個異常處理部分。在函數的頭中必須有一個RETURN 子句，並且在執行部分至少有一個 RETURN 語句。
• 函數可以作為一個方案對象被儲存在資料庫中用於反覆執行。
• 函數可以作為一個SQL 運算式或 PL/SQL運算式的一部分被調用。

函數文法：CREATE [OR REPLACE] FUNCTION function_name<br />[(parameter1 [mode1] datatype1,<br /> parameter2 [mode2] datatype2,<br /> . . .)]<br />RETURN datatype<br />IS|AS<br />PL/SQL Block;<br /> PL/SQL
塊必須至少有一個 RETURN 語句

文法說明

參數  說明  

function_name   函數的名字  

parameter   一個 PL/SQL變數的名字，其值被傳遞到函數中  

mode  參數的類型；可以是 IN、out、inout 參數 ,一般使用IN參數

datatype   參數的資料類型  

RETURNdatatype  RETURN 值的資料類型  

PL/SQLblock   定義要由函數執行的動作的程式體   

使用步驟：

1.在一個編輯器中輸入CREATE FUNCTION語句的文本並且將它儲存為一個SQL 指令檔
2.運行儲存原始碼的指令檔並且編譯函數
3.用SHOW ERRORS查看編譯錯誤

CREATE OR REPLACE FUNCTION get_sal<br /> (p_id IN emp.empno%TYPE)<br /> RETURN NUMBER<br />IS<br /> v_sal emp.sal%TYPE :=0;<br />BEGIN<br /> SELECT sal<br /> INTO v_sal<br /> FROM emp<br /> WHERE empno = p_id;<br /> RETURN v_sal;<br />END get_sal;<br />/ <br />在sql*plus建立函數並調用

VARIABLE g_sal NUMBER

EXEC :g_sal := get_sal(7369)

Print g_sal

調用使用者定義函數的位置

•SELECT語句的Select
列表中•WHERE和HAVING子句的條件中•START WITH、ORDER BY和GROUPBY子句中•INSERT語句的VALUES子句中•UPDATE語句的SET子句中

SELECT empno, tax(sal)

FROM  emp

WHERE tax(sal)>(SELECTMAX(tax(sal))

       FROM  emp

       WHERE deptno = 30)

ORDER BY tax(sal) DESC;

從SQL中調用函數的限制

為了從SQL 運算式中調用函數，使用者定義函數必須：

•只接受IN參數•只接受有效SQL 資料類型，而不接受PL/SQL特殊的類型

 作為參數

•返回資料類型為有效SQL資料類型，而非PL/SQL 特殊的

 類型

•在一個表上的UPDATE或DELETE語句中調用的函數不能查詢及更新同一個表•從SQL
語句中調用的函數不能包含結束事務的語句

函數刪除：

DROP FUNCTION function_name<br />

過程與函數：