问题描述:需要把游标中查询到的数据批量插入到表中
创建如下表结构:
-- Create table
CREATE table CUX.CUX_GL_AR_REV_REPT
( group_id NUMBER,
ledger_id NUMBER,
ledger_name VARCHAR2(50),
period_name VARCHAR2(50),
segment1 NUMBER,
company_name VARCHAR2(50),
segment3 NUMBER,
account_name VARCHAR2(50),
channel_name VARCHAR2(50),
currency_code VARCHAR2(10),
gl_fms_amount NUMBER,
gl_ar_amount NUMBER,
ims_ar_amount NUMBER,
ims_gl_amount NUMBER,
amount_difference NUMBER
);
CREATE SYNONYM CUX_GL_AR_REV_REPT FOR cux.CUX_GL_AR_REV_REPT;
-- Create/Recreate indexes
CREATE INDEX CUX.CUX_GL_AR_REV_REPT_U1 ON CUX.CUX_GL_AR_REV_REPT (GROUP_ID);
获取数据的游标如下:
--1.2. GL-AR-IMS金额数据
CURSOR cursor_gl_ar_data IS
SELECT l_group_id,
gl.ledger_id, --帐套ID
gl.name, --帐套
gjh.period_name period_name, --期间
gcc.segment1 segment1, --公司段
com_val.description company_name,
gcc.segment3 segment3, --科目段
acc_val.description account_name,
chan_val.description chanel_name, --渠道值
gjh.currency_code currency_code, --币种
SUM(nvl(gjl.entered_dr, 0) - nvl(gjl.entered_cr, 0)) amount --金额
FROM gl_je_headers gjh, --凭证头
gl_je_lines gjl, --凭证行
gl_import_references gi,
xla_ae_headers xah,
xla_ae_lines xal,
xla.xla_transaction_entities xte,
gl_code_combinations gcc,
fnd_flex_values_vl com_val,
fnd_flex_value_sets com_vs,
fnd_flex_values_vl acc_val,
fnd_flex_value_sets acc_vs,
fnd_flex_values_vl chan_val,
fnd_flex_value_sets chan_vs,
ra_customer_trx_all rcta, --应收发票
ra_batch_sources_all bs,
gl_ledgers gl,
cux_all_org_information_v cao
WHERE gjh.je_header_id = gjl.je_header_id
AND gjh.je_source = 'Receivables'
AND gjl.je_header_id = gi.je_header_id
AND gjl.je_line_num = gi.je_line_num
AND gi.gl_sl_link_id = xal.gl_sl_link_id
AND gi.gl_sl_link_table = xal.gl_sl_link_table
AND xah.ae_header_id = xal.ae_header_id
AND gjl.code_combination_id = gcc.code_combination_id
AND xte.entity_code = 'TRANSACTIONS'
AND xah.entity_id = xte.entity_id
AND xah.application_id = xte.application_id
AND rcta.customer_trx_id = xte.source_id_int_1
AND xah.application_id = 222
AND rcta.attribute3 IS NOT NULL
AND gcc.summary_flag = 'N'
AND gcc.segment1 = com_val.flex_value
AND com_val.flex_value_set_id = com_vs.flex_value_set_id
AND com_vs.flex_value_set_name = 'TENCENT_COMPANY'
AND gcc.segment3 = acc_val.flex_value
AND acc_val.flex_value_set_id = acc_vs.flex_value_set_id
AND acc_vs.flex_value_set_name = 'TENCENT_ACCOUNT'
AND gcc.segment7 = chan_val.flex_value
AND chan_val.flex_value_set_id = chan_vs.flex_value_set_id
AND chan_vs.flex_value_set_name = 'TENCENT_CHANNEL'
AND (gcc.segment3 LIKE '1131%' --应收帐款
OR gcc.segment3 LIKE '2162%' --递延收益
OR gcc.segment3 LIKE '5101%' --主营业务收入
OR gcc.segment3 LIKE '5301%' --营业外收入
OR gcc.segment3 LIKE '5102%' --其他业务收入
OR gcc.segment3 IN ('21210600', '21710110', '21710113', '21710114', '21710115'))
AND gcc.chart_of_accounts_id = gl.chart_of_accounts_id
AND gjh.ledger_id = gl.ledger_id
AND rcta.batch_source_id = bs.batch_source_id
AND rcta.org_id = bs.org_id
AND cao.org_id = rcta.org_id
AND gjh.status = 'P' --已过账
AND (l_org_id IS NULL OR gcc.segment1 = l_org_id) --102 参数
AND gjh.period_name = p_period_name --'2007-07' 参数
AND (p_currency_code IS NULL OR gjh.currency_code = p_currency_code) --参数币种,如CYN
AND (p_account_code IS NULL OR gcc.segment3 = p_account_code) --参数科目
AND sign(instr(bs.name, 'IMS系统')) = '1' --来自IMS
GROUP BY bs.name,
gl.ledger_id,
gl.name,
gjh.period_name,
gcc.segment1,
gcc.segment3,
gcc.segment7,
com_val.description,
acc_val.description,
chan_val.description,
gjh.currency_code;
方法1:定义一个记录类型
l_gl_ar_row_num NUMBER;
--定义一个记录类型,用于临时存放要插入到临时表的数据
TYPE tempt_record_type IS RECORD(
group_id NUMBER,
ledger_id NUMBER,
ledger_name VARCHAR2(50),
period_name VARCHAR2(50),
segment1 NUMBER,
company_name VARCHAR2(50),
segment3 NUMBER,
account_name VARCHAR2(50),
channel_name VARCHAR2(50),
currency_code VARCHAR2(10),
gl_fms_amount NUMBER,
gl_ar_amount NUMBER,
ims_ar_amount NUMBER,
ims_gl_amount NUMBER,
amount_difference NUMBER);
由于记录类型只能存放单条记录,需要利用记录表类型来实现存放多条记录,即 table of record,如下:
--table of record
TYPE gl_ar_tbl_rec IS TABLE OF tempt_record_type INDEX BY BINARY_INTEGER;
gl_ar_rec gl_ar_tbl_rec;
定义完记录表后,我们遍历游标,把数据暂存到上面定义的记录表中,即gl_ar_rec
-- 将GL-AR-IMS金额数据暂存在table of record,并批量插入表 cux_gl_ar_rev_rept
l_gl_ar_row_num := 1;
FOR l_cur_gl_ar IN cursor_gl_ar_data LOOP
gl_ar_rec(l_gl_ar_row_num).group_id := l_group_id;
gl_ar_rec(l_gl_ar_row_num).ledger_id := l_cur_gl_ar.ledger_id;
gl_ar_rec(l_gl_ar_row_num).ledger_name := l_cur_gl_ar.name;
gl_ar_rec(l_gl_ar_row_num).period_name := l_cur_gl_ar.period_name;
gl_ar_rec(l_gl_ar_row_num).segment1 := l_cur_gl_ar.segment1;
gl_ar_rec(l_gl_ar_row_num).company_name := l_cur_gl_ar.company_name;
gl_ar_rec(l_gl_ar_row_num).segment3 := l_cur_gl_ar.segment3;
gl_ar_rec(l_gl_ar_row_num).account_name := l_cur_gl_ar.account_name;
gl_ar_rec(l_gl_ar_row_num).channel_name := l_cur_gl_ar.chanel_name;
gl_ar_rec(l_gl_ar_row_num).currency_code := l_cur_gl_ar.currency_code;
gl_ar_rec(l_gl_ar_row_num).gl_fms_amount := '0.00';
gl_ar_rec(l_gl_ar_row_num).gl_ar_amount := l_cur_gl_ar.amount;
gl_ar_rec(l_gl_ar_row_num).ims_ar_amount := '0.00';
gl_ar_rec(l_gl_ar_row_num).ims_gl_amount := '0.00';
gl_ar_rec(l_gl_ar_row_num).amount_difference := '';
l_gl_ar_row_num := l_gl_ar_row_num + 1;--下一条记录
END LOOP;
最后,我们把记录表gl_ar_rec中暂存的数据批量插入到cux_gl_ar_rev_rept中,代码如下:
--批量插入表cux_gl_ar_rev_rept
FORALL i IN INDICES OF gl_ar_rec
INSERT INTO cux_gl_ar_rev_rept VALUES gl_ar_rec (i);
方法二:
我们直接定义一个表类型的变量,如下:
--TYPE type_gl_ar IS TABLE OF cux_gl_ar_rev_rept%ROWTYPE;
--l_gl_ar_rec type_gl_ar;
这里,区别于方法一的做法在于,我们不再利用记录表暂存,直接插入
OPEN cursor_gl_ar_data;
LOOP
FETCH cursor_gl_ar_data BULK COLLECT
INTO l_gl_ar_rec LIMIT 1000;
--批量插入表 cux_gl_ar_rev_rept
FORALL i IN 1 .. l_gl_ar_rec.count
INSERT INTO cux_gl_ar_rev_rept VALUES l_gl_ar_rec (i);
EXIT WHEN cursor_gl_ar_data%NOTFOUND;
END LOOP;
CLOSE cursor_gl_ar_data;