Oracle数据的批量插入

前两天接到一个需求——需要编程将SQL Server中的数据插入至Oracle 数据大约有 多万条记录 开始的时候我采取了直接构建SQL插入的方式 结果耗时太长 为了提高性能我上网找了资料 最终采用DataAdapter批量插入至Oracle 提高了性能

代码如下

一 直接构建SQL语句插入

 

VB NET

         sw Start()

         Read Z J from SQL Server

         Dim sqlCmd As New SqlCommand()

         sqlCmd Connection = sqlConnection

         sqlCmd CommandText = SELECT * FROM  Z J

         Dim sqlDr As SqlDataReader

         sqlDr = sqlCmd ExecuteReader()

         Dim cmdInsertZ J As New OracleCommand()

         cmdInsertZ J Connection = oraConnection

         cmdInsertZ J CommandText = BuildSQLStatement(SQLType Insert z j )

         Dim plantLever material oldMaterialNum materialDescription As Object

         While sqlDr Read()

             plantLever = ReadSqlDataReader(sqlDr )

             material = ReadSqlDataReader(sqlDr )

             oldMaterialNum = ReadSqlDataReader(sqlDr )

             materialDescription = ReadSqlDataReader(sqlDr )

              Insert to Oracle table Z J

             cmdInsertZ J Parameters AddWithValue( :plantLever plantLever)

             cmdInsertZ J Parameters AddWithValue( :material material)

             cmdInsertZ J Parameters AddWithValue( :oldMaterialNum oldMaterialNum)

             cmdInsertZ J Parameters AddWithValue( :materialDescription materialDescription)

             cmdInsertZ J ExecuteNonQuery()

         End While

         sw Stop()

         Loger Info( Reading z j form sql sever used sw Elapsed TotalSeconds ToString())

 

二 采用DataAdapter实现批量插入

 

VB NET

         sw Start()

         Read Z J from SQL Server

         Dim sqlCmd As New SqlCommand()

         sqlCmd Connection = sqlConnection

         sqlCmd CommandText = SELECT * FROM  Z J

         Dim sqlDr As SqlDataReader

         sqlDr = sqlCmd ExecuteReader()

         Dim cmdInsertZ J As New OracleCommand()

         cmdInsertZ J Connection = oraConnection

         cmdInsertZ J CommandText = BuildSQLStatement(SQLType Insert z j )

         Dim dtSqlZ J As New DataTable

         dtSqlZ J Columns Add( plantLever )

         dtSqlZ J Columns Add( material )

         dtSqlZ J Columns Add( oldMaterialNum )

         dtSqlZ J Columns Add( materialDescription )

         Dim plantLever material oldMaterialNum materialDescription As Object

         While sqlDr Read()

             plantLever = ReadSqlDataReader(sqlDr )

             material = ReadSqlDataReader(sqlDr )

             oldMaterialNum = ReadSqlDataReader(sqlDr )

             materialDescription = ReadSqlDataReader(sqlDr )

             dtSqlZ J Rows Add(plantLever material oldMaterialNum materialDescription)

         End While

         sw Stop()

         Loger Info( Reading z j form sql sever used sw Elapsed TotalSeconds ToString())

         sw Start()

         Dim oraDa As New OracleDataAdapter()

         oraDa InsertCommand = cmdInsertZ J

         oraDa InsertCommand Parameters Add( :plantLever OracleType Char plantLever )

         oraDa InsertCommand Parameters Add( :material OracleType Char material )

         oraDa InsertCommand Parameters Add( :oldMaterialNum OracleType Char oldMaterialNum )

         oraDa InsertCommand Parameters Add( :materialDescription OracleType Char materialDescription )

         oraDa InsertCommand UpdatedRowSource = UpdateRowSource None

         oraDa UpdateBatchSize =     Adjust the batch size based on testing result

         oraDa Update(dtSqlZ J)

         sw Stop()

lishixinzhi/Article/program/Oracle/201311/18480

一个例子你看看

create or replace procedure p_test is

    v_day                varchar2(20)

    nextday              varchar2(20)

    cur_value            number

    yes_value            number

    thismonth_totalvalue number

    lastmonth_totalvalue number

    i                    number

    tempday              varchar2(20)

    day_count            integer

begin

    execute immediate 'truncate table DM_KPID_IW_AREA'

    v_day := '20070101'

    -- lastmonth_totalvalue := 0

    for k in 0 .. 11 loop

        tempday              := to_char(add_months(to_date(v_day,

                                                           'yyyymmdd'),

                                                   k),

                                        'yyyymmdd')

        yes_value            := 0

        thismonth_totalvalue := 0

    

        -- 这里好像有一个比较方便的函数吧，记不清了

        day_count := to_char(last_day(to_date(tempday, 'yyyymmdd')), 'dd')

    

        for i in 0 .. day_count - 1 loop

            nextday              := to_char(to_date(tempday, 'yyyymmdd') + i,

                                            'yyyymmdd')

            cur_value            := trunc(dbms_random.value(1, 100), 0)

            thismonth_totalvalue := thismonth_totalvalue + cur_value

        

            begin

                select t.thismonth_totalvalue

                  into lastmonth_totalvalue

                  from dm_kpid_iw_area t

                 where t.day_id =

                      --考虑如3月30日之类这样的情况，前一月没有对应的日子的情况，

                      --如果此时上月值为0的话，则应该为：

                      --to_char(add_months(to_date(nextday, 'yyyymmdd'), -1),

                      --'yyyymm') || substr(nextday, 7, 2)                      

                       to_char(add_months(to_date(nextday, 'yyyymmdd'), -1),

                               'yyyymmdd')

            exception

                when no_data_found then

                    lastmonth_totalvalue := 0

            end

        

            insert into dm_kpid_iw_area

                (day_id,

                 cur_value,

                 yes_value,

                 thismonth_totalvalue,

                 lastmonth_totalvalue)

            values

                (nextday,

                 cur_value,

                 yes_value,

                 thismonth_totalvalue,

                 lastmonth_totalvalue)

            yes_value := cur_value

            commit

        end loop

    end loop

end

需要生成的SQL

insert into TMP_UPSTATE_CASEKEY values('TMP0000001', 1, sysdate)

存储过程实现

create or replace procedure proc_casekey_upstate

as

casekey char(14)

begin

for i in 1..10000000 loop

casekey := 'TMP'||lpad(i,7,0)-- TMP0000001

insert into TMP_UPSTATE_CASEKEY values(casekey, 1, sysdate)

end loop

commit

end

begin

proc_casekey_upstate()

end

测试发现生成一千万条数据用了14分钟左右，性能还是可以了，如果先去掉TMP_NUM_STATUS_ID的外键估计更快。

或者：

insert into TMP_UPSTATE_CASEKEY select 'TMP'||LPAD(rownum,7,0),1,sysdate from dual connect by level <= 1000000

---