SQL - 从一个表插入到另一个表但操作数据

I am trying to copy data from one table to another by using the following script:

我试图使用以下脚本将数据从一个表复制到另一个表:

insert into test_report
( company_id
, report_id
, brch_code
, definition
, description
, editable_flag
, executable_flag
, name
, report_type ) 
values
( 2420
, 'RP00002004'
, '0001'
, (select definition from test_template_report where template_id='RP00001242')
, (select description from test_template_report where template_id='RP00001242')
, (select editable_flag from test_template_report where template_id='RP00001242')
, (select executable_flag from test_template_report where template_id='RP00001242')
, (select name from test_template_report where template_id='RP00001242')
, '01' );

This is working fine, but the definition field contains XML which would need to be modified slightly.

这工作正常,但定义字段包含需要稍微修改的XML。

The following is part of the definition data:

以下是定义数据的一部分:

<listdef page='25'><reportId>RP00000390</reportId><name>Fund Transfer</name><description>Fund Transfer</description>

The <reportId>RP00000390</reportId> part would need to be changed to be RP00002004 as per the insert into script.

根据插入脚本, RP00000390 部分需要更改为RP00002004。

Like the following:

如下:

<listdef page='25'><reportId>RP00002004</reportId><name>Fund Transfer</name><description>Fund Transfer</description>

Is this possible?

这可能吗?

2 个解决方案

#1

You can use XMLQuery with a modify ... replace value of node:

您可以将XMLQuery与modify ...替换为node的值:

insert into test_report (company_id,report_id,brch_code,definition,description,
  editable_flag,executable_flag,name,report_type)
select 2420, 'RP00002004', '0001',
  XMLQuery('copy $i := $d modify
      (for $j in $i//reportId return replace value of node $j with $r)
      return $i'
    passing definition as "d", 'RP00002004' as "r"
    returning content),
  description, editable_flag, executable_flag, name, '01'
from test_template_report where template_id='RP00001242';

You don't need all the individual selects from the template table, a single insert-select will do.

您不需要模板表中的所有单独选择,单个insert-select就可以。

The XML manipulation assumes definition is an XMLType; if it isn't you can convert it to one in the passing clause, i.e. passing XMLType(definition) as "d". The value of the reportId node (or nodes) is replaced with the string passed as "r".

XML操作假设定义是XMLType;如果不是,你可以将它转换为传递子句中的一个,即将XMLType(定义)传递为“d”。 reportId节点(或节点)的值将替换为作为“r”传递的字符串。

As a quick static demo of that replacement happening, with the XML supplied in-line as a string literal:

作为替换发生的快速静态演示,使用内联提供的XML作为字符串文字:

select
  XMLQuery('copy $i := $d modify
      (for $j in $i//reportId return replace value of node $j with $r)
      return $i'
    passing XMLType(q'[<listdef page='25'><reportId>RP00000390</reportId><name>Fund Transfer</name><description>Fund Transfer</description></listdef>]') as "d",
      'RP00002004' as "r"
    returning content)
  as modified_definition
from dual;

MODIFIED_DEFINITION                                                                                                           
------------------------------------------------------------------------------------------------------------------------------
<listdef page="25"><reportId>RP00002004</reportId><name>Fund Transfer</name><description>Fund Transfer</description></listdef>

#2

The replace function replaces one text string with another, so you could change

replace函数将一个文本字符串替换为另一个文本字符串,因此您可以更改

definition

replace(definition, '<reportId>RP00000390</reportId>', '<reportId>RP00002004</reportId>')

You can also get all the columns you need from test_template_report in one go:

您还可以一次性从test_template_report获取所需的所有列:

insert into test_report
     ( company_id
     , report_id
     , brch_code
     , definition
     , description
     , editable_flag
     , executable_flag
     , name
     , report_type )
select 2420
     , 'RP00002004'
     , '0001'
     , replace(tr.definition, '<reportId>RP00000390</reportId>', '<reportId>RP00002004</reportId>')
     , tr.description
     , tr.editable_flag
     , tr.executable_flag
     , tr.name
     , '01'
from   test_template_report tr
where  tr.template_id = 'RP00001242';

If you wanted to replace any value of reportIf and not just 'RP00000390', you could use regexp_replace:

如果要替换reportIf的任何值而不仅仅是'RP00000390',则可以使用regexp_replace:

insert into test_report
     ( company_id
     , report_id
     , brch_code
     , definition
     , description
     , editable_flag
     , executable_flag
     , name
     , report_type )
select 2420
     , 'RP00002004'
     , '0001'
     , regexp_replace(definition,'<reportId>[^<]+</reportId>','<reportId>RP00002004</reportId>')
     , tr.description
     , tr.editable_flag
     , tr.executable_flag
     , tr.name
     , '01'
from   test_template_report tr
where  tr.template_id = 'RP00001242';

#1