oracle存儲(chǔ)過(guò)程與函數(shù)

1、第十四章 存儲(chǔ)過(guò)程與函數(shù)第十三章 存儲(chǔ)過(guò)程與函數(shù)定義 ：過(guò)程（函數(shù)）是在服務(wù)器端運(yùn)行的完成一定功能的一段pl/sql程序。優(yōu)點(diǎn)：存儲(chǔ)過(guò)程因?yàn)镾QL語(yǔ)句已經(jīng)預(yù)編繹過(guò)了，因此運(yùn)行的速度比較快。存儲(chǔ)過(guò)程可以接受參數(shù)、輸出參數(shù)、返回單個(gè)或多個(gè)結(jié)果集以及返回值?？梢韵虺绦蚍祷劐e(cuò)誤原因。存儲(chǔ)過(guò)程運(yùn)行比較穩(wěn)定，不會(huì)有太多的錯(cuò)誤。只要一次成功，以后都會(huì)按這個(gè)程序運(yùn)行。第十三章 存儲(chǔ)過(guò)程與函數(shù)存儲(chǔ)過(guò)程主要是在服務(wù)器上運(yùn)行，減少對(duì)客戶機(jī)的壓力。存儲(chǔ)過(guò)程可以包含程序流、邏輯以及對(duì)數(shù)據(jù)庫(kù)的查詢。同時(shí)可以實(shí)體封裝和隱藏了數(shù)據(jù)邏輯。存儲(chǔ)過(guò)程可以在單個(gè)存儲(chǔ)過(guò)程中執(zhí)行一系列SQL語(yǔ)句。存儲(chǔ)過(guò)程可以從自己的存儲(chǔ)過(guò)程內(nèi)引用其它存

2、儲(chǔ)過(guò)程，這可以簡(jiǎn)化一系列復(fù)雜語(yǔ)句。 14.1 存儲(chǔ)過(guò)程 創(chuàng)建過(guò)程語(yǔ)法:create or replace procedure procedure_name (argment in| in out type, argment in | out | in out type is | as ( 注: 不用 declare 語(yǔ)句 ) Begin exception end;14.1 存儲(chǔ)過(guò)程這里的IN表示向存儲(chǔ)過(guò)程傳遞參數(shù)，OUT表示從存儲(chǔ)過(guò)程返回參數(shù)。而IN OUT 表示傳遞參數(shù)和返回參數(shù)；在存儲(chǔ)過(guò)程內(nèi)的變量類型只能指定變量類型；不能指定長(zhǎng)度；在AS或IS 后聲明要用到的變量名稱和變量類型及長(zhǎng)度；在

3、AS或IS 后聲明變量不要加declare 語(yǔ)句。 14.1 存儲(chǔ)過(guò)程CREATE OR REPLACE PROCEDURE ModeTest ( p_InParameter IN NUMBER, p_OutParameter OUT NUMBER, p_InOutParameter IN OUT NUMBER) IS v_LocalVariable NUMBER;14.1 存儲(chǔ)過(guò)程BEGIN v_LocalVariable := p_InParameter; - Legal p_InParameter := 7; - Illegal p_OutParameter := 7; - Legal

4、v_LocalVariable := p_outParameter; - Illegal v_LocalVariable := p_InOutParameter; - Legal p_InOutParameter := 7; - LegalEND ModeTest;/14.1 存儲(chǔ)過(guò)程使用過(guò)程 存儲(chǔ)過(guò)程建立完成后，只要通過(guò)授權(quán)，用戶就可以在SQLPLUS 、Oracle開發(fā)工具或第三方開發(fā)工具來(lái)調(diào)用運(yùn)行。Oracle 使用EXECUTE 語(yǔ)句來(lái)實(shí)現(xiàn)對(duì)存儲(chǔ)過(guò)程的調(diào)用。14.1 存儲(chǔ)過(guò)程開發(fā)存儲(chǔ)過(guò)程步驟1、 編輯存儲(chǔ)過(guò)程源碼使用文字編輯處理軟件編輯存儲(chǔ)過(guò)程源碼，要用類似WORD 文字處理軟件進(jìn)行編

5、輯時(shí)，要將源碼存為文本格式。2、 對(duì)存儲(chǔ)過(guò)程程序進(jìn)行解釋在SQLPLUS或用調(diào)試工具將 存儲(chǔ)過(guò)程程序進(jìn)行解釋；如：SQLstart c:stat1.sql3、 調(diào)試源碼直到正確。 在SQLPLUS下來(lái)調(diào)試主要用的方法是：使用 SHOW ERROR命令來(lái)提示源碼的錯(cuò)誤位置；使用 user_errors 數(shù)據(jù)字典來(lái)查看各存儲(chǔ)過(guò)程的錯(cuò)誤位置。 14.1 存儲(chǔ)過(guò)程例1創(chuàng)建一個(gè)存儲(chǔ)過(guò)程，完成給定的員工號(hào)以后，刪除該員工。CREATE OR REPLACE PROCEDURE DelEmp(p_empno IN emp.empno%TYPE) ASNo_result EXCEPTION;BEGIN DEL

6、ETE FROM emp WHERE empno=p_empno; IF SQL%NOTFOUND THEN RAISE no_result; END IF; DBMS_OUTPUT.PUT_LINE(編碼為|p_empno|的員工已被除名!);14.1 存儲(chǔ)過(guò)程EXCEPTION WHEN no_result THEN DBMS_OUTPUT.PUT_LINE(你需要的數(shù)據(jù)不存在!); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(發(fā)生其它錯(cuò)誤!);END;14.1 存儲(chǔ)過(guò)程例2 給指定的員工加薪。CREATE OR REPLACE PROCEDURE mon_

7、addsal(p_empno in emp.empno%TYPE,p_addsal in m%TYPE) ASno_result EXCEPTION;BEGINUPDATE empSET comm=p_addsalWHERE empno=p_empno;IF SQL%NOTFOUND THEN RAISE no_result;END IF; 14.1 存儲(chǔ)過(guò)程DBMS_OUTPUT.PUT_LINE(p_empno|的本月加薪額度為|p_addsal);EXCEPTIONWHEN no_result THENDBMS_OUTPUT.PUT_LINE(該員工不存在!);WHEN OTHERS T

8、HENDBMS_OUTPUT.PUT_LINE(未知錯(cuò)誤!) ;END;14.2 創(chuàng)建函數(shù) Oracle的函數(shù)是一個(gè)獨(dú)有的對(duì)象，它也是由PL/SQL語(yǔ)句編寫而成，但的不同的地方是：函數(shù)必須返回某些值，而存儲(chǔ)過(guò)程可以不返回任何值。 14.2 創(chuàng)建函數(shù)CREATE FUNCTION語(yǔ)法如下：create or replace function function_name (argment in| in out TYPE, argment in | out | in out type return return_type is | as begin function_body exception .

9、 . . . . . end;14.2 創(chuàng)建函數(shù)例1：創(chuàng)建一個(gè)函數(shù)，完成給定的部門號(hào)以后，求出該部門的所有員工的工資和 。CREATE OR REPLACE FUNCTION f_sum_sal(deptid IN emp.deptno%TYPE) RETURN NUMBER ASv_sumsal number;BEGINSELECT SUM(sal)+SUM(nvl(comm,0) INTO v_sumsal FROM emp WHERE deptno=deptid;-DBMS_OUTPUT.PUT_LINE(deptid|的工資和為|v_sumsal);RETURN V_sumsal;14

10、.2 創(chuàng)建函數(shù)EXCEPTIONWHEN NO_DATA_FOUND THENDBMS_OUTPUT.PUT_LINE(你需要的數(shù)據(jù)不存在!);WHEN OTHERS THENDBMS_OUTPUT.PUT_LINE(發(fā)生其它錯(cuò)誤!);END;14.2 創(chuàng)建函數(shù)調(diào)用過(guò)程：DECLARE v_sum_sal number;BEGINV_sum_sal:=f_sum_sal(20);DBMS_OUTPUT.PUT_LINE(總工資是|v_sum_sal);END;14.3 過(guò)程和函數(shù)中的例外處理 使用例外處理的必要性一旦出現(xiàn)例外的情況，Oracle就自動(dòng)終止程序的運(yùn)行。當(dāng)程序出錯(cuò)時(shí)用戶無(wú)法得到提示

11、，調(diào)試者也無(wú)法進(jìn)行修改程序。一般無(wú)論多簡(jiǎn)單的程序最好也要給出例外處理的要求。 14.3 過(guò)程和函數(shù)中的例外處理1、用戶定義的例外必須聲明且必須用RAISE語(yǔ)句來(lái)激活no_result EXCEPTION;BEGINIF SQL%NOTFOUND THEN RAISE no_result;END IF;EXCEPTIONWHEN no_result THENDBMS_OUTPUT.PUT_LINE(該員工不存在!);WHEN OTHERS THENDBMS_OUTPUT.PUT_LINE(未知錯(cuò)誤!) ;END;14.3 過(guò)程和函數(shù)中的例外處理使用戶EXCEPTION_INIT處理 . Prag

12、ma（也叫偽指令）是在編譯時(shí)內(nèi)處理，不是在運(yùn)行時(shí)被處理。DECLAREdeadlock_detected EXCEPTION;PRAGMA EXCEPTION_INIT(deadlock_detected, -60);BEGIN.EXCEPTIONWHEN deadlock_detected THEN- handle the errorEND;14.3 過(guò)程和函數(shù)中的例外處理使用戶raise_application_error處理 BEGINSELECT sal INTO curr_sal FROM emp WHERE empno = emp_id;IF curr_sal IS NULL TH

13、EN/* 發(fā)布用戶定義的錯(cuò)誤信息 */raise_application_error(-20101, Salary is missing);ELSEUPDATE emp SET sal = curr_sal + amount WHERE empno = emp_id;END IF;END raise_salary;與存儲(chǔ)過(guò)程、函數(shù)相關(guān)數(shù)據(jù)字典user_source 用戶的存儲(chǔ)過(guò)程、函數(shù)的源代碼字典all_source 所有用戶的存儲(chǔ)過(guò)程、函數(shù)的源代碼字典user_errors 用戶的存儲(chǔ)過(guò)程、函數(shù)的源代碼存在錯(cuò)誤的信息字典相關(guān)的權(quán)限:create any proceduredrop any procedure如果某個(gè)用戶沒有權(quán)限來(lái)創(chuàng)建存儲(chǔ)過(guò)程，則需要DBA將創(chuàng)