sp_sequence_get_range (Transact-SQL)sp_sequence_get_range (Transact-SQL)

适用对象: 是SQL Server 是Azure SQL 数据库 是Azure Synapse Analytics (SQL DW) 否并行数据仓库 APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

从序列对象中返回一系列序列值。Returns a range of sequence values from a sequence object. 序列对象生成和发出请求的值数目,并为应用程序提供与该系列序列值相关的元数据。The sequence object generates and issues the number of values requested and provides the application with metadata related to the range.

有关序列号的详细信息,请参阅序列号For a more information about sequence numbers, see Sequence Numbers.

“主题链接”图标 Transact-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

  
sp_sequence_get_range [ @sequence_name = ] N'<sequence>'   
     , [ @range_size = ] range_size  
     , [ @range_first_value = ] range_first_value OUTPUT   
    [, [ @range_last_value = ] range_last_value OUTPUT ]  
    [, [ @range_cycle_count = ] range_cycle_count OUTPUT ]  
    [, [ @sequence_increment = ] sequence_increment OUTPUT ]  
    [, [ @sequence_min_value = ] sequence_min_value OUTPUT ]  
    [, [ @sequence_max_value = ] sequence_max_value OUTPUT ]  
    [ ; ]  

参数Arguments

[ @sequence_name = ] N'sequence' 序列对象的名称。[ @sequence_name = ] N'sequence' The name of the sequence object. 架构是可选的。The schema is optional. sequence_namenvarchar (776)sequence_name is nvarchar(776).

[ @range_size = ] range_size 要从序列中提取的值的数目。[ @range_size = ] range_size The number of values to fetch from the sequence. @range_sizebigint@range_size is bigint.

[ @range_first_value = ] range_first_value Output 参数返回序列对象的第一个(最小值或最大值)值,用于计算请求的范围。[ @range_first_value = ] range_first_value Output parameter returns the first (minimum or maximum) value of the sequence object used to calculate the requested range. @range_first_value sql_variant与请求中使用的序列对象相同的基类型。@range_first_value is sql_variant with the same base type as that of the sequence object used in the request.

[ @range_last_value = ] range_last_value 可选的输出参数将返回所请求的范围的最后一个值。[ @range_last_value = ] range_last_value Optional output parameter returns the last value of the requested range. @range_last_value sql_variant与请求中使用的序列对象相同的基类型。@range_last_value is sql_variant with the same base type as that of the sequence object used in the request.

[ @range_cycle_count = ] range_cycle_count 可选的输出参数将返回序列对象为了返回所请求的范围而循环的次数。[ @range_cycle_count = ] range_cycle_count Optional output parameter returns the number of times that the sequence object cycled in order to return the requested range. @range_cycle_countint@range_cycle_count is int.

[ @sequence_increment = ] sequence_increment 可选 output 参数返回用于计算所请求范围的序列对象的增量。[ @sequence_increment = ] sequence_increment Optional output parameter returns the increment of the sequence object used to calculate the requested range. @sequence_increment sql_variant与请求中使用的序列对象相同的基类型。@sequence_increment is sql_variant with the same base type as that of the sequence object used in the request.

[ @sequence_min_value = ] sequence_min_value 可选的输出参数将返回序列对象的最小值。[ @sequence_min_value = ] sequence_min_value Optional output parameter returns the minimum value of the sequence object. @sequence_min_value sql_variant与请求中使用的序列对象相同的基类型。@sequence_min_value is sql_variant with the same base type as that of the sequence object used in the request.

[ @sequence_max_value = ] sequence_max_value 可选的输出参数将返回序列对象的最大值。[ @sequence_max_value = ] sequence_max_value Optional output parameter returns the maximum value of the sequence object. @sequence_max_value sql_variant与请求中使用的序列对象相同的基类型。@sequence_max_value is sql_variant with the same base type as that of the sequence object used in the request.

返回代码值Return Code Values

0(成功)或 1(失败)0 (success) or 1 (failure)

RemarksRemarks

sys 中的 sp_sequence_get_rangeis。sp_sequence_get_rangeis in the sys. 架构,可将其作为 sys.databases 引用 sp_sequence_get_range。schema and can be referenced as sys.sp_sequence_get_range.

循环序列Cycling sequences

如果需要,序列对象将循环相应的次数以处理请求的范围。If required, the sequence object will cycle the appropriate number of times to service the requested range. 将通过 @range_cycle_count 参数向调用方返回循环次数。The number of times cycled is returned to the caller through the @range_cycle_count parameter.

备注

在进行循环时,序列对象从序列对象最小值(升序)或最大值(降序)重新开始,而不是从序列对象的开始值重新开始。When cycling, a sequence object restarts from the minimum value for an ascending sequence and the maximum value for a descending sequence, not from the start value of the sequence object.

非循环序列Non-cycling sequences

如果请求的范围中的值数目大于序列对象中的剩余可用值数目,并不会从序列对象中缩减请求的范围,将返回下面的错误 11732:If the number of values in the requested range is greater than the remaining available values in the sequence object the requested range is not deducted from the sequence object and the following error 11732 is returned:

The requested range for sequence object '%.*ls' exceeds the maximum or minimum limit. Retry with a smaller range.

PermissionsPermissions

要求对序列对象或序列对象架构具有 UPDATE 权限。Requires UPDATE permission on the sequence object or the schema of the sequence object.

示例Examples

下面的示例使用一个名为 RangeSeq 的序列对象。The following examples use a sequence object named Test.RangeSeq. 使用以下语句创建 RangeSeq 序列。Use the following statement to create the Test.RangeSeq sequence.

CREATE SCHEMA Test ;  
GO  
  
CREATE SEQUENCE Test.RangeSeq  
    AS int   
    START WITH 1  
    INCREMENT BY 1  
    MINVALUE 1  
    MAXVALUE 25  
    CYCLE  
    CACHE 10  
;  

A.A. 检索序列值的范围Retrieving a range of sequence values

以下语句从 RangeSeq 序列对象中获取四个序列号,并向用户返回第一个编号。The following statement gets four sequence numbers from the Test.RangeSeq sequence object and returns the first of the numbers to the user.

DECLARE @range_first_value_output sql_variant ;  
  
EXEC sp_sequence_get_range  
@sequence_name = N'Test.RangeSeq'  
, @range_size = 4  
, @range_first_value = @range_first_value_output OUTPUT ;  
  
SELECT @range_first_value_output AS FirstNumber ;  
  

B.B. 返回所有输出参数Returning all output parameters

下面的示例返回 sp_sequence_get_range 过程中的所有输出值。The following example returns all the output values from the sp_sequence_get_range procedure.

DECLARE    
  @FirstSeqNum sql_variant  
, @LastSeqNum sql_variant  
, @CycleCount int  
, @SeqIncr sql_variant  
, @SeqMinVal sql_variant  
, @SeqMaxVal sql_variant ;  
  
EXEC sys.sp_sequence_get_range  
@sequence_name = N'Test.RangeSeq'  
, @range_size = 5  
, @range_first_value = @FirstSeqNum OUTPUT   
, @range_last_value = @LastSeqNum OUTPUT   
, @range_cycle_count = @CycleCount OUTPUT  
, @sequence_increment = @SeqIncr OUTPUT  
, @sequence_min_value = @SeqMinVal OUTPUT  
, @sequence_max_value = @SeqMaxVal OUTPUT ;  
  
-- The following statement returns the output values  
SELECT  
  @FirstSeqNum AS FirstVal  
, @LastSeqNum AS LastVal  
, @CycleCount AS CycleCount  
, @SeqIncr AS SeqIncrement  
, @SeqMinVal AS MinSeq  
, @SeqMaxVal AS MaxSeq ;  
  

@range_size 参数更改为较大的数字(例如 75)将导致循环访问序列对象。Changing the @range_size argument to a large number such as 75 will cause the sequence object to cycle. 请检查 @range_cycle_count 参数以确定是否循环访问序列对象以及循环次数。Check the @range_cycle_count argument to determine if and how many times the sequence object has cycled.

C.C. 使用 ADO.NET 的示例Example using ADO.NET

下面的示例使用 ADO.NET 从 RangeSeq 获取一个范围。The following example gets a range from the Test.RangeSeq by using ADO.NET.

SqlCommand cmd = new SqlCommand();  
cmd.Connection = conn;  
cmd.CommandType = CommandType.StoredProcedure;  
cmd.CommandText = "sys.sp_sequence_get_range";  
cmd.Parameters.AddWithValue("@sequence_name", "Test.RangeSeq");  
cmd.Parameters.AddWithValue("@range_size", 10);  
  
// Specify an output parameter to retreive the first value of the generated range.  
SqlParameter firstValueInRange = new SqlParameter("@range_first_value", SqlDbType.Variant);  
firstValueInRange.Direction = ParameterDirection.Output;  
cmd.Parameters.Add(firstValueInRange);  
  
conn.Open();  
cmd.ExecuteNonQuery();  
  
// Output the first value of the generated range.  
Console.WriteLine(firstValueInRange.Value);  
  

另请参阅See Also

CREATE SEQUENCE (Transact-SQL) CREATE SEQUENCE (Transact-SQL)
ALTER SEQUENCE (Transact-SQL) ALTER SEQUENCE (Transact-SQL)
DROP SEQUENCE (Transact-SQL) DROP SEQUENCE (Transact-SQL)
NEXT VALUE FOR (Transact-SQL) NEXT VALUE FOR (Transact-SQL)
序列号Sequence Numbers