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

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

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

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

4、信德存儲過程存儲過程的注釋- 注釋一行2009/07/09尚洋信德存儲過程存儲過程入?yún)⑴c返回值 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; realname varchar2(40); realj

5、ob 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 塊內(nèi)聲明的變量影響本Begin end 。變量聲明必須在存儲

6、過程開頭或者Begin end 塊的開頭部分。2009/07/09尚洋信德存儲過程存儲過程操作符+-*/| 合并 如：sp_str1=“ASD”|”ERT” ,則： sp_str1=“ASDERT” 2009/07/09尚洋信德存儲過程存儲過程異?？刂芿xception when too_many_rows then DBMS_OUTPUT.PUT_LINE(返回值多于1行); when others then DBMS_OUTPUT.PUT_LINE(在RUNBYPARMETERS過程中出錯！);2009/07/09尚洋信德存儲過程存儲過程結(jié)構(gòu)塊BEGIN 第一步處理；END;BEGIN 第

7、二步處理；END;BEGIN 第三步處理；END; 注釋: 存儲過程BEGIN END 之間可以進行嵌套。2009/07/09尚洋信德存儲過程存儲過程游標1.帶參數(shù)的游標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尚洋信德存儲過程2.不帶參數(shù)的游標CURSOR C_

8、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循環(huán) Counter :=0; LOOP counter :=counter +1 ; EXIT WHEN counter =5; END LOOP;2009/07/09尚洋信德存儲過程存儲過程for循環(huán)1.方式一:BEGIN FOR cur_result

9、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循環(huán)Counter :=0; WHILE counter str2 thenresult :=1;ELSIF str2 str1 THENresult

10、:= -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中可以將變量和常量聲明為內(nèi)建或用戶定義的數(shù)據(jù)類型,以引用一個列名,同時繼承他的數(shù)據(jù)類型和大小. 注釋: v_a number(5):=10; v_b v_a%type:=15; v_c v_a%type;2009/07/09尚洋信德存儲過程游標屬性1.o

11、rcale 在對DML操作時會產(chǎn)生隱式游標.2.DML是指:insert、update、delete, select 的操作.3.隱式游標只使用sql%found, sql%notfound, sql%rowcount三個屬性.4.sql%found,sql%notfound是布爾值，sql%rowcount是整數(shù)值。5. sql%found為 true, sql%notfound為 false.6. sql%rowcount是返回當前位置為止游標讀取的記錄行數(shù).7.在執(zhí)行任何DML語句之前, sql%found,sql%notfound,sql%rowcount的 值都是null. 2009

12、/07/09尚洋信德存儲過程存儲過程控制語句的跳出 exit when eixt 注釋: exit 語句可立即結(jié)束循環(huán) exit when 語句是在指定條件下結(jié)束循環(huán),并且可以 出現(xiàn)在循環(huán)代碼中的任何位置. 2009/07/09尚洋信德存儲過程游標的使用1. loop 循環(huán).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 where dname=a;begin-打開游標，對其

13、中找到的記錄進行遍歷2009/07/09尚洋信德存儲過程open c_de(a);Loopfetch c_de into v_a;exit when c_de%notfound; / (exit when語句一定要緊跟在fetch之后。必避免多余的數(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);end loop;close c_de;end;2009/07/09尚洋信德存儲過程游標的使用2. for

14、循環(huán).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.put_line(rec.p_manager)

15、; DBMS_OUTPUT.put_line(rec.p_client); END LOOP; END; 2009/07/09尚洋信德存儲過程游標的使用3. while 循環(huán).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_postype into v_postype,v_desc

16、ription ; 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尚洋信德存儲過程儲存過程中建表 create or rep

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

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

19、when others then DBMS_OUTPUT.PUT_LINE(在RUNBYPARMETERS過程中出錯！); end;2009/07/09尚洋信德存儲過程開發(fā)一個存儲過程 注釋:如果沒有or replace語句，則僅僅是新建一個存儲過程,如果系統(tǒng)存在該存儲過程，則會報錯。Create or replace procedure 如果系統(tǒng)中沒有此存儲過程就新建一個，如果系統(tǒng)中有此存儲過程則把原來刪除掉，重新創(chuàng)建一個存儲過程.存儲過程名定義：包括存儲過程名和參數(shù)列表,參數(shù)名和參數(shù)類型,參數(shù)名不能重復.as (is )為關(guān)鍵字,可以理解為pl/sql的declare關(guān)鍵字,用于聲明變量.

20、2009/07/09尚洋信德存儲過程開發(fā)一個存儲過程2.帶參數(shù)的儲存過程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 /存儲過程異常 when too_many_rows the

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

22、調(diào)用存儲過程之前對該參數(shù)進行了賦值，在存儲過程中該參數(shù)的值仍然是null.IN參數(shù)的寬度是由外部決定,對于OUT 和IN OUT 參數(shù)的寬度是由存儲過程內(nèi)部決定。2009/07/09尚洋信德存儲過程開發(fā)一個存儲過程3.參數(shù)的存儲過程默認值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 procdefault2(p2 =aa);注釋: 1

23、.可以通過default 關(guān)鍵字為存儲過程的參數(shù)指定默認值。在對存儲過程調(diào)用時，就可以省略默認值。 2. 默認值僅僅支持IN傳輸類型的參數(shù)。OUT 和 IN OUT不能指定默認值2009/07/09尚洋信德存儲過程存儲過程調(diào)用方式 1.方式一:Declare realsal emp.sal%type; realname varchar(40); realjob varchar(40); BEGIN realsal:=1100; realname:=; realjob:=CLERK; runbyparmeters(realsal,realname,realjob); 必須按順序 DBMS_OUT

24、PUT.PUT_LINE(REALNAME| |REALJOB); (輸出模式)END;2009/07/09尚洋信德存儲過程存儲過程調(diào)用方式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); -指定值對應(yīng)變量順序可變 DBMS_OUTPUT.PUT_LINE(REALNAME|

25、 |REALJOB);(輸出模式)END; 2009/07/09尚洋信德存儲過程儲存過程調(diào)試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; 注釋: 存儲過程調(diào)試主要根據(jù)開發(fā)者自己的決定,一般情況下通過打印語句來完成, 這里就不詳細說明.2009/07/09尚洋信德存儲過程系統(tǒng)異常錯誤信息ACCESS_INTO_NULL 試圖給為初始

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

27、t into 時，結(jié)果集超過一行 ZERO_DIVIDE 試圖用0 除某個數(shù)字SUBSCRIPT_BEYOND_COUNT 元素下標超過嵌套表或 VARRAY 的最大值 SUBSCRIPT_OUTSIDE_LIMIT 試圖使用嵌套表或 VARRAY 時，將下標指定為負數(shù) 2009/07/09尚洋信德存儲過程系統(tǒng)異常錯誤信息VALUE_ERROR 發(fā)生算術(shù),轉(zhuǎn)換,截斷或大小約束錯誤.LOGIN_DENIED PL/SQL 應(yīng)用程序連接到 oracle 數(shù)據(jù)庫時，提供了不正確的用戶名 或密碼 NOT_LOGGED_ON PL/SQL 應(yīng)用程序在試圖連接數(shù)據(jù)庫之前訪問數(shù)據(jù)庫中的數(shù)據(jù)PROGRAM_E

28、RROR PL/SQL 內(nèi)部問題，可能需要重裝數(shù)據(jù)字典pl./SQL 系統(tǒng)包 ROWTYPE_MISMATCH 宿主游標變量與 PL/SQL 游標變量的返回類型不兼容 SELF_IS_NULL 使用對象類型時，在 null 對象上調(diào)用對象方法 STORAGE_ERROR 運行 PL/SQL 時，內(nèi)存用盡或者內(nèi)存出現(xiàn)問題SYS_INVALID_ID 無效的 ROWID 字符串 TIMEOUT_ON_RESOURCE 當數(shù)據(jù)庫等待某資源時超時 2009/07/09尚洋信德存儲過程ORACLE 和和informix 存儲過程區(qū)別存儲過程區(qū)別1.建立存儲過程的語法Oracle:create or re

29、place procedure procedue_name (arg1 in | out | in out type(argn in | out | in out type,) is | as 變量定義區(qū) begin end procedure_name;Informix:create procedure proc_name( .in_parameter_list)returning out_para_list / out_result_set;2009/07/09尚洋信德存儲過程ORACLE 和和informix 存儲過程區(qū)別存儲過程區(qū)別2.沒有參數(shù)也沒有返回值Oracle:create o

30、r 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尚洋信德存儲過程ORACLE 和和informix 存

31、儲過程區(qū)別存儲過程區(qū)別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 pReturnSet; 2009/07/09尚洋信德

32、存儲過程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 存儲過程區(qū)別存儲過程區(qū)別4.錯誤捕捉Oracle:Exceptionwhen others thenDBMS_OUTPUT.PUT_LINE(在

33、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 存儲過程區(qū)別存儲過程區(qū)別5.對游標的處理Oracle:create or replace procedure pHasCursor as v_f1 number(10,0); cursor curt1

34、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) then update t1 set f2 =

35、 one where f3=1001; end if; end foreach;End ;End pHasCursor;2009/07/09尚洋信德存儲過程ORACLE 和和informix 存儲過程區(qū)別存儲過程區(qū)別6.打印調(diào)試信息的處理打印調(diào)試信息的處理Oracle: DBMS_OUTPUT.PUT_LINE(開始執(zhí)行存儲過程開始執(zhí)行存儲過程); DBMS_OUTPUT.PUT_LINE(v_date=|v_date); DBMS_OUTPUT.PUT_LINE(存儲過程執(zhí)行完畢存儲過程執(zhí)行完畢); Informix:set debug file to trace_check; - with

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

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

38、RACLE 和和informix 存儲過程區(qū)別存儲過程區(qū)別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 存儲過程區(qū)別存儲過程區(qū)別10.賦值 Oracle: v_1 := 100; Informix: let v_1 = 100;2009/07/09尚洋信德存儲過程練習創(chuàng)建一個存儲過程, 使用游標從dept表中取BID為010開頭的記錄信息, 把它往表t01_psn中插入一條記錄,并且循環(huán)打印插入的數(shù)據(jù)記錄.2009/07/09尚洋信德函數(shù)1. 函數(shù)結(jié)構(gòu) CREATE OR REPLACE FUNCTION 函數(shù)名(參數(shù)1 IN NUMBER, 參數(shù)2 IN NUMBER )RETURN 類型 IS | ASBEGIN FUNCTION _BODYEND函數(shù)名; 注釋: 函數(shù)與存儲過程相似,唯一區(qū)別函