oracle過程、函數(shù)和程序包ppt課件

1、過程、函數(shù)和包目的在完本錢章學(xué)習(xí)后，他將掌握以下技藝；PL/SQL過程的定義與用法PL/SQL函數(shù)的定義與用法PL/SQL包的定義與用法本章內(nèi)容PL/SQL過程PL/SQL函數(shù)PL/SQL包導(dǎo)航PL/SQL過程PL/SQL函數(shù)PL/SQL包PL/SQL過程過程是指用于執(zhí)行特定操作的PL/SQL塊經(jīng)過運(yùn)用過程，不僅可以簡(jiǎn)化客戶運(yùn)用的開發(fā)和維護(hù)，而且可以提高運(yùn)用程序的運(yùn)轉(zhuǎn)性能。語(yǔ)法：CREATE OR REPLACE PROCEDURE procedure_name(argument1mode1 datatype1,argument2mode2 datatype2,)IS|ASPL/SQL Blo

2、ck;其中procedure_name指定過程名，argument指定過程參數(shù)；IS或AS用于開場(chǎng)PL/SQL塊。PL/SQL過程-建立無參數(shù)的過程下面以建立用于輸出當(dāng)前日期和時(shí)間的過程out_time為例。CREATE OR REPLACE PROCEDURE out_timeISBEGIN DBMS_SESSION.SET_NLS(NLS_DATE_FORMAT,YYYY-MM-DD HH24:MI:SS); DBMS_OUTPUT.PUT_LINE(sysdate);END;/PL/SQL過程-建立帶有輸入?yún)?shù)的過程經(jīng)過運(yùn)用輸入?yún)?shù)，可以將運(yùn)用程序數(shù)據(jù)傳送到過程。當(dāng)定義過程參數(shù)時(shí)，默許參

3、數(shù)方式就是輸入?yún)?shù)可以運(yùn)用IN關(guān)鍵字顯式定義輸入?yún)?shù)。CREATE OR REPLACE PROCEDURE add_empl( empno empl.empno%TYPE,ename empl.ename%TYPE, ebirthday empl.ebirthday%TYPE, egrade empl.egrade%TYPE, ejob empl.ejob%TYPE,emajor empl.emajor%TYPE, indate empl.indate%TYPE,salary empl.salary%TYPE, allow empl.allow%TYPE,deptno empl.deptno

4、%TYPE) IS BEGIN INSERT INTO empl VALUES(empno,ebirthday,ename,ejob,egrade,emajor,indate,salary,allow,depno);END;PL/SQL過程-建立帶有輸出參數(shù)的過程經(jīng)過運(yùn)用輸入?yún)?shù)，可以將數(shù)據(jù)或音訊傳送到調(diào)用環(huán)境和運(yùn)用程序。當(dāng)定義輸出參數(shù)時(shí)，需求制定參數(shù)方式為OUT。下面建立用于更新雇員工資并輸出雇員姓名的過程update_sal為例。CREATE OR REPLACE PROCEDURE update_sal( eno varhcar2, sal NUMBER, name OUT VARCHA

5、R2) IS BEGIN UPDATE empl SET salary=sal WHERE empno=eno RETURNING ename INTO name;END;/PL/SQL過程-建立帶有輸入輸出參數(shù)的過程經(jīng)過在過程中運(yùn)用輸入輸出函數(shù)，可以調(diào)用過程時(shí)輸入數(shù)據(jù)到過程，在調(diào)用終了后輸出數(shù)據(jù)到調(diào)用環(huán)境和運(yùn)用程序。當(dāng)定義輸入輸出參數(shù)時(shí)，需求指定參數(shù)方式為IN OUT。CREATE OR REPLACE PROCEDURE devide( num1 IN OUT NUMBER，num2 IN OUT NUMBER) IS v1 NUMBER; v2 NUMBER;BEGIN v1:=TRUN

6、C(num1/num2); v2:=MOD(num1,num2); num1:=v1; num2:=v2;END;/PL/SQL過程-調(diào)用過程在SQL*PLUS中調(diào)用過程時(shí)，需求運(yùn)用CALL或者EXECUTE命令；而在PL/SQL塊中過程可以直接運(yùn)用。過程調(diào)用帶參數(shù)分4種情況：假設(shè)無參數(shù)，那么可以直接援用過程名；假設(shè)有輸入?yún)?shù)，那么需求為輸入?yún)?shù)提供數(shù)值；假設(shè)帶有輸出參數(shù)，那么需求運(yùn)用變量接納輸出結(jié)果；假設(shè)有輸入輸出參數(shù)，那么調(diào)用時(shí)需求使器具有輸入值的變量。PL/SQL過程-調(diào)用無參數(shù)的過程直接援用過程名exec out_timePL/SQL過程-調(diào)用帶有輸入?yún)?shù)的過程需求為輸入?yún)?shù)提供數(shù)據(jù)值

7、exec add_empl(1111, 1980-3-4, lei, programmer, 1, 7369, SYSDATE, 800, null, 30)PL/SQL過程-調(diào)用帶有輸出參數(shù)的過程需求運(yùn)用變量接受輸出參數(shù)的數(shù)據(jù)值DECLARE v_name empl.ename%TYPE;BEGIN update_sal(&eno,&salary,v_name); DBMS_OUTPUT.PUT_LINE(name:|v_name);END;/輸入 雇員號(hào)的值： 1111輸入工資的值： 2500姓名：leiPL/SQL過程-調(diào)用帶有輸入輸出參數(shù)的過程調(diào)用之前需求定義變量存放輸入值，并在調(diào)用終

8、了之后運(yùn)用變量輸出數(shù)據(jù)。DECLARE n1 NUMBER:=&n1; n2 NUMBER:=&n2;BEGIN divide(n1,n2); DBMS_OUTPUT.PUT_LINE(商:|n1|,余數(shù):|n2);END; /輸入n1的值：100輸入n2的值：30商：3，余數(shù)10為參數(shù)傳送變量或數(shù)據(jù)當(dāng)為參數(shù)傳送變量或數(shù)據(jù)，可以采用以下3種方法位置傳送稱號(hào)傳組合傳送PL/SQL過程-運(yùn)用位置傳送為參數(shù)傳送變量和數(shù)據(jù)位置傳送是指按照參數(shù)定義順序依次為每個(gè)參數(shù)提供變量或者數(shù)據(jù)值當(dāng)運(yùn)用位置傳送時(shí)，必需為一切參數(shù)提供變量或者輸入數(shù)據(jù)CALL add_empl(2222,1978-12-2， alex,

9、NULL, 1， NULL,SYSDATE,800,NULL,30);PL/SQL過程-運(yùn)用稱號(hào)傳送為參數(shù)傳送變量和數(shù)據(jù)稱號(hào)傳送是指經(jīng)過制定參數(shù)名為其提供變量或者數(shù)據(jù)當(dāng)運(yùn)用稱號(hào)傳送時(shí)，需求運(yùn)用=為參數(shù)提供變量或者數(shù)值exec add_emp(empno=3333,ebirghday=,1989-2-2，indate=null, egrade=1, ename=JOHN,ejob=null,major=null, salary=null,allow=null,deptno=null)PL/SQL過程-運(yùn)用組合傳送為參數(shù)傳送變量和數(shù)據(jù)組合傳送是指在調(diào)用子程序時(shí)結(jié)合運(yùn)用位置傳送和稱號(hào)傳送exec a

10、dd_emp(4444, 1985-2-3,AGASI , tester,1, null, indate=SYSDATE, salary=1200, allow=0, deptno=30)PL/SQL過程-過程開發(fā)例如為過程參數(shù)指定默許值CREATE OR REPLACE PROCEDURE add_emp( empno empl,empno%TYPE, ebirthday empl.ebirthday%TYPE, ename empl.ename%TYPE, ejob empl.ejob%TYPE DEFAULT programmer, egrade empl.egrade%TYPE DEF

11、AULT 1, major empl.mgr%TYPE, indate empl.indate%TYPE DEFAULT SYSDATE, salary empl.salary%TYPE DEFAULT 1000, allow empl.allow%TYPE DEFAULT 0, deptno empl.deptno%TYPE)ISBEGIN INSERT INTO emp VALUES(empno,ebirthday,ename,ejob,egrade major, indate, salary,allow, deptno);END;/PL/SQL過程-運(yùn)用異常處置CREATE OR REP

12、LACE PROCEDURE update_sal( name empl.ename%TYPE, sal empl.salary%TYPE) IS e_no_rows EXCEPTION;BEGIN UPDATE empl SET salary=sal WHERE LOWER(ename)=LOWER(name); IF SQL%NOTFOUND THEN RAISE e_no_rows; END IF;EXCEPTION WHEN e_no_rows THEN DBMS_OUTPUT.PUT_LINE(這雇員不存在);END;Exec update_sal(jarbus,1500) 這雇員不

13、存在Exec update_sal(allen,1500)PL/SQL過程-維護(hù)過程刪除過程 DROP PROCEDURE 顯示編譯錯(cuò)誤，查詢數(shù)據(jù)字典USER_ERRORS，或者執(zhí)行SHOW ERRORS命令。確定過程形狀, 查詢數(shù)據(jù)字典USER_OBJECTS。編譯過程，ALTER PROCEDURE COMPILE;查看過程 代碼，經(jīng)過查詢數(shù)據(jù)字典USER_SOURCE。導(dǎo)航PL/SQL過程PL/SQL函數(shù)PL/SQL包函數(shù)函數(shù)是一種命名存儲(chǔ)塊，可以接納輸入并進(jìn)展定義的邏輯處置以后把結(jié)果前往給調(diào)用者。與過程類似，只是函數(shù)必需有前往值。函數(shù)-創(chuàng)建根本語(yǔ)法CREATE OR REPLACE F

14、UNCTION function_name (parameterIN|IN OUT date_type1,parameterIN|IN OUT date_type2,RETURN return_type;IS|AS declaration sectionBEGIN executable statementsEXCEPTION exception handlers;ENDfunction_name; 函數(shù)-例子 以下是求從1到某個(gè)數(shù)累加的例子CREATE OR REPLACE FUNCTION sum(n IN number) RETURN numberAS result number:=0;B

15、EGIN FOR i in 1n LOOP result:=result+i; END LOOP; return(result);END sum;函數(shù)-調(diào)用調(diào)用函數(shù)的時(shí)候需求保管前往值例子DECLARE num1 number:=10; sum1 number:=0;BEGIN sum1:=sum(num1); DBMS_OUTPUT.PUT_LINE(“result=“|sum1);導(dǎo)航PL/SQL過程PL/SQL函數(shù)PL/SQL包PL/SQL包包用于邏輯組合相關(guān)的自定義類型、常量、變量、游標(biāo)、過程和函數(shù)。簡(jiǎn)化了客戶運(yùn)用開發(fā)，提高性能，實(shí)現(xiàn)信息隱藏、子程序重載等功能。PL/SQL包-規(guī)范包規(guī)

16、范用于定義包的公用組件，包括常量、變量、游標(biāo)、自定義類型、過程和函數(shù)等。建立包規(guī)范語(yǔ)法CREATE OR REPLACE PACKAGE package_nameIS|AS - 定義公用常量、變量等 -定義公用過程和函數(shù)等。END package_name;PL/SQL包-例子CREATE OR REPLACE PACKAGE emp_package ISg_deptno empl.deptno%TYPE:=30;PROCEDURE add_employee(empno empl.empno%TYPE, ebirthday empl.ebirthday%TYPE, ename empl.ena

17、me%TYPE, ejob empl.ejob%TYPE DEFAULT programmer, egrade empl.egrade%TYPE,major empl.mgr%TYPE, indate empl.indate%TYPE DEFAULT SYSDATE, salary empl.salary%TYPE DEFAULT 1000, allow empl.allow%TYPE, deptno empl.deptno%TYPE DEFAULT g_deptno);PROCEDURE fire_employee(eno NUMBER);FUNCTION get_sal(eno NUMBE

18、R) RETURN NUMBER;END emp_package;PL/SQL包-建立包體包體用于實(shí)現(xiàn)包規(guī)范所定義的公用過程和函數(shù)，包體不僅可用與實(shí)現(xiàn)公用過程和函數(shù)，而且可以定義包的私有組件變量、常量等建立包體語(yǔ)法CREATE OR REPLACE PACKAGE BODY package_name IS|AS -定義私有變量、常量等 -實(shí)現(xiàn)公用過程和函數(shù)END package_name;PL/SQL包-例子CREATE OR REPLACE PACKAGE BODY emp_package ISFUNCTION validate_deptno(dno empl.deptno%TYPE) RE

19、TURN BOOLEANIS temp INT;BEGIN SELECT 1 INTO temp FROM dept WHERE deptno=dno; RETURN TRUE;EXCEPTION WHEN NO_DATA_FOUND THEN RETURN FALSE; END;PROZCEDURE add_employee(empno empl.empno%TYPE, ebirthday empl.ebirthday%TYPE, ename empl.ename%TYPE, ejob empl.ejob%TYPE DEFAULT programmer, egrade empl.egrade%TYPE,major empl.mgr%TYPE, indate empl.indate%TYPE DEFAULT SYSDATE, salary empl.salary%TYPE DEFAULT 1000, allow empl.allow%TYPE, deptno empl.deptno%TYPE DEFAULT g_deptno);ISBEGIN IF validate_deptno(deptno) THEN