oracle存储过程数组用法,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。

全部