1、oracle存儲過程存儲過程 培訓材料培訓材料版權所有， 2009 CYTS Sysnet Electronics CO., LTD壽險二部帥曉鋒存儲過程存儲過程的建立存儲過程的語法結構存儲過程的控制語句存儲過程的開發存儲過程的運行存儲過程的調試Informix與oracle存儲過程的差異函數包2009/07/09尚洋信德存儲過程存儲過程的編寫編寫后綴名為sql的文件，一個存儲過程一個文件。過程格式：CREATE OR REPLACE PROCEDURE 存儲過程名字 ( 參數1 IN NUMBER, 參數2 IN NUMBER ) IS 變量1 INTEGER :=0; 變量2 DATE;

2、BEGIN END 存儲過程名字;2009/07/09尚洋信德存儲過程創建一個簡單存儲過程的方式1、1.從Window打開SQL*Plus并且從SQL*Plus登錄到你的數據庫;打開skeleton.sql文件. 2.在SQL命令提示符下輸入以下命令： SQLskeleton 注釋:(SQL*Plus裝載skeleton.sql文件的內容到SQL*Plus緩沖區, 并且執行SQL*Plus語句;SQL*Plus會通知你存儲過程已經被成功地創建)3.寫一個存儲過程,實例: CREATE OR REPLACE PROCEDURE skeleton IS BEGIN DBMS_OUTPUT.PUT_

3、LINE(HelloWorld!); END;2009/07/09尚洋信德存儲過程運行查看信息1.SQLEXECUTE skeleton; 注釋(SQL*Plus輸出一下信息確信存儲過程成功執 即PL/SQLproceduresuccessfullycompleted). 2.在SQL*Plus命令行提示符,鍵入: SQLSET SERVEROUTPUT ON 再次敲入SQLEXECUTE skeleton即可. 注釋: 查看存儲過程中的打印語句信息,實行以上命令. 2009/07/09尚洋信德存儲過程刪除一個存儲過程1.在SQL命令提示符下輸入以下命令： 命令 : SQLDROP PROCE

4、DURE 存儲過程名;2009/07/09尚洋信德存儲過程存儲過程的注釋- 注釋一行2009/07/09尚洋信德存儲過程存儲過程入參與返回值 create or replace procedure runbyparmeters (isal in emp.sal%type, sname out varchar, sjob in out varchar) as icount number; begin if icount=1 then . else . end if; end; 2009/07/09尚洋信德存儲過程存儲過程變量定義、常用變量類型realsal emp.sal%type; realn

5、ame varchar2(40); realjob varchar2(40); Price number(5,2);Product_id interger;注釋 : 同一存儲過程中，變量名最好不要重復。2009/07/09尚洋信德存儲過程存儲過程賦值語句:=realjob := work ;Product_id := 100001 ;realname := Brunhilda;Price := 3.1415;this_day := TODAY;2009/07/09尚洋信德存儲過程存儲過程變量先聲明且必須聲明才能使用。Begin end 塊外聲明的變量影響全局。Begin end 塊內聲明的變量

6、影響本Begin end 。變量聲明必須在存儲過程開頭或者Begin end 塊的開頭部分。2009/07/09尚洋信德存儲過程存儲過程操作符+-*/| 合并 如：sp_str1=“ASD”|”ERT” ,則： sp_str1=“ASDERT” 2009/07/09尚洋信德存儲過程存儲過程異常控制exception when too_many_rows then DBMS_OUTPUT.PUT_LINE(返回值多于1行); when others then DBMS_OUTPUT.PUT_LINE(在RUNBYPARMETERS過程中出錯！);2009/07/09尚洋信德存儲過程存儲過程結構塊

7、BEGIN 第一步處理；END;BEGIN 第二步處理；END;BEGIN 第三步處理；END; 注釋: 存儲過程BEGIN END 之間可以進行嵌套。2009/07/09尚洋信德存儲過程存儲過程游標1.帶參數的游標CURSOR C_USER(C_ID NUMBER) IS SELECT NAME FROM USER WHERE TYPEID = C_ID NUMBER; OPEN C_USER(變量值); LOOP FETCH C_USER INTO V_NAME; EXIT WHEN C_USER%NOTFOUND; END LOOP; CLOSE C_USER;2009/07/09尚洋信

8、德存儲過程2.不帶參數的游標CURSOR C_USER IS SELECT NAME FROM USER WHERE TYPEID = C_ID; OPEN C_USER; LOOP FETCH C_USER INTO V_NAME; EXIT WHEN C_USER%NOTFOUND; END LOOP; CLOSE C_USER;2009/07/09尚洋信德存儲過程存儲過程LOOP循環 Counter :=0; LOOP counter :=counter +1 ; EXIT WHEN counter =5; END LOOP;2009/07/09尚洋信德存儲過程存儲過程for循環1.方式

9、一:BEGIN FOR cur_result in cur LOOP BEGIN V_SUM :=cur_result.列名1+cur_result.列名2 ;END; END LOOP; END;2009/07/09尚洋信德存儲過程2.方式二:BEGIN FOR cur_result REVERSE 1.5 LOOP BEGIN DBMS_OUTPUT.PUT_LINE(cur_result );END; END LOOP; END;2009/07/09尚洋信德存儲過程存儲過程while循環Counter :=0; WHILE counter str2 thenresult :=1;ELSI

10、F str2 str1 THENresult := -1;ELSEresult := 0;END IF;2009/07/09尚洋信德存儲過程存儲過程if條件表達式比較符 =!=AND OR NOT(NOT) BETWEEN AND (NOT) IN ( , , , ,)IS (NOT) NULL(NOT) LIKE2009/07/09尚洋信德存儲過程 %type定義方法1. 在pl/sql中可以將變量和常量聲明為內建或用戶定義的數據類型,以引用一個列名,同時繼承他的數據類型和大小. 注釋: v_a number(5):=10; v_b v_a%type:=15; v_c v_a%type;20

11、09/07/09尚洋信德存儲過程游標屬性1.orcale 在對DML操作時會產生隱式游標.2.DML是指:insert、update、delete, select 的操作.3.隱式游標只使用sql%found, sql%notfound, sql%rowcount三個屬性.4.sql%found,sql%notfound是布爾值，sql%rowcount是整數值。5. sql%found為 true, sql%notfound為 false.6. sql%rowcount是返回當前位置為止游標讀取的記錄行數.7.在執行任何DML語句之前, sql%found,sql%notfound,sql%

12、rowcount的 值都是null. 2009/07/09尚洋信德存儲過程存儲過程控制語句的跳出 exit when eixt 注釋: exit 語句可立即結束循環 exit when 語句是在指定條件下結束循環,并且可以 出現在循環代碼中的任何位置. 2009/07/09尚洋信德存儲過程游標的使用1. loop 循環.create or replace procedure dept_procedure(a in varchar2,v_a out dept%rowtype)is-聲明游標cursor c_de(a in varchar2)is select tid from dept wher

13、e dname=a;begin-打開游標，對其中找到的記錄進行遍歷2009/07/09尚洋信德存儲過程open c_de(a);Loopfetch c_de into v_a;exit when c_de%notfound; / (exit when語句一定要緊跟在fetch之后。必避免多余的數據處理。) dbms_output.put_line(deptno:|v_a.deptno);dbms_output.put_line(dname:|v_a.dname);dbms_output.put_line(loc:|v_a.loc);end loop;close c_de;end;2009/07

14、/09尚洋信德存儲過程游標的使用2. for 循環.CURSOR cur_test ISSELECT p_number,p_name,p_manager,p_client FROM project WHERE p_name LIKE S% ; BEGIN INSERT INTO project VALUES (v_number,v_name,v_manager,v_client); FOR rec IN cur_test LOOP DBMS_OUTPUT.put_line(rec.p_number); DBMS_OUTPUT.put_line(rec.p_name); DBMS_OUTPUT.

15、put_line(rec.p_manager); DBMS_OUTPUT.put_line(rec.p_client); END LOOP; END; 2009/07/09尚洋信德存儲過程游標的使用3. while 循環.cursor c_postype(a in varchar2)is select bid ,bidname from dept where dname=a;open c_postype(a);begin fetch c_postype into v_postype,v_description; while c_postype%found loop fetch c_postyp

16、e into v_postype,v_description ; end loop;close c_postype;end;2009/07/09尚洋信德存儲過程儲存過程嵌套create or replace procedure innerBlock(p1 in varchar2) as o1 varchar2(10) := out1;inner1 varchar2(20); Begindbms_output.put_line(); begin inner1 :=inner1; exception when others then null; end; end; 2009/07/09尚洋信德存儲

17、過程儲存過程中建表 create or replace procedure skeleton as begin execute immediate create table table1(id number,name varchar2(20); End ;2009/07/09尚洋信德存儲過程存儲過程的返回值 Oracle 存儲過程的返回值,必須在創建一個存儲過程時定義返回值.2009/07/09尚洋信德存儲過程存儲過程的執行 Execute xxx; Execute xxx(值1,值2); Call xxx(值1,值2,值3); Select xxx(值1) from dual; 注釋: Se

18、lect xxx(值1) from dual 用法一般只在函數及包代碼中用到. 2009/07/09尚洋信德存儲過程常用存儲過程介紹Spcmpdaysum(sp_statdate,26)Spcmpmonthsum(sp_statdate)Spfnul2zero2009/07/09尚洋信德存儲過程開發一個存儲過程1.不帶參數的儲存過程create or replace procedure runbyparmeters begin if icount=1 then . else . end if; exception /存儲過程異常 when too_many_rows then DBMS_OUT

19、PUT.PUT_LINE(返回值多于1行); when others then DBMS_OUTPUT.PUT_LINE(在RUNBYPARMETERS過程中出錯！); end;2009/07/09尚洋信德存儲過程開發一個存儲過程 注釋:如果沒有or replace語句，則僅僅是新建一個存儲過程,如果系統存在該存儲過程，則會報錯。Create or replace procedure 如果系統中沒有此存儲過程就新建一個，如果系統中有此存儲過程則把原來刪除掉，重新創建一個存儲過程.存儲過程名定義：包括存儲過程名和參數列表,參數名和參數類型,參數名不能重復.as (is )為關鍵字,可以理解為pl

20、/sql的declare關鍵字,用于聲明變量.2009/07/09尚洋信德存儲過程開發一個存儲過程2.帶參數的儲存過程create or replace procedure runbyparmeters(isal in emp.sal%type, sname out varchar, sjob in out varchar) as icount number; begin select count(*) into icount from emp where salisal and job=sjob; if icount=1 then else end if; exception /存儲過程異常

21、 when too_many_rows then DBMS_OUTPUT.PUT_LINE(返回值多于1行); when others then DBMS_OUTPUT.PUT_LINE(在RUNBYPARMETERS過程中出錯！); end;2009/07/09尚洋信德存儲過程開發一個存儲過程注釋:sal%type目的是為了保持與傳參過來的數據寬度一致.IN /OUT 即可作輸入參數，也可作輸出參數。變量聲明塊：緊跟著的as (is )關鍵字,用于聲明變量。IN 按值傳遞，并且它不允許在存儲過程中被重新賦值。如果存儲過程的參數沒有指定存參數傳遞類型，默認為IN.OUT 參數：作為輸出參數，需

22、要注意，當一個參數被指定為OUT類型時，就算在調用存儲過程之前對該參數進行了賦值，在存儲過程中該參數的值仍然是null.IN參數的寬度是由外部決定,對于OUT 和IN OUT 參數的寬度是由存儲過程內部決定。2009/07/09尚洋信德存儲過程開發一個存儲過程3.參數的存儲過程默認值create or replace procedure procdefault(p1 varchar2, p2 varchar2 default mark) as begin dbms_output.put_line(p2); end; SQL exec procdefault(a); 或者SQL exec pro

23、cdefault2(p2 =aa);注釋: 1.可以通過default 關鍵字為存儲過程的參數指定默認值。在對存儲過程調用時，就可以省略默認值。 2. 默認值僅僅支持IN傳輸類型的參數。OUT 和 IN OUT不能指定默認值2009/07/09尚洋信德存儲過程存儲過程調用方式 1.方式一:Declare realsal emp.sal%type; realname varchar(40); realjob varchar(40); BEGIN realsal:=1100; realname:=; realjob:=CLERK; runbyparmeters(realsal,realname,r

24、ealjob); 必須按順序 DBMS_OUTPUT.PUT_LINE(REALNAME| |REALJOB); (輸出模式)END;2009/07/09尚洋信德存儲過程存儲過程調用方式2. 方式二:declare realsal emp.sal%type; realname varchar(40); realjob varchar(40); begin realsal:=1100; realname:=; realjob:=CLERK; runbyparmeters(sname=realname,isal=realsal,sjob=realjob); -指定值對應變量順序可變 DBMS_OU

25、TPUT.PUT_LINE(REALNAME| |REALJOB);(輸出模式)END; 2009/07/09尚洋信德存儲過程儲存過程調試declare param_out varchar2(28); param_inout varchar2(28); begin param_inout:=ff; proce_test(dd,param_out,param_inout); dbms_output.put_line(param_out); end; 注釋: 存儲過程調試主要根據開發者自己的決定,一般情況下通過打印語句來完成, 這里就不詳細說明.2009/07/09尚洋信德存儲過程系統異常錯誤信息

26、ACCESS_INTO_NULL 試圖給為初始化對象的屬性賦值 CASE_NOT_FOUND CASE 中若未包含相應的 WHEN ，并且沒有設置 COLLECTION_IS_NULL 試圖向為初始化的嵌套表和變長數組賦值時,引發異常CURSER_ALREADY_OPEN 試圖打開一個已經打開的游標時產生異常DUP_VAL_ON_INDEX 唯一索引對應的列上有重復的值 INVALID_CURSOR 在不合法的游標上進行操作 INVALID_NUMBER 內嵌的 SQL 語句不能將字符轉換為數字 NO_DATA_FOUND 使用 select into 未返回行，或企圖在表中訪問為初始化的數據

27、 TOO_MANY_ROWS 執行 select into 時，結果集超過一行 ZERO_DIVIDE 試圖用0 除某個數字SUBSCRIPT_BEYOND_COUNT 元素下標超過嵌套表或 VARRAY 的最大值 SUBSCRIPT_OUTSIDE_LIMIT 試圖使用嵌套表或 VARRAY 時，將下標指定為負數 2009/07/09尚洋信德存儲過程系統異常錯誤信息VALUE_ERROR 發生算術,轉換,截斷或大小約束錯誤.LOGIN_DENIED PL/SQL 應用程序連接到 oracle 數據庫時，提供了不正確的用戶名 或密碼 NOT_LOGGED_ON PL/SQL 應用程序在試圖連接

28、數據庫之前訪問數據庫中的數據PROGRAM_ERROR PL/SQL 內部問題，可能需要重裝數據字典pl./SQL 系統包 ROWTYPE_MISMATCH 宿主游標變量與 PL/SQL 游標變量的返回類型不兼容 SELF_IS_NULL 使用對象類型時，在 null 對象上調用對象方法 STORAGE_ERROR 運行 PL/SQL 時，內存用盡或者內存出現問題SYS_INVALID_ID 無效的 ROWID 字符串 TIMEOUT_ON_RESOURCE 當數據庫等待某資源時超時 2009/07/09尚洋信德存儲過程ORACLE 和和informix 存儲過程區別存儲過程區別1.建立存儲過

29、程的語法Oracle:create or replace procedure procedue_name (arg1 in | out | in out type(argn in | out | in out type,) is | as 變量定義區 begin end procedure_name;Informix:create procedure proc_name( .in_parameter_list)returning out_para_list / out_result_set;2009/07/09尚洋信德存儲過程ORACLE 和和informix 存儲過程區別存儲過程區別2.沒有

30、參數也沒有返回值Oracle:create or replace procedure pNoParam as begin delete from t1; commit; end; end pNoParam;Informix: 2009/07/09尚洋信德存儲過程create procedure pNoParam()Returning int begin on exception rollback work;end exception begin work; delete from t1;commit work; return; end; end procedure;2009/07/09尚洋信德

31、存儲過程ORACLE 和和informix 存儲過程區別存儲過程區別3.返回記錄集Oracle: procedure pReturnSet (RefCursor out varchar2, Refdefine out varchar2) as localCursor TestRefCursorTyp; localnumber TestRefCursorTyp; begin select f1, f2 into localCursor , localnumber from t1; RefCursor := localCursor; Refdefine := localnumber;end pRe

32、turnSet; 2009/07/09尚洋信德存儲過程Informix:create procedure pReturnSet() returning integer; define i integer; define j varchar(10); beginforeach select f1, f2 into i, j from t1 end foreach; return i, j; End ;end procedure;2009/07/09尚洋信德存儲過程ORACLE 和和informix 存儲過程區別存儲過程區別4.錯誤捕捉Oracle:Exceptionwhen others the

33、nDBMS_OUTPUT.PUT_LINE(在XX過程中出錯！);Informix:ON EXCEPTION SET sp_errsql, sp_errisam, sp_errstr IF (sp_errsql!=0) THEN RAISE EXCEPTION -746,sp_errisam,錯誤碼:|sp_errsql|; END IF; END EXCEPTION;2009/07/09尚洋信德存儲過程ORACLE 和和informix 存儲過程區別存儲過程區別5.對游標的處理Oracle:create or replace procedure pHasCursor as v_f1 numb

34、er(10,0); cursor curt1 is select f1 from t1 for update ; begin open curt1; loop fetch curt1 Into v_f1; exit when curt1%notfound; end loop; Close curt1;End;2009/07/09尚洋信德存儲過程Informix:create procedure pHasCursor() define v_f1 integer； begin foreach select f1 into v_f1 from t1 - 注意這里沒有分號 if (v_f1 = 1)

35、then update t1 set f2 = one where f3=1001; end if; end foreach;End ;End pHasCursor;2009/07/09尚洋信德存儲過程ORACLE 和和informix 存儲過程區別存儲過程區別6.打印調試信息的處理打印調試信息的處理Oracle: DBMS_OUTPUT.PUT_LINE(開始執行存儲過程開始執行存儲過程); DBMS_OUTPUT.PUT_LINE(v_date=|v_date); DBMS_OUTPUT.PUT_LINE(存儲過程執行完畢存儲過程執行完畢); Informix:set debug file

36、 to trace_check; - with append; 說明說明“with append”表示以追加模式打開跟蹤結果文表示以追加模式打開跟蹤結果文trace 開始執行存儲過程開始執行存儲過程 trace v_date=|v_date; trace 存儲過程執行完畢存儲過程執行完畢 trace off;2009/07/09尚洋信德存儲過程ORACLE 和和informix 存儲過程區別存儲過程區別7.關于參數的說明 注釋: 如果存儲過程想返回一個參數,在informix中是通過返回值的形式實現的,而在oracle是通過輸出參數或者輸入輸出參數實現的.Oracle:create or re

37、place procedure p1(x out number) as begin x := 0; end p1;Informix:create procedure p1() returning integer; return 0;2009/07/09尚洋信德存儲過程存儲過程ORACLE 和和informix 存儲過程區別存儲過程區別8.存儲過程中調用另一個存儲過程Oracle: Call pNoParam; Call pNormalParam(1, a, v_Result); Informix:spNoParam()pNormalParam(1, a) returning v_Result;

38、2009/07/09尚洋信德存儲過程存儲過程ORACLE 和和informix 存儲過程區別存儲過程區別9.if 使用Oracle:IF str1 str2 thenresult :=1;ELSIF str2 str1 THENresult := -1;ELSEresult := 0;END IFInformix:2009/07/09尚洋信德存儲過程 IF str1 str2 thenresult =1;ELIF str2 str1 THENresult = -1;ELSEresult = 0;END IF2009/07/09尚洋信德存儲過程ORACLE 和和informix 存儲過程區別存儲過程區別10.賦值 Oracle: v_1 := 100; Informix: let v_1 = 100;2009/07/09尚洋信德存儲過程練習創建一個存儲過程, 使用游標從dept表中取BID為010開頭的記錄信息, 把它往表t01_psn中插入一條記錄,并且循環打印插入的數據記錄.2009/07/09尚洋信德函數1. 函數結構 CREATE OR REPLACE FUNCTION 函數名(參數1 IN NUMBER, 參數2 IN NUMBER )RETURN 類型 IS | ASBEGIN FUNCTION _BODYEND函數名; 注釋: 函數與存儲過程相似,唯一