如何在oracle中为字符串分配多个值

I want to assign mulitple values into a variable and use that variable in where-clause.

我想将mulitple值赋值给一个变量，并在where子句中使用该变量。

For Ex:

为例:

declare

    v_filename varchar2(300) := ''('filename1','filename2')'';
    cnt number;

begin

    select count(*) into cnt from table_name
    where filename in v_filename;

end;

Please advise.

请建议。

Thanks, Deepak

谢谢,迪帕克

4 个解决方案

#1

you have basically 2 options. 1. Use dynamic query 2. Use collections

你基本上有两个选择。1。使用动态查询2。使用集合

With dynamic query you dynamically construct the query at runtime and than run it:
使用动态查询，您可以在运行时动态构造查询并运行它:


DECLARE
  v_filename VARCHAR2(300) := '(''ALL_VIEWS'', ''ALL_TABLES'')'; 
--double quotes inside a string
  cnt NUMBER;

BEGIN
execute immediate 'SELECT COUNT(*) INTO :cnt FROM all_views
   WHERE view_name IN ' || v_filename
   into cnt;
  dbms_output.put_line('counted: ' || cnt);
END;
/

Advantages: easy to write, especially for short queries, fast Disadvanteges: possible SQL INJECTION in cases when you wrongly paste user input in the query string, hard parse everytime you change the filename list

优点:易于编写，特别是对于简短的查询，快速的Disadvanteges:如果在查询字符串中错误地粘贴用户输入，每次您更改文件名列表时，都可能会出现SQL注入。

Use collections. You create a collection type, then fill it and use it as a pseudo table.
使用集合。创建一个集合类型，然后填充它并将其用作伪表。


create type tt_strings as table of varchar2(30);

declare 
  cnt NUMBER;
  v_condition tt_strings;

begin
  v_condition := tt_strings();
  v_condition.extend;
  v_condition(1) := 'ALL_VIEWS';
  v_condition.extend;
  v_condition(2) := 'ALL_TABLES';

  SELECT COUNT(*)
    INTO cnt
    FROM all_views o, TABLE(v_condition) x
   WHERE x.column_value = o.VIEW_NAME;
  dbms_output.put_line('counted: ' || cnt);
end;
/

Advantages: Safe, maintainable for larger collections with more than just 2 elements Disadvatages: You need to create a type, Harder to code (at first), a bit slower (which in 99% cases doesn't matter)

优点:安全、可维护的大型集合，只包含两个以上的元素:您需要创建一个类型，难于编码(开始时)，速度稍慢一点(在99%的情况下这并不重要)

#2

I would recommend reading Dynamic IN-Lists

我建议阅读动态列表

EXECUTE IMMEDIATE is also a possibility, but I wouldn't recommend it if you don't check the IN string.

执行立即也是一种可能性，但是如果不检查IN字符串，我不建议这样做。

Just wrote on the quick(used the first method presented in "Dynamic IN-Lists"):

刚写了关于quick(使用了“Dynamic in - list”中出现的第一个方法):

CREATE OR REPLACE TYPE t_varchar_tab AS TABLE OF VARCHAR2 (4000);
CREATE OR REPLACE FUNCTION in_list (p_in_list  IN  VARCHAR2) RETURN t_varchar_tab
AS
  l_tab   t_varchar_tab := t_varchar_tab();
  l_text  VARCHAR2(32767) := p_in_list || ',';
  l_idx   NUMBER;
BEGIN
  LOOP
    l_idx := INSTR(l_text, ',');
    EXIT WHEN NVL(l_idx, 0) = 0;
    l_tab.extend;
    l_tab(l_tab.last) := TRIM(SUBSTR(l_text, 1, l_idx - 1));
    l_text := SUBSTR(l_text, l_idx + 1);
  END LOOP;

  RETURN l_tab;
END;

SELECT * FROM TABLE(in_list('filename1,filename2'));
SELECT COUNT(*) INTO cnt FROM table_name WHERE filename IN (SELECT * FROM TABLE(in_list(v_filename)));

#3

You must put the whole query into a string and then execute that string with EXECUTE IMMEDIATE (see the docs).

您必须将整个查询放入一个字符串中，然后使用execute IMMEDIATE执行该字符串(请参阅文档)。

#4

Alternatively to the "EXECUTE IMMEDIATE", you could use like, e.g.

你也可以用like来代替EXECUTE IMMEDIATE，例如。

declare

    v_filename varchar2(300) := '(''filename1'',''filename2'')';
    cnt number;

begin

    select count(*) into cnt from table_name
    where v_filename like '%'''||filename||'''%';

end;

Note that the parenteses and the comma in v_filename are just for human readability. This is by no means a perfect solution, as it presumes that the filenames in your table do not contain some special characters; it's also bad in terms of performance if there is an index on the filename column (which is not used this way).

注意，parenteses和v_filename中的逗号仅用于人类可读性。这绝不是一个完美的解决方案，因为它假定您表中的文件名不包含某些特殊字符;如果filename列上有索引(这种方式不使用)，那么性能也很差。

#1