1、创建varry
CREATE OR REPLACE TYPE Varchar2Varray IS VARRAY(100) of VARCHAR2(40);
2、创建分割函数
CREATE OR REPLACE FUNCTION f_strsplit (STRING VARCHAR2, substring VARCHAR2)
RETURN varchar2varray
IS
len INTEGER := LENGTH (substring);
lastpos INTEGER := 1 - len;
pos INTEGER;
num INTEGER;
i INTEGER := 1;
ret varchar2varray := varchar2varray (NULL);
v_str VARCHAR2 (20);
/**自定义split函数,将指定的字符串按指定的标志符分割成字符数组*/
BEGIN
LOOP
pos := INSTR (STRING, substring, lastpos + len);
IF pos > 0
THEN --found
num := pos - (lastpos + len);
ELSE --not found
num := LENGTH (STRING) + 1 - (lastpos + len);
END IF;
IF i > ret.LAST
THEN
ret.EXTEND;
END IF;
v_str := SUBSTR (STRING, lastpos + len, num);
--DBMS_OUTPUT.put_line (v_str);
ret(i) := v_str;
EXIT WHEN pos = 0;
lastpos := pos;
i := i + 1;
END LOOP;
RETURN ret;
END;
3、调用
select * from tab_dailyreport_user where user_id in (
SELECT * FROM
( TABLE( select f_strsplit(user_invite,',')
from tab_dailyreport_user t
where t.user_id = 168148)));
说明：主要是改写开发提过来的一个sql语句的帮助
select * from tab_dailyreport_user where user_id in (
--'166445','164216','171128','184427','160113','160133','160134','160138','160144','160163','160587','166457','167914','168076','168192','168997','169842','169901','184407','184747','185089','185130','208134','208141','208143','208183','160142','166455','167838','168074','168194','168666','185088','185138','185145','208103','169844','171071','160107','166421','166874','168193','179871','179872','184096','184228','184232','184269','184774','184969','185111','207871','160167','161813','14880','134','164355','168149')
select substr(regexp_replace,3,length(regexp_replace)-4) from (
select regexp_replace((select user_invite
from tab_dailyreport_user t
where t.user_id = 168148),
'(,)',
chr(39)||','||chr(39)) regexp_replace
from dual) )
括号里面的语句查询出来的结果是注释部分，直接使用注释的部分在in中可以查询，如果使用里面的语句不能查询，他们的需求是想把外面的user_id在里面的语句中的，可是oracle会把里面的in查询出来的结果作为一个整体，从而出现number类型不能和varchar类型匹配的提示，采用方法是分割in里面查询出来的语句，然后类型转换为table进行查询。