下面的方法提供了比较好的数据库分割字符串的方法。不要在每次需要这种功能的地方各写一个，统一的工具类非常重要。更能排除以下低级的错误。
create or replace
PACKAGE pk_genutilitypkg
AS
/*
Generic String Parser: provide a delimiter and it returns an
index-by table of the individual elements of the string that are
separated by the specified delimiter.
Author: "GAUTHAM CHANDRASEKARAN" <gautha@hotmail.com>
*/
TYPE t_string IS TABLE OF VARCHAR2(2000)
INDEX BY BINARY_INTEGER;
m_ctr NUMBER(5);
m_pos NUMBER(5);
PROCEDURE sp_parsestring (
p_string IN VARCHAR2,
delimiter IN VARCHAR2,
p_t_string OUT t_string);
END pk_genutilitypkg;
create or replace
PACKAGE BODY pk_genutilitypkg
AS
PROCEDURE sp_parsestring (
p_string IN VARCHAR2,
delimiter IN VARCHAR2,
p_t_string OUT t_string)
IS
m_string VARCHAR2(4000);
BEGIN
/* Raise a Error if the length of the delimiter is not 1 */
IF LENGTH (delimiter) != 1
THEN
raise_application_error (-20001,
'Delimiter should be of only one character');
RETURN;
END IF;
m_string := p_string;
m_ctr := 1;
LOOP
m_pos := INSTR (m_string, delimiter);
IF m_pos > 1
THEN
p_t_string (m_ctr) := LTRIM(RTRIM( SUBSTR (m_string, 1, m_pos - 1) ));
IF (m_pos < LENGTH (m_string))
THEN
m_string := SUBSTR (
m_string,
m_pos + 1,
LENGTH (m_string) - m_pos
);
ELSIF m_pos = LENGTH (m_string)
THEN
m_ctr := m_ctr + 1;
p_t_string (m_ctr) := NULL;
EXIT;
END IF;
ELSIF m_pos = 1
THEN
p_t_string (m_ctr) := NULL;
IF m_pos < LENGTH (m_string)
THEN
m_string := SUBSTR (
m_string,
m_pos + 1,
LENGTH (m_string) - m_pos
);
ELSIF m_pos = LENGTH (m_string)
THEN
m_ctr := m_ctr + 1;
p_t_string (m_ctr) := NULL;
EXIT;
END IF;
ELSIF m_pos = 0
THEN
p_t_string (m_ctr) := LTRIM(RTRIM( m_string ));
EXIT;
END IF;
m_ctr := m_ctr + 1;
END LOOP;
END;
END pk_genutilitypkg;
使用方法如下:
PROCEDURE UseStringParse(
user_list IN VARCHAR2
)IS
userid_list VARCHAR2 (1000) := user_list;
userlistarray pk_genutilitypkg.t_string;
vuserid VARCHAR2 (100);
BEGIN
pk_genutilitypkg.sp_parsestring(userid_list, ',', userlistarray);
FOR userindex IN 1 .. userlistarray.COUNT
loop
vuserid:=userlistarray(userindex);
END LOOP;
END UseStringParse;
|