这样创建自增长不是很好,因为在长到009的时候,就会到0010,而实际上应该是010,我应该如何创建呢?

Question

create table t_03_team(
team_id varchar2(50) primary key not null,
team_name varchar2(50),
team_local varchar(50)
);create sequence seq_t_03_team
start with 1
increment by 1
nomaxvalue
nocycle;insert into t_03_team values('00'||seq_t_03_team.nextval,'尤文图斯','罗马');
insert into t_03_team values('00'||seq_t_03_team.nextval,'AC米兰','罗马');
insert into t_03_team values('00'||seq_t_03_team.nextval,'罗马','罗马');
insert into t_03_team values('00'||seq_t_03_team.nextval,'帕尔玛','罗马');

Answer 1

insert into t_03_team values(lpad(seq_t_03_team.nextval,3,'0'),'尤文图斯','罗马'); 
insert into t_03_team values(lpad(seq_t_03_team.nextval,3,'0'),,'AC米兰','罗马'); 
insert into t_03_team values(lpad(seq_t_03_team.nextval,3,'0'),,'罗马','罗马'); 
insert into t_03_team values(lpad(seq_t_03_team.nextval,3,'0'),,'帕尔玛','罗马');

Answer 2

00是字符变不了,而且感觉你的序列最大值是999
insert into t_03_team values('00'||seq_t_03_team.nextval,'尤文图斯','罗马');
insert into t_03_team values('00'||seq_t_03_team.nextval,'AC米兰','罗马');
insert into t_03_team values('00'||seq_t_03_team.nextval,'罗马','罗马');
insert into t_03_team values('00'||seq_t_03_team.nextval,'帕尔玛','罗马'); create sequence seq_t_03_team
start with 001
increment by 1
nomaxvalue 999
nocycle; 
看看这样行不行?

Answer 3

１楼应该满足了，看来楼主也是球迷：）我喜欢juventus.当然全面支持意大利队．

Answer 4

2楼说的对,oracle有lpad/rpad函数 

Answer 5

00||seq_t_03_team.nextval  这个不变是不对的,而这么写是真的不变呀,我想让它变一下,010是对的,而到十的时候就是0010了,这是不对的,呵呵 

Answer 6

这个做法不错...
还可以写个function

Answer 7

insert into t_03_team values(substr('00'||seq_t_03_team.nextval,1,3),'尤文图斯','罗马'); 
insert into t_03_team values(substr('00'||seq_t_03_team.nextval,1,3),'AC米兰','罗马'); 
insert into t_03_team values(substr('00'||seq_t_03_team.nextval,1,3),'罗马','罗马'); 
insert into t_03_team values(substr('00'||seq_t_03_team.nextval,1,3),'帕尔玛','罗马'); 

Answer 8

我都快晕死了,我是这么运行的,你们看看对不对?
drop table t_03_team cascade constraint;
drop sequence seq_t_03_team;create table t_03_team
(
team_id varchar2(50) primary key,
team_name varchar2(50),
team_local varchar2(50)
);create sequence seq_t_03_team
start with 1
increment by 1
nomaxvalue
nocycle;
insert into t_03_team values(lpad(seq_t_03_team.nextval,3,'0'),'尤文图斯','罗马'); 
commit; 
insert into t_03_team values(lpad(seq_t_03_team.nextval,3,'0'),'AC米兰','罗马'); 
commit; 
insert into t_03_team values(lpad(seq_t_03_team.nextval,3,'0'),'罗马','罗马'); 
commit; 
insert into t_03_team values(lpad(seq_t_03_team.nextval,3,'0'),'帕尔玛','罗马'); 
commit;
insert into t_03_team values(lpad(seq_t_03_team.nextval,3,'0'),'尤文图斯','罗马'); 
commit; 
insert into t_03_team values(lpad(seq_t_03_team.nextval,3,'0'),'AC米兰','罗马'); 
commit; 
insert into t_03_team values(lpad(seq_t_03_team.nextval,3,'0'),'罗马','罗马'); 
commit; 
insert into t_03_team values(lpad(seq_t_03_team.nextval,3,'0'),'帕尔玛','罗马'); 
commit;
insert into t_03_team values(lpad(seq_t_03_team.nextval,3,'0'),'尤文图斯','罗马'); 
commit; 
insert into t_03_team values(lpad(seq_t_03_team.nextval,3,'0'),'AC米兰','罗马'); 
commit; 
insert into t_03_team values(lpad(seq_t_03_team.nextval,3,'0'),'罗马','罗马'); 
commit; 
insert into t_03_team values(lpad(seq_t_03_team.nextval,3,'0'),'帕尔玛','罗马'); 
commit;
insert into t_03_team values(lpad(seq_t_03_team.nextval,3,'0'),'1','1'); 
commit; 
insert into t_03_team values(lpad(seq_t_03_team.nextval,3,'0'),'2','2'); 
commit; 
insert into t_03_team values(lpad(seq_t_03_team.nextval,3,'0'),'3','3'); 
commit; 
insert into t_03_team values(lpad(seq_t_03_team.nextval,3,'0'),'4','4'); 
commit;
insert into t_03_team values(lpad(seq_t_03_team.nextval,3,'0'),'1','1'); 
commit; 
insert into t_03_team values(lpad(seq_t_03_team.nextval,3,'0'),'2','2'); 
commit; 
insert into t_03_team values(lpad(seq_t_03_team.nextval,3,'0'),'3','3'); 
commit; 
insert into t_03_team values(lpad(seq_t_03_team.nextval,3,'0'),'4','4'); 
commit;
insert into t_03_team values(lpad(seq_t_03_team.nextval,3,'0'),'1','1'); 
commit; 
insert into t_03_team values(lpad(seq_t_03_team.nextval,3,'0'),'2','2'); 
commit; 
insert into t_03_team values(lpad(seq_t_03_team.nextval,3,'0'),'3','3'); 
commit; 
insert into t_03_team values(lpad(seq_t_03_team.nextval,3,'0'),'4','4'); 
commit;
select * from t_03_team;你看看结果,真的是到020就没有了

Answer 9

TEAM_ID
---------------
TEAM_NAME
---------------
TEAM_LOCAL
---------------
001
尤文图斯
罗马002
AC米兰
罗马003
罗马
罗马004
帕尔玛
罗马005TEAM_ID
---------------
TEAM_NAME
---------------
TEAM_LOCAL
---------------
尤文图斯
罗马006
AC米兰
罗马007
罗马
罗马008
帕尔玛
罗马009
尤文图斯TEAM_ID
---------------
TEAM_NAME
---------------
TEAM_LOCAL
---------------
罗马010
AC米兰
罗马011
罗马
罗马012
帕尔玛
罗马013
1
1TEAM_ID
---------------
TEAM_NAME
---------------
TEAM_LOCAL
---------------014
2
2015
3
3016
4
4017
1
1
TEAM_ID
---------------
TEAM_NAME
---------------
TEAM_LOCAL
---------------
018
2
2019
3
3020
4
4021
1
1022TEAM_ID
---------------
TEAM_NAME
---------------
TEAM_LOCAL
---------------
2
2023
3
3024
4
4