type tbtypename is table of type index by binary_integer 用index_by表,或者可变数组
use javascript function can to soulte the problem取前四位,然后分隔,然后与第二个字符串的四位进行比较,两重循环,可以搞定
create or replace function f_con_str(s1 varchar2,s2 varchar2) return varchar2 as v_temp varchar2(10); v_return varchar2(400); v_s1 varchar2(200); begin v_return:=s2; v_s1:=s1; loop v_s1:=rtrim(ltrim(v_s1,','),','); exit when instr(v_s1,',')<1 or length(v_s1)<4; v_temp:=substr(v_s1,1,instr(v_s1,',')); if instr(s2,v_temp)<1 then v_return:=v_return||v_temp; end if; v_s1:=substr(v_s1,instr(v_s1,',')); end loop; return v_return; end f_con_str;16:44:54 SQL> select f_con_str(',1212,1211,1210,1209,1208,1207,1206,1205,1204,1203,1202,1201,',- 16:53:15 > ',9999,9998,1200,1100,1003,1002,1000,1212,1211,1210,1209,1208,1207,1205,- 16:53:17 > 1204,1203,1202,1201,') from dual;F_CON_STR(',1212,1211,1210,1209,1208,1207,1206,1205,1204,1203,1202,1201,',',9999 -------------------------------------------------------------------------------- ,9999,9998,1200,1100,1003,1002,1000,1212,1211,1210,1209,1208,1207,1205, 1204,120 3,1202,1201,1206, 已用时间: 00: 00: 00.31 16:53:19 SQL>
刚想写,bzszp(www.bzszp.533.net)兄已经帖出来了.呵呵...
create or replace function f_addstr(str1 varchar2,str2 varchar2) return varchar2 is v_Str1 varchar2(400); v_temp varchar2(400); v_str varchar2(400):='';begin v_Str1 := str1||substr(str2,2);
while length(v_Str1) > 4 loop v_temp := substr(v_Str1,1,instr(v_Str1,',',2) - 1); v_Str1 := substr(v_Str1,instr(v_str1,',',2)); if instr(v_str,v_temp) is null or instr(v_str,v_temp) < 1 then v_str := v_str||v_temp; end if; end loop; v_str := v_str||v_str1; return v_str;end f_addstr;
用index_by表,或者可变数组
as
v_temp varchar2(10);
v_return varchar2(400);
v_s1 varchar2(200);
begin
v_return:=s2;
v_s1:=s1;
loop
v_s1:=rtrim(ltrim(v_s1,','),',');
exit when instr(v_s1,',')<1 or length(v_s1)<4;
v_temp:=substr(v_s1,1,instr(v_s1,','));
if instr(s2,v_temp)<1 then
v_return:=v_return||v_temp;
end if;
v_s1:=substr(v_s1,instr(v_s1,','));
end loop;
return v_return;
end f_con_str;16:44:54 SQL> select f_con_str(',1212,1211,1210,1209,1208,1207,1206,1205,1204,1203,1202,1201,',-
16:53:15 > ',9999,9998,1200,1100,1003,1002,1000,1212,1211,1210,1209,1208,1207,1205,-
16:53:17 > 1204,1203,1202,1201,') from dual;F_CON_STR(',1212,1211,1210,1209,1208,1207,1206,1205,1204,1203,1202,1201,',',9999
--------------------------------------------------------------------------------
,9999,9998,1200,1100,1003,1002,1000,1212,1211,1210,1209,1208,1207,1205, 1204,120
3,1202,1201,1206,
已用时间: 00: 00: 00.31
16:53:19 SQL>
is
v_Str1 varchar2(400);
v_temp varchar2(400);
v_str varchar2(400):='';begin
v_Str1 := str1||substr(str2,2);
while length(v_Str1) > 4 loop
v_temp := substr(v_Str1,1,instr(v_Str1,',',2) - 1);
v_Str1 := substr(v_Str1,instr(v_str1,',',2));
if instr(v_str,v_temp) is null or instr(v_str,v_temp) < 1 then
v_str := v_str||v_temp;
end if;
end loop;
v_str := v_str||v_str1;
return v_str;end f_addstr;