給Oracle添加split和splitstr函數(shù)的方法？oracle的split函數(shù)截取如何返回成一行數(shù)據(jù)CREATEORREPLACETYPEty_str_splitISTABLEOFVARCHA

給Oracle添加split和splitstr函數(shù)的方法？

oracle的split函數(shù)截取如何返回成一行數(shù)據(jù)

CREATEORREPLACETYPEty_str_splitISTABLEOFVARCHAR2(4000)

CREATEORREPLACEFUNCTIONsplit(p_strINVARCHAR2，p_delimiterINVARCHAR2)

RETURNty_str_split

IS

jINT:=0

iINT:=1

lenINT:=0

len1INT:=0

strVARCHAR2(4000)

str_splitty_str_split:=ty_str_split()

BEGIN

len:=LENGTH(p_str)

len1:=LENGTH(p_delimiter)

WHILEj

如何將oracle中一條數(shù)據(jù)拆分成多條？

例如表名為test，數(shù)據(jù)如下

原數(shù)據(jù)為

col1 col2 col3 col4

A 1 2 3

B 1 2 3

然后用如下語(yǔ)句

select * from

(select col1，col2 from test

union all

select col1，col3 from test

union all

select col1，col4 from test) as t

order by col1

得到的數(shù)據(jù)就是

A 1

A 2

A 3

B 1

B 2

B 3