第14章存儲過程前面章節中講解了PL/SQL有關的內容。事實上，PL/SQL主要包括匿名塊、存儲過程和存儲函數三種形式的PL/SQL塊。存儲過程是一段存儲在數據庫中執行某功能的程序。使用存儲過程可以方便用戶使用數據庫中的應用程序。本章重點介紹如何創建存儲過程以及如何在PL/SQL塊中使用過程訪問數據庫中的數據。第14章存儲過程前面章節中講解了PL/SQL有關的內容。114.1存儲過程簡介在PL/SQL塊中常把一些功能相對獨立、需要經常執行的代碼定義為一個子程序，在需要時可以根據子程序的名字進行調用，從而簡化操作。這樣不僅便于程序設計，而且有利于程序的調試。與其他PL/SQL塊一樣，在子程序中也可以定義變量、類型、游標等，同樣可以進行異常處理操作。需要注意的是有時必須向子程序提供參數（向子程序傳遞參數）。PL/SQL的子程序有兩種形式，即過程和函數。過程與函數的區別在于函數有返回值，可以向調用者返回執行結果，而過程沒有返回值。14.1存儲過程簡介在PL/SQL塊中常把一些功能相對214.1.1認識存儲過程在Oracle數據庫中，可以將一些固定的操作集中起來由Oracle數據庫服務器來完成，以實現某個任務，這就是存儲過程。存儲過程是Oracle開發者在數據轉換或查詢報表時經常使用的方式之一。一旦在數據庫管理系統中創建了存儲過程對象，用戶就可以使用應用程序，通過簡單命令執行存儲過程。存儲過程在第一次執行時，進行語法檢查和編譯，執行后，它的執行計劃就駐留在高速緩存中，用于后續調用。存儲過程可以接收和輸出參數，返回執行存儲過程的狀態值，還可以嵌套調用。詳細內容在下面講解。14.1.1認識存儲過程在Oracle數據庫中，可以將一314.1.2存儲過程的作用存儲過程的編寫相對復雜，但是很多人都在使用它。這是因為它有著一系列的優點。簡化復雜操作。增加數據獨立性。提高安全性。實現表字段完整性。14.1.2存儲過程的作用存儲過程的編寫相對復雜，但是很414.2

創建存儲過程和執行存儲過程更傾向于數據庫操作。存儲過程的創建有固定的模式。我們可以使用語句的方法創建，也可以使用PL/SQL工具創建，本節主要學習使用語句的方法創建執行存儲過程。14.2創建存儲過程和執行存儲過程更傾向于數據庫操作。514.2.1創建存儲過程在PL/SQL語句中，可以使用createorreplaceprocedure命令創建用戶自定義存儲過程。存儲過程包括無參數的存儲過程以及各類有參數的存儲過程。創建無參數的存儲過程語法結構如圖所示。14.2.1創建存儲過程在PL/SQL語句中，可以使用c614.2.1創建存儲過程1.創建無參數存儲過程無參數的存儲過程就是在創建的存儲過程中不帶任何參數，通過這種存儲過程用做數據轉換的幾率比較大?！臼纠?4-1】創建一個簡單的存儲過程，輸出信息“人生追求情趣，年輕追求個性?！?。【示例14-2】在數據表customersnew中，存儲了顧客信息?！臼纠?4-3】創建一個無參數存儲過程，用于修改表customersnew中列credit_limit的數據，為status是“gold”的credit_limit增加10000。14.2.1創建存儲過程1.創建無參數存儲過程714.2.1創建存儲過程2.查看存儲過程信息存儲過程一旦創建，就存儲在數據庫服務器上，Oracle允許開發人員查看已經存在的存儲過程腳本，這可以通過數據字典（視圖）user_objects和user_source進行查看。當從視圖中查詢過程時，需要把名稱大寫。14.2.1創建存儲過程2.查看存儲過程信息814.2.1創建存儲過程（1）通過user_objects查看存儲過程?！臼纠?4-4】前面創建了創建一個無參數存儲過程pro_print，現在通過數據字典user_objects查看存儲過程信息。【示例14-5】利用user_source查看存儲過程pro_print的詳細信息。14.2.1創建存儲過程（1）通過user_object914.2.1創建存儲過程3.顯示存儲過程的錯誤編寫存儲過程時由于各種原因都有可能出現錯誤而導致過程編譯失敗，這種情況下，可以利用視圖user_errors查看具體的錯誤提示。【示例14-6】創建一個簡單的帶有錯誤的存儲過程，輸出信息“人生追求情趣，年輕追求個性。”?！臼纠?4-7】利用user_errors查看存儲過程的具體的錯誤提示。14.2.1創建存儲過程3.顯示存儲過程的錯誤1014.2.1創建存儲過程4.執行存儲過程存儲過程一旦創建，便可以被其他用戶調用。執行存儲過程非常簡單。當處于PL/SQL代碼塊中時，可以直接利用存儲過程名稱來調用和執行存儲過程如下所示?！臼纠?4-8】調用和執行存儲過程pro_print。【示例14-9】使用execute命令調用存儲過程pro_credit。14.2.1創建存儲過程4.執行存儲過程1114.2.2有參數的存儲過程無參數的存儲過程不會接受參數的傳入和傳出，是針對表或者視圖的查詢或者刪除操作，適合進行數據的轉換操作。但是存儲過程可以帶參數，實現特定的功能。參數的使用將增加存儲過程的靈活性，給數據庫編程帶來極大的方便。14.2.2有參數的存儲過程無參數的存儲過程不會接受參數1214.2.2有參數的存儲過程存儲過程的參數有三種：in（輸入類型）參數、out（輸出類型）參數和inout（輸入輸出類型）參數。在過程中可以定義參數，在調用該過程時，可以向過程傳遞實際參數。如果沒有參數，則過程名后面的圓括號及參數列表可以省略。參數的定義形式如圖所示。14.2.2有參數的存儲過程存儲過程的參數有三種：in（1314.2.2有參數的存儲過程三種參數傳遞模式的比較如表所示。參數默認值的作用是在調用過程時，如果沒有提供實際參數，則將此默認值作為實際參數傳遞給過程。數據類型用來指定參數的類型，在參數定義中不能指定對參數的約束條件，即不能指定參數的長度和是否為空等屬性。

inoutinout是否默認默認必須明確指定必須明確指定參數傳遞方向從調用者到過程從過程到調用者兩個方向形式參數的作用一個常量沒有初始化的變量經過初始化的變量實際參數的形式常量、表達式、變量必須是一個變量必須是一個變量14.2.2有參數的存儲過程三種參數傳遞模式的比較如表所1414.2.3

in參數in參數，是輸入類型的參數，表示這個參數輸入給過程，供過程使用。本小節將講述in參數的使用。前面我們講解到使用存儲過程可以檢查數據的完整性。下面就通過示例加深理解。14.2.3in參數in參數，是輸入類型的參數，表示這個1514.2.3

in參數1.創建包含in參數的存儲過程【示例14-10】對于表customersnew的插入操作，我們希望對提供的信息，account_mgr_id做判斷，如果符合要求，則可以執行插入操作，否則，禁止添加顧客信息。14.2.3in參數1.創建包含in參數的存儲過程1614.2.3

in參數2.在存儲過程中為in參數賦值如果在存儲過程內部，嘗試修改in參數，那么將引起Oracle編譯錯誤?！臼纠?4-11】對于已經創建的存儲過程insert_cus，在存儲過程內部，嘗試將輸入參數mgr_id賦值為145。14.2.3in參數2.在存儲過程中為in參數賦值1714.2.3

in參數3.重建帶輸入參數的存儲過程【示例14-12】前面我們創建了一個無參數存儲過程pro_credit，用于修改表customersnew中列credit_limit的數據，為status是“gold”的credit_limit增加10000。現在將該存儲過程修改為帶輸入參數的存儲過程，可以指定顧客編號對credit_limit進行相應處理。4.執行存儲過程【示例14-13】執行存儲過程pro_credit，將所有顧客的credit_limit增加10000。【示例14-14】在上述例子中，將所有顧客的credit_limit增加10000，現在將顧客編號為101的顧客增加10000。14.2.3in參數3.重建帶輸入參數的存儲過程1814.2.4

out參數out參數是輸出類型的參數，表示這個參數在存儲過程中被復制，可以傳給過程提以外的部分。與in參數相反，out參數是只出不進的參數。14.2.4out參數out參數是輸出類型的參數，表示這1914.2.4

out參數1.創建一個帶out參數的存儲過程，并執行【示例14-15】對于前面示例中的存儲過程insert_cus，在調用插入數據之后，我們通過查詢表中數據才能驗證是否執行成功。如果使用out參數，更方便?！臼纠?4-16】執行存儲過程out_cus。14.2.4out參數1.創建一個帶out參數的存儲過程2014.2.4

out參數2.創建包含in參數、out參數的存儲過程【示例14-17】創建存儲過程total_order，計算orders數據表中所有的訂單總和，參數設置時，我們設置一個in參數，一個out參數?！臼纠?4-18】執行存儲過程total_order。14.2.4out參數2.創建包含in參數、out參數的2114.2.5

inout參數inout參數綜合了上述兩種參數類型，既向過程體傳值，在過程中，也被賦值而傳到過程體外。inout參數既可以作為輸入也可以作為輸出?！臼纠?4-19】結合使用inout參數的，實現交換兩個變量的值?！臼纠?4-20】執行存儲過程exch，實現交換兩個變量的值。14.2.5inout參數inout參數綜合了上述兩2214.3

SQL

Developer工具創建存儲過程使用SQLDeveloper創建存儲過程是比較簡單的。該工具提供一個相對便利的操作環境，尤其對于存儲過程的調試和錯誤的查找都比語句創建存儲過程要方便。本節介紹在SQLDeveloper中創建和調試存儲過程。14.3SQLDeveloper工具創建存儲過程使用S2314.3.1創建存儲過程SQLDeveloper提供了創建存儲過程的模版，該模版允許輸入存儲過程名和參數，然后自動創建腳本。該腳本是一個簡單框架，細節則需要用戶自己開發?！臼纠?4-21】利用SQLDeveloper工具創建存儲過程exch1，實現交換兩個變量的值。14.3.1創建存儲過程SQLDeveloper提供了2414.3.2調試存儲過程存儲過程創建之后，調試存儲過程是很有必要的，它和其他語言的調試功能類似，可以設置斷點，可以分步執行。本小節將簡要介紹如何在PL/SQLDeveloper中調試修改存儲過程?！臼纠?4-22】在SQLDeveloper中調試存儲過程。14.3.2調試存儲過程存儲過程創建之后，調試存儲過程是2514.4管理存儲過程存儲過程創建之后，可以對其進行調用。但是在后期使用中，如果有不合適的地方，需要對存儲過程進行修改和刪除。本小節主要講解圖和修改存儲過程、重新編譯存儲過程以及刪除存儲過程。14.4管理存儲過程存儲過程創建之后，可以對其進行調用。2614.4.1修改存儲過程當存儲過程中出現錯誤時，可以修改存儲過程。修改存儲過程可以人為地把原來的存儲過程刪除，然后建立新的存儲過程，也可以使用Oracle提供的語法直接完成修改。14.4.1修改存儲過程當存儲過程中出現錯誤時，可以修改2714.4.1修改存儲過程修改存儲過程使用replace關鍵字，即覆蓋。我們一般在創建存儲過程時，就將該關鍵字寫上?！臼纠?4-23】創建存儲過程，完成一個數的加倍?！臼纠?4-24】修改存儲過程double1，將存儲過程的參數設置為inout參數類型，并修改賦值方式。14.4.1修改存儲過程修改存儲過程使用replace關2814.4.2重新編譯存儲過程存儲過程在某些情況下是需要重新編譯的，重新編譯可以獲得存儲過程的最新狀態。重新編譯存儲過程的語法結構如圖所示?！臼纠?4-25】重新編譯存儲過程double1，完成一個數的加倍。【示例14-26】執行存儲過程double1。14.4.2重新編譯存儲過程存儲過程在某些情況下是需要重2914.4.3刪除存儲過程不再需要存儲過程時，可以將其刪除。利用語句刪除存儲過程的語法結構如圖所示?！臼纠?4-27】刪除存儲過程double1。14.4.3刪除存儲過程不再需要存儲過程時，可以將其刪除3014.4.3刪除存儲過程除了使用語句方法外，還可以使用工具刪除存儲過程。這種方式是在PL/SQLDeveloper中選中要刪除的存儲過程，右擊該過程，在彈出的快捷菜單中選擇“刪除”選項，此時會出現確認刪除窗口，單擊“是”按鈕即可。14.4.3刪除存儲過程除了使用語句方法外，還可以使用工3114.5小結存儲過程的使用十分普遍，是Oracle的重點知識。本章不僅介紹了存儲過程的概念和作用，而且詳細講解了如何讓創建和操作各種類型的存儲過程。本章的重點是如何根據需求創建合適的操作過程，難點是對存儲過程參數的設置，以及如何執行存儲過程。下一章將學習函數和程序包。14.5小結存儲過程的使用十分普遍，是Oracle的重點32第14章存儲過程前面章節中講解了PL/SQL有關的內容。事實上，PL/SQL主要包括匿名塊、存儲過程和存儲函數三種形式的PL/SQL塊。存儲過程是一段存儲在數據庫中執行某功能的程序。使用存儲過程可以方便用戶使用數據庫中的應用程序。本章重點介紹如何創建存儲過程以及如何在PL/SQL塊中使用過程訪問數據庫中的數據。第14章存儲過程前面章節中講解了PL/SQL有關的內容。3314.1存儲過程簡介在PL/SQL塊中常把一些功能相對獨立、需要經常執行的代碼定義為一個子程序，在需要時可以根據子程序的名字進行調用，從而簡化操作。這樣不僅便于程序設計，而且有利于程序的調試。與其他PL/SQL塊一樣，在子程序中也可以定義變量、類型、游標等，同樣可以進行異常處理操作。需要注意的是有時必須向子程序提供參數（向子程序傳遞參數）。PL/SQL的子程序有兩種形式，即過程和函數。過程與函數的區別在于函數有返回值，可以向調用者返回執行結果，而過程沒有返回值。14.1存儲過程簡介在PL/SQL塊中常把一些功能相對3414.1.1認識存儲過程在Oracle數據庫中，可以將一些固定的操作集中起來由Oracle數據庫服務器來完成，以實現某個任務，這就是存儲過程。存儲過程是Oracle開發者在數據轉換或查詢報表時經常使用的方式之一。一旦在數據庫管理系統中創建了存儲過程對象，用戶就可以使用應用程序，通過簡單命令執行存儲過程。存儲過程在第一次執行時，進行語法檢查和編譯，執行后，它的執行計劃就駐留在高速緩存中，用于后續調用。存儲過程可以接收和輸出參數，返回執行存儲過程的狀態值，還可以嵌套調用。詳細內容在下面講解。14.1.1認識存儲過程在Oracle數據庫中，可以將一3514.1.2存儲過程的作用存儲過程的編寫相對復雜，但是很多人都在使用它。這是因為它有著一系列的優點。簡化復雜操作。增加數據獨立性。提高安全性。實現表字段完整性。14.1.2存儲過程的作用存儲過程的編寫相對復雜，但是很3614.2

創建存儲過程和執行存儲過程更傾向于數據庫操作。存儲過程的創建有固定的模式。我們可以使用語句的方法創建，也可以使用PL/SQL工具創建，本節主要學習使用語句的方法創建執行存儲過程。14.2創建存儲過程和執行存儲過程更傾向于數據庫操作。3714.2.1創建存儲過程在PL/SQL語句中，可以使用createorreplaceprocedure命令創建用戶自定義存儲過程。存儲過程包括無參數的存儲過程以及各類有參數的存儲過程。創建無參數的存儲過程語法結構如圖所示。14.2.1創建存儲過程在PL/SQL語句中，可以使用c3814.2.1創建存儲過程1.創建無參數存儲過程無參數的存儲過程就是在創建的存儲過程中不帶任何參數，通過這種存儲過程用做數據轉換的幾率比較大?！臼纠?4-1】創建一個簡單的存儲過程，輸出信息“人生追求情趣，年輕追求個性?！??！臼纠?4-2】在數據表customersnew中，存儲了顧客信息?！臼纠?4-3】創建一個無參數存儲過程，用于修改表customersnew中列credit_limit的數據，為status是“gold”的credit_limit增加10000。14.2.1創建存儲過程1.創建無參數存儲過程3914.2.1創建存儲過程2.查看存儲過程信息存儲過程一旦創建，就存儲在數據庫服務器上，Oracle允許開發人員查看已經存在的存儲過程腳本，這可以通過數據字典（視圖）user_objects和user_source進行查看。當從視圖中查詢過程時，需要把名稱大寫。14.2.1創建存儲過程2.查看存儲過程信息4014.2.1創建存儲過程（1）通過user_objects查看存儲過程?！臼纠?4-4】前面創建了創建一個無參數存儲過程pro_print，現在通過數據字典user_objects查看存儲過程信息?！臼纠?4-5】利用user_source查看存儲過程pro_print的詳細信息。14.2.1創建存儲過程（1）通過user_object4114.2.1創建存儲過程3.顯示存儲過程的錯誤編寫存儲過程時由于各種原因都有可能出現錯誤而導致過程編譯失敗，這種情況下，可以利用視圖user_errors查看具體的錯誤提示。【示例14-6】創建一個簡單的帶有錯誤的存儲過程，輸出信息“人生追求情趣，年輕追求個性?！?。【示例14-7】利用user_errors查看存儲過程的具體的錯誤提示。14.2.1創建存儲過程3.顯示存儲過程的錯誤4214.2.1創建存儲過程4.執行存儲過程存儲過程一旦創建，便可以被其他用戶調用。執行存儲過程非常簡單。當處于PL/SQL代碼塊中時，可以直接利用存儲過程名稱來調用和執行存儲過程如下所示?！臼纠?4-8】調用和執行存儲過程pro_print?！臼纠?4-9】使用execute命令調用存儲過程pro_credit。14.2.1創建存儲過程4.執行存儲過程4314.2.2有參數的存儲過程無參數的存儲過程不會接受參數的傳入和傳出，是針對表或者視圖的查詢或者刪除操作，適合進行數據的轉換操作。但是存儲過程可以帶參數，實現特定的功能。參數的使用將增加存儲過程的靈活性，給數據庫編程帶來極大的方便。14.2.2有參數的存儲過程無參數的存儲過程不會接受參數4414.2.2有參數的存儲過程存儲過程的參數有三種：in（輸入類型）參數、out（輸出類型）參數和inout（輸入輸出類型）參數。在過程中可以定義參數，在調用該過程時，可以向過程傳遞實際參數。如果沒有參數，則過程名后面的圓括號及參數列表可以省略。參數的定義形式如圖所示。14.2.2有參數的存儲過程存儲過程的參數有三種：in（4514.2.2有參數的存儲過程三種參數傳遞模式的比較如表所示。參數默認值的作用是在調用過程時，如果沒有提供實際參數，則將此默認值作為實際參數傳遞給過程。數據類型用來指定參數的類型，在參數定義中不能指定對參數的約束條件，即不能指定參數的長度和是否為空等屬性。

inoutinout是否默認默認必須明確指定必須明確指定參數傳遞方向從調用者到過程從過程到調用者兩個方向形式參數的作用一個常量沒有初始化的變量經過初始化的變量實際參數的形式常量、表達式、變量必須是一個變量必須是一個變量14.2.2有參數的存儲過程三種參數傳遞模式的比較如表所4614.2.3

in參數in參數，是輸入類型的參數，表示這個參數輸入給過程，供過程使用。本小節將講述in參數的使用。前面我們講解到使用存儲過程可以檢查數據的完整性。下面就通過示例加深理解。14.2.3in參數in參數，是輸入類型的參數，表示這個4714.2.3

in參數1.創建包含in參數的存儲過程【示例14-10】對于表customersnew的插入操作，我們希望對提供的信息，account_mgr_id做判斷，如果符合要求，則可以執行插入操作，否則，禁止添加顧客信息。14.2.3in參數1.創建包含in參數的存儲過程4814.2.3

in參數2.在存儲過程中為in參數賦值如果在存儲過程內部，嘗試修改in參數，那么將引起Oracle編譯錯誤。【示例14-11】對于已經創建的存儲過程insert_cus，在存儲過程內部，嘗試將輸入參數mgr_id賦值為145。14.2.3in參數2.在存儲過程中為in參數賦值4914.2.3

in參數3.重建帶輸入參數的存儲過程【示例14-12】前面我們創建了一個無參數存儲過程pro_credit，用于修改表customersnew中列credit_limit的數據，為status是“gold”的credit_limit增加10000?，F在將該存儲過程修改為帶輸入參數的存儲過程，可以指定顧客編號對credit_limit進行相應處理。4.執行存儲過程【示例14-13】執行存儲過程pro_credit，將所有顧客的credit_limit增加10000?！臼纠?4-14】在上述例子中，將所有顧客的credit_limit增加10000，現在將顧客編號為101的顧客增加10000。14.2.3in參數3.重建帶輸入參數的存儲過程5014.2.4

out參數out參數是輸出類型的參數，表示這個參數在存儲過程中被復制，可以傳給過程提以外的部分。與in參數相反，out參數是只出不進的參數。14.2.4out參數out參數是輸出類型的參數，表示這5114.2.4

out參數1.創建一個帶out參數的存儲過程，并執行【示例14-15】對于前面示例中的存儲過程insert_cus，在調用插入數據之后，我們通過查詢表中數據才能驗證是否執行成功。如果使用out參數，更方便。【示例14-16】執行存儲過程out_cus。14.2.4out參數1.創建一個帶out參數的存儲過程5214.2.4

out參數2.創建包含in參數、out參數的存儲過程【示例14-17】創建存儲過程total_order，計算orders數據表中所有的訂單總和，參數設置時，我們設置一個in參數，一個out參數。【示例14-18】執行存儲過程total_order。14.2.4out參數2.創建包含in參數、out參數的5314.2.5

inout參數inout參數綜合了上述兩種參數類型，既向過程體傳值，在過程中，也被賦值而傳到過程體外。inout參數既可以作為輸入也可以作為輸出?！臼纠?4-19】結合使用inout參數的，實現交換兩個變量的值。【示例14-20】執行存儲過程exch，實現交換兩個變量的值。14.2.5inout參數inout參數綜合了上述兩5414.3

SQL

Developer工具創建存儲過程使用SQLDeveloper創建存儲過程是比較簡單的。該工具提供一個相對便利的操作環境，尤其對于存儲過程的調試和錯誤的查找都比語句創建存儲過程要方便。本節介紹在SQLDeveloper中創建和調試存儲過程。14.3SQLDeveloper工具創建存儲過程使用S5514.3.1創建存儲過程SQLDeveloper提供了創建存儲過程的模版，該模版允許輸入存儲過程名和參數，然后自動創建腳本。該腳本是一個簡單框架，細節則需要用戶自己開發?！臼纠?4-21】利用SQLDeveloper工具創建存儲過程exch1，實現交換兩個變量的值。14.3.1創建存儲過程SQLDeveloper提供了5614.3.2調試存儲過程存儲過程創建之后，調試存儲過程是很有必要的