Oracle 序列(增删改查，伪列NEXTVAL 和CURRVAL)

1.创建序列(SEQUENCE)

• Oracle的序列是一种数据库对象，其主要工作是用来为表产生唯一值。序列被创建后可以通过数据字典找到序列对象，因此序列可以被多个对象共享。
• 序列的一个典型的用途是创建一个主键的值，它对于每一行必须是唯一的。

  • 基本语法

    CREATE SEQUENCE sequence
    [INCREMENT BY n]
    [START WITH n]
    [{MAXVALUE n | NOMAXVALUE}]
    [{MINVALUE n | NOMINVALUE}]
    [{CYCLE | NOCYCLE}]
    [{CACHE n | NOCACHE}];
    

    • sequence：指定序列的名称。
    • INCREMENT BY：用于定义序列的步长，如果省略，则默认为1，如果出现负值，则代表序列的值是按照此步长递减的。
    • START WITH：定义序列的初始值（即产生的第一个值），默认为1。
    • MAXVALUE：定义序列生成器能产生的最大值。选项NOMAXVALUE是默认选项，代表没有最大值定义，这时对于递增序列，系统能够产生的最大值是10的27次方；对于递减序列，最大值是一1。
    • MINVALUE：定义序列生成器能产生的最小值。选项NOMAXVALUE是默认选项，代表没有最小值定义，这时对于递减序列，系统能够产生的最小值是10的26次方；对于递增序列，最小值是1。
    • CYCLE和NOCYCLE：表示当序列生成器的值达到限制值后是否循环。CYCLE代表循环，NOCYCLE代表不循环。如果循环，则当递增序列达到最大值时，循环到最小值；对于递减序列，达到最小值时，循环到最大值。如果不循环，达到限制值后，继续产生新值就会发生错误。
    • CACHE（缓冲）：定义存放序列的内存块的大小，默认为20。NOCACHE表示不对序列进行内存缓冲。对序列进行内存缓冲，可以改善序列的性能。

  • 创建序列

    CREATE SEQUENCE invoice_seq   -- 序列invoice_seq
    INCREMENT BY 1  --  步长1
    START WITH 1  -- 初始值1
    MAXVALUE 9999999  -- 最大值9999999
    NOCYCLE   -- 序列不循环
    NOCACHE;  -- 不缓冲
    

2.查看数据库序列

• 查看当前数据库中所有序列
  • 在user_sequences表中保存了序列明细信息，比如序列的最小值与最大值，最后一次的数字值等。

    SELECT sequence_name, min_value, max_value, increment_by, last_number FROM user_sequences;
    

3.修改序列

• 基本语法

  ALTER SEQUENCE sequence
  [INCREMENT BY n]
  [{MAXVALUE n | NOMAXVALUE}]
  [{MINVALUE n | NOMINVALUE}]
  [{CYCLE | NOCYCLE}]
  [{CACHE n | NOCACHE}];
  

  • 不能改变序列的起始值，为了以不同的数字重新开始一个序列，必须先删除序列再重新创建。
  • 序列的最小值不能大于序列的当前值。
  • 序列的最大值不能小于序列的当前值。
  • 修改后的序列规则不影响以前的序列值，只有未来的序列值会受到影响。
  • 用户必须具有ALTER SEQUENCE的权限。
• 修改序列

  -- 序列invoice_seq的步进值改为2
  ALTER SEQUENCE invoice_seq INCREMENT BY 2
  

4.删除序列

• 将序列从数据字典中删除。

  DROP SEQUENCE invoice_seq;
  

5.NEXTVAL 和CURRVAL伪列

• 每个序列都具有两个伪列用来允许使用序列的表来获取序列的值。
  • NEXTVAL：返回下一个可用的序列值，它每次返回一个唯一的被引用值，即使对于不同的用户也是如此。
  • CURRVAL：获得当前的序列值。
• 对于序列来说，在使用CURRVAL之间，必须先使用NEXTVAL初始化一次，否则Oracle将弹出异常。

  -- 序列初始化，获取序列的下一个值
  SELECT invoice_seq.NEXTVAL FROM DUAL;
  
  -- 返回当前序列的值
  SELECT invoice_seq.CURRVAL FROM DUAL;
  
  --查询当前序列与下一序列
  SELECT invoice_seq.CURRVAL，invoice_seq.NEXTVAL FROM DUAL；
  

• 使用插入表

  INSERT INTO 
  invoice(invoice_id, vendor_id, invoice_number, invoice_total) 
  VALUES(invoice_seq.NEXTVAL, 10, 'INV' || invoice_seq.CURRVAL, 100);