PL-SQL 存儲過程與函數(shù)

PL/SQL存儲過程與函數(shù)1、存儲過程存儲過程的參數(shù)形式參數(shù)和實(shí)際參數(shù)，例如，有如下一個存儲過程，該過程接收一個作家代碼和一個工資值，將該作家的工資改為接收到的工資值。Java代碼create

or

replace

procedure

updateauths(

p_authscode

auths.author_code%type,

p_authssalary

auths.salary%type)

as

begin

update

auths

set

salary=p_authssalary

where

author_code=p_authscode;

commit;

end

updateauths;

下面的PL/SQl塊調(diào)用updateauths存儲過程，將代碼為A00011的作家的工資改為350元。Java代碼declare

v_authorcode

auths.author_code%type:='A00011';

v_salary

auths.salary%type:=350;

begin

updateauths(v_authorcode,v_salary);

end;

v_authorcode、v_salary作為參數(shù)傳遞到存儲過程updateauths中，這些參數(shù)是實(shí)際參數(shù)，簡稱實(shí)參。

p_authscode、p_authssalary就是形式參數(shù)，簡稱形參。

參數(shù)定義中，IN、OUT和INOUT代表參數(shù)的三種不同模式：

IN：當(dāng)調(diào)用存儲過程時(shí)，該模式的形參接收對應(yīng)實(shí)參的值，并且該是只讀的，即不能被修改。默認(rèn)為IN。

OUT：該形參被認(rèn)為只能寫，既只能為其賦值。在存儲過程中不能讀它的值。返回時(shí)，將該形參值傳給相應(yīng)的實(shí)參。

INOUT：都允許。Java代碼create

or

replace

procedure

updateauthssalary(

p_author_code

in

out

auths.author_code%type,

p_salary

in

number,

p_name

out

%type)

is

v_salary_temp

number;

--定義存儲過程中的局部變量

begin

select

salary

into

v_salary_temp

from

auths

where

author_code=p_author_code;

if

v_salary_temp<300

then

update

auths

set

salary=p_salary

where

author_code=p_author_code;

end

if;

select

name

into

p_name

from

auths

where

author

code=p_author_code;

end

updateauthssalary;

(1)參數(shù)的數(shù)據(jù)類型在定義一個存儲過程參數(shù)時(shí)，不能指定CHAR類型和VARCHAR2類型形參的長度，也不能指定NUMBER形參的精度和標(biāo)度。這些約束由實(shí)參來傳遞。

例如，下面的存儲過程定義不合法，將產(chǎn)生一個編譯錯誤：Java代碼create

or

replace

procedure

proc_auths(

--參數(shù)定義了類型長度，將產(chǎn)生編譯錯誤。

p_code

in

out

varchar2(6),

p_salary

out

number(8,2))

as

begin

select

salary

into

p_salary

from

auths

where

author_code=p_code;

end

proc_auths;

修改上面存儲過程的定義為：Java代碼create

or

replace

procedure

proc_auths(

--參數(shù)定義了類型長度，將產(chǎn)生編譯錯誤。

p_code

in

out

varchar2,

p_salary

out

number)

as

begin

select

salary

into

p_salary

from

auths

where

author_code=p_code;

end

proc_auths;

p_code的長度約束和p_salary的精度，標(biāo)度約束由實(shí)參傳遞。Java代碼delcare

v_code

varchar2(6);

v_salary

number(8,2);

begin

v_code:='A00001';

proc_auths(v_code,v_salary);

end;

注意，如果使用%type為參數(shù)定義類型，那么該參數(shù)具有定義在形參上而不是通過實(shí)參傳遞的數(shù)據(jù)長度。Java代碼create

or

replace

procedure

query_salary(

p_code

in

out

auths.author_code%type,

p_salary

out

auths.salary%type)

as

--那么由于author_code的長度為6，因此p_code的長度也為6。

(2)參數(shù)的傳值方式位置表示法、名稱表示法

如有這樣的存儲過程Java代碼create

or

replace

procedure

insert_auths(

p_code

auths.author_code%type,

p_name

%type,

p_sex

auths.sex%type,

p_birthdate

auths.birthdate%type)

as

下面進(jìn)行兩種方法的調(diào)用：Java代碼declare

v_code

varchar2(6);

v_name

varchar2(12);

v_sex

number(1);

v_birthdate

date;

begin

v_code:='A00021';

v_name:='張';

v_sex:=1;

v_birthdate:='5-seq-70';

--實(shí)參的位置順序與形參的位置順序相對應(yīng)。---位置表示法

insert_auths(v_code,v_name,v_sex,v_birthdate);

--實(shí)參名與形參名對應(yīng)，這樣就可以重新排列參數(shù)的先后順序。---命名表示法

end;

注意，位置表示法和命名表示法在一些調(diào)用中也可以混合使用。但是，如果出現(xiàn)第一個用命名表示法的參數(shù)時(shí)，后面的參數(shù)也必須使用命名表示法傳值。

(3)參數(shù)的缺省值

如可以這樣：

p_entry_date_timeauths.entry_date_time%type:sysdate,

p_sexauths.sex%typedefault1

2、創(chuàng)建函數(shù)函數(shù)與存儲過程非常類似，都有三種模式的參數(shù)。它們都可以存儲在數(shù)據(jù)庫中(當(dāng)然過程與函數(shù)也可以不在于數(shù)據(jù)庫中)，并且在塊中調(diào)用。

與存儲過程不同，存儲過程只能作為一個PL/SQL語句調(diào)用，而函數(shù)作為表達(dá)式的一部分調(diào)用。并且它們的定義、可執(zhí)行、異常處理部分是不同的。

例如，如作家表中男作家或女作家的工資在200元以上的人數(shù)大于百分之七十，則下面的函數(shù)返回TRUE，否則返回FALSE：Java代碼create

or

replace

function

salarystat(

p_sex

auths.sex%type)

return

boolean

is

v_currentsexauthors

number;

v_maxauthors

number;

v_returnvalue

boolean;

v_percent

constant

number:=70;

begin

--獲得滿足條件的作家的最大數(shù)。

select

count(author_code)

into

v_maxauthors

from

auths

where

sex=p_sex

and

salary>=200;

select

count(author_code)

into

v_currentsexauthors

from

auths

where

sex=p_sex;

if(v_maxauthors/v_currentsexauthors*100)>v_percent

then

v_returnvalue:=true;

else

v_returnvalue:=false;

end

if;

return

v_returnvalue;

end

salarystat;

下面進(jìn)行調(diào)用：Java代碼declare

cursor

c_auths

is

select

distinct

sex

from

auths;

begin

for

v_authsrecord

in

c_auths

loop

if

salarystat(v_authsrecord.sex)

then

update

auths

set

salary=salary-50

where

sex=v_authsrecord.sex;

end

if;

end

loop;

end;

return也可以用在存儲過程中。在這種情況下，它沒有參數(shù)。當(dāng)執(zhí)行了不帶參數(shù)的return語句后，立刻將控制返回到調(diào)用環(huán)境，并將OUT和INOUT模式的形參的當(dāng)前值傳給實(shí)參，然后繼續(xù)執(zhí)行調(diào)用存儲過程后的語句。在使用函數(shù)與存儲過程時(shí)，一般情況下，如果只有一個返回值，則使用函數(shù)；如果有多個返回值則使用存儲過程。盡管函數(shù)的參數(shù)可以是OUT模式，但是一般不這樣使用。3、刪除過程與函數(shù)dropprocedureprocedurename;dropfunctionfunctionname;4、庫存子程序和局部子程序前面的子程序都是存儲在數(shù)據(jù)庫中的子程序，即庫存子程序。這些子程序是由ORACLE命令創(chuàng)建的，并可在其它的PL/SQL塊中調(diào)用。它們在創(chuàng)建時(shí)要進(jìn)行編譯，并將編譯后的代碼存儲在數(shù)據(jù)庫中。當(dāng)子程序被調(diào)用時(shí)，編譯后的代碼從數(shù)據(jù)庫中讀出并執(zhí)行。

一個子程序也可以在塊的定義部分創(chuàng)建，這樣的子程序被叫作局部子程序。

下面定義了一個局部函數(shù)formatname：Java代碼declare

cursor

c_allauthors

is

select

name,sex

from

auths;

v_formattedname

varchar2(60);

function

formatname(p_name

in

varchar2,p_sex

in

number)

return

varchar2

is

v_sex

varchar2(16);

begin

if

p_sex=1

then

v_sex:='男';

else

v_sex:='女';

end

if;

return

p_name||'('||v_sex||')';

end

formatname;

begin

for

v_authsrecord

in

c_allauthors

l