是否可以在不循环的情况下为 iSeries 表中的每一行生成唯一的数值? [英] Is it possible to generate a unique numeric value for each row in an iSeries table without looping?

问题描述

我有一个包含数千行的 iSeries 表，并且刚刚添加了一个新的数字/整数列作为表键.这基本上是一个整数 id 列(1,2,3...).

I have an iSeries table with thousands of rows, and a new numeric/integer column was just added as the table Key. This will basically be a integer id column (1,2,3...).

假设我无法将此列设为自动递增列.有没有一种简单的方法可以为每一行分配唯一的增量值，而不必遍历每条记录来分配值?也许是某种 UPDATE 查询?

Let's say that I can't make this column an auto-incrementing column. Is there an simple way to assign unique, incremental values to each row without having to loop through each record assigning a value? Perhaps some kind of UPDATE query?

推荐答案

您可以使用 Sequence 对象.例如

You could use a Sequence object. For example

CREATE SEQUENCE MySeq
    as {numeric-datatype}

数据类型可以是 SMALLINT、INTEGER、BIGINT、DECIMAL 或 NUMERIC，小数位数为零(即没有小数位).创建一个数据区域来存储值.

The data type can be SMALLINT, INTEGER, BIGINT, DECIMAL, or NUMERIC, with scale of zero (ie. no decimal places). A data area is created to store the value.

然后您可以使用 NEXT VALUE FOR 表达式来检索和递增序列，如

You can then use a NEXT VALUE FOR expression to retrieve and increment the Sequence, as in

UPDATE MyTable SET id = NEXT VALUE FOR MySeq

如果您想知道分配的最后一个值，您可以使用表达式 mySeq 的上一个值

If you wish to know the last value assigned you can use the expression Previous value for mySeq

此方法不如 RRN() 快，但在某些情况下可能很有用，例如当您希望在不同表中具有唯一的数字时.

This method would not be as fast as RRN(), but may be useful in some cases, such as when you wish to have numbers that are unique across different tables.

这篇关于是否可以在不循环的情况下为 iSeries 表中的每一行生成唯一的数值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！