create or replace function GenID(fl varchar2)
return varchar2 is
result varchar2;
curNo varchar2 ;begin result:='';
select currentNo into curNo from basbillcfg where firstLetter=fl;
if( curno is null) then
update Basbillcfg set CurrentNo=fl+to_char(sysdate,'yyyymmdd')+lpad('1',serlen,'0') where firstletter=fl;
else
if( substr(currentNo,2,8)=to_char(sysdate,'yyyymmdd') ) then
select substr(currentNo,0,10) as s1 ,lpad(tochar(to_int( substr(crurentno,length(currentNo)-serlen,Serlen))+1),serlen,'0') as s2 ,s1||s2 into currentNo from BasBillCfg where firstLetter =fl ;
update BasBillcfg set CurrentNo =curNo where firstLetter =fl;
result:=curNo;
return;
else
select substr(currentNo,0,10) as s1,lpad('1',serlen,'0') as s2,s1||s2 into currentNo from BasBillCfg where firstLetter =fl ; update BasBillcfg set CurrentNo =curNo where firstLetter =fl;
result:=curNo;
return;
end if; return(result);end GenID;oracle函数
return varchar2 is
result varchar2;
curNo varchar2 ;begin result:='';
select currentNo into curNo from basbillcfg where firstLetter=fl;
if( curno is null) then
update Basbillcfg set CurrentNo=fl+to_char(sysdate,'yyyymmdd')+lpad('1',serlen,'0') where firstletter=fl;
else
if( substr(currentNo,2,8)=to_char(sysdate,'yyyymmdd') ) then
select substr(currentNo,0,10) as s1 ,lpad(tochar(to_int( substr(crurentno,length(currentNo)-serlen,Serlen))+1),serlen,'0') as s2 ,s1||s2 into currentNo from BasBillCfg where firstLetter =fl ;
update BasBillcfg set CurrentNo =curNo where firstLetter =fl;
result:=curNo;
return;
else
select substr(currentNo,0,10) as s1,lpad('1',serlen,'0') as s2,s1||s2 into currentNo from BasBillCfg where firstLetter =fl ; update BasBillcfg set CurrentNo =curNo where firstLetter =fl;
result:=curNo;
return;
end if; return(result);end GenID;oracle函数
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货