设为首页 - 加入收藏 ASP站长网(Aspzz.Cn)- 科技、建站、经验、云计算、5G、大数据,站长网!
热搜: 创业者 数据 手机
当前位置: 首页 > 百科 > 正文

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

发布时间:2020-12-30 17:27 所属栏目:128 来源:网络整理
导读:需求描述:两张表,如下,需要查询tmp1表中id_new在tmp2中的name select from tmp1; select from tmp2; 方法一: 好处:简单,直接sql展示 劣处:如果主表数据量太大,十几亿的话,性能会大大下降,此时建议第二种方法 select a.id_old, to_char(wm_concat(di

需求描述:两张表,如下,需要查询tmp1表中id_new在tmp2中的name
select from tmp1;

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


select
from tmp2;

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


方法一:
好处:简单,直接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;

效果如下:

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

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

(编辑:ASP站长网)

    网友评论
    推荐文章
      热点阅读