?
???
Oracle存儲過程包含三部分：過程聲明，執(zhí)行過程部分，存儲過程異常。
Oracle存儲過程可以有無參數(shù)存儲過程和帶參數(shù)存儲過程。?
一、無參程序過程語法
1?create?or?replace?procedure?NoParPro
2?as??
;
3?begin
4?;
5?exception???? //存儲過程異常
6?????;
7?end;
8?
????? ? 二、帶參存儲過程實例
?1?create?or?replace?procedure?queryempname(sfindno?emp.empno%type)?as
?2????????sName?emp.ename%type;
?3????????sjob?emp.job%type;
?4?begin
?5????? ? ....
?7?exception
????????? ....
14?end;
15?
????三、 帶參數(shù)存儲過程含賦值方式?1?create?or?replace?procedure?runbyparmeters??(isal?in?emp.sal%type,
??????????????????????????? sname?out?varchar,sjob?in?out?varchar)
?2??as?icount?number;
?3??begin
?4???????select?count(*)?into?icount?from?emp?where?sal>isal?and?job=sjob;
?5???????if?icount=1?then
?6???????? ....
?9???????else
10????? ?? ....
12???????end?if;
13??exception
14???????when?too_many_rows?then
15???????DBMS_OUTPUT.PUT_LINE('返回值多于1行');
16???????when?others?then
17???????DBMS_OUTPUT.PUT_LINE('在RUNBYPARMETERS過程中出錯！');
18??end;
19?
? 四、在Oracle中對存儲過程的調(diào)用? 過程調(diào)用方式一
?1?declare
?2????????realsal?emp.sal%type;
?3????????realname?varchar(40);
?4????????realjob?varchar(40);
?5??begin?? //存儲過程調(diào)用開始
?6????????realsal:=1100;
?7????????realname:='';
?8????????realjob:='CLERK';
?9????????runbyparmeters(realsal,realname,realjob);???? －－必須按順序
10????????DBMS_OUTPUT.PUT_LINE(REALNAME||'???'||REALJOB);
11??END;? //過程調(diào)用結(jié)束
12?
? 過程調(diào)用方式二
?1?declare
?2???????realsal?emp.sal%type;
?3???????realname?varchar(40);
?4???????realjob?varchar(40);
?5?begin????//過程調(diào)用開始
?6???????realsal:=1100;
?7???????realname:='';
?8???????realjob:='CLERK';
?9???????runbyparmeters(sname=>realname,isal=>realsal,sjob=>realjob);? －－指定值對應變量順序可變
10???????DBMS_OUTPUT.PUT_LINE(REALNAME||'???'||REALJOB);
11?END;? //過程調(diào)用結(jié)束
12?
?至此，有關ORACLE的基本存儲過程以及對Oracle存儲過程的調(diào)用方式介紹完畢。
?
鳳凰涅槃/浴火重生/馬不停蹄/只爭朝夕
???? 隱姓埋名/低調(diào)華麗/簡單生活/完美人生