动态pl/sql的where子句中的Plstring变量

Question

我有这样的代码:

declare
  instr varchar2(20);
  num_no number;
begin 
    select AR_ID, ID
    into instr, num_no
    from quelle_match
    where ID = 1;
      execute immediate 'CREATE or replace VIEW interm2 AS
      SELECT AR_ID, kurs,
        ((TO_CHAR(to_date(di.kursdatum,''dd.mm.yyyy''),''YYYYMMDD''))+    (TO_CHAR(to_date(di.verfall,''dd.mm.yyyy''),''YYYYMMDD''))) AS dumdate
      FROM daten_import di 
      where di.AR_ID = '||instr||'';      
  -- where di.AR_ID = ''GXIndex''';
end ;

我得到错误b/c instr是无效的。当我用实际的字符串GXIndex替换变量时，代码就可以工作了。它也可以用数字作为变量，但不能用字符串。为什么呢?

Answer 1

this:

  execute immediate 'CREATE or replace VIEW interm2 AS
  SELECT AR_ID, kurs,
    ((TO_CHAR(to_date(di.kursdatum,''dd.mm.yyyy''),''YYYYMMDD''))+    (TO_CHAR(to_date(di.verfall,''dd.mm.yyyy''),''YYYYMMDD''))) AS dumdate
  FROM daten_import di 
  where di.AR_ID = '||instr||''; 

应:

  execute immediate 'CREATE or replace VIEW interm2 AS
  SELECT AR_ID, kurs,
    ((TO_CHAR(to_date(di.kursdatum,''dd.mm.yyyy''),''YYYYMMDD''))+    (TO_CHAR(to_date(di.verfall,''dd.mm.yyyy''),''YYYYMMDD''))) AS dumdate
  FROM daten_import di 
  where di.AR_ID = '||instr; 

如果instr是整数，我认为它是。

Answer 2

您连接instr的方式有问题。为了清楚起见，让我简化你的问题:

EXECUTE IMMEDIATE
  'CREATE OR REPLACE VIEW interm2 AS
   SELECT ar_id FROM daten_import di
   WHERE di.ar_id = ' || instr;

instr为字符串。你需要用引号括起来

EXECUTE IMMEDIATE
  'CREATE OR REPLACE VIEW interm2 AS
   SELECT ar_id FROM daten_import di
   WHERE di.ar_id = ''' || instr || '''';
--                  ^^               ^^
--                  ||               ||
--                  -- escaped quotes--