작성일 : 2008.04.11(2008.04.21 완료)
작성자 : 유일환
제 목 : 각종 형식의 일련번호 만들기
순 서 :
1.일련번호를 사용하는 예
2.IDENTITY
3.문자형식의 일련번호 만들기
4.응용프로시저
안녕하세요.
오늘은 각종 형식의 일련번호 만드는 방법에 대해서 적어볼까 합니다.
일련번호란 것은 데이터들을 구분하기 데이터에 부여한 특정한 번호를 이야기 합니다.
즉, PK처럼 데이터를 구분할 수 있는 번호를 이야기 하는 것입니다.
이런 일련번호는 업무의 편의성을 위해 많이 사용하며, 업무의 특징에 맞추어 일정한 포맷을 가지고 생성하게 됩니다.
자주 사용하는 형식은 아마도 200804110001, 200804110002, 200804110003과 같은 YYYYMMDD + NNNN과 같은 형식일 것입니다.
뿐만 아니라, 08041101, 08041102, 08041103, ... 0804110A, 0804110B... 처럼 문자형도 포함된 일련번호도 사용할 수 있습니다.
이런 문자형이 포함된 일련번호는 일련번호 길이를 적게 하고도 많은 번호를 생성할 수 있다는 장점이 있습니다.
이러한 일련 번호를 만들기 위해서 어떠한 방법들이 있고, 어떤 방법이 가장 효율적인가를 살펴 보도록 하겠습니다.
1.일련번호를 사용하는 예
주문번호, 또는 문서번호와 같은 데이터를 구분짓는 번호를 통털어서 일련번호라고 합니다.
즉, 주문번호, 문서번호가 사용되는 경우가 모두 일련번호를 사용하는 예라 할 수 있습니다.
어떤 사용자가 인터넷쇼핑을 통해 주문을 하게 되면 해당 주문에 대해 주문번호를 만들어서, 해당 주문을 관리하게 됩니다.
이와 같이 주문번호를 사용함으로서 주문을 편리하고 효율적으로 관리를 할 수 있을 것입니다.
우리가 평소에 많이 사용하는 신용카드의 전표에도 전표번호가 일련적으로 만들어 지는 것을 볼 수 있습니다.
이 번호도 마찬가지로 시스템에서 자동으로 부여하는 일련번호인 것입니다.
문서관리 시스템에서는 각 문서별로 문서번호를 만들어 내서 문서를 관리해야 겠지요.
병원의 시스템에서도 환자마다 차트번호를 만들어서 관리하는데, 이것 역시 일련번호처럼 자동으로 부여를 해줍니다.
이처럼 많은 시스템에서 일련번호를 따는 프로세스를 사용하고 있습니다.
2.IDENTITY
SQL Server를 조금이라도 배워보고 사용해온 분들에게는 지루한 이야기이겠지만, identity를 짚고 넘어가지 않을 수가 없네요.
이 역시 일련번호와 같이 일련적인 번호를 만들어 내는 기능이니까요.
identity를 사용하는 방법이 가장 쉽고 가장 간단한 일련번호를 만들어 내는 방법입니다.
identity(1,1)로 자료형을 만들게 되면, 해당 컬럼은 자동으로 1씩 숫자가 증가하면서 데이터가 쌓이게 됩니다.
아무런 추가 작업 없이 일련번호를 만들어 낼 수 있는 것이죠. 하지만, identity는 숫자형이기 때문에,
데이터의 특징에 맞추어 일정한 포맷의 일련번호를 만들어 내기가 어렵기 때문입니다.
예를 들어, 사용자가 원하는 일련번호 형식이 YYYYMMDD + NNNN과 같이 날짜 + 숫자 4자리라면, identity 컬럼만을
사용해서는 만들기가 쉽지 않습니다. identity는 1, 2, 3 ... 9999처럼 하나씩 숫자를 증가시키기 때문이죠.
하지만, identity를 사용해서 YYYYMMDD+NNNN과 같은 형태를 만들어 내지 말라는 법은 없습니다.
SQL의 세상에서는 번뜩이는 아이디어만 있으면 무엇이든 가능하답니다.
identity의 일반적인 사용법은 아실테고 바로 identity를 사용해서 YYYYMMDD+NNNN형식을 만드는 방법을 설명해 보도록 하겠습니다.
먼저, 테스트를 위한 테이블을 하나 생성하도록 하겠습니다.
<SQL 1>
|
CREATE TABLE dbo.OrderTest
( OrderNo numeric(12,0) identity(1,1) NOT NULL
,ModelCD int NULL
) |
<SQL 1>을 통해 만든 테이블에 데이터를 입력하면, 데이터는 자동으로 1 부터 증가를 하게 됩니다.
하지만, 우리는 YYYYMMDD+NNNN형식으로 변경하기 위해서 DBCC CHECKIDENT란 명령어를 사용할 수 있습니다.
<SQL 2>
|
DBCC CHECKIDENT('dbo.OrderTest', RESEED, 200804120001) |
<SQL 2>를 수행하게 되면 OrderTest의 identity의 현재값은 200804120001로 맞추어 지게 되고, 데이터가 입력되면,
자동으로 200804120001부터 순차적으로 데이터가 증가가 됩니다.
<SQL 3>
INSERT INTO OrderTest (ModelCD) VALUES (1)
INSERT INTO OrderTest (ModelCD) VALUES (2)
INSERT INTO OrderTest (ModelCD) VALUES (1)
INSERT INTO OrderTest (ModelCD) VALUES (4)
SELECT *
FROM OrderTest |
<SQL 3>의 결과를 보시면, 200804120001, 200804120002, 200804120003 순으로 주문번호가 발생된 것을 알 수 있습니다.
그렇다면, 우리는 <SQL 2>의 DBCC CHECKIDENT명령어만 매일 0시에 현재일자에 0001부터 초기화를 시켜주면,
우리가 원하는 YYYYMMDD+NNNN형식을 identity를 사용해서도 설정이 가능합니다.
매일 새벽 0시에 다음과 같은 SQL이 수행되도록 스케쥴을 거는 것입니다.
<SQL 4>
DECLARE @NewOrderNo numeric(12,0)
SET @NewOrderNo = CONVERT(numeric(12,0), CONVERT(nchar(8), GETDATE(), 112) + '0000')
DBCC CHECKIDENT('dbo.OrderTest', RESEED, @NewOrderNo) |
<SQL 4>를 매일 새벽에 수행시키는 것이죠. 하지만, <SQL 4>가 잘 못 수행되거나, 실수로 하루에 두번 수행된다면, 시스템에는
잘못된 주문번호가 생성되어져 여기저기 문제를 일으키게 될 것입니다.
저도 실제로는 이러한 방법을 사용해 본적은 없습니다. 하지만, 일별 스케쥴만 잘 관리하고, 잘못되는 번호가 부여되는 것을
막을 방법만 고려한다면, 적절하게 사용할 수 있을 것이라 생각됩니다.
3.문자형식의 일련번호 만들기
이번에는 문자형식의 일련번호를 만드는 과정을 보도록 하겠습니다.
YYMMDD + NNNN형식처럼 년도를 2자리만 쓴 경우에는 08,년도 09년도와 같이 앞에 0이 오는 경우가 있으므로, identity를 이용할 경우는 문제가 있습니다.
문자형으로 YYMMDD + NNNN형식의 일련번호를 생성하면,
0804110001, 0804110002, 0804110003과 같이 생성 가능하지만, identity는 숫자형이므로 804110001, 804110002, 804110003처럼
앞에 0이 제거되어 자릿수가 한자리 적은 일련번호가 만들어 집니다.
그러므로 제일 앞에 0이 와야 하는 문서번호는 숫자형으로는 절대 처리가 불가능하고 문자형으로 처리를 해야 합니다.
물론, 화면에서 보일때만 앞에 0을 붙여버리는 꽁수가 있겠지만, 화면단 프로그래밍도 복잡해지고, 쓸데없는 형변환을
계속 해야 하므로 절대 사용해서는 안되는 방법입니다.
문자형 컬럼을 이용해 YYMMDD+NNNN형식의 주문번호를 만드는 과정을 보기 위해 먼저 테이블을 하나 생성하도록 하겠습니다.
<SQL 5>
--주문 마스터 테이블 생성
CREATE TABLE dbo.OrderMaster
( OrderNo nvarchar(30) COLLATE Korean_Wansung_CI_AS NOT NULL,
ModelCD int NOT NULL,
OrderQty int NOT NULL,
OrderDT datetime NULL,
CustomerID nvarchar(30) COLLATE Korean_Wansung_CI_AS NULL
)
ALTER TABLE OrderMaster
ADD CONSTRAINT PK_OrderMaster PRIMARY KEY(OrderNo) |
OrderMaster는 고객이 주문을 했을 때 생성되는 데이터를 저장하는 테이블로서,
주문이 만들어질때 마다. OrderNo(주문번호)를 자동으로 만들어 주어야 합니다.
3-1.YYMMDD+NNNN형식
년2자리+월2자리+일자2자리+일련번호4자리 형식의 문서 번호를 만드는 것입니다.
이와 같은 문서번호를 만들기 위해서 다음과 같은 프로시저를 사용할 수 있습니다
<SQL 6>
|
--YYMMDD+NNNN형식 번호 생성 프로시저
CREATE PROC dbo.usp_OrderMaster_YYMMDDNNNN
( @ModelCD int
,@OrderQty int
,@CustomerID nvarchar(30)
,@OrderYMD nchar(8)
) as
INSERT INTO OrderMaster
(
OrderNo
,ModelCD
,OrderQty
,OrderDT
,CustomerID
)
SELECT RIGHT(@OrderYMD,6) +
RIGHT('0000' + CONVERT(nvarchar(4), ISNULL(RIGHT(MAX(OrderNo),4),0) + 1), 4)
,@ModelCD
,@OrderQty
,GETDATE()
,@CustomerID
FROM OrderMaster T1
WHERE T1.OrderNo LIKE RIGHT(@OrderYMD,6) + '%'
go |
<SQL 6>을 통해 프로시저를 만든 후에 <SQL 7>을 수행해서 데이터를 입력해 보도록 합시다.
<SQL 7>
exec usp_OrderMaster_YYMMDDNNNN @ModelCD = 1, @OrderQty = 1, @CustomerID = 'A', @OrderYMD = '20080410'
exec usp_OrderMaster_YYMMDDNNNN @ModelCD = 2, @OrderQty = 1, @CustomerID = 'A', @OrderYMD = '20080410'
exec usp_OrderMaster_YYMMDDNNNN @ModelCD = 3, @OrderQty = 1, @CustomerID = 'A', @OrderYMD = '20080410'
exec usp_OrderMaster_YYMMDDNNNN @ModelCD = 1, @OrderQty = 1, @CustomerID = 'A', @OrderYMD = '20080411'
exec usp_OrderMaster_YYMMDDNNNN @ModelCD = 2, @OrderQty = 1, @CustomerID = 'A', @OrderYMD = '20080411'
exec usp_OrderMaster_YYMMDDNNNN @ModelCD = 3, @OrderQty = 1, @CustomerID = 'A', @OrderYMD = '20080411' |
<SQL 7>을 수행하면, 0804100001, 0804100002, 0804100003, 0804110001, 0804110002, ... 와 같이 순차적으로 만들어진
주문번호를 볼 수 있습니다.
RIGHT(@OrderYMD,6) +
RIGHT('0000' + CONVERT(nvarchar(4), ISNULL(RIGHT(MAX(OrderNo),4),0) + 1), 4)
일련번호를 만들어 내기 위해 사용한 구문입니다. 하나씩 살펴보면 다음과 같습니다.
1.주문일자를 오른쪽에서 6자리 잘라내고요. : RIGHT(@OrderYMD, 6) (ex) 080411
2.오늘자 주문번호들중(WHERE T1.OrderNo LIKE RIGHT(@OrderYMD,6) + '%')에서 MAX값을 취합니다.
: MAX(OrderNo)
3.2번의 결과에 대해서 오른쪽에서4자리를 잘라냅니다. 4자리를 잘라냈는데, 값이 없으면 0으로 변경합니다.
: ISNULL(RIGHT(MAX(OrderNo),4),0)
4.일련번호를 증가시키기 위해서 3번의 결과에 1을 더합니다.
: ISNULL(RIGHT(MAX(OrderNo),4),0) + 1)
5.4번의 결과를 문자열 4자리로 변경시킵니다(ex) 1->0001, 2->0002, 10->0010
:RIGHT('0000' + CONVERT(nvarchar(4), ISNULL(RIGHT(MAX(OrderNo),4),0) + 1), 4)
6.1번과 5번의 결과를 더하면 새로운 일련번호가 만들어 집니다.
이와 같은 일련번호를 만드는데 주의할 점은 일련번호가 만들어지면서 동시에 테이블에 데이터가 들어가도록 하는 것입니다.
만약에 일련번호 구하는 SQL과 INSERT하는 SQL을 분리하면, 여러명이 동시에 사용하는 시스템에서 동일한 일련번호가
만들어져, 오류가 발생되어질 것입니다.
<SQL 8>을 통해서 이러한 테스트를 해보도록 하겠습니다.
테스트를 위해서 SQL창 2개를 열어 주시기 바랍니다.
<SQL 8>
|
BEGIN TRAN
DECLARE @OrderYMD nchar(8)
SET @OrderYMD = '20080413'
DECLARE @OrderNo nvarchar(30)
DECLARE @Error int
--새로운 OrderNo를 생성
SELECT @OrderNo = RIGHT(@OrderYMD,6) +
RIGHT('0000' + CONVERT(nvarchar(4),
ISNULL(RIGHT(MAX(OrderNo),4),0) + 1), 4)
FROM OrderMaster T1
WHERE T1.OrderNo LIKE RIGHT(@OrderYMD,6) + '%'
--5초의 시간을 지연시키는 SQL
WAITFOR DELAY '00:00:05'
INSERT INTO OrderMaster
(OrderNo, ModelCD, OrderQty, OrderDT, CustomerID)
VALUES (@OrderNo, 2, 5, GETDATE(), 'AA')
COMMIT TRAN |
BEGIN TRAN
DECLARE @OrderYMD nchar(8)
SET @OrderYMD = '20080413'
DECLARE @OrderNo nvarchar(30)
DECLARE @Error int
--새로운 OrderNo를 생성
SELECT @OrderNo = RIGHT(@OrderYMD,6) +
RIGHT('0000' + CONVERT(nvarchar(4),
ISNULL(RIGHT(MAX(OrderNo),4),0) + 1), 4)
FROM OrderMaster T1
WHERE T1.OrderNo LIKE RIGHT(@OrderYMD,6) + '%'
--5초의 시간을 지연시키는 SQL
WAITFOR DELAY '00:00:05'
INSERT INTO OrderMaster
(OrderNo, ModelCD, OrderQty, OrderDT, CustomerID)
VALUES (@OrderNo, 2, 5, GETDATE(), 'AA')
COMMIT TRAN |
2개의 SQL창을 열고 <SQL 8>의 SQL을 각각의 SQL창에 카피를 합니다.(<SQL-8>의 2개의 SQL은 동일한 SQL입니다.)
첫 번째 창에서 SQL을 실행시키고, 바로 뒤이어 다른창의 SQL을 실행시킵니다.
첫 번째 창의 SQL은 제대로 수행이 되지만, 두 번째 창에서 수행시킨 SQL은 PK에러가 발생되는 것을 볼 수 있습니다.
주문번호를 SELECT문장과 INSERT하는 문장사이에 5초간의 지연(WAITFOR DELAY '00:00:05')이 있으므로 2개의 창에서
SELECT시에는 동일한 새로운 주문번호가 만들어 지게 되고 이로 인해 2번째로 수행된 SQL에서는 오류가 발생되어 집니다.
여기서는 테스트를 위해 WAITFOR를 사용했지만, 실제 운영 시스템에서는 WAITFOR를 사용하지 않아도, 주문번호를 SELECT하는
문장과 주문을 INSERT하는 문장사이에 성능저하가 발생될 수 있고, 그로 인해 오류가 발생되어 질 것입니다.
그러므로 주문번호를 생성하는 동시에 주문테이블에 데이터가 들어가도록 해야 할 것입니다.
3-2.YYMMDD+SSSS형식(알파벳을 포함한 일련번호)
년2자리+월2자리+일자2자리+일련번호4자리 형식의 주문 번호는 하루에 총 9999의 주문만 받을 수 있습니다.
시스템에 따라서 이 정도면 충분할 수 있겠지만, 주문이 많이 발생되는 시스템에서는 주문번호를 늘려서 더 많은 주문이
들어가도록 해야합니다. 만약에 하루에 예상 주문이 십만건 정도 된다면, 뒤의 일련번호가 6자리가 되어야 겠죠.
하지만 그럴 경우, 주문번호가 너무 길어져 사용자들이 외우기에 불편함을 느낄 수 있습니다.
이럴 때, 주문번호의 뒤의 일련번호 4자리에 알파벳을 섞어 쓰는 방법이 있습니다.
이와 같이 알파벳을 일련번호에 섞어 넣으면, 4자리로 만들 수 있는 주문번호는 하루에 총160만건 정도가 됩니다.
그렇다면, 알파벳을 섞어서 일련번호를 어떻게 생성해야 할까요?
ASCII코드값을 구해서 변경을 하거나, 기타의 여러가지 방법이 있을 겁니다.
여기서는 일련번호 참고 테이블을 미리 생성해서 사용하는 방법을 소개하도록 하겠습니다..
<SQL 9>
|
--0~9와 A~Z까지의 문자열을 가진 임시테이블 생성
SELECT * INTO #TMP_STR
FROM (
SELECT '0' [STR] UNION ALL SELECT '1' UNION ALL SELECT '2' UNION ALL SELECT '3' UNION ALL SELECT '4' UNION ALL
SELECT '5' [STR] UNION ALL SELECT '6' UNION ALL SELECT '7' UNION ALL SELECT '8' UNION ALL SELECT '9' UNION ALL
SELECT 'A' [STR] UNION ALL SELECT 'B' UNION ALL SELECT 'C' UNION ALL SELECT 'D' UNION ALL SELECT 'E' UNION ALL
SELECT 'F' [STR] UNION ALL SELECT 'G' UNION ALL SELECT 'H' UNION ALL SELECT 'I' UNION ALL SELECT 'J' UNION ALL
SELECT 'K' [STR] UNION ALL SELECT 'L' UNION ALL SELECT 'M' UNION ALL SELECT 'N' UNION ALL SELECT 'O' UNION ALL
SELECT 'P' [STR] UNION ALL SELECT 'Q' UNION ALL SELECT 'R' UNION ALL SELECT 'S' UNION ALL SELECT 'T' UNION ALL
SELECT 'U' [STR] UNION ALL SELECT 'V' UNION ALL SELECT 'W' UNION ALL SELECT 'X' UNION ALL SELECT 'Y' UNION ALL
SELECT 'Z' [STR]
) T1
go
--문자열 일련번호 참고 테이블(이 테이블에서 부여 될 일련번호를 찾아낸다.)
CREATE TABLE dbo.SEQ_STR
( SEQ_STR nchar(4) COLLATE Korean_Wansung_CI_AS NOT NULL
,BF_SEQ_STR nchar(4) COLLATE Korean_Wansung_CI_AS NULL
,SEQ int identity(1,1) NOT NULL
)
go
--SEQ_STR에 데이터 생성(총 1,679,616건의 일련번호가 만들어진다./하루에 해당 수치만큼 주문을 받을 수 있다.)
INSERT INTO SEQ_STR
(SEQ_STR, BF_SEQ_STR)
SELECT T1.SEQ_STR, NULL
FROM (
SELECT T1.[STR] + T2.[STR] + T3.[STR] + T4.[STR] SEQ_STR
FROM #TMP_STR T1
CROSS JOIN #TMP_STR T2
CROSS JOIN #TMP_STR T3
CROSS JOIN #TMP_STR T4
) T1
ORDER BY T1.SEQ_STR
go
--PK생성
ALTER TABLE SEQ_STR
ADD CONSTRAINT PK_SEQ_STR PRIMARY KEY NONCLUSTERED(SEQ_STR)
go
--SEQ에 대한 인덱스 생성(BF_SEQ_STR을 찾아내기 위해 사용)
CREATE INDEX IDX_SEQ_STR_1 ON SEQ_STR(SEQ, SEQ_STR)
go
--BF_SEQ_STR에 SEQ_STR의 바로 이전값을 설정한다.
--일련번호 생성시 BF_SEQ_STR에 equal(=)조건을 사용해 빠르게 새로운 일련번호를 부여할 수 있다.)
UPDATE T1
SET T1.BF_SEQ_STR =
ISNULL(
(SELECT A.SEQ_STR
FROM SEQ_STR A
WHERE A.SEQ = T1.SEQ - 1), '')
FROM SEQ_STR T1
go
--BF_SEQ_STR에 인덱스 생성
CREATE CLUSTERED INDEX CLU_SEQ_STR ON SEQ_STR(BF_SEQ_STR) |
<SQL 9>를 통해 우리는 SEQ_STR이라는 일련번호 참고 테이블을 만들었습니다. 해당 테이블을 사용해서 알파벳이 포함된
일련번호를 생성할 수 있습니다. <SQL 10>과 같은 일련번호 생성 프로시저를 만들도록 합니다.
<SQL 10>
|
--YYMMDD+SSSS형식 번호 생성
CREATE PROC dbo.usp_OrderMaster_YYMMDDSSSS
( @ModelCD int
,@OrderQty int
,@CustomerID nvarchar(30)
,@OrderYMD nchar(8)
) as
INSERT INTO OrderMaster
(
OrderNo
,ModelCD
,OrderQty
,OrderDT
,CustomerID
)
SELECT RIGHT(@OrderYMD,6) +
( SELECT A.SEQ_STR
FROM SEQ_STR A
WHERE A.BF_SEQ_STR = ISNULL(RIGHT(MAX(T1.OrderNo),4),'')
)
,@ModelCD
,@OrderQty
,GETDATE()
,@CustomerID
FROM OrderMaster T1
WHERE T1.OrderNo LIKE RIGHT(@OrderYMD,6) + '%'
|
<SQL 10>을 사용해 새로운 프로시저를 만든 후, <SQL 11>을 사용해 관연 제대로 일련번호가 부여되는지 확인하도록 합니다.
<SQL 11>
|
--기존의 주문데이터들을 삭제
DELETE OrderMaster
go
--새로운 주문을 3건 입력
exec usp_OrderMaster_YYMMDDSSSS @ModelCD = 1, @OrderQty = 1, @CustomerID = 'A', @OrderYMD = '20080410'
exec usp_OrderMaster_YYMMDDSSSS @ModelCD = 2, @OrderQty = 1, @CustomerID = 'A', @OrderYMD = '20080410'
exec usp_OrderMaster_YYMMDDSSSS @ModelCD = 3, @OrderQty = 1, @CustomerID = 'A', @OrderYMD = '20080410'
go
--숫자만 포함된 주문번호가 만들어져 있다.
SELECT *
FROM OrderMaster
go
--알파벳 주문번호가 부여되도록 9998 주문번호를 수동으로 입력
INSERT INTO OrderMaster
(OrderNo ,ModelCD ,OrderQty ,OrderDT ,CustomerID)
SELECT '0804109998',2 ,4 ,GETDATE() ,'A'
go
--새로운 주문을 3건 입력
exec usp_OrderMaster_YYMMDDSSSS @ModelCD = 1, @OrderQty = 1, @CustomerID = 'A', @OrderYMD = '20080410'
exec usp_OrderMaster_YYMMDDSSSS @ModelCD = 2, @OrderQty = 1, @CustomerID = 'A', @OrderYMD = '20080410'
exec usp_OrderMaster_YYMMDDSSSS @ModelCD = 3, @OrderQty = 1, @CustomerID = 'A', @OrderYMD = '20080410'
go
--알파벳이 포함된 주문번호를 볼 수 있다.
SELECT *
FROM OrderMaster
go |
<SQL 11>을 차례대로 수행해서, 제일 마지막에 결과를 보면, 알파벳으로 부여된 주문번호를 볼 수 있습니다.
여기서 한 가지 유념 하실 것은 바로 SEQ_STR테이블에 BF_SEQ_STR컬럼을 만든 것입니다.
이 컬럼을 통해 간편하고, 빠른속도로 일련번호를 구해낼 수 있는 것입니다.
3-3.각종형식의 문서번호
3-1과 3-2를 통해 주문번호를 만드는 간단한 예제를 살펴보았습니다.
이외에 각종형식의 문서번호는 이들을 사용한 여러분들의 응용력에 달려 있습니다.
각 시스템에 따라서, 업무에 따라서 각종 형태의 코드규칙, 문서번호 규칙이 있습니다. 이 규칙을 구현할 때,
위의 2가지를 참고하시면, 어렵지 않게 구현하실 수 있을 것입니다.
여기에 적은 내용은 새로운 내용도 아니고, 고난이도의 기술도 아닙니다. 단지 초보자분들께는 새로운 자료로서,
이미 경력을 가지고 계신 분들에게는 다시 한번 경험을 정리 할 수 있도록 사용될 수 있으리라고 생각합니다.
4.응용프로시저
이번에는 해당 기법을 사용한 여러가지의 응용프로시저를 만들어 보도록 하겠습니다.
<시나리오1>
|
* 사용자의 주문을 처리해야 합니다.
* 주문시 주문 모델, 주문 수량, 배송지 정보를 입력 받습니다.
* 주문이 입력되면, 해당 모델에 대해 재고가 있는지 먼저 점검을 해서, 재고가 있는 경우만 주문이 가능합니다.
* 주문이 생성되어지면, 재고를 감소시켜야 합니다.
* 주문이 생성되어지면, 해당 주문번호에 대해 배송정보를 생성해 주어야 합니다. |
<시나리오1>을 위한 모델링은 다음과 같습니다.
핵심 내용에 대해서만 간단하게 모델링을 했습니다.
추가적인 설명을 드리지 않아도 쉽게 이해하실 수 있으시리라 믿고 바로 프로시저를 생성하는 SQL을 살펴보도록 하겠습니다.
<SQL 12>
|
CREATE PROC dbo.usp_OrderMasterInsert_Type1
( @ModelCD nvarchar(30)
,@OrderQty int
,@DeliveryAddr nvarchar(100)
,@ErrorCD nchar(4) OUTPUT
) AS
DECLARE @Error int
DECLARE @RCnt int
DECLARE @NewOrderNo nvarchar(30)
--재고존재 체크
UPDATE T1
SET T1.InventoryQty = T1.InventoryQty - @OrderQty
FROM Inventory T1
WHERE T1.ModelCD = @ModelCD
AND T1.InventoryQty >= @OrderQty
SELECT @RCnt = @@ROWCOUNT, @Error = @@ERROR
--Error Check
IF @Error <> 0
BEGIN
SET @ErrorCD = '9999' --시스템에러
RETURN
END
--Error Check
IF @RCnt = 0
BEGIN
SET @ErrorCD = '1001' --재고부족
RETURN
END
--주문 생성
INSERT INTO OrderMaster
(
OrderNo ,ModelCD ,OrderST
,OrderQty ,RegiDT
)
SELECT CONVERT(nchar(8), GETDATE(), 112)
+ RIGHT(REPLICATE('0', 4) + CONVERT(nvarchar(4), ISNULL(CONVERT(int, RIGHT(MAX(T1.OrderNo),4)),0) + 1), 4) OrderNo
,@ModelCD
,'RECV' --접수완료
,@OrderQty
,GETDATE() RegiDT
FROM OrderMaster T1
WHERE T1.OrderNo LIKE CONVERT(nchar(8), GETDATE(), 112) + '%'
--Error Check
SELECT @Error = @@ERROR
IF @Error <> 0
BEGIN
SET @ErrorCD = '9999' --시스템에러
RETURN
END
--배송생성을 위해 마지막 입력된 주문번호를 가져온다.
SELECT @NewOrderNo = MAX(T1.OrderNo)
FROM OrderMaster T1
--배송생성
INSERT INTO DeliveryMaster
(
OrderNo ,DeliveryST ,DeliveryAddr
)
SELECT @NewOrderNo ,'RECV' ,@DeliveryAddr
IF @Error <> 0
BEGIN
SET @ErrorCD = '9999' --시스템에러
RETURN
END
SET @ErrorCD = '0000'
RETURN
go |
<SQL 12>에서 새성한 usp_OrderMasterInsert_Type1 프로시저를 이용해 주문을 입력하는 SQL구문입니다.
<SQL 13>
|
BEGIN TRAN
DECLARE @ErrorCD nchar(4)
EXEC usp_OrderMasterInsert_Type1
@ModelCD = 'A01' ,@OrderQty = 1 ,@DeliveryAddr = N'서울시 구로구' , @ErrorCD = @ErrorCD OUTPUT
IF @ErrorCD <> '0000'
BEGIN
SELECT 'Fail' MSG
ROLLBACK TRAN
RETURN
END
SELECT 'Success' MSG
COMMIT TRAN
RETURN |
프로시저에 매개변수로 @ErrorCD를 넘겨서 트랜잭션을 컨트롤 하고 있습니다.
<SQL 12>프로시저의 프로세스를 정리하면 다음과 같습니다.
1. 재고수량 변경
2. 주문생성
3. 마지막으로 부여된 주문번호 가져오기
4. 배송정보 생성
여기서 주의해야 할 점은 2가지가 있습니다.
첫 번째는 재고수량을 먼저 변경하는 것입니다.
<SQL 14>
|
--usp_OrderMasterInsert_Type1 프로시저의 재고변경 SQL
--재고존재 체크
UPDATE T1
SET T1.InventoryQty = T1.InventoryQty - @OrderQty
FROM Inventory T1
WHERE T1.ModelCD = @ModelCD
AND T1.InventoryQty >= @OrderQty
SELECT @RCnt = @@ROWCOUNT, @Error = @@ERROR
IF @Error <> 0
BEGIN
SET @ErrorCD = '9999' --시스템에러
RETURN
END
IF @RCnt = 0
BEGIN
SET @ErrorCD = '1001' --재고부족
RETURN
END
|
<SQL 14>는 usp_OrderMasterInsert_Type1의 재고변경 프로세스 입니다.
WHERE조건을 보면, ModelCD의 조건과 T1.InventoryQty >= @OrderQty 이 사용된 것을 볼 수 있습니다.
재고가 있을 경우만 주문을 생성할 수 있습니다. 해당 조건을 사용해 UPDATE를 하게 되면 해당 모델에 재고가 있는 경우만
재고가 감소될 것입니다. UPDATE후에, SELECT @RCnt = @@ROWCOUNT, @Error = @@ERROR 를 사용해서 UPDATE된 건수와,
해당 SQL의 오류 여부를 체크합니다. @Rcnt에 0이 설정되면, UPDATE로 변경된 데이터가 없다는 것인데,
이것은 곧, 재고가 부족하다는 것입니다. 그러므로 여기서 @ErrorCD를 설정해서 RETURN을 해버립니다.
@ErrorCD에 재고부족으로 에러가 설정되었으므로 해당 프로시저를 ROLLBACK을 합니다.(<SQL 13>에서 처리)
이로 인해 UPDATE를 하면서 동시에 재고 존재 체크까지 한 번에 끝내 버리는 것입니다.
잘 기억하시고 완벽하게 이해하시면, 유용하게 사용하실 수 있을 겁니다.
두 번째로 주의할 점은, 새로운 주문번호를 가져오는 구문입니다.
<SQL 15>
|
--usp_OrderMasterInsert_Type1 프로시저의 새로 생성된 주문번호를 가져오는 SQL
--배송생성을 위해 마지막 입력된 주문번호를 가져온다.SELECT CONVERT(nchar(8), GETDATE(), 112)
SELECT @NewOrderNo = MAX(T1.OrderNo)
FROM OrderMaster T1 |
간단하게 MAX를 사용해서 마지막으로 부여된 주문번호를 가져오고 있습니다. 이렇게 하면, 여러명이서 사용하는,
시스템에서 다른 주문번호를 가져오는 것이 아닐지 걱정하는 분이 있으실 것입니다.
하지만 ISOLATION LEVEL이 READ UNCOMMITTED가 아니고, 트랜잭션만 제대로 걸려 있으면, 절대로 다른 사용자가 만 든
주문번호를 가져오게 되는 일은 없습니다. 결국에는 트랜잭션 처리에 의해 이와 같은 문법이 가능합니다.
(트랜잭션과 락에 대해서는 여기서는 다루지 않겠습니다.)
usp_OrderMasterInsert_Type1프로시저를 살펴보았습니다. 여러분이 생각할 때는 해당 프로시저가 잘 만들어졌다고
생각이 되시나요? 저는 아니라고 생각합니다.
굉장히 많은 사용자들이 사용하는 시스템에서 이와 같이 주문을 처리하면 주문번호를 부여 받는데 엄청난 부하가 생길 것입니다.
usp_OrderMasterInsert_Type1의 SQL들이 수행되면서 어떠한 락들이 발생될지 보도록 하겠습니다.
<표 1>
| 사용자A |
사용자A의 락의 발생 |
사용자A가 COMMIT 이전의
락으로 인한 다른 프로세스 |
| BEGIN TRAN |
|
|
| UPDATE 재고테이블의 재고수량 |
재고 테이블에 해당 모델에 대해 락 발생 |
사용자A가 끝날 때까지, 동일한
모델에 대해 재고 변경 불가능 |
| INSERT 주문 |
주문테이블에 락 발생 |
사용자A가 끝날 때까지, 새로운
주문번호 생성 불가
MAX(OrderNo)를 수행할 수 없다. |
| SELECT 신규 주문번호 |
|
사용자A가 끝날 때까지,
MAX(OrderNo)를 수행할 수 없다. |
| INSERT 배송 |
배송테이블에 락 발생 |
사용자A에서 입력한 배송테이블의
PK인 주문번호만 틀리면 INSERT가능 |
| COMMIT TRAN |
모든 락이 풀림 |
차단 됐던 프로세스들 수행 가능 |
트랜잭션을 걸고 사용자 A가 A01모델에 대해 재고수량을 변경하면, 해당 트랜잭션이 COMMIT되기 전까지, 다른 사용자들은
해당 모델의 재고수량을 변경할 수 없습니다. 이로 인해, 동일한 모델에 주문이 동시간대에 몰리게 되면, 시스템이 느려질 것입니다.
뿐만 아니라, 주문테이블에 INSERT를 하기 위해서는 새로운 주문번호를 만들어야 하는데, 그러기 위해서는 주문테이블에서
MAX(OrderNo)를 수행해야만 합니다. 그런데 다른 사용자가 주문테이블에 데이터를 입력하고, COMMIT을 하지 않으면,
MAX(OrderNo)가 무엇인지 알지 못하기 때문에 절대 새로운 주문번호를 생성해낼 수가 없게 됩니다.
즉, 재고테이블과 주문테이블에 발생된 락으로 인해서, 한 사용자가 입력한 주문이 완전히 주문되어서 COMMIT되기 까지는,
다른 사용자가 수행할 수 없습니다. 이런 경우 한 건의 주문입력이 0.001초로 진행이 된다면, 문제가 없을 것입니다.
하지만, 주문프로세스 내에 많은 프로세스가 있거나, 느린 프로세스가 있는 경우에는, 사용자들이 주문을 많이 하면,
많이 할수록 프로세스들이 차단되어서 점점 느려질 것입니다.
이와 같은 차단에 의한 시스템의 성능저하를 해결하기 위해 WITH(NOLOCK)이라는 힌트를 사용하실 수 있는데,
이 힌트를 사용하게 되면, 차단은 피해갈 수 있지만,
COMMIT되지 않은 데이터를 읽음으로 해서 데이터의 정확성에 문제가 발생될 수 있습니다.
시스템의 성능을 위해, 가장 중요한 데이터의 정확성을 잃어버린다면, 시스템의 성능 향상은 아무런 의미가 없습니다.
우리가 어떤 프로세스를 만들때 가장 중요한 것은 데이터의 정확성을 유지하는 것입니다.
그러므로, 데이터를 생성, 변경, 삭제 처리하는 과정에서는 데이터의 정확성을 위해, WITH(NOLOCK)과 같은
힌트는 굉장히 주의해서 사용해야 합니다.
결국에는 WITH(NOLOCK)을 사용하지 않고 차단을 해결하도록 노력해야 합니다.
궁극의 해결책은 하나의 프로세스가 빠르게 해결되도록, 시스템과 프로세스가 설계되는 것입니다.
이와 같은 방법으로 해결이 안된다면, 선접수, 후처리 방법등을 생각할 수 있습니다.
접수된 주문에 대해 주문번호를 생성하고 COMMIT을 하고, 나머지 처리들은 나중에 처리되도록 하는 방법입니다.
시스템의 상황과 업무에 맞추어서 적절하게 프로세스를 변경하는 것이죠.
이것까지 설명을 하자면, 여러가지 업무의 시나리오까지 설명을 해야 하니, 여기서는 이야기 하지 않도록 하겠습니다.
마지막으로 이와 같은 주문프로세스를 만들때 주의할 점을 정리하면,
- 중복된 번호가 발생되지 않도록 한다.
- 차단을 최대한 줄일 수 있도록 핵심 조건에 적절한 인덱스를 생성해 준다.
- 테이블에 인덱스가 늘어날 수록 데이터 생성 시간이 느려짐을 고려한다.
- 테스트! 그리고 테스트! 그리고 또 테스트!!
이와 같은 주문번호 생성프로세스는 시스템내에서 굉장히 중요한 프로세스입니다.
그러므로 많은 테스트를 꼭 동반하도록 해야 합니다.
앞으로 주문번호와 같은 일련번호 생성 프로세스를 만드는 작업을 하실때 이 문서가 도움이 되길 바라며 오늘은
여기까지 하도록 하겠습니다.
다들 즐거운 하루 하루 보내세요.^^
