ตั้งแต่ SQL Server 2012 เป็นต้นมา Microsoft ได้เพิ่มเรื่องของการจัดการ SEQUENCE หรือการรันเลขที่มาให้เราใช้งาน ซึ่งสามารถนำมาประยุกต์ใช้งานในการจัดการเลขที่ของเอกสารได้อย่างสะดวกและง่ายดาย
การสร้างเลขลำดับ
คำสั่งในการสร้างลำดับของเอกสาร
เช่น ต้องการสร้างลำดับของเอกสารใบสั่งซื้อ (PO) สามารถทำได้โดยใช้คำสั่ง
CREATE SEQUENCE PO
START WITH 1
INCREMENT BY 1 ;
ต้องการสร้างลำดับของเอกสารใบสั่งซื้อ (Invoice) สามารถทำได้โดยใช้คำสั่ง
CREATE SEQUENCE IV
START WITH 1
INCREMENT BY 1 ;
ทำการ Excute คำสั่งก็จะได้ข้อมูลสำหรับเก็บเลขลำดับของ PO และ IV ซึ่่งเลขลำดับที่เราสร้างขึ้นจะถูกเก็บไว้ภายใต้ ตาราง (System Views) ชื่อ sys.sequences ซึ่งเราสามารถเรียกดูข้อมูลของ SEQUENCE ที่เราสร้างได้โดยใช้คำสั่ง
SELECT * FROM sys.sequences -- จะแสดงข้อมูลทั้งหมด
SELECT name, current_value FROM sys.sequences -- จะแสดงชื่อและลำดับปัจจุบันออกมา
การลบเลขลำดับ
กรณีสร้างไว้เยอะเกินก็สามารถลบออกได้โดยใช้ คำสั่ง DROP SEQUENCE เช่น
DROP SEQUENCE IV ;
การเรียกใช้งานเลขลำดับ
การเรียกใช้งานเลขลำดับสามารถเรียกใช้งานได้ภายใต้คำสั่งต่างๆ เช่น SELECT, INSERT, UPDATE หรือ ภายใต้ตัวแปร เป็นต้น โดยใช้คำสั่ง
NEXT VALUE FOR ชื่อ
เช่น
SELECT NEXT VALUE FOR PO
หรือ SELECT NEXT VALUE FOR PO AS PONO
หรือ
DECLARE @PO VARCHAR(10)
DECLARE @SEQ INT
SET @SEQ = NEXT VALUE FOR PO
SET @PO = 'PO' + RIGHT('00000' + CAST(@SEQ AS VARCHAR(5)),5)
SELECT @PO
-- ผลลัพธ์ที่ได้ PO00001
การแก้ไขเลขลำดับ หรือกำหนดเลขลำดับใหม่
การกำหนดเลขลำดับใหม่สามารถทำได้โดยใช้คำสั่ง ALTER SEQUENCE
เช่น ต้องการกำหนดลำดับของ PO ใหม่เป็น 101
ALTER SEQUENCE PO
RESTART WITH 100 -- เมื่อเรียกเลขลำดับในครั้งต่อไปจะได้เลข 101
การนำไปใช้งาน
สำหรับตัวอย่างต่อไปนี้เป็นการสร้าง Stored Procedure สำหรับสร้างและเรียกใช้งาน SEQUENCE
CREATE PROCEDURE spRunDoc (@DocType AS NVARCHAR(20))
AS
BEGIN
DECLARE @cmd NVARCHAR(250)
DECLARE @zero NVARCHAR(5) = '00000'
IF NOT EXISTS(SELECT name FROM sys.sequences WHERE name = @DocType)
BEGIN
SET @cmd = 'CREATE SEQUENCE ' + @DocType + ' START WITH 1 INCREMENT BY 1'
EXECUTE(@cmd)
END
SET @cmd = 'SELECT ''' + @DocType + ''' + RIGHT(''' + @zero + ''' + CAST(NEXT VALUE FOR ' + @DocType + ' AS NVARCHAR),5)'
EXECUTE(@cmd)
END
การนำไปใช้
EXEC spRunDoc 'PO' -- จะส่งตารางของลำดับออกมา
EXEC spRunDoc 'PO61' -- จะส่งตารางของลำดับออกมา
จาก Stored Procedure ข้างต้นจะเป็นการสร้าง Sequence ให้ใหม่เมื่อตรวจสอบไม่พบชื่อ Sequence ที่อ้างถึง จากนั้นก็ทำการเรียกเลขลำดับออกมา
สวัสดีมีเลขลำดับไว้ใช้งาน
ไม่มีความคิดเห็น:
แสดงความคิดเห็น