需求描述：两张表，如下,需要查询tmp1表中id_new在tmp2中的name
**select * from tmp1; **
select * from tmp2;
方法一：
好处：简单，直接sql展示
劣处：如果主表数据量太大，十几亿的话，性能会大大下降，此时建议第二种方法
select a.id_old,
to_char(wm_concat(distinct a.id_new)) id_new,
to_char(wm_concat(distinct b.name)) name
from tmp2 b,
(select a.id_old, regexp_substr(a.id_new, '[^,]+', 1, level) id_new
from tmp1 a
connect by level <= regexp_count(a.id_new, ',') + 1) a
where a.id_new = b.id_old(+)
group by a.id_old;
方法二：创建自定义函数来实现 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;
效果如下：
