改变序列

改变序列 “更改序列生成器的定义

句法

ALTER SEQUENCE [schema.]sequence [ sequence_option [,... ] ]

where sequence_option can be one of the following:

START WITH value
 QUANTUM SIZE size

描述

改变序列 用于指定现有序列的新起始值。序列的未来值不会小于指定的值。此操作无法回滚,并且对所有其他事务立即可见其效果。

The 改变序列 command can also change the QUANTUM SIZE for the sequence. This is the size of the block of integer values that will be reserved for a transaction engine (TE) to be later assigned.

有关序列行为的详细信息,请参阅 创建序列.

By default, only the user that creates a sequence database object can ALTER the sequence. This user can 授予 the ALTER privilege on the sequence to other users (see 授予). The 选择 privilege needs to be granted to other users to 选择 from the sequence.

参数

sequence

Name of an existing SEQUENCE to alter.

START WITH value

The START WITH value is optional. It sets the new starting value for the SEQUENCE. The value must be an integer value greater than the current ceiling value for the sequence. The starting value must be an integer value greater than zero (0). The starting value can also be an expression that returns an integer value. Some examples of an expression include, but are not limited to:

  • A scalar SQL 选择 statement (see 选择). This must be enclosed by parentheses. This includes all valid SQL 选择 statements, including statements using aggregate functions, GROUP BY, ORDER BY, LIMIT, etc. The SQL 选择 statement must return a number value.

  • 标量用户定义的函数(UDF)。

  • 数学公式。

QUANTUM SIZE[.var]size

新尺寸,指示为给定TE保留的整数值数量的块大小。默认值为100。

例子

以下示例创建一个序列,并创建一个使用新序列来生成ID列值的表。

用 TEST
CREATE SEQUENCE seq_test START WITH 1;
CREATE TABLE tst_table (id INTEGER GENERATED ALWAYS AS IDENTITY (seq_test), column2 STRING);
 INTO tst_table (column2) VALUES ('row1'),('row2');
选择 * FROM tst_table;
 ID  COLUMN2
 --- --------
  1    row1
  2    row2

选择 NEXT VALUE FOR seq_test FROM DUAL;
 SEQ_TEST
 ---------
    3

 INTO tst_table (column2) VALUES ('row3'),('row4');
选择 * FROM tst_table;
 ID  COLUMN2
 --- --------
  1    row1
  2    row2
  4    row3
  5    row4

The 改变序列 command can use the START WITH option to change the new starting value of a sequence, but can only increase it. In the following example, the START WITH option value of one (1) is less than the current value 6. Therefore, the sequence is not altered.

ALTER SEQUENCE seq_test START WITH 1000;
选择 NEXT VALUE FOR seq_test FROM DUAL;
 SEQ_TEST
 ---------
   1000

The 改变序列 command is not transactional. Its effects become visible immediately to all transactions and can’t be rolled back.