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

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

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

?

準備部分(復(fù)習(xí)):

? ①準備表:

??? 創(chuàng)建一個存放最終數(shù)據(jù)的表:

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

? ②準備知識點:

??? 如果只是計算數(shù)據(jù)用function,如果需要修改數(shù)據(jù)用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必須返回一個結(jié)果,在定義部分"return 返回類型",在塊中用"return 返回值",該值必須是前面定義的數(shù)據(jù)類型

???? 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;

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

不論是function還是procedure都有3種參數(shù):

????????????? 輸入?yún)?shù) in:表示該參數(shù)只能接收數(shù)據(jù),不能對外提供數(shù)據(jù)讀取

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

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

接下來我們就可以直接從外部通過方法調(diào)用來完成我們需要的業(yè)務(wù)了:

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循環(huán)(見本人前1,2篇文章所述,這里不再詳談),定義好了函數(shù)和過程后我們就可以當(dāng)系統(tǒng)函數(shù)來使用了,使用方法與使用系統(tǒng)函數(shù)一樣,傳入我們需要的參數(shù)即可,本例是自動傳參的.

?

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

?

其他:

包的創(chuàng)建和使用,如下圖:

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

?

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

?

每日一問:

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

<!-- -->

Oracle學(xué)習(xí)筆記(三)——函數(shù)與過程