函數與存儲過程是數據庫操作中很重要的一部分，為了更好的理解,我就不做太多的理論講解,下面是我為了把今天的內容融合到一起自己設計的一個需求:

??? 用我們現在手上有的兩張表:emp、dept。emp表中有員工工資,獎金等信息,dept表中有部門信息

??? 有時候我們會遇到這樣的業務:財務部門將統計出來的工資分發到每個部門,由各個部門自己發工資(個人虛構的),在這個過程中我們就需要將每個部門的總工資算出來,于是就有了今天的代碼:

?

準備部分(復習):

? ①準備表:

??? 創建一個存放最終數據的表:

CREATE TABLE getmoney(
????? deptno NUMBER,??????????? --部門編號
????? d_name VARCHAR2(20),??????--部門名字
????? money NUMBER,???????????? --該部門工資總額
????? isget VARCHAR(20)???????? --是否領取
)

? ②準備知識點:

??? 如果只是計算數據用function,如果需要修改數據用procedure

?③主體部分:

??? function部分:

CREATE OR REPLACE FUNCTION get_result(
?? p_deptno IN NUMBER
)
RETURN NUMBER
AS
??? result_money NUMBER;
BEGIN
???? result_money := 0;
???? FOR my_cursor IN (SELECT * FROM emp WHERE emp.deptno=p_deptno)
???? LOOP
??????? result_money := result_money+nvl(my_cursor.sal,0)+nvl(my_cursor.comm,0);
???? END LOOP;
???? RETURN result_money;
END;

說明 :function必須返回一個結果,在定義部分"return 返回類型",在塊中用"return 返回值",該值必須是前面定義的數據類型

???? procedure部分:

CREATE OR REPLACE PROCEDURE p_add_data(
??? p_deptno NUMBER,
??? p_name VARCHAR2,
??? p_money NUMBER,
??? p_isget VARCHAR2 DEFAULT '否'
)
AS
BEGIN
???? INSERT INTO getmoney(deptno,d_name,money,isget)
??????????? VALUES(p_deptno,p_name,p_money,p_isget);
???? COMMIT;
END;

說明 :在定義參數的時候可以使用默認值(如p_isget VARCHAR2 DEFAULT '否'),表示如果在調用該過程時沒有傳入該參數的值,則系統為使用默認值來代替,定義默認值時必須放在最后定義.注意在procedure中沒有return,如果想對外返回一個結果可以定義參數為out或者in out,見下行:

不論是function還是procedure都有3種參數:

????????????? 輸入參數 in:表示該參數只能接收數據,不能對外提供數據讀取

????????????? 輸出參數 out:表示該參數只能被讀取,不能修改

????????????? 輸入輸出參數 in out:功能結合了上面兩者,注意不能寫為out in

接下來我們就可以直接從外部通過方法調用來完成我們需要的業務了:

DECLARE
?? money NUMBER;
BEGIN
???? money := 0;
???? FOR my_deptno IN (SELECT * FROM dept)
???? LOOP
???????? money :=? scott.get_result(my_deptno.deptno);
???????? scott.p_add_data(my_deptno.deptno,my_deptno.dname,money);
???? END LOOP;
END;

這里用到了游標FOR循環(見本人前1,2篇文章所述,這里不再詳談),定義好了函數和過程后我們就可以當系統函數來使用了,使用方法與使用系統函數一樣,傳入我們需要的參數即可,本例是自動傳參的.

?

通過上面的例子,我們可以看到函數和過程不是我們想象中的那么復雜,所以我們應該有足夠的理由將ORACLE學好.

?

其他:

包的創建和使用,如下圖:

注意:包相當于JAVA中的包+接口,我們可以看到在上部分定義包中,只定義了函數而沒有具體的實現部分,在body部分做具體的實現,外部調用的時候用'包名.函數名'的方式調用

?

??ORACLE中的命名規范:
存儲過程：strore procedure,所以用 sp_
函數:function,所以用 func_或f_
觸發器:trigger,所以用 tr_
包：package，所以用pkg_
參數：parameter,所以用 p_
變量：variable,所以用 v_
表:table,t_
說明：
每個公司都有一些自定義開發規范。

?

每日一問:

? 常見問題：
?? 面對復雜業務，用其它語言(如JAVA)開發，還是用數據庫腳本(存儲過程、函數、觸發器)開發 分析?
①如果開發的系統所使用數據庫平臺有更換的可能，則其它語言開發比較好，因為不同的數據庫，腳本語言是完全不同的(方言)。
②通常電力，電信，移動等數據庫平臺相對固定，業務又相當復雜，所以數據庫腳本編程已經在大量使用。
③說明：在流行的J2EE的架構中，不建議在數據庫端處理業務，而是通過“OR映射” (如Hibernate)的方式處理。

<!-- -->

Oracle學習筆記(三)——函數與過程