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;