oracle存儲(chǔ)過程的詳細(xì)列子說明加分析

1、文章分類:數(shù)據(jù)庫存儲(chǔ)過程創(chuàng)建語法：（1）無參create or replace procedure 存儲(chǔ)過程名as變量1 類型（值范圍）;變量2 類型（值范圍）;Begin .Exception .End;（2）帶參create or replace procedure 存儲(chǔ)過程名（param1 in type，param2 out type）as變量1 類型（值范圍）;變量2 類型（值范圍）;Begin Select count(*) into 變量1 from 表A where列名=param1； If (判斷條件) then Select 列名 into 變量2 from 表A wher

2、e列名=param1； Dbms_output.Put_line(打印信息); Elseif (判斷條件) then Dbms_output.Put_line(打印信息); Else Raise 異常名（NO_DATA_FOUND）; End if;Exception When others then Rollback;End;注意事項(xiàng)：1， 存儲(chǔ)過程參數(shù)不帶取值范圍，in表示傳入，out表示輸出2， 變量帶取值范圍，后面接分號(hào)3， 在判斷語句前最好先用count（*）函數(shù)判斷是否存在該條操作記錄4， 用select 。into。給變量賦值5， 在代碼中拋異常用 raise+異常名以命名的異常

3、命名的系統(tǒng)異常 產(chǎn)生原因ACCESS_INTO_NULL 未定義對(duì)象CASE_NOT_FOUND CASE 中若未包含相應(yīng)的 WHEN ，并且沒有設(shè)置ELSE 時(shí)COLLECTION_IS_NULL 集合元素未初始化CURSER_ALREADY_OPEN 游標(biāo)已經(jīng)打開DUP_VAL_ON_INDEX 唯一索引對(duì)應(yīng)的列上有重復(fù)的值INVALID_CURSOR 在不合法的游標(biāo)上進(jìn)行操作INVALID_NUMBER 內(nèi)嵌的 SQL 語句不能將字符轉(zhuǎn)換為數(shù)字NO_DATA_FOUND 使用 select into 未返回行，或應(yīng)用索引表未初始化的 TOO_MANY_ROWS 執(zhí)行 select int

4、o 時(shí)，結(jié)果集超過一行ZERO_DIVIDE 除數(shù)為 0SUBSCRIPT_BEYOND_COUNT 元素下標(biāo)超過嵌套表或 VARRAY 的最大值SUBSCRIPT_OUTSIDE_LIMIT 使用嵌套表或 VARRAY 時(shí)，將下標(biāo)指定為負(fù)數(shù)VALUE_ERROR 賦值時(shí)，變量長(zhǎng)度不足以容納實(shí)際數(shù)據(jù)LOGIN_DENIED PL/SQL 應(yīng)用程序連接到 oracle 數(shù)據(jù)庫時(shí)，提供了不正確的用戶名或密碼NOT_LOGGED_ON PL/SQL 應(yīng)用程序在沒有連接 oralce 數(shù)據(jù)庫的情況下訪問數(shù)據(jù)PROGRAM_ERROR PL/SQL 內(nèi)部問題，可能需要重裝數(shù)據(jù)字典 pl./SQL系統(tǒng)包R

5、OWTYPE_MISMATCH 宿主游標(biāo)變量與 PL/SQL 游標(biāo)變量的返回類型不兼容SELF_IS_NULL 使用對(duì)象類型時(shí)，在 null 對(duì)象上調(diào)用對(duì)象方法STORAGE_ERROR 運(yùn)行 PL/SQL 時(shí)，超出內(nèi)存空間SYS_INVALID_ID 無效的 ROWID 字符串TIMEOUT_ON_RESOURCE Oracle 在等待資源時(shí)超時(shí) 例子：1 create or replace procedure runbyparmeters (isal in emp.sal%type, sname out varchar,sjob in out varchar)2 as icount num

6、ber;3 begin4 select count(*) into icount from emp where sal>isal and job=sjob;5 if icount=1 then6 .9 else10 .12 end if;13 exception14 when too_many_rows then15 DBMS_OUTPUT.PUT_LINE('返回值多于1行');16 when others then17 DBMS_OUTPUT.PUT_LINE('在RUNBYPARMETERS過程中出錯(cuò)！');18 end;過程調(diào)用 方式一1 decl

7、are2 realsal emp.sal%type;3 realname varchar(40);4 realjob varchar(40);5 begin6 realsal:=1100;7 realname:=''8 realjob:='CLERK'9 runbyparmeters(realsal,realname,realjob); 必須按順序10 DBMS_OUTPUT.PUT_LINE(REALNAME|' '|REALJOB);11 END;12 方式二1 declare2 realsal emp.sal%type;3 realname

8、 varchar(40);4 realjob varchar(40);5 begin6 realsal:=1100;7 realname:=''8 realjob:='CLERK'9 runbyparmeters(sname=>realname,isal=>realsal,sjob=>realjob); 指定值對(duì)應(yīng)變量順序可變10 DBMS_OUTPUT.PUT_LINE(REALNAME|' '|REALJOB);11 END;說明：（1）使用%TYPE在許多情況下，PL/SQL變量可以用來存儲(chǔ)在數(shù)據(jù)庫表中的數(shù)據(jù)。在這種情況下

9、，變量應(yīng)該擁有與表列相同的類型。例如，students表的first_name列的類型為VARCHAR2(20),我們可以按照下述方式聲明一個(gè)變量DECLAREv_FirstName VARCHAR2(20);但是如果first_name列的定義改變了會(huì)發(fā)生什么（比如說表改變了，first_name現(xiàn)在的類型變?yōu)閂ARCHAR2(25)）？那就會(huì)導(dǎo)致所 有使用這個(gè)列的PL/SQL代碼都必須進(jìn)行修改。如果你有很多的PL/SQL代碼，這種處理可能是十分耗時(shí)和容易出錯(cuò)的。這時(shí)，你可以使用”%TYPE”屬性而不是將變量類型硬性編碼。（2）使用%ROWTYPE (相當(dāng)于定義一個(gè)struct 來進(jìn)行存放，

10、以對(duì)象來看對(duì)數(shù)據(jù)）在PL/SQL中將一個(gè)記錄聲明為具有相同類型的數(shù)據(jù)庫行的作法是很常見的。PL/SQL提供了%ROWTYPE運(yùn)算符，使得這樣的操作更為方便。例如：DECLAREv_StudentRecord students%ROWTYPE;將定義一個(gè)記錄，該記錄中的字段將與students表中的列相對(duì)應(yīng)。例如：declare v_jobs %rowtype;begin select * into v_jobs from where job_id ='&aa' dbms_output.put_line('序號(hào)'|v_jobs.

11、job_id ); dbms_output.put_line('名稱'|v_jobs.job_title);end;執(zhí)行，我們輸入aa變量的值：AD_VP輸出結(jié)果為：序號(hào)AD_VP名稱Administration Vice President以下是代碼片段： create or replace package PSH_GPRSSTREAMSTAT is- Author : ADMINISTRATOR- Created : 2004-12-8 10:56:01- Purpose : GPRS流量統(tǒng)計(jì)狀態(tài)- 統(tǒng)計(jì)GPRS流量typeC_Cur is ref cursor;funct

12、ion Calcu_GPRSSTREAM return number;end PSH_GPRSSTREAMSTAT;-create or replace package body PSH_GPRSSTREAMSTAT isfunction Calcu_GPRSSTREAM return numberisc_IPPackHeadLen constant number := 40; - 定義IP包頭長(zhǎng)度CURSOR c_SPINFO isselect distinct spid from sh_spinfo where isactive = '0'c_MDTINFO C_Cur ;

13、v_MDTINFO number;v_UpTransContentLens number(20,0); - 存放當(dāng)前GPRS終端上傳轉(zhuǎn)發(fā)的信息內(nèi)容長(zhǎng)度v_UpContentLens number(20,0);v_UpTotalLens number(20,0); - 累計(jì)GPRS終端上傳的信息內(nèi)容長(zhǎng)度v_DownContentLens number(20,0);v_DownTotalLens number(20,0);newID number(20,0);begin- 初始化以下是代碼片段： select max(statid) into newID from sh_gprsstreamsta

14、t;if (newID is null) thennewID := 1;end if;for v_SPINFO In c_SPINFO loop - 首先獲取SPID- 其次遍歷出與當(dāng)前SPID對(duì)應(yīng)的所有MDT以下是代碼片段： open c_MDTINFo for select distinct mdtid from sh_mdtinfo where (isactive = '0') and (spid = v_SPINFO.spid);loopfetch c_MDTINFO into v_MDTINFO;exit when c_MDTINFO%notfound;v_UpCon

15、tentLens := 0;v_UpTransContentLens := 0;v_UpTotalLens := 0;v_DownContentLens := 0;v_DownTotalLens := 0;1、用來插入大量測(cè)試數(shù)據(jù)的存儲(chǔ)過程CREATE OR REPLACE PROCEDURE INSERTAMOUNTTEST(ST_NUM IN NUMBER,ED_NUM IN NUMBER)ISBEGINdeclare i number;beginFOR i IN ST_NUM.ED_NUM LOOPINSERT INTO tb values(i,i,'3','3&

16、#39;,'3',100,'0');END LOOP;end;END;運(yùn)行：sql>execute INSERTAMOUNTTEST(1,45000) - 一次插入45000條測(cè)試數(shù)據(jù)2、從存儲(chǔ)過程中返回值create or replace procedure spaddflowdate(varAppTypeId in varchar2,varFlowId in varchar2,DateLength in number,ReturnValue out number -返回值)isbegininsert into td values(varAppTypeI

17、d,varFlowId,DateLength)returning 1 into ReturnValue; -返回值commit;exceptionwhen others thenrollback;end;存儲(chǔ)過程的執(zhí)行sql>variable testvalue number;sql>execute spaddflowdate('v','v',2,:testvalue);sql>print就可以看到執(zhí)行結(jié)果 3、用包實(shí)現(xiàn)存儲(chǔ)過程返回游標(biāo)：create or replace package test_p as type outList is re

18、f cursor; PROCEDURE getinfor(taxpayerList out outList); end test_p; / create or replace package body test_p as PROCEDURE getinfor(taxpayerList out outList) is begin OPEN taxpayerList FOR select * from td where tag='0' end getinfor; end test_p; / 運(yùn)行： set serverout on; -將輸出工具打開 variable x refc

19、ursor; execute test_p.getinfor(:x);exec test_p.getinfor(:x); print x; drop package test_p;oracle 存儲(chǔ)過程的基本語法1.基本結(jié)構(gòu)CREATE OR REPLACE PROCEDURE 存儲(chǔ)過程名字( 參數(shù)1 IN NUMBER, 參數(shù)2 IN NUMBER) IS變量1 INTEGER :=0;變量2 DATE;BEGINEND 存儲(chǔ)過程名字2.SELECT INTO STATEMENT 將select查詢的結(jié)果存入到變量中，可以同時(shí)將多個(gè)列存儲(chǔ)多個(gè)變量中，必須有一條 記錄，否則拋出異常(如果沒有記

20、錄拋出NO_DATA_FOUND) 例子： BEGIN SELECT col1,col2 into 變量1,變量2 FROM typestruct where xxx; EXCEPTION WHEN NO_DATA_FOUND THEN xxxx; END; .3.IF 判斷 IF V_TEST=1 THEN BEGIN do something END; END IF;4.while 循環(huán) WHILE V_TEST=1 LOOP BEGIN XXXX END; END LOOP;5.變量賦值 V_TEST := 123;6.用for in 使用cursor . IS CURSOR cur I

21、S SELECT * FROM xxx; BEGIN FOR cur_result in cur LOOP BEGIN V_SUM :=cur_result.列名1+cur_result.列名2 END; END LOOP; END;7.帶參數(shù)的cursor CURSOR C_USER(C_ID NUMBER) IS SELECT NAME FROM USER WHERE TYPEID=C_ID; OPEN C_USER(變量值); LOOP FETCH C_USER INTO V_NAME; EXIT FETCH C_USER%NOTFOUND; do something END LOOP;

22、 CLOSE C_USER;8.用pl/sql developer debug 連接數(shù)據(jù)庫后建立一個(gè)Test WINDOW 在窗口輸入調(diào)用SP的代碼,F9開始debug,CTRL+N單步調(diào)試關(guān)于oracle存儲(chǔ)過程的若干問題備忘1.在oracle中，數(shù)據(jù)表別名不能加as，如：select a.appname from appinfo a;- 正確select a.appname from appinfo as a;- 錯(cuò)誤 也許，是怕和oracle中的存儲(chǔ)過程中的關(guān)鍵字as沖突的問題吧2.在存儲(chǔ)過程中，select某一字段時(shí)，后面必須緊跟into，如果select整個(gè)記錄，利用游標(biāo)的話就另當(dāng)別

23、論了。 select af.keynode into kn from APPFOUNDATION af where af.appid=aid and af.foundationid=fid;- 有into，正確編譯 select af.keynode from APPFOUNDATION af where af.appid=aid and af.foundationid=fid;- 沒有into，編譯報(bào)錯(cuò)，提示：Compilation Error: PLS-00428: an INTO clause is expected in this SELECT statement3.在利用select

24、.into.語法時(shí)，必須先確保數(shù)據(jù)庫中有該條記錄，否則會(huì)報(bào)出"no data found"異常。 可以在該語法之前，先利用select count(*) from 查看數(shù)據(jù)庫中是否存在該記錄，如果存在，再利用o.4.在存儲(chǔ)過程中，別名不能和字段名稱相同，否則雖然編譯可以通過，但在運(yùn)行階段會(huì)報(bào)錯(cuò) select keynode into kn from APPFOUNDATION where appid=aid and foundationid=fid;- 正確運(yùn)行select af.keynode into kn from APPFOUNDATION af

25、 where af.appid=appid and af.foundationid=foundationid;- 運(yùn)行階段報(bào)錯(cuò)，提示ORA-01422:exact fetch returns more than requested number of rows5.在存儲(chǔ)過程中，關(guān)于出現(xiàn)null的問題假設(shè)有一個(gè)表A，定義如下：create table A(id varchar2(50) primary key not null,vcount number(8) not null,bid varchar2(50) not null - 外鍵 );如果在存儲(chǔ)過程中，使用如下語句：select sum

26、(vcount) into fcount from A where bid='xxxxxx'如果A表中不存在bid="xxxxxx"的記錄，則fcount=null(即使fcount定義時(shí)設(shè)置了默認(rèn)值，如：fcount number(8):=0依然無效，fcount還是會(huì)變成null)，這樣以后使用fcount時(shí)就可能有問題，所以在這里最好先判斷一下：if fcount is null then fcount:=0;end if;這樣就一切ok了。6.Hibernate調(diào)用oracle存儲(chǔ)過程 this.pnumberManager.getHibernateTemplate().execute( new HibernateCallback() . public