201209262005SQL - SQL SERVER 2012 新功能 SEQUENCE(順序)

SQL SERVER 2012 的新功能不少,其中有一個 T-SQL 的功能就是 SEQUENCE(順序),這個功能在 ORACLE 上是存在的功能。而 SQL 2012 能夠提供這個功能,對於筆者而言確實是一個福音。

為什麼這樣說呢!? 原因很簡單,那就是「取號」這個功能,在 ORACLE 中,常見以 SEQUENCE 來做為取號之用,而在 SQL 2012 以前,筆者大多都是用一個 取號檔資料表 來做為取號的紀錄,所以在控制上,就要特別的去留意處理,就深怕哪天發生取到相同號碼的危機,所以有時會特別地去用 FOR UPDATE 這等具有危險性的語法來管控,除了取號外,還需要自行去設計所謂的取號區間及循環等等記錄用的資料表,可謂是多工又耗時。

然而現在,有了這個功能後,對於日後的取號功能,確實可以省去許多的時間了,以下就來透過一些範例,來實作這好玩的功能吧!!

1.建立 SEQUENCE(順序)

介面:




語法:

CREATE SEQUENCE [schema_name . ] sequence_name
[ AS [ built_in_integer_type | user-defined_integer_type ] ]
[ START WITH <constant> ]
[ INCREMENT BY <constant> ]
[ { MINVALUE [ <constant> ] } | { NO MINVALUE } ]
[ { MAXVALUE [ <constant> ] } | { NO MAXVALUE } ]
[ CYCLE | { NO CYCLE } ]
[ { CACHE [ <constant> ] } | { NO CACHE } ]
[ ; ]

CREATE SEQUENCE dbo.MYSEQ
AS [INT]
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 1000
CYCLE
CACHE;

2.修改 SEQUENCE(順序)

語法:

ALTER SEQUENCE [schema_name. ] sequence_name
    [ RESTART [ WITH <constant> ] ]
    [ INCREMENT BY <constant> ]
    [ { MINVALUE <constant> } | { NO MINVALUE } ]
    [ { MAXVALUE <constant> } | { NO MAXVALUE } ]
    [ CYCLE | { NO CYCLE } ]
    [ { CACHE [ <constant> ] } | { NO CACHE } ]
    [ ; ]

ALTER SEQUENCE dbo.MYSEQ
MAXVALUE 2000
NO CYCLE
NO CACHE;

結果:


3.刪除 SEQUENCE(順序)

語法:

DROP SEQUENCE { [ database_name . [ schema_name ] . | schema_name. ]    sequence_name } [ ,...n ]
[ ; ]

DROP SEQUENCE dbo.MYSEQ;

4.使用在語法中

建立測試用資料表:
CREATE TABLE [dbo].[MY_TEST_TABLE](
 [UID] [int] NOT NULL,
 [UNAME] [nvarchar](50) NOT NULL,
 [UPDTIME] [datetime] NOT NULL
) ON [PRIMARY]

--建立資料,以下語法為 SQL 2012 的新功能之一,新增多筆資料時可用
INSERT [dbo].[MY_TEST_TABLE]
VALUES(NEXT VALUE FOR DBO.MYSEQ, N'張大呆', GETDATE())
,(NEXT VALUE FOR DBO.MYSEQ, N'張二呆', GETDATE())
,(NEXT VALUE FOR DBO.MYSEQ, N'張小呆', GETDATE());

結果:


註:用於 SELECT 的方式,可參考「1.建立 SEQUENCE(順序)」的第四張圖片

用於在欄位的預設值中:

語法:
ALTER TABLE [dbo].[MY_TEST_TABLE] ADD  CONSTRAINT [DF_MY_TEST_TABLE_UID]  DEFAULT (NEXT VALUE FOR [dbo].[MYSEQ]) FOR [UID]

結果:


5.使用sp_sequence_get_range來取得 SEQUENCE 的區間

語法:

--以下程式碼來自於微軟MSDN範例
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'dbo.MYSEQ'
, @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 ;

結果:


6.透過程式來呼叫sp_sequence_get_range

畫面:


程式:

string strConnStr = @"***連線字串***";
using (SqlConnection sc = new SqlConnection(strConnStr))
{
 sc.Open();
 using (SqlCommand cmd = new SqlCommand())
 {
  cmd.Connection = sc;
  cmd.CommandType = CommandType.StoredProcedure;
  cmd.CommandText = "sys.sp_sequence_get_range";
  cmd.Parameters.AddWithValue("@sequence_name", "dbo.MYSEQ");
  cmd.Parameters.AddWithValue("@range_size", 5);

 

  SqlParameter sp_FirstValue = new SqlParameter("@range_first_value", SqlDbType.Variant);
  sp_FirstValue.Direction = ParameterDirection.Output;
  cmd.Parameters.Add(sp_FirstValue);

 

  SqlParameter sp_LastValue = new SqlParameter("@range_last_value", SqlDbType.Variant);
  sp_LastValue.Direction = ParameterDirection.Output;
  cmd.Parameters.Add(sp_LastValue);

 

  cmd.ExecuteNonQuery();

 

  label1.Text += sp_FirstValue.Value;
  label2.Text += sp_LastValue.Value;
 }
}

結果:


呆言呆語:一次取五個號碼,這樣可以再透過程式來做一些加工,對於慣用於取號檔資料表的筆者,這樣的功能,確實是很好用的...

7.使用 sys.sequences 來查詢 SEQUENCES 的相關資訊

語法:

SELECT *
FROM SYS.SEQUENCES;

結果:


參考:
Using SQL Server 2012 T-SQL New Features
SQL Server 2012 的 T-SQL 新功能 – SEQUENCE 物件
SQL Server 2012 - Sequence Object
Sequence Numbers
sp_sequence_get_range (Transact-SQL)
CREATE SEQUENCE (Transact-SQL)
ALTER SEQUENCE (Transact-SQL)
DROP SEQUENCE (Transact-SQL)
NEXT VALUE FOR (Transact-SQL)
順序屬性 (一般頁面)
sys.sequences (Transact-SQL)

沒有上一則|日誌首頁|沒有下一則
回應
關鍵字
累積 | 今日
loading......