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_poin...全部
环境：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。收起
