oracle 创建nchar类型,ORACLE创建自定义函数返回varchar类型

方法二：创建自定义函数来实现

create or replace function f_tmp_split(p_str varchar2, p_f varchar2)

return varchar2 is

v_pos pls_integer := 0; --获取当前分隔符位置

v_pre_pos pls_integer := 1; --从第几位开始截取

v_len pls_integer := 0; --字符串长度

v_len1 pls_integer := 0; --分隔符长度

v_result dbms_sql.Varchar2_Table; --结果集

v_num pls_integer := 1; --元素数量

v_name_class varchar2(1000); --返回的集合

v_name_tmp varchar2(1000); --返回拼接的值

begin

v_len := length(p_str);

v_len1 := length(p_f);

while v_pos < v_len loop

v_pos := instr(p_str, p_f, v_pre_pos);

if v_pos = 0 then

v_pre_pos := v_len;

v_result(v_num) := substr(p_str, v_pre_pos);

begin

select a.name

into v_name_tmp

from tmp2 a

where a.id_old = v_result(v_num);

exception

when no_data_found then

v_name_tmp := '';

end;

v_name_class := v_name_class || v_name_tmp;

if v_pre_pos >= v_len then

exit;

end if;

else

v_result(v_num) := substr(p_str, v_pre_pos, v_pos - v_pre_pos);

begin

select a.name || p_f

into v_name_tmp

from tmp2 a

where a.id_old = v_result(v_num);

exception

when no_data_found then

v_name_tmp := '';

end;

v_name_class := v_name_class || v_name_tmp;

v_pre_pos := v_pos + v_len1;

end if;

end loop;

return v_name_class;

end;