MS SQL Cursor 사용 예제

SET NOCOUNT ON DECLARE @vendor_id int, @vendor_name nvarchar(50), @message varchar(80), @product nvarchar(50) PRINT '-------- Vendor Products Report --------' DECLARE vendor_cursor CURSOR FOR SELECT VendorID, Name FROM Purchasing.Vendor WHERE PreferredVendorStatus = 1 ORDER BY VendorID OPEN vendor_cursor FETCH NEXT FROM vendor_cursor INTO @vendor_id, @vendor_name WHILE @@FETCH_STATUS = 0 BEGIN PRINT ' ' SELECT @message = '----- Products From Vendor: ' + @vendor_name PRINT @message -- Declare an inner cursor based -- on vendor_id from the outer cursor. DECLARE product_cursor CURSOR FOR SELECT v.Name FROM Purchasing.ProductVendor pv, Production.Product v WHERE pv.ProductID = v.ProductID AND pv.VendorID = @vendor_id -- Variable value from the outer cursor OPEN product_cursor FETCH NEXT FROM product_cursor INTO @product IF @@FETCH_STATUS <> 0 PRINT ' <<None>>' WHILE @@FETCH_STATUS = 0 BEGIN SELECT @message = ' ' + @product PRINT @message FETCH NEXT FROM product_cursor INTO @product END CLOSE product_cursor DEALLOCATE product_cursor -- Get the next vendor. FETCH NEXT FROM vendor_cursor INTO @vendor_id, @vendor_name END CLOSE vendor_cursor DEALLOCATE vendor_cursor


MSDN에서도 확인 하 실 수 있습니다..

http://technet.microsoft.com/ko-kr/library/ms180169.aspx

---

위의 예제는 msdn에서 사용된 예제 입니다.
제가 사용한 예제는 아래와 같습니다..

펼쳐 보기 누르셔서 보세요...

BEGIN TRAN DECLARE @DOC_ID CHAR(36) DECLARE @COMMENT_ID VARCHAR(36) DECLARE @CREATE_DATE VARCHAR(30) DECLARE @LEVEL SMALLINT DECLARE cu_1 CURSOR FOR SELECT DISTINCT(DOC_ID) FROM [dbo].[tb_BoardComment] OPEN cu_1 FETCH NEXT FROM cu_1 INTO @DOC_ID WHILE(@@fetch_status=0) BEGIN DECLARE @CNT INT DECLARE @COMMENT_ORDER VARCHAR(30) SET @CNT = 0 DECLARE cu_2 CURSOR FOR SELECT [COMMENT_ID] ,[CREATE_DATE] ,[LEVEL] FROM [dbo].[tb_BoardComment] WHERE [DOC_ID] = @DOC_ID ORDER BY [LEVEL] ASC, [CREATE_DATE] ASC OPEN cu_2 FETCH NEXT FROM cu_2 INTO @COMMENT_ID, @CREATE_DATE, @LEVEL WHILE(@@fetch_status=0) BEGIN IF(@LEVEL = 1) BEGIN PRINT('LEVEL ONE') SET @CNT = (@CNT + 1) SET @COMMENT_ORDER = RIGHT('0000000' + CONVERT(varchar, @CNT), 7) + 'z' UPDATE [dbo].[tb_BoardComment] SET [PARENT_COMMENT_ID] = [COMMENT_ID] ,[OREGIN_COMMENT_ID] = [COMMENT_ID] ,[COMMENT_ORDER] = @COMMENT_ORDER WHERE COMMENT_ID = @COMMENT_ID END ELSE IF(@LEVEL = 2) BEGIN DECLARE @PARENT_COMMENT_ID CHAR(36) SELECT @PARENT_COMMENT_ID = [PARENT_COMMENT_ID] FROM [dbo].[tb_BoardComment] WHERE [COMMENT_ID] = @COMMENT_ID SELECT @COMMENT_ORDER = [COMMENT_ORDER] FROM [dbo].[tb_BoardComment] WHERE [COMMENT_ID] = @PARENT_COMMENT_ID DECLARE @TEMP_COMMENT_ID CHAR(36) DECLARE @TEMP_CNT INT SET @TEMP_CNT = 0 PRINT('START') DECLARE cu_3 CURSOR FOR SELECT [COMMENT_ID] FROM [dbo].[tb_BoardComment] WHERE [PARENT_COMMENT_ID] = @PARENT_COMMENT_ID AND [COMMENT_ID] <> @PARENT_COMMENT_ID ORDER BY [CREATE_DATE] ASC OPEN cu_3 FETCH NEXT FROM cu_3 INTO @TEMP_COMMENT_ID PRINT('LEVLE TWO') WHILE(@@fetch_status=0) BEGIN SET @TEMP_CNT = @TEMP_CNT + 1 PRINT('COUNT') IF(@TEMP_COMMENT_ID = @COMMENT_ID) BEGIN PRINT('OK') BREAK END FETCH NEXT FROM cu_3 INTO @TEMP_COMMENT_ID END CLOSE cu_3 DEALLOCATE cu_3 IF(@TEMP_CNT = 0) BEGIN PRINT(@COMMENT_ID) PRINT(@TEMP_COMMENT_ID) PRINT(@PARENT_COMMENT_ID) PRINT(@COMMENT_ORDER) END SET @COMMENT_ORDER = CONVERT(VARCHAR, LEFT(@COMMENT_ORDER, LEN(@COMMENT_ORDER) - 1)) + RIGHT('00' + CONVERT(varchar, @TEMP_CNT), 2) + 'z' UPDATE [dbo].[tb_BoardComment] SET [OREGIN_COMMENT_ID] = @PARENT_COMMENT_ID ,[COMMENT_ORDER] = @COMMENT_ORDER WHERE COMMENT_ID = @COMMENT_ID END FETCH NEXT FROM cu_2 INTO @COMMENT_ID, @CREATE_DATE, @LEVEL END CLOSE cu_2 DEALLOCATE cu_2 FETCH NEXT FROM cu_1 INTO @DOC_ID END CLOSE cu_1 DEALLOCATE cu_1 -- ROLLBACK