Oracle存储过程的数组参数

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

sql脚本：

View Code

1 CREATE OR REPLACE TYPE WANGCHY.t_cableLine_point AS OBJECT
2 (
3   ID NUMBER(10),
4   CABLELINEID NUMBER(10),
5   ROADPOINTID NUMBER(10),
6   ORDERNUM NUMBER(10),
7   REMARK NUMBER(10)
8 )

View Code

1 CREATE OR REPLACE TYPE WANGCHY.ARRAY_cableLine_point AS table OF t_cableLine_point

View Code

1 CREATE TABLE WANGCHY.RSC_CABLELINE_POINT
2 (
3   ID           NUMBER(10)                       NOT NULL,
4   CABLELINEID  NUMBER(10)                       NOT NULL,
5   ROADPOINTID  NUMBER(10)                       NOT NULL,
6   ORDERNUM     NUMBER(10),
7   REMARK       NUMBER(10)
8 )

View Code

1 CREATE OR REPLACE procedure WANGCHY.batch_cableline_point(i_object in ARRAY_cableLine_point)is 
2     begin 
3         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); 
4         
5     end batch_cableline_point;

java代码实现:

View Code

 1 package com.test.oracle;
 2 
 3 import java.sql.Connection;
 4 
 5 import java.sql.DriverManager;
 6 
 7 import java.sql.PreparedStatement;
 8 
 9 public class Test {
10 
11     public static void main(String[] args) {
12 
13         try {
14 
15             Class.forName("oracle.jdbc.driver.OracleDriver");
16 
17             String url = "jdbc:oracle:thin:@10.10.71.24:1521:orcl";
18 
19             Connection con = DriverManager.getConnection(url, "wangchy", "wangchy");
20 
21             PreparedStatement pstmt = null;
22 
23             String sql = "{call batch_cableline_point(?)}";
24 
25             pstmt = con.prepareCall(sql);
26 
27             Object[][] object1 = new Object[10][5];
28 
29             int max = 3615142;// 由于表有索引
30 
31             for (int i = 0; i < 10; i++) {
32 
33                 object1[i][0] = ++max;
34 
35                 object1[i][1] = 158870593;
36 
37                 object1[i][2] = 333;
38 
39                 object1[i][3] = 444;
40 
41                 object1[i][4] = 555;
42 
43             }
44 
45             oracle.sql.ArrayDescriptor desc = oracle.sql.ArrayDescriptor
46 
47             .createDescriptor("ARRAY_CABLELINE_POINT", con);
48 
49             oracle.sql.ARRAY array = new oracle.sql.ARRAY(desc, con, object1);
50 
51             pstmt.setArray(1, array);
52 
53             pstmt.executeUpdate();
54 
55         } catch (Exception e) {
56 
57             e.printStackTrace();
58 
59         }
60 
61     }
62 }