Oracle儲存過程與函數的詳細使用教學

2022-07-18 14:05:27

一、儲存過程

儲存過程是一種命名的PL/SQL資料塊，儲存在Oracle資料庫中，可以被使用者呼叫。儲存過程可以包含引數，也可以沒有引數，它一般沒有返回值。儲存過程是事先編譯好的程式碼，再次呼叫的時候不需再次編譯，因此程式的執行效率非常高。

1、儲存過程的建立

語法如下

create [or replace] 過程名
[<引數1> inioutin out <引數型別>[預設值|:=初始值]]
[,<引數2> inioutin out <引數型別>[預設值|:=初始值],...]
isias
[區域性變數宣告]
begin
程式語句序列
[exception]
例外處理語句序列
end 過程名

引數說明如下：

1、or replace 可選引數，表示如果資料庫中已經存在要建立的過程，則先把原先過程刪除，再重新建立過程，或者說覆蓋原先的過程。

2、如果過程中存在引數，則需要在引數後面用“inioutin out”關鍵字。如果是輸入引數，則引數後面用“in”關鍵字，表示接受外部過程傳遞來的值；如果是輸出引數，則引數後面用“out”關鍵字，表示此引數將在過程中被複制，並傳遞給過程體外；如果是“in out” 關鍵字則表示該引數既具有輸入引數特性，又具有輸出引數的特性。預設是in引數，即如果不寫就預設為in引數。

3、引數型別不能指定長度，只需要給出型別即可。

4、區域性變數宣告中所定義的變數只在該過程中有效。

5、區域性變數宣告，程式語句序列和例外處理語句序列定義和使用同上一章PL/SQL塊。

2、儲存過程的呼叫及刪除

儲存過程建立後，以編譯的形式存在於oracle資料庫中，可以在sql plus中或者pl/sql塊中呼叫。

1、在sql plus中呼叫儲存過程

語法如下：

execute 過程名 [引數序列]

其中execute可以簡寫成exec。

2、在pl/sql塊中呼叫儲存過程

直接把過程名寫到其他pl/sql塊中即可呼叫，此時不需使用execute命令。

3、儲存過程的刪除

儲存過程的刪除和表的刪除類似，基本語法如下所示。

drop procdure 過程名

3、儲存過程的使用

1、不帶引數的儲存過程

1、建立一個儲存過程，向student表中插入一條記錄

create or replace procedure pro_stu is 
begin
	insert into student<id,name,class> values<10,'張三','五班'>;
	commit;
	dbms_output.put_line<'插入一條新紀錄！！！'>;
end pro_stu;

上面儲存過程已經成功建立，但是並沒有執行，執行語句如下。

exec pro_stu;

上面是exec命令執行，我們也可以在PL/SQL塊中直接呼叫，語法如下。

begin
	pro_stu;
end;

2、帶in引數的儲存過程
使用in引數可以向儲存過程中的程式單元輸入資料，在呼叫的時候提供引數值，被儲存過程讀取。這種模式是預設的引數模式。下面看一個範例。

2、建立一個儲存過程，接收來自外部的數值，在儲存過程中判斷該數值是否大於零並顯示。

create or replace procedure pro_decide<
	var_num in number
> is
begin
	if var_num>=0 then
		dbms_output.put_line<'傳遞進來的引數大於等於0'>;
	else
		dbms_output.put_line<'傳遞進來的引數小於0'>;
	end if;
end pro_decide;

執行儲存過程

exec pro_decide<3>;

結果顯示：
傳遞進來的引數大於等於0

3、輸入一個編號，查詢student表中是否有這個編號，如果有則顯示對應學生的姓名，如果沒有則提示沒有對應的學生。

create or replace procedure pro_show<
	var_stuid in student.id%type	--定義in引數
> is
	var_name student.name%type;	--定義儲存過程內部變數
	no_result exception;
begin
	select name into var_name from student where id = var_stuid;	--取值
	if sql%found then
		dbms_output.put_line<'所查詢的學生姓名是：' || var_name>;	--顯示
	end if;
		when no_data_found then
			dbms_output.put_line<'沒有對應此編號的學生'>;	--錯誤處理
	end pro_show;

執行儲存過程。

exec pro_show<10>

4、建立一個儲存過程，向資料表student中插入一條記錄。

create or replace procedure pro_add<
	var_id in number,
	var_name in varchar2,
	var_class in varchar2> is
begin
	insert into student values<var_id,var_name,var_class>;	--插入記錄
	commit;
	dbms_output.put_line<'插入一條新紀錄！！！'>;
end pro_add;

執行儲存過程

exec pro_add<10,'張三','五班'>;

5、輸入一個編號，查詢student表中是否有這個編號，如果有則返回對應學生的姓名，如果沒有則提示沒有對應的學生。

上面我們使用in是顯示學生的姓名，現在我們要返回學生的姓名就要使用out，語法如下

create or replace procedure pro_show1<
	var_id in student.id%type,	--定義in引數
	var_name out student.name%type	--定義out引數
> is
	no_result exception;
begin
	select name init var_name from student where id = var_id;	--取值
exception
	when no_data_found then
		dbms_output.put_line<'沒有對應此編號的學生'>;	--錯誤處理
end pro_show1;

呼叫含有out引數的儲存過程需要提前宣告一個相應型別的變數，然後用來接收。

variable var_name varchar2<10>;
exec pro_show1<10,:var_name>;

在呼叫的時候，使用“：”後面緊跟變數名。

4、儲存過程的查詢

儲存過程的查詢需要使用到資料字典user_source,語法如下

select distinct name from user_source where type=upper('procedure');

上面這個語句查詢當前使用者下所有的儲存過程的名字。

此外，我們還可以查詢儲存過程的內容，查詢語句如下所示。

select text from user_source where name = upper('pro_aa');

二、函數

上面的儲存過程有輸入引數和輸出引數，但是沒有返回值，函數和儲存過程非常類似，也是可以儲存在oracle資料庫中的PL/SQL程式碼塊，但是有返回值，可以把經常使用的功能定義為一個函數，就像系統自帶的函數（例如大小寫轉換，求絕對值等函數）一樣使用。

1、函數的建立

函數的建立的基本語法格式如下所示。

create or replace function 函數名
[<引數1> inioutin out <引數型別>[預設值|:=初始值]]
return 返回資料型別
isias
[區域性變數宣告]
begin
程式語句序列
[exception]
例外處理語句序列
end 過程名

其中的引數說明如下。

1、or replace 可選引數，表示如果資料庫中已經存在要建立的函數，則先把原先函數刪除，再重新建立函數，或者說覆蓋原先的函數。

3、引數型別不能指定長度，只需要給出型別即可。

4、函數的返回值型別是必選項。

5、區域性變數宣告中所定義的變數只在該函數中有效。

6、區域性變數宣告、程式語句序列和例外處理語句序列定義以及使用PL/SQL塊。

在函數的主程式中，必須使用return語句返回最終的函數值，並且返回值的資料型別要和宣告的時候說明的型別一樣。

## 2、隱式遊標的建立與使用
>和顯示遊標不同，隱式遊標是系統自動建立的，用於處理DML語句（例如insert、update、delete等指令）的執行結果或者select查詢返回的單行資料，這時隱式遊標是指向緩衝區的指標。使用時不需要進行宣告、開啟和關閉，因此不需要open、fetch、close這樣的操作指令。隱式遊標也有前述介紹的4種屬性，使用時需要在屬性前面加上隱式遊標的預設名稱SQL，因此隱式遊標也叫SQL遊標。

###  1、將student表中張三的學生年齡增加10歲，然後使用隱式遊標的%rowcount屬性輸出涉及的員工數量
```go
begin
	update student set age=age+10	--年齡增加10
	where name = '張三';
	if sql%notfound then	--是否有符合條件的記錄
		dbms_output.put_line<'沒有符合條件的學生'>;
	else
		dbms_output.put_line<'符合條件的學生數量為：' || sql%rowcount>;
	end if;
end;

2、函數的呼叫與刪除

函數的呼叫基本上與系統內建函數的呼叫方法相同。可以直接在SQL plus中使用，也可以在儲存過程中使用。

函數的刪除與儲存過程的刪除類似，語法如下：

drop function 函數名

3、函數的使用

1、建立一個函數，如果是偶數則計算其平方，如果是奇數則計算其平方根

create or replace function fun_cal
	<var_num number>	--宣告函數引數
	return number		--宣告函數返回型別
is
i int:=2;
begin
	if mod<var_num,2>=0 then	--判斷奇偶性
		return power<var_num,i>;	--返回平方
	flse
		return round<sqrt<var_num>,2>;	--返回平方根
	end if;
end fun_cal;

4、函數的查詢

在實際使用中經常會需要查詢資料庫中已有的函數或者某一個函數的內容，使用的方法和儲存過程類似，也需要使用到資料字典user_source，使用的查詢語句如下所示。

select distinct name from user_source where type=upper('function');

上面這個語句查詢當前使用者下所有的使用者定義的函數名字。

此外，我們還可以查詢函數的內容，查詢語句如下所示。

select text from user_source where name=upper('fun_cal') and type=upper('function')

補充：儲存過程與儲存函數的區別和聯絡

相同點：1.建立語法結構相似，都可以攜帶多個傳入引數和傳出引數。

　　　　2.都是一次編譯，多次執行。

不同點：1.儲存過程定義關鍵字用procedure，函數定義用function。

　　　　2.儲存過程中不能用return返回值，但函數中可以，而且函數中必須有return子句。

　　　　3.執行方式略有不同，儲存過程的執行方式有兩種（1.使用execute2.使用begin和end），函數除了儲存過程的兩種方式外，還可以當做表示式使用，例如放在select中（select f1() form dual;）。

總結：如果只有一個返回值，用儲存函數，否則，一般用儲存過程。

總結

這裡的相關內容還沒有整理完畢，文章後面持續更新，建議收藏。

文章中涉及到的命令大家一定要像我一樣每個都敲幾遍，只有在敲的過程中才能發現自己對命令是否真正的掌握了。

到此這篇關於Oracle儲存過程與函數的詳細使用的文章就介紹到這了,更多相關Oracle儲存過程與函數內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！