oracle 输出in字符,oracle处理in(字符串，字符串，字符串)

1.使用层次结构connect by ...：

select * from(select regexp_substr(replace('11110xx1xx2222','0xx1xx',','), '[^,]+', 1, rownum) n

from dual

connect by rownum < 50) where n is not null

2使用自定义的分拆函数split,：

首先需要定义 2 个类型

1. Row 类型

CREATE OR REPLACE TYPE ty_row_str_split as object (strValue VARCHAR2 ( 4000 ))

2. Table 类型

CREATE OR REPLACE TYPE ty_tbl_str_split IS TABLE OF ty_row_str_split

创建函数：

CREATE OR REPLACE FUNCTION fn_split(p_str IN VARCHAR2 ,

p_delimiter IN VARCHAR2 )

RETURN ty_tbl_str_split IS

j INT : = 0 ;

i INT : = 1 ;

len INT : = 0 ;

len1 INT : = 0 ;

str VARCHAR2 ( 4000 );

str_split ty_tbl_str_split : = ty_tbl_str_split();

BEGIN

len : = LENGTH(p_str);

len1 : = LENGTH(p_delimiter);

WHILE j < len LOOP

j : = INSTR(p_str, p_delimiter, i);

IF j = 0 THEN

j : = len ;

str : = SUBSTR(p_str, i);

str_split.EXTEND;

str_split(str_split. COUNT ) : = ty_row_str_split(strValue => str );

IF i >= len THEN

EXIT ;

END IF ;

ELSE

str : = SUBSTR(p_str, i, j - i);

i : = j + len1;

str_split.EXTEND;

str_split(str_split. COUNT ) : = ty_row_str_split(strValue => str );

END IF ;

END LOOP;

RETURN str_split;

END fn_split;

使用样例：

select to_number(strvalue) as Value from table (fn_split( ' 1,2,3 ' , ' , ' ))

select to_char(strvalue) as Value from table (fn_split( ' aa,bb,cc ' , ' , ' ))