奉贤网站建设上海站霸,模板下载网站源码,搜索营销案例,厦门旅游网站建设目的1. Sequence 定义
在Oracle中可以用SEQUENCE生成自增字段。Sequence序列是Oracle中用于生成数字序列的对象#xff0c;可以创建一个唯一的数字作为主键。
2. 为什么要用 Sequence
你可能有疑问为什么要使用序列#xff1f;
不能使用一个存储主键的表并每次递增吗#xf…1. Sequence 定义
在Oracle中可以用SEQUENCE生成自增字段。Sequence序列是Oracle中用于生成数字序列的对象可以创建一个唯一的数字作为主键。
2. 为什么要用 Sequence
你可能有疑问为什么要使用序列
不能使用一个存储主键的表并每次递增吗或者将列设置为AUTO INCREMENT
如果使用一个表来储存主键值的话也许需要这样做
创建一个新表来储存单一值。每次插入新值到你想要插入的表时在新表中使用该值。然后更新新表将其加1。这些逻辑都可以加入进存储过程。
然而这种方法存在以下几个问题
如果你的事务中途失败可能会得到重复的数据插入的数据有一个ID但新表没有增加。这可以通过先递增然后插入来部分避免但它仍然有中途失败的风险。更新表将对其进行锁定这可能会导致多个事务或用户出现问题。如果有一个用户这可能是可以的但如果程序有两个或多个用户那么这可能会导致问题。创建和维护一个表以及执行此操作的代码是繁重的工作。
Sequence是一个更好的方法原因如下
创建更简单。使用起来更简单。没有死锁以及导致其他事务出现问题的风险。
3. 创建CREATESequence
CREATE SEQUENCE [schema_name.]{sequence_name} -- schema_name为将存储序列的模式名sequence_name 为自定义名称START WITH n -- n 为序列的初始值默认为1INCREMENT BY n -- n 为序列步长序列增加的幅度默认为1如果是负则按此步长递减[MINVALUE n | NOMINVALUE ] -- 如果序列递减定义序列生成器能产生的最小值,默认为1[MAXVALUE n | NOMAXVALUE ] -- 定义序列生成器能产生的最大值,默认无限制1e28 - 1[CACHE n | NOCACHE] -- value 是存放序列的内存块大小默认20。对序列进行内存缓存可以改善序列性能。[ORDER | NOORDER] -- 表示序列号是按照请求的顺序生成的。如果使用时间戳的序列号会有用。[CYCLE | NOCYCLE] -- 值达到限制值后是否循环如果不循环达到限制值后继续产生新值会发生错误 ;
使用缓存创建序列的缺点是如果发生系统故障所有未使用的缓存序列值都将“丢失”。这会在指定的序列值中产生“间隙”。当系统恢复时Oracle将从序列中停止的位置缓存新数字忽略所谓的“丢失”序列值。要恢复丢失的序列值您可以始终执行ALTER sequence命令将计数器重置为正确的值。
4. 使用 Sequence
CURRVAL和NEXTVAL可以在以下几个地方使用
INSERT语句的VALUES子句。SELECT语句的SELECT列表UPDATE语句的UPDATE子句
它们不能在以下地方使用
CHECK约束的条件视图查询实体化视图查询子查询带有DISTINCT关键字、GROUP BY子句或ORDER BY子句的SELECT语句使用UNION、INTERSECT或MINUS运算符与另一个SELECT语句组合的SELECT语句SELECT语句的WHERE子句CREATE TABLE或ALTER TABLE语句中列的DEFAULT值
最后一点意味着您不能使用DEFAULT关键字的序列将列设置为自动递增。有一种方法可以创建自动递增列那就是使用触发器和序列。
1基本Oracle序列示例
以下语句创建一个名为id_seq的升序Sequence从10开始递增10最小值为10最大值为100。由于CYCLE选项序列一旦达到100就返回10。
CREATE SEQUENCE id_seqINCREMENT BY 10START WITH 10MINVALUE 10MAXVALUE 100CYCLECACHE 2;
使用NEXTVAL伪列获取序列的下一个值
SELECT id_seq.NEXTVAL
FROM dual;
以下是输出NEXTVAL
----------10 一旦通过NEXTVAL伪列获取序列号就可以使用CURRVAL伪列重复访问它
SELECT id_seq.CURRVAL
FROM dual;
以下是输出CURRVAL
----------10
注意当调用NEXTVAL伪列时它会为正在选择或插入的每一行数据返回一个新值。
SELECT id_seq.NEXTVAL
FROM dual;
以下是输出NEXTVAL
----------20
但如果我使用几个NEXTVAL命令运行此查询的话
SELECT id_seq.NEXTVAL AS nv1,
id_seq.NEXTVAL AS nv2,
id_seq.NEXTVAL AS nv3
FROM dual;
以下是输出NV1 NV2 NV3
---------- ---------- ----------30 30 30
这表明NEXTVAL只为此记录调用过一次并且每次的值都是相同的。
2) 在表列中使用 Sequence 的例子
在Oracle12c之前只能在插入时将序列与表列间接关联。
首先创建一个名为tasks的新表
CREATE TABLE tasks(id NUMBER PRIMARY KEY,title VARCHAR2(255) NOT NULL
);
其次为tasks表的id列创建一个序列
CREATE SEQUENCE task_id_seq;
第三在任务表中插入数据
INSERT INTO tasks(id, title)
VALUES(task_id_seq.NEXTVAL, Create Sequence in Oracle);INSERT INTO tasks(id, title)
VALUES(task_id_seq.NEXTVAL, Examine Sequence Values);
最后从任务表中查询数据
SELECT id, title
FROMtasks; 在本例中tasks表与task_id_seq序列没有直接关联。
3通过标识列使用序列的例子
在Oracle12c中您可以通过标识列将序列与表列相关联。
首先用id列的identity列创建tasks表。
CREATE TABLE tasks(id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,title VARCHAR2(255) NOT NULL
);
Oracle创建了一个与 tasks表 的 id列 关联的序列。
由于Oracle自动为 id列 生成序列因此在您的Oracle实例中序列的名称可能不同。
Oracle创建序列-标识列 Oracle使用 sys.idnseq$ 来存储表和序列之间的链接。
此查询返回任务表和 ISEQ$$_74366序列 的关联
SELECT a.name AS table_name,b.name AS sequence_name
FROM sys.idnseq$ cJOIN obj$ a ON c.obj# a.obj#JOIN obj$ b ON c.seqobj# b.obj#
WHERE a.name TASKS;
第二在任务表中插入一些行
INSERT INTO tasks(title)
VALUES(Learn Oracle identity column in 12c);INSERT INTO tasks(title)
VALUES(Verify contents of the tasks table);
最后从任务表中查询数据
SELECTid, title
FROMtasks; 5. 删除DROPSequence
要删除序列它必须在您的模式中或者您必须具有drop ANY sequence权限才能删除其他模式中的序列。
DROP SEQUENCE seq_name;
6. 更改AlterSequence
创建序列后可以更改序列的大多数特性。唯一不能更改的是序列的起始编号和序列的当前值。要更改的话需要删除并重新创建序列。
ALTER SEQUENCE seq02
INCREMENT BY 20
MAXVALUE 100000
CYCLE;
7. Sequence 原理
Oracle用Sequence来按需提供几乎无限的数字序列这些序列保证是唯一的。最简单的方法就是保持一个全局可见的计数器并在每次查询sequence_name.nextval的值时递增。
但Oracle必须跟踪提供的最后一个数字以避免两次提供相同数字的风险——这对数据库系统来说很容易只需将最新的数字保存在表中即可。然而如果每次有人需要下一个号码时都必须更新一个表格这流程会非常缓慢。这时应该使用一个自主事务这样就不需要等待第一个会话提交其当前事务然后才能生成下一个数字。因此Oracle使用递归的、自主的事务来处理表更新并且它通过在内存中计数并记住下一次需要更新表的时机来避免每次调用nextval都更新表。
自制”序列的一种常见策略是在一个表中包含序列名最近使用的序列号的列然后编写一个函数从表中选择相关行进行更新增加编号更新表并将编号提供给最终用户这正是Oracle内在的机制。
您可以通过查询视图 user_sequences 来查看序列的数据库映像。假设我刚刚创建了一个名为s1的序列图3.1是一个查询创建序列后立即给出的结果和一次调用获取 s1.nextval 后得到的结果
selectmin_value, max_value, increment_by, cache_size, last_number, cycle_flag, order_flag
from user_sequences
where sequence_name S1
/ 图7.1
如果你有DBA权限你可以进一步找到序列的 object_id然后查询视图所基于的字典表seq$。以下运行了两次查询一次在第一次调用 s1.nextval 之前另一次是刚刚调用 s1 之后
select minvalue, maxvalue, increment$, cache, highwater, cycle#, order$
from seq$
whereobj# 124874
; 图7.2
如果你的账户已被授权可以通过动态性能视图 v$_sequences 查看内存中的信息
selectmin_value, max_value,increment_by, cache_size, highwater,cycle_flag, order_flag, nextvalue
fromv$_sequences
whereobject# 124874
/
第一次调用s1.nextval之后我只查询了一次视图在第一次调用nextval时序列才会出现在视图中 图7.3
动态性能视图的列 nextvalue 保存了下次用户调用 s1.nextval 时将提供的值。
三个视图中都有一个“highwater”值在图7.1视图user_sequences中称为“last_number”。当对s1.nextval 的调用返回 highwater 中显示的值highwater和nextvalue相同时进行调用的会话将执行并提交递归事务将增量值添加到当前 highwater 并更新 seq$ 表。
“缓存”不是传统意义上的缓存它只是一对数字无论您设置的序列缓存大小有多大当前值和提升highwater 后的值。为序列设置大型缓存不会造成资源损失所以在大多数情况下您应该尽可能使用大的cache。 1.直接删除序列,然后重新创建并且设置序列的初始值为希望的数据
drop sequence SEQ_StockFlow_ID;create sequence SEQ_StockFlow_IDincrement by 1start with 1000 --你想要的值MAXvalue 9999999999999999;
2、修改序列的每次增加的值然后再修改回每次自增1
-- 修改序列alter sequence SEQ_StockFlow_ID increment by 13500; -- 查看序列select SEQ_StockFlow_ID.nextval from dual;-- 修改序列alter sequence SEQ_StockFlow_ID increment by 1;
