1、存儲過程
存儲過程的參數
形式參數和實際參數，例如，有如下一個存儲過程，該過程接收一個作家代碼和一個工資值，將該作家的工資改為接收到的工資值。
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塊調用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作為參數傳遞到存儲過程updateauths中，這些參數是實際參數，簡稱實參。
p_authscode、p_authssalary就是形式參數，簡稱形參。
參數定義中，IN、OUT和IN OUT代表參數的三種不同模式：
IN：當調用存儲過程時，該模式的形參接收對應實參的值，并且該是只讀的，即不能被修改。默認為IN。
OUT：該形參被認為只能寫，既只能為其賦值。在存儲過程中不能讀它的值。返回時，將該形參值傳給相應的實參。
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)參數的數據類型
在定義一個存儲過程參數時，不能指定CHAR類型和VARCHAR2類型形參的長度，也不能指定NUMBER形參的精度和標度。這些約束由實參來傳遞。
例如，下面的存儲過程定義不合法，將產生一個編譯錯誤：
Java代碼

1. create or replace procedure proc_auths(??
2. ?? --參數定義了類型長度，將產生編譯錯誤。??
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( --參數定義了類型長度，將產生編譯錯誤。 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. ?? --參數定義了類型長度，將產生編譯錯誤。??
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( --參數定義了類型長度，將產生編譯錯誤。 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的精度，標度約束由實參傳遞。
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為參數定義類型，那么該參數具有定義在形參上而不是通過實參傳遞的數據長度。
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)參數的傳值方式
位置表示法、名稱表示法
如有這樣的存儲過程
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下面進行兩種方法的調用：
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. ?? --實參的位置順序與形參的位置順序相對應。---位置表示法??
12. ?? insert_auths(v_code,v_name,v_sex,v_birthdate);??
13. ?? --實參名與形參名對應，這樣就可以重新排列參數的先后順序。---命名表示法??
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'; --實參的位置順序與形參的位置順序相對應。---位置表示法 insert_auths(v_code,v_name,v_sex,v_birthdate); --實參名與形參名對應，這樣就可以重新排列參數的先后順序。---命名表示法 end;注意，位置表示法和命名表示法在一些調用中也可以混合使用。但是，如果出現第一個用命名表示法的參數時，后面的參數也必須使用命名表示法傳值。
(3)參數的缺省值
如可以這樣：
p_entry_date_time auths.entry_date_time%type:sysdate,
p_sex auths.sex%type default 1



2、創(chuàng)建函數
函數與存儲過程非常類似，都有三種模式的參數。它們都可以存儲在數據庫中(當然過程與函數也可以不在于數據庫中)，并且在塊中調用。
與存儲過程不同，存儲過程只能作為一個PL/SQL語句調用，而函數作為表達式的一部分調用。并且它們的定義、可執(zhí)行、異常處理部分是不同的。
例如，如作家表中男作家或女作家的工資在200元以上的人數大于百分之七十，則下面的函數返回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. ?? --獲得滿足條件的作家的最大數。??
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 --獲得滿足條件的作家的最大數。 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;下面進行調用：
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也可以用在存儲過程中。在這種情況下，它沒有參數。當執(zhí)行了不帶參數的return語句后，立刻將控制返回到調用環(huán)境，并將OUT和IN OUT模式的形參的當前值傳給實參，然后繼續(xù)執(zhí)行調用存儲過程后的語句。
在使用函數與存儲過程時，一般情況下，如果只有一個返回值，則使用函數；如果有多個返回值則使用存儲過程。盡管函數的參數可以是OUT模式，但是一般不這樣使用。

3、刪除過程與函數
drop procedure procedurename;
drop function functionname;

4、庫存子程序和局部子程序
前面的子程序都是存儲在數據庫中的子程序，即庫存子程序。這些子程序是由ORACLE命令創(chuàng)建的，并可在其它的PL/SQL塊中調用。它們在創(chuàng)建時要進行編譯，并將編譯后的代碼存儲在數據庫中。當子程序被調用時，編譯后的代碼從數據庫中讀出并執(zhí)行。
一個子程序也可以在塊的定義部分創(chuàng)建，這樣的子程序被叫作局部子程序。
下面定義了一個局部函數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函數。這個函數只在創(chuàng)建它的塊中可用，它的作用域從創(chuàng)建它開始到結束。
局部子程序只能在定義部分的最后被創(chuàng)建，如果將formatname函數移到上面，將會出現編譯錯誤。子程序必須先定義再引用。

存儲過程與函數