Oracle "IN clause" from parameter
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.
Thanks
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.ma开发者_JAVA百科in_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...
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.
Make sure that the first and last character in the list is a comma (',')
(also, as a new comer to Oracle I found Tech Republic to be a very helpful quick resource.)
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.
Solutions based on text search with delimiters may be performance killer.
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;
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'
精彩评论