oracle 根据年月日生成编号

来源：互联网发布：淘宝商城首页编辑：程序博客网时间：2024/06/16 23:34

Create Table test
(
Id Number(32) Primary Key,
createDateTime Date
)
Delete From test;
Commit;
Insert Into test Values(2011120901,sysdate);
Insert Into test Values(2011120902,sysdate);
Insert Into test Values(2011120903,sysdate);
Insert Into test Values(2011120904,sysdate);
Insert Into test Values(2011120905,sysdate);
Insert Into test Values(2011120906,sysdate);
Insert Into test Values(2011120907,sysdate);
Insert Into test Values(2011120908,sysdate);
Insert Into test Values(2011120909,sysdate);
Insert Into test Values(20111209010,sysdate);
Insert Into test Values(201112090100,sysdate);
Commit;

Select Case When nvl(max(Id),0) =0 Then concat(To_char(Sysdate,'yyyyMMdd'),'01')
Else concat(concat(to_char(Sysdate,'yyyymmdd'),'0'),to_char(To_number(substr(to_char(Max(Id)),length(to_char(Sysdate,'yyyymmdd'))+1,length(to_char(Max(Id)))))+1))
End As ids1
from test
Where to_char(createDateTime,'yyyy-mm-dd')=to_char(Sysdate,'yyyy-MM-dd');