Oracle儲存過程

菜鳥界的菜鳥發表於2020-11-27

一.什麼是儲存過程

儲存過程，百度百科上是這樣解釋的，儲存過程（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；

分類	運算子	含義	示例表示式
算術運算子	+	加	s := 2 + 2;
	-	減	s := 3 – 1;
	*	乘	*s := 2 3;**
	/	除	s := 6 / 2;
	mod(,)	取模，取餘	m : = mod(3,2)
	**	乘方	102 =100**
關係運算子	=	等於	s = 2
	<>或!=或~=	不等於	s != 2
	<	小於	s < 3
	>	大於	s > 0
	<=	小於等於	s <= 9
	>=	大於等於	s >= 1
比較運算子	LIKE	滿足匹配為true	‘li’ like ‘%i’返回true
	BETWEEN	是否處於一個範圍中	2 between 1 and 3 返回true
	IN	是否處於一個集合中	‘x’ in (‘x’,’y’) 返回true
	IS NULL	判斷變數是否為空	若：n:=3,n is null,返回false
邏輯運算子	AND	邏輯與	s=3 and c is null
	OR	邏輯或	s=3 or c is null
	NOT	邏輯非	not c is null
其他	:=	賦值	s := 0;
	..	範圍	1..9，即1至9範圍
	\|\|	字串連線	‘hello’\|\|’world’

(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.下表是常用的遊標屬性：

屬性	描述
%FOUND	如果DML語句執行後影響有資料被更新或DQL查到了結果，返回true。否則，返回false。
%NOTFOUND	如果DML語句執行後影響有資料被更新或DQL查到了結果，返回false。否則，返回true。
%ISOPEN	遊標開啟時返回true，反之，返回false。
%ROWCOUNT	返回DML執行後影響的行數。

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<=total LOOP
UPDATE student SET grade=grade+1 WHERE s_no=i;
i := i + 1;
IF i >= 100000 THEN
COMMIT;
END IF;
END LOOP;
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;

例子：查出年齡小於10歲的學生姓名賦值給陣列arr_name變數

SELECT s_name BULK COLLECT INTO arr_name FROM s_age < 10;

(2).FETCH INTO

從一個集合中抓取一部分資料賦值給一個集合變數。

語法結構如下：

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 < 10;
--宣告瞭一個聯合陣列型別，元素型別和遊標cur_no每個元素的型別一致
TYPE ARR_NO IS VARRAY(10) OF cur_no%ROWTYPE;
--宣告一個該陣列型別的變數no
no ARR_NO;
BEGIN
FETCH cur_no BULK COLLECT INTO no LIMIT 100;
FORALL i IN 1..no.count SAVE EXCEPTONS
UPDATE student SET s_grade=s_grade-1 WHERE no(i);
END;

說明：先查出年齡小於10歲的學生的學號放在遊標裡，再每次從遊標裡拿出100個學號，進行更新，給他們的年級降一級。

(3).RETURNING

BULK COLLECT除了與SELECT，FETCH進行批量繫結之外，還可以與INSERT，DELETE，UPDATE語句結合使用，可以返回這些DML語句執行後所影響的記錄內容（某些欄位）。

再看一眼學生表的欄位情況：student(s_no, s_name, s_age, s_grade)

語法結構如下：

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 < 10
RETURNING s_name BULK COLLECT INTO names;
END;

說明：

NAME_COLLECT：是一個集合型別，型別是student表的name欄位的型別。

names：定義了一個NAME_COLLECT型別的變數。

(4).注意事項

a.不能對使用字串型別作鍵的關聯陣列使用BULK COLLECT 子句。

b.只能在伺服器端的程式中使用BULK COLLECT，如果在客戶端使用，就會產生一個不支援這個特性的錯誤。

c.BULK COLLECT INTO 的目標物件必須是集合型別。

d.複合目標(如物件型別)不能在RETURNING INTO 子句中使用。

e.如果有多個隱式的資料型別轉換的情況存在，多重複合目標就不能在BULK COLLECT INTO 子句中使用。

f.如果有一個隱式的資料型別轉換，複合目標的集合(如物件型別集合)就不能用於BULK COLLECTINTO 子句中。

2.FORALL

(1).語法

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含義如上

聯合陣列請看文章（或自行百度）：https://blog.csdn.net/leshami/article/details/7372061

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.案例實戰

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

這條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，指令碼如下：