Db2 简明教程

DB2 - Sequences

本章将介绍序列、序列创建、序列查看和序列删除的概念。

This chapter introduces you to the concept of sequence, creation of sequence, viewing the sequence, and dropping them.

Introduction

序列是一种软件函数，它在一定范围内生成升序或降序整数，以生成主键并在表之间协调其他键。您可以使用序列来利用整数，比如，employee_id 或 transaction_id。序列可以支持 SMALLINT、BIGINT、INTEGER 和 DECIMAL 数据类型。序列可以在多个应用程序中共享。无论事务如何，序列都会递增或递减。

A sequence is a software function that generates integer numbers in either ascending or descending order, within a definite range, to generate primary key and coordinate other keys among the table. You use sequence for availing integer numbers say, for employee_id or transaction_id. A sequence can support SMALLINT, BIGINT, INTEGER, and DECIMAL data types. A sequence can be shared among multiple applications. A sequence is incremented or decremented irrespective of transactions.

通过 CREATE SEQUENCE 语句创建序列。

A sequence is created by CREATE SEQUENCE statement.

Types of Sequences

有两种类型的序列：

There are two type of sequences available:

  1. NEXTVAL: It returns an incremented value for a sequence number.

  2. PREVIOUS VALUE: It returns recently generated value.

Parameters of sequences

以下参数用于序列：

The following parameters are used for sequences:

Data type :这是返回递增值的数据类型。（SMALLINT、BIGINT、INTEGER、NUMBER、DOUBLE）

Data type: This is the data type of the returned incremented value. (SMALLINT, BIGINT, INTEGER, NUMBER, DOUBLE)

START WITH ：序列从该参考值开始。

START WITH: The reference value, with which the sequence starts.

MINVALUE ：序列开始使用的最小值。

MINVALUE: A minimum value for a sequence to start with.

MAXVALUE ：序列的最大值。

MAXVALUE: A maximum value for a sequence.

INCREMENT BY ：序列递增的步长值。

INCREMENT BY: step value by which a sequence is incremented.

Sequence cycling ：CYCLE 子句导致重复生成序列。通过引用已由前序序列生成并存储到数据库中的返回的值来进行序列生成。

Sequence cycling: the CYCLE clause causes generation of the sequence repeatedly. The sequence generation is conducted by referring the returned value, which is stored into the database by previous sequence generation.

Creating a sequence

你可以使用以下语法创建序列：

You can create sequence using the following syntax:

Syntax:

Syntax:

db2 create sequence <seq_name>

Example ：[用从 1 开始的递增值创建具有“sales1_seq”名称的新序列]

Example: [To create a new sequence with the name ‘sales1_seq’ and increasing values from 1]

db2 create sequence sales1_seq as int start
with 1 increment by 1

Viewing the sequences

你可以使用以下语法查看序列：

You can view a sequence using the syntax given below:

Syntax:

Syntax:

db2 value <previous/next> value for <seq_name>

Example ：[查看序列“sales1_seq”中先前更新过的值的列表]

Example: [To see list of previous updated value in sequence ‘sales1_seq’]

db2 values previous value for sales1_seq

Output:

Output:

 1
-----------
  4
  1 record(s) selected.

Dropping the sequence

若要移除序列，你需要使用 “DROP SEQUENCE” 命令。以下是如何操作：

To remove the sequence, you need to use the “DROP SEQUENCE ” command. Here is how you do it:

Syntax:

Syntax:

db2 drop sequence <seq_name>>

Example ：[从数据库中丢弃序列“sales1_seq”]

Example: [To drop sequence ‘sales1_seq’ from database]

db2 drop sequence sales1_seq

Output:

Output:

 DB20000I The SQL command completed successfully.