问题描述
我将字符串数组(plcListchar)传递给存储过程,我想在 IN() 子句中使用这个字符串数组.
I am passing String array(plcListchar) to Stored Procedure, i would like to use this String array in IN() clause.
我不能在 IN() 子句中直接使用 plcListchar.让我展示我如何在 JAVA 中创建 plcListchar 字符串数组.
i can not use plcListchar directly in IN() clause. Let me show how i am crating plcListchar string array in JAVA.
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);
用于装箱 CHAR_ARRAY,
for crating CHAR_ARRAY,
create or replace type CHAR_ARRAY as table of varchar2;
我想在 IN 子句中使用 plcListchar.以下是我的存储过程.
i want use plcListchar in IN clause. the following is my Stored Procedure.
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.
推荐答案
假设你的集合是在 SQL 中定义的,而不仅仅是在 PL/SQL 中,你可以使用 TABLE
操作符(定义你发布在语法上无效-您需要为 VARCHAR2
)
Assuming that your collection is defined in SQL, not just in PL/SQL, you can use the TABLE
operator (the definition you posted isn't syntactically valid-- you'd need to specify a length for the VARCHAR2
)
AND p.plc_status IN (SELECT column_value
FROM TABLE( plcListchar ))
因为我没有你的表,一个使用 SCOTT
模式的例子
Since I don't have your tables, an example using the SCOTT
schema
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.
这篇关于IN() 子句中的数组 oracle PLSQL的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!