何时使用序列
:
oracle实现表中某一列自动递增的,如1,2,3,4,5,6.......以后就自动加1了
创建序列
(S_RESOURCE_DATA_TEST_SEQ)
-- Create sequence
create sequence S_RESOURCE_DATA_TEST_SEQ
minvalue 1
maxvalue 99999999999999
start with 1
increment by 1
cache 20;
//NOMAXvalue -- 不设置最大值//NOCYCLE -- 一直累加,不循环
删除序列
(S_RESOURCE_DATA_TEST_SEQ)
DROP SEQUENCE S_RESOURCE_DATA_TEST_SEQ;
修改序列
ALTER SEQUENCE 序列名
[INCREMENT BY n]
[{MAXVALUE/ MINVALUE n|NOMAXVALUE}]
[{CYCLE|NOCYCLE}]
[{CACHE n|NOCACHE}];
*不能修改序列的初始值
查询序列
(1)查询下一个将要使用的序列
select
SEQ_NAME.nextval
from
dual
(2)查询当前序列
select
SEQ_NAME.currval
from
dual
插入数据
insert into 表名(id,name)values(
S_RESOURCE_DATA_TEST_SEQ.Nextval,'sequence 插入测试');
在java中的使用
public
int insertDataReturnKeyByGetNextVal()
throws Exception {
Connection conn = getConnection();
String vsql =
"select seq_t1.nextval as id from dual";
PreparedStatement pstmt =(PreparedStatement)conn.prepareStatement(vsql);
ResultSet rs=pstmt.executeQuery();
rs.next();
int id=rs.getInt(1);
rs.close();
pstmt.close();
vsql=
"insert into t1(id) values(?)"
;
pstmt =(PreparedStatement)conn.prepareStatement(vsql);
pstmt.setInt(1, id);
pstmt.executeUpdate();
System.out.print(
"id:"
+id);
return id;
}