《Oracle大型數(shù)據(jù)庫基礎(chǔ)開發(fā)教程》（微課視頻版）課件 第9章 存儲過程與函數(shù)

第9章存儲過程與函數(shù)9.1存儲過程和函數(shù)概述存儲過程和函數(shù)也是一種PL/SQL塊，是存入數(shù)據(jù)庫的PL/SQL塊。但其不同于已經(jīng)介紹過的PL/SQL程序，通常把PL/SQL程序稱為未命名塊，而存儲過程和函數(shù)是以命名的方式存儲于數(shù)據(jù)庫中的。和PL/SQL程序未命名塊相比，存儲過程和函數(shù)等命名塊有很多優(yōu)點(diǎn)，主要有幾下幾點(diǎn)：（1）存儲過程和函數(shù)以命名的數(shù)據(jù)庫對象形式存儲于數(shù)據(jù)庫當(dāng)中。存儲在數(shù)據(jù)庫中的優(yōu)點(diǎn)是很明顯的，因?yàn)榇a不保存在本地，用戶可以在任何客戶機(jī)上登錄到數(shù)據(jù)庫，并調(diào)用或修改代碼。（2）存儲過程和函數(shù)可由數(shù)據(jù)庫提供安全保證，要想使用存儲過程和函數(shù)，需要由存儲過程和函數(shù)的所有者的授權(quán)，只有被授權(quán)的用戶或創(chuàng)建者本身才能執(zhí)行存儲過程或調(diào)用函數(shù)。（3）存儲過程和函數(shù)的信息是寫入數(shù)據(jù)字典的，所以存儲過程可以看作是一個公用模塊，用戶編寫的PL/SQL程序或其它存儲過程都可以調(diào)用它(但存儲過程和函數(shù)不能調(diào)用PL/SQL程序)。一個重復(fù)使用的功能，可以設(shè)計成為存儲過程，比如：顯示一張工資統(tǒng)計表，可以設(shè)計成為存儲過程；一個經(jīng)常調(diào)用的計算，可以設(shè)計成為存儲函數(shù)；根據(jù)雇員編號返回雇員的姓名，可以設(shè)計成存儲函數(shù)。（4）像其他高級語言的過程和函數(shù)一樣，可以傳遞參數(shù)給存儲過程或函數(shù)，參數(shù)的傳遞也有多種方式。存儲過程和函數(shù)的區(qū)別是存儲過程沒有返回值，而函數(shù)有且僅有一個返回值。9.2存儲過程1創(chuàng)建存儲過程用戶創(chuàng)建存儲過程，需要有CREATEPROCEDURE或CREATEANYPROCEDURE的系統(tǒng)權(quán)限。該權(quán)限通常由系統(tǒng)管理員授予。創(chuàng)建存儲過程的語法如下：Create[orreplace]procedureproc_name [(參數(shù)名[in|out|inout]type[,…..])]Is|as聲明部分;begin執(zhí)行部分;EXCEPTION異常處理部分;endproc_name;9.2存儲過程1創(chuàng)建存儲過程用戶創(chuàng)建存儲過程，需要有CREATEPROCEDURE或CREATEANYPROCEDURE的系統(tǒng)權(quán)限。該權(quán)限通常由系統(tǒng)管理員授予。創(chuàng)建存儲過程的語法如下：Create[orreplace]procedureproc_name [(參數(shù)名[in|out|inout]type[,…..])]Is|as聲明部分;begin執(zhí)行部分;EXCEPTION異常處理部分;endproc_name;9.2存儲過程1創(chuàng)建存儲過程例1：寫一個存儲過程，顯示雇員表EMP雇員人數(shù)。CREATEORREPLACEPROCEDUREPROC_COUNTASV_TOTALNUMBER(3);--聲明一個變量，用于存儲雇員人數(shù)

BEGINSELECTCOUNT(*)INTOV_TOTALFROMEMP;--把人數(shù)存入變量DBMS_OUTPUT.PUT_LINE('雇員總?cè)藬?shù)為：'||V_TOTAL);--輸出人數(shù)信息END;9.2存儲過程2調(diào)用存儲過程創(chuàng)建過程后，該過程就存儲在數(shù)據(jù)庫中，用戶可以調(diào)用執(zhí)行。存儲過程的執(zhí)行有兩種方式：一是使用EXECUTE執(zhí)行過程，另一種是在其它塊（未命名塊或者命名塊）中調(diào)用執(zhí)行。執(zhí)行(或調(diào)用)存儲過程的用戶可以是過程的創(chuàng)建者或是擁有EXECUTEANYPROCEDURE系統(tǒng)權(quán)限的可以或是被擁有者授予EXECUTE權(quán)限的用戶。EXECUTE執(zhí)行存儲過程使用EXECUTE執(zhí)行存儲過程的語法為：EXECUTEPROC_NAME[(參數(shù)值,,,)];例:執(zhí)行PROC_COUNT過程;SQL>SETSERVEROUTPUTONSQL>EXECUTEPROC_COUNT;9.2存儲過程塊中調(diào)用執(zhí)行存儲過程可以在塊中調(diào)用已創(chuàng)建的存儲過程。例2：寫一個存儲過程PROC_INFO，查詢雇員的表所有員工的工號、姓名和參加工作時間，并調(diào)用PROC_NAME存儲過程顯示員工總?cè)藬?shù)。---創(chuàng)建PROC_INFO存儲過程，在存儲過程內(nèi)部調(diào)用了已創(chuàng)建的存儲過程---CReaTEORREPLACEPROCEDUREPROC_INFOISCURSORCUR1ISSELECTEMPNO,ENAME,HireDATEFROMEMP;BEGINFORREC_CUR1INCUR1LOOPDBMS_OUTPUT.PUT_LINE('員工工號：'||rec_cur1.empno||''||'姓名：'||rec_cur1.ename||''||'參加工作時間：'||rec_cur1.hiredate);endloop;Proc_count;--注意存儲過程的調(diào)用方式，不能作為其它表達(dá)式的一部分end;9.2存儲過程帶有參數(shù)的存儲過程PL/SQL中可以定義帶有參數(shù)的存儲過程，通過參數(shù)向存儲過程傳遞數(shù)據(jù)或從存儲過程內(nèi)部向外傳出值。正確的使用參數(shù)可以大大增加存儲過程的靈活性和通用性。存儲過程參數(shù)的類型有三種，分別為IN、OUT和INOUT參數(shù)，默認(rèn)是IN參數(shù)，具體說明如表9-1所示。9.2存儲過程例3：帶有輸入?yún)?shù)的存儲過程舉例：寫一個存儲過程PROC_ENAME，輸出特定員工的姓名。createorreplaceprocedureproc_Ename(v_empnoinemp.empno%type)isv_enameemp.ename%type;beginselectenameintov_enamefromempwhereempno=v_empno;dbms_output.put_line(v_empno||'的姓名：'||v_ename);exceptionwhenno_data_foundthendbms_output.put_line(v_empno||'不存在');end;9.2存儲過程例4：帶有輸出參數(shù)的存儲過程舉例：寫一個存儲過程PROC_OUT1，利用輸出參數(shù)輸出特定員工的姓名。createorreplaceprocedureproc_OUT1(v_empnoinemp.empno%type,v_enameoutemp.ename%type)isbeginselectenameintov_enamefromempwhereempno=v_empno;exceptionwhenno_data_foundthendbms_output.put_line(v_empno||'不存在');end;9.2存儲過程9.3函數(shù)函數(shù)用于計算和返回特定的數(shù)據(jù)，可以將經(jīng)常需要進(jìn)行的計算寫成函數(shù)，函數(shù)的調(diào)用是表達(dá)式的一部分，而過程的調(diào)用是一條PL/SQL語句。函數(shù)也是存儲過程的一種，它與存儲過程的區(qū)別在于：函數(shù)必須向調(diào)用者返回一個執(zhí)行結(jié)果，而存儲過程沒有返回值。1創(chuàng)建函數(shù)創(chuàng)建函數(shù)的語法如下：create[orreplace]functionfun_name([參數(shù)名[in|out|inout)type[,….]])return函數(shù)返回值類型Is|as

聲明部分;begin執(zhí)行部分;Return表達(dá)式;endfun_name;9.3函數(shù)例5：編寫一個PL/SQL函數(shù)fun_count，返回dept表的部門個數(shù)。createorreplacefunctionfun_countreturnnumber--此時不能指定寬度asv_countnumber(3);beginselectcount(*)intov_countfromdept;return(v_count);end;9.3函數(shù)調(diào)用函數(shù)函數(shù)創(chuàng)建后，就可以對其進(jìn)行調(diào)用了。與存儲過程不同，函數(shù)調(diào)用時必須作為表達(dá)式的一部分，函數(shù)調(diào)用方法與存儲過程相比，函數(shù)還可以出現(xiàn)在SELECT語句中。下面以FUN_ename函數(shù)的調(diào)用為例，介紹函數(shù)的調(diào)用方法。1.在select語句中調(diào)用函數(shù)Selectfun_ename(7788)fromdual;--函數(shù)作為查詢表達(dá)式的一部分。2.在塊中調(diào)用函數(shù)BeginDbms_output.put_line(fun_ename(7788));--函數(shù)作為輸出表達(dá)式的一部分。End;3.使用execute調(diào)用函數(shù)由于函數(shù)有一個返回值，在調(diào)用時必須作為表達(dá)式的一部分，當(dāng)使用execute調(diào)用函數(shù)時，同樣需要使用結(jié)合變量，把函數(shù)的返回值存儲到結(jié)合變量中。Variablev_enamevarchar2(20);Execute:v_ename:=fun_ename(7788);--函數(shù)作為賦值表達(dá)式的一部分。Print:v_ename;9.4程序包程序包的概念和組成程序包(PACKAGE，簡稱包)是一組相關(guān)過程、函數(shù)、變量、常量和游標(biāo)等PL/SQL程序設(shè)計元素的組合，作為一個完整的單元存儲在數(shù)據(jù)庫中，用名稱來標(biāo)識包。它具有面向?qū)ο蟪绦蛟O(shè)計語言的特點(diǎn)，是對這些PL/SQL

程序設(shè)計元素的封裝。包類似于c#和JAVA語言中的類，其中變量相當(dāng)于類中的成員變量，過程和函數(shù)相當(dāng)于類方法。把相關(guān)的模塊歸類成為包，可使開發(fā)人員利用面向?qū)ο蟮姆椒ㄟM(jìn)行存儲過程的開發(fā)，從而提高系統(tǒng)性能。與高級語言中的類相同，包中的程序元素也分為公用元素和私用元素兩種，這兩種元素的區(qū)別是他們允許訪問的程序范圍不同，即它們的作用域不同。公用元素不僅可以被包中的函數(shù)、過程所調(diào)用，也可以被包外的PL/SQL程序訪問，而私有元素只能被包內(nèi)的函數(shù)和過程序所訪問。9.4程序包程序包的創(chuàng)建1.程序包說明程序包說明用于聲明包的公用組件，如變量、常量、自定義數(shù)據(jù)類型、異常、過程、函數(shù)、游標(biāo)等。包說明中定義的公有組件不僅可以在包內(nèi)使用，還可以由包外其他過程、函數(shù)。包說明語法如下：CREATE[ORREPLACE]PACKAGEpackage_nameIS|AS[PRAGMASERIALLY_REUSABLE;]

公有數(shù)據(jù)類型定義

公有變量聲明

公有常量聲明

公有異常錯誤聲明

公有游標(biāo)，函數(shù)，存儲過程聲明ENDpackage_name;9.4程序包程序包的創(chuàng)建2.程序包體包體是包的具體實(shí)現(xiàn)細(xì)節(jié)，其實(shí)現(xiàn)在包說明中聲明的所有公有過程、函數(shù)、游標(biāo)等。當(dāng)然也可以在包體中聲明僅屬于自己的私有過程、