在sql server与oracle里,调用存储过程时,经常需要将数据拼成字符串做为参数调用存储过程,而在储存过程中分割字符串虽然简单但麻烦,封装了该函数,可以将拼串分割成内存表返回,方便使用,返回的表字段从a,b,c,d,e开始,最多支持15列
调用方式:select * from flfcp_StrSplitToTable('a,b,c|e,f,g', ',', '|')
返回结果:
调用方式:select * from flfcp_StrSplitToTable('a,b,c,e,f,g', ',', '')
返回结果:
调用方式:select * from flfcp_StrSplitToTable('a,b,c,e,f,g', '', ',')
返回结果:
SQL SERVER 版本脚本
/****** Object: UserDefinedFunction [dbo].[flfcp_StrSplitToTable] Script Date: 08/05/2013 20:29:07 ******/
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[flfcp_StrSplitToTable](@strList VARCHAR(5000), @columnSplitChar VARCHAR(1), @rowSplitChar VARCHAR(1))
RETURNS @tempTable TABLE(ID INT IDENTITY(1,1) PRIMARY KEY,
[A] [varchar](100) NULL,
[B] [varchar](100) NULL,
[C] [varchar](100) NULL,
[D] [varchar](100) NULL,
[E] [varchar](100) NULL,
[F] [varchar](100) NULL,
[G] [varchar](100) NULL,
[H] [varchar](100) NULL,
[I] [varchar](100) NULL,
[J] [varchar](100) NULL,
[K] [varchar](100) NULL,
[L] [varchar](100) NULL,
[M] [varchar](100) NULL,
[N] [varchar](100) NULL,
[O] [varchar](100) NULL )
AS
BEGIN
DECLARE @A varchar(100)
DECLARE @B varchar(100)
DECLARE @C varchar(100)
DECLARE @D varchar(100)
DECLARE @E varchar(100)
DECLARE @F varchar(100)
DECLARE @G varchar(100)
DECLARE @H varchar(100)
DECLARE @I varchar(100)
DECLARE @J varchar(100)
DECLARE @K varchar(100)
DECLARE @L varchar(100)
DECLARE @M varchar(100)
DECLARE @N varchar(100)
DECLARE @O varchar(100)
DECLARE @colNo INT -- 列的序号
DECLARE @colStart INT -- 列开始查询位置
DECLARE @colEnd INT -- 列查找到的位置
DECLARE @strData VARCHAR(2000) -- 内容
DECLARE @colData VARCHAR(2000) -- 列内容
DECLARE @rowStart INT -- 行开始查询位置
DECLARE @rowEnd INT -- 行查找到的位置
DECLARE @strLen INT -- 字符串长度
DECLARE @LastFlag INT -- 最后一行标志
DECLARE @LastFlag2 INT -- 最后一行标志
SET @LastFlag=1
SET @LastFlag2=1
IF ( LEN(ISNULL(@strList,''))>0 )
BEGIN
IF ( LEN(ISNULL(@columnSplitChar,''))=0 )
BEGIN --列为空的情况
IF ( LEN(ISNULL(@rowSplitChar,''))=0 )
BEGIN
-- 一行一列
INSERT INTO @tempTable (A) VALUES(@strList)
END
ELSE
BEGIN
-- 多行一列
SET @rowStart=1
SET @rowEnd= CHARINDEX(@rowSplitChar,@strList,@rowStart)
WHILE(@rowEnd>0 )
BEGIN
SET @strData =SUBSTRING(@strList,@rowStart,@rowEnd-@rowStart)
INSERT INTO @tempTable (A) VALUES(@strData)
SET @rowStart=@rowEnd+1
SET @rowEnd= CHARINDEX(@rowSplitChar,@strList,@rowStart)
END
--最后行
SET @strData =SUBSTRING(@strList,@rowStart,8000)
INSERT INTO @tempTable (A) VALUES(@strData)
END
END
ELSE
BEGIN --列不为空的情况
IF ( LEN(ISNULL(@rowSplitChar,''))=0 )
BEGIN
--一行多列
SET @colNo=1
SET @colStart=1
SET @colEnd= CHARINDEX(@columnSplitChar,@strList,@colStart)
WHILE(@colEnd>0 OR @LastFlag>0 )
BEGIN
IF ( @colEnd>0 )
SET @colData =SUBSTRING(@strList,@colStart,@colEnd-@colStart)
ELSE
SET @colData =SUBSTRING(@strList,@colStart,8000)
IF ( @colNo=1 )
SET @A=@colData
ELSE IF ( @colNo=2 )
SET @B=@colData
ELSE IF ( @colNo=3 )
SET @C=@colData
ELSE IF ( @colNo=4 )
SET @D=@colData
ELSE IF ( @colNo=5 )
SET @E=@colData
ELSE IF ( @colNo=6 )
SET @F=@colData
ELSE IF ( @colNo=7 )
SET @G=@colData
ELSE IF ( @colNo=8 )
SET @H=@colData
ELSE IF ( @colNo=9 )
SET @I=@colData
ELSE IF ( @colNo=10 )
SET @J=@colData
ELSE IF ( @colNo=11 )
SET @K=@colData
ELSE IF ( @colNo=12 )
SET @L=@colData
ELSE IF ( @colNo=13 )
SET @M=@colData
ELSE IF ( @colNo=14 )
SET @N=@colData
ELSE IF ( @colNo=15 )
SET @O=@colData
IF ( @colEnd>0 )
BEGIN
SET @colNo=@colNo+1
SET @colStart=@colEnd+1
SET @colEnd= CHARINDEX(@columnSplitChar,@strList,@colStart)
END
ELSE
BEGIN
SET @LastFlag=0
END
END
INSERT INTO @tempTable VALUES(@A, @B, @C, @D,
@E, @F, @G, @H,
@I, @J, @K, @L,
@M, @N, @O)
END
ELSE
BEGIN
--多行多列
SET @rowStart=1
SET @rowEnd= CHARINDEX(@rowSplitChar,@strList,@rowStart)
WHILE(@rowEnd>0 OR @LastFlag>0)
BEGIN
IF ( @rowEnd>0 )
SET @strData =SUBSTRING(@strList,@rowStart,@rowEnd-@rowStart)
ELSE
SET @strData =SUBSTRING(@strList,@rowStart,8000)
--------------获取列开始
SET @colNo=1
SET @A =''
SET @B =''
SET @C =''
SET @D =''
SET @E =''
SET @F =''
SET @G =''
SET @H =''
SET @I =''
SET @J =''
SET @K =''
SET @L =''
SET @M =''
SET @N =''
SET @O =''
SET @LastFlag2=1
SET @colStart=1
SET @colEnd= CHARINDEX(@columnSplitChar,@strData,@colStart)
WHILE(@colEnd>0 OR @LastFlag2>0 )
BEGIN
IF ( @colEnd>0 )
SET @colData =SUBSTRING(@strData,@colStart,@colEnd-@colStart)
ELSE
SET @colData =SUBSTRING(@strData,@colStart,8000)
IF ( @colNo=1 )
SET @A=@colData
ELSE IF ( @colNo=2 )
SET @B=@colData
ELSE IF ( @colNo=3 )
SET @C=@colData
ELSE IF ( @colNo=4 )
SET @D=@colData
ELSE IF ( @colNo=5 )
SET @E=@colData
ELSE IF ( @colNo=6 )
SET @F=@colData
ELSE IF ( @colNo=7 )
SET @G=@colData
ELSE IF ( @colNo=8 )
SET @H=@colData
ELSE IF ( @colNo=9 )
SET @I=@colData
ELSE IF ( @colNo=10 )
SET @J=@colData
ELSE IF ( @colNo=11 )
SET @K=@colData
ELSE IF ( @colNo=12 )
SET @L=@colData
ELSE IF ( @colNo=13 )
SET @M=@colData
ELSE IF ( @colNo=14 )
SET @N=@colData
ELSE IF ( @colNo=15 )
SET @O=@colData
IF ( @colEnd>0 )
BEGIN
SET @colNo=@colNo+1
SET @colStart=@colEnd+1
SET @colEnd= CHARINDEX(@columnSplitChar,@strData,@colStart)
END
ELSE
BEGIN
SET @LastFlag2=0
END
END
INSERT INTO @tempTable VALUES(@A, @B, @C, @D,
@E, @F, @G, @H,
@I, @J, @K, @L,
@M, @N, @O)
--------------获取列结束
IF ( @rowEnd>0 )
BEGIN
SET @rowStart=@rowEnd+1
SET @rowEnd= CHARINDEX(@rowSplitChar,@strList,@rowStart)
END
ELSE
BEGIN
SET @LastFlag=0
END
END
END
END
END
RETURN
END
Oracle版本脚本
示例用法:
SELECT * FROM TABLE(flfcp_StrSplitToTable('a,b,c,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,AB', '' , '' ));
SELECT * FROM TABLE(flfcp_StrSplitToTable('a,b,c', ',' , '' ));
SELECT * FROM TABLE(flfcp_StrSplitToTable('a,b,c', '' , ',' ));
SELECT * FROM TABLE(flfcp_StrSplitToTable('a,b,c;d,e,f;g,h,i,l,m,n', ',' , ';' ));
DROP TYPE fltype_StrSplitToTable;
CREATE OR REPLACE TYPE fltp_StrSplitToTableObject AS OBJECT(
A VARCHAR2(100), B VARCHAR2(100), C VARCHAR2(100), D VARCHAR2(100),
E VARCHAR2(100), F VARCHAR2(100), G VARCHAR2(100), H VARCHAR2(100),
I VARCHAR2(100), J VARCHAR2(100), K VARCHAR2(100), L VARCHAR2(100),
M VARCHAR2(100), N VARCHAR2(100), O VARCHAR2(100), P VARCHAR2(100),
Q VARCHAR2(100), R VARCHAR2(100), S VARCHAR2(100), T VARCHAR2(100),
U VARCHAR2(100), V VARCHAR2(100), W VARCHAR2(100), X VARCHAR2(100),
Y VARCHAR2(100), Z VARCHAR2(100)
);
CREATE OR REPLACE TYPE fltype_StrSplitToTable AS TABLE OF fltp_StrSplitToTableObject;
CREATE OR REPLACE FUNCTION flfcp_StrSplitToTable
(
strList VARCHAR2,
columnSplitChar VARCHAR2,
rowSplitChar VARCHAR2
)
RETURN fltype_StrSplitToTable
AS
v_test fltype_StrSplitToTable := fltype_StrSplitToTable();
A VARCHAR2(100);
B VARCHAR2(100);
C VARCHAR2(100);
D VARCHAR2(100);
E VARCHAR2(100);
F VARCHAR2(100);
G VARCHAR2(100);
H VARCHAR2(100);
I VARCHAR2(100);
J VARCHAR2(100);
K VARCHAR2(100);
L VARCHAR2(100);
M VARCHAR2(100);
N VARCHAR2(100);
O VARCHAR2(100);
P VARCHAR2(100);
Q VARCHAR2(100);
R VARCHAR2(100);
S VARCHAR2(100);
T VARCHAR2(100);
U VARCHAR2(100);
V VARCHAR2(100);
W VARCHAR2(100);
X VARCHAR2(100);
Y VARCHAR2(100);
Z VARCHAR2(100);
colNo NUMBER; -- 列的序号
colStart NUMBER; -- 列开始查询位置
colEnd NUMBER; -- 列查找到的位置
strData VARCHAR2(4000); -- 内容
colData VARCHAR2(4000); -- 列内容
rowStart NUMBER; -- 行开始查询位置
rowEnd NUMBER; -- 行查找到的位置
strLENGTH NUMBER; -- 字符串长度
LastFlag NUMBER; -- 最后一行标志
LastFlag2 NUMBER; -- 最后一行标志
BEGIN
LastFlag:=1;
LastFlag2:=1;
IF ( LENGTH(NVL(strList,''))>0 ) THEN
IF ( columnSplitChar IS NULL OR LENGTH(columnSplitChar)=0 ) THEN
--列为空的情况
IF ( rowSplitChar IS NULL OR LENGTH(rowSplitChar)=0 ) THEN
-- 一行一列
v_test.EXTEND();
v_test(v_test.count) := fltp_StrSplitToTableObject(strList,B,C,D,
E,F,G,H,
I,J,K,L,
M,N,O,P,
Q,R,S,T,
U,V,W,X,
Y,Z);
ELSE
-- 多行一列
rowStart:=1;
rowEnd:= INSTR(strList,rowSplitChar,rowStart);
WHILE(rowEnd>0 ) LOOP
strData:=SUBSTR(strList,rowStart,rowEnd-rowStart);
v_test.EXTEND();
v_test(v_test.count) := fltp_StrSplitToTableObject(strData,B,C,D,
E,F,G,H,
I,J,K,L,
M,N,O,P,
Q,R,S,T,
U,V,W,X,
Y,Z);
rowStart:=rowEnd+1;
rowEnd:= INSTR(strList,rowSplitChar,rowStart);
END LOOP;
--最后行
strData :=SUBSTR(strList,rowStart,4000);
v_test.EXTEND();
v_test(v_test.count) := fltp_StrSplitToTableObject(strData,B,C,D,
E,F,G,H,
I,J,K,L,
M,N,O,P,
Q,R,S,T,
U,V,W,X,
Y,Z);
END IF;
ELSE --列不为空的情况
IF ( rowSplitChar IS NULL OR LENGTH(rowSplitChar)=0 ) THEN
--一行多列
colNo:=1;
colStart:=1;
colEnd:= INSTR(strList,columnSplitChar,colStart);
WHILE(colEnd>0 OR LastFlag>0 ) LOOP
IF ( colEnd>0 ) THEN
colData :=SUBSTR(strList,colStart,colEnd-colStart) ;
ELSE
colData :=SUBSTR(strList,colStart,4000);
END IF;
IF ( colNo=1 ) THEN
A:=colData;
ELSIF ( colNo=2 ) THEN
B:=colData;
ELSIF ( colNo=3 ) THEN
C:=colData;
ELSIF ( colNo=4 ) THEN
D:=colData;
ELSIF ( colNo=5 ) THEN
E:=colData;
ELSIF ( colNo=6 ) THEN
F:=colData;
ELSIF ( colNo=7 ) THEN
G:=colData;
ELSIF ( colNo=8 ) THEN
H:=colData;
ELSIF ( colNo=9 ) THEN
I:=colData;
ELSIF ( colNo=10 ) THEN
J:=colData ;
ELSIF ( colNo=11 ) THEN
K:=colData;
ELSIF ( colNo=12 ) THEN
L:=colData;
ELSIF ( colNo=13 ) THEN
M:=colData;
ELSIF ( colNo=14 ) THEN
N:=colData;
ELSIF ( colNo=15 ) THEN
O:=colData;
ELSIF ( colNo=16 ) THEN
P:=colData;
ELSIF ( colNo=17 ) THEN
Q:=colData;
ELSIF ( colNo=18 ) THEN
R:=colData;
ELSIF ( colNo=19 ) THEN
S:=colData;
ELSIF ( colNo=20 ) THEN
T:=colData;
ELSIF ( colNo=21 ) THEN
U:=colData;
ELSIF ( colNo=22 ) THEN
V:=colData;
ELSIF ( colNo=23 ) THEN
W:=colData;
ELSIF ( colNo=24 ) THEN
X:=colData;
ELSIF ( colNo=25 ) THEN
Y:=colData;
ELSIF ( colNo=26 ) THEN
Z:=colData;
END IF;
IF ( colEnd>0 ) THEN
colNo:=colNo+1;
colStart:=colEnd+1;
colEnd:= INSTR(strList, columnSplitChar, colStart);
ELSE
LastFlag:=0;
END IF;
END LOOP;
v_test.EXTEND();
v_test(v_test.count) := fltp_StrSplitToTableObject(A,B,C,D,
E,F,G,H,
I,J,K,L,
M,N,O,P,
Q,R,S,T,
U,V,W,X,
Y,Z);
ELSE
--多行多列
rowStart:=1;
rowEnd:= INSTR(strList,rowSplitChar,rowStart);
WHILE(rowEnd>0 OR LastFlag>0) LOOP
IF ( rowEnd>0 ) THEN
strData :=SUBSTR(strList,rowStart,rowEnd-rowStart);
ELSE
strData :=SUBSTR(strList,rowStart,4000);
END IF;
--------------获取列开始
colNo:=1;
A :='';
B :='';
C :='';
D :='';
E :='';
F :='';
G :='';
H :='';
I :='';
J :='';
K :='';
L :='';
M :='';
N :='';
O :='';
P :='';
Q :='';
R :='';
S :='';
T :='';
U :='';
V :='';
W :='';
X :='';
Y :='';
Z :='';
LastFlag2:=1;
colStart:=1;
colEnd:= INSTR(strData,columnSplitChar,colStart);
WHILE(colEnd>0 OR LastFlag2>0 ) LOOP
IF ( colEnd>0 ) THEN
colData :=SUBSTR(strData,colStart,colEnd-colStart);
ELSE
colData :=SUBSTR(strData,colStart,4000);
END IF;
IF ( colNo=1 ) THEN
A:=colData;
ELSIF ( colNo=2 ) THEN
B:=colData;
ELSIF ( colNo=3 ) THEN
C:=colData;
ELSIF ( colNo=4 ) THEN
D:=colData;
ELSIF ( colNo=5 ) THEN
E:=colData;
ELSIF ( colNo=6 ) THEN
F:=colData;
ELSIF ( colNo=7 ) THEN
G:=colData;
ELSIF ( colNo=8 ) THEN
H:=colData;
ELSIF ( colNo=9 ) THEN
I:=colData;
ELSIF ( colNo=10 ) THEN
J:=colData ;
ELSIF ( colNo=11 ) THEN
K:=colData;
ELSIF ( colNo=12 ) THEN
L:=colData;
ELSIF ( colNo=13 ) THEN
M:=colData;
ELSIF ( colNo=14 ) THEN
N:=colData;
ELSIF ( colNo=15 ) THEN
O:=colData;
ELSIF ( colNo=16 ) THEN
P:=colData;
ELSIF ( colNo=17 ) THEN
Q:=colData;
ELSIF ( colNo=18 ) THEN
R:=colData;
ELSIF ( colNo=19 ) THEN
S:=colData;
ELSIF ( colNo=20 ) THEN
T:=colData;
ELSIF ( colNo=21 ) THEN
U:=colData;
ELSIF ( colNo=22 ) THEN
V:=colData;
ELSIF ( colNo=23 ) THEN
W:=colData;
ELSIF ( colNo=24 ) THEN
X:=colData;
ELSIF ( colNo=25 ) THEN
Y:=colData;
ELSIF ( colNo=26 ) THEN
Z:=colData;
END IF;
IF ( colEnd>0 ) THEN
colNo:=colNo+1;
colStart:=colEnd+1;
colEnd:= INSTR(strData, columnSplitChar, colStart);
ELSE
LastFlag2:=0;
END IF;
END LOOP;
v_test.EXTEND();
v_test(v_test.count) := fltp_StrSplitToTableObject(A,B,C,D,
E,F,G,H,
I,J,K,L,
M,N,O,P,
Q,R,S,T,
U,V,W,X,
Y,Z);
--------------获取列结束
IF ( rowEnd>0 ) THEN
rowStart:=rowEnd+1;
rowEnd := INSTR(strList,rowSplitChar,rowStart);
ELSE
LastFlag :=0;
END IF;
END LOOP;
END IF;
END IF;
END IF;
RETURN v_test;
END flfcp_StrSplitToTable;
参考:PL/SQL表(oracle内存表)---table()函数用法