Oracle存儲(chǔ)過(guò)程總結(jié)
1.存儲(chǔ)過(guò)程結(jié)構(gòu)
1.1 第一個(gè)存儲(chǔ)過(guò)程
create or replace procedure proc1(
para1 varchar2,
para2 out varchar2,
para3 in out varchar2
) as
v_name varchar2(20);
begin
v_name :='zhangsf';
para3 := v_name;
dbms_output.put_line('para3:'||para3);
end;
上面就是一個(gè)最簡(jiǎn)單的存儲(chǔ)過(guò)程文兢。一個(gè)存儲(chǔ)過(guò)程大體分為這么幾個(gè)部分:
創(chuàng)建語(yǔ)句：create or replace procedure 存儲(chǔ)過(guò)程名
如果沒(méi)有or replace語(yǔ)句己肮，則僅僅是新建一個(gè)存儲(chǔ)過(guò)程。如果系統(tǒng)存在該存儲(chǔ)過(guò)程慎璧，則會(huì)報(bào)錯(cuò)耘成。Create or replace procedure 如果系統(tǒng)中沒(méi)有此存儲(chǔ)過(guò)程就新建一個(gè)耙册，如果系統(tǒng)中有此存儲(chǔ)過(guò)程則把原來(lái)刪除掉荧飞，重新創(chuàng)建一個(gè)存儲(chǔ)過(guò)程掌呜。
存儲(chǔ)過(guò)程名定義：包括存儲(chǔ)過(guò)程名和參數(shù)列表滓玖。參數(shù)名和參數(shù)類型。參數(shù)名不能重復(fù)质蕉， 參數(shù)傳遞方式:IN, OUT, IN OUT
IN 表示輸入?yún)?shù)势篡，按值傳遞方式。
OUT 表示輸出參數(shù)模暗，可以理解為按引用傳遞方式禁悠。可以作為存儲(chǔ)過(guò)程的輸出結(jié)果兑宇，供外部調(diào)用者使用碍侦。
IN OUT 即可作輸入?yún)?shù)，也可作輸出參數(shù)隶糕。
參數(shù)的數(shù)據(jù)類型只需要指明類型名即可瓷产，不需要指定寬度。
參數(shù)的寬度由外部調(diào)用者決定枚驻。
過(guò)程可以有參數(shù)濒旦，也可以沒(méi)有參數(shù)
變量聲明塊：緊跟著的as (is )關(guān)鍵字，可以理解為pl/sql的declare關(guān)鍵字再登，用于聲明變量尔邓。
變量聲明塊用于聲明該存儲(chǔ)過(guò)程需要用到的變量，它的作用域?yàn)樵摯鎯?chǔ)過(guò)程霎冯。另外這里聲明的變量必須指定寬度铃拇。遵循PL/SQL的變量聲明規(guī)范。
過(guò)程語(yǔ)句塊：從begin 關(guān)鍵字開(kāi)始為過(guò)程的語(yǔ)句塊沈撞。存儲(chǔ)過(guò)程的具體邏輯在這里來(lái)實(shí)現(xiàn)慷荔。
異常處理塊：關(guān)鍵字為exception ，為處理語(yǔ)句產(chǎn)生的異常缠俺。該部分為可選
結(jié)束塊：由end關(guān)鍵字結(jié)果显晶。
1.2 存儲(chǔ)過(guò)程的參數(shù)傳遞方式
存儲(chǔ)過(guò)程的參數(shù)傳遞有三種方式:IN,OUT,IN OUT .
IN 按值傳遞，并且它不允許在存儲(chǔ)過(guò)程中被重新賦值壹士。如果存儲(chǔ)過(guò)程的參數(shù)沒(méi)有指定存參數(shù)傳遞類型磷雇，默認(rèn)為IN
create or replace procedure proc1(
para1 varchar2,
para2 out varchar2,
para3 in out varchar2
) as
v_name varchar2(20);
begin
para1 := 'aaa';
para2 := 'bbb';
v_name :='zhangsf';
para3 := v_name
dbms_output.put_line('para3:'||para3);
null;
end;
OUT 參數(shù)：作為輸出參數(shù)，需要注意躏救，當(dāng)一個(gè)參數(shù)被指定為OUT類型時(shí)唯笙，就算在調(diào)用存儲(chǔ)過(guò)程之前對(duì)該參數(shù)進(jìn)行了賦值螟蒸，在存儲(chǔ)過(guò)程中該參數(shù)的值仍然是null.
首先，我們要明白崩掘，我們無(wú)法在存儲(chǔ)過(guò)程的定義中指定存儲(chǔ)參數(shù)的寬度七嫌，也就導(dǎo)致了我們無(wú)法在存儲(chǔ)過(guò)程中控制傳入變量的寬度。這個(gè)寬度是完全由外部傳入時(shí)決定的苞慢。
我們?cè)賮?lái)看看OUT類型的參數(shù)的寬度诵原。
create or replace procedure proc2(
para1 varchar2,
para2 out varchar2,
para3 in out varchar2
) as
v_name varchar2(2);
begin
para2 :='aaaaaaaaaaaaaaaaaaaa';
end;
--調(diào)用proc2
var p1 varchar2(1);
var p2 varchar2(1);
var p3 varchar2(1);
exec :p2 := 'a';
exec proc1(:p1,:p2,:p3);
在該過(guò)程中，p_para2被賦予了20個(gè)字符a.
而在外部的調(diào)用過(guò)程中挽放，p2這個(gè)參數(shù)僅僅被定義為varchar2(1).
而把p2作為參數(shù)調(diào)用這個(gè)過(guò)程绍赛，卻并沒(méi)有報(bào)錯(cuò)。而且它的真實(shí)值就是20個(gè)a
執(zhí)行這個(gè)過(guò)程辑畦，仍然正確執(zhí)行吗蚌。
可見(jiàn)，對(duì)于IN參數(shù)航闺，其寬度是由外部決定褪测。
對(duì)于OUT 和IN OUT 參數(shù)，其寬度是由存儲(chǔ)過(guò)程內(nèi)部決定潦刃。
因此侮措，在寫(xiě)存儲(chǔ)過(guò)程時(shí)，對(duì)參數(shù)的寬度進(jìn)行說(shuō)明是非常有必要的乖杠，最明智的方法就是參數(shù)的數(shù)據(jù)類型使用%type分扎。這樣雙方就達(dá)成了一致。
1.3 參數(shù)的默認(rèn)值
存儲(chǔ)過(guò)程的參數(shù)可以設(shè)置默認(rèn)值
create or replace procedure procdefault(
p1 varchar2,
p2 varchar2 default 'mark'
) as
begin
dbms_output.put_line(p2);
end;
mark
可以通過(guò)default 關(guān)鍵字為存儲(chǔ)過(guò)程的參數(shù)指定默認(rèn)值胧洒。在對(duì)存儲(chǔ)過(guò)程調(diào)用時(shí)畏吓，就可以省略默認(rèn)值。
需要注意的是：默認(rèn)值僅僅支持IN傳輸類型的參數(shù)卫漫。OUT 和 IN OUT不能指定默認(rèn)值
對(duì)于有默認(rèn)值的參數(shù)不是排在最后的情況菲饼。
create or replace procedure procdefault2(
p1 varchar2 default 'remark',
p2 varchar2
) as
begin
dbms_output.put_line(p1);
end;
第一個(gè)參數(shù)有默認(rèn)值，第二個(gè)參數(shù)沒(méi)有列赎。如果我們想使用第一個(gè)參數(shù)的默認(rèn)值時(shí)
exec procdefault2('aa');
這樣是會(huì)報(bào)錯(cuò)的宏悦。
那怎么變呢？可以指定參數(shù)的值包吝。
SQL> exec procdefault2(p2 =>'aa');
remark
這樣就OK了饼煞，指定aa傳給參數(shù)p2
2. 存儲(chǔ)過(guò)程內(nèi)部塊
2.1 內(nèi)部塊
我們知道了存儲(chǔ)過(guò)程的結(jié)構(gòu)，語(yǔ)句塊由begin開(kāi)始诗越，以end結(jié)束砖瞧。這些塊是可以嵌套。在語(yǔ)句塊中可以嵌套任何以下的塊嚷狞。
Declare .. beign ... exception ... end;
create or replace procedure innerBlock(
p1 varchar2
) as
o1 varchar2(10) := 'out1';
begin
dbms_output.put_line(o1);
declare
inner1 varchar2(20);
begin
inner1 := 'inner1';
dbms_output.put_line(inner1);
declare
inner2 varchar2(20);
begin
inner2 := 'inner2';
dbms_output.put_line(inner2);
end;
exception
when others then
null;
end;
end;
需要注意變量的作用域块促。
3.存儲(chǔ)過(guò)程的常用技巧
3.1 哪種集合?
我們?cè)谑褂么鎯?chǔ)過(guò)程的時(shí)候經(jīng)常需要處理記錄集荣堰，也就是多條數(shù)據(jù)記錄。分為單列多行和多列多行褂乍，這些類型都可以稱為集合類型持隧。我們?cè)谶@里進(jìn)行比較這些集合類型，以便于在編程時(shí)做出正確的選擇逃片。
索引表,也稱為pl/sql表，不能存儲(chǔ)于數(shù)據(jù)庫(kù)中只酥，元素的個(gè)數(shù)沒(méi)有限制,下標(biāo)可以為負(fù)值褥实。
3.2 選用何種游標(biāo)?
顯示游標(biāo)分為：普通游標(biāo)，參數(shù)化游標(biāo)和游標(biāo)變量三種裂允。
下面以一個(gè)過(guò)程來(lái)進(jìn)行說(shuō)明
create or replace procedure proccursor(
p varchar2
) as
v_rownum number(10) := 1;
cursor c_postype is select pos_type from pos_type_tb1 where rownum = 1;
cursor c_postype1 is select pos_type from pos_type_tb1 where rownum = v_rownum;
cursor c_postype2(p_rownum number) is select pos_type from pos_type_tb1 where rownum = p_rownum;
type t_postype is ref cursor;
c_postype3 t_postype;
v_postype varchar2(20);
begin
open c_postype;
fetch c_postype into v_postype;
dbms_output.put_line(v_postype);
close c_postype;
open c_postype1;
fetch c_postype1 into v_postype;
dbms_output.put_line(v_postype);
close c_postype1;
open c_postype2(1);
fetch c_postype2 into v_postype;
dbms_output.put_line(v_postype);
close c_postype2;
open c_postype3 for select post_type from pos_type_tb1 where rownum=1;
fetch c_postype3 into v_postype;
dbms_output.put_line(v_postype);
close c_postype3
end;
cursor c_postype is select pos_type from pos_type_tbl where rownum =1
這一句是定義了一個(gè)最普通的游標(biāo)损离，把整個(gè)查詢已經(jīng)寫(xiě)死,調(diào)用時(shí)不可以作任何改變。
cursor c_postype1 is select pos_type from pos_type_tbl where rownum = v_rownum;
這一句并沒(méi)有寫(xiě)死绝编，查詢參數(shù)由變量v_rownum來(lái)決定僻澎。需要注意的是v_rownum必須在這個(gè)游標(biāo)定義之前聲明。
cursor c_postype2(p_rownum number) is select pos_type from pos_type_tbl where rownum = p_rownum;
這一條語(yǔ)句與第二條作用相似十饥，都是可以為游標(biāo)實(shí)現(xiàn)動(dòng)態(tài)的查詢窟勃。但是它進(jìn)一步的縮小了參數(shù)的作用域范圍。但是可讀性降低了不少逗堵。
type t_postype is ref cursor ;
c_postype3 t_postype;
先定義了一個(gè)引用游標(biāo)類型秉氧，然后再聲明了一個(gè)游標(biāo)變量。
open c_postype3 for select pos_type from pos_type_tbl where rownum =1;
然后再用open for 來(lái)打開(kāi)一個(gè)查詢蜒秤。需要注意的是它可以多次使用,用來(lái)打開(kāi)不同的查詢汁咏。
從動(dòng)態(tài)性來(lái)說(shuō)，游標(biāo)變量是最好用的作媚，但是閱讀性也是最差的攘滩。
注意，游標(biāo)的定義只能用使關(guān)鍵字IS纸泡，它與AS不通用漂问。
3.3 游標(biāo)循環(huán)最佳策略
我們?cè)谶M(jìn)行PL/SQL編程時(shí)，經(jīng)常需要循環(huán)讀取結(jié)果集的數(shù)據(jù)弟灼。進(jìn)行逐行處理级解，這個(gè)過(guò)程就需要對(duì)游標(biāo)進(jìn)行循環(huán)。對(duì)游標(biāo)進(jìn)行循環(huán)的方法有多種田绑，我們?cè)诖艘灰环治觥?/p>
create or replace procedure proccycle(
p varchar2
) as
cursor c_postype is select pos_type,description from pos_type_tb1 where rownum < 6;
v_postype varchar2(20);
v_description varchar2(50);
begin
open c_postype;
if c_postype%found then
dbms_output.put_line('found true');
elseif c_postype%found = false then
dbms_output.put_line('found false');
else
dbms_output.put_line('found null');
end if;
loop
fetch c_postype into v_postype,v_description;
exit when c_postype%notfound;
dbms_output.put_line('postype:'||v_postype||',description:'||v_description);
end loop;
close c_postype;
dbms_output.put_line('---loop end---');
open c_postype;
fetch c_postype into v_postype,v_description;
while c_postype%found loop
dbms_output.put_line('postype:'||v_postype||',description:'||v_description);
fetch c_postype into v_postype,v_description;
end loop;
close c_postype;
dbms_output.put_line('---while end---');
for v_pos in c_postype loop
v_postype := v_pos.pos_type;
v_description := v_pos.description;
dbms_output.put_line('postype:'||v_postype||',description:'||v_description);
end loop;
dbms_output.put_line('---for end---');
end;
使用游標(biāo)之前需要開(kāi)打游標(biāo),open cursor勤哗，循環(huán)完后再關(guān)閉游標(biāo)close cursor.
這是使用游標(biāo)應(yīng)該慎記于心的法則。
上面的過(guò)程演示了游標(biāo)循環(huán)的三種方法掩驱。
在討論循環(huán)方法之前芒划，我們先看看%found和%notfound這些游標(biāo)的屬性冬竟。
open c_postype;
if c_postype%found then
dbms_output.put_line('found true');
elseif c_postype%found = false then
dbms_output.put_line('found false');
else
dbms_output.put_line('found null');
end if;
在打開(kāi)一個(gè)游標(biāo)之后，馬上檢查它的%found或%notfound屬性民逼，它得到的結(jié)果即不是true也不是false.而是null.必須執(zhí)行一條fetch語(yǔ)句后泵殴，這些屬性才有值。
第一種使用loop 循環(huán)
loop
fetch c_postype into v_postype,v_description;
exit when c_postype%notfound;
...
end loop;
這里需要注意拼苍，exit when語(yǔ)句一定要緊跟在fetch之后笑诅。必避免多余的數(shù)據(jù)處理。
處理邏輯需要跟在exit when之后疮鲫。這一點(diǎn)需要多加小心吆你。
循環(huán)結(jié)束后要記得關(guān)閉游標(biāo)
第二種使用while循環(huán)。
fetch c_postype into v_postype,v_description;
while c_postype%found loop
...
fetch c_postype into v_postype,v_description;
end loo;
我們知道了一個(gè)游標(biāo)打開(kāi)后俊犯，必須執(zhí)行一次fetch語(yǔ)句妇多，游標(biāo)的屬性才會(huì)起作用。所以使用while 循環(huán)時(shí)燕侠，就需要在循環(huán)之前進(jìn)行一次fetch動(dòng)作者祖。
而且數(shù)據(jù)處理動(dòng)作必須放在循環(huán)體內(nèi)的fetch方法之前。循環(huán)體內(nèi)的fetch方法要放在最后绢彤。否則就會(huì)多處理一次七问。這一點(diǎn)也要非常的小心。
總之杖虾，使用while來(lái)循環(huán)處理游標(biāo)是最復(fù)雜的方法烂瘫。
第三種 for循環(huán)
for v_pos in c_postype loop
v_postype := v_pos.pos_type;
v_description := v_pos.description;
...
end loop;
可見(jiàn)for循環(huán)是比較簡(jiǎn)單實(shí)用的方法。
首先奇适，它會(huì)自動(dòng)open和close游標(biāo)坟比。解決了你忘記打開(kāi)或關(guān)閉游標(biāo)的煩惱。
其它,自動(dòng)定義了一個(gè)記錄類型及聲明該類型的變量嚷往，并自動(dòng)fetch數(shù)據(jù)到這個(gè)變量中葛账。
我們需要注意v_pos 這個(gè)變量無(wú)需要在循環(huán)外進(jìn)行聲明，無(wú)需要為其指定數(shù)據(jù)類型皮仁。
它應(yīng)該是一個(gè)記錄類型籍琳，具體的結(jié)構(gòu)是由游標(biāo)決定的。
這個(gè)變量的作用域僅僅是在循環(huán)體內(nèi)贷祈。
把v_pos看作一個(gè)記錄變量就可以了趋急，如果要獲得某一個(gè)值就像調(diào)用記錄一樣就可以了。
如v_pos.pos_type
由此可見(jiàn)势誊，for循環(huán)是用來(lái)循環(huán)游標(biāo)的最好方法呜达。高效，簡(jiǎn)潔粟耻，安全查近。
但遺憾的是眉踱，常常見(jiàn)到的卻是第一種方法。所以從今之后得改變這個(gè)習(xí)慣了霜威。
3.4 select into不可乎視的問(wèn)題
我們知道在pl/sql中要想從數(shù)據(jù)表中向變量賦值谈喳，需要使用select into 子句。
但是它會(huì)帶動(dòng)來(lái)一些問(wèn)題戈泼，如果查詢沒(méi)有記錄時(shí)婿禽，會(huì)拋出no_data_found異常。
如果有多條記錄時(shí)大猛，會(huì)拋出too_many_rows異常谈宛。
這個(gè)是比較糟糕的。一旦拋出了異常胎署，就會(huì)讓過(guò)程中斷。特別是no_data_found這種異常窑滞，沒(méi)有嚴(yán)重到要讓程序中斷的地步琼牧，可以完全交給由程序進(jìn)行處理。
create or replace procedure procexception(
p varchar2
) as
v_postype varchar2(20);
begin
select pos_type into v_postype from pos_type_tb1 where 1=0;--絕壁為空,報(bào)no_data_found的錯(cuò)誤
dbms_output.put_line(v_postype);
end;
處理這個(gè)有三個(gè)辦法
1． 直接加上異常處理哀卫。
create or replace procedure procexception(
p varchar2
) as
v_postype varchar2(20);
begin
select pos_type into v_postype from pos_type_tb1 where 1=0;--絕壁為空
dbms_output.put_line(v_postype);
--解決辦法1:增加異常處理模塊
exception
when no_data_found then
dbms_output.put_line("未找到數(shù)據(jù)");
end;
這樣做換湯不換藥巨坊，程序仍然被中斷〈烁模可能這樣不是我們所想要的趾撵。
- select into做為一個(gè)獨(dú)立的塊，在這個(gè)塊中進(jìn)行異常處理
create or replace procedure procexception(
p varchar2
) as
v_postype varchar2(20);
begin
select pos_type into v_postype from pos_type_tb1 where 1=0;--絕壁為空
dbms_output.put_line(v_postype);
--其實(shí)吧,我覺(jué)得和上面那種差不多,原理一樣的呀,no zuo nod ie
exception
when no_data_found then
v_postype := '';
end;
dbms_output.put_line(v_postype);
end;
這是一種比較好的處理方式了共啃。不會(huì)因?yàn)檫@個(gè)異常而引起程序中斷占调。
3.使用游標(biāo)
create or replace procedure proceexception(
p varchar2
) as
v_postype varchar2(20);
cursor c_postype is select pos_type from pos_type_tb1 where 1=0;
beign
open c_postype;
fetch c_postype into v_postype;
close c_postype;
dbms_output.put_line(v_postype);
end;
--個(gè)人覺(jué)得這樣先進(jìn)行非空判斷會(huì)更好
create or replace procedure proceexception(
p varchar2
) as
v_postype varchar2(20);
cursor c_postype is select pos_type from pos_type_tb1 where 1=0;
beign
if c_postype%found then
open c_postype;
fetch c_postype into v_postype;
close c_postype;
dbms_output.put_line(v_postype);
end if;
end;
這樣就完全的避免了no_data_found異常。完全交由程序員來(lái)進(jìn)行控制了移剪。
第二種情況是too_many_rows 異常的問(wèn)題究珊。
Too_many_rows 這個(gè)問(wèn)題比起no_data_found要復(fù)雜一些。
給一個(gè)變量賦值時(shí)纵苛，但是查詢結(jié)果有多個(gè)記錄剿涮。
處理這種問(wèn)題也有兩種情況：
1． 多條數(shù)據(jù)是可以接受的，也就是說(shuō)從結(jié)果集中隨便取一個(gè)值就行攻人。這種情況應(yīng)該很極端了吧取试，如果出現(xiàn)這種情況，也說(shuō)明了程序的嚴(yán)謹(jǐn)性存在問(wèn)題怀吻。
2． 多條數(shù)據(jù)是不可以被接受的瞬浓，在這種情況肯定是程序的邏輯出了問(wèn)題，也說(shuō)是說(shuō)原來(lái)根本就不會(huì)想到它會(huì)產(chǎn)生多條記錄烙博。
對(duì)于第一種情況瑟蜈，就必須采用游標(biāo)來(lái)處理烟逊，而對(duì)于第二種情況就必須使用內(nèi)部塊來(lái)處理，重新拋出異常铺根。
多條數(shù)據(jù)可以接受,隨便取一條宪躯，這個(gè)跟no_data_found的處理方式一樣，使用游標(biāo)位迂。
我這里僅說(shuō)第二種情況访雪，不可接受多條數(shù)據(jù)，但是不要忘了處理no_data_found哦掂林。這就不能使用游標(biāo)了臣缀，必須使用內(nèi)部塊。
create or replace proceduer procexception2(
p varchar2
) as
v_postype varchar2(20);
begin
begin
select pos_type into v_postype from pos_type_tb1 where rownum < 5;
exception
when no_data_found then
v_postype := null;
when too_many_rows then
raise_application_error(-20000,'對(duì)v_postype賦值是,找到多條數(shù)據(jù)');
end;
dbms_output.put_line(v_postype);
end;
需要注意的是一定要加上對(duì)no_data_found的處理泻帮，對(duì)出現(xiàn)多條記錄的情況則繼續(xù)拋出異常精置，讓上一層來(lái)處理。
總之對(duì)于select into的語(yǔ)句需要注意這兩種情況了锣杂。需要妥當(dāng)處理啊脂倦。
3.5 在存儲(chǔ)過(guò)程中返回結(jié)果集
我們使用存儲(chǔ)過(guò)程都是返回值都是單一的，有時(shí)我們需要從過(guò)程中返回一個(gè)集合元莫。即多條數(shù)據(jù)赖阻。這有幾種解決方案。比較簡(jiǎn)單的做法是寫(xiě)臨時(shí)表踱蠢，但是這種做法不靈活火欧。而且維護(hù)麻煩。我們可以使用嵌套表來(lái)實(shí)現(xiàn).沒(méi)有一個(gè)集合類型能夠與java的jdbc類型匹配茎截。這就是對(duì)象與關(guān)系數(shù)據(jù)庫(kù)的阻抗吧苇侵。數(shù)據(jù)庫(kù)的對(duì)象并不能夠完全轉(zhuǎn)換為編程語(yǔ)言的對(duì)象，還必須使用關(guān)系數(shù)據(jù)庫(kù)的處理方式
