oracle中什么是存儲(chǔ)過(guò)程

在oracle中，存儲(chǔ)過(guò)程是一組為了完成特定功能的sql語(yǔ)句集，經(jīng)編譯后存儲(chǔ)在數(shù)據(jù)庫(kù)中；經(jīng)過(guò)第一次編譯后再次調(diào)用不需要再次編譯，用戶通過(guò)指定存儲(chǔ)過(guò)程的名字并給出參數(shù)來(lái)調(diào)用存儲(chǔ)過(guò)程。

本教程操作環(huán)境：Windows7系統(tǒng)、Oracle 11g版、Dell G3電腦。

一、什么是存儲(chǔ)過(guò)程

存儲(chǔ)過(guò)程，百度百科上是這樣解釋的，存儲(chǔ)過(guò)程（Stored Procedure）是在大型數(shù)據(jù)庫(kù)系統(tǒng)中，一組為了完成特定功能的SQL 語(yǔ)句集，存儲(chǔ)在數(shù)據(jù)庫(kù)中，經(jīng)過(guò)第一次編譯后再次調(diào)用不需要再次編譯，用戶通過(guò)指定存儲(chǔ)過(guò)程的名字并給出參數(shù)（如果該存儲(chǔ)過(guò)程帶有參數(shù)）來(lái)調(diào)用存儲(chǔ)過(guò)程。

簡(jiǎn)單的說(shuō)就是專門(mén)干一件事一段sql語(yǔ)句。

可以由數(shù)據(jù)庫(kù)自己去調(diào)用，也可以由Java程序去調(diào)用。

在oracle數(shù)據(jù)庫(kù)中存儲(chǔ)過(guò)程是procedure。

二、為什么要寫(xiě)存儲(chǔ)過(guò)程

1、效率高

??存儲(chǔ)過(guò)程編譯一次后，就會(huì)存到數(shù)據(jù)庫(kù)，每次調(diào)用時(shí)都直接執(zhí)行。而普通的sql語(yǔ)句我們要保存到其他地方（例如：記事本? 上），都要先分析編譯才會(huì)執(zhí)行。所以想對(duì)而言存儲(chǔ)過(guò)程效率更高。

2、降低網(wǎng)絡(luò)流量

存儲(chǔ)過(guò)程編譯好會(huì)放在數(shù)據(jù)庫(kù)，我們?cè)谶h(yuǎn)程調(diào)用時(shí)，不會(huì)傳輸大量的字符串類型的sql語(yǔ)句。

3、復(fù)用性高

存儲(chǔ)過(guò)程往往是針對(duì)一個(gè)特定的功能編寫(xiě)的，當(dāng)再需要完成這個(gè)特定的功能時(shí)，可以再次調(diào)用該存儲(chǔ)過(guò)程。

4、可維護(hù)性高

當(dāng)功能要求發(fā)生小的變化時(shí)，修改之前的存儲(chǔ)過(guò)程比較容易，花費(fèi)精力少。

5、安全性高

完成某個(gè)特定功能的存儲(chǔ)過(guò)程一般只有特定的用戶可以使用，具有使用身份限制，更安全。

三、存儲(chǔ)過(guò)程基礎(chǔ)

1、存儲(chǔ)過(guò)程結(jié)構(gòu)

(1)、基本結(jié)構(gòu)

Oracle存儲(chǔ)過(guò)程包含三部分：過(guò)程聲明，執(zhí)行過(guò)程部分，存儲(chǔ)過(guò)程異常（可寫(xiě)可不寫(xiě)，要增強(qiáng)腳本的容錯(cuò)性和調(diào)試的方便性那就寫(xiě)上異常處理）

(2)、無(wú)參存儲(chǔ)過(guò)程

CREATE?OR?REPLACE?PROCEDURE?demo?AS/IS 	變量2?DATE; 	變量3?number; BEGIN 	--要處理的業(yè)務(wù)邏輯 	EXCEPTION????--存儲(chǔ)過(guò)程異常 END

這里的as和is一樣任選一個(gè)，在這里沒(méi)有區(qū)別，其中demo是存儲(chǔ)過(guò)程名稱。

(3)、有參存儲(chǔ)過(guò)程

a.帶參數(shù)的存儲(chǔ)過(guò)程

CREATE?OR?REPLACE?PROCEDURE?存儲(chǔ)過(guò)程名稱(param1?student.id%TYPE) AS/IS name?student.name%TYPE; age?number?:=20; BEGIN ??--業(yè)務(wù)處理..... END

上面腳本中，

• 第1行：param1 是參數(shù)，類型和student表id字段的類型一樣。

• 第3行：聲明變量name，類型是student表name字段的類型（同上）。

• 第4行：聲明變量age，類型數(shù)數(shù)字，初始化為20

b.帶參數(shù)的存儲(chǔ)過(guò)程并且進(jìn)行賦值

CREATE?OR?REPLACE?PROCEDURE?存儲(chǔ)過(guò)程名稱( ???????s_no?in?varchar, ???????s_name?out?varchar, ???????s_age?number)?AS total?NUMBER?:=?0; BEGIN ??select?count(1)?INTO?total?FROM?student?s?WHERE?s.age=s_age; ??dbms_output.put_line('符合該年齡的學(xué)生有'||total||'人'); ??EXCEPTION ????WHEN?too_many_rows?THEN? ????DBMS_OUTPUT.PUT_LINE('返回值多于1行');? END

上面腳本中：

其中參數(shù)IN表示輸入?yún)?shù)，是參數(shù)的默認(rèn)模式。
OUT表示返回值參數(shù)，類型可以使用任意Oracle中的合法類型。
OUT模式定義的參數(shù)只能在過(guò)程體內(nèi)部賦值，表示該參數(shù)可以將某個(gè)值傳遞回調(diào)用他的過(guò)程
IN OUT表示該參數(shù)可以向該過(guò)程中傳遞值，也可以將某個(gè)值傳出去

• 第7行：查詢語(yǔ)句，把參數(shù)s_age作為過(guò)濾條件，INTO關(guān)鍵字，把查到的結(jié)果賦給total變量。

• 第8行：輸出查詢結(jié)果，在數(shù)據(jù)庫(kù)中“||”用來(lái)連接字符串

• 第9—11行：做異常處理

2、存儲(chǔ)過(guò)程語(yǔ)法

(1)、運(yùn)算符

這里s，m，n是變量，類型是number；

(2)、SELECT INTO STATEMENT語(yǔ)句

該語(yǔ)句將select到的結(jié)果賦值給一個(gè)或多個(gè)變量，例如：

CREATE?OR?REPLACE?PROCEDURE?DEMO_CDD1?IS s_name?VARCHAR2;???--學(xué)生名稱 s_age?NUMBER;??????--學(xué)生年齡 s_address?VARCHAR2;?--學(xué)生籍貫 BEGIN ??--給單個(gè)變量賦值 ??SELECT?student_address?INTO?s_address ??FROM?student?where?student_grade=100; ???--給多個(gè)變量賦值 ??SELECT?student_name,student_age?INTO?s_name,s_age ??FROM?student?where?student_grade=100; ??--輸出成績(jī)?yōu)?00分的那個(gè)學(xué)生信息 ??dbms_output.put_line('姓名：'||s_name||',年齡：'||s_age||',籍貫：'||s_address); END

上面腳本中：

存儲(chǔ)過(guò)程名稱：DEMO_CDD1, student是學(xué)生表，要求查出成績(jī)?yōu)?00分的那個(gè)學(xué)生的姓名，年齡，籍貫

(3)、選擇語(yǔ)句

a、if..END IF

學(xué)生表的sex字段：1-男生；0-女生

IF?s_sex=1?THEN ??dbms_output.put_line('這個(gè)學(xué)生是男生'); END?IF

b、IF..ELSE..END IF

IF?s_sex=1?THEN ??dbms_output.put_line('這個(gè)學(xué)生是男生'); ELSE ??dbms_output.put_line('這個(gè)學(xué)生是女生'); END?IF

(4)、循環(huán)語(yǔ)句

a、基本循環(huán)

LOOP ??IF?表達(dá)式?THEN ????EXIT; ??END?IF END?LOOP;

b、while循環(huán)

WHILE?表達(dá)式?LOOP ??dbms_output.put_line('haha'); END?LOOP;

c、for循環(huán)

FOR?a?in?10?..?20?LOOP ??dbms_output.put_line('value?of?a:?'?||?a); END?LOOP;

(5)、游標(biāo)

??? Oracle會(huì)創(chuàng)建一個(gè)存儲(chǔ)區(qū)域，被稱為上下文區(qū)域，用于處理SQL語(yǔ)句，其中包含需要處理的語(yǔ)句，例如所有的信息，行數(shù)處理，等等。

??? 游標(biāo)是指向這一上下文的區(qū)域。 PL/SQL通過(guò)控制光標(biāo)在上下文區(qū)域。游標(biāo)持有的行(一個(gè)或多個(gè))由SQL語(yǔ)句返回。行集合光標(biāo)保持的被稱為活動(dòng)集合。

a、下表是常用的游標(biāo)屬性：

b、使用游標(biāo)

聲明游標(biāo)定義游標(biāo)的名稱和相關(guān)的SELECT語(yǔ)句：

CURSOR?cur_cdd?IS?SELECT?s_id,?s_name?FROM?student;

打開(kāi)游標(biāo)游標(biāo)分配內(nèi)存，使得它準(zhǔn)備取的SQL語(yǔ)句轉(zhuǎn)換成它返回的行：

OPEN?cur_cdd;

抓取游標(biāo)中的數(shù)據(jù)，可用LIMIT關(guān)鍵字來(lái)限制條數(shù)，如果沒(méi)有默認(rèn)每次抓取一條：

FETCH?cur_cdd?INTO?id,?name?;

關(guān)閉游標(biāo)來(lái)釋放分配的內(nèi)存:

CLOSE?cur_cdd;

3、pl/sql處理存儲(chǔ)過(guò)程

(1)、新建存儲(chǔ)過(guò)程：右鍵procedures，點(diǎn)擊new，彈出PROCEDURE框，再點(diǎn)擊OK，如下圖：

(2)、在下面的編輯區(qū)，編寫(xiě)存儲(chǔ)過(guò)程腳本

(3)、在這里我們編寫(xiě)一個(gè)demo_cdd存儲(chǔ)過(guò)程，要求輸出“hello world”，如下圖：

(4)、右鍵剛才新建的存儲(chǔ)過(guò)程名稱，點(diǎn)擊“Test”，在點(diǎn)擊執(zhí)行按鈕

4.案例實(shí)戰(zhàn)

場(chǎng)景：

有表student(s_no, s_name, s_age, s_grade)，其中s_no-學(xué)號(hào)，也是主鍵，是從1開(kāi)始向上排的（例如：第一個(gè)學(xué)生學(xué)號(hào)是1，第二個(gè)是2，一次類推）；s_name-學(xué)生姓名；s_age-學(xué)生年齡；s_grade-年級(jí)；這張表的數(shù)據(jù)量有幾千萬(wàn)甚至上億。一個(gè)學(xué)年結(jié)束了，我要讓這些學(xué)生全部升一年級(jí)，即，讓s_grade字段加1。

這條sql，寫(xiě)出來(lái)如下：

update?student?set?s_grade=s_grade+1

分析：

如果我們直接運(yùn)行運(yùn)行這條sql，因數(shù)據(jù)量太大會(huì)把數(shù)據(jù)庫(kù)undo表空間撐爆，從而發(fā)生異常。那我們來(lái)寫(xiě)個(gè)存儲(chǔ)過(guò)程，進(jìn)行批量更新，我們每10萬(wàn)條提交一次。

CREATE?OR?REPLACE?PROCEDURE?process_student?is total?NUMBER?:=?0; i?NUMBER?:=?0; BEGIN ??SELECT?COUNT(1)?INTO?total?FROM?student; ??WHILE?i=?100000?THEN ??????COMMIT; ????END?IF; ??END?LOOP; ??dbms_output.put_line('finished!'); END;

上面案例中存在問(wèn)題，應(yīng)粉絲要求，把改后的案例sql更新到原文中，如下案例，方便大家閱讀。

CREATE OR REPLACE PROCEDURE process_student is total NUMBER := 0; i NUMBER := 0; BEGIN     SELECT COUNT(1) INTO total FROM student;     WHILE i<=total LOOP         UPDATE student SET grade=grade+1 WHERE s_no=i;     	i := i + 1;     	IF mod(i,100000) = 0 THEN   -- 每10萬(wàn)條提交一次       	  COMMIT;     	END IF;     END LOOP;     COMMIT;  -- 最后一批不夠10萬(wàn)條的提交一次     dbms_output.put_line('finished!'); END;

四、存儲(chǔ)過(guò)程進(jìn)階

?????? 在上面的案例中，我們的存儲(chǔ)過(guò)程處理完所有數(shù)據(jù)要多長(zhǎng)時(shí)間呢？事實(shí)我沒(méi)有等到它執(zhí)行完，在我可接受的時(shí)間范圍內(nèi)它沒(méi)有完成。那么對(duì)于處理這種千萬(wàn)級(jí)數(shù)據(jù)量的情況，存儲(chǔ)過(guò)程是不是束手無(wú)策呢？答案是否定的，接下來(lái)我們看看其他絕招。

?????? 我們先來(lái)分析下執(zhí)行過(guò)程的執(zhí)行過(guò)程：一個(gè)存儲(chǔ)過(guò)程編譯后，在一條語(yǔ)句一條語(yǔ)句的執(zhí)行時(shí)，如果遇到pl/sql語(yǔ)句就拿去給pl/sql引擎執(zhí)行，如果遇到sql語(yǔ)句就送到sql引擎執(zhí)行，然后把執(zhí)行結(jié)果再返回給pl/sql引擎。遇到一個(gè)大數(shù)據(jù)量的更新，則執(zhí)行焦點(diǎn)（正在執(zhí)行的，狀態(tài)處于ACTIVE）會(huì)不斷的來(lái)回切換。

?????? Pl/SQL與SQL引擎之間的通信則稱之為上下文切換，過(guò)多的上下文切換將帶來(lái)過(guò)量的性能負(fù)載。最終導(dǎo)致效率降低，處理速度緩慢。

?????? 從Oracle8i開(kāi)始PL/SQL引入了兩個(gè)新的數(shù)據(jù)操縱語(yǔ)句：FORALL、BUIK COLLECT，這些語(yǔ)句大大滴減少了上下文切換次數(shù)（一次切換多次執(zhí)行），同時(shí)提高DML性能，因此運(yùn)用了這些語(yǔ)句的存儲(chǔ)過(guò)程在處理大量數(shù)據(jù)時(shí)速度簡(jiǎn)直和飛一樣。

1、BUIK COLLECT

??? Oracle8i中首次引入了Bulk Collect特性，Bulk Collect會(huì)能進(jìn)行批量檢索，會(huì)將檢索結(jié)果結(jié)果一次性綁定到一個(gè)集合變量中，而不是通過(guò)游標(biāo)cursor一條一條的檢索處理。可以在SELECT INTO、FETCH INTO、RETURNING INTO語(yǔ)句中使用BULK COLLECT，接下來(lái)我們一起看看這些語(yǔ)句中是如何使用BULK COLLECT的。

(1)、SELECT INTO

查出來(lái)一個(gè)結(jié)果集合賦值給一個(gè)集合變量。

語(yǔ)法結(jié)構(gòu)是：

SELECT?field?BULK?COLLECT?INTO?var_conllect?FROM?table?where?colStatement;

說(shuō)明：

?????? field：要查詢的字段，可以是一個(gè)或多個(gè)（要保證和后面的集合變量要向?qū)?yīng)）。

?????? var_collect：集合變量（聯(lián)合數(shù)組等），用來(lái)存放查到的結(jié)果。

?????? table：表名，要查詢的表。

?????? colStatement：后面過(guò)濾條件語(yǔ)句。比如s_age

例子：查出年齡小于10歲的學(xué)生姓名賦值給數(shù)組arr_name變量

SELECT?s_name?BULK?COLLECT?INTO?arr_name?FROM?s_age?<p style="margin-left:0cm;"><strong>(2)、FETCH INTO</strong></p><p style="margin-left:0cm;">從一個(gè)集合中抓取一部分?jǐn)?shù)據(jù)賦值給一個(gè)集合變量。</p><p style="margin-left:0cm;">語(yǔ)法結(jié)構(gòu)如下：</p><pre class="has">FETCH?cur1?BULK?COLLECT?INTO?var_collect?[LIMIT?rows]

說(shuō)明：

??????? cur1：是個(gè)數(shù)據(jù)集合，例如是個(gè)游標(biāo)。

??????? var_collect：含義同上。

??????? [LIMIT rows]：可有可無(wú)，限制每次抓取的數(shù)據(jù)量。不寫(xiě)的話，默認(rèn)每次一條數(shù)據(jù)。

例子：給年齡小于10歲的學(xué)生的年級(jí)降一級(jí)。

--查詢年齡小于10歲的學(xué)生的學(xué)號(hào)放在游標(biāo)cur_no里 CURSOR?cur_no?IS? 		SELECT?s_no?FROM?student?WHERE?s_age?<p style="margin-left:0cm;">說(shuō)明：先查出年齡小于10歲的學(xué)生的學(xué)號(hào)放在游標(biāo)里，再每次從游標(biāo)里拿出100個(gè)學(xué)號(hào)，進(jìn)行更新，給他們的年級(jí)降一級(jí)。</p><p style="margin-left:0cm;"><strong>(3)、RETURNING</strong></p><p style="margin-left:0cm;">BULK COLLECT除了與SELECT，F(xiàn)ETCH進(jìn)行批量綁定之外，還可以與INSERT，delete，UPDATE語(yǔ)句結(jié)合使用，可以返回這些DML語(yǔ)句執(zhí)行后所影響的記錄內(nèi)容（某些字段）。</p><p style="margin-left:0cm;">再看一眼學(xué)生表的字段情況：student(s_no, s_name, s_age, s_grade)</p><p style="margin-left:0cm;">語(yǔ)法結(jié)構(gòu)如下：</p><pre class="has">DMLStatement ???????RETURNING?field?BULK?COLLECT?INTO?var_field;

說(shuō)明：

??????? DMLStatement：是一個(gè)DML語(yǔ)句。

??????? field：是這個(gè)表的某個(gè)字段，當(dāng)然也可以寫(xiě)多個(gè)逗號(hào)隔開(kāi)（field1，field2, field3）。

??????? var_field：一個(gè)類型為該字段類型的集合，多個(gè)的話用逗號(hào)隔開(kāi)，如下：

??????? （var_field1, var_field2, var_field3）

例子：獲取那些因?yàn)槟挲g小于10歲而年級(jí)被將一級(jí)的學(xué)生的姓名集合。

TYPE?NAME_COLLECT?IS?TABLE?OF?student.s_name%TYPE; names?NAME_COLLECT; BEGIN ??UPDATE?student?SET?s_grade=s_grade-1?WHERE?s_age?<p style="margin-left:0cm;">說(shuō)明：</p><p style="margin-left:0cm;">?????? NAME_COLLECT：是一個(gè)集合類型，類型是student表的name字段的類型。</p><p style="margin-left:0cm;">?????? names：定義了一個(gè)NAME_COLLECT類型的變量。</p><p style="margin-left:0cm;"><strong>(4)、注意事項(xiàng)</strong></p><p style="margin-left:0cm;">a.不能對(duì)使用字符串類型作鍵的關(guān)聯(lián)數(shù)組使用BULK COLLECT 子句。</p><p style="margin-left:0cm;">b.只能在服務(wù)器端的程序中使用BULK COLLECT，如果在客戶端使用，就會(huì)產(chǎn)生一個(gè)不支持這個(gè)特性的錯(cuò)誤。</p><p style="margin-left:0cm;">c.BULK COLLECT INTO 的目標(biāo)對(duì)象必須是集合類型。</p><p style="margin-left:0cm;">d.復(fù)合目標(biāo)(如對(duì)象類型)不能在RETURNING INTO 子句中使用。</p><p style="margin-left:0cm;">e.如果有多個(gè)隱式的數(shù)據(jù)類型轉(zhuǎn)換的情況存在，多重復(fù)合目標(biāo)就不能在BULK COLLECT INTO 子句中使用。</p><p style="margin-left:0cm;">f.如果有一個(gè)隱式的數(shù)據(jù)類型轉(zhuǎn)換，復(fù)合目標(biāo)的集合(如對(duì)象類型集合)就不能用于BULK COLLECTINTO 子句中。</p><h3 id="2.FORALL" style="margin-left:0cm;">2、FORALL</h3><p style="margin-left:0cm;"><strong>(1)、語(yǔ)法</strong></p><pre class="has">FORALL?index?IN?bounds?[SAVE?EXCEPTIONS]????? ?????sqlStatement；

說(shuō)明：

??????? index是指下標(biāo)；

??????? bounds是一個(gè)邊界，形式是start..end

??????? [SAVE EXCEPTIONS] 可寫(xiě)可不寫(xiě)，這個(gè)下面介紹；

??????? sqlStatement是一個(gè)DML語(yǔ)句，這里有且僅有一個(gè)sql語(yǔ)句；

例子：

--例子1：移除年級(jí)是5到10之間的學(xué)生 FORALL?i?IN?5..10 ???????DELETE?FROM?student?where?s_grade=i;

--例子：2，arr是一個(gè)數(shù)組,存著要升高一年級(jí)的學(xué)生名稱 FORALL?s?IN?1..arr.count?SAVE?EXCEPTIONS ???????UPDATE?student?SET?s_grade=s_grade+1?WHERE?s_name=arr(i);

(2)、SAVE EXCEPTIONS

通常情況寫(xiě)我們?cè)趫?zhí)行DML語(yǔ)句時(shí)，可能會(huì)遇到異常，可能致使某個(gè)語(yǔ)句或整個(gè)事務(wù)回滾。如果我們寫(xiě)FORALL語(yǔ)句時(shí)沒(méi)有用SAVE EXCEPTIONS語(yǔ)句，那么DML語(yǔ)句會(huì)在執(zhí)行到一半的時(shí)候停下來(lái)。

?????? 如果我們的FORALL語(yǔ)句后使用了SAVE EXCEPTIONS語(yǔ)句，當(dāng)在執(zhí)行過(guò)程中如果遇到異常，數(shù)據(jù)處理會(huì)繼續(xù)向下進(jìn)行，發(fā)生的異常信息會(huì)保存到SQL%BULK_EXCEPTONS的游標(biāo)屬性中，該游標(biāo)屬性是個(gè)記錄集合，每條記錄有兩個(gè)字段，例如：（1, 02300）;

?????? ERROR_INDEX：該字段會(huì)存儲(chǔ)發(fā)生異常的FORALL語(yǔ)句的迭代編號(hào)；

?????? ERROR_CODE：存儲(chǔ)對(duì)應(yīng)異常的，oracle錯(cuò)誤代碼；

SQL%BULK_EXCEPTONS這個(gè)異常信息總是存儲(chǔ)著最近一次執(zhí)行的FORALL語(yǔ)句可能發(fā)生的異常。而這個(gè)異常記錄集合異常的個(gè)數(shù)則由它的COUNT屬性表示，即:

?????? SQL%BULK_EXCEPTONS.COUNT，SQL%BULK_EXCEPTIONS有效的下標(biāo)索引范圍在1到%BULK_EXCEPTIONS.COUNT之間。

(3)、INDICES OF

在Oracle數(shù)據(jù)庫(kù)10g之前有一個(gè)重要的限制，該數(shù)據(jù)庫(kù)從IN范圍子句中的第一行到最后一行，依次讀取集合的內(nèi)容，如果在該范圍內(nèi)遇到一個(gè)未定義的行，Oracle數(shù)據(jù)庫(kù)將引發(fā)ORA-22160異常事件：ORA-22160: element at index [N] does not exist。針對(duì)這一問(wèn)題，Oracle后續(xù)又提供了兩個(gè)新語(yǔ)句：INDICES OF 和 VALUES OF。

接下來(lái)我們來(lái)看看這個(gè)INDICES OF語(yǔ)句，用于處理稀疏數(shù)組或包含有間隙的數(shù)組（例如：一個(gè)集合的某些元素被刪除了）。

該語(yǔ)句語(yǔ)法結(jié)構(gòu)是：

FORALL?i?INDICES?OF?Collection?[SAVE?EXCEPTIONS]  ???????sqlStatement;

說(shuō)明：

i：集合（嵌套表或聯(lián)合數(shù)組）下標(biāo)。

collection：是這個(gè)集合。

[SAVE EXCEPTIONS]和sqlStatement上面已經(jīng)解釋過(guò)。

例子：arr_std是一個(gè)聯(lián)合數(shù)組，每個(gè)元素包含（name,age,grade）,現(xiàn)在要向student表插入數(shù)據(jù)。

FORALL?i?IN?INDICES?OF?arr_stu ???????INSERT?INTO?student?VALUES( ???????????arr_stu(i).name, ??????????????arr_stu(i).age, ??????????????arr_stu(i).grade ???????);

(4)、VALUES OF

VALUES OF適用情況：綁定數(shù)組可以是稀疏數(shù)組，也可以不是，但我只想使用該數(shù)組中元素的一個(gè)子集。VALUES OF選項(xiàng)可以指定FORALL語(yǔ)句中循環(huán)計(jì)數(shù)器的值來(lái)自于指定集合中元素的值。但是，VALUES OF在使用時(shí)有一些限制：

?????? 如果VALUES OF子句中所使用的集合是聯(lián)合數(shù)組，則必須使用PLS_INTEGER和BINARY_INTEGER進(jìn)行索引，VALUES OF 子句中所使用的元素必須是PLS_INTEGER或BINARY_INTEGER；

?????? 當(dāng)VALUES OF 子句所引用的集合為空，則FORALL語(yǔ)句會(huì)導(dǎo)致異常；

該語(yǔ)句的語(yǔ)法結(jié)構(gòu)是：

FORALL?i?IN?VALUES?OF?collection?[SAVE?EXCEPTIONS] ???????sqlStatement;

說(shuō)明：i和collection含義如上

聯(lián)合數(shù)組請(qǐng)看文章（或自行百度）：PL/SQL 聯(lián)合數(shù)組與嵌套表_樂(lè)沙彌的世界-CSDN博客

3、pl/sql調(diào)試存儲(chǔ)過(guò)程

首先，當(dāng)前這個(gè)用戶得有能調(diào)試存儲(chǔ)過(guò)程的權(quán)限，如果沒(méi)有的話，以數(shù)據(jù)庫(kù)管理員身份給你這個(gè)用戶授權(quán)：

--userName是你要拿到調(diào)試存儲(chǔ)過(guò)程權(quán)限的用戶名 GRANT?DEBUG?ANY?PROCEDURE,DEBUG?CONNECT?SESSION?TO?username;

(1)、右鍵一個(gè)存儲(chǔ)過(guò)程名稱，點(diǎn)擊測(cè)試，如下圖：

這里我用的pl/sql是12.0.4版本的，下面截圖中與低版本的pl/sql按鈕位置都相同，只是圖標(biāo)不一樣。

(2).點(diǎn)擊兩次step into按鈕，進(jìn)入語(yǔ)句調(diào)試，如下圖：

(3).每點(diǎn)擊一次step into按鈕，會(huì)想下執(zhí)行一條語(yǔ)句，也可以查看變量和表達(dá)式的值，如下圖：

查看變量值：在查看變量區(qū)域，在Variable列輸入變量i，在Value列點(diǎn)擊下，該變量的值就顯示出來(lái)了。

4、案例實(shí)戰(zhàn)

場(chǎng)景和上面的案例實(shí)戰(zhàn)是同一個(gè)，如下：

有表student(s_no, s_name, s_age, s_grade)，其中s_no-學(xué)號(hào)，也是主鍵，是從1開(kāi)始向上排的（例如：第一個(gè)學(xué)生學(xué)號(hào)是1，第二個(gè)是2，一次類推）；s_name-學(xué)生姓名；s_age-學(xué)生年齡；s_grade-年級(jí)；這張表的數(shù)據(jù)量有幾千萬(wàn)甚至上億。一個(gè)學(xué)年結(jié)束了，我要讓這些學(xué)生全部升一年級(jí)，即，讓s_grade字段加1。

這條sql，寫(xiě)出來(lái)如下：

update student set s_grade=s_grade+1

編寫(xiě)存儲(chǔ)過(guò)程：

(1)、存儲(chǔ)過(guò)程1

名稱為：process_student1，student表的s_no字段類型為varchar2(16)。

CREATE?OR?REPLACE?PROCEDURE?process_student1?AS ????CURSOR?CUR_STUDENT?IS?SELECT?s_no?FROM?student; ????TYPE?REC_STUDENT?IS?VARRAY(100000)?OF?VARCHAR2(16); ????students?REC_STUDENT; BEGIN ??OPEN?CUR_STUDENT; ??WHILE?(TRUE)?LOOP ????FETCH?CUR_STUDENT?BULK?COLLECT?INTO?students?LIMIT?100000; ????FORALL?i?IN?1..students.count?SAVE?EXCEPTIONS ??????UPDATE?student?SET?s_grade=s_grade+1?WHERE?s_no=students(i); ????COMMIT; ????EXIT?WHEN?CUR_STUDENT%NOTFOUND?OR?CUR_STUDENT%NOTFOUND?IS?NULL; ??END?LOO; ??dbms_output.put_line('finished'); END;

說(shuō)明：

??????? 把student表中要更新的記錄的學(xué)號(hào)拿出來(lái)放在游標(biāo)CUR_STUDENT，每次從這個(gè)游標(biāo)里抓取10萬(wàn)條數(shù)據(jù)賦值給數(shù)組students，每次更新這10萬(wàn)條記錄。循環(huán)進(jìn)行直到游標(biāo)里的數(shù)據(jù)全部抓取完。

??????? FETCH .. BULK COLLECT INTO .. LIMIT rows語(yǔ)句中：這個(gè)rows我測(cè)試目前最大可以為10萬(wàn)條。

(2)、存儲(chǔ)過(guò)程2（ROWID）

?????? 如果我們這個(gè)student表沒(méi)有主鍵，也沒(méi)有索引呢，該怎么來(lái)做呢？

分析下：

?????? ROWNUM是偽列，每次獲取結(jié)果后，然后在結(jié)果集里會(huì)產(chǎn)生一列，從1開(kāi)始排，每次都是從1開(kāi)始排。

?????? ?ROWID在每個(gè)表中，每條記錄的ROWID都是唯一的。在這種情況下，我們可以用ROWID。但要注意的是，ROWID是一個(gè)類型，注意它和VARCHAR2之間的轉(zhuǎn)換。有兩個(gè)方法：ROWIDTOCHAR（）是把ROWID類型轉(zhuǎn)換為CHAR類型；CHARTOROWID()是把CAHR類型轉(zhuǎn)換為ROWID類型。

接下來(lái)我們編寫(xiě)存儲(chǔ)過(guò)程process_student2，腳本如下：

CREATE?OR?REPLACE?PROCEDURE?process_student1?AS ????CURSOR?CUR_STUDENT?IS?SELECT?ROWIDTOCHAR(ROWID)?FROM?student; ????TYPE?REC_STUDENT?IS?VARRAY(100000)?OF?VARCHAR2(16); ????students?REC_STUDENT; BEGIN ??OPEN?CUR_STUDENT; ??WHILE?(TRUE)?LOOP ????FETCH?CUR_STUDENT?BULK?COLLECT?INTO?students?LIMIT?100000; ????FORALL?i?IN?1..students.count?SAVE?EXCEPTIONS ??????UPDATE?student?SET?s_grade=s_grade+1?WHERE?ROWID=CHARTOROWID(students(i)); ????COMMIT; ????EXIT?WHEN?CUR_STUDENT%NOTFOUND?OR?CUR_STUDENT%NOTFOUND?IS?NULL; ??END?LOO; ??dbms_output.put_line('finished'); END;

說(shuō)明：

?????? 我們首先查到記錄的ROWID并把它轉(zhuǎn)換為CHAR類型，存放到游標(biāo)CUR_STUDENT里，

再每次抓取10萬(wàn)條數(shù)據(jù)賦值給數(shù)組進(jìn)行更新，更新語(yǔ)句的WHERE條件時(shí)，又把數(shù)組元素是CAHR類型的rowid串轉(zhuǎn)換為ROWID類型。

推薦教程：《Oracle教程》

以上就是