写个吧,当然sql也能实现你的需求,不过还是函数简单点,异常判断自己加吧
CREATE OR REPLACE
FUNCTION convert_name_list( p_string IN VARCHAR2 ,p_delimiter IN VARCHAR2 default ',' )
RETURN VARCHAR2
AS
l_stringLONG DEFAULT p_string || p_delimiter;
l_id POR_USER.ID%TYPE;
l_name POR_USER.NAME%TYPE;
l_result VARCHAR2(32767);
n
NUMBER;
BEGIN
LOOP
EXIT WHEN l_string IS NULL;
n := instr( l_string, p_delimiter );
l_id :=
TRIM( substr( l_string, 1, n-1 ) );
SELECT NAME INTO l_name FROM POR_USER WHERE ID=l_id;
l_result := l_result||p_delimiter||l_name;
l_string := substr( l_string, n+1 );
END LOOP;
RETURN ltrim(l_result,p_delimiter);
END;
/
|