对于P_R4GSTATE
,我有一个IN
参数,其中我获得的过程值为"Rajasthan,Maharashtra,Haryana"
程序如下:
PROCEDURE GET_VENDOR_INFO
(
PVENDOR_NAME IN NVARCHAR2,
P_R4GSTATE IN NVARCHAR2,
P_OUTVENDOR OUT SYS_REFCURSOR
)
AS
BEGIN
OPEN P_OUTVENDOR FOR
SELECT * FROM IPCOLO_IPFEE_CALC_MST WHERE CIRCLE=P_R4GSTATE;
END IF;
END GET_VENDOR_INFO;
问题是数据可能在表中的任何一个状态中都是他们的,如何执行和检查。
将其拆分为行并在IN
子句中使用:
open p_outvendor for
select *
from ipcolo_ipfee_calc_mst
where circle in (select regexp_substr(p_r4gstate, '[^,]+', 1, level)
from dual
connect by level <= regexp_count(p_r4gstate, ',') + 1
);