oracle存儲過程培訓(xùn)材料(動畫版本)

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

2、 DATE;BEGINEND存儲過程程名字;2009/07/09尚洋信德德存儲過程程創(chuàng)建一個個簡單存存儲過程程的方式式1、1.從Window打開SQL*Plus并且從SQL*Plus登錄到你你的數(shù)據(jù)據(jù)庫;打開skeleton.sql文件.2.在SQL命令提示示符下輸輸入以下下命令：SQLskeleton注釋:(SQL*Plus裝載skeleton.sql文件的內(nèi)內(nèi)容到SQL*Plus緩沖區(qū),并且執(zhí)行行SQL*Plus語句;SQL*Plus會通知你你存儲過過程已經(jīng)經(jīng)被成功功地創(chuàng)建建)3.寫一個存存儲過程程,實例:CREATEORREPLACEPROCEDUREskeletonISBEGINDBM

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

4、令：命令: SQLDROPPROCEDURE存儲過程程名;2009/07/09尚洋信德德存儲過程程存儲過程程的注釋釋-注釋一行行2009/07/09尚洋信德德存儲過程程存儲過程程入?yún)⑴c與返回值值createorreplaceprocedurerunbyparmeters(isal in emp.sal%type,snameoutvarchar,sjobinoutvarchar)asicountnumber;beginificount=1then.else.endif;end;2009/07/09尚洋信德德存儲過程程存儲過程程變量定定義、常常用變量量類型realsalemp.sal%type;r

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

6、影響本本Beginend。變量聲明明必須在在存儲過過程開頭頭或者Beginend塊的開頭頭部分。2009/07/09尚洋信德德存儲過程程存儲過程程操作符符+-*/|合并如如：sp_str1=“ASD”|”ERT”,則：sp_str1=“ASDERT”2009/07/09尚洋信德德存儲過程程存儲過程程異?？乜刂芿xceptionwhen too_many_rowsthenDBMS_OUTPUT.PUT_LINE(返回值多多于1行);when othersthenDBMS_OUTPUT.PUT_LINE(在RUNBYPARMETERS過程中出出錯！);2009/07/09尚洋信德德存儲過程程存儲過

7、程程結(jié)構(gòu)塊塊BEGIN第一步處處理；END;BEGIN第二步處處理；END;BEGIN第三步處處理；END;注釋:存儲過程程BEGINEND之間可以以進行嵌嵌套。2009/07/09尚洋信德德存儲過程程存儲過程程游標(biāo)1.帶參數(shù)的的游標(biāo)CURSORC_USER(C_ID NUMBER)ISSELECTNAMEFROMUSERWHERETYPEID=C_ID NUMBER;OPENC_USER(變量值);LOOPFETCHC_USERINTOV_NAME;EXIT WHENC_USER%NOTFOUND;ENDLOOP;CLOSEC_USER;2009/07/09尚洋信德德存儲過程程2.不帶參數(shù)數(shù)

8、的游標(biāo)標(biāo)CURSORC_USERISSELECTNAMEFROMUSERWHERETYPEID=C_ID;OPEN C_USER;LOOPFETCHC_USERINTOV_NAME;EXIT WHENC_USER%NOTFOUND;ENDLOOP;CLOSEC_USER;2009/07/09尚洋信德德存儲過程程存儲過程程LOOP循環(huán)Counter:=0;LOOPcounter :=counter+1;EXIT WHENcounter=5;ENDLOOP;2009/07/09尚洋信德德存儲過程程存儲過程程for循環(huán)1.方式一:BEGINFORcur_resultincurLOOPBEGINV_S

9、UM:=cur_result.列名1+cur_result.列名2 ;END;ENDLOOP;END;2009/07/09尚洋信德德存儲過程程2.方式二:BEGINFORcur_resultREVERSE1.5LOOPBEGINDBMS_OUTPUT.PUT_LINE(cur_result );END;ENDLOOP;END;2009/07/09尚洋信德德存儲過程程存儲過程程while循環(huán)Counter:=0;WHILEcounter str2thenresult:=1;ELSIFstr2 str1THENresult:=-1;ELSEresult:=0;ENDIF;2009/07/09尚洋信

10、德德存儲過程程存儲過程程if條件表達達式比較符=!=ANDORNOT(NOT)BETWEENAND(NOT)IN( , ,)IS(NOT)NULL(NOT)LIKE2009/07/09尚洋信德德存儲過程程%type定義方法法1.在pl/sql中可以將將變量和和常量聲聲明為內(nèi)內(nèi)建或用用戶定義義的數(shù)據(jù)據(jù)類型,以引用一一個列名名,同時繼承承他的數(shù)數(shù)據(jù)類型型和大小小.注釋:v_anumber(5):=10;v_bv_a%type:=15;v_cv_a%type;2009/07/09尚洋信德德存儲過程程游標(biāo)屬性性1.orcale在對DML操作時會會產(chǎn)生隱隱式游標(biāo)標(biāo).2.DML是指:insert、upda

11、te、delete,select的操作.3.隱式游標(biāo)標(biāo)只使用用sql%found, sql%notfound,sql%rowcount三個屬性性.4.sql%found,sql%notfound是布爾值值，sql%rowcount是整數(shù)值值。5.sql%found為true,sql%notfound為false.6.sql%rowcount是返回當(dāng)當(dāng)前位置置為止游游標(biāo)讀取取的記錄錄行數(shù).7.在執(zhí)行任任何DML語句之前前, sql%found,sql%notfound,sql%rowcount的值都是null.2009/07/09尚洋信德德存儲過程程存儲過程程控制語語句的跳跳出exit when

12、eixt注釋:exit語句可立立即結(jié)束束循環(huán)exitwhen語句是在在指定條條件下結(jié)結(jié)束循環(huán)環(huán),并且可以以出現(xiàn)在循循環(huán)代碼碼中的任任何位置置.2009/07/09尚洋信德德存儲過程程游標(biāo)的使使用1.loop循環(huán).createorreplaceproceduredept_procedure(ainvarchar2,v_aout dept%rowtype)is-聲明游標(biāo)標(biāo)cursorc_de(ainvarchar2)isselecttidfromdept where dname=a;begin-打開游標(biāo)標(biāo)，對其其中找到到的記錄錄進行遍遍歷2009/07/09尚洋信德德存儲過程程open c_de(

13、a);Loopfetchc_deintov_a;exit whenc_de%notfound;/(exitwhen語句一定定要緊跟跟在fetch之后。必必避免多多余的數(shù)數(shù)據(jù)處理理。)dbms_output.put_line(deptno:|v_a.deptno);dbms_output.put_line(dname:|v_a.dname);dbms_output.put_line(loc:|v_a.loc);endloop;closec_de;end;2009/07/09尚洋信德德存儲過程程游標(biāo)的使使用2.for循環(huán).CURSORcur_testISSELECTp_number,p_name,

14、p_manager,p_clientFROM projectWHEREp_name LIKES%;BEGININSERTINTOproject VALUES(v_number,v_name,v_manager,v_client);FORrec IN cur_test LOOPDBMS_OUTPUT.put_line(rec.p_number);DBMS_OUTPUT.put_line(rec.p_name);DBMS_OUTPUT.put_line(rec.p_manager);DBMS_OUTPUT.put_line(rec.p_client);ENDLOOP;END;2009/07/09尚

15、洋信德德存儲過程程游標(biāo)的使使用3.while循環(huán).cursorc_postype(a in varchar2)isselectbid,bidnamefromdeptwheredname=a;openc_postype(a);beginfetchc_postypeintov_postype,v_description;whilec_postype%foundloopfetchc_postypeintov_postype,v_description;endloop;closec_postype;end;2009/07/09尚洋信德德存儲過程程儲存過程程嵌套createorreplaceproced

16、ureinnerBlock(p1invarchar2)aso1varchar2(10) := out1;inner1varchar2(20);Begindbms_output.put_line();begininner1:=inner1;exceptionwhen othersthennull;end;end;2009/07/09尚洋信德德存儲過程程儲存過程程中建表表createorreplaceprocedureskeletonas beginexecute immediatecreate table table1(idnumber,namevarchar2(20);End;2009/07/

17、09尚洋信德德存儲過程程存儲過程程的返回回值Oracle存儲過程程的返回回值,必須在創(chuàng)創(chuàng)建一個個存儲過過程時定定義返回回值.2009/07/09尚洋信德德存儲過程程存儲過程程的執(zhí)行行Executexxx;Executexxx(值1,值2);Callxxx(值1,值2,值3);Selectxxx(值1)from dual;注釋:Selectxxx(值1)from dual用法一般般只在函函數(shù)及包包代碼中中用到.2009/07/09尚洋信德德存儲過程程常用存儲儲過程介介紹Spcmpdaysum(sp_statdate,26)Spcmpmonthsum(sp_statdate)Spfnul2zero

18、2009/07/09尚洋信德德存儲過程程開發(fā)一個個存儲過過程1.不帶參數(shù)數(shù)的儲存存過程createorreplaceprocedurerunbyparmetersbeginificount=1then.else.endif;exception/存儲過程程異常when too_many_rowsthenDBMS_OUTPUT.PUT_LINE(返回值多多于1行);when othersthenDBMS_OUTPUT.PUT_LINE(在RUNBYPARMETERS過程中出出錯！);end;2009/07/09尚洋信德德存儲過程程開發(fā)一個個存儲過過程注釋:如果沒有有orreplace語句，則則僅僅

19、是是新建一一個存儲儲過程,如果系統(tǒng)統(tǒng)存在該該存儲過過程，則則會報錯錯。Createorreplaceprocedure如果系統(tǒng)統(tǒng)中沒有有此存儲儲過程就就新建一一個，如如果系統(tǒng)統(tǒng)中有此此存儲過過程則把把原來刪刪除掉，重新創(chuàng)創(chuàng)建一個個存儲過過程.存儲過程程名定義義：包括括存儲過過程名和和參數(shù)列列表,參數(shù)名和和參數(shù)類類型,參數(shù)名不不能重復(fù)復(fù).as(is)為關(guān)鍵字字,可以理解解為pl/sql的declare關(guān)鍵字,用于聲明明變量.2009/07/09尚洋信德德存儲過程程開發(fā)一個個存儲過過程2.帶參數(shù)的的儲存過過程createorreplaceprocedurerunbyparmeters(isalin

20、emp.sal%type,snameout varchar, sjobinout varchar)asicountnumber;beginselectcount(*)intoicountfromempwheresalisaland job=sjob;ificount=1thenelseendif;exception/存儲過程程異常when too_many_rowsthenDBMS_OUTPUT.PUT_LINE(返回值多多于1行);when othersthenDBMS_OUTPUT.PUT_LINE(在RUNBYPARMETERS過程中出出錯！);end;2009/07/09尚洋信德德存儲

21、過程程開發(fā)一個個存儲過過程注釋:sal%type目的是為為了保持持與傳參參過來的的數(shù)據(jù)寬寬度一致致.IN/OUT即可作輸輸入?yún)?shù)數(shù)，也可可作輸出出參數(shù)。變量聲明明塊：緊緊跟著的的as(is)關(guān)鍵字,用于聲明明變量。IN按值傳遞遞，并且且它不允允許在存存儲過程程中被重重新賦值值。如果果存儲過過程的參參數(shù)沒有有指定存存參數(shù)傳傳遞類型型，默認(rèn)認(rèn)為IN.OUT參數(shù)：作作為輸出出參數(shù)，需要注注意，當(dāng)當(dāng)一個參參數(shù)被指指定為OUT類型時，就算在在調(diào)用存存儲過程程之前對對該參數(shù)數(shù)進行了了賦值，在存儲儲過程中中該參數(shù)數(shù)的值仍仍然是null.IN參數(shù)的寬寬度是由由外部決決定,對于OUT和INOUT參數(shù)的寬寬度是由

22、由存儲過過程內(nèi)部部決定。2009/07/09尚洋信德德存儲過程程開發(fā)一個個存儲過過程3.參數(shù)的存存儲過程程默認(rèn)值值createorreplaceprocedureprocdefault(p1varchar2, p2 varchar2 defaultmark)asbegindbms_output.put_line(p2);end;SQL execprocdefault(a);或者SQL execprocdefault2(p2 =aa);注釋:1.可以通過過default關(guān)鍵字為為存儲過過程的參參數(shù)指定定默認(rèn)值值。在對對存儲過過程調(diào)用用時，就就可以省省略默認(rèn)認(rèn)值。2.默認(rèn)值僅僅僅支持持IN傳輸類型

23、型的參數(shù)數(shù)。OUT和INOUT不能指定定默認(rèn)值值2009/07/09尚洋信德德存儲過程程存儲過程程調(diào)用方方式1.方式一:Declarerealsalemp.sal%type;realnamevarchar(40);realjobvarchar(40);BEGINrealsal:=1100;realname:=;realjob:=CLERK;runbyparmeters(realsal,realname,realjob);必須須按順序序DBMS_OUTPUT.PUT_LINE(REALNAME|REALJOB);(輸出模式式)END;2009/07/09尚洋信德德存儲過程程存儲過程程調(diào)用方方式2

24、.方式二:declarerealsal emp.sal%type;realnamevarchar(40);realjob varchar(40);beginrealsal:=1100;realname:=;realjob:=CLERK;runbyparmeters(sname=realname,isal=realsal,sjob=realjob);-指定值對對應(yīng)變量量順序可可變DBMS_OUTPUT.PUT_LINE(REALNAME|REALJOB);(輸出模式式)END;2009/07/09尚洋信德德存儲過程程儲存過程程調(diào)試declareparam_outvarchar2(28);para

25、m_inoutvarchar2(28);beginparam_inout:=ff;proce_test(dd,param_out,param_inout);dbms_output.put_line(param_out);end;注釋:存儲過程程調(diào)試主主要根據(jù)據(jù)開發(fā)者者自己的的決定,一般情況況下通過過打印語語句來完完成,這里就不不詳細說說明.2009/07/09尚洋信德德存儲過程程系統(tǒng)異常常錯誤信信息ACCESS_INTO_NULL試圖給為為初始化化對象的的屬性賦賦值CASE_NOT_FOUNDCASE中若未包包含相應(yīng)應(yīng)的WHEN，并且沒沒有設(shè)置置COLLECTION_IS_NULL試圖向為為初

26、始化化的嵌套套表和變變長數(shù)組組賦值時時,引發(fā)異常常CURSER_ALREADY_OPEN試圖打開開一個已已經(jīng)打開開的游標(biāo)標(biāo)時產(chǎn)生生異常DUP_VAL_ON_INDEX唯一索引引對應(yīng)的的列上有有重復(fù)的的值INVALID_CURSOR在不合法法的游標(biāo)標(biāo)上進行行操作INVALID_NUMBER內(nèi)嵌的SQL語句不能能將字符符轉(zhuǎn)換為為數(shù)字NO_DATA_FOUND使用selectinto未返回行行，或企企圖在表表中訪問問為初始始化的數(shù)數(shù)據(jù)TOO_MANY_ROWS執(zhí)行selectinto時，結(jié)果果集超過過一行ZERO_DIVIDE試圖用0除某個數(shù)數(shù)字SUBSCRIPT_BEYOND_COUNT元素下標(biāo)標(biāo)

27、超過嵌嵌套表或或VARRAY的最大值值SUBSCRIPT_OUTSIDE_LIMIT試圖使用用嵌套表表或VARRAY時，將下下標(biāo)指定定為負(fù)數(shù)數(shù)2009/07/09尚洋信德德存儲過程程系統(tǒng)異常常錯誤信信息VALUE_ERROR發(fā)生算術(shù)術(shù),轉(zhuǎn)換,截斷或大大小約束束錯誤.LOGIN_DENIEDPL/SQL應(yīng)用程序序連接到到oracle數(shù)據(jù)庫時時，提供供了不正正確的用用戶名或密碼NOT_LOGGED_ONPL/SQL應(yīng)用程序序在試圖圖連接數(shù)數(shù)據(jù)庫之之前訪問問數(shù)據(jù)庫庫中的數(shù)數(shù)據(jù)PROGRAM_ERRORPL/SQL內(nèi)部問題題，可能能需要重重裝數(shù)據(jù)據(jù)字典pl./SQL系統(tǒng)包ROWTYPE_MISMATC

28、H宿主游標(biāo)標(biāo)變量與與PL/SQL游標(biāo)變量量的返回回類型不不兼容SELF_IS_NULL使用對象象類型時時，在null對象上調(diào)調(diào)用對象象方法STORAGE_ERROR運行PL/SQL時，內(nèi)存存用盡或或者內(nèi)存存出現(xiàn)問問題SYS_INVALID_ID無效的ROWID字符串TIMEOUT_ON_RESOURCE當(dāng)數(shù)據(jù)庫庫等待某某資源時時超時2009/07/09尚洋信德德存儲過程程ORACLE和informix存儲過程程區(qū)別1.建立存儲儲過程的的語法Oracle:createor replace procedureprocedue_name (arg1 in |out |inout type(argn

29、in| out| in outtype,)is|as變量定義義區(qū)beginendprocedure_name;Informix:createprocedure proc_name(.in_parameter_list)returningout_para_list/ out_result_set;2009/07/09尚洋信德德存儲過程程ORACLE和informix存儲過程程區(qū)別2.沒有參數(shù)數(shù)也沒有有返回值值Oracle:createorreplaceprocedurepNoParamasbegindeletefromt1;commit;end;endpNoParam;Informix:2009

30、/07/09尚洋信德德存儲過程程createprocedure pNoParam()Returningintbeginonexceptionrollbackwork;endexceptionbeginwork;deletefromt1;commitwork;return;end;endprocedure;2009/07/09尚洋信德德存儲過程程ORACLE和informix存儲過程程區(qū)別3.返回記錄錄集Oracle:procedurepReturnSet(RefCursoroutvarchar2,Refdefineoutvarchar2)as localCursorTestRefCursorT

31、yp;localnumberTestRefCursorTyp;beginselectf1, f2into localCursor ,localnumberfrom t1;RefCursor:=localCursor;Refdefine:= localnumber;endpReturnSet;2009/07/09尚洋信德德存儲過程程Informix:createprocedure pReturnSet()returninginteger;defineiinteger;definejvarchar(10);beginforeachselectf1, f2 intoi, jfrom t1endfor

32、each;returni, j;End;endprocedure;2009/07/09尚洋信德德存儲過程程ORACLE和informix存儲過程程區(qū)別4.錯誤捕捉捉Oracle:Exceptionwhen othersthenDBMS_OUTPUT.PUT_LINE(在XX過程中出出錯！);Informix:ONEXCEPTIONSET sp_errsql,sp_errisam,sp_errstrIF(sp_errsql!=0)THENRAISEEXCEPTION-746,sp_errisam,錯誤碼:|sp_errsql|;ENDIF;ENDEXCEPTION;2009/07/09尚洋信德德

33、存儲過程程ORACLE和informix存儲過程程區(qū)別5.對游標(biāo)的的處理Oracle:createorreplaceprocedurepHasCursorasv_f1number(10,0);cursorcurt1isselectf1fromt1forupdate;beginopen curt1;loopfetchcurt1Intov_f1;exit whencurt1%notfound;endloop;Closecurt1;End;2009/07/09尚洋信德德存儲過程程Informix:createprocedure pHasCursor()definev_f1integer；beginf

34、oreachselectf1intov_f1from t1-注意這里里沒有分分號if(v_f1= 1) thenupdatet1setf2=onewheref3=1001;endif;endforeach;End;EndpHasCursor;2009/07/09尚洋信德德存儲過程程ORACLE和informix存儲過程程區(qū)別6.打印調(diào)試試信息的的處理Oracle:DBMS_OUTPUT.PUT_LINE(開始執(zhí)行行存儲過過程);DBMS_OUTPUT.PUT_LINE(v_date=|v_date);DBMS_OUTPUT.PUT_LINE(存儲過程程執(zhí)行完完畢);Informix:setde

35、bugfiletotrace_check;-with append;說明“with append”表示以追追加模式式打開跟跟蹤結(jié)果果文trace開始執(zhí)行行存儲過過程tracev_date=|v_date;trace存儲過程程執(zhí)行完完畢traceoff;2009/07/09尚洋信德德存儲過程程ORACLE和informix存儲過程程區(qū)別7.關(guān)于參數(shù)數(shù)的說明明注釋:如果存儲儲過程想想返回一一個參數(shù)數(shù),在informix中是通過過返回值值的形式式實現(xiàn)的的,而在oracle是通過輸輸出參數(shù)數(shù)或者輸輸入輸出出參數(shù)實實現(xiàn)的.Oracle:createorreplaceprocedurep1(xoutnum

36、ber)asbeginx := 0;endp1;Informix:createprocedure p1()returning integer;return0;2009/07/09尚洋信德德存儲過程程ORACLE和informix存儲過程程區(qū)別8.存儲過程程中調(diào)用用另一個個存儲過過程Oracle:CallpNoParam;CallpNormalParam(1,a,v_Result);Informix:spNoParam()pNormalParam(1,a)returningv_Result;2009/07/09尚洋信德德存儲過程程ORACLE和informix存儲過程程區(qū)別9.if使用Oracl

37、e:IFstr1 str2thenresult:=1;ELSIFstr2 str1THENresult:=-1;ELSEresult:=0;ENDIFInformix:2009/07/09尚洋信德德存儲過程程IFstr1 str2thenresult=1;ELIF str2str1 THENresult=-1;ELSEresult=0;ENDIF2009/07/09尚洋信德德存儲過程程ORACLE和informix存儲過程程區(qū)別10.賦值Oracle:v_1:=100;Informix:letv_1 =100;2009/07/09尚洋信德德存儲過程程練習(xí)創(chuàng)建一個個存儲過過程,使用游標(biāo)標(biāo)從dept表中取BID為010開頭的記記錄信息息,把它往表表t01_psn中插入一一條記錄錄,并且循環(huán)環(huán)打印插插入的數(shù)數(shù)據(jù)記錄錄.2009/07/09尚洋信德德函數(shù)1.函數(shù)結(jié)構(gòu)構(gòu)CREATEORREPLACEFUNCTION函數(shù)名(參數(shù)1 IN NUMBER,參數(shù)2 IN NUMBER)RETURN類型IS| ASBEGINFUNCTION_BODYEND函數(shù)名;注釋:函數(shù)與存存儲過程程相似,唯一區(qū)別別函數(shù)