我将String数组(plcListchar)传递给Stored Procedure,我想在IN()子句中使用此String数组.
我不能直接在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);
为了装箱CHAR_ARRAY,
create or replace type CHAR_ARRAY as table of varchar2;
我想在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;
你能建议我如何使用,如果你想建议任何其他逻辑,这是伟大的.
假设您的集合是在sql中定义的,而不仅仅是在PL / sql中,您可以使用TABLE操作符(您发布的定义在语法上无效 – 您需要为VARCHAR2指定一个长度)
AND p.plc_status IN (SELECT column_value
FROM TABLE( plcListchar ))
因为我没有你的表,使用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.
