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

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

1、存儲(chǔ)過(guò)程的創(chuàng)建

語(yǔ)法如下

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

參數(shù)說(shuō)明如下：

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

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

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

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

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

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

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

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

語(yǔ)法如下：

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

其中execute可以簡(jiǎn)寫(xiě)成exec。

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

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

3、存儲(chǔ)過(guò)程的刪除

存儲(chǔ)過(guò)程的刪除和表的刪除類似，基本語(yǔ)法如下所示。

drop procdure 過(guò)程名

3、存儲(chǔ)過(guò)程的使用

1、不帶參數(shù)的存儲(chǔ)過(guò)程

1、創(chuàng)建一個(gè)存儲(chǔ)過(guò)程，向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;

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

exec pro_stu;

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

begin
	pro_stu;
end;

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

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

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

執(zhí)行存儲(chǔ)過(guò)程

exec pro_decide<3>;

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

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

create or replace procedure pro_show<
	var_stuid in student.id%type	--定義in參數(shù)
> is
	var_name student.name%type;	--定義存儲(chǔ)過(guò)程內(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<'沒(méi)有對(duì)應(yīng)此編號(hào)的學(xué)生'>;	--錯(cuò)誤處理
	end pro_show;

執(zhí)行存儲(chǔ)過(guò)程。

exec pro_show<10>

4、創(chuàng)建一個(gè)存儲(chǔ)過(guò)程，向數(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í)行存儲(chǔ)過(guò)程

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

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

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

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<'沒(méi)有對(duì)應(yīng)此編號(hào)的學(xué)生'>;	--錯(cuò)誤處理
end pro_show1;

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

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

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

4、存儲(chǔ)過(guò)程的查詢

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

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

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

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

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

二、函數(shù)

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

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

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

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

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

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

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

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

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

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

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

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

## 2、隱式游標(biāo)的創(chuàng)建與使用
>和顯示游標(biāo)不同，隱式游標(biāo)是系統(tǒng)自動(dòng)創(chuàng)建的，用于處理DML語(yǔ)句（例如insert、update、delete等指令）的執(zhí)行結(jié)果或者select查詢返回的單行數(shù)據(jù)，這時(shí)隱式游標(biāo)是指向緩沖區(qū)的指針。使用時(shí)不需要進(jìn)行聲明、打開(kāi)和關(guān)閉，因此不需要open、fetch、close這樣的操作指令。隱式游標(biāo)也有前述介紹的4種屬性，使用時(shí)需要在屬性前面加上隱式游標(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<'沒(méi)有符合條件的學(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)用方法相同?？梢灾苯釉赟QL plus中使用，也可以在存儲(chǔ)過(guò)程中使用。

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

drop function 函數(shù)名

3、函數(shù)的使用

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

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)常會(huì)需要查詢數(shù)據(jù)庫(kù)中已有的函數(shù)或者某一個(gè)函數(shù)的內(nèi)容，使用的方法和存儲(chǔ)過(guò)程類似，也需要使用到數(shù)據(jù)字典user_source，使用的查詢語(yǔ)句如下所示。

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

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

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

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

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

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

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

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

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

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

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

總結(jié)

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

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

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

最新評(píng)論