a demo function of oracle for AKing

you'd better use an IDE to debug this function,such as plsql developer.

and if any problems ,info me,i will answer you asap.

tomorrow i will go to hongkong.

---create demo data and insert into sample data.
---according to your description,the a.a_value contains the valuelist,and in the table B,the B.b_id
---is corresponding to the a._value valueList.
create table A(a_id number(10),a_value varchar2(200));
create table B(b_id number(10),b_value varchar2(200));

insert into A values(1,'11,22,33,44');
insert into A values(2,'33,44,22,11');

insert into B values(11,'xx1');
insert into B values(22,'xx2');
insert into B values(33,'xx3');
insert into B values(44,'xx4');
commit;

---this function need your take more exception case into account.eg. the valuelist contains special case. '11,,,,' or ',222,,,,' etc.
---and you need more test
---the function name: ConvertValue.
---the funciont description:if table A.b_value is 11,22,33,44.
--- and table B.a_id contains 11,22,33,44,and has corresponding value.
--- use this function we can get the value list in A.b_value corresponding value in table B
create or replace function ConvertValue(inStr in varchar2) return varchar2 is
Result varchar2(200);
tempstr varchar2(200);
lastPos number(10);
element varchar2(10);
element_value varchar2(10);
begin


---initialize the value
tempstr := instr;
Result := '';
lastPos :=1;

for i in 1..(length(tempstr)) loop

if(substr(tempstr,i,1)=',') then
element := substr(tempstr,lastPos,i-lastPos);
lastPos := i+1;

begin ---get the value from table B,use rownum<2 in case of preventing two many rows exception
select b_value into element_value
from B
where b_id = element
and rownum<2;
exception ---if no data or other exception use the previous value,no need change.
when others then
element_value := element;
end;

Result := Result||element_value||','; ---add to the return value.

end if;

end loop;

---the last elment of the valuelist.
element := substr(tempstr,lastPos);
begin
select b_value into element_value
from B
where b_id = element
and rownum<2;
exception
when others then
element_value := element;
end;

Result := Result||element_value;

return(Result);
end ConvertValue;

---then you can query from the table,just use the function.
select a.*,ConvertValue(a.a_value) from a

[@more@]