SQL IN() 子句中的数组 oracle PLSQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15515772/
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
Array in IN() clause oracle PLSQL
提问by Ravichandra
I am passing String array(plcListchar) to Stored Procedure, i would like to use this String array in IN() clause.
我将字符串数组（plcListchar）传递给存储过程，我想在 IN() 子句中使用这个字符串数组。
i can not use plcListchar directly in IN() clause. Let me show how i am crating plcListchar string array in JAVA.
我不能在 IN() 子句中直接使用 plcListchar。让我展示我如何在 JAVA 中创建 plcListchar 字符串数组。
String array[] = {"o", "l"};
ArrayDescriptor des = ArrayDescriptor.createDescriptor("CHAR_ARRAY", con);
ARRAY array_to_pass = new ARRAY(des,con,array);
callStmtProductSearch.setArray(4, array_to_pass);
for crating CHAR_ARRAY,
用于装箱 CHAR_ARRAY，
create or replace type CHAR_ARRAY as table of varchar2;
i want use plcListchar in IN clause. the following is my Stored Procedure.
我想在 IN 子句中使用 plcListchar。以下是我的存储过程。
CREATE OR REPLACE PROCEDURE product_search(
status IN varchar2,
plcList IN varchar2,
i_culture_id IN number,
plcListchar IN CHAR_ARRAY,
status_name OUT varchar2,
culture_code OUT varchar2)
AS
CURSOR search_cursor IS
SELECT p.status_name, p.culture_code
FROM PRISM_ITEM_cultures@prism p
WHERE p.publishable_flag=1
AND p.isroll =0
AND status = '-1'
AND p.plc_status IN ( ??);
BEGIN
OPEN search_cursor;
FETCH search_cursor INTO status_name, culture_code ;
CLOSE search_cursor;
END;
Could you please suggest me how to use, if you like to suggest any other logic, it is great.
您能否建议我如何使用，如果您想建议任何其他逻辑，那就太好了。
回答by Justin Cave
Assuming that your collection is defined in SQL, not just in PL/SQL, you can use the TABLEoperator (the definition you posted isn't syntactically valid-- you'd need to specify a length for the VARCHAR2)
假设您的集合是在 SQL 中定义的，而不仅仅是在 PL/SQL 中，您可以使用TABLE运算符（您发布的定义在语法上无效——您需要为 指定一个长度VARCHAR2）
AND p.plc_status IN (SELECT column_value
FROM TABLE( plcListchar ))
Since I don't have your tables, an example using the SCOTTschema
由于我没有您的表，因此使用SCOTT架构的示例
SQL> create type ename_tbl is table of varchar2(30);
2 /
Type created.
SQL> ed
Wrote file afiedt.buf
1 declare
2 l_enames ename_tbl := ename_tbl( 'KING', 'SMITH' );
3 begin
4 for i in (select *
5 from emp
6 where ename in (select column_value
7 from table( l_enames )))
8 loop
9 dbms_output.put_line( 'ENAME = ' || i.ename );
10 end loop;
11* end;
SQL> /
ENAME = KING
ENAME = SMITH
PL/SQL procedure successfully completed.
