文章詳情頁

Oracle存儲過程與函數(shù)的詳細(xì)使用教程

瀏覽：181日期：2023-03-12 15:25:22

一、存儲過程

存儲過程是一種命名的PL/SQL數(shù)據(jù)塊，存儲在Oracle數(shù)據(jù)庫中，可以被用戶調(diào)用。存儲過程可以包含參數(shù)，也可以沒有參數(shù)，它一般沒有返回值。存儲過程是事先編譯好的代碼，再次調(diào)用的時候不需再次編譯，因此程序的運(yùn)行效率非常高。

1、存儲過程的創(chuàng)建

語法如下

create [or replace] 過程名
[<參數(shù)1> inioutin out <參數(shù)類型>[默認(rèn)值|:=初始值]]
[,<參數(shù)2> inioutin out <參數(shù)類型>[默認(rèn)值|:=初始值],...]
isias
[局部變量聲明]
begin
程序語句序列
[exception]
異常處理語句序列
end 過程名

參數(shù)說明如下：

1、or replace 可選參數(shù)，表示如果數(shù)據(jù)庫中已經(jīng)存在要創(chuàng)建的過程，則先把原先過程刪除，再重新建立過程，或者說覆蓋原先的過程。

2、如果過程中存在參數(shù)，則需要在參數(shù)后面用“inioutin out”關(guān)鍵字。如果是輸入?yún)?shù)，則參數(shù)后面用“in”關(guān)鍵字，表示接受外部過程傳遞來的值；如果是輸出參數(shù)，則參數(shù)后面用“out”關(guān)鍵字，表示此參數(shù)將在過程中被復(fù)制，并傳遞給過程體外；如果是“in out” 關(guān)鍵字則表示該參數(shù)既具有輸入?yún)?shù)特性，又具有輸出參數(shù)的特性。默認(rèn)是in參數(shù)，即如果不寫就默認(rèn)為in參數(shù)。

3、參數(shù)類型不能指定長度，只需要給出類型即可。

4、局部變量聲明中所定義的變量只在該過程中有效。

5、局部變量聲明，程序語句序列和異常處理語句序列定義和使用同上一章PL/SQL塊。

2、存儲過程的調(diào)用及刪除

存儲過程創(chuàng)建后，以編譯的形式存在于oracle數(shù)據(jù)庫中，可以在sql plus中或者pl/sql塊中調(diào)用。

1、在sql plus中調(diào)用存儲過程

語法如下：

execute 過程名 [參數(shù)序列]

其中execute可以簡寫成exec。

2、在pl/sql塊中調(diào)用存儲過程

直接把過程名寫到其他pl/sql塊中即可調(diào)用，此時不需使用execute命令。

3、存儲過程的刪除

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

drop procdure 過程名

3、存儲過程的使用

1、不帶參數(shù)的存儲過程

1、創(chuàng)建一個存儲過程，向student表中插入一條記錄

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

上面存儲過程已經(jīng)成功創(chuàng)建，但是并沒有執(zhí)行，執(zhí)行語句如下。

exec pro_stu;

上面是exec命令執(zhí)行，我們也可以在PL/SQL塊中直接調(diào)用，語法如下。

begin
	pro_stu;
end;

2、帶in參數(shù)的存儲過程
使用in參數(shù)可以向存儲過程中的程序單元輸入數(shù)據(jù)，在調(diào)用的時候提供參數(shù)值，被存儲過程讀取。這種模式是默認(rèn)的參數(shù)模式。下面看一個范例。

2、創(chuàng)建一個存儲過程，接收來自外部的數(shù)值，在存儲過程中判斷該數(shù)值是否大于零并顯示。

create or replace procedure pro_decide<
	var_num in number
> is
begin
	if var_num>=0 then
		dbms_output.put_line<"傳遞進(jìn)來的參數(shù)大于等于0">;
	else
		dbms_output.put_line<"傳遞進(jìn)來的參數(shù)小于0">;
	end if;
end pro_decide;

執(zhí)行存儲過程

exec pro_decide<3>;

結(jié)果顯示：
傳遞進(jìn)來的參數(shù)大于等于0

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

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

執(zhí)行存儲過程。

exec pro_show<10>

4、創(chuàng)建一個存儲過程，向數(shù)據(jù)表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<"插入一條新紀(jì)錄?。?！">;
end pro_add;

執(zhí)行存儲過程

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

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

上面我們使用in是顯示學(xué)生的姓名，現(xiàn)在我們要返回學(xué)生的姓名就要使用out，語法如下

create or replace procedure pro_show1<
	var_id in student.id%type,	--定義in參數(shù)
	var_name out student.name%type	--定義out參數(shù)
> 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<"沒有對應(yīng)此編號的學(xué)生">;	--錯誤處理
end pro_show1;

調(diào)用含有out參數(shù)的存儲過程需要提前聲明一個相應(yīng)類型的變量，然后用來接收。

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

在調(diào)用的時候，使用“：”后面緊跟變量名。

4、存儲過程的查詢

存儲過程的查詢需要使用到數(shù)據(jù)字典user_source,語法如下

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

上面這個語句查詢當(dāng)前用戶下所有的存儲過程的名字。

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

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

二、函數(shù)

上面的存儲過程有輸入?yún)?shù)和輸出參數(shù)，但是沒有返回值，函數(shù)和存儲過程非常類似，也是可以存儲在oracle數(shù)據(jù)庫中的PL/SQL代碼塊，但是有返回值，可以把經(jīng)常使用的功能定義為一個函數(shù)，就像系統(tǒng)自帶的函數(shù)（例如大小寫轉(zhuǎn)換，求絕對值等函數(shù)）一樣使用。

1、函數(shù)的創(chuàng)建

函數(shù)的創(chuàng)建的基本語法格式如下所示。

create or replace function 函數(shù)名
[<參數(shù)1> inioutin out <參數(shù)類型>[默認(rèn)值|:=初始值]]
return 返回數(shù)據(jù)類型
isias
[局部變量聲明]
begin
程序語句序列
[exception]
異常處理語句序列
end 過程名

其中的參數(shù)說明如下。

1、or replace 可選參數(shù)，表示如果數(shù)據(jù)庫中已經(jīng)存在要創(chuàng)建的函數(shù)，則先把原先函數(shù)刪除，再重新建立函數(shù)，或者說覆蓋原先的函數(shù)。

3、參數(shù)類型不能指定長度，只需要給出類型即可。

4、函數(shù)的返回值類型是必選項。

5、局部變量聲明中所定義的變量只在該函數(shù)中有效。

6、局部變量聲明、程序語句序列和異常處理語句序列定義以及使用PL/SQL塊。

在函數(shù)的主程序中，必須使用return語句返回最終的函數(shù)值，并且返回值的數(shù)據(jù)類型要和聲明的時候說明的類型一樣。

## 2、隱式游標(biāo)的創(chuàng)建與使用
>和顯示游標(biāo)不同，隱式游標(biāo)是系統(tǒng)自動創(chuàng)建的，用于處理DML語句（例如insert、update、delete等指令）的執(zhí)行結(jié)果或者select查詢返回的單行數(shù)據(jù)，這時隱式游標(biāo)是指向緩沖區(qū)的指針。使用時不需要進(jìn)行聲明、打開和關(guān)閉，因此不需要open、fetch、close這樣的操作指令。隱式游標(biāo)也有前述介紹的4種屬性，使用時需要在屬性前面加上隱式游標(biāo)的默認(rèn)名稱SQL，因此隱式游標(biāo)也叫SQL游標(biāo)。

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

2、函數(shù)的調(diào)用與刪除

函數(shù)的調(diào)用基本上與系統(tǒng)內(nèi)置函數(shù)的調(diào)用方法相同。可以直接在SQL plus中使用，也可以在存儲過程中使用。

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

drop function 函數(shù)名

3、函數(shù)的使用

1、創(chuàng)建一個函數(shù)，如果是偶數(shù)則計算其平方，如果是奇數(shù)則計算其平方根

create or replace function fun_cal
	<var_num number>	--聲明函數(shù)參數(shù)
	return number		--聲明函數(shù)返回類型
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、函數(shù)的查詢

在實(shí)際使用中經(jīng)常會需要查詢數(shù)據(jù)庫中已有的函數(shù)或者某一個函數(shù)的內(nèi)容，使用的方法和存儲過程類似，也需要使用到數(shù)據(jù)字典user_source，使用的查詢語句如下所示。

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

上面這個語句查詢當(dāng)前用戶下所有的用戶定義的函數(shù)名字。

此外，我們還可以查詢函數(shù)的內(nèi)容，查詢語句如下所示。

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

補(bǔ)充：存儲過程與存儲函數(shù)的區(qū)別和聯(lián)系

相同點(diǎn)：1.創(chuàng)建語法結(jié)構(gòu)相似，都可以攜帶多個傳入?yún)?shù)和傳出參數(shù)。

　　　　2.都是一次編譯，多次執(zhí)行。

不同點(diǎn)：1.存儲過程定義關(guān)鍵字用procedure，函數(shù)定義用function。

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

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

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

總結(jié)

這里的相關(guān)內(nèi)容還沒有整理完畢，文章后面持續(xù)更新，建議收藏。

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

到此這篇關(guān)于Oracle存儲過程與函數(shù)的詳細(xì)使用的文章就介紹到這了,更多相關(guān)Oracle存儲過程與函數(shù)內(nèi)容請搜索以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持！

Oracle

上一條：Oracle數(shù)據(jù)泵實(shí)現(xiàn)不同用戶導(dǎo)入導(dǎo)出表級下一條：ORACLE正則匹配查詢LIKE查詢多個值檢索數(shù)據(jù)庫對象

排行榜

					
					MySQL中 concat函數(shù)的使用
傳甲骨文將增加對MySQL投資與微軟競爭
用三個方法設(shè)置Oracle數(shù)據(jù)庫穿越防火墻
Oracle 11g透明數(shù)據(jù)加密安全特性解析
MySQL CHAR和VARCHAR該如何選擇
MySQL全文索引、聯(lián)合索引、like查詢、json查詢速度哪個快
MySQL 性能、監(jiān)控與災(zāi)難恢復(fù)
認(rèn)識那些影響Oracle系統(tǒng)性能初始化參數(shù)
MySQL刪除數(shù)據(jù)，表文件大小依然沒變的原因
MYSQL(電話號碼,身份證)數(shù)據(jù)脫敏的實(shí)現(xiàn)
MySql導(dǎo)出后再導(dǎo)入數(shù)據(jù)時出錯問題
				