PL/SQL-IN CLAUSE中的逗号分隔列表 [英] PL/SQL - comma separated list within IN CLAUSE
问题描述
我在获取一部分pl/sql代码时遇到了麻烦.在过程的顶部,我从oracle apex应用程序中获取了有关选中了哪些复选框的数据.因为包含复选框的报告是动态生成的,所以我必须遍历
I am having trouble getting a block of pl/sql code to work. In the top of my procedure I get some data from my oracle apex application on what checkboxes are checked. Because the report that contains the checkboxes is generated dynamically I have to loop through the
APEX_APPLICATION.G_F01
列出并生成一个逗号分隔的字符串,如下所示:
list and generate a comma separated string which looks like this
v_list VARCHAR2(255) := (1,3,5,9,10);
我想稍后再查询该列表,并将v_list放在这样的IN子句上
I want to then query on that list later and place the v_list on an IN clause like so
SELECT * FROM users
WHERE user_id IN (v_list);
这当然会引发错误.我的问题是我可以将v_list转换为什么,以便能够将其插入到pl/sql过程中的查询的IN子句中?
This of course throws an error. My question is what can I convert the v_list to in order to be able to insert it into a IN clause in a query within a pl/sql procedure?
推荐答案
如果users
很小并且user_id
不包含逗号,则可以使用:
If users
is small and user_id
doesn't contain commas, you could use:
SELECT * FROM users WHERE ',' || v_list || ',' LIKE '%,'||user_id||',%'
此查询不是最佳查询,因为它不能使用user_id
上的索引.
This query is not optimal though because it can't use indexes on user_id
.
我建议您使用流水线函数,该函数返回可以直接查询的NUMBER
表.例如:
I advise you to use a pipelined function that returns a table of NUMBER
that you can query directly. For example:
CREATE TYPE tab_number IS TABLE OF NUMBER;
/
CREATE OR REPLACE FUNCTION string_to_table_num(p VARCHAR2)
RETURN tab_number
PIPELINED IS
BEGIN
FOR cc IN (SELECT rtrim(regexp_substr(str, '[^,]*,', 1, level), ',') res
FROM (SELECT p || ',' str FROM dual)
CONNECT BY level <= length(str)
- length(replace(str, ',', ''))) LOOP
PIPE ROW(cc.res);
END LOOP;
END;
/
随后,您将可以构建查询,例如:
You would then be able to build queries such as:
SELECT *
FROM users
WHERE user_id IN (SELECT *
FROM TABLE(string_to_table_num('1,2,3,4,5'));
这篇关于PL/SQL-IN CLAUSE中的逗号分隔列表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!