Oracle bulk Insert的用法

最新推荐文章于 2025-03-24 11:08:04 发布

zxy838279821

最新推荐文章于 2025-03-24 11:08:04 发布

阅读量9.3k

点赞数

分类专栏： ORACLE数据库

本文链接：https://blog.csdn.net/zxy838279821/article/details/8217364

版权

ORACLE数据库专栏收录该内容

8 篇文章

订阅专栏

本文介绍如何使用Oracle PL/SQL过程和Java程序实现数据批量插入到Oracle数据库的方法。通过定义类型、创建存储过程并结合Java代码调用，实现了高效的数据批量加载。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

首先创建一个简单的数据库表：

create table T_NAME
(
id NUMBER not null,
name VARCHAR2(20)
)

alter table T_NAME
add constraint PRIMARY_ID primary key (ID);

字段	字段长度	Nullable	Key
ID	NUMBER(6)	Not Nulll	Primary
NAME	varchar2(20)

根据相应的表结构创建过程

create or replace type ID_TABLE is table of number(10);
create or replace type NAME_TABLE is table of varchar2(20);
create or replace procedure pro_foralls_insert(v_1 ID_TABLE ,v_2 NAME_TABLE )
as
c integer;
begin
forall i in 1.. v_1.count
insert into T_NAME values(v_1(i),v_2(i));
end;

接着就写相应的java程序做测试

import java.sql.*;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;

public class TestTest {
private static final String sql="select max(id) from T_NAME";
private static final String jdbcUrl="jdbc:oracle:thin:@10.108.67.51:1521:orcl";
private static final String jdbcUser="b2cmonitor";
private static final String jdbcPassword="b2cmonitor";
private static int maxID;
public TestTest() {
Connection conn = null;
Statement stmt=null;
CallableStatement cstmt = null;
ResultSet rs=null;

try {
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection(jdbcUrl, jdbcUser,jdbcPassword);
conn.setAutoCommit(false);
stmt=conn.createStatement();
rs=stmt.executeQuery(sql);
while(rs.next()){
maxID=rs.getInt("MAX(ID)");
}
}
catch (ClassNotFoundException ex) {
ex.printStackTrace();
}
catch (SQLException ex) {
ex.printStackTrace();
}

ArrayDescriptor a = null;
ArrayDescriptor b = null;
Object[] s1 = new Object[100000];
Object[] s2 = new Object[100000];
System.out.println(maxID);
for (int i = 0; i < 100000; i++) {
s1[i] = i+1+maxID;
s2[i] = "SSSS";
}
try {
a = oracle.sql.ArrayDescriptor.createDescriptor("ID_TABLE", conn);
b = oracle.sql.ArrayDescriptor.createDescriptor("NAME_TABLE", conn);
ARRAY a_test = new ARRAY(a, conn, s1);
ARRAY b_test = new ARRAY(b, conn, s2);
cstmt = conn.prepareCall("{ call pro_foralls_insert(?,?) }");
cstmt.setObject(1, a_test);
cstmt.setObject(2, b_test);
System.out.println(System.currentTimeMillis());
cstmt.execute();
conn.commit();
rs.close();
rs=null;
stmt.close();
stmt=null;
cstmt.close();
cstmt=null;
conn.close();
conn=null;

} catch (SQLException e) {
e.printStackTrace();
}

}
public static void main(String args[]) {
TestTest a = new TestTest();
}

}