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

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

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

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

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

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

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

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

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

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

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

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

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

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

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