时间戳

首页 > 解决方案 > 如何在 oracle 的插入查询中使用转义符号？

oracle - 如何在 oracle 的插入查询中使用转义符号？

问题描述

请考虑这个场景

declare 
  lv_sql varchar(4000);
  lv_sql1 varchar(4000);
begin
  lv_sql1 := 'select case_id from tgt where excluded =''N''';
  lv_sql := 'insert into sample values ('||'''select  count(1) from ( '||lv_sql1||')'')';
  execute immediate (lv_sql);
end;

现在，如果我执行这个块，那么它将在 oracle 中引发 Missing COMMA 的错误。

标签： oracleplsqlsql-insert

解决方案

这里有两个问题；第一个是关于VALUES关键字的：

SQL> insert into aTable values (select count(*) from dual);
insert into aTable values (select count(*) from dual)
                           *
ERROR at line 1:
ORA-00936: missing expression


SQL> insert into aTable (select count(*) from dual);

1 row created.

第二个是你缺少括号：

SQL> select count(*) from select 1 from dual;
select count(*) from select 1 from dual
                     *
ERROR at line 1:
ORA-00903: invalid table name


SQL> select count(*) from (select 1 from dual);

  COUNT(*)
----------
         1

例如：

SQL> select * from aTable;

no rows selected

SQL> declare
  2      lv_sql1 varchar2(100);
  3      lv_sql  varchar2(100);
  4  begin
  5      lv_sql1 := 'select 1 from dual';
  6      lv_sql  :='insert into aTable (select count(1) from (' ||lv_sql1 || ') )';
  7      --
  8      execute immediate lv_sql;
  9  end;
 10  /

PL/SQL procedure successfully completed.

SQL> select * from aTable;

         N
----------
         1

SQL>

像你这样的桌子：

SQL> create table tgt(case_id, excluded) as (select 'xxx', 'N' from dual);

Table created.

SQL> create table sample(n number);

Table created.

SQL> declare
  2      lv_sql varchar(4000);
  3      lv_sql1 varchar(4000);
  4  begin
  5      lv_sql1 := 'select case_id from tgt where excluded =''N''';
  6      lv_sql := 'insert into sample (select count(1) from ( '||lv_sql1||'))';
  7      execute immediate (lv_sql);
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL> select * from sample;

         N
----------
         1

推荐阅读