oracle存儲(chǔ)過(guò)程學(xué)習(xí)經(jīng)典入門(mén)

PAGEOracle存儲(chǔ)過(guò)程學(xué)習(xí)目錄Oracle存儲(chǔ)過(guò)程學(xué)習(xí) 1Oracle存儲(chǔ)過(guò)程基礎(chǔ)知識(shí) 1Oracle存儲(chǔ)過(guò)程的基本語(yǔ)法 2關(guān)于Oracle存儲(chǔ)過(guò)程的若干問(wèn)題備忘 41. 在Oracle中，數(shù)據(jù)表別名不能加as。 42. 在存儲(chǔ)過(guò)程中，select某一字段時(shí)，后面必須緊跟into，如果select整個(gè)記錄，利用游標(biāo)的話(huà)就另當(dāng)別論了。 43. 在利用o...語(yǔ)法時(shí)，必須先確保數(shù)據(jù)庫(kù)中有該條記錄，否則會(huì)報(bào)出"nodatafound"異常。 44. 在存儲(chǔ)過(guò)程中，別名不能和字段名稱(chēng)相同，否則雖然編譯可以通過(guò)，但在運(yùn)行階段會(huì)報(bào)錯(cuò) 45. 在存儲(chǔ)過(guò)程中，關(guān)于出現(xiàn)null的問(wèn)題 56. Hibernate調(diào)用Oracle存儲(chǔ)過(guò)程 5用Java調(diào)用Oracle存儲(chǔ)過(guò)程總結(jié) 5一、 無(wú)返回值的存儲(chǔ)過(guò)程 6二、 有返回值的存儲(chǔ)過(guò)程（非列表） 7三、 返回列表 9在存儲(chǔ)過(guò)程中做簡(jiǎn)單動(dòng)態(tài)查詢(xún) 10一、 本地動(dòng)態(tài)SQL 11二、 使用DBMS_SQL包 12Oracle存儲(chǔ)過(guò)程調(diào)用Java方法 13Oracle高效分頁(yè)存儲(chǔ)過(guò)程實(shí)例 14Oracle存儲(chǔ)過(guò)程基礎(chǔ)知識(shí)商業(yè)規(guī)則和業(yè)務(wù)邏輯可以通過(guò)程序存儲(chǔ)在Oracle中，這個(gè)程序就是存儲(chǔ)過(guò)程。存儲(chǔ)過(guò)程是SQL,PL/SQL,Java語(yǔ)句的組合，它使你能將執(zhí)行商業(yè)規(guī)則的代碼從你的應(yīng)用程序中移動(dòng)到數(shù)據(jù)庫(kù)。這樣的結(jié)果就是，代碼存儲(chǔ)一次但是能夠被多個(gè)程序使用。要?jiǎng)?chuàng)建一個(gè)過(guò)程對(duì)象（proceduralobject），必須有CREATEPROCEDURE系統(tǒng)權(quán)限。如果這個(gè)過(guò)程對(duì)象需要被其他的用戶(hù)schema使用，那么你必須有CREATEANYPROCEDURE權(quán)限。執(zhí)行procedure的時(shí)候，可能需要excute權(quán)限?；蛘逧XCUTEANYPROCEDURE權(quán)限。如果單獨(dú)賦予權(quán)限，如下例所示：

grant

executeonMY_PROCEDURE

toJelly調(diào)用一個(gè)存儲(chǔ)過(guò)程的例子：executeMY_PROCEDURE('ONEPARAMETER');存儲(chǔ)過(guò)程(PROCEDURE)和函數(shù)(FUNCTION)的區(qū)別。function有返回值，并且可以直接在Query中引用function和或者使用function的返回值。本質(zhì)上沒(méi)有區(qū)別,都是PL/SQL程序,都可以有返回值。最根本的區(qū)別是：存儲(chǔ)過(guò)程是命令,

而函數(shù)是表達(dá)式的一部分。比如：selectmax(NAME)FROM但是不能execmax(NAME)如果此時(shí)max是函數(shù)。PACKAGE是function，procedure，variables和sql語(yǔ)句的組合。package允許多個(gè)procedure使用同一個(gè)變量和游標(biāo)。創(chuàng)建procedure的語(yǔ)法：CREATE[ORREPLACE]PROCEDURE[schema.]procedure[(argument[IN|OUT|INOUT][NOCOPY]datatype[,argument[IN|OUT|INOUT][NOCOPY]datatype]...)][authid{current_user|definer}]{is|as}{pl/sql_subprogram_body|language{javaname'String'|c[name,name]librarylib_name}]Sql代碼：CREATEPROCEDUREsam.credit(acc_noINNUMBER,amountINNUMBER)ASBEGINUPDATEaccountsSETbalance=balance+amountWHEREaccount_id=acc_no;END;可以使用createorreplaceprocedure語(yǔ)句，這個(gè)語(yǔ)句的用處在于，你之前賦予的excute權(quán)限都將被保留。IN,OUT,INOUT用來(lái)修飾參數(shù)。IN表示這個(gè)變量必須被調(diào)用者賦值然后傳入到PROCEDURE進(jìn)行處理。OUT表示PRCEDURE通過(guò)這個(gè)變量將值傳回給調(diào)用者。INOUT則是這兩種的組合。authid代表兩種權(quán)限：定義者權(quán)限（difinerright默認(rèn)），執(zhí)行者權(quán)限（invokerright）。定義者權(quán)限說(shuō)明這個(gè)procedure中涉及的表，視圖等對(duì)象所需要的權(quán)限只要定義者擁有權(quán)限的話(huà)就可以訪問(wèn)。執(zhí)行者權(quán)限則需要調(diào)用這個(gè)procedure的用戶(hù)擁有相關(guān)表和對(duì)象的權(quán)限。Oracle存儲(chǔ)過(guò)程的基本語(yǔ)法基本結(jié)構(gòu)CREATEORREPLACEPROCEDURE存儲(chǔ)過(guò)程名字

(

參數(shù)1INNUMBER,

參數(shù)2INNUMBER

)AS

變量1INTEGER:=0;

變量2DATE;

BEGINEND存儲(chǔ)過(guò)程名字SELECTINTOSTATEMENT將select查詢(xún)的結(jié)果存入到變量中，可以同時(shí)將多個(gè)列存儲(chǔ)多個(gè)變量中，必須有一條

記錄，否則拋出異常(如果沒(méi)有記錄拋出NO_DATA_FOUND)例子：BEGIN

SELECTcol1,col2into變量1,變量2FROMtypestructwherexxx;

EXCEPTION

WHENNO_DATA_FOUNDTHEN

xxxx;

END;

...IF判斷IFV_TEST=1THEN

BEGIN

dosomething

END;

ENDIF;while循環(huán)WHILEV_TEST=1LOOP

BEGIN

XXXX

END;

ENDLOOP;變量賦值V_TEST:=123;用forin使用cursor...

IS

CURSORcurISSELECT*FROMxxx;

BEGIN

FORcur_resultincurLOOP

BEGIN

V_SUM:=cur_result.列名1+cur_result.列名2

END;

ENDLOOP;

END;帶參數(shù)的cursorCURSORC_USER(C_IDNUMBER)ISSELECTNAMEFROMUSERWHERETYPEID=C_ID;

OPENC_USER(變量值);

LOOP

FETCHC_USERINTOV_NAME;

EXITFETCHC_USER%NOTFOUND;

dosomething

ENDLOOP;

CLOSEC_USER;用pl/sqldeveloperdebug

連接數(shù)據(jù)庫(kù)后建立一個(gè)TestWINDOW

在窗口輸入調(diào)用SP的代碼,F9開(kāi)始debug,CTRL+N單步調(diào)試Pl/Sql中執(zhí)行存儲(chǔ)過(guò)程在sql*plus中：

declare

--必要的變量聲明，視你的過(guò)程而定

begin

execute

yourprocudure(parameter1,parameter2,...);

end

/

在SQL/PLUS中調(diào)用存儲(chǔ)過(guò)程，顯示結(jié)果：SQL>setserveoutputon--打開(kāi)輸出SQL>varinfo1number;--輸出1SQL>varinfo2number;--輸出2SQL>declarevar1varchar2(20);--輸入1var2varchar2(20);--輸入2var3varchar2(20);--輸入2BEGINpro(var1,var2,var3,:info1,:info2);END;/SQL>printinfo1;SQL>printinfo2; 注：在EXECUTEIMMEDIATESTR語(yǔ)句是SQLPLUS中動(dòng)態(tài)執(zhí)行語(yǔ)句，它在執(zhí)行中會(huì)自動(dòng)提交，類(lèi)似于DP中FORMS_DDL語(yǔ)句，在此語(yǔ)句中str是不能換行的,只能通過(guò)連接字符"||"，或著在在換行時(shí)加上"-"連接字符。關(guān)于Oracle存儲(chǔ)過(guò)程的若干問(wèn)題備忘在Oracle中，數(shù)據(jù)表別名不能加as。如：select

a.appname

from

appinfo

a;--正確

select

a.appname

from

appinfo

as

a;--錯(cuò)誤

也許，是怕和Oracle中的存儲(chǔ)過(guò)程中的關(guān)鍵字as沖突的問(wèn)題吧在存儲(chǔ)過(guò)程中，select某一字段時(shí)，后面必須緊跟into，如果select整個(gè)記錄，利用游標(biāo)的話(huà)就另當(dāng)別論了。

select

af.keynode

into

kn

from

APPFOUNDATION

afwhere

af.appid=aid

and

af.foundationid=fid;--

有into，正確編譯select

af.keynode

from

APPFOUNDATION

af

where

af.appid=aid

and

af.foundationid=fid;--

沒(méi)有into，編譯報(bào)錯(cuò)，提示：Compilation

Error:

PLS-00428:

an

INTO

clause

is

expected

in

this

SELECT

statement在利用o...語(yǔ)法時(shí)，必須先確保數(shù)據(jù)庫(kù)中有該條記錄，否則會(huì)報(bào)出"nodatafound"異常?？梢栽谠撜Z(yǔ)法之前，先利用selectcount(*)from查看數(shù)據(jù)庫(kù)中是否存在該記錄，如果存在，再利用o...在存儲(chǔ)過(guò)程中，別名不能和字段名稱(chēng)相同，否則雖然編譯可以通過(guò)，但在運(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

where

af.appid=appid

and

af.foundationid=foundationid; --

運(yùn)行階段報(bào)錯(cuò)，提示：

ORA-01422:exact

fetch

returns

more

than

requested

number

of

rows在存儲(chǔ)過(guò)程中，關(guān)于出現(xiàn)null的問(wèn)題假設(shè)有一個(gè)表A，定義如下：create

table

A(

id

varchar2(50)

primary

key

not

null,

vcount

number(8)

not

null,

bid

varchar2(50)

not

null

--

外鍵

);如果在存儲(chǔ)過(guò)程中，使用如下語(yǔ)句：select

sum(vcount)

into

fcount

from

A

where

bid='xxxxxx';如果A表中不存在bid="xxxxxx"的記錄，則fcount=null(即使fcount定義時(shí)設(shè)置了默認(rèn)值，如：fcountnumber(8):=0依然無(wú)效，fcount還是會(huì)變成null)，這樣以后使用fcount時(shí)就可能有問(wèn)題，所以在這里最好先判斷一下：if

fcount

is

null

then

fcount:=0;

end

if;這樣就一切ok了。Hibernate調(diào)用Oracle存儲(chǔ)過(guò)程this.pnumberManager.getHibernateTemplate().execute(newHibernateCallback()...{publicObjectdoInHibernate(Sessionsession)throwsHibernateException,SQLException...{CallableStatementcs=session.connection().prepareCall("{callmodifyapppnumber_remain(?)}");cs.setString(1,foundationid);cs.execute();returnnull;}});用Java調(diào)用Oracle存儲(chǔ)過(guò)程總結(jié)無(wú)返回值的存儲(chǔ)過(guò)程測(cè)試表:--CreatetablecreatetableTESTTB(IDVARCHAR2(30),NAMEVARCHAR2(30))tablespaceBOMpctfree10initrans1maxtrans255storage(initial64Kminextents1maxextentsunlimited);例:存儲(chǔ)過(guò)程為(當(dāng)然了，這就先要求要建張表TESTTB,里面兩個(gè)字段（I_ID，I_NAME）。)：CREATEORREPLACEPROCEDURETESTA(PARA1INVARCHAR2,PARA2INVARCHAR2)ASBEGININSERTINTOBOM.TESTTB(ID,NAME)VALUES(PARA1,PARA2);ENDTESTA;在Java里調(diào)用時(shí)就用下面的代碼：packagecedure.test;importjava.sql.CallableStatement;importjava.sql.Connection;importjava.sql.DriverManager;importjava.sql.ResultSet;importjava.sql.SQLException;importjava.sql.Statement;publicclassTestProcedureDemo1{ publicTestProcedureDemo1(){ } publicstaticvoidmain(String[]args){ Stringdriver="Oracle.jdbc.driver.OracleDriver"; StringstrUrl="jdbc:Oracle:thin:@0:1521:vasms"; Statementstmt=null; ResultSetrs=null; Connectionconn=null; CallableStatementproc=null; try{ Class.forName(driver); conn=DriverManager.getConnection(strUrl,"bom","bom"); proc=conn.prepareCall("{callBOM.TESTA(?,?)}"); proc.setString(1,"100"); proc.setString(2,"TestOne"); proc.execute(); }catch(SQLExceptionex2){ ex2.printStackTrace(); }catch(Exceptionex2){ ex2.printStackTrace(); }finally{ try{ if(rs!=null){ rs.close(); if(stmt!=null){ stmt.close(); } if(conn!=null){ conn.close(); } } }catch(SQLExceptionex1){ } } }}有返回值的存儲(chǔ)過(guò)程（非列表）例：存儲(chǔ)過(guò)程為：CREATEORREPLACEPROCEDURETESTB(PARA1INVARCHAR2,PARA2OUTVARCHAR2)ASBEGINSELECTNAMEINTOPARA2FROMTESTTBWHEREID=PARA1;ENDTESTB;在Java里調(diào)用時(shí)就用下面的代碼：packagecedure.test;importjava.sql.CallableStatement;importjava.sql.Connection;importjava.sql.DriverManager;importjava.sql.ResultSet;importjava.sql.SQLException;importjava.sql.Statement;importjava.sql.Types;publicclassTestProcedureDemo2{ publicstaticvoidmain(String[]args){ Stringdriver="Oracle.jdbc.driver.OracleDriver"; StringstrUrl="jdbc:Oracle:thin:@0:1521:vasms"; Statementstmt=null; ResultSetrs=null; Connectionconn=null; CallableStatementproc=null; try{ Class.forName(driver); conn=DriverManager.getConnection(strUrl,"bom","bom"); proc=conn.prepareCall("{callBOM.TESTB(?,?)}"); proc.setString(1,"100"); proc.registerOutParameter(2,Types.VARCHAR); proc.execute(); StringtestPrint=proc.getString(2); System.out.println("=testPrint=is="+testPrint); }catch(SQLExceptionex2){ ex2.printStackTrace(); }catch(Exceptionex2){ ex2.printStackTrace(); }finally{ try{ if(rs!=null){ rs.close(); if(stmt!=null){ stmt.close(); } if(conn!=null){ conn.close(); } } }catch(SQLExceptionex1){ } } }}注意，這里的proc.getString(2)中的數(shù)值2并非任意的，而是和存儲(chǔ)過(guò)程中的out列對(duì)應(yīng)的，如果out是在第一個(gè)位置，那就是proc.getString(1)，如果是第三個(gè)位置，就是proc.getString(3)，當(dāng)然也可以同時(shí)有多個(gè)返回值，那就是再多加幾個(gè)out參數(shù)了。返回列表由于Oracle存儲(chǔ)過(guò)程沒(méi)有返回值，它的所有返回值都是通過(guò)out參數(shù)來(lái)替代的，列表同樣也不例外，但由于是集合，所以不能用一般的參數(shù)，必須要用pagkage了.所以要分兩部分，建一個(gè)程序包。如下：CREATEORREPLACEPACKAGETESTPACKAGEASTYPETEST_CURSORISREFCURSOR;endTESTPACKAGE;建立存儲(chǔ)過(guò)程，存儲(chǔ)過(guò)程為：CREATEORREPLACEPROCEDURETESTC(P_CURSORoutTESTPACKAGE.TEST_CURSOR)ISBEGINOPENP_CURSORFORSELECT*FROMBOM.TESTTB;ENDTESTC;可以看到，它是把游標(biāo)（可以理解為一個(gè)指針），作為一個(gè)out參數(shù)來(lái)返回值的。在Java里調(diào)用時(shí)就用下面的代碼：在這里要注意，在執(zhí)行前一定要先把Oracle的驅(qū)動(dòng)包放到class路徑里，否則會(huì)報(bào)錯(cuò)的。packagecedure.test;importjava.sql.CallableStatement;importjava.sql.Connection;importjava.sql.DriverManager;importjava.sql.ResultSet;importjava.sql.SQLException;importjava.sql.Statement;publicclassTestProcedureDemo3{ publicstaticvoidmain(String[]args){ Stringdriver="Oracle.jdbc.driver.OracleDriver"; StringstrUrl="jdbc:Oracle:thin:@0:1521:vasms"; Statementstmt=null; ResultSetrs=null; Connectionconn=null; CallableStatementproc=null; try{ Class.forName(driver); conn=DriverManager.getConnection(strUrl,"bom","bom"); proc=conn.prepareCall("{callbom.testc(?)}"); proc.registerOutParameter(1,Oracle.jdbc.OracleTypes.CURSOR); proc.execute(); rs=(ResultSet)proc.getObject(1); while(rs.next()){ System.out.println("<tr><td>"+rs.getString(1)+"</td><td>" +rs.getString(2)+"</td></tr>"); } }catch(SQLExceptionex2){ ex2.printStackTrace(); }catch(Exceptionex2){ ex2.printStackTrace(); }finally{ try{ if(rs!=null){ rs.close(); if(stmt!=null){ stmt.close(); } if(conn!=null){ conn.close(); } } }catch(SQLExceptionex1){ } } }}在存儲(chǔ)過(guò)程中做簡(jiǎn)單動(dòng)態(tài)查詢(xún)?cè)诖鎯?chǔ)過(guò)程中做簡(jiǎn)單動(dòng)態(tài)查詢(xún)代碼

,例如:CREATEORREPLACEprocedureZXM_SB_GZ_GET

(p_tableinvarchar2,

p_nameinvarchar2,

p_valueinvarchar2,

outparaoutlntxdba.zxm_pag_cs_power.c_type

)

as

begin

declare

wherevaluevarchar2(200);

begin

wherevalue:=select*from||p_table||where||p_name||=||p_value;

openoutparafor

wherevalue;

end;

end;一般的PL/SQL程序設(shè)計(jì)中，在DML和事務(wù)控制的語(yǔ)句中可以直接使用SQL，但是DDL語(yǔ)句及系統(tǒng)控制語(yǔ)句卻不能在PL/SQL中直接使用，要想實(shí)現(xiàn)在PL/SQL中使用DDL語(yǔ)句及系統(tǒng)控制語(yǔ)句，可以通過(guò)使用動(dòng)態(tài)SQL來(lái)實(shí)現(xiàn)。首先我們應(yīng)該了解什么是動(dòng)態(tài)SQL，在Oracle數(shù)據(jù)庫(kù)開(kāi)發(fā)PL/SQL塊中我們使用的SQL分為：靜態(tài)SQL語(yǔ)句和動(dòng)態(tài)SQL語(yǔ)句。所謂靜態(tài)SQL指在PL/SQL塊中使用的SQL語(yǔ)句在編譯時(shí)是明確的，執(zhí)行的是確定對(duì)象。而動(dòng)態(tài)SQL是指在PL/SQL塊編譯時(shí)SQL語(yǔ)句是不確定的，如根據(jù)用戶(hù)輸入的參數(shù)的不同而執(zhí)行不同的操作。編譯程序?qū)?dòng)態(tài)語(yǔ)句部分不進(jìn)行處理，只是在程序運(yùn)行時(shí)動(dòng)態(tài)地創(chuàng)建語(yǔ)句、對(duì)語(yǔ)句進(jìn)行語(yǔ)法分析并執(zhí)行該語(yǔ)句。Oracle中動(dòng)態(tài)SQL可以通過(guò)本地動(dòng)態(tài)SQL來(lái)執(zhí)行，也可以通過(guò)DBMS_SQL包來(lái)執(zhí)行。下面就這兩種情況分別進(jìn)行說(shuō)明：本地動(dòng)態(tài)SQL本地動(dòng)態(tài)SQL是使用EXECUTEIMMEDIATE語(yǔ)句來(lái)實(shí)現(xiàn)的。本地動(dòng)態(tài)SQL執(zhí)行DDL語(yǔ)句：需求：根據(jù)用戶(hù)輸入的表名及字段名等參數(shù)動(dòng)態(tài)建表。createorreplaceprocedureproc_test

(

table_nameinvarchar2,

--表名

field1invarchar2,

--字段名

datatype1invarchar2,

--字段類(lèi)型

field2invarchar2,

--字段名

datatype2invarchar2

--字段類(lèi)型

)as

str_sqlvarchar2(500);

begin

str_sql:=’createtable’||table_name||’(’||field1||’’||datatype1||’,’||field2||’’||datatype2||’)’;

executeimmediatestr_sql;

--動(dòng)態(tài)執(zhí)行DDL語(yǔ)句

exception

whenothersthen

null;

end;以上是編譯通過(guò)的存儲(chǔ)過(guò)程代碼。下面執(zhí)行存儲(chǔ)過(guò)程動(dòng)態(tài)建表。SQL>executeproc_test(’dinya_test’,’id’,’number(8)notnull’,’name’,’varchar2(100)’);

PL/SQLproceduresuccessfullycompleted

SQL>descdinya_test;

NameType

NullableDefaultComments

ID

NUMBER(8)

NAMEVARCHAR2(100)Y

SQL>到這里，就實(shí)現(xiàn)了我們的需求，使用本地動(dòng)態(tài)SQL根據(jù)用戶(hù)輸入的表名及字段名、字段類(lèi)型等參數(shù)來(lái)實(shí)現(xiàn)動(dòng)態(tài)執(zhí)行DDL語(yǔ)句。本地動(dòng)態(tài)SQL執(zhí)行DML語(yǔ)句。需求：將用戶(hù)輸入的值插入到上例中建好的dinya_test表中。createorreplaceprocedureproc_insert

(

idinnumber,

--輸入序號(hào)

nameinvarchar2

--輸入姓名

)as

str_sqlvarchar2(500);

begin

str_sql:=’insertintodinya_testvalues(:1,:2)’;

executeimmediatestr_sqlusingid,name;--動(dòng)態(tài)執(zhí)行插入操作

exception

whenothersthen

null;

end;

執(zhí)行存儲(chǔ)過(guò)程，插入數(shù)據(jù)到測(cè)試表中。SQL>executeproc_insert(1,’dinya’);

PL/SQLproceduresuccessfullycompleted

SQL>select*fromdinya_test;

ID

NAME

1

dinya在上例中，本地動(dòng)態(tài)SQL執(zhí)行DML語(yǔ)句時(shí)使用了using子句，按順序?qū)⑤斎氲闹到壎ǖ阶兞浚绻枰敵鰠?shù)，可以在執(zhí)行動(dòng)態(tài)SQL的時(shí)候，使用RETURNINGINTO子句，如：declare

p_idnumber:=1;

v_countnumber;

begin

v_string:=’selectcount(*)fromtable_nameawherea.id=:id’;

executeimmediatev_stringintov_countusingp_id;

end;使用DBMS_SQL包使用DBMS_SQL包實(shí)現(xiàn)動(dòng)態(tài)SQL的步驟如下：A、先將要執(zhí)行的SQL語(yǔ)句或一個(gè)語(yǔ)句塊放到一個(gè)字符串變量中。B、使用DBMS_SQL包的parse過(guò)程來(lái)分析該字符串。C、使用DBMS_SQL包的bind_variable過(guò)程來(lái)綁定變量。D、使用DBMS_SQL包的execute函數(shù)來(lái)執(zhí)行語(yǔ)句。1、使用DBMS_SQL包執(zhí)行DDL語(yǔ)句需求：使用DBMS_SQL包根據(jù)用戶(hù)輸入的表名、字段名及字段類(lèi)型建表。createorreplaceprocedureproc_dbms_sql

(

table_nameinvarchar2,

--表名

field_name1invarchar2,

--字段名

datatype1invarchar2,

--字段類(lèi)型

field_name2invarchar2,

--字段名

datatype2invarchar2

--字段類(lèi)型

)as

v_cursornumber;

--定義光標(biāo)

v_stringvarchar2(200);

--定義字符串變量

v_rownumber;

--行數(shù)

begin

v_cursor:=dbms_sql.open_cursor;

--為處理打開(kāi)光標(biāo)

v_string:=’createtable’||table_name||’(’||field_name1||’’||datatype1||’,’||field_name2||’’||datatype2||’)’;

dbms_sql.parse(v_cursor,v_string,dbms_sql.native);

--分析語(yǔ)句

v_row:=dbms_sql.execute(v_cursor);

--執(zhí)行語(yǔ)句

dbms_sql.close_cursor(v_cursor);

--關(guān)閉光標(biāo)

exception

whenothersthen

dbms_sql.close_cursor(v_cursor);

--關(guān)閉光標(biāo)

raise;

end;以上過(guò)程編譯通過(guò)后，執(zhí)行過(guò)程創(chuàng)建表結(jié)構(gòu)：SQL>executeproc_dbms_sql(’dinya_test2’,’id’,’number(8)notnull’,’name’,’varchar2(100)’);

PL/SQLproceduresuccessfullycompleted

SQL>descdinya_test2;

NameType

NullableDefaultComments

ID

NUMBER(8)

NAMEVARCHAR2(100)Y

SQL>使用DBMS_SQL包執(zhí)行DML語(yǔ)句需求：使用DBMS_SQL包根據(jù)用戶(hù)輸入的值更新表中相對(duì)應(yīng)的記錄。查看表中已有記錄：SQL>select*fromdinya_test2;

IDNAME

1Oracle

2CSDN

3ERP

SQL>建存儲(chǔ)過(guò)程，并編譯通過(guò)：createorreplaceprocedureproc_dbms_sql_update

(

idnumber,

namevarchar2

)as

v_cursornumber;

--定義光標(biāo)

v_stringvarchar2(200);

--字符串變量

v_rownumber;

--行數(shù)

begin

v_cursor:=dbms_sql.open_cursor;

--為處理打開(kāi)光標(biāo)

v_string:=’updatedinya_test2aset=:p_namewherea.id=:p_id’;

dbms_sql.parse(v_cursor,v_string,dbms_sql.native);

--分析語(yǔ)句

dbms_sql.bind_variable(v_cursor,’:p_name’,name);

--綁定變量

dbms_sql.bind_variable(v_cursor,’:p_id’,id);

--綁定變量

v_row:=dbms_sql.execute(v_cursor);--執(zhí)行動(dòng)態(tài)ＳＱＬ

dbms_sql.close_cursor(v_cursor);

--關(guān)閉光標(biāo)

exception

whenothersthen

dbms_sql.close_cursor(v_cursor);

--關(guān)閉光標(biāo)

raise;

end;執(zhí)行過(guò)程，根據(jù)用戶(hù)輸入的參數(shù)更新表中的數(shù)據(jù)：SQL>executeproc_dbms_sql_update(2,’csdn_dinya’);

PL/SQLproceduresuccessfullycompleted

SQL>select*fromdinya_test2;

IDNAME

1Oracle

2csdn_dinya

3ERP

SQL>執(zhí)行過(guò)程后將第二條的name字段的數(shù)據(jù)更新為新值csdn_dinya。這樣就完成了使用dbms_sql包來(lái)執(zhí)行DML語(yǔ)句的功能。使用DBMS_SQL中，如果要執(zhí)行的動(dòng)態(tài)語(yǔ)句不是查詢(xún)語(yǔ)句，使用DBMS_SQL.Execute或DBMS_SQL.Variable_Value來(lái)執(zhí)行，如果要執(zhí)行動(dòng)態(tài)語(yǔ)句是查詢(xún)語(yǔ)句，則要使用DBMS_SQL.define_column定義輸出變量，然后使用DBMS_SQL.Execute,DBMS_SQL.Fetch_Rows,DBMS_SQL.Column_Value及DBMS_SQL.Variable_Value來(lái)執(zhí)行查詢(xún)并得到結(jié)果。總結(jié)說(shuō)明：在Oracle開(kāi)發(fā)過(guò)程中，我們可以使用動(dòng)態(tài)SQL來(lái)執(zhí)行DDL語(yǔ)句、DML語(yǔ)句、事務(wù)控制語(yǔ)句及系統(tǒng)控制語(yǔ)句。但是需要注意的是，PL/SQL塊中使用動(dòng)態(tài)SQL執(zhí)行DDL語(yǔ)句的時(shí)候與別的不同，在DDL中使用綁定變量是非法的（bind_variable(v_cursor,’:p_name’,name)），分析后不需要執(zhí)行DBMS_SQL.Bind_Variable，直接將輸入的變量加到字符串中即可。另外，DDL是在調(diào)用DBMS_SQL.PARSE時(shí)執(zhí)行的，所以DBMS_SQL.EXECUTE也可以不用，即在上例中的v_row:=dbms_sql.execute(v_cursor)部分可以不要。Oracle存儲(chǔ)過(guò)程調(diào)用Java方法存儲(chǔ)過(guò)程中調(diào)用Java程序段軟件環(huán)境：操作系統(tǒng)：Windows

2000

Server數(shù)

據(jù)

庫(kù)：\o"搜索該標(biāo)簽相關(guān)文章"Oracle

8i

R2

(8.1.7)

for

NT

企業(yè)版安裝路徑：C:\ORACLE實(shí)現(xiàn)方法：創(chuàng)建一個(gè)文件為T(mén)est.javapublicclassTest{ publicstaticvoidmain(Stringargs[]){ System.out.println("HELLOTHISiSAJavaPROCEDURE"); }}javac

Test.javajava

TestSQL>

conn

system/managerSQL>

grant

create

any

directory

to

scott;SQL>

conn

scott/tigerSQL>

create

or

replace

directory

test_dir

as

'd:\';目錄已創(chuàng)建。SQL>

create

or

replace

java

class

using

bfile(test_dir,'TEST.CLASS')2

/Java

已創(chuàng)建。SQL>

select

object_name,object_type,STATUS

from

user_objects;SQL>

create

or

replace

procedure

test_javaas

language

javaname

'TEST.main(java.lang.String[])';/過(guò)程已創(chuàng)建。SQL>

set

serveroutput

on

size

5000SQL>

call

dbms_java.set_output(5000);調(diào)用完成。SQL>

execute

test_java;H