oracle数据转换方法，将字典表的id值以文本形式展示

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