一、存儲(chǔ)過程簡(jiǎn)介

1.1存儲(chǔ)過程描述

商業(yè)規(guī)則和業(yè)務(wù)邏輯可以通過程序存儲(chǔ)在Oracle中，這個(gè)程序就是存儲(chǔ)過程。

存儲(chǔ)過程是SQL, PL/SQL, Java語句的組合，它使你能將執(zhí)行商業(yè)規(guī)則的代碼從你的應(yīng)用程序中移動(dòng)到數(shù)據(jù)庫。這樣的結(jié)果就是，代碼存儲(chǔ)一次但是能夠被多個(gè)程序使用。

1.2 存儲(chǔ)過程的優(yōu)點(diǎn)

(1)減少網(wǎng)絡(luò)通信量。?調(diào)用一個(gè)行數(shù)不多的存儲(chǔ)過程與直接調(diào)用SQL語句的網(wǎng)絡(luò)通信量可能不會(huì)有很大的差別，可是如果存儲(chǔ)過程包含上百行SQL 語句，那么其性能絕對(duì)比一條一條的調(diào)用SQL 語句要高得多。?(2)執(zhí)行速度更快。?有兩個(gè)原因：首先，在存儲(chǔ)過程創(chuàng)建的時(shí)候，數(shù)據(jù)庫已經(jīng)對(duì)其進(jìn)行了一次解析和優(yōu)化。其次，存儲(chǔ)過程一旦執(zhí)行，在內(nèi)存中就會(huì)保留一份這個(gè)存儲(chǔ)過程，這樣下次再執(zhí)行同樣的存儲(chǔ)過程時(shí)，可以從內(nèi)存中直接調(diào)用。(3)更強(qiáng)的適應(yīng)性。?由于存儲(chǔ)過程對(duì)數(shù)據(jù)庫的訪問是通過存儲(chǔ)過程來進(jìn)行的，因此數(shù)據(jù)庫開發(fā)人員可以在不改動(dòng)存儲(chǔ)過程接口的情況下對(duì)數(shù)據(jù)庫進(jìn)行任何改動(dòng)，而這些改動(dòng)不會(huì)對(duì)應(yīng)用程序造成影響。(4)分布式工作。?應(yīng)用程序和數(shù)據(jù)庫的編碼工作可以分別獨(dú)立進(jìn)行，而不會(huì)相互壓制。

1.3 存儲(chǔ)過程的缺點(diǎn)

[if !supportLists]（1）[endif]如果更改范圍大到需要對(duì)輸入存儲(chǔ)過程的參數(shù)進(jìn)行更改，或者要更改由其返回的數(shù)據(jù)，則您仍需要更新程序集中的代碼以添加參數(shù)、更新GetValue()調(diào)用，等等，這時(shí)候估計(jì)比較繁瑣了。?（2）可移植性差由于存儲(chǔ)過程將應(yīng)用程序綁定到SQL Server，因此使用存儲(chǔ)過程封裝業(yè)務(wù)邏輯將限制應(yīng)用程序的可移植性。

1.4 創(chuàng)建存儲(chǔ)過程的參數(shù)

1.procedure_name：存儲(chǔ)過程的名稱，在前面加#為局部臨時(shí)存儲(chǔ)過程，加##為全局臨時(shí)存儲(chǔ)過程。2.; number：是可選的整數(shù)，用來對(duì)同名的過程分組，以便用一條 DROP PROCEDURE 語句即可將同組的過程一起除去。例如，名為 orders 的應(yīng)用程序使用的過程可以命名為 orderproc;1、orderproc;2 等。DROP PROCEDURE orderproc 語句將除去整個(gè)組。如果名稱中包含定界標(biāo)識(shí)符，則數(shù)字不應(yīng)包含在標(biāo)識(shí)符中，只應(yīng)在 procedure_name 前后使用適當(dāng)?shù)亩ń绶?3.@parameter： 存儲(chǔ)過程的參數(shù)?？梢杂幸粋€(gè)或多個(gè)。用戶必須在執(zhí)行過程時(shí)提供每個(gè)所聲明參數(shù)的值（除非定義了該參數(shù)的默認(rèn)值）。存儲(chǔ)過程最多可以有 2.100 個(gè)參數(shù)。?使用@符號(hào)作為第一個(gè)字符來指定參數(shù)名稱。參數(shù)名稱必須符合標(biāo)識(shí)符的規(guī)則。每個(gè)過程的參數(shù)僅用于該過程本身；相同的參數(shù)名稱可以用在其它過程中。默認(rèn)情況下，參數(shù)只能代替常量，而不能用于代替表名、列名或其它數(shù)據(jù)庫對(duì)象的名稱。有關(guān)更多信息，請(qǐng)參見 EXECUTE。?4.data_type：參數(shù)的數(shù)據(jù)類型。所有數(shù)據(jù)類型（包括 text、ntext 和 image）均可以用作存儲(chǔ)過程的參數(shù)。不過，cursor 數(shù)據(jù)類型只能用于 OUTPUT 參數(shù)。如果指定的數(shù)據(jù)類型為 cursor，也必須同時(shí)指定 VARYING 和 OUTPUT 關(guān)鍵字。有關(guān) SQL Server 提供的數(shù)據(jù)類型及其語法的更多信息，請(qǐng)參見數(shù)據(jù)類型。?說明對(duì)于可以是cursor數(shù)據(jù)類型的輸出參數(shù)，沒有最大數(shù)目的限制。?5.VARYING： 指定作為輸出參數(shù)支持的結(jié)果集（由存儲(chǔ)過程動(dòng)態(tài)構(gòu)造，內(nèi)容可以變化）。僅適用于游標(biāo)參數(shù)。?

6.default：?參數(shù)的默認(rèn)值。如果定義了默認(rèn)值，不必指定該參數(shù)的值即可執(zhí)行過程。默認(rèn)值必須是常量或 NULL。如果過程將對(duì)該參數(shù)使用 LIKE 關(guān)鍵字，那么默認(rèn)值中可以包含通配符（%、_、[] 和 [^]）。

7.OUTPUT：表明參數(shù)是返回參數(shù)。該選項(xiàng)的值可以返回給 EXEC[UTE]。使用 OUTPUT 參數(shù)可將信息返回給調(diào)用過程。Text、ntext 和 image 參數(shù)可用作 OUTPUT 參數(shù)。使用 OUTPUT 關(guān)鍵字的輸出參數(shù)可以是游標(biāo)占位符。?

8.RECOMPILE:表明 SQL Server 不會(huì)緩存該過程的計(jì)劃，該過程將在運(yùn)行時(shí)重新編譯。在使用非典型值或臨時(shí)值而不希望覆蓋緩存在內(nèi)存中的執(zhí)行計(jì)劃時(shí)，請(qǐng)使用 RECOMPILE 選項(xiàng)。

9.ENCRYPTION:表示 SQL Server 加密 syscomments 表中包含 CREATE PROCEDURE 語句文本的條目。使用 ENCRYPTION 可防止將過程作為 SQL Server 復(fù)制的一部分發(fā)布。 說明 在升級(jí)過程中，SQL Server 利用存儲(chǔ)在 syscomments 中的加密注釋來重新創(chuàng)建加密過程。?

10.FOR REPLICATION?:指定不能在訂閱服務(wù)器上執(zhí)行為復(fù)制創(chuàng)建的存儲(chǔ)過程。.使用 FOR REPLICATION 選項(xiàng)創(chuàng)建的存儲(chǔ)過程可用作存儲(chǔ)過程篩選，且只能在復(fù)制過程中執(zhí)行。本選項(xiàng)不能和 WITH RECOMPILE 選項(xiàng)一起使用。?

11.AS?:指定過程要執(zhí)行的操作。

12.sql_statement?:過程中要包含的任意數(shù)目和類型的 Transact-SQL 語句。但有一些限制。

1.5存儲(chǔ)過程的異常

[if !supportLists]1、[endif]異常的優(yōu)點(diǎn)如果沒有異常，在程序中，應(yīng)當(dāng)檢查每個(gè)命令的成功還是失敗，如　　BEGIN　　SELECT ...　　-- check for ’no data found’ error　　SELECT ...　　-- check for ’no data found’ error　　SELECT ...　　-- check for ’no data found’ error　　這種實(shí)現(xiàn)的方法缺點(diǎn)在于錯(cuò)誤處理沒有與正常處理分開，可讀性差，使用異常，可以方便處理錯(cuò)誤，而且異常處理程序與正常的事務(wù)邏輯分開，提高了可讀性，

[if !supportLists]2、[endif]異常的分類　　　　有兩種類型的異常，一種為內(nèi)部異常，一種為用戶自定義異常，內(nèi)部異常是執(zhí)行期間返回到PL/SQL塊的ORACLE錯(cuò)誤或由PL/SQL代碼的某操作引起的錯(cuò)誤，如除數(shù)為零或內(nèi)存溢出的情況。用戶自定義異常由開發(fā)者顯示定義，在PL/SQL塊中傳遞信息以控制對(duì)于應(yīng)用的錯(cuò)誤處理。?　?3、異常的拋出?由三種方式拋出異常（1）通過PL/SQL運(yùn)行時(shí)引擎?（2）使用RAISE語句?（3）調(diào)用RAISE_APPLICATION_ERROR存儲(chǔ)過程?4、異常的處理?　　PL/SQL程序塊的異常部分包含了程序處理錯(cuò)誤的代碼，當(dāng)異常被拋出時(shí)，一個(gè)異常陷阱就自動(dòng)發(fā)生，程序控制離開執(zhí)行部分轉(zhuǎn)入異常部分,一旦程序進(jìn)入異常部分就不能再回到同一塊的執(zhí)行部分。下面是異常部分的一般語法：?　　EXCEPTION　　WHEN exception_name THEN　　Code for handing exception_name　　[WHEN another_exception THEN　　Code for handing another_exception]　　[WHEN others THEN　　code for handing any other exception.]　　用戶必須在獨(dú)立的WHEN子串中為每個(gè)異常設(shè)計(jì)異常處理代碼，WHEN OTHERS子串必須放置在最后面作為缺省處理器處理沒有顯式處理的異常。當(dāng)異常發(fā)生時(shí)，控制轉(zhuǎn)到異常部分，ORACLE查找當(dāng)前異常相應(yīng)的WHEN..THEN語句，捕捉異常，THEN之后的代碼被執(zhí)行，如果錯(cuò)誤陷阱代碼只是退出相應(yīng)的嵌套塊，那么程序?qū)⒗^續(xù)執(zhí)行內(nèi)部塊END后面的語句。如果沒有找到相應(yīng)的異常陷阱，那么將執(zhí)行WHEN OTHERS。在異常部分WHEN 子串沒有數(shù)量限制。?　　EXCEPTION　　WHEN inventory_too_low THEN　　order_rec.staus:='backordered';　　replenish_inventory(inventory_nbr=>　　inventory_rec.sku,min_amount=>order_rec.qty-inventory_rec.qty);　　WHEN discontinued_item THEN　　--code for discontinued_item processing　　WHEN zero_divide THEN　　--code for zero_divide　　WHEN OTHERS THEN　　--code for any other exception　　END;　　當(dāng)異常拋出后，控制無條件轉(zhuǎn)到異常部分，這就意味著控制不能回到異常發(fā)生的位置，當(dāng)異常被處理和解決后，控制返回到上一層執(zhí)行部分的下一條語句?！　EGIN　　DECLARE　　bad_credit exception;　　BEGIN　　RAISE?bad_credit;　　--發(fā)生異常，控制轉(zhuǎn)向；?　　EXCEPTION　　WHEN bad_credit THEN　　dbms_output.put_line('bad_credit');　　END;　　--bad_credit異常處理后，控制轉(zhuǎn)到這里?　　EXCEPTION　　WHEN OTHERS THEN　　--控制不會(huì)從bad_credit異常轉(zhuǎn)到這里?　　--因?yàn)閎ad_credit已被處理?　　END;　　當(dāng)異常發(fā)生時(shí)，在塊的內(nèi)部沒有該異常處理器時(shí)，控制將轉(zhuǎn)到或傳播到上一層塊的異常處理部分?！　　　EGIN　　DECLARE ---內(nèi)部塊開始?　　　　bad_credit exception;　　BEGIN　　RAISE?bad_credit;　　　　--發(fā)生異常，控制轉(zhuǎn)向；?　　EXCEPTION　　WHEN ZERO_DIVIDE THEN --不能處理bad_credite異常?　　dbms_output.put_line('divide by zero error');　　　　END --結(jié)束內(nèi)部塊?　　　　--控制不能到達(dá)這里，因?yàn)楫惓]有解決；?　　　　--異常部分?　　　　EXCEPTION　　WHEN OTHERS THEN　　--由于bad_credit沒有解決，控制將轉(zhuǎn)到這里?　　END;

[if !supportLists]5、[endif]異常的傳播　　沒有處理的異常將沿檢測(cè)異常調(diào)用程序傳播到外面，當(dāng)異常被處理并解決或到達(dá)程序最外層傳播停止。在聲明部分拋出的異常將控制轉(zhuǎn)到上一層的異常部分。?　　BEGIN　　executable statements　　BEGIN　　today DATE:='SYADATE'; --ERRROR　　　　BEGIN --內(nèi)部塊開始?　　dbms_output.put_line('this line will not execute');　　EXCEPTION　　WHEN OTHERS THEN　　--異常不會(huì)在這里處理　　END;--內(nèi)部塊結(jié)束?　　EXCEPTION　　WHEN OTHERS THEN　　處理異?！　ND

二、存儲(chǔ)過程舉例

2.1創(chuàng)建語法

2.2創(chuàng)建不帶參數(shù)的存儲(chǔ)過程

2.3修改存儲(chǔ)過程

2.4帶通配符參數(shù)存儲(chǔ)過程

2.5帶輸出參數(shù)存儲(chǔ)過程

2.6不帶緩存存儲(chǔ)過程

2.7加密存儲(chǔ)過程

2.8帶游標(biāo)存儲(chǔ)過程

2.9分頁存儲(chǔ)過程

https://wenku.baidu.com/view/9059338f6edb6f1afe001f0d.html