oracle in字符串处理,函数中SQL中in 后面跟字符串要怎么做,请大侠们帮忙！

用动态SQL和package, 从一个到五个p_sub

create or replace package mypack is

function GET_ZBH_QTY(P_ITEM_ID IN NUMBER,S_ID IN NUMBER,P_SUB IN VARCHAR2) return number;

function GET_ZBH_QTY(P_ITEM_ID IN NUMBER,S_ID IN NUMBER

, P_SUB1 IN VARCHAR2, P_SUB2 IN VARCHAR2) return number;

function GET_ZBH_QTY(P_ITEM_ID IN NUMBER,S_ID IN NUMBER

, P_SUB1 IN VARCHAR2, P_SUB2 IN VARCHAR2, P_SUB3 IN VARCHAR2) return number;

function GET_ZBH_QTY(P_ITEM_ID IN NUMBER,S_ID IN NUMBER

, P_SUB1 IN VARCHAR2, P_SUB2 IN VARCHAR2, P_SUB3 IN VARCHAR2, P_SUB4 IN VARCHAR2) return number;

function GET_ZBH_QTY(P_ITEM_ID IN NUMBER,S_ID IN NUMBER

, P_SUB1 IN VARCHAR2, P_SUB2 IN VARCHAR2, P_SUB3 IN VARCHAR2, P_SUB4 IN VARCHAR2

, P_SUB5 IN VARCHAR2) return number;

end;

/

show error

create or replace package body mypack is

function GET_ZBH_QTY(P_ITEM_ID IN NUMBER,S_ID IN NUMBER,P_SUB IN VARCHAR2) return number is

begin

Result number;

begin

execute immediate 'SELECT SUM(T.TRANSACTION_QUANTITY)'||

' FROM INV.MTL_MATERIAL_TRANSACTIONS T '||

' WHERE T.SUBINVENTORY_CODE <> ''ZBH01'''||

' AND T.SUBINVENTORY_CODE IN ('''||P_SUB||''')'||

' AND T.INVENTORY_ITEM_ID = '||P_ITEM_ID||' '||

' AND T.TRX_SOURCE_LINE_ID = '||S_ID||' '||

' AND T.TRANSACTION_TYPE_ID = 52'

INTO Result;

return(Result);

end;

function GET_ZBH_QTY(P_ITEM_ID IN NUMBER,S_ID IN NUMBER

, P_SUB1 IN VARCHAR2, P_SUB2 IN VARCHAR2) return number is

begin

retrun mypack.get_zbh_qty(p_item_id,s_id,p_sub1||''','''||p_sub2);

end;

function GET_ZBH_QTY(P_ITEM_ID IN NUMBER,S_ID IN NUMBER

, P_SUB1 IN VARCHAR2, P_SUB2 IN VARCHAR2, P_SUB3 IN VARCHAR2) return number is

begin

retrun mypack.get_zbh_qty(p_item_id,s_id,p_sub1||''','''||p_sub2,p_sub3);

end;

function GET_ZBH_QTY(P_ITEM_ID IN NUMBER,S_ID IN NUMBER

, P_SUB1 IN VARCHAR2, P_SUB2 IN VARCHAR2, P_SUB3 IN VARCHAR2, P_SUB4 IN VARCHAR2) return number is

begin

retrun mypack.get_zbh_qty(p_item_id,s_id,p_sub1||''','''||p_sub2,p_sub3,p_sub4);

end;

function GET_ZBH_QTY(P_ITEM_ID IN NUMBER,S_ID IN NUMBER

, P_SUB1 IN VARCHAR2, P_SUB2 IN VARCHAR2, P_SUB3 IN VARCHAR2, P_SUB4 IN VARCHAR2

, P_SUB5 IN VARCHAR2) return number is

begin

retrun mypack.get_zbh_qty(p_item_id,s_id,p_sub1||''','''||p_sub2,p_sub3,p_sub4,p_sub5);

end;

end;

/

show error

用

mypack.GET_ZBH_QTY(99,99,'XE1');

mypack.GET_ZBH_QTY(99,99,'XE1','XE2','XE3','XE4);

mypack.GET_ZBH_QTY(99,99,'XE1','XE2','XE3','XE4','XE5');