比方一个表“CD02”,字段“DmCod”,“DmCpt”,其中“DmCod”记录类别代码,“DmCpt”记录相关描述。如:
DmCod DmCpt
01 阿
02 吧
03 才
我要编写一个Oracle的自定义函数,实现,当我输入的代码为“01,02,03”时,返回的结果集为:“阿,吧,才”想了半天也没有想出来方法,请高人指点。
DmCod DmCpt
01 阿
02 吧
03 才
我要编写一个Oracle的自定义函数,实现,当我输入的代码为“01,02,03”时,返回的结果集为:“阿,吧,才”想了半天也没有想出来方法,请高人指点。
select DmCpt into RetDmCpt from CD02 where DmCod = iDmCod;
return RetDmCpt;
--看一下
:
SQL> create table cd02(dmcod varchar2(8),dmcpy varchar2(8));Table createdSQL> insert into cd02 values('01','阿');1 row insertedSQL> insert into cd02 values('02','吧');1 row insertedSQL> insert into cd02 values('03','才');1 row insertedSQL> commit;Commit completeSQL> select * from cd02;DMCOD DMCPY
-------- --------
01 阿
02 吧
03 才
SQL>
SQL> CREATE OR REPLACE FUNCTION f_cd02(v1 IN varchar2,
2 v2 IN varchar2,
3 v3 IN varchar2) RETURN varchar2 IS
4 v_str varchar2(80);
5 cursor c1 is
6 select dmcpy from cd02 where dmcod = v1;
7 cursor c2 is
8 select dmcpy from cd02 where dmcod = v2;
9 cursor c3 is
10 select dmcpy from cd02 where dmcod = v3;
11 l_v1 varchar2(8);
12 l_v2 varchar2(8);
13 l_v3 varchar2(8);
14 begin
15 open c1;
16 fetch c1
17 into l_v1;
18 open c2;
19 fetch c2
20 into l_v2;
21 open c3;
22 fetch c3
23 into l_v3;
24 v_str := l_v1 || l_v2 || l_v3;
25 return v_str;
26 end;
27 /Function createdSQL> select f_cd02('01','02','03') from dual;F_CD02('01','02','03')
--------------------------------------------------------------------------------
阿吧才SQL>
SQL> SELECT WMSYS.WM_CONCAT(DMCPT) MY_STR
2 FROM TABLE_NAME TT
3 WHERE DMCOD IN ('01','02','03');MY_STR
--------------------------------------------------------------------------------
AA,BA,CAI
len INTEGER := 1;
lastpos INTEGER := 1 - len;
pos INTEGER;
num INTEGER;
i INTEGER := 1;
ret varchar2varray := varchar2varray(NULL);
strsql varchar2(200);
re_value varchar2(80);
/**自定义split函数,将指定的字符串按指定的标志符分割成字符数组*/
BEGIN
LOOP
pos := INSTR(v1, ',', lastpos + len);
IF pos > 0 THEN
--found
num := pos - (lastpos + len);
ELSE
--not found
num := LENGTH(v1) + 1 - (lastpos + len);
END IF;
IF i > ret.LAST THEN
ret.EXTEND;
END IF;
ret(i) := SUBSTR(v1, lastpos + len, num);
--select (re_value || ',' || (select DmCpt from CD02 where DmCod = ret(i))) into re_value from dual;
strsql := 'select ('''||re_value||'''||'',''|| (select dmcpt from '|| v2 ||' where dmcod = '||ret(i)||')) from dual';
--dbms_output.put_line(strsql);
execute immediate strsql into re_value;
EXIT WHEN pos = 0;
lastpos := pos;
i := i + 1;
END LOOP;
select ltrim(re_value,',') into re_value from dual;
RETURN re_value;
END;
PS:v1(代码)
v2(代码表名称)
终于做出来了!
RETURN varchar2
IS
out_str varchar2(4000);
BEGIN
execute immediate 'SELECT WMSYS.WM_CONCAT(DMCPT) MY_STR FROM TABLE_NAME TT
WHERE DMCOD IN '||'(''01'',''02'',''03'')' into out_str ;
RETURN out_str ;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
RETURN null;
WHEN OTHERS
THEN
RETURN -1;
END get_str;
/