精通Oracle核心技術(shù)和項(xiàng)目實(shí)戰(zhàn)之存儲(chǔ)過(guò)程ppt課件

1、第14章 存儲(chǔ)過(guò)程前面章節(jié)中講解了PL/SQL有關(guān)的內(nèi)容?，F(xiàn)實(shí)上，PL/SQL主要包括匿名塊、存儲(chǔ)過(guò)程和存儲(chǔ)函數(shù)三種方式的PL/SQL塊。存儲(chǔ)過(guò)程是一段存儲(chǔ)在數(shù)據(jù)庫(kù)中執(zhí)行某功能的程序。運(yùn)用存儲(chǔ)過(guò)程可以方便用戶運(yùn)用數(shù)據(jù)庫(kù)中的運(yùn)用程序。本章重點(diǎn)引見(jiàn)如何創(chuàng)建存儲(chǔ)過(guò)程以及如何在PL/SQL塊中運(yùn)用過(guò)程訪問(wèn)數(shù)據(jù)庫(kù)中的數(shù)據(jù)。.14.1 存儲(chǔ)過(guò)程簡(jiǎn)介在PL/SQL塊中常把一些功能相對(duì)獨(dú)立、需求經(jīng)常執(zhí)行的代碼定義為一個(gè)子程序，在需求時(shí)可以根據(jù)子程序的名字進(jìn)展調(diào)用，從而簡(jiǎn)化操作。這樣不僅便于程序設(shè)計(jì)，而且有利于程序的調(diào)試。與其他PL/SQL塊一樣，在子程序中也可以定義變量、類型、游標(biāo)等，同樣可以進(jìn)展異常處置操作

2、。需求留意的是有時(shí)必需向子程序提供參數(shù)向子程序傳送參數(shù)。PL/SQL的子程序有兩種方式，即過(guò)程和函數(shù)。過(guò)程與函數(shù)的區(qū)別在于函數(shù)有前往值，可以向調(diào)用者前往執(zhí)行結(jié)果，而過(guò)程沒(méi)有前往值。.14.1.1 認(rèn)識(shí)存儲(chǔ)過(guò)程在Oracle數(shù)據(jù)庫(kù)中，可以將一些固定的操作集中起來(lái)由Oracle數(shù)據(jù)庫(kù)效力器來(lái)完成，以實(shí)現(xiàn)某個(gè)義務(wù)，這就是存儲(chǔ)過(guò)程。存儲(chǔ)過(guò)程是Oracle開發(fā)者在數(shù)據(jù)轉(zhuǎn)換或查詢報(bào)表時(shí)經(jīng)常運(yùn)用的方式之一。一旦在數(shù)據(jù)庫(kù)管理系統(tǒng)中創(chuàng)建了存儲(chǔ)過(guò)程對(duì)象，用戶就可以運(yùn)用運(yùn)用程序，經(jīng)過(guò)簡(jiǎn)單命令執(zhí)行存儲(chǔ)過(guò)程。存儲(chǔ)過(guò)程在第一次執(zhí)行時(shí)，進(jìn)展語(yǔ)法檢查和編譯，執(zhí)行后，它的執(zhí)行方案就駐留在高速緩存中，用于后續(xù)調(diào)用。存儲(chǔ)過(guò)程可以接

3、納和輸出參數(shù)，前往執(zhí)行存儲(chǔ)過(guò)程的形狀值，還可以嵌套調(diào)用。詳細(xì)內(nèi)容在下面講解。.14.1.2 存儲(chǔ)過(guò)程的作用存儲(chǔ)過(guò)程的編寫相對(duì)復(fù)雜，但是很多人都在運(yùn)用它。這是由于它有著一系列的優(yōu)點(diǎn)。簡(jiǎn)化復(fù)雜操作。添加數(shù)據(jù)獨(dú)立性。提高平安性。實(shí)現(xiàn)表字段完好性。.14.2 創(chuàng)建存儲(chǔ)過(guò)程和執(zhí)行存儲(chǔ)過(guò)程更傾向于數(shù)據(jù)庫(kù)操作。存儲(chǔ)過(guò)程的創(chuàng)建有固定的方式。我們可以運(yùn)用語(yǔ)句的方法創(chuàng)建，也可以運(yùn)用PL/SQL工具創(chuàng)建，本節(jié)主要學(xué)習(xí)運(yùn)用語(yǔ)句的方法創(chuàng)建執(zhí)行存儲(chǔ)過(guò)程。.14.2.1 創(chuàng)建存儲(chǔ)過(guò)程在PL/SQL語(yǔ)句中，可以運(yùn)用create or replace procedure命令創(chuàng)建用戶自定義存儲(chǔ)過(guò)程。存儲(chǔ)過(guò)程包括無(wú)參數(shù)的存儲(chǔ)過(guò)程以

4、及各類有參數(shù)的存儲(chǔ)過(guò)程。創(chuàng)建無(wú)參數(shù)的存儲(chǔ)過(guò)程語(yǔ)法構(gòu)造如下圖。.14.2.1 創(chuàng)建存儲(chǔ)過(guò)程1.創(chuàng)建無(wú)參數(shù)存儲(chǔ)過(guò)程無(wú)參數(shù)的存儲(chǔ)過(guò)程就是在創(chuàng)建的存儲(chǔ)過(guò)程中不帶任何參數(shù)，經(jīng)過(guò)這種存儲(chǔ)過(guò)程用做數(shù)據(jù)轉(zhuǎn)換的幾率比較大?！纠?4-1】創(chuàng)建一個(gè)簡(jiǎn)單的存儲(chǔ)過(guò)程，輸出信息“人生追求情趣，年輕追求個(gè)性?！纠?4-2】在數(shù)據(jù)表customersnew中，存儲(chǔ)了顧客信息。【例如14-3】創(chuàng)建一個(gè)無(wú)參數(shù)存儲(chǔ)過(guò)程，用于修正表customersnew中列credit_limit的數(shù)據(jù)，為status是“gold的credit_limit添加10000。.14.2.1 創(chuàng)建存儲(chǔ)過(guò)程2.查看存儲(chǔ)過(guò)程信息存儲(chǔ)過(guò)程一旦創(chuàng)建，就存儲(chǔ)

5、在數(shù)據(jù)庫(kù)效力器上，Oracle允許開發(fā)人員查看曾經(jīng)存在的存儲(chǔ)過(guò)程腳本，這可以經(jīng)過(guò)數(shù)據(jù)字典視圖user_objects和user_source進(jìn)展查看。當(dāng)從視圖中查詢過(guò)程時(shí)，需求把稱號(hào)大寫。.14.2.1 創(chuàng)建存儲(chǔ)過(guò)程1經(jīng)過(guò)user_objects查看存儲(chǔ)過(guò)程。【例如14-4】前面創(chuàng)建了創(chuàng)建一個(gè)無(wú)參數(shù)存儲(chǔ)過(guò)程pro_print，如今經(jīng)過(guò)數(shù)據(jù)字典user_objects查看存儲(chǔ)過(guò)程信息?！纠?4-5】利用user_source查看存儲(chǔ)過(guò)程pro_print的詳細(xì)信息。.14.2.1 創(chuàng)建存儲(chǔ)過(guò)程3.顯示存儲(chǔ)過(guò)程的錯(cuò)誤編寫存儲(chǔ)過(guò)程時(shí)由于各種緣由都有能夠出現(xiàn)錯(cuò)誤而導(dǎo)致過(guò)程編譯失敗，這種情況下，可以利

6、用視圖user_errors查看詳細(xì)的錯(cuò)誤提示?！纠?4-6】創(chuàng)建一個(gè)簡(jiǎn)單的帶有錯(cuò)誤的存儲(chǔ)過(guò)程，輸出信息“人生追求情趣，年輕追求個(gè)性?！纠?4-7】利用user_errors查看存儲(chǔ)過(guò)程的詳細(xì)的錯(cuò)誤提示。.14.2.1 創(chuàng)建存儲(chǔ)過(guò)程4.執(zhí)行存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程一旦創(chuàng)建，便可以被其他用戶調(diào)用。執(zhí)行存儲(chǔ)過(guò)程非常簡(jiǎn)單。當(dāng)處于PL/SQL代碼塊中時(shí)，可以直接利用存儲(chǔ)過(guò)程稱號(hào)來(lái)調(diào)用和執(zhí)行存儲(chǔ)過(guò)程如下所示?！纠?4-8】調(diào)用和執(zhí)行存儲(chǔ)過(guò)程pro_print?！纠?4-9】運(yùn)用execute命令調(diào)用存儲(chǔ)過(guò)程pro_credit。.14.2.2 有參數(shù)的存儲(chǔ)過(guò)程無(wú)參數(shù)的存儲(chǔ)過(guò)程不會(huì)接受參數(shù)的傳入和傳出，是

7、針對(duì)表或者視圖的查詢或者刪除操作，適宜進(jìn)展數(shù)據(jù)的轉(zhuǎn)換操作。但是存儲(chǔ)過(guò)程可以帶參數(shù)，實(shí)現(xiàn)特定的功能。參數(shù)的運(yùn)用將添加存儲(chǔ)過(guò)程的靈敏性，給數(shù)據(jù)庫(kù)編程帶來(lái)極大的方便。.14.2.2 有參數(shù)的存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程的參數(shù)有三種：in輸入類型參數(shù)、out輸出類型參數(shù)和in out輸入輸出類型參數(shù)。在過(guò)程中可以定義參數(shù)，在調(diào)用該過(guò)程時(shí)，可以向過(guò)程傳送實(shí)踐參數(shù)。假設(shè)沒(méi)有參數(shù)，那么過(guò)程名后面的圓括號(hào)及參數(shù)列表可以省略。參數(shù)的定義方式如下圖。.14.2.2 有參數(shù)的存儲(chǔ)過(guò)程三種參數(shù)傳送方式的比較如表所示。參數(shù)默許值的作用是在調(diào)用過(guò)程時(shí)，假設(shè)沒(méi)有提供實(shí)踐參數(shù)，那么將此默許值作為實(shí)踐參數(shù)傳送給過(guò)程。數(shù)據(jù)類型用來(lái)指定參數(shù)的

8、類型，在參數(shù)定義中不能指定對(duì)參數(shù)的約束條件，即不能指定參數(shù)的長(zhǎng)度和能否為空等屬性。inoutin out是否默認(rèn)默認(rèn)必須明確指定必須明確指定參數(shù)傳遞方向從調(diào)用者到過(guò)程從過(guò)程到調(diào)用者兩個(gè)方向形式參數(shù)的作用一個(gè)常量沒(méi)有初始化的變量經(jīng)過(guò)初始化的變量實(shí)際參數(shù)的形式常量、表達(dá)式、變量必須是一個(gè)變量必須是一個(gè)變量.14.2.3 in參數(shù)in參數(shù)，是輸入類型的參數(shù)，表示這個(gè)參數(shù)輸入給過(guò)程，供過(guò)程運(yùn)用。本小節(jié)將講述in參數(shù)的運(yùn)用。前面我們講解到運(yùn)用存儲(chǔ)過(guò)程可以檢查數(shù)據(jù)的完好性。下面就經(jīng)過(guò)例如加深了解。.14.2.3 in參數(shù)1.創(chuàng)建包含in參數(shù)的存儲(chǔ)過(guò)程【例如14-10】對(duì)于表customersnew的插入操

9、作，我們希望對(duì)提供的信息，account_mgr_id做判別，假設(shè)符合要求，那么可以執(zhí)行插入操作，否那么，制止添加顧客信息。.14.2.3 in參數(shù)2.在存儲(chǔ)過(guò)程中為in參數(shù)賦值假設(shè)在存儲(chǔ)過(guò)程內(nèi)部，嘗試修正in參數(shù)，那么將引起Oracle編譯錯(cuò)誤?！纠?4-11】對(duì)于曾經(jīng)創(chuàng)建的存儲(chǔ)過(guò)程insert_cus，在存儲(chǔ)過(guò)程內(nèi)部，嘗試將輸入?yún)?shù)mgr_id賦值為145。.14.2.3 in參數(shù)3.重建帶輸入?yún)?shù)的存儲(chǔ)過(guò)程【例如14-12】前面我們創(chuàng)建了一個(gè)無(wú)參數(shù)存儲(chǔ)過(guò)程pro_credit，用于修正表customersnew中列credit_limit的數(shù)據(jù)，為status是“gold的credit

10、_limit添加10000。如今將該存儲(chǔ)過(guò)程修正為帶輸入?yún)?shù)的存儲(chǔ)過(guò)程，可以指定顧客編號(hào)對(duì)credit_limit進(jìn)展相應(yīng)處置。4.執(zhí)行存儲(chǔ)過(guò)程【例如14-13】執(zhí)行存儲(chǔ)過(guò)程pro_credit，將一切顧客的credit_limit添加10000?！纠?4-14】在上述例子中，將一切顧客的credit_limit添加10000，如今將顧客編號(hào)為101的顧客添加10000。.14.2.4 out參數(shù)out參數(shù)是輸出類型的參數(shù)，表示這個(gè)參數(shù)在存儲(chǔ)過(guò)程中被復(fù)制，可以傳給過(guò)程提以外的部分。與in參數(shù)相反，out參數(shù)是只出不進(jìn)的參數(shù)。.14.2.4 out參數(shù)1.創(chuàng)建一個(gè)帶out參數(shù)的存儲(chǔ)過(guò)程，并執(zhí)行

11、【例如14-15】對(duì)于前面例如中的存儲(chǔ)過(guò)程insert_cus，在調(diào)用插入數(shù)據(jù)之后，我們經(jīng)過(guò)查詢表中數(shù)據(jù)才干驗(yàn)證能否執(zhí)行勝利。假設(shè)運(yùn)用out參數(shù)，更方便。【例如14-16】執(zhí)行存儲(chǔ)過(guò)程out_cus。.14.2.4 out參數(shù)2.創(chuàng)建包含in參數(shù)、out參數(shù)的存儲(chǔ)過(guò)程【例如14-17】創(chuàng)建存儲(chǔ)過(guò)程total_order，計(jì)算orders數(shù)據(jù)表中一切的訂單總和，參數(shù)設(shè)置時(shí)，我們?cè)O(shè)置一個(gè)in參數(shù)，一個(gè)out參數(shù)。【例如14-18】執(zhí)行存儲(chǔ)過(guò)程total_order。.14.2.5 in out參數(shù)in out參數(shù)綜合了上述兩種參數(shù)類型，既向過(guò)程體傳值，在過(guò)程中，也被賦值而傳到過(guò)程體外。in out

12、參數(shù)既可以作為輸入也可以作為輸出。【例如14-19】結(jié)合運(yùn)用in out參數(shù)的，實(shí)現(xiàn)交換兩個(gè)變量的值?！纠?4-20】執(zhí)行存儲(chǔ)過(guò)程exch，實(shí)現(xiàn)交換兩個(gè)變量的值。.14.3 SQL Developer工具創(chuàng)建存儲(chǔ)過(guò)程運(yùn)用SQL Developer創(chuàng)建存儲(chǔ)過(guò)程是比較簡(jiǎn)單的。該工具提供一個(gè)相對(duì)便利的操作環(huán)境，尤其對(duì)于存儲(chǔ)過(guò)程的調(diào)試和錯(cuò)誤的查找都比語(yǔ)句創(chuàng)建存儲(chǔ)過(guò)程要方便。本節(jié)引見(jiàn)在SQL Developer中創(chuàng)建和調(diào)試存儲(chǔ)過(guò)程。.14.3.1 創(chuàng)建存儲(chǔ)過(guò)程SQL Developer提供了創(chuàng)建存儲(chǔ)過(guò)程的模版，該模版允許輸入存儲(chǔ)過(guò)程名和參數(shù)，然后自動(dòng)創(chuàng)建腳本。該腳本是一個(gè)簡(jiǎn)單框架，細(xì)節(jié)那么需求用戶本人

13、開發(fā)?！纠?4-21】利用SQL Developer工具創(chuàng)建存儲(chǔ)過(guò)程exch1，實(shí)現(xiàn)交換兩個(gè)變量的值。.14.3.2 調(diào)試存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程創(chuàng)建之后，調(diào)試存儲(chǔ)過(guò)程是很有必要的，它和其他言語(yǔ)的調(diào)試功能類似，可以設(shè)置斷點(diǎn)，可以分步執(zhí)行。本小節(jié)將簡(jiǎn)要引見(jiàn)如何在PL/SQL Developer中調(diào)試修正存儲(chǔ)過(guò)程。【例如14-22】在SQL Developer中調(diào)試存儲(chǔ)過(guò)程。.14.4 管理存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程創(chuàng)建之后，可以對(duì)其進(jìn)展調(diào)用。但是在后期運(yùn)用中，假設(shè)有不適宜的地方，需求對(duì)存儲(chǔ)過(guò)程進(jìn)展修正和刪除。本小節(jié)主要講解圖和修正存儲(chǔ)過(guò)程、重新編譯存儲(chǔ)過(guò)程以及刪除存儲(chǔ)過(guò)程。.14.4.1 修正存儲(chǔ)過(guò)程當(dāng)存儲(chǔ)過(guò)程

14、中出現(xiàn)錯(cuò)誤時(shí)，可以修正存儲(chǔ)過(guò)程。修正存儲(chǔ)過(guò)程可以人為地把原來(lái)的存儲(chǔ)過(guò)程刪除，然后建立新的存儲(chǔ)過(guò)程，也可以運(yùn)用Oracle提供的語(yǔ)法直接完成修正。.14.4.1 修正存儲(chǔ)過(guò)程修正存儲(chǔ)過(guò)程運(yùn)用replace關(guān)鍵字，即覆蓋。我們普通在創(chuàng)建存儲(chǔ)過(guò)程時(shí)，就將該關(guān)鍵字寫上?！纠?4-23】創(chuàng)建存儲(chǔ)過(guò)程，完成一個(gè)數(shù)的加倍。【例如14-24】修正存儲(chǔ)過(guò)程double1，將存儲(chǔ)過(guò)程的參數(shù)設(shè)置為in out參數(shù)類型，并修正賦值方式。.14.4.2 重新編譯存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程在某些情況下是需求重新編譯的，重新編譯可以獲得存儲(chǔ)過(guò)程的最新形狀。重新編譯存儲(chǔ)過(guò)程的語(yǔ)法構(gòu)造如下圖?！纠?4-25】重新編譯存儲(chǔ)過(guò)程double1，完成一個(gè)數(shù)的加倍?！纠?4-26】執(zhí)行存儲(chǔ)過(guò)程double1。.14.4.3 刪除存儲(chǔ)過(guò)程不再需求存儲(chǔ)過(guò)程時(shí)，可以將其刪除。利用語(yǔ)句刪除存儲(chǔ)過(guò)程的語(yǔ)法構(gòu)造如下圖。【例如14-27】刪除存儲(chǔ)過(guò)程double1。.14.4.3 刪除存儲(chǔ)過(guò)程除了運(yùn)用語(yǔ)句方法外，還