创建序列

创建序列 - 定义新的序列生成器

句法

CREATE SEQUENCE [ schema.]seqname [ sequence_option [,...] ]

where sequence_option can be one of the following:

START WITH value
QUANTUM SIZE size

描述

创建序列 创建一个新的序列号生成器。 The sequence database object is owned by the user issuing the 创建序列 command. 可以使用序列的访问权限 授予. See also 例3..

您需要授予表格的选择权限’S序列以使非管理员能够插入该表。

序列号生成器默认情况下为默认值分配BIGINT(64位整数)值(1)。 The START WITH sequence_option can be used to provide a different starting integer value for the sequence. This starting integer value is referred to as the floor value for the sequence.

序列将始终生成大于或等于序列的底部值的唯一整数值​​。 但是,这些整数值不一定以顺序,升序的顺序生成。 See 序列和多个TES 如何由事务引擎管理序列。

每次序列生成唯一的整数值时,序列值都会增加。 这可以以两种方式之一发生:

  • 将值分配给定义为由身份生成的列(请参阅 创建表)。 每次将新行插入表中,序列都会生成一个新的唯一整数值​​并将其分配给列值。

  • The NEXT VALUE Nuodb SQL. expression can be used to select from a sequence.

序列和多个TES

Successive calls to NEXT VALUE return a sequence of unique numbers but not necessarily a sequence of ascending values (as you might expect with other RDBMS such as Oracle or SQL Server). Being unique, the values returned are suitable for defining keys (the primary use of sequence values), but no assumptions should be made about the values of keys in successive INSERTs using the same sequence.

不寻常的订购是由于诺贝布’S分布式架构。

这个怎么运作

让’S考虑序列号生成器是如何由一个或多个事务引擎(TE)管理的。 序列号生成器将保留一系列整数值 每个 te从序列请求整数值。

  • 默认情况下,该整数值的此范围是在一百(100)个值的块中。

  • The QUANTUM SIZE sequence_option can be used to change this block size.

  • 一旦序列分配了为该TE保留的所有整数值,都会为该TE保留下一个整数值范围。

  • 以这种方式,可以由多个TES使用序列,其中争用最小(序列是单个共享资源和潜在的热点)。

例子

假设TE1和TE2都使用相同的序列。

  • 序列为TE1保留1-100的整数值,并为TE2保留整数101-200。

  • 在序列分配给TE1保留的所有整数值之后,序列号生成器将为TE1保留的下一个块可能包含整数值201-300。

  • If a client connects to the database via TE1 and selects the NEXT VALUE of the sequence, the integer value 1 is assigned.

  • If the same client creates a second connection to the database, this time to TE2, and the NEXT VALUE of the sequence is selected, the integer value 101 would be returned, and so forth.

  • 如果另一个客户端连接到TE1并使用相同的序列,它将接收值2。

  • If the first client now fetches the NEXT VALUE via a connection to TE1 it will receive the value 3.

Thus a multi-threaded client invoking a sequence of NEXT VALUE calls, using different connections in different threads, might receive the values 1, 101, 3, 102, 4, 105 …​

  • 这些数字是独一无二的,但没有单调地增加。

  • 客户端不应尝试“猜测”下一个值将是什么,也不会对其进行逐步。

  • 这与Oracle或SQL Server等其他RDBM相比。

地板和天花板值

As each TE returns unique integer values, the floor value for that sequence for that TE is incremented. In this way a sequence can be used across multiple TEs with minimal contention.

序列号生成器维护大于为所有TES保留的任何整数值的整数值。

  • This integer value is referred to as the ceiling value for the sequence.

  • 序列的上限值始终递增一千(1000)。

  • 此增量大小无法更改。

  • The initial ceiling value of a new sequence, by default, is 1000.

Once a value that is equal to or greater than 1000 has been reserved for a TE, the ceiling value for the sequence is incremented to 2000.

  • The sequence ceiling value will never decrease.

  • 在新TE上访问序列,可能在重新启动数据库后,将生成大于或等于序列上限值的序列值,即使先前的序列值,小于地板,从未返回给客户端。

参数

schema

将创建序列的架构的名称。 The default is the current schema as defined by the statement. If schema is provided, and the schema does not exist, it will be created. 无法在中创建序列 SYSTEM schema. 此架构保留用于数据库元数据。

seqname

序列的名称(可选地模式)。 The seqname must be distinct from the name of any other sequence object in the schema in which the SEQUENCE is created.

START WITH value

The optional clause START WITH value allows the sequence to begin at any value. The default starting value is one (1). The starting value can be a number, positive or negative. The starting value can also be an expression that returns an integer value. 表达的一些示例包括但不限于:

  • A scalar SQL 选择 statement (see 选择)。 这必须由括号括起来。 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.

  • 标量用户定义的函数。

  • 数学公式。

QUANTUM SIZE size

The optional QUANTUM SIZE clause allows you to specify how many unique integer values are included in a block of values that will be reserved for a TE. The default size is 100.

例子

示例1:创建序列并使用该序列为插入表中的每一行分配唯一值。
创建序列 seq_hockey_fans START WITH 111;

CREATE TABLE hockey_fans (id   INTEGER GENERATED ALWAYS AS IDENTITY(seq_hockey_fans),
                          name STRING);
INSERT INTO hockey_fans (name) VALUES ('me'), ('you');
SELECT * FROM hockey_fans;
 ID  NAME
 --- -----
 111  me
 112  you

SELECT NEXT VALUE FOR seq_hockey_fans FROM DUAL;
 SEQ_HOCKEY_FANS
 ----------------
        113

INSERT INTO hockey_fans (name) VALUES ('us');
SELECT * FROM hockey_fans;
ID  NAME
 --- -----
 111  me
 112  you
 114  us
示例2:使用标量SQL SELECT语句,表达式或函数(UDF)创建序列以获得起始值。
下拉序列 IF EXISTS seq_hockey_next;
SELECT MAX(id) FROM hockey.hockey;
 [MAX]
 ------
   24

CREATE SEQUENCE seq_hockey_next START WITH (SELECT MAX(id) +1 FROM hockey.hockey);

SELECT NEXT VALUE FOR seq_hockey_next FROM DUAL;
 [NEXT VALUE FOR TEST.SEQ_HOCKEY_NEXT]
 --------------------------------------
                  25

DROP SEQUENCE IF EXISTS seq_hockey_next;
CREATE SEQUENCE seq_hockey_next START WITH 25+1;

SELECT NEXT VALUE FOR seq_hockey_next FROM DUAL;
 [NEXT VALUE FOR TEST.SEQ_HOCKEY_NEXT]
 --------------------------------------
                  26

DROP SEQUENCE IF EXISTS seq_hockey_next;
SET DELIMITER @
CREATE FUNCTION fnc_seq_hockey_next
  RETURNS INTEGER
AS
  RETURN (SELECT MAX(ID) +1 FROM hockey.hockey);
END_FUNCTION;
@
SET DELIMITER ;
CREATE SEQUENCE seq_hockey_next START WITH fnc_seq_hockey_next();

SELECT NEXT VALUE FOR seq_hockey_next FROM DUAL;
 [NEXT VALUE FOR TEST.SEQ_HOCKEY_NEXT]
 --------------------------------------
                  25
示例3:在具有序列到非管理员用户的表上成功委派了插入权限。
授予  on hockey.hockey_fans to app_user;
授予 选择 on hockey.seq_hockey_fans to app_user;