考试首页 | 考试用书 | 培训课程 | 模拟考场 | 考试论坛  
  当前位置:编程开发 > ORACLE > 文章内容
  

ORACLE存储过程的数组参数

 [ 2012年2月21日 ] 【

ORACLE存储过程的数组参数

环境:Eclipse+Oracle9.0.2+Tomcat5.5

功能:采用存储过程、type组合来实现批量入库,以节省系统开销,提高效率。

*

sql脚本+测试代码:

* 1)create or replace type t_cableLine_point as object

ID NUMBER(10),

CABLELINEID NUMBER(10),

ROADPOINTID NUMBER(10),

ORDERNUM NUMBER(10),

REMARK NUMBER(10)

* 2)CREATE OR REPLACE TYPE ARRAY_cableLine_point AS table OF t_cableLine_point

* 3)create table RSC_CABLELINE_POINT

ID NUMBER(10) not null,

CABLELINEID NUMBER(10) not null,

ROADPOINTID NUMBER(10) not null,

ORDERNUM NUMBER(10),

REMARK NUMBER(10)

* 4)create or replace procedure batch_cableline_point(i_object in ARRAY_cableLine_point) is

begin

insert into RSC_CABLELINE_POINT

(ID, CABLELINEID, ROADPOINTID, ORDERNUM, REMARK)

select ID, CABLELINEID, ROADPOINTID, ORDERNUM, REMARK

from the (select cast(i_object as ARRAY_cableLine_point) from dual);

end batch_cableline_point;

* 5)测试代码:

package com.nilpower.test;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

public class Test {

public static void main(String[] args) {

try {

Class.forName(“oracle.jdbc.driver.OracleDriver”);

String url = “jdbc:oracle:thin:@127.0.0.1:1521:nilpower”;

Connection con = DriverManager.getConnection(url, “scott”, “tiger”);

PreparedStatement pstmt = null;

String sql = “{call batch_cableline_point(?)}”;

pstmt = con.prepareCall(sql);

Object[][] object1 = new Object[10][5];

int max = 3615142;// 由于表有索引

for (int i = 0; i 《 10; i++) {

object1[i][0] = ++max;

object1[i][1] = 158870593;

object1[i][2] = 333;

object1[i][3] = 444;

object1[i][4] = 555;

}

oracle.sql.ArrayDescriptor desc = oracle.sql.ArrayDescriptor

.createDescriptor(“ARRAY_CABLELINE_POINT”, con);

oracle.sql.ARRAY array = new oracle.sql.ARRAY(desc, con, object1);

pstmt.setArray(1, array);

pstmt.executeUpdate();

} catch (Exception e) {

e.printStackTrace();

}

}

}

备注:如果在入库的过程中发现字符串的值没有入进去,请检查有没有加载该类库nls_charset12.jar

* 该文章是参考“风雪”文章后个人试验的存档。

*

* 其他参考:(引用2008-5-24 04:49 Aowken)

* Tomcat+Oracle调用存储过程郁闷之旅

今天在改公司管理系统的时候,因为某个功能需要使用数组类型作为IN参数调用存储过程,看了看文档发现有Varray、nested table,但Varray有个最多数量的限制,也只好用nested table了,于是引发一连串的问题。

环境:

java version “1.6.0_05”

Java(TM) SE Runtime Environment (build 1.6.0_05-b13)

Java HotSpot(TM) Client VM (build 10.0-b19, mixed mode, sharing)

apache-tomcat-6.0.16

Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production

PL/SQL Release 9.2.0.1.0 - Production

CORE 9.2.0.1.0 Production

TNS for 32-bit Windows: Version 9.2.0.1.0 - Production

NLSRTL Version 9.2.0.1.0 - Production

本来对Java这种据说很先进的东西就懵懵懂懂,索性就真的以为他非常牛X。使用几维数组作为参数调用存储过程还不是跟Set个String一样那么简单,但其实我错了,所以我也对java很失望,他远不如想象中那么XX。

Object arrInt[] = {0,1,2,3,4,5,6};

callStmt.SetObject(1, arrInt, Types.ARRAY);

要是想像上面这样操作他就会抛个java.lang.ClassCastException: [Ljava.lang.Object; cannot be cast to oracle.sql.ARRAY,于是我知道java他不会自己转,非得人工干预。但我突然发现自己很愚蠢,我都没告诉他procedure参数的类型,即使可以转过去又有个P用,百度了一下才知道得用下面的法子。

import oracle.sql.ARRAY;

import oracle.sql.ArrayDescriptor;

Connnection conn = DBConnManager.getConnection();

callStmt = conn.prepareCall(sql);

ArrayDescriptor arrDesc = ArrayDescriptor.createDescriptor(“dbms_sql.Varchar2_Table”, conn);

ARRAY arr = new ARRAY(arrDesc, conn, arrInt);

执行一下,结果异常。我靠数据库里能用dbms_sql.Varchar2_Table声明,他居然 java.sql.SQLException: 无效的名称模式: DBMS_SQL.VARCHAR2_TABLE。我心想是不是得写成SYS.dbms_sql.Varchar2_Table,结果还是一样。再百度,人们说这样不行,原因。。.不知道,但必须得自己定义类型才可以。于是我不得不

create type numbertable as table of number;

ArrayDescriptor arrDesc = ArrayDescriptor.createDescriptor(“numbertable”, conn);

ARRAY arr = new ARRAY(arrDesc, conn, arrInt);

结果又来了个java.sql.SQLException: 无效的名称模式: baby.numbertable。我无语还得百度!@#¥%.。。.N久无果!但我发别人的码的代码里这个类型都是大写,于是我也写成 NUMBERTABLE。哈哈,果然不出那个异常了。但他NND又蹦个java.lang.ClassCastException: org.apache.tomcat.dbcp.dbcp.PoolingDataSource$PoolGuardConnectionWrapper cannot be cast to oracle.jdbc.OracleConnection出来。这下郁闷了,莫非从DBCP拿出来的Connection跟 OracleConnection不一样。晕了,别介呀,我对java不懂!又search了半天,发现了一个UnWrapper,本以为能把这个 Wrapper给干掉的,但搞了半天没搞明白。过了XX时间,不知道是在哪国网站上看到有人

ArrayDescriptor arrDesc = ArrayDescriptor.createDescriptor(“NUMBERTABLE”, ((DelegatingConnection)conn).getDelegate()));

他们用着都好用,到我这((DelegatingConnection)conn).getDelegate()出来的Connection是个null,很郁闷。后来又看到

public static Connection getNativeConnection(Connection con) throws SQLException {

if (con instanceof DelegatingConnection) {

Connection nativeCon = ((DelegatingConnection) con).getInnermostDelegate();

// For some reason, the innermost delegate can be null: not for a

// Statement‘s Connection but for the Connection handle returned by the pool.

// We’ll fall back to the MetaData‘s Connection in this case, which is

// a native unwrapped Connection with Commons DBCP 1.1.

return (nativeCon != null ? nativeCon : con.getMetaData().getConnection());

}

return con;

}

可((DelegatingConnection) con).getInnermostDelegate();依然是null但con.getMetaData().getConnection());得到了一个OracleConnection,debug时看着eclipse variables的窗口心中一阵暗喜,该OK了吧!

哎,事实上最近一段时间总是事与愿违,执行-又异常了!郁闷,一次比一次郁闷,一次比一次怪异!

java.lang.ClassCastException: oracle.jdbc.driver.OracleConnection cannot be cast to oracle.jdbc.OracleConnection 由于字符串太长搜都搜不到,想了好久,尝试着各种各样的方法!终于有一个次把tomcat/lib目录classes12.jar删掉,没有异常,一切 OK!但后来把classes12.jar又仍进去了,还正常的,代码没有一点变化!很是郁闷,但既然问题没了,也就懒得看了!

最后的代码:

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

public static Connection getConnection() {

Connection con = null;

try {

Context ic = new InitialContext();

DataSource ds = (DataSource) ic.lookup(“java:comp/env/jdbc/yoyo”);

con = ds.getConnection();

} catch (Exception e) {

System.out.println(“**** error DataSource”);

}

return con;

}

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

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.CallableStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Types;

import java.util.ArrayList;

import oracle.sql.ARRAY;

import oracle.sql.ArrayDescriptor;

import org.apache.tomcat.dbcp.dbcp.DelegatingConnection;

public class BussinessLog {

public static ArrayList CancelLog(String sLoginUser, Object[] arrLogID)

{

ArrayList arrList = new ArrayList();

Connection conn = null;

CallableStatement callStmt = null;

String sql = null;

ArrayDescriptor arrDesc = null;

try

{

conn = DbConnectionManager.getConnection();

sql = “{call P_CanceltLog(?,?,?,?)}”;

callStmt = conn.prepareCall(sql);

arrDesc = ArrayDescriptor.createDescriptor(“NUMBERTABLE”, getNativeConnection(conn));

ARRAY arr = new ARRAY(arrDesc, getNativeConnection(conn), arrLogID);

callStmt.setString(1, sLoginUser);

callStmt.setObject(2, arr, Types.ARRAY);

callStmt.registerOutParameter(3, Types.VARCHAR);

callStmt.registerOutParameter(4, Types.INTEGER);

callStmt.execute();

arrList.add(callStmt.getInt(4));

arrList.add(callStmt.getString(3));

return arrList;

} catch (Exception e) {

System.out.println(e.toString());

} finally {

DbAction.clear(conn, callStmt);

}

return arrList;

}

public static Connection getNativeConnection(Connection con) throws SQLException {

if (con instanceof DelegatingConnection) {

Connection nativeCon = ((DelegatingConnection) con).getInnermostDelegate();

// For some reason, the innermost delegate can be null: not for a

// Statement’s Connection but for the Connection handle returned by the pool.

// We‘ll fall back to the MetaData’s Connection in this case, which is

// a native unwrapped Connection with Commons DBCP 1.1.

return (nativeCon != null ? nativeCon : con.getMetaData().getConnection());

}

return con;

}

}

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

A. 在这之前我还下载了最新的commons-dbcp-1.2.2.jar,但使用里面DelegatingConnection时,con instanceof DelegatingConnection是false,看来tomcat里出来的Connection就的配Tomcat\lib\tomcat- dbcp.jar里的DelegatingConnection,还真是什么枪打什么鸟。

B.偶尔发现 ((DelegatingConnection) con).getInnermostDelegate();之所以返回null是因为tomcat里Context.Resource的 accessToUnderlyingConnectionAllowed参数默认为false所致,被设置成true之后是可以取到 OracleConnection的.

本文纠错】【告诉好友】【打印此文】【返回顶部

考试用书

全国计算机应用能力考试用书-红旗LinuxDesktop6.0操作系统全国计算机应用能力考试用书-红旗Linux
ISBN编号: 9787512904538 出版时间: 2013-3 出版社: 中国人事出版社 页数:..
定价:¥45 优惠价:¥45  更多书籍
全国专业技术人员计算机应用能力考试用书Word2007中文字处理全国专业技术人员计算机应用能力考试用
ISBN编号: 9787512904569 出版时间: 2013-4 出版社: 中国人事出版社 页数:..
定价:¥45 优惠价:¥45  更多书籍
将考试通添加到收藏夹 | 每次上网自动访问考试通 | 复制本页地址,传给QQ/MSN上的好友 | 申请链接 | 意见留言 TOP
关于本站  网站声明  广告服务  联系方式  站内导航  考试论坛
Copyright © 2007-2013 中华考试网(Examw.com) All Rights Reserved