select substr('prj-2009-301',1,instr('prj-2009-301','-',-1))||to_char(substr('prj-2009-301',instr('prj-2009-301','-',-1)+1)+1) from dual
楼主发表于:2009-08-05 18:46:04declare 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; http://topic.csdn.net/u/20090805/18/c2de467a-6cc5-48a4-8cd9-6e9be9df00a0.html?28722 哈哈借用一下
select substr('prj-2009-301',1,instr('prj-2009-301','-',-1))||to_char(substr('prj-2009-301',instr('prj-2009-301','-',-1)+1)+1) from dual也可以建个序列,然后在和字符来拼!
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;
http://topic.csdn.net/u/20090805/18/c2de467a-6cc5-48a4-8cd9-6e9be9df00a0.html?28722
哈哈借用一下
select substr('prj-2009-301',1,instr('prj-2009-301','-',-1))||to_char(substr('prj-2009-301',instr('prj-2009-301','-',-1)+1)+1) from dual也可以建个序列,然后在和字符来拼!
已写入 file afiedt.buf 1 create or replace function getnextflowno(vflowno varchar) return varchar
2 is
3 vn_flowno varchar2(20);
4 begin
5 select substr(vflowno,1,instr(vflowno,'-',-1))||to_char(substr(vflowno,instr(vflowno,'-',-1)+
6 return vn_flowno;
7* end;
SQL> /函数已创建。SQL> show error;
没有错误。
SQL> select getnextflowno('prj-2009-301') from dual;GETNEXTFLOWNO('PRJ-2009-301')
--------------------------------------------------------------------------------
prj-2009-302