oracle oracle中如何给一个字符串赋值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1714918/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to assign multiple values to a string in oracle
提问by Deepak
I want to assign mulitple values into a variable and use that variable in where-clause.
我想将多个值分配给一个变量并在 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
谢谢，迪帕克
回答by Michal Pravda
you have basically 2 options. 1. Use dynamic query 2. Use collections
你基本上有 2 个选择。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
优点：易于编写，尤其是短查询，速度快 缺点：可能SQL INJECTION，以防错误地将用户输入粘贴到查询字符串中，每次更改文件名列表时都很难解析
- 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)
优点：安全、可维护，适用于超过 2 个元素的大型集合 缺点：您需要创建一个类型，难以编码（起初），有点慢（在 99% 的情况下无关紧要）
回答by Marius Burz
I would recommend reading Dynamic IN-Lists
我建议阅读动态输入列表
EXECUTE IMMEDIATEis also a possibility, but I wouldn't recommend it if you don't check the IN string.
EXECUTE IMMEDIATE也是一种可能性，但如果您不检查 IN 字符串，我不会推荐它。
Just wrote on the quick(used the first method presented in "Dynamic IN-Lists"):
刚刚写在快速上（使用了“动态输入列表”中介绍的第一种方法）：
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)));
回答by Aaron Digulla
You must put the whole query into a string and then execute that string with EXECUTE IMMEDIATE(see the docs).
您必须将整个查询放入一个字符串中，然后使用EXECUTE IMMEDIATE（请参阅文档）执行该字符串。
回答by Erich Kitzmueller
Alternatively to the "EXECUTE IMMEDIATE", you could use like, e.g.
替代“立即执行”，您可以使用类似，例如
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).
请注意， v_filename 中的括号和逗号仅用于人类可读性。这绝不是一个完美的解决方案，因为它假定表中的文件名不包含某些特殊字符；如果文件名列上有索引（不以这种方式使用），那么它在性能方面也很糟糕。
