데이터베이스 커서
"오늘의AI위키"의 AI를 통해 더욱 풍부하고 폭넓은 지식 경험을 누리세요.
1. 개요
데이터베이스 커서는 SQL 프로시저에서 데이터를 처리하기 위한 도구로, 결과 집합을 정의하고, 데이터를 한 행씩 가져오거나, 데이터베이스의 특정 행을 수정 및 삭제하는 데 사용된다. SQL, ODBC, XQuery, DBM 등 다양한 환경에서 커서를 활용하며, 스크롤 가능, 보류 가능 커서와 같은 다양한 유형이 존재한다. 커서는 네트워크 왕복 및 서버 자원 사용으로 인한 성능 저하와 메모리 누수와 같은 단점을 가질 수 있으므로, 사용 시 주의가 필요하다.
SQL 프로시저에서 커서를 사용하는 일반적인 단계는 다음과 같다.[1]
관계형 데이터베이스의 질의어로 널리 사용되는 SQL에서는 커서를 이용한 데이터 접근을 위해 여러 명령문을 제공한다. SQL:2003 표준에서는 애플리케이션에서 커서를 사용하는 방법을 정의하고 있으며, 이 섹션에서는 해당 표준을 기준으로 설명한다. 다만, 모든 관계형 데이터베이스 시스템의 애플리케이션 바인딩이 이 표준을 따르는 것은 아니며, CLI나 JDBC 등 다른 인터페이스를 사용하는 경우도 있다.
2. 사용 방법
# 결과 집합을 정의하는 커서를 선언한다.
# 결과 집합을 만들기 위해 커서를 연다.
# 필요에 따라 커서에서 데이터를 한 번에 한 행씩 가져온다.
# 작업이 끝나면 커서를 닫는다.
이 내용은 SQL:2003 표준에서 정의된 커서 사용 방법을 기준으로 한다. 하지만 모든 관계형 데이터베이스 시스템이 이 표준을 따르는 것은 아니며, CLI나 JDBC와 같은 다른 인터페이스를 사용하기도 한다.[1]
커서를 사용하기 위한 주요 SQL 문은 다음과 같다.
DECLARE 커서_이름 CURSOR IS SELECT ... FROM ...
OPEN 커서_이름
FETCH 커서_이름 INTO ...
만약 더 이상 가져올 행이 없으면(결과 집합의 끝에 도달하면), DBMS는 SQLSTATE '02000' (보통 SQLCODE +100과 함께) 값을 반환하여 더 이상 데이터가 없음을 알린다.[1]
CLOSE 커서_이름
커서를 닫은 후에도 필요하다면 다시 `OPEN` 문으로 열 수 있다. 이때 DBMS는 쿼리를 다시 실행하여 새로운 결과 집합을 만든다.[1]
3. SQL에서의 커서
SQL 프로시저에서 커서를 사용하기 위한 일반적인 단계는 다음과 같다.
1. 커서 선언 (DECLARE): `DECLARE CURSOR` 문을 사용하여 DBMS에 커서를 알리고 이름을 할당한다. 이 단계에서 커서가 참조할 SELECT 문을 정의하여 결과 집합을 지정한다.
```sql
DECLARE 커서_이름 CURSOR FOR SELECT ... FROM ...
```
2. 커서 열기 (OPEN): `OPEN` 문을 사용하여 커서를 열고, 정의된 SELECT 문을 실행하여 결과 집합을 생성한다. 커서가 성공적으로 열리면, 결과 집합의 첫 번째 행 앞에 위치하게 된다.
```sql
OPEN 커서_이름
```
3. 데이터 가져오기 (FETCH): `FETCH` 문을 사용하여 결과 집합의 특정 행으로 커서를 이동시키고 해당 행의 데이터를 애플리케이션으로 가져온다. 일반적으로 `FETCH`는 현재 행의 데이터를 가져온 후 커서를 다음 행으로 이동시킨다.
```sql
FETCH 커서_이름 INTO ...
```
더 이상 가져올 행이 없을 때 `FETCH`를 시도하면, DBMS는 결과 집합의 끝에 도달했음을 알리기 위해 SQLSTATE '02000'(보통 SQLCODE +100과 함께)을 반환한다.
4. 커서 닫기 (CLOSE): `CLOSE` 문을 사용하여 커서 사용을 종료하고 관련 자원을 해제한다. 닫힌 커서는 다시 `OPEN` 문으로 열 수 있으며, 이때 DBMS는 쿼리를 다시 평가하여 새로운 결과 집합을 생성한다.
```sql
CLOSE 커서_이름
```
또한, 커서는 데이터를 가져오는 것 외에도 UPDATE나 DELETE 문과 함께 사용하여 특정 행의 데이터를 수정하거나 삭제하는 데 활용될 수 있다. 이는 `WHERE CURRENT OF 커서_이름` 절을 통해 이루어지며, 자세한 내용은 #위치 지정 UPDATE/DELETE 문 섹션에서 다룬다.
커서는 선언 방식에 따라 스크롤 가능 여부나 트랜잭션 종료 시 동작 방식이 달라질 수 있다. 자세한 내용은 #스크롤 가능 커서 및 #"WITH HOLD" 커서 섹션을 참조하라.
3. 1. 스크롤 가능 커서
프로그래머는 커서를 스크롤 가능 또는 스크롤 불가능으로 선언할 수 있다. 스크롤 가능 여부는 커서가 이동할 수 있는 방향을 나타낸다.
'''스크롤 불가능''' (또는 '''전방향''') 커서는 각 행을 최대 한 번만 가져올 수 있으며(''FETCH''), 커서는 자동으로 다음 행으로 이동한다. 마지막 행을 가져온 후 다시 가져오기를 시도하면 커서는 마지막 행 뒤에 위치하게 되고, 특정 SQL 상태 코드(''SQLSTATE 02000'', ''SQLCODE +100'')를 반환한다.
'''스크롤 가능''' 커서는 ''FETCH'' SQL 문을 사용하여 결과 집합 내의 어느 위치에나 배치할 수 있다. 스크롤 가능 커서를 사용하려면 선언할 때 ''SCROLL'' 키워드를 명시적으로 지정해야 한다. 기본값은 ''NO SCROLL''이지만, JDBC와 같은 다른 언어 바인딩에서는 다른 기본값을 적용할 수도 있다.
DECLARE ''커서_이름'' ''민감도'' '''SCROLL''' CURSOR FOR SELECT ... FROM ...
스크롤 가능 커서가 가져올(''FETCH'') 대상 위치는 다음과 같이 지정할 수 있다.
FETCH [ NEXT | PRIOR | FIRST | LAST ] FROM ''커서_이름''
FETCH ABSOLUTE ''n'' FROM ''커서_이름''
FETCH RELATIVE ''n'' FROM ''커서_이름'';
스크롤 가능 커서는 결과 집합의 동일한 행에 여러 번 접근할 수 있다는 특징이 있다. 이 때문에 다른 트랜잭션에서 수행된 데이터 수정(삽입, 업데이트, 삭제 작업)이 커서가 보고 있는 결과 집합에 영향을 미칠 수 있다. 커서는 이러한 데이터 수정에 대해 민감(''SENSITIVE'')하거나 비민감(''INSENSITIVE'')하게 동작하도록 설정할 수 있다.
또한, 커서는 ''ASENSITIVE''하게 설정될 수도 있는데, 이 경우 DBMS는 가능한 범위 내에서 최대한 민감도를 적용하려고 시도한다.
3. 2. "WITH HOLD" 커서
일반적으로 커서는 트랜잭션이 종료될 때, 즉 COMMIT 또는 ROLLBACK (또는 트랜잭션의 암묵적인 종료)이 발생하면 자동으로 닫힌다. 하지만 커서를 선언할 때 `'''WITH HOLD'''` 절을 사용하면 이러한 동작을 변경할 수 있다 (기본값은 `WITHOUT HOLD`).
`'''WITH HOLD'''`로 선언된 커서, 즉 보류 가능한 커서는 COMMIT 이후에도 열린 상태를 유지하고, ROLLBACK 시에만 닫힌다. (일부 DBMS는 이 표준 동작과 다르게 ROLLBACK 이후에도 `'''WITH HOLD'''` 커서를 열린 상태로 유지하기도 한다.)
`'''WITH HOLD'''` 커서는 다음과 같이 선언한다:
`DECLARE ''커서_이름'' CURSOR '''WITH HOLD''' FOR SELECT .... FROM ....`
COMMIT이 발생하면, `'''WITH HOLD'''` 커서는 다음 행 앞에 위치하게 된다. 따라서 위치 지정 UPDATE나 위치 지정 DELETE 문은 트랜잭션 내에서 COMMIT 후 반드시 `FETCH` 연산을 먼저 수행해야 성공적으로 실행될 수 있다.
JDBC는 기본적으로 커서를 보류 가능하도록 정의하는데, 이는 JDBC가 기본적으로 자동 커밋(Auto-commit)을 활성화하기 때문이다.
3. 3. 위치 지정 UPDATE/DELETE 문
커서는 DBMS에서 애플리케이션으로 데이터를 가져오는 용도 외에도, 업데이트하거나 삭제할 테이블의 특정 행을 식별하는 데 사용될 수 있다. SQL:2003 표준은 이러한 목적을 위해 위치 지정 업데이트(positioned update)와 위치 지정 삭제(positioned delete) SQL 문을 정의한다.
이 문들은 조건을 명시하는 일반적인 WHERE 절 대신, 커서를 사용하여 조작할 행을 직접 지정한다. 위치 지정 문을 사용하려면, 커서는 반드시 열려 있어야 하며 `FETCH` 문을 통해 특정 행에 위치해야 한다.
위치 지정 UPDATE 문의 기본 형식은 다음과 같다:
```sql
UPDATE table_name
SET ...
WHERE CURRENT OF cursor_name
```
위치 지정 DELETE 문의 기본 형식은 다음과 같다:
```sql
DELETE
FROM table_name
WHERE CURRENT OF cursor_name
```
이러한 위치 지정 문이 성공적으로 실행되려면, 커서가 가리키는 결과 집합이 업데이트 가능해야 한다. 만약 결과 집합이 업데이트 불가능하다면(예: 여러 테이블을 조인한 결과), DBMS는 변경 사항을 어느 테이블에 적용해야 할지 알 수 없으므로 오류가 발생할 수 있다.
관계형 데이터베이스에서 널리 사용되는 SQL은 커서를 이용한 데이터 접근 및 조작을 위한 여러 명령문을 제공한다. 커서는 `DECLARE CURSOR` 문으로 선언하고, `FETCH` 문으로 커서가 가리키는 위치의 데이터를 가져오면서 다음 데이터 행으로 이동한다. 데이터를 변경하는 `UPDATE` 문과 삭제하는 `DELETE` 문에서는 `WHERE CURRENT OF 커서명` 구문을 사용하여 현재 커서 위치의 데이터를 조작할 수 있다. 커서 사용이 끝나면 `CLOSE` 문으로 커서를 닫아 자원을 해제해야 한다. 구체적인 SQL 문법은 SQL#커서 정의・조작 항목에서 더 자세히 확인할 수 있다.
4. ODBC에서의 커서
C 언어 프로그램에서 SQL을 사용하여 데이터베이스에 액세스하기 위한 라이브러리 중 하나로 ODBC가 있다. ODBC는 Microsoft Windows에서 널리 사용된 후, 업계 표준인 SQL/CLI 및 JDBC의 기반이 되었다.
커서 조작과 관련된 SQL 문에는 다음과 같이 개별 ODBC 함수가 대응하고 있다.
| ODBC 함수 | 대응하는 SQL 문 |
|---|---|
| SQLPrepare | DECLARE CURSOR |
| SQLExecute | OPEN |
| SQLFetch | FETCH |
| SQLFreeStmt | CLOSE |
ODBC에서는, 응용 프로그램이 커서를 직접 지정하는 경우는 거의 없다. 위의 함수는 모두 "Statement Handle"을 인수로 하며[1], 이 핸들이 가리키는 데이터 구조 안에 커서도 포함되어 있다고 간주한다. 즉, Statement Handle이 이터레이터 역할을 한다.
5. XQuery에서의 커서
XQuery 언어는 '''subsequence()''' 함수를 사용하여 커서를 생성할 수 있다.
형식은 다음과 같다.
let $displayed-sequence := subsequence($result, $start, $item-count)
여기서 ''$result''는 초기 XQuery의 결과이고, ''$start''는 시작할 항목 번호이며, ''$item-count''는 반환할 항목의 수이다.
동일한 기능은 술어(predicate)를 사용하여 수행할 수도 있다.
let $displayed-sequence := $result[$start to $end]
여기서 ''$end''는 마지막 항목의 번호이다.
6. DBM에서의 커서
dbm은 관계형 데이터베이스보다 단순하며, 키와 값의 대응만으로 이루어진 데이터베이스 기능을 제공한다. 여기에서도 데이터베이스상의 "현재 위치"를 나타내기 위해 "커서"라는 용어가 사용된다[2].
7. 주의 사항
커서는 실행 중인 검색 조건이나 현재 위치를 유지하기 위해 메모리 등의 자원을 필요로 한다. 따라서 커서를 사용한 후 제대로 해제하지 않으면 메모리 누수가 발생할 수 있다.
표준 SQL 규격에서는 트랜잭션이 종료되면 커서가 자동으로 파기되도록 명시하고 있다. 그러나 일부 DBMS에서는 트랜잭션이 종료된 후에도 커서가 파기되지 않고 남아 있어 계속 데이터에 접근할 수 있도록 허용하기도 한다. 표준 SQL 규격을 준수하는 방식으로 개발한다면, 트랜잭션 종료 후에는 사용된 커서를 명시적으로 파기해야 한다.
분산 트랜잭션(X/Open XA 환경)에서 커서를 사용하는 것은 일반적으로 트랜잭션 모니터를 통해 제어되며, 분산되지 않은 환경과 크게 다르지 않다. 하지만 "WITH HOLD" 옵션으로 생성된 홀드 가능(holdable) 커서를 사용할 때는 특별한 주의가 필요하다. 이러한 커서는 트랜잭션이 커밋된 후에도 유지될 수 있으며, 해당 데이터베이스 연결이 다른 애플리케이션에 의해 재사용될 경우, 후속 트랜잭션(다른 애플리케이션에서 실행)이 기존의 홀드 가능 커서를 예기치 않게 상속받을 수 있다. 애플리케이션 개발자는 이러한 상황을 인지하고 있어야 한다.
8. 단점
커서는 실행 중인 검색 조건이나 현재 위치를 유지하기 위해 메모리와 같은 자원을 많이 사용한다. 만약 사용한 커서를 제대로 닫지 않고 남겨두면(할당 해제), 이는 메모리 누수로 이어질 수 있다.[1]
커서를 사용하여 데이터를 한 행씩 가져올 때마다 네트워크 왕복이 발생할 수 있다. 이는 DELETE와 같은 단일 SQL 문을 한 번 실행하는 것보다 훨씬 많은 네트워크 대역폭을 사용하게 만들며, 결과적으로 커서를 사용하는 작업의 속도를 크게 떨어뜨릴 수 있다. 일부 DBMS는 이러한 문제를 줄이기 위해 '블록 페치'라는 기술을 사용하기도 한다. 블록 페치는 여러 행의 데이터를 한 번에 서버에서 클라이언트로 보내는 방식이다. 클라이언트는 이 데이터 묶음을 로컬 버퍼에 저장해두고, 버퍼의 데이터가 소진될 때까지 네트워크 통신 없이 행을 가져올 수 있다.[2]
또한 커서는 서버에 잠금, 패키지, 프로세스, 임시 저장 공간 등 다양한 자원을 할당하게 만든다. 예를 들어, 마이크로소프트 SQL 서버는 커서를 구현하기 위해 임시 테이블을 생성하고 그곳에 쿼리 결과 집합을 저장하기도 한다. 만약 커서를 사용한 뒤 제대로 닫지 않으면, 해당 SQL 세션(연결)이 종료될 때까지 이 자원들은 계속 점유된 상태로 남게 된다. 이렇게 서버 자원이 낭비되면 시스템 성능이 저하되거나 심각한 장애가 발생할 수도 있다.
표준 SQL 규격에 따르면, 트랜잭션이 종료될 때 커서도 함께 파기(삭제)되어야 한다. 하지만 실제 DBMS 중에는 트랜잭션이 종료된 후에도 커서가 파기되지 않고 계속 데이터를 접근할 수 있도록 하는 경우도 있다. 표준 SQL 규격을 준수하려면 트랜잭션이 종료된 후에는 반드시 커서를 파기해야 한다.
참조
[1]
웹사이트
ODBC Function Summary
http://msdn.microsof[...]
2008-06-07
[2]
웹사이트
dbopen
http://linuxjm.osdn.[...]
2008-06-07
본 사이트는 AI가 위키백과와 뉴스 기사,정부 간행물,학술 논문등을 바탕으로 정보를 가공하여 제공하는 백과사전형 서비스입니다.
모든 문서는 AI에 의해 자동 생성되며, CC BY-SA 4.0 라이선스에 따라 이용할 수 있습니다.
하지만, 위키백과나 뉴스 기사 자체에 오류, 부정확한 정보, 또는 가짜 뉴스가 포함될 수 있으며, AI는 이러한 내용을 완벽하게 걸러내지 못할 수 있습니다.
따라서 제공되는 정보에 일부 오류나 편향이 있을 수 있으므로, 중요한 정보는 반드시 다른 출처를 통해 교차 검증하시기 바랍니다.
문의하기 : help@durumis.com