oracle中什么是存儲過程

在oracle中，存儲過程是一組為了完成特定功能的sql語句集，經編譯后存儲在數據庫中；經過第一次編譯后再次調用不需要再次編譯，用戶通過指定存儲過程的名字并給出參數來調用存儲過程。

本教程操作環境：Windows7系統、Oracle 11g版、Dell G3電腦。

一、什么是存儲過程

存儲過程，百度百科上是這樣解釋的，存儲過程（Stored Procedure）是在大型數據庫系統中，一組為了完成特定功能的SQL 語句集，存儲在數據庫中，經過第一次編譯后再次調用不需要再次編譯，用戶通過指定存儲過程的名字并給出參數（如果該存儲過程帶有參數）來調用存儲過程。

簡單的說就是專門干一件事一段sql語句。

可以由數據庫自己去調用，也可以由Java程序去調用。

在oracle數據庫中存儲過程是procedure。

二、為什么要寫存儲過程

1、效率高

??存儲過程編譯一次后，就會存到數據庫，每次調用時都直接執行。而普通的sql語句我們要保存到其他地方（例如：記事本? 上），都要先分析編譯才會執行。所以想對而言存儲過程效率更高。

2、降低網絡流量

存儲過程編譯好會放在數據庫，我們在遠程調用時，不會傳輸大量的字符串類型的sql語句。

3、復用性高

存儲過程往往是針對一個特定的功能編寫的，當再需要完成這個特定的功能時，可以再次調用該存儲過程。

4、可維護性高

當功能要求發生小的變化時，修改之前的存儲過程比較容易，花費精力少。

5、安全性高

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

三、存儲過程基礎

1、存儲過程結構

(1)、基本結構

Oracle存儲過程包含三部分：過程聲明，執行過程部分，存儲過程異常（可寫可不寫，要增強腳本的容錯性和調試的方便性那就寫上異常處理）

(2)、無參存儲過程

CREATE?OR?REPLACE?PROCEDURE?demo?AS/IS 	變量2?DATE; 	變量3?number; BEGIN 	--要處理的業務邏輯 	EXCEPTION????--存儲過程異常 END

這里的as和is一樣任選一個，在這里沒有區別，其中demo是存儲過程名稱。

(3)、有參存儲過程

a.帶參數的存儲過程

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

上面腳本中，

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

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

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

b.帶參數的存儲過程并且進行賦值

CREATE?OR?REPLACE?PROCEDURE?存儲過程名稱( ???????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('符合該年齡的學生有'||total||'人'); ??EXCEPTION ????WHEN?too_many_rows?THEN? ????DBMS_OUTPUT.PUT_LINE('返回值多于1行');? END

上面腳本中：

其中參數IN表示輸入參數，是參數的默認模式。
OUT表示返回值參數，類型可以使用任意Oracle中的合法類型。
OUT模式定義的參數只能在過程體內部賦值，表示該參數可以將某個值傳遞回調用他的過程
IN OUT表示該參數可以向該過程中傳遞值，也可以將某個值傳出去

• 第7行：查詢語句，把參數s_age作為過濾條件，INTO關鍵字，把查到的結果賦給total變量。

• 第8行：輸出查詢結果，在數據庫中“||”用來連接字符串

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

2、存儲過程語法

(1)、運算符

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

(2)、SELECT INTO STATEMENT語句

該語句將select到的結果賦值給一個或多個變量，例如：

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

上面腳本中：

存儲過程名稱：DEMO_CDD1, student是學生表，要求查出成績為100分的那個學生的姓名，年齡，籍貫

(3)、選擇語句

a、if..END IF

學生表的sex字段：1-男生；0-女生

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

b、IF..ELSE..END IF

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

(4)、循環語句

a、基本循環

LOOP ??IF?表達式?THEN ????EXIT; ??END?IF END?LOOP;

b、while循環

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

c、for循環

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

(5)、游標

??? Oracle會創建一個存儲區域，被稱為上下文區域，用于處理SQL語句，其中包含需要處理的語句，例如所有的信息，行數處理，等等。

??? 游標是指向這一上下文的區域。 PL/SQL通過控制光標在上下文區域。游標持有的行(一個或多個)由SQL語句返回。行集合光標保持的被稱為活動集合。

a、下表是常用的游標屬性：

b、使用游標

聲明游標定義游標的名稱和相關的SELECT語句：

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

打開游標游標分配內存，使得它準備取的SQL語句轉換成它返回的行：

OPEN?cur_cdd;

抓取游標中的數據，可用LIMIT關鍵字來限制條數，如果沒有默認每次抓取一條：

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

關閉游標來釋放分配的內存:

CLOSE?cur_cdd;

3、pl/sql處理存儲過程

(1)、新建存儲過程：右鍵procedures，點擊new，彈出PROCEDURE框，再點擊OK，如下圖：

(2)、在下面的編輯區，編寫存儲過程腳本

(3)、在這里我們編寫一個demo_cdd存儲過程，要求輸出“hello world”，如下圖：

(4)、右鍵剛才新建的存儲過程名稱，點擊“Test”，在點擊執行按鈕

4.案例實戰

場景：

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

這條sql，寫出來如下：

update?student?set?s_grade=s_grade+1

分析：

如果我們直接運行運行這條sql，因數據量太大會把數據庫undo表空間撐爆，從而發生異常。那我們來寫個存儲過程，進行批量更新，我們每10萬條提交一次。

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;

上面案例中存在問題，應粉絲要求，把改后的案例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萬條提交一次       	  COMMIT;     	END IF;     END LOOP;     COMMIT;  -- 最后一批不夠10萬條的提交一次     dbms_output.put_line('finished!'); END;

四、存儲過程進階

?????? 在上面的案例中，我們的存儲過程處理完所有數據要多長時間呢？事實我沒有等到它執行完，在我可接受的時間范圍內它沒有完成。那么對于處理這種千萬級數據量的情況，存儲過程是不是束手無策呢？答案是否定的，接下來我們看看其他絕招。

?????? 我們先來分析下執行過程的執行過程：一個存儲過程編譯后，在一條語句一條語句的執行時，如果遇到pl/sql語句就拿去給pl/sql引擎執行，如果遇到sql語句就送到sql引擎執行，然后把執行結果再返回給pl/sql引擎。遇到一個大數據量的更新，則執行焦點（正在執行的，狀態處于ACTIVE）會不斷的來回切換。

?????? Pl/SQL與SQL引擎之間的通信則稱之為上下文切換，過多的上下文切換將帶來過量的性能負載。最終導致效率降低，處理速度緩慢。

?????? 從Oracle8i開始PL/SQL引入了兩個新的數據操縱語句：FORALL、BUIK COLLECT，這些語句大大滴減少了上下文切換次數（一次切換多次執行），同時提高DML性能，因此運用了這些語句的存儲過程在處理大量數據時速度簡直和飛一樣。

1、BUIK COLLECT

??? Oracle8i中首次引入了Bulk Collect特性，Bulk Collect會能進行批量檢索，會將檢索結果結果一次性綁定到一個集合變量中，而不是通過游標cursor一條一條的檢索處理。可以在SELECT INTO、FETCH INTO、RETURNING INTO語句中使用BULK COLLECT，接下來我們一起看看這些語句中是如何使用BULK COLLECT的。

(1)、SELECT INTO

查出來一個結果集合賦值給一個集合變量。

語法結構是：

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

說明：

?????? field：要查詢的字段，可以是一個或多個（要保證和后面的集合變量要向對應）。

?????? var_collect：集合變量（聯合數組等），用來存放查到的結果。

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

?????? colStatement：后面過濾條件語句。比如s_age

例子：查出年齡小于10歲的學生姓名賦值給數組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;">從一個集合中抓取一部分數據賦值給一個集合變量。</p><p style="margin-left:0cm;">語法結構如下：</p><pre class="has">FETCH?cur1?BULK?COLLECT?INTO?var_collect?[LIMIT?rows]

說明：

??????? cur1：是個數據集合，例如是個游標。

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

??????? [LIMIT rows]：可有可無，限制每次抓取的數據量。不寫的話，默認每次一條數據。

例子：給年齡小于10歲的學生的年級降一級。

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

說明：

??????? DMLStatement：是一個DML語句。

??????? field：是這個表的某個字段，當然也可以寫多個逗號隔開（field1，field2, field3）。

??????? var_field：一個類型為該字段類型的集合，多個的話用逗號隔開，如下：

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

例子：獲取那些因為年齡小于10歲而年級被將一級的學生的姓名集合。

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;">說明：</p><p style="margin-left:0cm;">?????? NAME_COLLECT：是一個集合類型，類型是student表的name字段的類型。</p><p style="margin-left:0cm;">?????? names：定義了一個NAME_COLLECT類型的變量。</p><p style="margin-left:0cm;"><strong>(4)、注意事項</strong></p><p style="margin-left:0cm;">a.不能對使用字符串類型作鍵的關聯數組使用BULK COLLECT 子句。</p><p style="margin-left:0cm;">b.只能在服務器端的程序中使用BULK COLLECT，如果在客戶端使用，就會產生一個不支持這個特性的錯誤。</p><p style="margin-left:0cm;">c.BULK COLLECT INTO 的目標對象必須是集合類型。</p><p style="margin-left:0cm;">d.復合目標(如對象類型)不能在RETURNING INTO 子句中使用。</p><p style="margin-left:0cm;">e.如果有多個隱式的數據類型轉換的情況存在，多重復合目標就不能在BULK COLLECT INTO 子句中使用。</p><p style="margin-left:0cm;">f.如果有一個隱式的數據類型轉換，復合目標的集合(如對象類型集合)就不能用于BULK COLLECTINTO 子句中。</p><h3 id="2.FORALL" style="margin-left:0cm;">2、FORALL</h3><p style="margin-left:0cm;"><strong>(1)、語法</strong></p><pre class="has">FORALL?index?IN?bounds?[SAVE?EXCEPTIONS]????? ?????sqlStatement；

說明：

??????? index是指下標；

??????? bounds是一個邊界，形式是start..end

??????? [SAVE EXCEPTIONS] 可寫可不寫，這個下面介紹；

??????? sqlStatement是一個DML語句，這里有且僅有一個sql語句；

例子：

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

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

(2)、SAVE EXCEPTIONS

通常情況寫我們在執行DML語句時，可能會遇到異常，可能致使某個語句或整個事務回滾。如果我們寫FORALL語句時沒有用SAVE EXCEPTIONS語句，那么DML語句會在執行到一半的時候停下來。

?????? 如果我們的FORALL語句后使用了SAVE EXCEPTIONS語句，當在執行過程中如果遇到異常，數據處理會繼續向下進行，發生的異常信息會保存到SQL%BULK_EXCEPTONS的游標屬性中，該游標屬性是個記錄集合，每條記錄有兩個字段，例如：（1, 02300）;

?????? ERROR_INDEX：該字段會存儲發生異常的FORALL語句的迭代編號；

?????? ERROR_CODE：存儲對應異常的，oracle錯誤代碼；

SQL%BULK_EXCEPTONS這個異常信息總是存儲著最近一次執行的FORALL語句可能發生的異常。而這個異常記錄集合異常的個數則由它的COUNT屬性表示，即:

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

(3)、INDICES OF

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

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

該語句語法結構是：

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

說明：

i：集合（嵌套表或聯合數組）下標。

collection：是這個集合。

[SAVE EXCEPTIONS]和sqlStatement上面已經解釋過。

例子：arr_std是一個聯合數組，每個元素包含（name,age,grade）,現在要向student表插入數據。

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適用情況：綁定數組可以是稀疏數組，也可以不是，但我只想使用該數組中元素的一個子集。VALUES OF選項可以指定FORALL語句中循環計數器的值來自于指定集合中元素的值。但是，VALUES OF在使用時有一些限制：

?????? 如果VALUES OF子句中所使用的集合是聯合數組，則必須使用PLS_INTEGER和BINARY_INTEGER進行索引，VALUES OF 子句中所使用的元素必須是PLS_INTEGER或BINARY_INTEGER；

?????? 當VALUES OF 子句所引用的集合為空，則FORALL語句會導致異常；

該語句的語法結構是：

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

說明：i和collection含義如上

聯合數組請看文章（或自行百度）：PL/SQL 聯合數組與嵌套表_樂沙彌的世界-CSDN博客

3、pl/sql調試存儲過程

首先，當前這個用戶得有能調試存儲過程的權限，如果沒有的話，以數據庫管理員身份給你這個用戶授權：

--userName是你要拿到調試存儲過程權限的用戶名 GRANT?DEBUG?ANY?PROCEDURE,DEBUG?CONNECT?SESSION?TO?username;

(1)、右鍵一個存儲過程名稱，點擊測試，如下圖：

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

(2).點擊兩次step into按鈕，進入語句調試，如下圖：

(3).每點擊一次step into按鈕，會想下執行一條語句，也可以查看變量和表達式的值，如下圖：

查看變量值：在查看變量區域，在Variable列輸入變量i，在Value列點擊下，該變量的值就顯示出來了。

4、案例實戰

場景和上面的案例實戰是同一個，如下：

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

這條sql，寫出來如下：

update student set s_grade=s_grade+1

編寫存儲過程：

(1)、存儲過程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;

說明：

??????? 把student表中要更新的記錄的學號拿出來放在游標CUR_STUDENT，每次從這個游標里抓取10萬條數據賦值給數組students，每次更新這10萬條記錄。循環進行直到游標里的數據全部抓取完。

??????? FETCH .. BULK COLLECT INTO .. LIMIT rows語句中：這個rows我測試目前最大可以為10萬條。

(2)、存儲過程2（ROWID）

?????? 如果我們這個student表沒有主鍵，也沒有索引呢，該怎么來做呢？

分析下：

?????? ROWNUM是偽列，每次獲取結果后，然后在結果集里會產生一列，從1開始排，每次都是從1開始排。

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

接下來我們編寫存儲過程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;

說明：

?????? 我們首先查到記錄的ROWID并把它轉換為CHAR類型，存放到游標CUR_STUDENT里，

再每次抓取10萬條數據賦值給數組進行更新，更新語句的WHERE條件時，又把數組元素是CAHR類型的rowid串轉換為ROWID類型。

推薦教程：《Oracle教程》

以上就是