Oracle"IN子句"从参数 [英] Oracle "IN clause" from parameter

问题描述

我对Oracle非常不熟悉，只是想使它起作用.我在此处和

I'm very unfamiliar with Oracle and am just trying to get this to work. I've looked here and here but 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.

谢谢

这是proc(p_Scope是逗号分隔的输入):

Here's the proc (p_Scope would be the comma-delimited input):

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;

推荐答案

我相信这是一种更好的方法"，但是我不确定现在是什么...

I believe there is a 'better way', but I'm not sure what it is right now...

这应该对您有用:

替换:

b.customer_no in (p_Scope);

与

instr(p_Scope, ','||b.customer_no||',' ) > 0

如果b.customer_no出现在列表中，它将搜索p_Scope并返回> 0的值.

This will search p_Scope and return a value of > 0 if b.customer_no appears in the list.

确保列表中的第一个和最后一个字符是逗号('，')

Make sure that the first and last character in the list is a comma (',')

(另外，作为Oracle的新手，我发现技术共和国是一个非常有用的快速资源.)

(also, as a new comer to Oracle I found Tech Republic to be a very helpful quick resource.)

这篇关于Oracle"IN子句"从参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！