使用特殊字符作为 Oracle 表中的数据更新查询

Question

我正在尝试更新下表上的列 QUERY1，

ctrl_id  query1
C001     NULL

要在 QUERY1 列中更新的查询 - 选择 "C001" 作为CTRL_ID，"SRC1"作为源，[公司代码]，总和（[总 AV]） 来自 $Src_tbl1 分组 按 [公司代码]

下面是我使用的查询，

update table test1  set QUERY1= ''SELECT ‘C001’ as CTRL_ID, ‘SRC1’ as SOURCE, [Company Code], Sum([Total AV]) FROM $Src_tbl1 GROUP BY [Company Code]'' WHERE CTRL_ID='C001'

此查询失败。请协助。

Answer 1

您必须使用 2 个单引号来存储单引号'：

update 
  test1  
set QUERY1= 'SELECT ''C001'' as CTRL_ID, ''SRC1'' as SOURCE, [Company Code], Sum([Total AV]) FROM $Src_tbl1 GROUP BY [Company Code] WHERE CTRL_ID=''C001''';

这会将值存储为：

SELECT 'C001' as CTRL_ID, 'SRC1' as SOURCE, [Company Code], Sum([Total AV]) FROM
 $Src_tbl1 GROUP BY [Company Code] WHERE CTRL_ID='C001'

正如您在末尾看到的，有 3 个单引号：
前 2 个用于转义 'C001'
的结束单引号和 3d 结束 QUERY1 的全部价值。
如果希望将值存储为：

'SELECT 'C001' as CTRL_ID, 'SRC1' as SOURCE, [Company Code], Sum([Total AV]) FROM $Src_tbl1 GROUP BY [Company Code] WHERE CTRL_ID='C001''

然后你必须这样做：

update 
  test1  
set QUERY1= '''SELECT ''C001'' as CTRL_ID, ''SRC1'' as SOURCE, [Company Code], Sum([Total AV]) FROM $Src_tbl1 GROUP BY [Company Code] WHERE CTRL_ID=''C001''''';

Answer 2

设置 query1='select 'coo1'' 作为ctrl_id， ''src1'' 作为源，

Company_code，总和（total_avg）从表分组company_code'