이론적인 것을 떠나서 실습을 통해 T-SQL 에서의 커서가 무엇인가 알아보도록 하겠다. 커서의 정의나 그 외 구체적인 이론들은 msdn2 를 참조하기를 바란다.
개괄적으로 커서란?
Data Set 을 Row 단위로 읽어들여 그 Row 에 따른 데이터로 특정한 작업을 수행하는 것을 말한다.
이렇게 설명을 들어도 잘 모를테다. 그래서 실습을 통해 Cursor 가 무엇인지 차근 차근 알아보도록 하겠다. [단, 커서에 대한 부분은 굳이 이해하지 않아도 된다. 왜냐하면 커서는 매우 위험한 툴이다! RDBMS 개념을 파괴시키는 것이 커서이다 - 때로는 유용하게 쓰일 수도 있으나 가급적 사용은 삼가하는 것이 좋겠다]
여기서도 마찬가지로 MS AdventureWorks DB 를 통해 알아보도록 하겠다.
(샘플 데이터베이스 다운로드: http://www.microsoft.com/downloads/details.aspx?familyid=e719ecf7-9f46-4312-af89-6ad8702e4e6e&displaylang=en )
SalesOrderHeader 라는 테이블 (Sales스키마)은 상품에 대한 판매 자료를 담고 있다. 일단, 다음의 쿼리를 사용하여 하나의 Row 를 추가하자.
쿼리1 (Language : sql)
1: ALTER TABLE Sales.SalesOrderHeader
2: ADD historyCaptureDate datetime
3: GO
SalesOrderHeader 에는 historyCaptureDate 라는 Column 이 없으므로 위의 쿼리는 안전하게 수행된다. 설명하기에 앞서 아래의 쿼리를 수행하여 세 개의 테이블을 더 생성해 본다.
쿼리2 (Language : sql)
1: CREATE schema YOUNGSBLOG
2: go
3: CREATE TABLE YOUNGSBLOG.CardTypeUsage
4: (
5: CardType nvarchar(50) NOT NULL,
6: Year numeric(4) NOT NULL,
7: OrderCount numeric(10) NOT NULL,
8: ChargeSum money
9: )
10: GO
11: CREATE TABLE YOUNGSBLOG.CustomerOrderSummary
12: (
13: CustomerID int NOT NULL,
14: Year numeric(4) NOT NULL,
15: OrderCount numeric(10) NOT NULL,
16: OrderValue money
17: )
18: GO
19: CREATE TABLE YOUNGSBLOG.SalesPersonSummary
20: (
21: SalesPersonID int NOT NULL,
22: Year numeric(4) NOT NULL,
23: OrderCount numeric(10) NOT NULL,
24: OrderValue money
25: )
26: GO
YOUNGSBLOG 라는 스키마를 생성한 뒤 CardTypeUsage 라는 테이블을 추가하였다. 이 테이블에는 Sales.SalesOrderHeader 에서 판매된 상품을 결재한 카드의 타입을 연도별로 묶어서 횟수를 측정하고 합계금액을 측정하기 위해 생성하였다.
마찬가지로 CustomerOrderSummary 테이블에는 Sales.SalesOrderHeader 에서 상품을 구입한 고객의 아이디를 연도별로 묶고 횟수와 합계 금액을 측정한다.
SalesPersonSummary 는 특별한 설명없이도 이해하리라 생각된다. 판매한 사람에 대한 정보를 연도별로 묶는 것이다.
이렇게 세 개의 테이블을 생성하였다. 그러면 SalesOrderHeader 에서는 카드의 타입을 볼 수 없으므로 CreditCard 테이블과 조인하여 테이블을 살펴보자.
카드 타입, 연도, 금액, 고객아이디, 판매한 사원의 아이디 .. 이렇게 추출했다. 이제 이 데이터를 가지고 각 Row 의 데이터에 따라서 새로 생성한 CardTypeUsage 에 삽입하거나 Update 하는 방법을 생각해 보자.
당연히 Stored Procedure 를 사용해야 편하게 이 방법을 수행할 수 있을 테지만 막상 쉽게 떠오르지는 않을 것이다. 자, 그러면 완성된 커서문을 가지고 차근 차근 설명해 보도록 하겠다.
커서를 사용한 Stored Procedure (Language : sql)
1: SET ANSI_NULLS ON
2: GO
3: SET QUOTED_IDENTIFIER ON
4: GO
5: -- =============================================
6: -- Author: Young Park
7: -- Create date: July 23, 2007
8: -- =============================================
9: IF EXISTS (SELECT * FROM sys.objects
10: WHERE object_id = object_id(N'[dbo].[pParkAssignment6]') AND type IN (N'P', N'PC'))
11: DROP PROCEDURE [dbo].[pParkAssignment6]
12: GO
13:
14: CREATE PROCEDURE [dbo].[pParkAssignment6]
15: (
16: @pOrderDateYear NUMERIC(4)
17: )
18: AS
19: BEGIN
20: DECLARE @CrsrOrderDate CURSOR,
21: @vCardType NVARCHAR(50),
22: @vOrderYear NUMERIC(4),
23: @vOrderValue MONEY,
24: @vCustomerID INT,
25: @vSalesPersonID INT
26:
27: SET @CrsrOrderDate = CURSOR FOR
28: SELECT C.CardType,
29: YEAR(S.OrderDate),
30: S.TotalDue,
31: S.CustomerID,
32: S.SalesPersonID
33: FROM Sales.SalesOrderHeader AS S
34: INNER JOIN Sales.Creditcard AS C
35: ON S.CreditCardID = C.CreditCardID
36: WHERE YEAR(S.OrderDate) = @pOrderDateYear
37: AND S.historyCaptureDate IS NULL
38:
39: OPEN @CrsrOrderDate
40:
41: FETCH NEXT FROM @CrsrOrderDate INTO @vCardType,
42: @vOrderYear,
43: @vOrderValue,
44: @vCustomerID,
45: @vSalesPersonID
46:
47: WHILE (@@FETCH_STATUS = 0)
48: BEGIN
49:
50: --*** First
51: IF EXISTS(SELECT * FROM YOUNGSBLOG.CardTypeUsage
52: WHERE CardType = @vCardType
53: AND Year = @vOrderYear)
54: BEGIN
55: -- update, if cardtype and year exists in CardTypeUsage table
56: UPDATE YOUNGSBLOG.CardTypeUsage
57: SET OrderCount = OrderCount + 1,
58: ChargeSum = ChargeSum + @vOrderValue
59: WHERE CardType = @vCardType
60: AND Year = @vOrderYear
61: END
62: ELSE
63: BEGIN
64: -- if not exists, insert
65: INSERT INTO YOUNGSBLOG.CardTypeUsage
66: VALUES (@vCardType,
67: @vOrderYear,
68: 1,
69: @vOrderValue)
70: END
71:
72: --** Second
73: IF EXISTS (SELECT * FROM YOUNGSBLOG.CustomerOrderSummary
74: WHERE CustomerID = @vCustomerID
75: AND Year = @vOrderYear)
76: BEGIN
77: -- update, if customerID and year exists in CutomerOrderSummary table
78: UPDATE YOUNGSBLOG.CustomerOrderSummary
79: SET OrderCount = OrderCount + 1,
80: OrderValue = OrderValue + @vOrderValue
81: WHERE CustomerID = @vCustomerID
82: AND Year = @vOrderYear
83: END
84: ELSE
85: BEGIN
86: -- if not exists, insert
87: INSERT INTO YOUNGSBLOG.CustomerOrderSummary
88: VALUES (@vCustomerID,
89: @vOrderYear,
90: 1,
91: @vOrderValue)
92: END
93: --** Third
94: IF @vSalesPersonID IS NOT NULL
95: BEGIN
96: IF EXISTS (SELECT * FROM YOUNGSBLOG.SalesPersonSummary
97: WHERE SalesPersonID = @vSalesPersonID
98: AND Year = @vOrderYear)
99: BEGIN
100: -- update
101: UPDATE YOUNGSBLOG.SalesPersonSummary
102: SET OrderCount = OrderCount + 1,
103: OrderValue = OrderValue + @vOrderValue
104: WHERE SalesPersonID = @vSalesPersonID
105: AND Year = @vOrderYear
106: END
107: ELSE
108: BEGIN
109: -- insert
110: INSERT INTO YOUNGSBLOG.SalesPersonSummary
111: VALUES (@vSalesPersonID,
112: @vOrderYear,
113: 1,
114: @vOrderValue)
115: END
116: END
117:
118: UPDATE Sales.SalesOrderHeader
119: SET historyCaptureDate = GETDATE()
120: WHERE CURRENT OF @CrsrOrderDate
121:
122: FETCH NEXT FROM @CrsrOrderDate INTO @vCardType,
123: @vOrderYear,
124: @vOrderValue,
125: @vCustomerID,
126: @vSalesPersonID
127: END -- End of While
128:
129: -- Close Cursor
130: CLOSE @CrsrOrderDate
131: DEALLOCATE @CrsrOrderDate
132:
133: END
134: GO
9 - 12절은 Stored Procedure 문이 이미 존재할 경우에 삭제하고 다시 생성하라는 구문이다. 유용하므로 알아두기를 바란다.
(Language : sql)
1: IF EXISTS (SELECT * FROM sys.objects
2: WHERE object_id = object_id(N'[dbo].[pParkAssignment6]') AND type IN (N'P', N'PC'))
3: DROP PROCEDURE [dbo].[pParkAssignment6]
4: GO
그 다음의 구문을 살펴보자. Stored Procedure 를 생성하는 구문인데 @pOrderDateYear 를 파라미터로 받는 Stored Procedure 이다. SalesOrderHeader 는 2001 년도부터 2004년도까지의 데이터를 가지고 있으므로 이 경우에는 2001, 2002, 2003, 2004 이 네가지만 사용이 가능하다.
(Language : sql)
1: CREATE PROCEDURE [dbo].[pParkAssignment6]
2: (
3: @pOrderDateYear NUMERIC(4)
4: )
다음의 구문은 변수 선언이다. 유의해서 볼 것은 아래의 빨간 부분이다. 커서를 선언할 때에는 변수를 선언할 때와 마찬가지로 할 수 있다. 커서가 선언이 됐으면...
(Language : sql)
1: DECLARE @CrsrOrderDate CURSOR,
2: @vCardType NVARCHAR(50),
3: @vOrderYear NUMERIC(4),
4: @vOrderValue MONEY,
5: @vCustomerID INT,
6: @vSalesPersonID INT
위에서 살펴본 테이블을 토대로 커서를 생성한 것이다.
(Language : sql)
1: SET @CrsrOrderDate = CURSOR FOR
2: SELECT C.CardType,
3: YEAR(S.OrderDate),
4: S.TotalDue,
5: S.CustomerID,
6: S.SalesPersonID
7: FROM Sales.SalesOrderHeader AS S
8: INNER JOIN Sales.Creditcard AS C
9: ON S.CreditCardID = C.CreditCardID
10: WHERE YEAR(S.OrderDate) = @pOrderDateYear
11: AND S.historyCaptureDate IS NULL
2-11 절은 커서가 어떠한 테이블을 참조할 것인지를 나타낸다. 위에서 살펴본 테이블과 같으나 다른 것은 YEAR(S.OrderDate) 에 파라미터를 할당함으로서 년도를 수동으로 입력할 수 있게 해놨다.
(Language : sql)
-
OPEN @CrsrOrderDate
커서는 열고/닫히는 과정안에서 이루어진다. 일단, 연다...
(Language : sql)
1: FETCH NEXT FROM @CrsrOrderDate INTO @vCardType,
2: @vOrderYear,
3: @vOrderValue,
4: @vCustomerID,
5: @vSalesPersonID
자, 여기서부터 중요하다. FETCH 문을 사용하여 커서로 가리키게 되는 테이블의 Row 의 각 Column 을 @파라미터로 받고 있다. 유의깊게 봐야 할 것은 위에서 커서문을 선언했을 때 사용된 테이블의 컬럼과 일치되어야 한다. SELECT 한 테이블에 사용된 컬럼수가 5개 이므로 마찬가지로 FETCH (가져오기) 를 할 때에도 5개로 일치하게 해야 한다.
FETCH 는 NEXT , PRIOR, FIRST, LAST 등의 다른 옵션도 있다. (자세한 것은 MSDN 참조)
(Language : sql)
-
WHILE (@@FETCH_STATUS = 0)
자, While 문으로 FETCH_STATUS 가 0 (Successful) 할때까지 순환하게 된다. 순환하는 이유에 대해서 잠깐 설명하자면, Cursor 는 각 Row 를 참조하므로 만족하는 Row 가 끝날 때 까지 순환을 해야만 전체 테이블을 읽게 되는 것이다.
(Language : sql)
1: IF EXISTS(SELECT * FROM YOUNGSBLOG.CardTypeUsage
2: WHERE CardType = @vCardType
3: AND Year = @vOrderYear)
4: BEGIN
5: -- update, if cardtype and year exists in CardTypeUsage table
6: UPDATE YOUNGSBLOG.CardTypeUsage
7: SET OrderCount = OrderCount + 1,
8: ChargeSum = ChargeSum + @vOrderValue
9: WHERE CardType = @vCardType
10: AND Year = @vOrderYear
11: END
12: ELSE
13: BEGIN
14: -- if not exists, insert
15: INSERT INTO YOUNGSBLOG.CardTypeUsage
16: VALUES (@vCardType,
17: @vOrderYear,
18: 1,
19: @vOrderValue)
20: END
위의 문장을 글로 풀이를 해보자:
만일 CardTypeUsage 테이블에서 카드의 타입과 년도가 처음 우리가 읽어들인 SELECT (조인된)문에서 가져온 카드의 타입과 년도와 일치한다면 업데이트만 하고, 그렇지 않다면 생성(Insert) 하라는 것이다. 업데이트 항목은 당연히 횟수를 나타내는 OrderCount 를 증가하고, ChargeSum 에 일치한 데이터의 금액을 합산하면 되는 것이다.
그렇지 않다면 ELSE 문으로 INSERT 하는 구문이 읽혀지게 되는 것이다.
마찬가지로 두번째, 세번째 테이블도 UPDATE/INSERT 를 하면 되는 것이다. (이 부분은 생략)
(Language : sql)
1: UPDATE Sales.SalesOrderHeader
2: SET historyCaptureDate = GETDATE()
3: WHERE CURRENT OF @CrsrOrderDate
그리고 위의 코드에서는 SalesOrderHeader 에서 현재 참조하고 있는 (CURRENT OF) 커서 구문을 참조하여 historyCaptureDate 에 캡쳐하는 순간의 날짜를 업데이트 하는 것이다.
(Language : sql)
1: FETCH NEXT FROM @CrsrOrderDate INTO @vCardType,
2: @vOrderYear,
3: @vOrderValue,
4: @vCustomerID,
5: @vSalesPersonID
FETCH 문이 한번 더 나오는 이유는 그래야만 다음의 Row 로 넘어가기 때문이다. 그래서 While 문이 반복되어 테이블이 끝날 때까지 작업을 수행한다.
(Language : sql)
1: -- Close Cursor
2: CLOSE @CrsrOrderDate
3: DEALLOCATE @CrsrOrderDate
마지막으로 커서를 닫고 Deallocate 해주면 된다.
결과는 직접 확인하시길... 커서의 수행으로 어느정도 기다려야 결과물이 출력된다.
Be the first to rate this post
- Currently 0/5 Stars.
- 1
- 2
- 3
- 4
- 5