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

1. create or replace procedure updateauths(??
2. ?? p_authscode auths.author_code%type,??
3. ?? p_authssalary auths.salary%type)??
4. as??
5. begin??
6. ?? update auths set salary=p_authssalary where author_code=p_authscode;??
7. ?? commit;??
8. end updateauths;??

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代碼

1. declare??
2. ?? v_authorcode auths.author_code%type:='A00011';??
3. ?? v_salary auths.salary%type:=350;??
4. begin??
5. ?? updateauths(v_authorcode,v_salary);??
6. end;??

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和IN OUT代表參數(shù)的三種不同模式：
IN：當(dāng)調(diào)用存儲過程時，該模式的形參接收對應(yīng)實(shí)參的值，并且該是只讀的，即不能被修改。默認(rèn)為IN。
OUT：該形參被認(rèn)為只能寫，既只能為其賦值。在存儲過程中不能讀它的值。返回時，將該形參值傳給相應(yīng)的實(shí)參。
IN OUT：都允許。
Java代碼

1. create or replace procedure updateauthssalary(??
2. ?? p_author_code in out auths.author_code%type,??
3. ?? p_salary in number,??
4. ?? p_name out auths.name%type) is??
5. ?? v_salary_temp number; --定義存儲過程中的局部變量??
6. begin??
7. ?? select salary into v_salary_temp from auths where author_code=p_author_code;??
8. ??if v_salary_temp<300 then??
9. ???? update auths set salary=p_salary where author_code=p_author_code;??
10. ?? end if;??
11. ?? select name into p_name from auths where author code=p_author_code;??
12. end updateauthssalary;??

create or replace procedure updateauthssalary( p_author_code in out auths.author_code%type, p_salary in number, p_name out auths.name%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ù)時，不能指定CHAR類型和VARCHAR2類型形參的長度，也不能指定NUMBER形參的精度和標(biāo)度。這些約束由實(shí)參來傳遞。
例如，下面的存儲過程定義不合法，將產(chǎn)生一個編譯錯誤：
Java代碼

1. create or replace procedure proc_auths(??
2. ?? --參數(shù)定義了類型長度，將產(chǎn)生編譯錯誤。??
3. ?? p_code in out varchar2(6),??
4. ?? p_salary out number(8,2)) as??
5. begin??
6. ?? select salary into p_salary from auths where author_code=p_code;??
7. end proc_auths;??

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代碼

1. create or replace procedure proc_auths(??
2. ?? --參數(shù)定義了類型長度，將產(chǎn)生編譯錯誤。??
3. ?? p_code in out varchar2,??
4. ?? p_salary out number) as??
5. begin??
6. ?? select salary into p_salary from auths where author_code=p_code;??
7. end proc_auths;??

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代碼

1. delcare??
2. ?? v_code varchar2(6);??
3. ?? v_salary number(8,2);??
4. begin??
5. ?? v_code:='A00001';??
6. ?? proc_auths(v_code,v_salary);??
7. end;??

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代碼

1. create or replace procedure query_salary(??
2. ?? p_code in out auths.author_code%type,??
3. ?? p_salary out auths.salary%type) as??

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代碼

1. create or replace procedure insert_auths(??
2. ?? p_code auths.author_code%type,??
3. ?? p_name auths.name%type,??
4. ?? p_sex auths.sex%type,??
5. ?? p_birthdate auths.birthdate%type) as??

create or replace procedure insert_auths( p_code auths.author_code%type, p_name auths.name%type, p_sex auths.sex%type, p_birthdate auths.birthdate%type) as下面進(jìn)行兩種方法的調(diào)用：
Java代碼

1. declare??
2. ?? v_code varchar2(6);??
3. ?? v_name varchar2(12);??
4. ?? v_sex number(1);??
5. ?? v_birthdate date;??
6. begin??
7. ?? v_code:='A00021';??
8. ?? v_name:='張';??
9. ?? v_sex:=1;??
10. ?? v_birthdate:='5-seq-70';??
11. ?? --實(shí)參的位置順序與形參的位置順序相對應(yīng)。---位置表示法??
12. ?? insert_auths(v_code,v_name,v_sex,v_birthdate);??
13. ?? --實(shí)參名與形參名對應(yīng)，這樣就可以重新排列參數(shù)的先后順序。---命名表示法??
14. end;??

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ù)也必須使用命名表示法傳值。
(3)參數(shù)的缺省值
如可以這樣：
p_entry_date_time auths.entry_date_time%type:sysdate,
p_sex auths.sex%type default 1



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代碼

1. create or replace function salarystat(??
2. ?? p_sex auths.sex%type)??
3. ??return boolean is??
4. ?? v_currentsexauthors number;??
5. ?? v_maxauthors number;??
6. ?? v_returnvalue boolean;??
7. ?? v_percent constant number:=70;??
8. begin??
9. ?? --獲得滿足條件的作家的最大數(shù)。??
10. ?? select count(author_code) into v_maxauthors from auths where sex=p_sex and salary>=200;??
11. ?? select count(author_code) into v_currentsexauthors from auths where sex=p_sex;??
12. ??if(v_maxauthors/v_currentsexauthors*100)>v_percent then??
13. ???? v_returnvalue:=true;??
14. ??else??
15. ???? v_returnvalue:=false;??
16. ?? end if;??
17. ??return v_returnvalue;??
18. end salarystat;??

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代碼

1. declare??
2. ?? cursor c_auths is??
3. ???? select distinct sex from auths;??
4. begin??
5. ??for v_authsrecord in c_auths loop??
6. ????if salarystat(v_authsrecord.sex) then??
7. ?????? update auths set salary=salary-50 where sex=v_authsrecord.sex;??
8. ???? end if;??
9. ?? end loop;??
10. end;??

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和IN OUT模式的形參的當(dāng)前值傳給實(shí)參，然后繼續(xù)執(zhí)行調(diào)用存儲過程后的語句。
在使用函數(shù)與存儲過程時，一般情況下，如果只有一個返回值，則使用函數(shù)；如果有多個返回值則使用存儲過程。盡管函數(shù)的參數(shù)可以是OUT模式，但是一般不這樣使用。

3、刪除過程與函數(shù)
drop procedure procedurename;
drop function functionname;

4、庫存子程序和局部子程序
前面的子程序都是存儲在數(shù)據(jù)庫中的子程序，即庫存子程序。這些子程序是由ORACLE命令創(chuàng)建的，并可在其它的PL/SQL塊中調(diào)用。它們在創(chuàng)建時要進(jìn)行編譯，并將編譯后的代碼存儲在數(shù)據(jù)庫中。當(dāng)子程序被調(diào)用時，編譯后的代碼從數(shù)據(jù)庫中讀出并執(zhí)行。
一個子程序也可以在塊的定義部分創(chuàng)建，這樣的子程序被叫作局部子程序。
下面定義了一個局部函數(shù)formatname：
Java代碼

1. declare??
2. ?? cursor c_allauthors is??
3. ???? select name,sex from auths;??
4. ?? v_formattedname varchar2(60);??
5. ?? function formatname(p_name in varchar2,p_sex in number)??
6. ????return varchar2 is??
7. ???? v_sex varchar2(16);??
8. ?? begin??
9. ????if p_sex=1 then??
10. ?????? v_sex:='男';??
11. ????else??
12. ?????? v_sex:='女';??
13. ???? end if;??
14. ????return p_name||'('||v_sex||')';??
15. ?? end formatname;??
16. begin??
17. ??for v_authsrecord in c_allauthors loop??
18. ???? v_formattedname:=??
19. ?????? formatname(v_authsrecord.name,v_authsrecord.sex);??
20. ???? dbms_output.put_line(v_formattedname);??
21. ?? end loop;??
22. end;??

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 loop v_formattedname:= formatname(v_authsrecord.name,v_authsrecord.sex); dbms_output.put_line(v_formattedname); end loop; end;如上例，在無名塊的定義部分創(chuàng)建了formatname函數(shù)。這個函數(shù)只在創(chuàng)建它的塊中可用，它的作用域從創(chuàng)建它開始到結(jié)束。
局部子程序只能在定義部分的最后被創(chuàng)建，如果將formatname函數(shù)移到上面，將會出現(xiàn)編譯錯誤。子程序必須先定義再引用。

存儲過程與函數(shù)