定义数组:
CREATE OR REPLACE
TYPE Varchar2Varray IS VARRAY(100) of VARCHAR2(40);
/
定义函数:
CREATE OR REPLACE FUNCTION f_strsplit (STRING VARCHAR2, substring VARCHAR2)
RETURN varchar2varray
IS
len INTEGER := LENGTH (substring);
lastpos INTEGER := 1 - len;
pos INTEGER;
num INTEGER;
i INTEGER := 1;
ret varchar2varray := varchar2varray (NULL);
/**自定义split函数,将指定的字符串按指定的标志符分割成字符数组*/
BEGIN
LOOP
pos := INSTR (STRING, substring, lastpos + len);
IF pos > 0
THEN --found
num := pos - (lastpos + len);
ELSE --not found
num := LENGTH (STRING) + 1 - (lastpos + len);