1、先建立一个包,定义数组类型:
CREATE OR REPLACE PACKAGE pkg_string is
-- Purpose : 字符串处理
-- Public type declarations
TYPE StringArray IS VARRAY(2000) OF VARCHAR2(2000);
END pkg_string;
2、拆分字符串的通用函数,即数组的函数:
CREATE OR REPLACE FUNCTION fun_ParseToArray(
a_SourceString IN VARCHAR2, --源字符串
a_Delimiter IN VARCHAR2 --拆分的分隔符
) RETURN pkg_string.StringArray IS
-- ********************************************************
-- 根据分隔符拆分字符串为数组
-- ********************************************************
v_Result pkg_string.StringArray; --数组
iCount INTEGER;
iBegin INTEGER;
sValue VARCHAR2(2000);
sSplitString VARCHAR2(2000);
BEGIN
--检查参数
IF a_SourceString IS NULL OR a_Delimiter IS NULL THEN
RETURN(v_Result);
END IF;
IF a_SourceString = '' THEN
RETURN(v_Result);
END IF;
--初始化数组
v_Result := pkg_string.StringArray();
--Only one entry was found
IF instr(a_SourceString,a_Delimiter) = 0 THEN
-- 数组加1维
v_Result.extend();
-- 将数据存入数组
v_Result(1) := a_SourceString;
RETURN(v_Result);
END IF;
iCount := 0;
sSplitString := a_SourceString;
LOOP
iBegin := INSTR(sSplitString,a_Delimiter);
EXIT WHEN iBegin < 1;
sValue := Substr(sSplitString,1,iBegin - 1);
sSplitString := SubStr(sSplitString,iBegin + 1);
-- 计数器加1
iCount := iCount + 1;
-- 数组加1维
v_Result.extend();
-- 将数据存入数组
v_Result(iCount) := sValue;
END LOOP;
--Set last entry
sSplitString := SubStr(sSplitString, iBegin);
--Update array and counter if necessary
IF Length(sSplitString) > 0 THEN
-- 计数器加1
iCount := iCount + 1;
-- 数组加1维
v_Result.extend();
-- 将数据存入数组
v_Result(iCount) := sSplitString;
END IF;
RETURN(v_Result);
EXCEPTION
WHEN others THEN
return v_Result ;
END fun_ParseToArray;
3、用法:
CREATE OR REPLACE FUNCTION demo(--占比值
field in VARCHAR
)
RETURN VARCHAR2
IS
v_sql VARCHAR2(4000) := '';
v_number number;
vs_row pkg_string.StringArray;
BEGIN
--求传入字段的分割之后的size,例子field=123,456
SELECT LENGTHB(TRANSLATE(field,','||field,',')) into v_number FROM DUAL;
v_number := v_number+1;
--分割字符串组成数组
vs_row := fun_ParseToArray(field,',');
--循环数组
for i in 1 .. v_number loop
v_sql := v_sql || vs_row(i)||'ccc';
end loop;
if v_sql is not null then
RETURN v_sql;
else
RETURN '【数值为空】';
end if;
EXCEPTION
WHEN NO_DATA_FOUND THEN
return ('你需要的数据不存在!');
WHEN OTHERS THEN
return '错误';
END demo;
参考:https://sangei.iteye.com/blog/1343837