oracle教程07plsql高級01

1、 PL/SQLPL/SQL高級編程高級編程 存儲過程函數(shù)的概念開發(fā)一個存儲過程或函數(shù)目的是把一個 PLSQL塊存進數(shù)據(jù)庫中，并在以后重復(fù)使用。例如：創(chuàng)建一個記錄用戶名和當(dāng)前日期的 無參數(shù)過程CREATE PROCEDURE log_execution ISBEGIN INSERT INTO log_table(user_id, log_date) VALUES(user,sysdate)；END；/可以在不同的環(huán)境調(diào)用同一個存儲過程。注意：語法有區(qū)別 在 SQL*Plus中記錄用戶名和當(dāng)前日期SQL EXECUTE log_execution； 從存儲過程 FIRE_EMP中記錄用戶名和當(dāng)前日

2、期CREATE PROCEDUER fire_emp (v_emp_no IN emp.empnoTYPE) IS BEGIN log_execution； DELETE FROM emp WHERE empno=v_emp_no； END；/存儲過程與應(yīng)用程序的區(qū)別 開發(fā)存儲過程和函數(shù) 1、開發(fā)一個存儲過程和函數(shù)的步驟 （1）用文本編輯器編寫一個含有Create Procedure或Create Function語句的PL/SQL腳本文件。（如：c:procedure1.sql） （2）在 SQL*Plus中用命令（如：SQLstart c:procedure1.sql;）編譯腳本文件，調(diào)試

3、編譯錯誤。系統(tǒng)就將創(chuàng)建過程或函數(shù)的源代碼存入數(shù)據(jù)字典user_source中 。（3） 編譯成功后將編譯代碼存入數(shù)據(jù)字典（4） 調(diào)用存儲過程和函數(shù)，在運行期間調(diào)試存儲過程、函數(shù)的邏輯錯誤。 創(chuàng)建存儲過程的語法注意：注意：IS IS 后面是一個完整的后面是一個完整的PL/SQLPL/SQL塊的三部分（參見第六章），可以定義局部塊的三部分（參見第六章），可以定義局部變量、游標(biāo)等，但不能以變量、游標(biāo)等，但不能以 DECLAREDECLARE開始。開始。 CREATE OR REPLACE PROCEDURE 過程名 (參數(shù)名 IN | OUT | IN OUT 數(shù)據(jù)類型, )IS | AS 說明部分

4、BEGIN 語句序列 EXCEPTION 出錯處理END 過程名； 形式參數(shù)可以有三種模式-IN、OUT、IN OUT。如果沒有為形式參數(shù)指定模式，那么缺省的模式是IN。 類型 描述-IN(缺省)參數(shù) 用來從調(diào)用環(huán)境中向過程傳遞值OUT參數(shù) 用來從過程中返回值給調(diào)用者IN OUT參數(shù) 既可從調(diào)用者向過程中傳遞值， 也可以從過程中返回可能改變了 的值給調(diào)用者局部變量 在過程內(nèi)部存放值例：下面的過程說明了三種模式參數(shù)的區(qū)別create or replace procedure modetest( p_inparameter in number, p_outparameter out number,

5、 p_inoutparameter in out number) is v_localvariable number;begin v_localvariable:=p_inparameter; /*正確*/ (p_inparameter:=7; 錯誤) v_outparameter:=7; /*正確*/ (v_localvariable:=p_outparameter; 錯誤) v_localvariable:=p_inoutparameter; /*正確*/ v_inoutparameter:=7; /*正確*/end; /例：下列存儲過程給某一指定的員工漲指定數(shù)量的工資。Create pr

6、ocedure raise_salary (emp_id integer, v_increase real)is begin update emp set sal= sal + v_increase where empno=emp_id ; commit;end ;/例：下列過程根據(jù)給定的員工號返回員工的姓名、工資和獎金等信息。CREATE OR REPLACE PROCEDURE query_emp (v_emp_no IN emp.empnoTYPE， V_emp_name OUT emp.enameTYPE， v_emp_sal OUT emp.salTYPE， V_emp_comm O

7、UT mTYPE)ISBEGIN SELECT ename，sal，comm INTO v_emp_name，v_emp_sal， v_emp_commFROM emp WHERE empno=v_emp_no;END query_emp；/例：利用 IN OUT模式參數(shù)，將一個7位數(shù)字的電話號碼轉(zhuǎn)換成8位數(shù)字的電話號碼。CREATE OR REPLACE PROCEDURE add_dash (v_phone_no IN OUT VARCHAR2) /*字符型的形式參數(shù)不指定長度*/ISBEGIN v_phone_no:SUBSTR (v_phone_no,l,3) |-|SUBSTR (v

8、_phone_no，4，4)；END add_dash；/ /創(chuàng)建存儲函數(shù)的語法CREATE OR REPLACE FUNCTION 函數(shù)名 （參數(shù)名 IN 數(shù)據(jù)類型 .） RETURN 數(shù)據(jù)類型 IS | AS 說明部分BEGIN 語句序列 RETURN （表達(dá)式） EXCEPTION 例外處理程序END 函數(shù)名；例：根據(jù)員工號獲取該員工工資的查詢CREATE OR REPLACE FUNCTION get_sal (p_emp_no IN emp.empnoTYPE)RETURN NUMBERIS v_emp_sal emp.salTYPE:0；BEGIN SELECT sal INTO

9、v_emp_sal FROM emp WHERE empno=p_emp_no; RETURN(v_emp_sal)；Exception When no_data_found or too_many_rows then Dbms_output.put_line(發(fā)生系統(tǒng)錯誤); When others then Dbms_output.put_line(sqlerrm);END get_sal；/例：編寫一個函數(shù)，計算幾個人的平均工資，并在函數(shù)體的循環(huán)過程中輸出結(jié)果。CREATE OR REPLACE FUNCTION average_sal(v_n IN NUMBER(3) RETURN N

10、UMBERIS CURSOR c_emp IS SELECT empno，sal FROM emp； v_total_sal emp.salTYPE:=0； v_counter number; v_emp_no emp.empno%type;BEGIN FOR r_emp IN c_emp LOOP EXIT WHEN c_emp ROWCOUNT v_n OR c_empNOTFOUND； v_total_sal:=v_total_sal + r_emp.sal； v_counter:=c_empROWCOUNT； v_emp_no:=r_emp.empno； DBMS_OUTPUT.PUT

11、_LINE(loop=| v_counter|；Empno=| v_emp_no)； END LOOP； RETURN(v_total_salv_counter)；END average_sal；/存儲過程與函數(shù)的區(qū)別 存儲過程和函數(shù)的主要差別有兩個： 一是返回值的方法不同 二是調(diào)用方法不同（1）返回值的方法不同 存儲函數(shù)：有零個或多個參數(shù)，但不能有OUT參數(shù)。函數(shù)只返回一個值，靠RETURN子句返回。 存儲過程：有零個或多個參數(shù)，過程不返回值，其返回值是靠OUT參數(shù)帶出來的。（2）調(diào)用方法不同調(diào)用過程的語句可以作為單獨的可執(zhí)行語句在PL/SQL塊中單獨出現(xiàn)。如：過程名（實際參數(shù)1，實際參數(shù)2

12、.）；函數(shù)可以在任何表達(dá)式能夠出現(xiàn)的地方被調(diào)用，調(diào)用函數(shù)的語句不能作為可執(zhí)行語句單獨出現(xiàn)在PL/SQL塊中。如：變量名：=函數(shù)名（實際參數(shù)1，實際參數(shù)2.） 存儲過程和函數(shù)中的出錯處理 存儲過程和函數(shù)就是一個PL/SQL塊，所以在過程函數(shù)體內(nèi)應(yīng)該考慮出錯處理。 不管是哪種類型的出錯情況，只要在過程和函數(shù)體內(nèi)考慮了出錯處理的方法，即使在運行過程中出現(xiàn)了錯誤，過程和函數(shù)都能成功地執(zhí)行，程序不會被中斷。如果在過程和函數(shù)體內(nèi)忽略了出錯處理，過程和函數(shù)執(zhí)行時以交互方式通知用戶出錯，讓用戶自行處理，程序被中斷。例：根據(jù)給定的員工號，刪除該員工記錄。CREATE OR REPLACE PROCEDURE f

13、ire_emp(p_emp_no IN emp.empnoTYPE)IS invalid_employee EXCEPTION； （定義錯誤）BEGIN DELETE FROM emp WHERE empno=p_emp_no; IF SQLNOTFOUND THEN RAISE invalid_employee； （觸發(fā)錯誤） END IF； COMMIT WORK；EXCEPTION WHEN invalid_employee THEN （處理錯誤） ROLLBACK WORK； INSERT INTO exception_table(line_nr,line) VALUES(1,Empl

14、oyee does not exist.)； WHEN others THEN Dbms_output.put_line(sqlerrm);END fire_emp；/存儲過程和函數(shù)的管理 1 過程和函數(shù)的管理命令過程和函數(shù)的管理命令 例：刪除存儲過程FIRE_EMPSQL DROP PROCEDURE FIRE_EMP; 查看過程和函數(shù)的文檔信息查看過程和函數(shù)的文檔信息 SQL show errors;SQL show errors;存儲過程和函數(shù)的調(diào)用和測試 參數(shù)傳值一般采用位置對應(yīng)法向形式參數(shù)傳值，要求實際參數(shù)與形式參數(shù)保持次序、類型、個數(shù)一致。例：從 SQL*Plus命令中通過位置對應(yīng)

15、法調(diào)用 HIRE_EMP過程。SQL variable v_ename varchar2(12)；SQL EXECUTE hire_emp(9999，:v_ename)；注意： 如果形式參數(shù)是IN模式的參數(shù)，實際參數(shù)可以是一個具體的值，或一個有值的變量。 如果形式參數(shù)是OUT模式的參數(shù)，實際參數(shù)必須是一個變量。當(dāng)調(diào)用過程后，此變量就被賦值了。 如果形式參數(shù)是IN OUT模式的參數(shù)，則實際參數(shù)必須是一個預(yù)先已經(jīng)賦值的變量。執(zhí)行完過程后，該變量被重新賦值。 可以調(diào)用系統(tǒng)內(nèi)置的DBMS_OUTPUT包中的過程輸出此變量的值，測試過程執(zhí)行的結(jié)果。 存儲過程和函數(shù)的調(diào)用方法存儲過程和函數(shù)的調(diào)用方法 各種

16、環(huán)境調(diào)用過程和函數(shù)的語法和例子 （1） 在PL/SQL塊中如何調(diào)用 過程的調(diào)用例：從 PLSQL塊中調(diào)用過程QUERY_EMP。執(zhí)行過程后，輸出變量測試結(jié)果。DECLARE V_empno emp.empno%type:=7654; V_ename emp.ename%type; V_sal emp.sal%type; V_comm m%type;Begin Query_emp(v_empno,v_ename,v_sal,v_comm); Dbms_output.put_line(v_ename| |v_sal| |v_comm); /*測試結(jié)果*/End;/ 函數(shù)的調(diào)用例：從一個 PLSQL

17、塊中調(diào)用 GET_SAL函數(shù)。DECLARE v_empno NUMBER:7654； v_sal NUMBER；BEGIN v_sal:=get_sal(v_empno)； DBMS_OUTPUT.PUT_LINE(v_sal)； /*測試結(jié)果*/END；/在SQL*PLUS中如何調(diào)用 過程的調(diào)用例：從SQL*PLUS中調(diào)用過程QUERY_EMPSET serveroutput ON /*激活DBMS_OUTPUT 系統(tǒng)包*/ ACCEPT p_emp_no PROMPT please enter the employee number： /*接受員工號*/VARIABLE v_emp_na

18、me VARCHAR2（14）； /*定義存放OUT參數(shù)輸出結(jié)果的變量*/VARIABLE v_emp_sal NUMBER；VARIABLE v_emp_comm NUMBER；EXECUTE query_emp(&p_emp_no, ：v_emp_name, ：v_emp_sal, ：v_emp_comm);EXECUTE DBMS_OUTPUT.PUT_LINE(Information for employee：| TO_CHAR(&p_emp_no)；EXECUTE DBMS_OUTPUT.PUT_LINE(The name is：| ：v_emp_name)；EXEC

19、UTE DBMS_OUTPUT.PUT_LINE(the salary is：| TO_CHAR(：v_emp_sal)；EXECUTE DBMS_OUTPUT.PUT_LINE(The commission is：| TO_CHAR(：v_emp_comm)； 函數(shù)的調(diào)用函數(shù)不能作為一條語句單獨出現(xiàn)，只能出現(xiàn)在別的過程中，作為別的過程的參數(shù)。例：調(diào)用函數(shù)get_salSQL EXECUTE DBMS_OUTPUT.PUT_LINE(員工號7654的工資是：|get_sal(7654);或者用SELECT語句查詢函數(shù)的結(jié)果。SQL SELECT GET_SAL(7654) FROM DUAL； 在PRO*C中如何調(diào)用過程例：從一個 pro* C程序中調(diào)用過程 FIRE_EMP。 void run_fire_emp() EXEC SQL BEGIN DECLARE SECTION； int empno； EXEC SQL END DECLARE SECTION ； printf(nPlease enter the employee number：) ； scanf(d，&empno)； EXEC SQL fire_emp(:empno)； /*在過程名前面加EXEC SQL關(guān)鍵字*