declare a varchar(20);
begin
a:='asf001';
a:=System.Getcharadd1(a);
dbms_output.put_line('最后结果:'||a);
end;
运行上面的代码 结果就是 字符自动加1 比如
001 运行函数System.Getcharadd1('001');结果就是002
199 运行函数System.Getcharadd1('001');结果就是200
js199 运行函数System.Getcharadd1('001');结果就是js002
js001a 运行函数System.Getcharadd1('001');结果就是js001b
感觉坛子里面的朋友帮忙指导我 才完成这个函数 所以源代码发上来希望能帮上需要这个功能的朋友
create or replace
FUNCTION GETCHARADD1(cDjbh1 varchar2)
RETURN VARCHAR2 AS
i INTEGER;
j INTEGER;
Hz Varchar2(550);
pcDjbh_tmp Varchar2(250);
cDjbh Varchar2(250);
cRight Varchar2(250);
cSubstr Varchar2(250);
cSubstrold Varchar2(250);
BEGIN
cDjbh:=trim(cDjbh1);
pcDjbh_tmp:='';
cSubstr:='0001';
cSubstrold:='*';
i:=1;
cRight:='';
While i<=Length(cDjbh)
loop
j:=0;
cSubstr:=Substr(cDjbh,Length(cDjbh)-i+1,1);
if Ascii(cSubstrold)=122 And Not (Ascii(cSubstr)>=97 And Ascii(cSubstr)<=122) and j=0 then --头个是9而当前这个不在'a'到'z'之间
cRight:='a'||cRight;
j:=1;
Exit ;
end if;
if Ascii(cSubstrold)=90 And Not (Ascii(cSubstr)>=65 And Ascii(cSubstr)<=97) and j=0 then --头个是9而当前这个不在'A'到'Z'之间
cRight:='A'||cRight;
j:=1;
Exit ;
end if;
if Ascii(cSubstrold)=57 And Not (Ascii(cSubstr)>=48 And Ascii(cSubstr)<=57) and j=0 then --头个是9而当前这个不在'0'到'9'之间
cRight:='1'||cRight;
j:=1;
Exit ;
end if;
if (Ascii(cSubstr)>=97 And Ascii(cSubstr)<=121) and j=0 then --头个是9而当前这个不在'0'到'9'之间
cSubstr:=Chr(Ascii(cSubstr)+1);
j:=1;
Exit ;
end if;
if (Ascii(cSubstr)>=65 And Ascii(cSubstr)<=89) and j=0 then --头个是9而当前这个不在'0'到'9'之间
cSubstr:=Chr(Ascii(cSubstr)+1);
j:=1;
Exit ;
end if;
if (Ascii(cSubstr)>=48 And Ascii(cSubstr)<=56) and j=0 then --头个是9而当前这个不在'0'到'9'之间
cSubstr:=Chr(Ascii(cSubstr)+1);
j:=1;
Exit ;
end if;
If Ascii(cSubstr)=122 and j=0 then --头个是9而当前这个不在'0'到'9'之间
cRight:='a'||cRight;
j:=1;
End if;
If Ascii(cSubstr)=90 and j=0 then --头个是9而当前这个不在'0'到'9'之间
cRight:='A'||cRight;
j:=1;
End if;
If Ascii(cSubstr)=57 and j=0 then --头个是9而当前这个不在'0'到'9'之间
j:=1;
If i=Length(cDjbh) then
cRight:='10'||cRight;
cSubstr:='';
Else
cRight:='0'||cRight;
end if;
End if ;
If j=0 then
cSubstr:=cSubstr||'1';
Exit ;
end if;
i:=i+1;
cSubstrold:=cSubstr;
End loop;
if i>Length(cDjbh) then
i:=Length(cDjbh);
end if;
pcDjbh_tmp:=Substr(cDjbh,1,Length(cDjbh)-i)||cSubstr||trim(cRight);
return pcDjbh_tmp;
END GETCHARADD1;
begin
a:='asf001';
a:=System.Getcharadd1(a);
dbms_output.put_line('最后结果:'||a);
end;
运行上面的代码 结果就是 字符自动加1 比如
001 运行函数System.Getcharadd1('001');结果就是002
199 运行函数System.Getcharadd1('001');结果就是200
js199 运行函数System.Getcharadd1('001');结果就是js002
js001a 运行函数System.Getcharadd1('001');结果就是js001b
感觉坛子里面的朋友帮忙指导我 才完成这个函数 所以源代码发上来希望能帮上需要这个功能的朋友
create or replace
FUNCTION GETCHARADD1(cDjbh1 varchar2)
RETURN VARCHAR2 AS
i INTEGER;
j INTEGER;
Hz Varchar2(550);
pcDjbh_tmp Varchar2(250);
cDjbh Varchar2(250);
cRight Varchar2(250);
cSubstr Varchar2(250);
cSubstrold Varchar2(250);
BEGIN
cDjbh:=trim(cDjbh1);
pcDjbh_tmp:='';
cSubstr:='0001';
cSubstrold:='*';
i:=1;
cRight:='';
While i<=Length(cDjbh)
loop
j:=0;
cSubstr:=Substr(cDjbh,Length(cDjbh)-i+1,1);
if Ascii(cSubstrold)=122 And Not (Ascii(cSubstr)>=97 And Ascii(cSubstr)<=122) and j=0 then --头个是9而当前这个不在'a'到'z'之间
cRight:='a'||cRight;
j:=1;
Exit ;
end if;
if Ascii(cSubstrold)=90 And Not (Ascii(cSubstr)>=65 And Ascii(cSubstr)<=97) and j=0 then --头个是9而当前这个不在'A'到'Z'之间
cRight:='A'||cRight;
j:=1;
Exit ;
end if;
if Ascii(cSubstrold)=57 And Not (Ascii(cSubstr)>=48 And Ascii(cSubstr)<=57) and j=0 then --头个是9而当前这个不在'0'到'9'之间
cRight:='1'||cRight;
j:=1;
Exit ;
end if;
if (Ascii(cSubstr)>=97 And Ascii(cSubstr)<=121) and j=0 then --头个是9而当前这个不在'0'到'9'之间
cSubstr:=Chr(Ascii(cSubstr)+1);
j:=1;
Exit ;
end if;
if (Ascii(cSubstr)>=65 And Ascii(cSubstr)<=89) and j=0 then --头个是9而当前这个不在'0'到'9'之间
cSubstr:=Chr(Ascii(cSubstr)+1);
j:=1;
Exit ;
end if;
if (Ascii(cSubstr)>=48 And Ascii(cSubstr)<=56) and j=0 then --头个是9而当前这个不在'0'到'9'之间
cSubstr:=Chr(Ascii(cSubstr)+1);
j:=1;
Exit ;
end if;
If Ascii(cSubstr)=122 and j=0 then --头个是9而当前这个不在'0'到'9'之间
cRight:='a'||cRight;
j:=1;
End if;
If Ascii(cSubstr)=90 and j=0 then --头个是9而当前这个不在'0'到'9'之间
cRight:='A'||cRight;
j:=1;
End if;
If Ascii(cSubstr)=57 and j=0 then --头个是9而当前这个不在'0'到'9'之间
j:=1;
If i=Length(cDjbh) then
cRight:='10'||cRight;
cSubstr:='';
Else
cRight:='0'||cRight;
end if;
End if ;
If j=0 then
cSubstr:=cSubstr||'1';
Exit ;
end if;
i:=i+1;
cSubstrold:=cSubstr;
End loop;
if i>Length(cDjbh) then
i:=Length(cDjbh);
end if;
pcDjbh_tmp:=Substr(cDjbh,1,Length(cDjbh)-i)||cSubstr||trim(cRight);
return pcDjbh_tmp;
END GETCHARADD1;
SELECT str,REPLACE(src_chars,'@',lpad(src_num+1,LENGTH(src_num),0)) str_next
FROM (SELECT str,
REGEXP_REPLACE(str,'([a-zA-Z]*)([0-9]{1,})([a-zA-Z]*)','\1@\3') src_chars,
REGEXP_REPLACE(str,'([a-zA-Z]*)([0-9]{1,})([a-zA-Z]*)','\2') src_num
FROM (SELECT '001' str FROM dual
UNION
SELECT '199' str FROM dual
UNION
SELECT 'js199' str FROM dual
UNION
SELECT 'js001a' str FROM dual
)
)
FROM (SELECT str,
REGEXP_REPLACE(str,'([a-zA-Z]*)([0-9]{1,})([a-zA-Z]*)','\1@\3') src_chars,
REGEXP_REPLACE(str,'([a-zA-Z]*)([0-9]{1,})([a-zA-Z]*)','\2') src_num
FROM (SELECT '001' str FROM dual
UNION
SELECT '199' str FROM dual
UNION
SELECT 'js199' str FROM dual
UNION
SELECT 'js001a' str FROM dual
UNION
SELECT 'js999b' str FROM dual
)
)
STR STR_NEXT
001 2
199 200
js001a js2a
js199 js200
js999b js1000b
SELECT str,REPLACE(src_chars,'@',lpad(src_num+1,LENGTH(src_num)+LENGTH(src_num+1)-LENGTH(to_number(src_num)),0)) str_next
FROM (SELECT str,
REGEXP_REPLACE(str,'([a-zA-Z]*)([0-9]{1,})([a-zA-Z]*)','\1@\3') src_chars,
REGEXP_REPLACE(str,'([a-zA-Z]*)([0-9]{1,})([a-zA-Z]*)','\2') src_num
FROM (SELECT '001' str FROM dual
UNION
SELECT '199' str FROM dual
UNION
SELECT 'js199' str FROM dual
UNION
SELECT 'js001a' str FROM dual
UNION
SELECT 'js999b' str FROM dual
)
)