1、存儲過程1 CREATE OR REPLACE PROCEDURE 存儲過程名2 IS3 BEGIN4 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 procedur

2、e存儲過程名（paraml intype , param2 out type ）as變量 1 類型（值范圍）；-vs_msg VARCHAR2（4000）;變量2類型（值范圍）；BeginSelect count（*） into 變量 1 from 表 A where 列名=param1 ；If （判斷條件）thenSelect列名into 變量2 from 表A where 列名=param1 ；Dbms_output。Put_line（ 打印信息）;Elsif （判斷條件）thenDbms_output。Put_line（ 打印信息）;ElseRaise 異常名（NO_DATA_FOUND

3、 ）;End if;ExceptionWhen others thenRollback;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 (

4、4000 );-錯誤信息變量 vs_ym_beg CHAR (6);-起始月份vs_ym_end CHAR (6);-終止月份vs_ym_sn_beg CHAR (6);-同期起始月份 vs_ym_sn_end CHAR (6);-同期終止月份-定義游標（簡單的說就是一個可以遍歷的結果集CURSORSELECTFROM o o oWHERE oooGROUP BY °。Oralce 的SUBSTREGIN-用輸入參數給變量賦初值，用到了TO_CHAR ADD_MONTHSTO_DATE等很常用的函數 vs_ym_beg := SUBSTR(is_ym,1,6);vs_ym_end :

5、= SUBSTR(is_ym,7,6);vs_ym_sn_begTO_CHAR(ADD_MONTHS(TO_DATE(vs_ym_beg,'yyy ymm'), -12),'yyyymm');vs_ym_sn_end :=TO_CHAR(ADD_MONTHS(TO_DATE(vs_ym_end,'yyy ymm'), -12),'yyyymm');-先刪除表中特定條件的數據DELETE FROM 表名 WHERE ym = is_ym;-然后用內置的DBMS_OUTPUT對象的put_line方法打印出影響的記錄行數，其中用到一個

6、系統變量SQL%rowcountBMS OUTPUT.put line('del上月記錄='|SQL%rowcount|' 條');INSERT INTO 表名(area_code,ym,CMCODE,rmb_amt,usd_amt)SELECTarea_code,is_ym,CMCODE,SUM(rmb_amt)/10000,SUM(usd_amt)/10000FROM BGD_AREA_CM_M_BASE_TWHERE ym >= vs_ym_begAND ym <= vs_ym_endGROUP BY area_code,CMCODE;DBMS

7、_OUTPUT.put_line('ins當月記錄='|SQL%rowcount|' 條');-遍歷游標處理后更新到表。遍歷游標有幾種方法，用for 語句是其中比較直觀的一種。FOR rec IN cur_1 LOOPUPDATE表名SET rmb_amt_sn = rec.rmb_amt_sn,usd_amt_sn =rec.usd_amt_snWHERE area_code = rec.area_codeAND CMCODE = rec.CMCODEAND ym = is_ym;END LOOP;COMMIT;-錯誤處理部分。OTHERS表示除了聲明外的任意

8、錯誤。SQLERRM是系統內置變量保存了當前錯誤的詳細信息。EXCEPTIONWHEN OTHERS THENvs_msg := 'ERROR INxxxxxxxxxxx_p('|is_ym|'):'|SUBSTR(SQLERRM,1,500)ROLLBACK;-把當前錯誤記錄進日志表INSERT INTOLOGNF O(proc_name,error_info,op_date)VALUES ('xxxxxxxxxxx_p',vs_msg,SYSDATE);COMMIT;RETURN;END;oracle存儲過程語法1 、判斷語句：if 比較式

9、then begin end; end if;create or replace procedure test(x in number) is beginif x >0 thenbeginx := 0 - x;end;end if;if x = 0 thenbeginx: = 1;end;end if;end test;2、For循環For . in . LOOP-執行語句end LOOP;(1)循環遍歷游標create or replace procedure test() asCursor cursor is select name from student; name varcha

10、r(20);beginfor name in cursor LOOPbegindbms_output.putline(name);end;end LOOP;end test;(2)循環遍歷數組create or replace procedure test(varArray in myPackage.TestArray) as-(輸入參數varArray 是自定義的數組類型，定義方式 見標題6)i number;begini := 1;-存儲過程數組是起始位置是從 1開始的，與 java、C、C+ 等語言不同。因為在 Oracle 中本是沒有 數組的概念的，數組其實就是一張-表(Table),

11、每個數組元素就是表中的一個記錄，所以遍歷數組時就相當于從表中的第一條記錄開始遍歷for i in 1.varArray.count LOOPdbms_output.putlin e(&apos;The No.& apos;| i | &apos;record in varArray is:&apos;|varArray(i);end LOOP;end test;3、While 循環while 條件語句 LOOPbeginend;end LOOP;E.gcreate or replace procedure test(i in number) as beginwh

12、ile i < 10 LOOPbegini:= 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) isx array;beginx := new array();y := x;end te

13、st;(2)自定義的數組類型(自定義數據類型時，建議通過創建Package 的方式實現，以便于管理 )create or replace package myPackage isPublic type declarationstype info isrecord( name varchar(20),y number);type TestArray is table of info index by binary_integer;-此處聲明了一個 TestArray的類型數據，其實其為一張存儲Info數據類型的Table而已，及TestArray 就是一 張表，有兩個字段，一個是name ，一個

14、是y。需要注意的是此處使用了Index by binary_integer 編制該Table的索引項，也可以不寫，直接寫成：type TestArray istable of info，如果不寫的話使用數組時就需要進行初始化： varArray myPackage.TestArray; varArray := new myPackage.TestArray();end TestArray;5. 游標的使用Oracle中Cursor是非常有用的，用于遍歷臨時表中的查詢結果。其相關方法和屬性也很多，現僅 就常用的用法做一二介紹：("Cursor型游標(不能用于參數傳遞)create or

15、 replace procedure test() iscusor_1 Cursor is select std_name from studentwhere ；-Cursor 的使用方式 1 cursor_2 Cursor;beginselect class_name into cursor_2 from classwhere；-Cursor 的使用方式2可使用 For x in cursor LOOP . end LOOP; 來實現對 Cursor 的遍歷end test;(2)SYS_REFCURSOR型游標，該游標是Oracle 以預先定義的游標，可作出參數進行傳遞create or

16、replace procedure test(rsCursor outSYS_REFCURSOR) iscursor SYS_REFCURSOR;name varhcar(20);beginOPEN cursor FOR select name from studentwhere . -SYS REFCURSOR只能通過OPEN方法來打開和賦值LOOPfetch cursor into name-SYS REFCURSOR 只能通過fetch into來打開和遍歷exit whencursor%NOTFOUND;-SYS REFCURSOR 中可使用三個狀態屬性:-%NOTFOUND(未找到記

17、錄信息）%FOUND（ 找到記錄信-%ROWCOUNT(然后當前游標所指向的行位置）dbms_output.putline(name);end LOOP;rsCursor := cursor;end test;實例下面寫一個簡單的例子來對以上所說的存儲過程的用法 做一個應用：現假設存在兩張表，一張是學生成績表(studnet)，字段 為：stdld,math,article,language,music,sport,total,average,step一張是學生課外成績表(out_school), 字段為:stdld,parctice,comment通過存儲過程自動計算出每位學生的總成績和平均

18、成績，同時，如果學生在課外課程中獲得的評價為A，就在總成績上加20分。create or replace procedure autocomputer(step in number) isrsCursor SYS_REFCURSOR;commentArray myPackage.myArray;math number;article number; language number; music number; sport number; total number;average number;stdId varchar(30);record myPackage.stdlnfo;i number

19、;begini := 1;et comment(commentArray);調用名為get_comment()的存儲過程獲取學生課外評分信息OPEN rsCursor for select stdld,math,article,language,music,sport from student t where t.step = step;LOOPfetch rsCursor into stdId,math,article,language,music,sport; exit when rsCursor%NOTFOUND;total := math + article + language +

20、music + sport;for i in mentArray.count LOOPrecord := commentArray(i);if stdId = record.stdId thenbeginif ment = &apos;A&apos; thenbegintotal := total + 20;go to next;- 使用go to 跳出for 循環 end;end if;end;end if;end LOOP;v<continue>> average := total / 5;update student t set t.total=total