求助，写一个oracle函数。

Question

本帖最后由 hero1979 于 2010-02-21 15:53:24 编辑

Answer 1

自己解决，呵呵。create or replace function FN_GETALLNAME(in_ids in varchar2) return varchar2 is
  Result varchar2(4000);
  v_tempstr varchar2(4000);
  v_strsql varchar2(4000);
  v_name varchar2(100);
  v_allname varchar2(4000);
  type myCursor is ref cursor;
  out_result myCursor;
begin
  select replace(in_ids,':',',') into v_tempstr from dual;
  v_strsql:= 'select NAMEfrom table2 where id in('||v_tempstr||')';  OPEN out_result FOR v_strsql ;
  loop
       fetch out_result into v_name;
       exit when out_result%NOTFOUND;
       if(v_allname is not null or v_allname<>'') then
           v_allname:=v_allname||'：'||v_name;
       else v_allname:=v_name;
       end if;
  end loop;
  close out_result;  Result:=v_allname;
  return(Result);
end FN_GETALLNAME;

Answer 2

17:17:15 scott@TUNGKONG> select * from table1;ID                         NUMS
-------------------- ----------
300:301:302                   1
300:302                       1
300:301:400                   1
302:303:400                   1
301:303                       1
400                           3已选择6行。已用时间:  00: 00: 00.04
17:17:22 scott@TUNGKONG> select * from table2;ID                   NAME
-------------------- --------------------
300                  AAAA
301                  BBBB
302                  CCCC
303                  DDDD
400                  EEEE已用时间:  00: 00: 00.00
17:17:24 scott@TUNGKONG> CREATE OR REPLACE FUNCTION fun_test(tmp varchar2)
17:17:30   2  RETURN varchar2
17:17:30   3  IS
17:17:30   4   rlt varchar2(100);
17:17:30   5   CURSOR myCursor IS SELECT id,name FROM table2;
17:17:30   6  BEGIN
17:17:30   7   rlt := tmp;
17:17:30   8   FOR cur IN myCursor LOOP
17:17:30   9   rlt := REPLACE(rlt,cur.id,cur.name);
17:17:30  10   END LOOP;
17:17:30  11   RETURN rlt;
17:17:30  12  END;
17:17:30  13  /函数已创建。已用时间:  00: 00: 00.01
17:17:31 scott@TUNGKONG> select fun_test(t.id)as NAME,t.nums from table1 t;NAME                       NUMS
-------------------- ----------
AAAA:BBBB:CCCC                1
AAAA:CCCC                     1
AAAA:BBBB:EEEE                1
CCCC:DDDD:EEEE                1
BBBB:DDDD                     1
EEEE                          3已选择6行。已用时间:  00: 00: 00.00

Answer 3

SQL> select * from table3;
 
ID                                                      NUMS
-------------------- ---------------------------------------
300:301:302                                                1
300:301:400                                                1
300:302                                                    1
301:303                                                    1
302:303:400                                                1
400                                                        1
 
6 rows selected
 
SQL> select * from table4;
 
ID                   NAME
-------------------- --------------------
300                  AAAA
301                  BBBB
302                  CCCC
303                  DDDD
400                  EEEE
 
SQL> 
SQL> select a.id, WMSYS.WM_CONCAT(b.name) id2
  2    from (select a.*, regexp_substr(a.id, '[^:]+', 1, b.n) id1
  3            from table3 a,
  4                 (select rownum n from dual connect by rownum < 100) b
  5           where length(regexp_replace(a.id, '[^:]+')) + 1 >= b.n
  6           order by 1, 3) a,
  7         table4 b
  8   where a.id1 = b.id
  9   group by a.id
 10   order by 1
 11  /
 
ID                   ID2
-------------------- --------------------------------------------------------------------------------
300:301:302          AAAA,BBBB,CCCC
300:301:400          AAAA,EEEE,BBBB
300:302              AAAA,CCCC
301:303              BBBB,DDDD
302:303:400          CCCC,DDDD,EEEE
 
SQL>