查看: 3998|回复: 6
如何在oracle中实现sql server中的bulk insert功能
论坛徽章:0
因为我要把大量的文本文件导入到表中，是通过odbc发送bulk insert语句来实现的。我想询问一下oracle有这样的语句吗？不是sqlload命令。请高手指点吧
论坛徽章:24
从文件导入数据使用sqlldr更好些
论坛徽章:3
你可以在pl/sql develper中直接复制
论坛徽章:0
我知道用sqlldr，但是我不知道这条命令他会执行多长时间的阿，我不能去盯着看的哦，我想通过odbc调用函数，这样可以返回一个值来判断是否完成了哦。还有什么方法吗？
论坛徽章:36
论坛徽章:0
你说的forall怎么使用呢？
是创建存储过程使用？
论坛徽章:0
FORALL i IN 1..deptnums.COUNT
& &&&INSERT INTO emp_by_dept
& && &&&SELECT employee_id, department_id FROM employees
& && && &&&WHERE department_id = deptnums(i);
itpub.net All Right Reserved. 北京皓辰网域网络信息技术有限公司版权所有　　　　
　北京市公安局海淀分局网监中心备案编号：　广播电视节目制作经营许可证：编号（京）字第1149号[Oracle] Bulk Insert Data - Oracle - 次元立方网 - 电脑知识与技术互动交流平台
[Oracle] Bulk Insert Data
命名空间：Oracle.DataAccess.Client
组件：Oracle.DataAccess.dll（2.112.1.0）
ODP.NET 版本：ODP.NET for .NET Framework 2.0 或 ODP.NET for .NET Framework 4
工具：Microsoft Visual Studio Ultimate 2013 + Oracle SQL Developer 1.5.5 + Oracle Database 11g Enterprise Edition 11.2.0.1.0(32位) + TNS for 32-bit Windows 11.2.0.1.0
方式一：ArrayBind
当插入一条数据时，SQL语句如下：
INSERT INTO table_name VALUES (:col1, :col2, :col3, :col4, :col5)
1 public void InsertDataRow(Dictionary&string, object& dataRow)
StringBuilder sbCmdText = new StringBuilder();
sbCmdText.AppendFormat('INSERT INTO {0}(', m_TableName);
sbCmdText.Append(string.Join(',', dataRow.Keys.ToArray()));
sbCmdText.Append(') VALUES (');
sbCmdText.Append(':' + string.Join(',:', dataRow.Keys.ToArray()));
sbCmdText.Append(')');
using (OracleConnection conn = new OracleConnection())
using (OracleCommand cmd = conn.CreateCommand())
mandType = CommandType.T
mandText = sbCmdText.ToString();
OracleParameter parameter =
OracleDbType dbType = OracleDbType.O
foreach (string colName in dataRow.Keys)
dbType = GetOracleDbType(dataRow[colName]);
parameter = new OracleParameter(colName, dbType);
parameter.Direction = ParameterDirection.I
parameter.OracleDbTypeEx = dbT
parameter.Value = dataRow[colName];
cmd.Parameters.Add(parameter);
conn.Open();
int result = cmd.ExecuteNonQuery();
此时，每一个 OracleParameter 的 Value 值都赋予单个字段的 一个具体值，这种也是最为传统的插入数据的方法。
Oracle V6 中 OCI 编程接口加入了数组接口特性。
当采用 ArrayBind 时，OraleParameter 的 Value 值则是赋予单个字段的 一个数组，即多条数据的该字段组合成的一个数组。此时 Oracle 仅需要执行一次 SQL 语句，即可在内存中批量解析并导入数据，减少程序与数据库之间来回的操作，其优点就是数据导入的总体时间明显减少，尤其是进程占用CPU的时间。
如果数据源是 DataTable 类型，首先把 DataTable 数据源，转换成 object[][] 类型，然后绑定 OracleParameter 的 Value 值为对应字段的一个 Object[] 数组即可；参考代码如下：
1 /// &summary&
2 /// 批量插入大数据量
3 /// &/summary&
4 /// &param name='columnData'&列名-列数据字典&/param&
5 /// &param name='dataCount'&数据量&/param&
6 /// &returns&插入数据量&/returns&
7 public int InsertBigData(Dictionary&string, object& columnData, int dataCount)
int result = 0;
if (columnData == null || columnData.Count & 1)
string[] colHeaders = columnData.Keys.ToArray();
StringBuilder sbCmdText = new StringBuilder();
if (columnData.Count & 0)
// 拼接INSERT的SQL语句
sbCmdText.AppendFormat('INSERT INTO {0}(', m_TableName);
sbCmdText.Append(string.Join(',', colHeaders));
sbCmdText.Append(') VALUES (');
sbCmdText.Append(m_ParameterPrefix + string.Join(',' + m_ParameterPrefix, colHeaders));
sbCmdText.Append(')');
OracleConnection connection =
connection = new OracleConnection(GetConnectionString());
using (OracleCommand command = connection.CreateCommand())
command.ArrayBindCount = dataC
command.BindByName =
mandType = CommandType.T
mandText = sbCmdText.ToString();
mandTimeout = 1800;
OracleDbType dbType = OracleDbType.O
foreach (string colName in colHeaders)
dbType = GetOracleDbType(columnData[colName]);
parameter = new OracleParameter(colName, dbType);
parameter.Direction = ParameterDirection.I
parameter.OracleDbTypeEx = dbT
parameter.Value = columnData[colName];
command.Parameters.Add(parameter);
connection.Open();
OracleTransaction trans = connection.BeginTransaction();
command.Transaction =
result = command.ExecuteNonQuery();
catch (Exception ex)
trans.Rollback();
if (connection != null)
connection.Close();
connection.Dispose();
GC.Collect();
GC.WaitForFullGCComplete();
1 /// &summary&
2 /// 根据数据类型获取OracleDbType
3 /// &/summary&
4 /// &param name='value'&数据&/param&
5 /// &returns&数据的Oracle类型&/returns&
6 private static OracleDbType GetOracleDbType(object value)
OracleDbType dataType = OracleDbType.O
if (value is string[])
dataType = OracleDbType.Varchar2;
else if (value is DateTime[])
dataType = OracleDbType.TimeS
else if (value is int[] || value is short[])
dataType = OracleDbType.Int32;
else if (value is long[])
dataType = OracleDbType.Int64;
else if (value is decimal[] || value is double[] || value is float[])
dataType = OracleDbType.D
else if (value is Guid[])
dataType = OracleDbType.Varchar2;
else if (value is bool[] || value is Boolean[])
dataType = OracleDbType.B
else if (value is byte[])
dataType = OracleDbType.B
else if (value is char[])
dataType = OracleDbType.C
return dataT
GetOracleDbType说明：如果采用分次（每次1万数据）执行 InsertBigData 方法，速度反而比一次性执行 InsertBigData 方法慢，详见下面测试结果；
测试结果：
无索引，数据类型：4列NVARCHAR2，2列NUMBER
30+万（7.36M）：一次性导入用时 15：623，每次10000导入用时&
60+万（14.6M）：一次性导入用时 28：207，每次10000导入用时 1:2:300
100+万（24.9M）：一次性导入报如下异常
此时实际上从资源监视器上可以得知仍有可用内存，但是仍旧报 OutOfMemoryException，所以猜测应该是一个 bug；
如果每次10000导入用时 2：9：252
如果每次50000导入用时 58：101
附加 InsertBigData 方法使用示例：
1 // 每10000数据导入一次
2 Dictionary&string, object& columnsData = new Dictionary&string, object&();
3 int dataCount = m_SourceDataTable.Rows.C
4 int times = dataCount / 10000 + (dataCount % 10000 == 0 ? 0 : 1);
5 for (int i = 0; i & i++)
int startIndex = i * 10000;
int endIndex = (i + 1) * 10000;
endIndex = endIndex & dataCount ? dataCount : endI
int currDataCount = endIndex - startI
columnsData.Add('COL1', new string[currDataCount]);
columnsData.Add('COL2', new string[currDataCount]);
columnsData.Add('COL3', new decimal[currDataCount]);
columnsData.Add('COL4', new string[currDataCount]);
columnsData.Add('COL5', new decimal[currDataCount]);
columnsData.Add('COL6', new string[currDataCount]);
for (int rowIndex = startI rowIndex & endI rowIndex++)
int dicRowIndex = rowIndex - startI// 列数据行索引
foreach (string colName in columnsData.Keys)
object cell = m_SourceDataTable.Rows[rowIndex][colName];
string cellStr = (cell + '').TrimEnd(new char[] { '', ' ' });
if (colName == 'COL3' || colName == 'COL5')
decimal value = 0;
decimal.TryParse(cellStr, out value);
((decimal[])columnsData[colName])[dicRowIndex] =
((string[])columnsData[colName])[dicRowIndex] = cellS
m_DAL.InsertBigData(columnsData, currDataCount);
columnsData.Clear();
GC.Collect();
GC.WaitForFullGCComplete();
方式二：OracleBulkCopy
1. OracleBulkCopy 采用 direct path 方式导入；
2. 不支持 transaction，无法 Rollback；
3. 如果该表存在触发器时，无法使用 OracleBulkCopy（报异常信息 Oracle Error: ORA-26086），除非先禁用该表的所有触发器；
4. 过程中会自动启用 NOT NULL、UNIQUE 和 PRIMARY KEY 三种约束，其中 NOT NULL 约束在列数组绑定时验证，任何违反 NOT NULL 约束条件的行数据都会舍弃；UNIQUE 约束是在导入完成后重建索引时验证，但是在 bulk copy 时，允许违反索引约束，并在完成后将索引设置成禁用（UNUSABLE）状态；而且，如果索引一开始状态就是禁用（UNUSABLE）状态时，OracleBulkCopy 是会报错的。
参考代码如下：
1 /// &summary&
2 /// 批量插入数据
3 /// 该方法需要禁用该表所有触发器，并且插入的数据如果为空，是不会采用默认值
4 /// &/summary&
5 /// &param name='table'&数据表&/param&
6 /// &param name='targetTableName'&数据库目标表名&/param&
7 /// &returns&&/returns&
8 public bool InsertBulkData(DataTable table, string targetTableName)
bool result =
string connStr = GetConnectionString();
using (OracleConnection connection = new OracleConnection(connStr))
using (OracleBulkCopy bulkCopy = new OracleBulkCopy(connStr, OracleBulkCopyOptions.Default))
if (table != null && table.Rows.Count & 0)
bulkCopy.DestinationTableName = targetTableN
for (int i = 0; i & table.Columns.C i++)
string col = table.Columns[i].ColumnN
bulkCopy.ColumnMappings.Add(col, col);
connection.Open();
bulkCopy.WriteToServer(table);
bulkCopy.Close();
bulkCopy.Dispose();
测试结果：
数据类型：4列NVARCHAR2，2列NUMBER
30+万（7.36M）：用时 14：590
60+万（14.6M）：用时 28：28
.9M）：用时 52：971
附加，禁用表的所有外键SQL：
ALTER TABLE table_name DISABLE ALL TRIGGERS
1、在30+万和60+万数据时，ArrayBind一次性导入和OracleBulkCopy时间相差不是很大，但是ArrayBind方式一般都需要转换数据形式，占用了一些时间，而 OracleBulkCopy 则只需要简单处理一下 DataTable 数据源即可导入；
2、当数据量达到100+万时，ArrayBind很容易出现内存不足异常，此时只能采用分批次执行导入，根据测试结果可知，次数越少，速度越快；而采用 OracleBulkCopy 方式则很少出现内存不足现象，由此可见 OracleBulkCopy 占用内存比 ArrayBind 方式少；
参考资料：
1、ArrayBind /technetwork/issue-archive/2009/09-sep/o59odpnet-085168.html
2、ArrayBind /dotnet/csharp/csharp_01.html
3、Oracle数据导入方法 http://dbanotes.net/Oracle/All_About_Oracle_Data_Loading.htm
4、介绍OracleBulkCopy类 /cd/E11882_01/win.112/e23174/OracleBulkCopyClass.htm#ODPNT7446
5、/questions/7287/what-specifically-does-oraclebulkcopy-do-and-how-can-i-optimize-its-performance
延伸阅读：
一般问题（TO_DATE和TO_CHAR两种格式互换）比如：只要...
本教程为 李华明 编著的iOS-Cocos2d游戏开发系列教程：教程涵盖关于i......
专题主要学习DirectX的初级编程入门学习，对Directx11的入门及初学者有......
&面向对象的JavaScript&这一说法多少有些冗余，因为JavaScript 语言本......
Windows7系统专题 无论是升级操作系统、资料备份、加强资料的安全及管......Oracle Insert和bulk Insert测速对比实例
阅读：2658次&&&时间： 21:53:47&&
经过长时间接触Oracle Insert和bulk Insert，我对比了一下他们的执行效率。在这里和大家分享一下，希望你看完本文后有不少收获。
测试java的insert 同使用9i以后的bulk Insert 的速度，结果显示通过bulk Insert 速度相当的快.
100000条记录
insert ,---------------93秒
bulk Insert -------------0.441秒
oracle 10.2.0.3 Windows 2000Server
代码:SQL&desc&a &Name&Type&Nullable&Default&Comments& &----&------------&--------&-------&--------& &ID&INTEGER&Y& &NAME&VARCHAR2(20)&Y& &
bulk Insert 使用的类型及过程create&or&replace&type&i_table&is&table&of&number(10); &create&or&replace&type&v_table&is&table&of&varchar2(10); &create&or&replace&procedure&pro_forall_insert(v_1&i_table,v_2&v_table) &as &c& &begin &forall&i&in&1..&v_1.count& &insert&into&a&values(v_1(i),v_2(i)); & &&
测试的java代码:import&java.io.*; &import&java.sql.*; &import&java.util.*; &import&javax.naming.C &import&javax.naming.InitialC &import&javax.naming.*; &import&oracle.jdbc.OracleT &import&oracle.sql.*; &import&oracle.sql.ARRAY; &import&oracle.sql.ArrayD &import&oracle.sql.STRUCT; &import&oracle.sql.StructD &import&java.sql.C &import&java.sql.DriverM &import&oracle.jdbc.OracleCallableS &&public&class&testOracle&{ &public&testOracle()&{ &Connection&oraCon&=&null; &PreparedStatement&ps&=&null; &Statement&st&=&null; &ResultSet&rs&=&null; &try&{ &try&{ &Class.forName("oracle.jdbc.driver.OracleDriver"); &}&catch&(ClassNotFoundException&ex)&{} &oraCon&=&DriverManager.getConnection("jdbc:oracle:thin:@192.168.15.234:1521:ora10g",&"imcs","imcs"); &oraCon.setAutoCommit(false); &}&catch&(SQLException&ex)&{ &ex.printStackTrace(); &} &CallableStatement&cstmt&=&null; &oracle.sql.ArrayDescriptor&a&=&null; &oracle.sql.ArrayDescriptor&b&=&null; &if&(1&==&1&) &{ &Object[]&s1&=&new&Object[100000]; &Object[]&s2&=&new&Object[100000]; &for&(int&i&=&0;&i&&;&i++)&{ &s1[i]&=&new&Integer(1); &s2[i]&=&new&String("aaa").concat(String.valueOf(i)); &} &try&{ &a&=&oracle.sql.ArrayDescriptor.createDescriptor("I_TABLE",&oraCon); &b&=&oracle.sql.ArrayDescriptor.createDescriptor("V_TABLE",&oraCon); &ARRAY&a_test&=&new&ARRAY(a,&oraCon,&s1); &ARRAY&b_test&=&new&ARRAY(b,&oraCon,&s2); &cstmt&=&oraCon.prepareCall("{&call&pro_forall_insert(?,?)&}"); &cstmt.setObject(1,&a_test); &cstmt.setObject(2,&b_test); &long&aaaa&=&System.currentTimeMillis(); &System.out.println(System.currentTimeMillis()); &cstmt.execute(); &<mit(); &System.out.println(System.currentTimeMillis()-aaaa); &&catch&(Exception&e)&{ &e.printStackTrace(); &} &} &else &{ &try &{ &PreparedStatement&oraPs&=&null; &String&oraInsertSql&= &"insert&into&a&values(?,?)"; &oraPs&=&oraCon.prepareStatement(oraInsertSql); &long&aaaa&=&System.currentTimeMillis(); &System.out.println(System.currentTimeMillis()); &for&(int&i&=&0;&i&&;&i++) &{ &oraPs.setInt(1,i); &oraPs.setString(2,&new&String("aaa").concat(String.valueOf(i))); &oraPs.executeUpdate();& &} &<mit(); &System.out.println(System.currentTimeMillis()-aaaa); &} &catch&(SQLException&ex) &{ &System.out.print("dddddd"); &System.out.print(ex.getMessage()); &} &} &try&{ &jbInit(); &}&catch&(Exception&ex)&{ &ex.printStackTrace(); &} &} &public&static&void&main(String&args[])&{ &testOracle&a&=&new&testOracle(); &} &private&void&jbInit()&throws&Exception&{ &} &}; &&
[商业源码]&
[商业源码]&
[商业源码]&
[商业源码]&
[商业源码]&
[商业源码]&
[商业源码]&
[商业源码]&
[商业源码]&
[商业源码]&
Copyright &
All Rights ReservedBulk Inserts with Oracle
Bulk Inserts with Oracle
The Old Fashioned Way
A quick glance at the following Code should make one point very clear: This is
unfortunately, it takes a lot of time to run - it is
"old-fashioned" code, so let's improve it using collections and bulk processing.
CREATE OR REPLACE PROCEDURE test_proc IS
& FOR x IN (SELECT * FROM all_objects)
&&& INSERT INTO t1
&&& (owner, object_name, subobject_name, object_id,
&&&& data_object_id, object_type, created, last_ddl_time,
&&&& timestamp, status, temporary, generated, secondary)
&&& VALUES
&&& (x.owner, x.object_name, x.subobject_name, x.object_id,
&&& x.data_object_id, x.object_type, x.created,
&&& x.last_ddl_time, x.timestamp, x.status, x.temporary,
&&& x.generated, x.secondary);
& END LOOP;
CREATE TABLE t1 AS SELECT * FROM all_objects WHERE 1 = 2;
SQL& exec test_
PL/SQL procedure successfully completed.
Elapsed: 00:00:12.84
SQL& exec test_
PL/SQL procedure successfully completed.
Elapsed: 00:00:15.03
SQL& exec test_
PL/SQL procedure successfully completed.
Elapsed: 00:00:12.54
Very slow - do not use it in that way!
USING Bulk Collect
Converting to collections and bulk processing can increase the volume and complexity
of your code. If you need a serious boost in performance, however, that
increase is well-justified.
Collections, an evolution of PL/SQL tables that allows us to manipulate many
variables at once, as a unit. Collections, coupled with two new features
introduced with Oracle 8i, BULK_COLLECT and FORALL, can dramatically increase the
performance of data manipulation code within PL/SQL.
CREATE OR REPLACE PROCEDURE test_proc (p_array_size IN PLS_INTEGER DEFAULT 100)
TYPE ARRAY IS TABLE OF all_objects%ROWTYPE;
l_data ARRAY;
CURSOR c IS SELECT * FROM all_
&&& FETCH c BULK COLLECT INTO l_data LIMIT
&&& FORALL i IN 1..l_data.COUNT
&&& INSERT INTO t1 VALUES l_data(i);
&&& EXIT WHEN c%NOTFOUND;
&&& END LOOP;
SQL& exec test_
PL/SQL procedure successfully completed.
Elapsed: 00:00:03.34
SQL& exec test_
PL/SQL procedure successfully completed.
Elapsed: 00:00:03.20
SQL& exec test_
PL/SQL procedure successfully completed.
Elapsed: 00:00:03.90
Eliminate CURSOR LOOP at all
You may eliminate the CURSOR Loop at all, the resulting Procedure is compacter and
the performance is more or less the same.
CREATE OR REPLACE PROCEDURE test_proc
TYPE TObjectTable IS TABLE OF ALL_OBJECTS%ROWTYPE;
ObjectTable$ TObjectT
&& SELECT * BULK COLLECT INTO ObjectTable$
&&&& FROM ALL_OBJECTS;
&&&& FORALL x in
ObjectTable$.First..ObjectTable$.Last
&&&& INSERT INTO t1 VALUES ObjectTable$(x) ;
SQL& exec test_
PL/SQL procedure successfully completed.
Elapsed: 00:00:03.51
SQL& exec test_
PL/SQL procedure successfully completed.
Elapsed: 00:00:03.35
SQL& exec test_
PL/SQL procedure successfully completed.
Elapsed: 00:00:04.46SSIS导入Oracle中的性能问题
来源：博客园
在SSIS2005中，如果目标数据库是Oracle，就无法使用SSIS本身提供的bulk insert的功能，在操作大数据量的时候时间过长。如果SSIS和Oracle不在一个内部网里面，效率会非常非常的慢。解决办法也很简单，就是使用oracel sqlloader.先用SSIS导出文本文件，然后调用一个Batch，执行sql loader插入到数据库里面。可以使用Bat_To_Exe_Converter.exe去加密在批处理中的密码。
免责声明：本站部分内容、图片、文字、视频等来自于互联网，仅供大家学习与交流。相关内容如涉嫌侵犯您的知识产权或其他合法权益，请向本站发送有效通知，我们会及时处理。反馈邮箱&&&&。
学生服务号
在线咨询，奖学金返现，名师点评，等你来互动