存儲過程

　　1??CREATE?OR?REPLACE?PROCEDURE?存儲過程名

　　2??IS

　　3??BEGIN

　　4??NULL;

　　5??END;

?

行1:

　　CREATE?OR?REPLACE?PROCEDURE?是一個SQL語句通知Oracle數據庫去創建一個叫做skeleton存儲過程,?如果存在就覆蓋它;

行2:

　　IS關鍵詞表明后面將跟隨一個PL/SQL體。

行3:

　　BEGIN關鍵詞表明PL/SQL體的開始。

行4:

　　NULL?PL/SQL語句表明什么事都不做，這句不能刪去，因為PL/SQL體中至少需要有一句;

行5:

　　END關鍵詞表明PL/SQL體的結束

存儲過程創建語法：

?create?or?replace?procedure?存儲過程名（param1?in?type，param2?out?type）?

as?

變量1?類型（值范圍）;?--vs_msg???VARCHAR2(4000);?

變量2?類型（值范圍）;

Begin

Select?count(*)?into?變量1?from?表A?where列名=param1；

?

????If?(判斷條件)?then

???????Select?列名?into?變量2?from?表A?where列名=param1；

???????Dbms_output。Put_line(‘打印信息’);

????Elsif?(判斷條件)?then

???????Dbms_output。Put_line(‘打印信息’);

????Else

???????Raise?異常名（NO_DATA_FOUND）;

????End?if;

Exception

????When?others?then

???????Rollback;

End;

?

?

?

注意事項：

1，?存儲過程參數不帶取值范圍，in表示傳入，out表示輸出

類型可以使用任意Oracle中的合法類型。

2，??變量帶取值范圍，后面接分號

3，??在判斷語句前最好先用count（*）函數判斷是否存在該條操作記錄

4，??用select?。。。into。。。給變量賦值

5，??在代碼中拋異常用?raise+異常名

?

CREATE?OR?REPLACE?PROCEDURE存儲過程名
(

--定義參數
?is_ym??IN?CHAR(6)?,

the_count?OUT?NUMBER,
)?
AS?
--定義變量?
vs_msg???VARCHAR2(4000);???--錯誤信息變量
vs_ym_beg??CHAR(6);??????--起始月份
vs_ym_end??CHAR(6);??????--終止月份
vs_ym_sn_beg?CHAR(6);?????--同期起始月份
vs_ym_sn_end?CHAR(6);?????--同期終止月份

--定義游標(簡單的說就是一個可以遍歷的結果集)?

CURSOR?cur_1?IS?
??SELECT?。。。?
??FROM?。。。?
????WHERE?。。。
???GROUP?BY?。。。;?

BEGIN?

--用輸入參數給變量賦初值，用到了Oralce的SUBSTR?TO_CHAR?ADD_MONTHS?

?

TO_DATE?等很常用的函數。?
vs_ym_beg?:=?SUBSTR(is_ym,1,6);?
vs_ym_end?:=?SUBSTR(is_ym,7,6);?
vs_ym_sn_beg?:=?TO_CHAR(ADD_MONTHS(TO_DATE(vs_ym_beg,'yyyymm'),?-12),'yyyymm');?
vs_ym_sn_end?:=?TO_CHAR(ADD_MONTHS(TO_DATE(vs_ym_end,'yyyymm'),?-12),'yyyymm');?

--先刪除表中特定條件的數據。?

DELETE?FROM?表名?WHERE?ym?=?is_ym;?

??--然后用內置的DBMS_OUTPUT對象的put_line方法打印出影響的記錄行數，其中用到一個系統變量SQL%rowcount?

DBMS_OUTPUT.put_line('del上月記錄='||SQL%rowcount||'條');?

INSERT?INTO表名(area_code,ym,CMCODE,rmb_amt,usd_amt)?
SELECT?area_code,is_ym,CMCODE,SUM(rmb_amt)/10000,SUM(usd_amt)/10000?
FROM?BGD_AREA_CM_M_BASE_T?
??WHERE?ym?>=?vs_ym_beg?
??AND?ym?<=?vs_ym_end?
GROUP?BY?area_code,CMCODE;?

DBMS_OUTPUT.put_line('ins當月記錄='||SQL%rowcount||'條');?
--遍歷游標處理后更新到表。遍歷游標有幾種方法，用for語句是其中比較直觀的一種。

?
FOR?rec?IN?cur_1?LOOP?
??UPDATE?表名
??SET?rmb_amt_sn?=?rec.rmb_amt_sn,usd_amt_sn?=?rec.usd_amt_sn?
???WHERE?area_code?=?rec.area_code?
???AND?CMCODE?=?rec.CMCODE?
???AND?ym?=?is_ym;?
END?LOOP;?

COMMIT;?

--錯誤處理部分。OTHERS表示除了聲明外的任意錯誤。SQLERRM是系統內置變量保存了當前錯誤的詳細信息。?

EXCEPTION?

???WHEN?OTHERS?THEN?
??????vs_msg?:=?'ERROR?IN?xxxxxxxxxxx_p('||is_ym||'):'||SUBSTR(SQLERRM,1,500);

?
???ROLLBACK;?

???--把當前錯誤記錄進日志表。?

???INSERT?INTO?LOG_INFO(proc_name,error_info,op_date)?
???VALUES('xxxxxxxxxxx_p',vs_msg,SYSDATE);?
???COMMIT;?
???RETURN;?

END;

?

?

?

?

?

?

?

?

?

?

?

?

?

?

?

?

?

?

?

?

?

?

?

?

?

?

?

?

?

?

oracle存儲過程語法

1?、判斷語句:?

if?比較式?then?begin?end;?end?if;?

create?or?replace?procedure?test(x?in?number)?is?

begin?

????????if?x?>0?then?

?????????begin?

????????x?:=?0?-?x;?

????????end;?

????end?if;?

????if?x?=?0?then?

???????begin?

????????x:?=?1;?

????end;?

????end?if;?

end?test;?

2?、For?循環?

For?...?in?...?LOOP?

--?執行語句?

end?LOOP;?

(1)?循環遍歷游標?

create?or?replace?procedure?test()?as?

Cursor?cursor?is?select?name?from?student;?name?varchar(20);?

begin?

for?name?in?cursor?LOOP?

begin?

?dbms_output.putline(name);??

end;?

end?LOOP;?

end?test;?

(2)?循環遍歷數組?

?create?or?replace?procedure?test(varArray?in?myPackage.TestArray)?as?

--(?輸入參數varArray?是自定義的數組類型，定義方式見標題6)?

i?number;?

begin?

i?:=?1;??--?存儲過程數組是起始位置是從1?開始的，與java?、C?、C++?等語言不同。因為在Oracle?中本是沒有數組的概念的，數組其實就是一張?

--?表(Table),?每個數組元素就是表中的一個記錄，所以遍歷數組時就相當于從表中的第一條記錄開始遍歷?

for?i?in?1..varArray.count?LOOP??????

dbms_output.putline('The?No.'||?i?||?'record?in?varArray?is:'||varArray(i));????

?end?LOOP;?

end?test;?

3?、While?循環?

while?條件語句?LOOP?

begin?

end;?

end?LOOP;?

E.g?

create?or?replace?procedure?test(i?in?number)?as?

begin?

while?i?<?10?LOOP?

begin?????

?i:=?i?+?1;?

end;?

end?LOOP;?

?end?test;?

4?、數組?

首先明確一個概念：Oracle?中本是沒有數組的概念的，數組其實就是一張表(Table),?每個數組元素就是表中的一個記錄。?

使用數組時，用戶可以使用Oracle?已經定義好的數組類型，或可根據自己的需要定義數組類型。?

(1)?使用Oracle?自帶的數組類型?

x?array;?--?使用時需要需要進行初始化?

e.g:?

create?or?replace?procedure?test(y?out?array)?is?

?x?array;???

?begin?

x?:=?new?array();?

y?:=?x;?

end?test;?

(2)?自定義的數組類型?(?自定義數據類型時，建議通過創建Package?的方式實現，以便于管理)?

create?or?replace?package?myPackage?is?

???Public?type?declarations???type?info?is?record(?????name?varchar(20),?????y?number);?

??type?TestArray?is?table?of?info?index?by?binary_integer;???

--?此處聲明了一個TestArray?的類型數據，其實其為一張存儲Info?數據類型的Table?而已，及TestArray?就是一張表，有兩個字段，一個是name?，一個是y?。需要注意的是此處使用了Index?by?binary_integer?編制該Table?的索引項，也可以不寫，直接寫成：type?TestArray?is?

table?of?info?，如果不寫的話使用數組時就需要進行初始化：varArray?myPackage.TestArray;?varArray?:=?new?myPackage.TestArray();?

end?TestArray;?

5.?游標的使用?Oracle?中Cursor?是非常有用的，用于遍歷臨時表中的查詢結果。其相關方法和屬性也很多，現僅就常用的用法做一二介紹：?

(1)Cursor?型游標(?不能用于參數傳遞)?

create?or?replace?procedure?test()?is???

cusor_1?Cursor?is?select?std_name?from?student?where??...;??--Cursor?的使用方式1???cursor_2?Cursor;?

begin?

select?class_name?into?cursor_2?from?class?where?...;??--Cursor?的使用方式2?

可使用For?x?in?cursor?LOOP?....?end?LOOP;?來實現對Cursor?的遍歷?

end?test;?

(2)SYS_REFCURSOR?型游標，該游標是Oracle?以預先定義的游標，可作出參數進行傳遞?

create?or?replace?procedure?test(rsCursor?out?SYS_REFCURSOR)?is?

cursor?SYS_REFCURSOR;?

name?varhcar(20);?

begin?

OPEN?cursor?FOR?select?name?from?student?where?...?--SYS_REFCURSOR?只能通過OPEN?方法來打開和賦值?

LOOP?

?fetch?cursor?into?name???--SYS_REFCURSOR?只能通過fetch?into?來打開和遍歷?exit?when?cursor%NOTFOUND;??????????????--SYS_REFCURSOR?中可使用三個狀態屬性：?????????????????????????????????????????---%NOTFOUND(?未找到記錄信息)?%FOUND(?找到記錄信息)?????????????????????????????????????????---%ROWCOUNT(?然后當前游標所指向的行位置)?

?dbms_output.putline(name);?

end?LOOP;?

rsCursor?:=?cursor;?

end?test;?

?

?

?

實例

下面寫一個簡單的例子來對以上所說的存儲過程的用法做一個應用：?

現假設存在兩張表，一張是學生成績表(studnet)?，字段為：stdId,math,article,language,music,sport,total,average,step???????????????

一張是學生課外成績表(out_school),?字段為:stdId,parctice,comment?

通過存儲過程自動計算出每位學生的總成績和平均成績，同時，如果學生在課外課程中獲得的評價為A?，就在總成績上加20?分。?

create?or?replace?procedure?autocomputer(step?in?number)?is?

rsCursor?SYS_REFCURSOR;?

commentArray?myPackage.myArray;?

math?number;?

article?number;?

language?number;?

music?number;?

sport?number;?

total?number;?

average?number;?

stdId?varchar(30);?

record?myPackage.stdInfo;?

i?number;?

begin?

i?:=?1;?

get_comment(commentArray);?--?調用名為get_comment()?的存儲過程獲取學生課外評分信息?

OPEN?rsCursor?for?select?stdId,math,article,language,music,sport?from?student?t?where?t.step?=?step;?

LOOP?

fetch?rsCursor?into?stdId,math,article,language,music,sport;?exit?when?rsCursor%NOTFOUND;?

total?:=?math?+?article?+?language?+?music?+?sport;?

for?i?in?1..commentArray.count?LOOP??

?record?:=?commentArray(i);?????

if?stdId?=?record.stdId?then???

?begin??????

?if?record.comment?=?'A'?then??????

??begin??????????

?total?:=?total?+?20;????

???go?to?next;?--?使用go?to?跳出for?循環????????

??end;?????

end?if;???

end;???

end?if;?

end?LOOP;?

<<continue>>??average?:=?total?/?5;?

?update?student?t?set?t.total=total?and?t.average?=?average?where?t.stdId?=?stdId;?

end?LOOP;?

end;?

end?autocomputer;?

--?取得學生評論信息的存儲過程?

create?or?replace?procedure?get_comment(commentArray?out?myPackage.myArray)?is?

rs?SYS_REFCURSOR?；?

record?myPackage.stdInfo;?

stdId?varchar(30);?

comment?varchar(1);?

i?number;?

begin?

open?rs?for?select?stdId,comment?from?out_school?

i?:=?1;?

LOOP?

?fetch?rs?into?stdId,comment;?exit?when?rs%NOTFOUND;?

record.stdId?:=?stdId;?

?record.comment?:=?comment;?

recommentArray(i)?:=?record;?

i:=i?+?1;?

end?LOOP;?

end?get_comment;?

--?定義數組類型myArray?

create?or?replace?package?myPackage?is?begin?

type?stdInfo?is?record(stdId?varchar(30),comment?varchar(1));?

type?myArray?is?table?of?stdInfo?index?by?binary_integer;?

end?myPackage;

轉載于:https://www.cnblogs.com/sotall/p/4366066.html

總結

以上是生活随笔為你收集整理的Oracle存储过程（转）的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。