oracle数据转换方法，将字典表的id值以文本形式展示
- （本文原创，引用请注明出处--zhangjieatbky）
- 应用场景：例如，将某字段中存放的id值，如1,2,3转换成文本展示，如 篮球、足球、排球
- 创建两张表
-
1 create a1(id number(8),text varchar(15)); //创建字典表 2 insert into a1 values(10,足球); 3 insert into a1 values(11,篮球); 4 insert into a1 values(12,羽毛球); 5 insert into a1 values(13,排球); 6 7 create b1(bid number(8),btext varchar2(15)); //业务主表 8 insert into b1 values(1000,'10,11,13'); 9 insert into b1 values(2000,'11,12');
- 创建转换函数
-
1 //创建函数 2 create or replace function converter( 3 p_bid b1.bid%type) 4 return varchar2 5 as 6 v_len number(2); 7 v_dict_text varchar(80); 8 v_index binary_integer :=1; 9 v_result varchar(150):=''; 10 v_temp1 varchar2(3); 11 v_temp2 varchar2(50); 12 begin 13 select b1.btext into v_dict_text from b1 where bid=p_bid; 14 select length(translate(v_dict_text, 'a0123456789', ' ')) into v_len from dual; 15 while v_index <= v_len+1 loop 16 SELECT regexp_substr(v_dict_text, '[[:alnum:]]+', 1, v_index ) INTO v_temp1 FROM dual; 17 SELECT TEXT INTO v_temp2 FROM A1 WHERE ID=v_temp1; 18 v_result:=concat(concat(v_result,v_temp2),','); 19 v_index := v_index+1; 20 end loop; 21 RETURN SUBSTR(v_result,0,length(v_result)-1); 22 end converter; 23 24 //测试结果，如下图，成功将字典值转为文本值 25 SELECT BID,converter(BID) TEXT from B1
- 结果如下
