'Cursor'에 해당되는 글 2건

Programming/Microsoft SQL Server

Cursor는 집합단위로 처리하는 Query를 행단위로 처리하도록 합니다. 그런데 저는 개인적으로 Cursor는 잘 쓰지 않는 편입니다. Cursor를 쓸 경우에는 Cursor로 인해 얻는 이득보다는 아무래도 실이 더 많은듯 합니다.

Cursor를 쓰는 경우의 대부분을 일반 Query로 대체할 수 있을 뿐만 아니라 무엇보다도 속도문제가 가장 걸리기 때문입니다. 따라서 되도록이면 Cursor를 쓰지 말라고 권하고 싶습니다.(되도록이면 이지 무조건이 아닙니다..;;;)

Cursor가 사용되기 위해서는 먼저 생성, 열기, 사용, 닫기, 제거등의 절차를 거쳐야 합니다.

1. Cursor 생성

Declare (커서이름) Cursor
For (Query)


Declare를 통해 먼저 Cursor를 선언하고 For다음에 가져올 행과 열을 지정합니다.

Declare ex_cursor Cursor
For Select DepartmentID, Name, GroupName
 From HumanResources.Department
 Where DepartmentID = 10;


ex_cursor이라는 이름의 Cursor를 선언하였습니다. 이 Cursor는 HumanResources.Department Table의 DepartmentID가 10이라는 행의 DepartmentID, Name, GroupName Column을 가져옵니다.

이렇게 Cursor선언하고 사용하려면 Cursor에 대한 Open이 필요합니다.

2. Cursor Open

Open ex_cursor;


Open한 Cursor는 Fatch구문을 통해 선언할때의 Data를 가져오게 됩니다. 커서는 전역(Global)커서와 지역(Local)커서로 구분될 수 있는데 만약 전역와 지역커서모두 동일한 이름의 커서가 존재한다면 Local커서가 우선적으로 열리게 됩니다. 그러므로 전역커서를 명시적으로 Open하려면 다음과 같이 Global을 지정해 주도록 합니다.

 

 

Open Global ex_cursor;

 

Declare @ID Int
Declare @Name nVarChar(50)
Declare @GroupName nVarChar(50)
Fetch
From ex_cursor
Into @ID, @Name, @GroupName;

 

 

 

Data를 가져오기 위해 우선 @ID, @Name, @GroupName변수를 선언하고 Into절을 통해 해당 변수에 Cursor에서 가져온 Data값을 넣습니다.

 

Select @ID, @Name, @GroupName;

변수에 담은 Data를 조회합니다.


3. Cursor Close

 

Open된 Cursor를 사용하고 나면 다시 Close하고 커서의 할당을 해제하도록 합니다.

 

Close ex_cursor;

Deallocate ex_cursor;


참고:
Deallocate 써서 Cursor제거를 시도하면 그 즉시 Cursor가 삭제되지 않는 경우도 있습니다. 이렇게 되는 이유는 Cursor완전히 제거되기 위해선 해당 Cursor를 사용하고 있는 다른 부분도 완전히 종료되어야 하기 때문입니다.

4. @@Fetch_Status

Cursor에서 가져온 Data가 존재하는지 여부를 확인하기 위해 쓰입니다. 0이면 Data가 존재하고 -1이면 존재하지 않음을 의미합니다.

Declare ex_cursor Cursor
For Select DepartmentID, Name, GroupName
 From HumanResources.Department
 
Open ex_cursor;
Declare @ID Int
Declare @Name nVarChar(50)
Declare @GroupName nVarChar(50)

Fetch
From ex_cursor
Into @ID, @Name, @GroupName;

Select @ID, @Name + ' - ' + @GroupName;

While @@Fetch_Status = 0
Begin
 Fetch
 From ex_cursor
 Into @ID, @Name, @GroupName;
 
 Select @ID, @Name + ' - ' + @GroupName;
End

Close ex_cursor;

Deallocate ex_cursor;


먼저 Fetch를 실행한뒤 Data를 가져오는데 성공하면(@@Fetch_Status = 0) While문을 통해 행단위 Data를 순차적으로 가져옵니다.


5. Insensitive

이 Option은 Cursor를 정의할때 Data들을 tempdb에 저장되도록 하여 원본과의 동일성을 차단합니다. 즉, 원본 Table의 내용이 바뀌어도 Cursor의 내용에는 변함이 없도록 하기 위한 것입니다.

Declare ex_cursor Insensitive Cursor
For Select DepartmentID, Name, GroupName
 From HumanResources.Department
 Where DepartmentID = 16
 
Open ex_cursor;
Declare @ID Int
Declare @Name nVarChar(50)
Declare @GroupName nVarChar(50)

Fetch
From ex_cursor
Into @ID, @Name, @GroupName;

Update HumanResources.Department
Set GroupName = 'Executive General'
Where DepartmentID = 16;

Select @ID, @Name, @GroupName;

Select DepartmentID, Name, GroupName
From HumanResources.Department
Where DepartmentID = 16;

Close ex_cursor;

Deallocate ex_cursor;


Insensitive를 적용하여 Cursor를 생성하였습니다. Fetch이후에 Update를 통하여 HumanResources.Department Table의 GroupName을 'Executive General and Administration' 에서 'Executive General'로 변경하고 Fetch를 통해 가져온 Data와 실제 Table의 내용을 같이 조회하였습니다.

 

비슷한 역활을 하는 것으로 Static이 있습니다. 기능은 Insensitive와 동일합니다.

 

Declare ex_cursor Cursor Static
For
Select DepartmentID, [Name], GroupName
From HumanResources.Department
Where DepartmentID = 10;

 

Static외에 Dynamic과 Keyset 옵션도 존재합니다.

 

Declare ex_cursor Cursor Keyset
For
Select DepartmentID, [Name], GroupName
From HumanResources.Department
Where DepartmentID = 10;

 

Keyset은 테이블에서 키에 해당하는 열만 tempdb에 복사하도록 합니다. Cursor가 이동할때 키에 해당하는 데이터는 실제 테이블에서 가져오게 되므로 데이터가 삭제나 변경을 그대로 반영할 수 있습니다. 다만 새로 삽입되는 행의 경우에는 tempdb에 해당 키값이 존재하지 않을 것이므로 Insert는 반영되지 않습니다.

 

Dynamic은 Cursor 포인터의 키만 tempdb에 복사하도록 합니다. Cursor의 포인터가 이동할때마다 그때 그때의 키가 복사되는 형태이므로 Keyset과 다르게 신규추가되는 행까지도 모두 반영이 가능합니다.

 

주의할 점은 Keyset이나 Dynamic은 모두 테이블에서 복사할 키열을 필요로 하므로 테이블에 키가 없다면 옵션을 지정하더라도 암시적으로 Static이 될 수 있다는 것입니다. 만약 Keyset이나 Dynamic을 지정했을때 정상적으로 처리할 수 없는 상황(테이블에 고유 인덱스가 없는 상황)이라면 type_warning 옵션을 지정해 오류를 발생시켜 명확히 오류상황임을 파악할 수 있습니다.

 

Declare ex_cursor Cursor Dynamic Type_warning

 

6. Cursor의 이동

 

Cursor는 기본적으로 처음부터 끝까지 이동되는 동작방식을 가지며 필요에 따라 이전과 다음으로의 자유로운 이동을 가능하게 할 수도 있습니다.

 

Declare ex_cursor Cursor Static Scroll
For
Select DepartmentID, [Name], GroupName
From HumanResources.Department
--Where DepartmentID = 10;

 

Cursor를 선언할때 Scroll 옵션을 지정했습니다. Scroll은 Cursor의 자유로운 이동을 가능하게 하는 것으로 별도로 지정하지 않으면 Scroll은 기본값입니다.

 

Declare @ID Int
Declare @Name nVarChar(50)
Declare @GroupName nVarChar(50)
Fetch Next From ex_cursor
Into @ID, @Name, @GroupName;
Select @ID, @Name, @GroupName;

 

Fetch Next 를 통해 Cursor를 다음으로 이동하도록 하였습니다. 따라서 위 쿼리를 실행할때마다 HumanResources.Department 테이블에서 하나씩 행의 값을 순서대로 가져올 것입니다.

 

Declare @ID Int
Declare @Name nVarChar(50)
Declare @GroupName nVarChar(50)
Fetch Prior From ex_cursor
Into @ID, @Name, @GroupName;
Select @ID, @Name, @GroupName;

 

이번에는 Fetch Prior를 통해 Cursor를 이전행으로 이동하도록 하였습니다.

 

Fetch First From ex_cursor

 

Cursor의 포인터를 처음으로 이동시킵니다.

 

Fetch Last From ex_cursor

 

Cursor의 포인터를 맨 마지막으로 이동시킵니다.

 

만약 Cursor가 뒤로 되돌아갈일이 없다면 Forward_only 옵션을 지정해 Cursor의 성능을 향상시킬 수 있습니다.

 

Declare ex_cursor Cursor Static Forward_only
For
Select DepartmentID, [Name], GroupName
From HumanResources.Department
--Where DepartmentID = 10;


7. Global

Cursor를 Open할때 Global Option을 사용하면 해당 Cursor를 다른곳에서도 사용핡 수 있게 됩니다.

예를 들어 어떤 한 Procedure에서 ex_cursor이라는 Cursor를 다음과 같이 Open한 경우

 

Open Global ex_cursor;


ex_cursor Cursor는 현재 Cursor를 열고 있는 Procedure뿐만이 아니라 전혀 다른 Procedure안에서도 ex_cursor Cursor를 다룰 수 있게 되는 것입니다.(예를 Procedure로 들었지만 SPID가 다른 사용자간의 공유도 가능해 집니다.)

 

Global이외에 Local로 지역커서를 설정할 수 있는데 이 경우에는 해당 Cursor를 열고 있는 세션안에서만 사용이 가능합니다. 이를 테면 Procedure안에서 사용하는 경우 Procedure의 수행이 종료되면 커서도 같이 소멸합니다. 다만 Procedure안에서 Output등의 방법으로 커서를 반환하는 형태라면 커서를 반환받은 변수등이 소멸해야만 비로소 Cursor도 같이 소멸됩니다.

 

아무것도 지정하지 않으면 기본값을 따라가며 기본값을 변경하지 않았다면 Global이 기본값이 됩니다.

 

기본값은 SSMS에서 해당 DB의 속성(Properties)에 들어가 Opetion -> Default Cursor 항목을 보면 확인할 수 있는데 이미 커서가 생성된 상태라면 sp_describe_cursor 프로시저를 통해 cursor의 상태를 확인함으로서 해당 Cursor가 Global인지 Local인지를 알 수 있습니다.

 

Declare @my_cursor Cursor;
Exec sp_describe_cursor @my_cursor Output, 'Global', 'ex_cursor';
Fetch Next From @my_cursor

 

인수중 ex_cursor는 확인하고자 하는 커서의 이름이므로 상황에 따라 바꿔야 하며 해당 Global인지 Local인지를 같이 지정하면 됩니다.

 

결과에서 cursor_scope가 2라면 Global, 1이면 지역커서를 의미하며 참고로 model이 1이면 Cursor가 Static, 2면 Keyset, 3이면 Dynamic임을 의미합니다.

'Programming > Microsoft SQL Server' 카테고리의 다른 글

[SQL] CTE  (0) 2011.03.07
[SQL] 뷰(VIEW)  (0) 2011.03.04
[SQL] 커서(Cursor)  (2) 2011.03.03
[SQL] 트리거(Trigger)  (2) 2011.03.02
[SQL] 사용자 정의 함수  (1) 2011.02.28
[SQL Server] 예약 작업(작업 Schuduling)  (0) 2011.02.23
2 0
Programming/Microsoft SQL Server
1. 숫자(정수)

 데이터형  크기  범위
 Begint  8byte  -9,223,372,036,854,775,808 ~ 9,223,372,036,854,775,807
 Int  4byte  -2,147,483,648 ~ -2,147,483,647
 Tinyint  2byte  0 ~ 255
 Bit  1byte  1, 0, Null
 Smallint  4byte  -32,768 ~ 32,767

2. 실수

(1) 고정형(Decimal, Numeric)
크기및 범위 : Numeric(전체크기, 소수점이하크기)

만일 Numeric(12, 2)라고 한다면 전체가 12자리이고 그 중에서 소수점이 2자리임을 의미합니다. 이때 전체크기가 1~9라면 5 byte, 10~19라면 9 byte, 20~28이라면 13 byte의 크기를 갖게 됩니다. 즉, 전체자리가 위와같이 증가한다면 크기는 4byte씩 늘어나는 것입니다.

또한 사용상 Decimal과 Numeric은 차이가 없습니다.

(2) 부동형실수(Float, Real)
크기및 범위 : Float(숫자)

Float에는 얼마만큼의 숫자가 들어가느냐에 따라 표현가능한 자리수가 달라집니다. 일반적으로 1~24까지 7자리(4 byte), 25~53까지 15자리(8 byte)표현이 가능합니다.

Real은 Float(24)와 같으며 7자리까지만 표현이 가능합니다.

Float과 Real은 상당히 큰 숫자를 다룰 수 있지만 근사치 데이터를 저장하는데, 실수를 다르는 경우 되도록 Decimal을 사용하는 것이 좋습니다.

3. 통화(통화형식은 소수점 이하 4자리수까지로 제한합니다.)

 데이터형  크기  범위
 Money  8byte  -922,337,203,685,477.5808 ~ 922,337,203,685,477.5807
 Smallmoney  4byte  -214,748.3648 ~ 214,748.3647

4. 문자(문자열)

 데이터형  크기  범위
 Char(n) / NChar(n)  입력된 n byte / 입력된 n * 2 byte  8000자 이하 / 4000자 이하
 Varchar(n) / NVarchar(n)  n에 입력된 크기와 상관없이 실제 사용된 만큼  8000자 이하 / 4000자 이하, MAX지정시 2기가 / 1,073,741,823자(단 MAX는 MS SQL 2008버전부터는 사용되지 않습니다.)
 Text / NText  231-1 byte / 230-1 byte  2,147,483,647자 / 1,073,741,823자

Text 나 NText 그리고 Image는 더이상 MS SQL Server에서 지원하지 않을 예정이니 사용하지 말고 대신 VarChar(Max), nVarChar(Max), Varbinary(Max)를 사용하는 것이 좋습니다.

Char는 고정형입니다. 예를 들어 Char(100)이라고 하면 100바이트 크기를 나타냅니다. 'abc'문자열을 저장하는 경우 3바이트만을 필요로 하지만 100중 3만 차지하고 나머지 97바이트는 버려집니다. 반면 VarChar는 가변형식으로서 VarChar(100)을 하더라도 'abc'를 저장할때는 3바이트만을 차지하기 때문에 공간효휼성은 더 좋습니다. 다만 성능은 Char형이 더 우위에 있습니다.

참고:
n으로 시작되는 형식은 유니코드문자용입니다.(대부분의 경우 크기는 n이 없는 형의 * 2 가 되며 저장할 수 있는 크기는 n이 없는 형의 절반입니다.)

5. 날짜형식

 데이터형  크기  범위
 Datetime  8byte  1753-01-01 ~ 9999-12-31(ms단위까지)
 SmallDateTime  4byte  1990-01-01 ~ 2079-06-09(분까지)

참고 :
년도를 4자리로 하지 않고 두자리만 지정시 년도는 다음과 같이 인식됩니다.
00 ~ 49 까지 : 2000년대
50 ~ 99 까지 : 1900년대

6. 이진형식

 데이터형  범위
 Binary(n) / VarBinary(n)  1~8000까지의 이진데이터(n을 생략시 1, cast에서 지정하지 않으면 30을 기본)
 Image  232-1byte(2,147,483,647 byte까지)

7. 시스템 형식

 데이터형  설명
 Cursor  커서
 Sql_Variant  IMAGE, NTEXT, NVARCHAR(MAX), TEXT, TIMESTAMP, VARCHAR(MAX), XML를 제외한 모든 자료형 대체가능
 Table  Table
 TimeStamp  TABLE의 행이 바뀔때마다 바뀌는 고유한 숫자(8 byte)
 Uniqueidentifier  Newid()를 통해 발생되는 고유값으로 16진수로 구성된 각 자리수가 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx형태로 구성되며 IS NULL이나 IS NOT NULL, =, <=, >=, <>, >, < 의 연산자만 허용합니다.
 Xml  Xml

8. 사용자 형식

(1) sp_addtype

sp_addtype 이름, '데이터형', null 여부, '소유자' 의 형태로 씁니다. null여부와 소유자는 생략할 수 있는데 이경우 null허용하게 되며 해당 데이터형은 현재사용자가 소유자가 되는것이 기본입니다.

sp_addtype postal, 'Char(07)';
Go

Declare @ipostal As postal;
Set @ipostal = '780-080';
Select @ipostal;


지정한 사용자 형식을 삭제하는 방법은 다음과 같습니다.

sp_droptype postal;

(2) Create Type

Create Type은 sp_addtype에 비해 소유자를 지정할 수 없을뿐 sp_addtype과 같습니다.
사용형식은 'Create Type 이름 From 데이터형, null 여부'로 구현됩니다.

Create Type postal From Char(07) Not Null;
Go

Declare @ipostal As postal;
Set @ipostal = '780-080'
Select @ipostal;


지정한 사용자 형식을 삭제하는 방법은 다음과 같습니다.

Drop Type postal;


9. ISO 표준 형식


MS SQL Server는 ISO와의 호환을 위해 다음 단어를 해당 형식으로 일치시켰습니다. 따라서 Char대신 Character를 사용할 수 있습니다.


 본래형식

 ISO형식

 Char

 Character

 VarChar

 Character Varying

 Varbinary

 Binary Varying

 Decimal

 Dec

 Float

 Double Precision

 Int

 Integer

 nChar

 National Character

 nVarChar

 National Character Varying

 RowVersion

 Timestamp


2 0
1
블로그 이미지

클리엘