ORACLE中 大量数据插入表 SQL


declare
g_commit_count number;

cursor cu1 is
      select

gl_flexfields_pkg.get_description_sql(gcc.chart_of_accounts_id,
                                                   1, --1
                                                   gcc.segment1) company_name,
             p_com_code company_code,
             gl.name,
             gl.description sob_desc,
             ps.period_year,
             ps.period_name,
             to_char(jeh.default_effective_date, 'yyyy-mm-dd') creation_date,
             to_char(jeh.posted_date, 'yyyy-mm-dd') posted_date,
             jec.user_je_category_name,
             jeh.doc_sequence_value,
             jel.je_line_num,
             decode(nvl(jel.accounted_dr, 0), 0, null, gcc.segment2) dr_acc,
             decode(nvl(jel.accounted_cr, 0), 0, null, gcc.segment2) cr_acc,
             jel.description,
             jel.accounted_dr,
             jel.accounted_cr,
             jeh.attribute3,
             null fa_num,
             null po_num,
             jeh.currency_code
        from gl_je_headers        jeh,
             gl_je_lines          jel,
             gl_code_combinations gcc,
             gl_je_sources_vl     src,
             gl_je_categories     jec,
             gl_period_statuses   ps,
             gl_ledgers           gl
       WHERE jeh.je_header_id = jel.je_header_id
         AND jeh.je_source = src.je_source_name
         AND jeh.je_category = jec.JE_CATEGORY_name
         AND jel.code_combination_id = gcc.code_combination_id
         AND jeh.ledger_id = gl.ledger_id
         AND src.language = userenv('LANG')
         AND jec.language = userenv('LANG')
         AND jeh.actual_flag = 'A'
         AND jeh.status = 'P'
         AND ps.application_id = 101
         AND jel.ledger_id = ps.ledger_id
         AND ps.period_name = jeh.period_name
         AND (nvl(jel.accounted_dr, 0) <> 0 OR
             nvl(jel.accounted_cr, 0) <> 0)
         AND jeh.ledger_id = p_sob_id
          --and jeh.je_header_id=13704942--
         AND ps.period_name between nvl(p_period_f, ps.period_name) and
             nvl(p_period_t, to_char(sysdate, 'YYYY-MM'))
         --AND (jeh.default_effective_date >=to_date(P_START_DATE,'yyyy-mm-dd hh24:mi:ss') or P_START_DATE is null)
        -- AND (jeh.default_effective_date <=to_date(P_END_DATE,'yyyy-mm-dd hh24:mi:ss') or P_END_DATE is null)
       ;
       --order by ps.period_name, jeh.je_header_id, jel.je_line_num;
     
TYPE type_je_detail IS TABLE OF cux_glje_detail_tmp%ROWTYPE INDEX BY BINARY_INTEGER;

    t_je_detail type_je_detail ;
  BEGIN
   g_commit_count := 5000; 
   
    OPEN cu1;
    LOOP
      FETCH cu1 BULK COLLECT
        INTO t_je_detail  LIMIT g_commit_count;
      FORALL i IN 1 .. t_je_detail .count
        INSERT /* +APPEND */ 
        INTO cux_glje_detail_tmp
        VALUES
           (
        t_je_detail (i).company_name
        t_je_detail (i).company_code,
--中间省略
        t_je_detail (i).币currency_code);
      COMMIT;
      EXIT WHEN cu1%NOTFOUND;
    END LOOP;
    CLOSE cu1;

 


posted @ 2017-05-19 14:23  我不卖豆腐  阅读(2163)  评论(0编辑  收藏  举报