来自参数的 Oracle“IN 子句”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6629433/
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
Oracle "IN clause" from parameter
提问by Metallicraft
I'm very unfamiliar with Oracle and am just trying to get this to work. I've looked hereand herebut have no idea how to make it all work. Here's what I need. I need a proc that will accept a comma-delimited string as a parameter that I can use in the "IN clause" of the query and return a cursor result. Is ther a way to do this in one, all-inclusive proc? If not, what are the different steps I need to take? Again, I apologize for my lack of knowledge of Oracle, this is just trying to get something to work real quick.
我对 Oracle 非常不熟悉，我只是想让它工作。我已经看过here和here，但不知道如何让它全部工作。这就是我需要的。我需要一个接受逗号分隔字符串作为参数的过程，我可以在查询的“IN 子句”中使用该参数并返回游标结果。有没有办法在一个包罗万象的过程中做到这一点？如果没有，我需要采取哪些不同的步骤？再次，我为我缺乏 Oracle 知识而道歉，这只是试图让某些东西真正快速地工作。
Thanks
谢谢
Here's the proc (p_Scope would be the comma-delimited input):
这是过程（p_Scope 将是逗号分隔的输入）：
create or replace PROCEDURE CU_SELECTION_ID
(
p_Scope IN varchar2,
p_ResultSet OUT SYS_REFCURSOR
)
is
BEGIN
OPEN p_ResultSet FOR
select
b.addr1,
b.addr2,
b.city,
b.country_code,
a.customer_no,
b.des1,
a.entity,
b.main_phone_no,
b.phone_area_code,
b.status,
b.wb_site_url,
b.zip
from
ar_customer a,
ct_addr b
where b.main_loc_flag = 'Y' and
a.customer_no = b.customer_no and
a.entity = b.cust_entity and
b.stk_loc_no = '3' and
b.customer_no in (p_Scope);
END;
回答by Cos Callis
I believe there is a 'better way', but I'm not sure what it is right now...
我相信有一种“更好的方法”，但我不确定现在是什么......
This should work for you though:
不过，这应该对你有用：
replace:
代替：
b.customer_no in (p_Scope);
with
和
instr(p_Scope, ','||b.customer_no||',' ) > 0
This will search p_Scope and return a value of > 0 if b.customer_no appears in the list.
如果 b.customer_no 出现在列表中，这将搜索 p_Scope 并返回 > 0 的值。
Make sure that the first andlast character in the list is a comma (',')
确保列表中的第一个和最后一个字符是逗号 (',')
(also, as a new comer to Oracle I found Tech Republicto be a very helpful quick resource.)
（此外，作为 Oracle 的新手，我发现Tech Republic是一个非常有用的快速资源。）
回答by Tomá? Zálusky
Assuming declaration
假设声明
create or replace type cvarchar2 as table of varchar2(4000);
the query
查询
select * from some_table t where some_column in
('FOO','BAR')
gives same result as
给出相同的结果
select * from some_table t where some_column in
(select column_value from table(cvarchar2('FOO','BAR')))
You can use second one and pass PLSQL collection into table function. I do it this way from Java where ...table(?)perfectly works. Explain plan seems not too bad in comparison with traditional IN clause.
您可以使用第二个并将 PLSQL 集合传递到表函数中。我是从 Java 以这种方式做到的，在那里我可以...table(?)完美地工作。与传统的 IN 子句相比，解释计划似乎还不错。
Solutions based on text search with delimiters may be performance killer.
基于带分隔符的文本搜索的解决方案可能是性能杀手。
回答by Rajesh D
You can use it this way:
你可以这样使用它：
SELECT * FROM MATABLE
WHERE MT_ID
IN (SELECT REGEXP_SUBSTR(MYPARAM,'[^,]+', 1, LEVEL)
FROM DUAL
CONNECT BY REGEXP_SUBSTR(MYPARAM, '[^,]+', 1, LEVEL) IS NOT NULL))
MYPARAM- '368134,181956'
MYPARAM-'368134,181956'
回答by Cortright
For the record, here's another ugly way to do it.
为了记录，这是另一种丑陋的方法。
PROCEDURE getreport (
p_affiliates IN varchar2,
p_StartDate IN date,
p_EndDate IN date,
p_ReturnValue OUT sys_refcursor
) IS
BEGIN
DECLARE
sql_stmt VARCHAR2(4000);
BEGIN
sql_stmt := 'SELECT
FIRSTNAME,
LASTNAME,
ADDRESSLINE,
SUITE,
CITY,
STATE,
ZIP
FROM
ORDERHEADER head
INNER JOIN ORDERDETAIL detail
on head.ORDERTRACKINGLOGID = detail.ORDERTRACKINGLOGID
INNER JOIN ORDERTRACKINGDETAIL trackdetail
on detail.ORDERDETAILID = trackdetail.ORDERDETAILID
AND head.ORDERHEADERID = trackdetail.ORDERHEADERID
INNER JOIN AFFILIATE aff
on trackdetail.AFFILIATEID = aff.AFFILIATEID
WHERE
aff.AFFILIATEID IN
(
select
AFFILIATEID
from
AFFILIATE
where
AFFILIATEID IN (' || p_affiliates || ')
)
AND
head.CALENDAR_DATE >= TO_DATE( :p_StartDate )
AND
head.CALENDAR_DATE <= TO_DATE( :p_EndDate )
ORDER BY AFFILIATEID,
AFFILIATENAME
';
OPEN p_ReturnValue for sql_stmt USING p_StartDate, p_EndDate;
END;
END getreport;
