Orcale分割字串返回数组

定义一个数组，元素是长度小于100字符的字串
TYPE str_array IS TABLE OF varchar(100);
定义一个数组，元素是长度小于100字符的字串
type arrays is table of varchar2(200);
declare
str_array array;
begin
str_array(1)='A0001';
str_array.extends(1);
str_array(str_array.count)='A0002';(=str_array(2)='A0002')
end;
分割字串返回数组
/*
用指定字符将字符串分隔为数组
str 字符串
str_split 分割字符
return 分割后的数组
*/
function splitString(sourceString varchar2,splitFlag varchar2)
return arrays
is
dataList arrays:=arrays();--数组定义
temp varchar2(200);--存储临时数据
tempSource varchar2(200):=sourceString;--存储需要分割的字符串
splitIndex number(2);--分隔符在字符串中首次出现的位置
splitLength number(2):=length(splitFlag);--分割符长度
begin
while instr(tempSource,splitFlag)>0 loop
splitIndex := instr(tempSource,splitFlag);
--取出第一个分割符前的字符串
temp := substr(tempSource,1,splitIndex-splitLength);
--减去第一个分隔符前的字符串
tempSource := substr(tempSource,splitIndex+splitLength,length(tempSource));
if temp is not null then
dataList.extend(1);
dataList(dataList.count) := temp;
end if;
end loop;
if tempSource is not null then
dataList.extend(1);
dataList(dataList.count) := tempSource;
end if;
return dataList;
end splitString; 

 

 

字符串分割
create or replace FUNCTION         CHAR_SPLIT
(INPUT in varchar2, SPLIT_CHAR in varchar2 default ',')
return CHAR_TABLE as
str long default INPUT||SPLIT_CHAR;
n number;
OUPUT CHAR_TABLE:=CHAR_TABLE();
begin
  loop
  n := instr(str,SPLIT_CHAR );
  exit when (nvl(n,0) = 0);
    OUPUT.extend;
    OUPUT(OUPUT.count):= ltrim(rtrim(substr(str,1,n-1)));
    str:= substr(str, n+length(SPLIT_CHAR) );
  end loop;
  --dbms_output.PUT_LINE(OUPUT.count);
  return OUPUT;
end;

Oracle自定义函数之二：列数据合并成字符串
 create or replace FUNCTION SUMSTR(COL VARCHAR2,SQLSTR VARCHAR2) RETURN VARCHAR2
IS
TMP_SUM VARCHAR2(100);
NEWSTR VARCHAR2(2000);
TYPE MYCURSOR IS REF CURSOR;
C1 MYCURSOR;
BEGIN
  OPEN C1 FOR SQLSTR;
  LOOP
  FETCH C1 INTO TMP_SUM;
  EXIT WHEN C1%NOTFOUND;
  NEWSTR:=NEWSTR||TMP_SUM||',';
  END LOOP;
  CLOSE C1;
  NEWSTR:= substr(NEWSTR,0,length(NEWSTR)-1);
  RETURN NEWSTR;
END;