CREATE OR REPLACE FUNCTION DATA_USER."FUJIAIPC_OLD" (in_ipc in varchar2, --输入老版本附加分类号
in_main in varchar2 --输入老版本主分类号作为备用的小类号
) return varchar2 is
ipc varchar2(4000);
strReturn varchar2(4000);
xl varchar2(444) := null; --前4位小类号
xl2 varchar2(4) := null; --备用前4位小类号
ipc1 varchar2(4000);
ipc2 varchar2(4000);
ipc3 varchar2(4000);
xl3 varchar2(10) := null;
i int;
j int;
k int;
m int;
n int;
g int;
begin
ipc := null;
ipc1 := null; --循环中截取段判断
ipc2 := null; --4位后4位
ipc3 := null; --最后6位
strReturn := '';
i := 1; --循环计数

-------------------------------------------------

ipc := trim(in_ipc);
if (substr(ipc, length(ipc) - 1) in (' ', ' ')) then
loop
ipc := substr(ipc, 1, length(ipc) - 1);
end loop;
end if;
xl3 := substr(in_main, 1, 4);
if (trim(ipc) is null or length(trim(ipc)) < 3) then
strReturn := null;
else
ipc1 := trim(replace(ipc, ':', '/'));

if (substr(ipc1, length(ipc1) - 1) in
(' ', ' ', '', '/', '(', ')', ',')) --处理末尾的特殊字符
then
loop
ipc1 := substr(ipc1, 1, length(ipc1) - 1);
end loop;
end if;

while (length(trim(ipc1)) > 1) loop
j := 0; --定位'/'
k := 0; --括号位置
m := 0; --空格位置
n := 0; --逗号位置
g := 0; --连续未分隔的定位
while (substr(ipc1, 1, 1) in ('', '/', '(', ')', ' ', ',')) loop
--首字母特殊字符去除处理
ipc1 := substr(ipc1, 2);
end loop;
xl := substr(ipc1, 1, 4);
--判断前4位小类号是否规范，规范则存xl2备用，不规范的则取xl2
if (substr(xl, 1, 1) between 'A' and 'Z' AND
substr(xl, 2, 2) between '00' and '99' AND
substr(xl, 4, 1) between 'A' and 'Z') THEN
--dbms_output.put_line('4位规范');
xl2 := xl;
ipc1 := substr(ipc1, 5);
else
if (trim(xl2) is null) --不规范，并且xl2无值，则取主分类号的小类
then
--dbms_output.put_line('前4位不规范的直接用xl2');
xl2 := xl3;
end if;
end if;
j := instr(ipc1, '/');
--dbms_output.put_line('取斜杠位置:' || j);
ipc2 := lpad(trim(substr(ipc1, 1, j - 1)), 4, '0'); --4位左补零
--dbms_output.put_line('取斜杠位置ipc2:' || ipc2);
ipc1 := trim(substr(ipc1, j + 1));
--dbms_output.put_line('取斜杠位置ipc1:' || ipc1);
--判断是否有无分隔符的分类号,以第一位为大写字母为准
for l in 1 .. 6 loop
if (substr(ipc1, l, 1) between 'A' and 'Z') then
g := l;
goto luohao;
end if;
end loop;
<>

--dbms_output.put_line('无分隔');
k := instr(ipc1, ')');
m := instr(ipc1, ' ');
n := instr(ipc1, ',');
--dbms_output.put_line('g: ' || g);
--dbms_output.put_line('k: ' || k);
--dbms_output.put_line('m: ' || m);
--dbms_output.put_line('n: ' || n);
if (((g < k and k <> 0) or (g < m and m <> 0) or (g < n and n <> 0) or
(g > 0 and m = 0 and n = 0 and k = 0)) and g > 0) then
--dbms_output.put_line('无分隔');
ipc3 := rpad(substr(ipc1, 1, g - 1), 6, '0'); --6位右补零
ipc1 := substr(ipc1, g);
else
if ((k = 0 and m <> 0 and n <> 0) or (k <> 0 and n = 0 and m <> 0) or
(m = 0 and n <> 0 and k <> 0)) then
--dbms_output.put_line('1个为0');
i := (case when k <> 0 then case when m <> 0 then least(k, m) else least(k, n) end else least(m, n) end);
ipc3 := rpad(substr(ipc1, 1, i - 1), 6, '0'); --6位右补零
ipc1 := substr(ipc1, i);
else
if ((k = 0 and m = 0 and n <> 0) or (k = 0 and n = 0 and m <> 0) or
(m = 0 and n = 0 and k <> 0)) then
--dbms_output.put_line('两个为0');
ipc3 := rpad(substr(ipc1, 1, greatest(k, m, n) - 1), 6, '0'); --6位右补零
ipc1 := trim(substr(ipc1, greatest(k, m, n)));
else
if (k = 0 and m = 0 and n = 0) then
--dbms_output.put_line('3个为0');
ipc3 := rpad(substr(ipc1, 1), 6, '0'); --6位右补零
ipc1 := '';
else
--dbms_output.put_line('3个不为0');
if (k < m and k < n) then
--dbms_output.put_line('后面跟括号');
ipc3 := rpad(substr(ipc1, 1, k - 1), 6, '0'); --6位右补零
ipc1 := trim(substr(ipc1, k));
else
if (m < k and m < n) then
--dbms_output.put_line('后面跟空格');
ipc3 := rpad(substr(ipc1, 1, m - 1), 6, '0'); --6位右补零
ipc1 := trim(substr(ipc1, m));
else
if (n < k and n < m) then
--dbms_output.put_line('后面跟逗号');
ipc3 := rpad(substr(ipc1, 1, n - 1), 6, '0'); --6位右补零
ipc1 := trim(substr(ipc1, n));
end if;
end if;
end if;
end if;
end if;
end if;
end if;
----------------------------------
--dbms_output.put_line('ipc3:'||ipc3);

strReturn := strReturn || ',' || xl2 || ipc2 || ipc3;
end loop;
end if;
-------------------------------------------------------
strReturn := substr(strReturn, 2);
if (length(strReturn) - instr(strReturn, ',', -1) < 14) then
strReturn := substr(strReturn, 1, instr(strReturn, ',', -1) - 1);
end if;
return strReturn;
--exception when others then
--dbms_output.put_line('M：'||m||' ipc1:'||ipc1||' N: '||n||' g: '||g);
end;