oracle proc learning strategies

1.基本结构

CREATEORREPLACEPROCEDURE存储过程名字

(

参数1INNUMBER,

参数2INNUMBER

)IS

变量1INTEGER:=0;

变量2DATE;

BEGIN

END 存储过程名字

2.SELECTINTOSTATEMENT

将select查询的结果存入到变量中,可以同时将多个列存储多个变量中,必须有一条

记录,否则抛出异常(如果没有记录抛出NO_DATA_FOUND)

例子:

BEGIN

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

EXCEPTION

WHENNO_DATA_FOUNDTHEN

xxxx;

END;

...

3.IF判断

IFV_TEST=1THEN

BEGIN

dosomething

END;

ENDIF;

4.while循环

WHILEV_TEST=1LOOP

BEGIN

XXXX

END;

ENDLOOP;

5.变量赋值

V_TEST:=123;

6.用forin使用cursor

...

IS

CURSORcurISSELECT*FROMxxx;

BEGIN

FORcur_resultincurLOOP

BEGIN

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

END;

ENDLOOP;

END;

7.带参数的cursor

CURSORC_USER(C_IDNUMBER)ISSELECTNAMEFROMUSERWHERETYPEID=C_ID;

OPENC_USER(变量值);

LOOP

FETCHC_USERINTOV_NAME;

EXITFETCHC_USER%NOTFOUND;

dosomething

ENDLOOP;

CLOSEC_USER;

8.用pl/sqldeveloperdebug

连接数据库后建立一个TestWINDOW

在窗口输入调用SP的代码,F9开始debug,CTRL+N单步调试

关于oracle存储过程的若干问题备忘
1.在oracle中,数据表别名不能加as,如:
oracle proc learning strategiesselect a.appname from appinfo a;-- 正确

selecta.appnamefromappinfoasa;--错误

也许,是怕和oracle中的存储过程中的关键字as冲突的问题吧
2.在存储过程中,select某一字段时,后面必须紧跟into,如果select整个记录,利用游标的话就另当别论了。
oracle proc learning strategies  select af.keynode into kn from APPFOUNDATION af where af.appid=aid and af.foundationid=fid;-- 有into,正确编译

selectaf.keynodefromAPPFOUNDATIONafwhereaf.appid=aidandaf.foundationid=fid;--没有into,编译报错,提示:Compilation

Error:PLS-00428:anINTOclauseisexpectedinthisSELECTstatement

3.在利用select...into...语法时,必须先确保数据库中有该条记录,否则会报出"nodatafound"异常。
   可以在该语法之前,先利用select count(*) from 查看数据库中是否存在该记录,如果存在,再利用select...into...
4.在存储过程中,别名不能和字段名称相同,否则虽然编译可以通过,但在运行阶段会报错
oracle proc learning strategies select keynode into kn from APPFOUNDATION where appid=aid and foundationid=fid;-- 正确运行

selectaf.keynodeintoknfromAPPFOUNDATIONafwhereaf.appid=appidandaf.foundationid=foundationid;--运行阶段报错,提示

ORA-01422:exactfetchreturnsmorethanrequestednumberofrows

5.在存储过程中,关于出现null的问题
假设有一个表A,定义如下:
oracle proc learning strategiescreate table A(

idvarchar2(50)primarykeynotnull,

vcountnumber(8)notnull,

bidvarchar2(50)notnull--外键

);

如果在存储过程中,使用如下语句:
oracle proc learning strategiesselect sum(vcount) into fcount from A where bid='xxxxxx';
如果A表中不存在bid="xxxxxx"的记录,则fcount=null(即使fcount定义时设置了默认值,如:fcount number(8):=0依然无效,fcount还是会变成null),这样以后使用fcount时就可能有问题,所以在这里最好先判断一下:
oracle proc learning strategiesif fcount is null then

fcount:=0;

endif;

这样就一切ok了。
6.Hibernate调用oracle存储过程
oracle proc learning strategies        this.pnumberManager.getHibernateTemplate().execute(

newHibernateCallback()...{

publicObjectdoInHibernate(Sessionsession)

throwsHibernateException,SQLException...{

CallableStatementcs=session

.connection()

.prepareCall("{callmodifyapppnumber_remain(?)}");

cs.setString(1,foundationid);

cs.execute();

returnnull;

}

oracle proc learning strategies                });

if ($ != jQuery) { $ = jQuery.noConflict(); } var isLogined = false; var cb_blogId = 26507; var cb_entryId = 806830; var cb_blogApp = "happyday56"; var cb_blogUserGuid = "ac733d0b-63cf-dd11-9e4d-001cf0cd104b"; var cb_entryCreatedDate = '2007/7/5 11:08:00';

如果单独赋予权限,如下例所示:

grantexecuteonMY_PROCEDUREtoJelly

调用一个存储过程的例子:

executeMY_PROCEDURE('ONEPARAMETER');

存储过程(PROCEDURE)和函数(FUNCTION)的区别。

function有返回值,并且可以直接在Query中引用function和或者使用function的返回

值。

本质上没有区别,都是PL/SQL程序,都可以有返回值。最根本的区别是:存储过程是命

令,而函数是表达式的一部分。比如:

selectmax(NAME)FROM

但是不能execmax(NAME)如果此时max是函数。

PACKAGE是function,procedure,variables和sql语句的组合。package允许多个

procedure使用同一个变量和游标。

创建procedure的语法:

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)AS

BEGIN

UPDATEaccounts

SETbalance=balance+amount

WHEREaccount_id=acc_no;

END;

可以使用createorreplaceprocedure语句,这个语句的用处在于,你之前赋予的

excute权限都将被保留。

IN,OUT,INOUT用来修饰参数。

IN表示这个变量必须被调用者赋值然后传入到PROCEDURE进行处理。

OUT表示PRCEDURE通过这个变量将值传回给调用者。

INOUT则是这两种的组合。

authid代表两种权限:

定义者权限(difinerright默认),执行者权限(invokerright)。

定义者权限说明这个procedure中涉及的表,视图等对象所需要的权限只要定义者拥有

权限的话就可以访问。

执行者权限则需要调用这个procedure的用户拥有相关表和对象的权限。

Oracle存储过程的基本语法

1.基本结构

CREATEORREPLACEPROCEDURE存储过程名字

(

参数1INNUMBER,

参数2INNUMBER

)AS

变量1INTEGER:=0;

变量2DATE;

BEGIN

END存储过程名字

2.SELECTINTOSTATEMENT

将select查询的结果存入到变量中,可以同时将多个列存储多个变量中,必须有一条

记录,否则抛出异常(如果没有记录抛出NO_DATA_FOUND)

例子:

BEGIN

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

EXCEPTION

WHENNO_DATA_FOUNDTHEN

xxxx;

END;

...

3.IF判断

IFV_TEST=1THEN

BEGIN

dosomething

END;

ENDIF;

4.while循环

WHILEV_TEST=1LOOP

BEGIN

XXXX

END;

ENDLOOP;

5.变量赋值

V_TEST:=123;

6.用forin使用cursor

...

IS

CURSORcurISSELECT*FROMxxx;

BEGIN

FORcur_resultincurLOOP

BEGIN

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

END;

ENDLOOP;

END;

7.带参数的cursor

CURSORC_USER(C_IDNUMBER)ISSELECTNAMEFROMUSERWHERETYPEID=C_ID;

OPENC_USER(变量值);

LOOP

FETCHC_USERINTOV_NAME;

EXITFETCHC_USER%NOTFOUND;

dosomething

ENDLOOP;

CLOSEC_USER;

8.用pl/sqldeveloperdebug

连接数据库后建立一个TestWINDOW

在窗口输入调用SP的代码,F9开始debug,CTRL+N单步调试

9.Pl/Sql中执行存储过程

在sql*plus中:

declare

--必要的变量声明,视你的过程而定

begin

executeyourprocudure(parameter1,parameter2,...);

end

/

在SQL/PLUS中调用存储过程,显示结果:

SQL>setserveoutputon--打开输出

SQL>varinfo1number;--输出1

SQL>varinfo2number;--输出2

SQL>declare

var1varchar2(20);--输入1

var2varchar2(20);--输入2

var3varchar2(20);--输入2

BEGIN

pro(var1,var2,var3,:info1,:info2);

END;

/

SQL>printinfo1;

SQL>printinfo2;

注:在EXECUTEIMMEDIATESTR语句是SQLPLUS中动态执行语句,它在执行中会自动

提交,类似于DP中FORMS_DDL语句,在此语句中str是不能换行的,只能通过连接字符"||",

或着在在换行时加上"-"连接字符。

关于Oracle存储过程的若干问题备忘

1.在Oracle中,数据表别名不能加as。

如:

selecta.appnamefromappinfoa;--正确

selecta.appnamefromappinfoasa;--错误

也许,是怕和Oracle中的存储过程中的关键字as冲突的问题吧

2.在存储过程中,select某一字段时,后面必须紧跟into,

如果select整个记录,利用游标的话就另当别论了。

selectaf.keynodeintokn

fromAPPFOUNDATIONaf

whereaf.appid=aidandaf.foundationid=fid;--有into,正确编译

selectaf.keynode

fromAPPFOUNDATIONaf

whereaf.appid=aidandaf.foundationid=fid;--没有into,编译报错,提示:

CompilationError:PLS-00428:anINTOclauseisexpectedinthisSELECTstatement

3.在利用select...into...语法时,必须先确保数据库中有该条

记录,否则会报出"nodatafound"异常。

可以在该语法之前,先利用selectcount(*)from查看数据库中是否存在该记录,如果存

在,再利用select...into...

4.在存储过程中,别名不能和字段名称相同,否则虽然编译

可以通过,但在运行阶段会报错

selectkeynodeintoknfromAPPFOUNDATIONwhereappid=aidandfoundationid=fid;

--正确运行

selectaf.keynodeintoknfromAPPFOUNDATIONafwhereaf.appid=appidandaf.foundationid

=foundationid;

--运行阶段报错,提示:

ORA-01422:exactfetchreturnsmorethanrequestednumberofrows

5.在存储过程中,关于出现null的问题

假设有一个表A,定义如下:

createtableA(

idvarchar2(50)primarykeynotnull,

vcountnumber(8)notnull,

bidvarchar2(50)notnull--外键

);

如果在存储过程中,使用如下语句:

selectsum(vcount)intofcountfromAwherebid='xxxxxx';

如果A表中不存在bid="xxxxxx"的记录,则fcount=null(即使fcount定义时设置了默认值,

如:fcountnumber(8):=0依然无效,fcount还是会变成null),这样以后使用fcount时就可能

有问题,所以在这里最好先判断一下:

iffcountisnullthen

fcount:=0;

endif;

这样就一切ok了。

6.Hibernate调用Oracle存储过程

this.pnumberManager.getHibernateTemplate().execute(

newHibernateCallback()...{

publicObjectdoInHibernate(Sessionsession)

throwsHibernateException,SQLException...{

CallableStatementcs=session

.connection()

.prepareCall("{call

modifyapppnumber_remain(?)}");

cs.setString(1,foundationid);

cs.execute();

returnnull;

}

});

用Java调用Oracle存储过程总结

一、无返回值的存储过程

测试表:

--Createtable

createtableTESTTB

(

IDVARCHAR2(30),

NAMEVARCHAR2(30)

)t

ablespaceBOM

pctfree10

initrans1

maxtrans255

storage

(

initial64K

minextents1

maxextentsunlimited

);

例:存储过程为(当然了,这就先要求要建张表TESTTB,里面两个字段(I_ID,I_NAME)。

):

CREATEORREPLACEPROCEDURETESTA(PARA1INVARCHAR2,PARA2INVARCHAR2)

AS

BEGIN

INSERTINTOBOM.TESTTB(ID,NAME)VALUES(PARA1,PARA2);

ENDTESTA;

在Java里调用时就用下面的代码:

packagecom.yiming.procedure.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:@10.20.30.30: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();

}i

f(conn!=null){

conn.close();

}

}

}catch(SQLExceptionex1){

}

}

}

}

二、有返回值的存储过程(非列表)

例:存储过程为:

CREATEORREPLACEPROCEDURETESTB(PARA1INVARCHAR2,PARA2OUTVARCHAR2)

AS

BEGIN

SELECTNAMEINTOPARA2FROMTESTTBWHEREID=PARA1;

ENDTESTB;

在Java里调用时就用下面的代码:

packagecom.yiming.procedure.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:@10.20.30.30: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)中的数值2并非任意的,而是和存储过程中的out列对应

的,如果out是在第一个位置,那就是proc.getString(1),如果是第三个位置,就是

proc.getString(3),当然也可以同时有多个返回值,那就是再多加几个out参数了。

三、返回列表

由于Oracle存储过程没有返回值,它的所有返回值都是通过out参数来替代的,列表

同样也不例外,但由于是集合,所以不能用一般的参数,必须要用pagkage了.所以要分两

部分,

1.建一个程序包。如下:

CREATEORREPLACEPACKAGETESTPACKAGEAS

TYPETEST_CURSORISREFCURSOR;

endTESTPACKAGE;

2.建立存储过程,存储过程为:

CREATEORREPLACEPROCEDURETESTC(P_CURSORout

TESTPACKAGE.TEST_CURSOR)IS

BEGIN

OPENP_CURSORFOR

SELECT*FROMBOM.TESTTB;

ENDTESTC;

可以看到,它是把游标(可以理解为一个指针),作为一个out参数来返回值的。

在Java里调用时就用下面的代码:

在这里要注意,在执行前一定要先把Oracle的驱动包放到class路径里,否则会报错的。

packagecom.yiming.procedure.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:@10.20.30.30: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();

}i

f(conn!=null){

conn.close();

}

}

}catch(SQLExceptionex1){

}

}

}

}

在存储过程中做简单动态查询

在存储过程中做简单动态查询代码,例如:

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程序设计中,在DML和事务控制的语句中可以直接使用SQL,但是DDL

语句及系统控制语句却不能在PL/SQL中直接使用,要想实现在PL/SQL中使用DDL语句及系

统控制语句,可以通过使用动态SQL来实现。

首先我们应该了解什么是动态SQL,在Oracle数据库开发PL/SQL块中我们使用的SQL

分为:静态SQL语句和动态SQL语句。所谓静态SQL指在PL/SQL块中使用的SQL语句在编

译时是明确的,执行的是确定对象。而动态SQL是指在PL/SQL块编译时SQL语句是不确定

的,如根据用户输入的参数的不同而执行不同的操作。编译程序对动态语句部分不进行处理,

只是在程序运行时动态地创建语句、对语句进行语法分析并执行该语句。

Oracle中动态SQL可以通过本地动态SQL来执行,也可以通过DBMS_SQL包来执行。下面就

这两种情况分别进行说明:

一、本地动态SQL

本地动态SQL是使用EXECUTEIMMEDIATE语句来实现的。

1、本地动态SQL执行DDL语句:

需求:根据用户输入的表名及字段名等参数动态建表。

createorreplaceprocedureproc_test

(

table_nameinvarchar2,--表名

field1invarchar2,--字段名

datatype1invarchar2,--字段类型

field2invarchar2,--字段名

datatype2invarchar2--字段类型

)as

str_sqlvarchar2(500);

begin

str_sql:=’create

table’||table_name||’(’||field1||’’||datatype1||’,’||field2||’’||datatype2||’)’;

executeimmediatestr_sql;--动态执行DDL语句

exception

whenothersthen

null;

end;

以上是编译通过的存储过程代码。下面执行存储过程动态建表。

SQL>executeproc_test(’dinya_test’,’id’,’number(8)notnull’,’name’,’varchar2(100)’);

PL/SQLproceduresuccessfullycompleted

SQL>descdinya_test;

NameTypeNullableDefaultComments

----------------------------------------

IDNUMBER(8)

NAMEVARCHAR2(100)Y

SQL>

到这里,就实现了我们的需求,使用本地动态SQL根据用户输入的表名及字段名、字段

类型等参数来实现动态执行DDL语句。

2、本地动态SQL执行DML语句。

需求:将用户输入的值插入到上例中建好的dinya_test表中。

createorreplaceprocedureproc_insert

(

idinnumber,--输入序号

nameinvarchar2--输入姓名

)as

str_sqlvarchar2(500);

begin

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

executeimmediatestr_sqlusingid,name;--动态执行插入操作

exception

whenothersthen

null;

end;

执行存储过程,插入数据到测试表中。

SQL>executeproc_insert(1,’dinya’);

PL/SQLproceduresuccessfullycompleted

SQL>select*fromdinya_test;

IDNAME

1dinya

在上例中,本地动态SQL执行DML语句时使用了using子句,按顺序将输入的值绑定

到变量,如果需要输出参数,可以在执行动态SQL的时候,使用RETURNINGINTO子句,如:

declare

p_idnumber:=1;

v_countnumber;

begin

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

executeimmediatev_stringintov_countusingp_id;

end;

二、使用DBMS_SQL包

使用DBMS_SQL包实现动态SQL的步骤如下:

A、先将要执行的SQL语句或一个语句块放到一个字符串变量中。

B、使用DBMS_SQL包的parse过程来分析该字符串。

C、使用DBMS_SQL包的bind_variable过程来绑定变量。

D、使用DBMS_SQL包的execute函数来执行语句。

1、使用DBMS_SQL包执行DDL语句

需求:使用DBMS_SQL包根据用户输入的表名、字段名及字段类型建表。

createorreplaceprocedureproc_dbms_sql

(

table_nameinvarchar2,--表名

field_name1invarchar2,--字段名

datatype1invarchar2,--字段类型

field_name2invarchar2,--字段名

datatype2invarchar2--字段类型

)as

v_cursornumber;--定义光标

v_stringvarchar2(200);--定义字符串变量

v_rownumber;--行数

begin

v_cursor:=dbms_sql.open_cursor;--为处理打开光标

v_string:=’create

table’||table_name||’(’||field_name1||’’||datatype1||’,’||field_name2||’’||datatype2||’)’;

dbms_sql.parse(v_cursor,v_string,dbms_sql.native);--分析语句

v_row:=dbms_sql.execute(v_cursor);--执行语句

dbms_sql.close_cursor(v_cursor);--关闭光标

exception

whenothersthen

dbms_sql.close_cursor(v_cursor);--关闭光标

raise;

end;

以上过程编译通过后,执行过程创建表结构:

SQL>executeproc_dbms_sql(’dinya_test2’,’id’,’number(8)notnull’,’name’,’varchar2(100)’);

PL/SQLproceduresuccessfullycompleted

SQL>descdinya_test2;

NameTypeNullableDefaultComments

----------------------------------------

IDNUMBER(8)

NAMEVARCHAR2(100)Y

SQL>

2、使用DBMS_SQL包执行DML语句

需求:使用DBMS_SQL包根据用户输入的值更新表中相对应的记录。

查看表中已有记录:

SQL>select*fromdinya_test2;

IDNAME

1Oracle

2CSDN

3ERP

SQL>

建存储过程,并编译通过:

createorreplaceprocedureproc_dbms_sql_update

(

idnumber,

namevarchar2

)as

v_cursornumber;--定义光标

v_stringvarchar2(200);--字符串变量

v_rownumber;--行数

begin

v_cursor:=dbms_sql.open_cursor;--为处理打开光标

v_string:=’updatedinya_test2aseta.name=:p_namewherea.id=:p_id’;

dbms_sql.parse(v_cursor,v_string,dbms_sql.native);--分析语句

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);--执行动态SQL

dbms_sql.close_cursor(v_cursor);--关闭光标

exception

whenothersthen

dbms_sql.close_cursor(v_cursor);--关闭光标

raise;

end;

执行过程,根据用户输入的参数更新表中的数据:

SQL>executeproc_dbms_sql_update(2,’csdn_dinya’);

PL/SQLproceduresuccessfullycompleted

SQL>select*fromdinya_test2;

IDNAME

1Oracle

2csdn_dinya

3ERP

SQL>

执行过程后将第二条的name字段的数据更新为新值csdn_dinya。这样就完成了使用

dbms_sql包来执行DML语句的功能。

使用DBMS_SQL中,如果要执行的动态语句不是查询语句,使用DBMS_SQL.Execute或

DBMS_SQL.Variable_Value来执行,如果要执行动态语句是查询语句,则要使用

DBMS_SQL.define_column定义输出变量,然后使用DBMS_SQL.Execute,

DBMS_SQL.Fetch_Rows,DBMS_SQL.Column_Value及DBMS_SQL.Variable_Value来执行查询并

得到结果。

总结说明:

在Oracle开发过程中,我们可以使用动态SQL来执行DDL语句、DML语句、事务控制

语句及系统控制语句。但是需要注意的是,PL/SQL块中使用动态SQL执行DDL语句的时候

与别的不同,在DDL中使用绑定变量是非法的(bind_variable(v_cursor,’:p_name’,name)),分

析后不需要执行DBMS_SQL.Bind_Variable,直接将输入的变量加到字符串中即可。另外,DDL

是在调用DBMS_SQL.PARSE时执行的,所以DBMS_SQL.EXECUTE也可以不用,即在上例中的

v_row:=dbms_sql.execute(v_cursor)部分可以不要。

Oracle存储过程调用Java方法

存储过程中调用Java程序段

软件环境:

1、操作系统:Windows2000Server

2、数据库:Oracle8iR2(8.1.7)forNT企业版

3、安装路径:C:\ORACLE

实现方法:

1、创建一个文件为Test.java

publicclassTest{

publicstaticvoidmain(Stringargs[]){

System.out.println("HELLOTHISiSAJavaPROCEDURE");

}

}

2、javacTest.java

3、javaTest

4、SQL>connsystem/manager

SQL>grantcreateanydirectorytoscott;

SQL>connscott/tiger

SQL>createorreplacedirectorytest_diras'd:\';

目录已创建。

SQL>createorreplacejavaclassusingbfile(test_dir,'TEST.CLASS')

2/

Java已创建。

SQL>selectobject_name,object_type,STATUSfromuser_objects;

SQL>createorreplaceproceduretest_java

aslanguagejava

name'TEST.main(java.lang.String[])';

/

过程已创建。

SQL>setserveroutputonsize5000

SQL>calldbms_java.set_output(5000);

调用完成。

SQL>executetest_java;

HELLOTHISiSAJavaPROCEDURE

PL/SQL过程已成功完成。

SQL>calltest_java();

HELLOTHISiSAJavaPROCEDURE

调用完成。

Oracle8I9I都测试通过。

Oracle高效分页存储过程实例

createorreplacepackagep_pageis

--Author:PHARAOHS

--Created:2006-4-3014:14:14

--Purpose:分页过程

TYPEtype_curISREFCURSOR;--定义游标变量用于返回记录集

PROCEDUREPagination(

Pindexinnumber,--分页索引

Psqlinvarchar2,--产生dataset的sql语句

Psizeinnumber,--页面大小

Pcountoutnumber,--返回分页总数

v_curouttype_cur--返回当前页数据记录

);

procedurePageRecordsCount(

Psqlcountinvarchar2,--产生dataset的sql语句

Prcountoutnumber--返回记录总数

);

endp_page;

/

createorreplacepackagebodyp_pageis

PROCEDUREPagination(

Pindexinnumber,

Psqlinvarchar2,

Psizeinnumber,

Pcountoutnumber,

v_curouttype_cur

)

AS

v_sqlVARCHAR2(1000);

v_countnumber;

v_Plownumber;

v_Pheinumber;

Begin

------------------------------------------------------------取分页总数

v_sql:='selectcount(*)from('||Psql||')';

executeimmediatev_sqlintov_count;

Pcount:=ceil(v_count/Psize);

------------------------------------------------------------显示任意页内容

v_Phei:=Pindex*Psize+Psize;

v_Plow:=v_Phei-Psize+1;

--Psql:='selectrownumrn,t.*fromzzdat';--要求必须包含rownum字段

v_sql:='select*from('||Psql||')wherernbetween'||v_Plow||'and'||v_Phei;

openv_curforv_sql;

EndPagination;

--*****************************************************************************

*********

procedurePageRecordsCount(

Psqlcountinvarchar2,

Prcountoutnumber

)

as

v_sqlvarchar2(1000);

v_prcountnumber;

begin

v_sql:='selectcount(*)from('||Psqlcount||')';

executeimmediatev_sqlintov_prcount;

Prcount:=v_prcount;--返回记录总数

endPageRecordsCount;

--*****************************************************************************

*********

endp_page;

/

相关推荐