DEFINE vs DECLARE -转义引号

Question

我已经定义了一个变量

define myStrings = "'abc','def'"

，我稍后需要在过程块中使用它并将其转换为varchars

的表。

declare
    type varcharListType is table of varchar(200);
    myList varcharListType;
begin
    myList := varcharListType(&myStrings);
    .
    .
    .
end;
/

我试图在过程块

内的创建查询中使用IN子句中的变量或表

execute immediate 'create table tmp_foo '
               || 'as select * from bar '
               || 'where bar_val in (&myStrings) ';

我也试过使用the REPLACE函数

myNewStrings := replace(&myStrings, '''' , '''''');

，但我得到一个异常相关的abc和def没有被定义。

问题:

我得到一个语法异常，因为myString中abc和def周围的引号没有转义。值"'abc','def'"必须是"定义的"，而不是"声明的"，以便稍后替换。

问题:

是否有可能"定义"一个变量在这样一种方式，我可以使用它既作为表类型的值，也在执行立即语句中的字符串?

复制:

创建

create table bar (bar_id number not null, bar_val varchar2(20), 
  constraint bar_pk primary key (bar_id)
  enable 
);

insert into bar (bar_id, bar_val)
values (1, 'abc'),
       (2, 'def'),
       (3, 'ghi');

抽样程序

set verify off;
set serveroutput on; 
define myStrings = "'abc','def'"
declare
    type varcharListType is table of varchar(20);
    myList varcharListType;
begin
    myList := varcharListType(&myStrings);
    execute immediate 'create table tmp_foo '
                   || 'as select * from bar '
                   || 'where bar_val in (&myStrings) ';
    for i in myList.FIRST..myList.LAST loop
        dbms_output.put_line('VALUE: ' || myList(i));
    end loop;
end;
/
set serveroutput off;
set verify on;

Answer 1

下面是我将采用的方法，注意循环中表的使用，这是因为DBMS_UTILITY。COMMA_TO_TABLE过程在表的末尾添加一个空值。

希望对你有帮助

declare
  myStrings varchar2(100) := '''abc'',''def''';
  myList dbms_utility.uncl_array;
  tablen number :=0;
begin
  DBMS_UTILITY.COMMA_TO_TABLE ( replace(myStrings, '''', ''),  tablen,  myList); 
  execute immediate 'create table tmp_foo '
               || 'as select * from bar '
               || 'where bar_val in (' ||myStrings||')';
  for i in myList.FIRST..tablen loop
    dbms_output.put_line('VALUE: ' || myList(i));
  end loop;
end;
/

Answer 2

感谢@ShaunPeterson启发了这个问题的解决方案。虽然它直接解决了问题，但它提供了正确的方法，因此所有+1都应该转到他那里。

他的回答不足之处在于，他"宣布"了myStrings，而不是"定义"了它。

declare
  myStrings varchar2(100) := '''abc'',''def''';

不

define myStrings = "'abc','def'"

问题的关键就在这里。在PL/SQL中，为myStringsVar这样的过程块"声明"的变量不会像"定义"变量那样被替换。根据OP的要求，"myStrings"首先被"定义"，然后转换为在过程块中使用。

因此，最终的解决方案看起来像这样:

define myStrings = "''abc'',''def''"
declare
  myStringsVar varchar2(100) := '&myStrings';
  myList dbms_utility.uncl_array;
  tablen number :=0;
begin
  DBMS_UTILITY.COMMA_TO_TABLE ( replace(myStringsVar, '''', ''),  tablen,  myList); 
  execute immediate 'create table tmp_foo '
               || 'as select * from bar '
               || 'where bar_val in (' || myStringsVar||')';
  for i in myList.FIRST..tablen loop
    dbms_output.put_line('VALUE: ' || myList(i));
  end loop;
end;
/