CURSOR 사용
SQL서버는 모든 작업을 컬럼(열)기반으로 처리 합니다.
작업의 처리 방식은 컬럼에 대해서 수행 된다는 의미 입니다.
WHERE절은? 컬럼의 특정 값에 대해서 로우(행)을 선택할 뿐입니다.
커서는 로우를 기반으로 하는 작업이 된다!!! 라는 것입니다.
저러한 로우를 하나 SELECT해서 여러가지 처리를 한후 어떠어떠한 작업을 한다~~
이것을 가능하게 하는 것이 바로!!! 커서 입니다.
커서는
1. 커서 선언(Declare)
2. 커서 오픈(Open)
3. 데이터 행 가져오기(Fetch)
4. 커서 클로즈(Close)
5. 커서 선언 제거(Deallocate)
로 이루어 집니다.
--커서 선언
DECLARE cur_konan_Test CURSOR
FOR
SELECT 번호, 우편번호, 우편주소 FROM 우편물
--커서 오픈
OPEN cur_konan_Test
--첫 로우 FETCH
FETCH NEXT FROM cur_konan_Test INTO @v_번호, @v_우편번호, @v_우편주소
커서의 또한 중요한 녀석 입니다.!!! FETCH!!!
하나의 행을 반입해서 메모리에 올린다는 의미 입니다.
WHILE @@FETCH_STATUS = 0
BEGIN
--FETCH된 데이터를 tempdb에 삽입
UPDATE 우편물
SET 우편번호 = (SELECT 우편번호 FROM 우편번호 WHERE 우편주소 = @v_우편주소)
WHERE 번호 = @v_번호
WHILE @@FETCH_STATUS = 0 요녀석은!!! @@FETCH_STATUS 바로 요건
행 반입 상태를 의미 합니다.!!!
반환 값 설명
0 FETCH 문은 성공적이었습니다.
-1 FETCH 문은 실패했거나 행이 결과 집합의 범위를 벗어났습니다.
-2 반입된 행이 없습니다.
--다음 로우 FEETCH - 루프
FETCH NEXT FROM cur_konan_Test INTO @v_번호, @v_우편번호, @v_우편주소
END
--커서 CLOSE
CLOSE cur_konan_Test
--커서 DEALLOCATE
DEALLOCATE cur_konan_Test
GO
CURSOR 구문
-------------
1. DECLARE
-------------
SQL-92 구문
DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR
FOR select_statement
[ FOR { READ ONLY | UPDATE [ OF column_name [ ,...n ] ] } ]
Transact-SQL 확장 구문
DECLARE cursor_name CURSOR
[ LOCAL | GLOBAL ]
[ FORWARD_ONLY | SCROLL ]
[ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]
[ TYPE_WARNING ]
FOR select_statement
[ FOR UPDATE [ OF column_name [ ,...n ] ] ]
커서의 선언은 위와같은 구문을 가지고 이용 됩니다.
주의하실 점은 이 커서의 선언부는 커서의 속도에 매우 지대한 영향을 줍니다.
역시나 처음에 언급 드린대로 주로 사용하는 옵션을 먼저 설명을 드리겠습니다.
저는 SQL-92 표준을 가지고 주로 선언 합니다.
옵션들의 갯수와 영어를 보니 눈이 아프십니까? 찬찬히 풀어 보도록 하지요.
cursor_name
커서 이름을 의미 합니다.
INSENSITIVE
커서에서 사용할 데이터를 임시로 복사해 주는 커서를 정의합니다. 커서에 대한 모든 요청은 tempdb의 임시 테이블에서 응답하므로 기본 테이블에 대한 수정 사항은 해당 커서에 대한 반입으로 반환된 데이터에는 반영되지 않고 해당 커서는 수정할 수 없습니다. SQL-92 구문을 사용할 때 INSENSITIVE를 생략하면 원본으로 사용하는 테이블에서 커밋된 삭제 및 업데이트 작업이 향후 반입된 데이터에 반영됩니다.
SCROLL
모든 반입 옵션(FIRST, LAST, PRIOR, NEXT, RELATIVE, ABSOLUTE)을 사용할 수 있도록 지정합니다. SQL-92 DECLARE CURSOR에서 SCROLL을 지정하지 않으면 NEXT 반입 옵션만 지원됩니다. FAST_FORWARD가 지정된 경우에는 SCROLL을 지정할 수 없습니다.
select_statement
커서의 결과 집합을 정의하는 표준 SELECT 문입니다. 커서 선언의 select_statement에서 COMPUTE, COMPUTE BY, FOR BROWSE, INTO 키워드는 사용할 수 없습니다.Microsoft® SQL Server™는 select_statement의 절이 요청한 커서 형식의 기능과 충돌할 경우 커서를 다른 형식으로 암시적으로 변환합니다.
READ ONLY
이 커서를 통한 업데이트를 방지합니다. UPDATE 문이나 DELETE 문의 WHERE CURRENT OF 절에서는 이 커서를 참조할 수 없습니다. 이 옵션에서는 업데이트할 커서의 기본 기능을 무시합니다.
UPDATE [OF column_name [,...n]]
커서 내에서 업데이트할 수 있는 열을 정의합니다. OF column_name [,...n]을 지정하면 표시된 열만 수정할 수 있으나 열 목록 없이 UPDATE를 지정하면 모든 열을 업데이트할 수 있습니다.
다음으로 찬찬히 T-SQL 확장 구문에 대한 설명 입니다.
cursor_name
커서 이름 입니다.
LOCAL
커서가 만들어진 일괄 처리, 저장 프로시저, 트리거에서 커서 범위를 지역 커서로 지정합니다. 커서 이름은 지정된 범위에서만 유효합니다. 일괄 처리, 저장 프로시저, 트리거의 지역 커서 변수, 또는 저장 프로시저 OUTPUT 매개 변수에서 커서를 참조할 수 있습니다. OUTPUT 매개 변수는 지역 커서를 호출한 일괄 처리, 저장 프로시저, 트리거로 다시 전달하는 데 사용되며 저장 프로시저가 종료된 후 커서 변수에 매개 변수를 할당하여 커서를 참조할 수 있습니다. 커서가 OUTPUT 매개 변수에 다시 전달되지 않을 경우 일괄 처리나 저장 프로시저, 트리거가 종료되면 커서가 암시적으로 할당 해제됩니다. OUTPUT 매개 변수에서 커서가 다시 전달되면, 커서를 참조하는 마지막 변수의 할당이 취소되거나 공간이 부족할 때 커서의 할당이 취소됩니다.
GLOBAL
커서 범위를 연결의 전역 커서로 지정합니다. 연결되어 실행하는 모든 저장 프로시저 또는 일괄 처리에서 커서 이름을 참조할 수 있습니다. 커서는 연결 해제 시에만 암시적으로 할당이 해제됩니다. GLOBAL이나 LOCAL을 모두 지정하지 않으면 기본적으로 default to local cursor 데이터베이스 옵션의 설정에 따라 결정됩니다. SQL Server 7.0에서 이 옵션은 모든 커서가 전역 커서인 이전 버전의 SQL Server와 일치하도록 기본적으로 FALSE가 됩니다. SQL Server의 이후 버전에서는 이 옵션의 기본값이 변경됩니다.
FORWARD_ONLY
첫 번째 행에서 마지막 행 방향으로만 커서를 스크롤할 수 있도록 지정합니다. 유일하게 지원되는 반입 옵션은 FETCH NEXT입니다. STATIC, KEYSET, DYNAMIC 키워드를 사용하지 않고 FORWARD_ONLY를 지정하면 DYNAMIC 커서로 작동됩니다. STATIC, KEYSET, DYNAMIC 키워드가 지정된 경우를 제외하면 FORWARD_ONLY나 SCROLL을 모두 지정하지 않을 경우 FORWARD_ONLY가 기본값이 됩니다. STATIC, KEYSET, DYNAMIC 커서는 기본적으로 SCROLL이 됩니다. ODBC, ADO 등의 데이터베이스 API와 달리 STATIC, KEYSET, DYNAMIC Transact-SQL 커서에서는 FORWARD_ONLY가 지원됩니다. FAST_FORWARD와 FORWARD_ONLY는 서로 배타적입니다. 하나를 지정하면 다른 하나는 지정할 수 없습니다.
STATIC
커서에서 사용할 데이터를 임시로 복사해 주는 커서를 정의합니다. 커서에 대한 모든 요청은 tempdb의 임시 테이블에서 응답하므로 기본 테이블에 대한 수정 사항은 해당 커서에 대한 반입으로 반환된 데이터에는 반영되지 않고 해당 커서는 수정할 수 없습니다.
KEYSET
커서를 열 때 커서에 포함된 행의 구성원과 순서가 고정되도록 지정합니다. 행을 고유하게 식별하는 keyset라는 키 집합이 tempdb의 테이블에 구축됩니다. 커서 소유자나 다른 사용자가 기본 테이블에서 키가 아닌 값을 변경하면 커서 소유자가 커서를 스크롤할 때 변경 사항이 표시됩니다. 그러나 다른 사용자가 삽입한 데이터는 볼 수 없습니다(Transact-SQL 서버 커서를 통해 삽입할 수 없음). 삭제된 행을 반입하려고 하면 @@FETCH_STATUS가 -2로 반환됩니다. 커서 외부에서 키 값을 업데이트하는 것은 이전 행을 삭제하고 새 행을 삽입하는 것과 같습니다. 새 값을 가진 행을 볼 수 없으므로 이전 값을 가진 행을 반입하려고 하면 @@FETCH_STATUS가 -2로 반환됩니다. WHERE CURRENT OF 절을 지정하여 커서를 통해 업데이트를 수행한 경우에는 새 값을 볼 수 있습니다.
DYNAMIC
커서를 스크롤할 때 결과 집합에 행에 대한 모든 데이터 변경 사항이 반영되는 커서를 정의합니다. 따라서 반입할 때마다 행의 데이터 값과 순서, 구성원이 변경될 수 있습니다. 동적 커서에서는 ABSOLUTE 반입 옵션이 지원되지 않습니다.
FAST_FORWARD
전진전용, 읽기 전용으로 가장 빠릅니다
성능 최적화가 설정된 FORWARD_ONLY, READ_ONLY 커서를 지정합니다. SCROLL 또는 FOR_UPDATE가 지정된 경우에는 FAST_FORWARD를 지정할 수 없습니다. FAST_FORWARD와 FORWARD_ONLY는 서로 배타적입니다. 하나를 지정하면 다른 하나는 지정할 수 없습니다.
READ_ONLY
이 커서를 통한 업데이트를 방지합니다. UPDATE 문이나 DELETE 문의 WHERE CURRENT OF 절에서는 이 커서를 참조할 수 없습니다. 이 옵션에서는 업데이트할 커서의 기본 기능을 무시합니다.
SCROLL_LOCKS
커서를 통해 수행되는 위치 지정 업데이트나 삭제가 성공하도록 지정합니다. Microsoft® SQL Server™는 커서로 행을 읽어들일 때 나중에 수정할 수 있도록 행을 잠급니다. FAST_FORWARD가 지정된 경우에는 SCROLL_LOCKS를 지정할 수 없습니다.
OPTIMISTIC
커서로 행을 읽어들인 후 해당 행이 업데이트 된 경우 커서를 통해 수행되는 위치 지정 업데이트나 삭제가 성공하지 못하도록 지정합니다. 이 경우 SQL Server는 커서로 행을 읽어들일 때 잠그지 않습니다. 대신 timestamp 열 값을 비교하거나 테이블에 timestamp 열이 없을 경우 검사값을 비교하여 커서로 행을 읽어들인 후 해당 행이 수정되었는지를 확인합니다. 행이 수정된 경우 시도한 위치 지정 업데이트나 삭제가 실패합니다. FAST_FORWARD가 지정된 경우에는 OPTIMISTIC을 지정할 수 없습니다.
TYPE_WARNING
요청한 커서 형식이 다른 형식으로 암시적으로 변환된 경우 클라이언트에게 경고 메시지를 보냅니다.
select_statement
커서의 결과 집합을 정의하는 표준 SELECT 문입니다. 커서 선언의 select_statement에서 COMPUTE, COMPUTE BY, FOR BROWSE, INTO 키워드는 사용할 수 없습니다.
select_statement의 절이 요청한 커서 형식의 기능과 충돌할 경우 SQL Server는 커서를 다른 형식으로 암시적으로 변환합니다.
UPDATE [OF column_name [,...n]]
커서 내에서 업데이트할 수 있는 열을 정의합니다. OF column_name [,...n]을 지정하면 표시된 열만 수정할 수 있습니다. READ_ONLY 병행성 옵션이 지정된 경우를 제외하면 열 목록 없이 UPDATE를 지정할 경우 모든 열을 업데이트할 수 있습니다.
비고
DECLARE CURSOR는 스크롤 동작, 커서가 작동하는 결과 집합을 구축하는 데 사용되는 쿼리 등 Transact-SQL 서버 커서의 속성을 정의합니다. OPEN 문은 결과 집합을 채우고 FETCH는 결과 집합에서 행을 반환합니다. CLOSE 문은 커서와 연결된 현재 결과 집합을 해제하며 DEALLOCATE 문은 커서에서 사용된 자원을 해제합니다.
DECLARE CURSOR 문의 첫 번째 형식은 커서 동작을 선언하기 위해 SQL-92 구문을 사용합니다. DECLARE CURSOR 문의 두 번째 형식은 ODBC, ADO, DB-Library의 데이터베이스 API 커서 함수에서 사용된 것과 동일한 커서 형식을 사용하여 커서를 정의할 수 있는 Transact-SQL 확장을 사용합니다.
위의 두 가지 형식을 함께 사용할 수는 없습니다. CURSOR 키워드 앞에 SCROLL 또는 INSENSITIVE 키워드를 지정하면 CURSOR와 FOR select_statement 키워드 사이에 어떤 키워드도 사용할 수 없습니다. CURSOR와 FOR select_statement 사이에 키워드를 지정하면 CURSOR 키워드 앞에 SCROLL 또는 INSENSITIVE를 지정할 수 없습니다.
Transact-SQL 구문을 사용하는 DECLARE CURSOR에서 READ_ONLY, OPTIMISTIC, SCROLL_LOCKS를 지정하지 않을 경우 기본값은 다음과 같습니다.
권한 부족, 업데이트를 지원하지 않는 원격 테이블 액세스 등의 이유로 SELECT 문이 업데이트를 지원하지 않을 경우 커서는 READ_ONLY가 됩니다.
STATIC 및 FAST_FORWARD 커서는 기본적으로 READ_ONLY가 됩니다.
DYNAMIC 및 KEYSET 커서는 기본적으로 OPTIMISTIC이 됩니다.
시스템 저장 프로시저 설명
sp_cursor_list 현재 연결에서 볼 수 있는 커서 목록과 그 속성을 반환합니다.
sp_describe_cursor 전진 전용 커서, 스크롤 커서 등의 커서 속성을 설명합니다.
sp_describe_cursor_columns 커서 결과 집합에서 열의 속성을 설명합니다.
sp_describe_cursor_tables 커서에 의해 액세스되는 기본 테이블을 설명합니다.
------------
OPEN 구문
------------
문법
OPEN { { [ GLOBAL ] cursor_name } | cursor_variable_name }
GLOBAL
cursor_name이 전역 커서를 가리키도록 지정합니다.
cursor_name
선언된 커서의 이름입니다. 동일한 cursor_name을 가진 전역 커서와 로컬 커서가 모두 존재할 경우 GLOBAL이 지정되어 있으면 cursor_name이 전역 커서를 가리키고 그렇지 않으면 로컬 커서를 가리킵니다.
cursor_variable_name
커서를 참조하는 커서 변수의 이름입니다.
--------------
FETCH
--------------
FETCH
[ [ NEXT | PRIOR | FIRST | LAST
| ABSOLUTE { n | @nvar }
| RELATIVE { n | @nvar }
]
FROM
]
{ { [ GLOBAL ] cursor_name } | @cursor_variable_name }
[ INTO @variable_name [ ,...n ] ]
NEXT
현재 행 바로 다음의 결과 행을 반환하며 현재 행을 반환되는 행 앞의 행으로 만듭니다. 커서에 대해 FETCH NEXT가 첫 번째 반입인 경우에는 결과 집합의 첫 번째 행을 반환합니다. NEXT는 기본 커서 반입 옵션입니다.
PRIOR
현재 행 바로 앞의 결과 행을 반환하며 현재 행을 반환되는 행 뒤의 행으로 만듭니다. 커서에 대해 FETCH PRIOR가 첫 번째 반입인 경우에는 행이 반환되지 않으며 커서는 첫 번째 행 앞에 위치하게 됩니다.
FIRST
커서의 첫 번째 행을 반환하며 그 행을 현재 행으로 만듭니다.
LAST
커서의 마지막 행을 반환하며 그 행을 현재 행으로 만듭니다.
ABSOLUTE {n | @nvar}
n 또는 @nvar이 양수인 경우에는 커서 맨 앞에서부터 n 번째 행에 있는 행을 반환하며 반환되는 행을 새 현재 행으로 만듭니다. n 또는 @nvar이 음수인 경우에는 커서 맨 뒤에서부터 n 번째 행에 있는 행을 반환하며 반환되는 행을 새 현재 행으로 만듭니다. n 또는 @nvar이 0인 경우에는 행이 반환되지 않습니다. n은 반드시 정수 상수여야 하며 @nvar은 반드시 smallint, tinyint, int 중 하나여야 합니다.
RELATIVE {n | @nvar}
n 또는 @nvar이 양수인 경우에는 현재 행에서부터 n 번째 행에 있는 행을 반환하며 반환되는 행을 새 현재 행으로 만듭니다. n 또는 @nvar이 음수인 경우에는 현재 행에서부터 앞으로 n 번째 행에 있는 행을 반환하며 반환되는 행을 새 현재 행으로 만듭니다. n 또는 @nvar이 0인 경우에는 현재 행을 반환합니다. 커서에 대해 처음으로 실행된 반입에 음수 또는 0으로 설정된 n 또는 @nvar를 사용하여 FETCH RELATIVE를 지정한 경우에는 행이 반환되지 않습니다. n은 반드시 정수 상수여야 하며 @nvar은 smallint, tinyint, int 중 하나여야 합니다.
GLOBAL
cursor_name이 전역 커서를 가리키도록 지정합니다.
cursor_name
반입이 수행되어야 하는 열려진 커서의 이름입니다. 동일한 cursor_name을 가진 전역 커서와 지역 커서가 있을 경우, GLOBAL이 지정되면 cursor_name에 전역 커서를 사용하고 GLOBAL이 지정되지 않으면 지역 커서를 사용합니다.
@cursor_variable_name
수행할 반입에서 열려진 커서를 참조하는 커서 변수의 이름입니다.
INTO @variable_name[,...n]
데이터를 반입의 열에서 지역 변수로 가져가도록 허용합니다. 목록의 각 변수는 왼쪽에서 오른쪽 순으로 커서 결과 집합의 해당 열과 연관됩니다. 각 변수의 데이터 형식은 반드시 해당 결과 집합 열의 데이터 형식과 일치하거나 암시적 변환이 지원되어야 합니다. 변수의 개수는 커서 선택 목록의 열 수와 일치해야 합니다.
비고
SQL-92 스타일 DECLARE CURSOR 문에서 SCROLL 옵션을 지정하지 않는 경우, 유일하게 지원되는 FETCH 옵션은 NEXT입니다. SQL-92 스타일 DECLARE CURSOR커서에서 SCROLL을 지정하면 모든 FETCH 옵션이 지원됩니다.
Transact_SQL DECLARE 커서 확장을 사용하는 경우에는 다음 규칙이 적용됩니다.
FORWARD-ONLY나 FAST_FORWARD를 지정한 경우에는 유일하게 지원되는 FETCH 옵션이 NEXT입니다.
DYNAMIC, FORWARD_ONLY 또는 FAST_FORWARD를 지정하지 않고 KEYSET, STATIC, SCROLL 중 하나를 지정한 경우에는 모든 FETCH 옵션이 지원됩니다.
DYNAMIC SCROLL 커서는 ABSOLUTE를 제외한 모든 FETCH 옵션을 지원합니다.
@@FETCH_STATUS 함수는 마지막 FETCH 문의 상태를 보고합니다. sp_describe_cursor에 의해 반환되는 커서의 fetch_status 열에도 동일한 정보가 기록됩니다. 해당 데이터에 대해 어떠한 작업을 수행하려고 시도하기 전에 반드시 이 상태 정보를 사용하여 FETCH 문이 반환하는 데이터의 유효성을 확인해야 합니다.
----------------
CLOSE
----------------
현재 결과 집합을 해제하고 커서가 위치한 행에 보유된 커서 잠금을 해제하여 열린 커서를 닫습니다. CLOSE를 실행해도 데이터 구조를 액세스하여 다시 열 수 있지만 커서를 다시 열 때까지 반입과 위치 지정 업데이트는 허용되지 않습니다. CLOSE는 열려 있는 커서에만 실행할 수 있으며 선언만 되었거나 이미 닫혀 있는 커서에는 사용할 수 없습니다.
CLOSE { { [ GLOBAL ] cursor_name } | cursor_variable_name }
인수
GLOBAL
cursor_name이 전역 커서를 가리키도록 지정합니다.
cursor_name
열린 커서의 이름입니다. 동일한 cursor_name을 가진 전역 커서와 로컬 커서가 존재할 경우 GLOBAL이 지정되어 있으면 전역 커서를 가리키고 그렇지 않으면 로컬 커서를 가리킵니다.
cursor_variable_name
열린 커서와 연관된 커서 변수의 이름입니다.
DEALLOCATE
커서 참조를 제거합니다. 마지막 커서 참조가 할당 해제될 경우 Microsoft® SQL Server™에서 커서를 구성하는 데이터 구조가 해제됩니다.
구문
DEALLOCATE { { [ GLOBAL ] cursor_name } | @cursor_variable_name }
인수
cursor_name
이미 선언된 커서의 이름입니다. 동일한 cursor_name을 가진 전역 커서와 지역 커서가 있을 경우, GLOBAL이 지정되면 cursor_name은 전역 커서를 참조하고 GLOBAL이 지정되지 않으면 지역 커서를 참조합니다.
@cursor_variable_name
cursor 변수의 이름입니다. @cursor_variable_name은 cursor 형식이어야 합니다.
SET NOCOUNT ON
DECLARE cur_konan_Test CURSOR FAST_FORWARD
FOR
SELECT name FROM sysobjects WHERE xtype='U'
OPEN cur_konan_Test
DECLARE @v_name VARCHAR(100)
FETCH NEXT FROM cur_konan_Test INTO @v_name
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT 'SELECT COUNT(*) FROM ' +@v_name
FETCH NEXT FROM cur_konan_Test INTO @v_name
END
--커서 CLOSE
CLOSE cur_konan_Test
--커서 DEALLOCATE
DEALLOCATE cur_konan_Test
GO