oracle多行记录与批量插入

来源：互联网发布：南昌市网络教育平台编辑：程序博客网时间：2024/06/05 02:28

问题描述：需要把游标中查询到的数据批量插入到表中

创建如下表结构：

-- Create tableCREATE  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;

0 0