8. 커서 처리 SQL문#
개요#
질의 결과로 여러 개의 레코드가 반환될 경우 커서를 선언해서 사용할 수 있다.
APRE는 커서를 선언하고 조작하는 다양한 내장 SQL문을 지원한다. 커서 관련 내장 SQL문은 커서 선언문, 커서 OPEN문, 커서 FETCH문, 커서 CLOSE문, 커서 CLOSE RELEASE문이 있다.
커서 처리 SQL문의 사용 순서#
커서 처리 SQL문의 사용순서는 다음과 같다.
- 커서 선언문
- 커서 OPEN문
- 커서 FETCH문
조건에 맞는 레코드를 모두 가져오기 위해서는 수행 결과가 SQL_NO_DATA일 때까지 커서 FETCH문을 반복 수행해야 한다. - 커서 CLOSE문 또는 커서 CLOSE RELEASE문
주의 사항#
선언되지 않은 커서 이름으로 커서 OPEN문, 커서 FETCH문, 커서 CLOSE문, 커서 CLOSE RELEASE문을 수행할 경우 "The cursor does not exist." 오류가 발생한다.
한 프로그램 내에서 같은 커서 이름을 하나 이상 선언할 수 있다. 이 경우 실행 시간에 가장 최근에 선언한 커서만이 유효하다. 즉, OPEN, FETCH, CLOSE, CLOSE RELEASE문은 가장 최근에 선언한 커서를 적용한다는 뜻이다.
커서 관련 내장 SQL문#
여기에서는 각각의 커서 관련 내장 SQL문들에 대해 자세히 알아보기로 하자.
DECLARE CURSOR#
커서를 선언한다.
구문#
EXEC SQL DECLARE <cursor name>
[SENSITIVE | INSENSITIVE] [SCROLL]
CURSOR [WITH HOLD] FOR <cursor specification>;
인자#
- <cursor name>
커서 이름. 커서 이름은 알파벳(a~z, A~Z), 밑줄("_") 또는 달러 기호("$")로 시작하여야 하며, 길이는 50 bytes로 제한된다. - SENSITIVE | INSENSITIVE
sensitive 커서는 데이터 수정을 커서의 결과 집합에 반영한다. 즉, 행 집합을 다시 가져올 필요가 있는 경우, 데이터베이스에서 최신 데이터를 가져온다. insensitive 커서는 커서가 열린 시점의 데이터를 커서의 결과 집합에 유지한다. - SCROLL
결과 집합에서 커서의 위치를 임의로 이동 가능하다. SCROLL과 SENSITIVE를 함께 사용해야 효과가 있다. - WITH HOLD
트랜잭션 완료 후에도 커서가 닫히지 않는다. 세션이 non-autocommit 모드일 경우에만 유효하다.SENSITIVE, SCROLL, 및 WITH HOLD 같은 커서 유형의 상세한 설명은 CLI User's Manual를 참고하기 바란다. - <cursor specification>
Altibase SQL의 SELECT문에 해당한다. SQL Reference 참조한다.
설명#
커서 선언문은 커서 처리 SQL문 중에서 가장 먼저 수행되어야 한다. 선언되지 않은 커서 이름으로 다른 커서 조작문을 수행할 경우 "The cursor does not exist." 오류가 발생한다.
커서 선언문 실행 시, 서버에서는 지정한 SQL문의 구문 검사(syntax check), 의미 검사(semantic check), 최적화(optimization), 실행 계획(execution plan) 작성 등 SQL문 수행을 위한 준비(prepare)를 한다. 이렇게 미리 SQL문의 수행 준비를 함으로써 한 번만 준비(ODBC의 SQLPrepare에 해당함-DECLARE CURSOR)하여 여러 번 실행(ODBC의 SQLExecute에 해당함-OPEN CURSOR)할 수 있으므로 성능 향상의 효과를 얻을 수 있다.
제한 사항#
SELECT문의 제한 사항이 그대로 적용된다.
예제#
다음은 DEPARTMENTS 테이블의 모든 레코드를 검색하는 커서를 선언하는 예를 보여준다.
< 예제 프로그램 : cursor1.sc >
EXEC SQL DECLARE DEPT_CUR CURSOR FOR
SELECT *
FROM DEPARTMENTS;
OPEN#
커서를 오픈한다.
구문#
EXEC SQL OPEN <cursor name>;
인자#
- <cursor name>: 커서 이름
설명#
커서 OPEN문은 커서 선언문의 SQL문을 수행한다.
수행되는 SQL문은 이미 커서 선언문에서 수행 준비가 끝난 SQL문이다. SQL문이 수행될 때 데이터베이스 서버는 테이블에서 조건에 맞는 레코드를 찾는다.
OPEN상태인 커서를 CLOSE수행 없이 OPEN 할 수 있으며, 이는 CLOSE한 후에 OPEN한 것과 같다.
예제#
다음은 DEPT_CUR을 OPEN하는 예를 보여준다.
< 예제 프로그램 : cursor1.sc >
EXEC SQL OPEN DEPT_CUR;
FETCH#
열린 커서로부터 칼럼의 값들을 읽어서 이에 대응되는 호스트 변수에 저장한다.
구문#
EXEC SQL FETCH [<fetch_orientation>]
<cursor name> INTO <host_var_list>;
인자#
- <cursor name>: 커서 이름
- <host_var_list>: 출력 호스트 변수와 출력 지시자 변수 리스트
- <fetch_orientation>
결과 집합 내에서 커서의 위치를 지정하여 값을 가져온다. 아래의 값이 올 수 있다.- FIRST: 결과 집합 내에서 첫 번째 행의 값을 가져온다.
- LAST: 결과 집합 내에서 마지막 행의 값을 가져온다.
- PRIOR: 현재 커서의 위치에서 이전 행의 값을 가져온다.
- NEXT: 현재 커서의 위치에서 다음 행의 값을 가져온다.
- CURRENT: 현재 커서 위치의 값을 가져온다.
- RELATIVE n: 현재 커서의 위치에서 n 만큼 다음 행의 값을 가져온다.
- ABSOLUTE n: 결과 집합 내에서 n 번째 행의 값을 가져온다.
설명#
커서를 다음 레코드로 이동시킨 후, 칼럼 값들을 대응하는 호스트 변수에 저장한다.
수행 결과#
다음은 커서 FETCH문의 수행 결과가 SQL_SUCCESS인 경우와 SQL_NO_DATA인 경우에 대해 설명한다.
- 결과가 SQL_SUCCESS인 경우
현재 FETCH 결과가 호스트 변수에 성공적으로 저장되고 데이터베이스 서버에는 반환할 결과가 남아 있음을 의미한다.
일반적으로 응용 프로그램 내에서, 결과가 SQL_SUCCESS인 경우 FETCH를 계속하도록 프로그램 코드를 작성한다. - 결과가 SQL_NO_DATA인 경우
현재 FETCH 결과가 없으며 호스트 변수에는 아무것도 저장되지 않기 때문에 호스트 변수값은 무의미(garbage value)하다. 데이터베이스 서버는 조건에 맞는 레코드를 이미 모두 반환하였거나, 조건에 맞는 레코드가 없음을 의미한다.
예제#
다음은 DEPT_CUR를 FETCH하는 예를 보여준다. 반환된 칼럼값들은 각각 s_department의 구성 요소에 저장된다. s_dept_ind 지시자 변수를 이용해 반환된 칼럼값의 NULL 여부를 검사할 수 있다. while loop 안에서 SQL_NO_DATA가 반환될 때까지 FETCH하여 조건에 맞는 모든 레코드를 가져온다.
< 예제 프로그램 : hostvar.h >
EXEC SQL BEGIN DECLARE SECTION;
typedef struct department
{
short dno;
char dname[30+1];
char dep_location[9+1];
int mgr_no;
} department;
typedef struct dept_ind
{
int dno;
int dname;
int dep_location;
int mgr_no;
} dept_ind;
EXEC SQL END DECLARE SECTION;
< 예제 프로그램 : cursor1.sc >
/* specify path of header file */
EXEC SQL OPTION (INCLUDE=./include);
/* include header file for precompile */
EXEC SQL INCLUDE hostvar.h;
EXEC SQL BEGIN DECLARE SECTION;
/* declare host variables */
department s_department;
/* structure indicator variables */
dept_ind s_dept_ind;
EXEC SQL END DECLARE SECTION;
while(1)
{
EXEC SQL FETCH DEPT_CUR
INTO :s_department :s_dept_ind;
if (sqlca.sqlcode == SQL_SUCCESS)
{
printf("%d %s %s %d\n",
s_department.dno, s_department.dname,
s_department.dep_location,
s_department.mgr_no);
}
else if (sqlca.sqlcode == SQL_NO_DATA)
{
break;
}
else
{
printf("Error : [%d] %s\n", SQLCODE,
sqlca.sqlerrm.sqlerrmc);
break;
}
}
CLOSE#
커서를 닫는다.
구문#
EXEC SQL CLOSE <cursor name>;
인자#
- <cursor name>: 커서 이름
설명#
커서 CLOSE문은 데이터베이스 서버에서 반환할 결과가 남아 있다면(FETCH를 끝까지 하지 않은 경우) 그 결과를 삭제한다. 즉, 커서 CLOSE문 수행 후에는, 그 커서로 FETCH문을 바로 수행할 수 없다. 만약 같은 커서 이름을 재사용하고자 한다면 커서를 다시 OPEN 후 FETCH하여야 한다.
커서 CLOSE문 실행 시에 커서에 할당된 자원은 해제되지 않고 커서 선언문에서 수행한 SQL문의 준비 작업 내용도 그대로 저장된다. 따라서 커서 CLOSE문 수행 후 같은 커서 이름을 재사용할 경우 커서 선언문은 생략하고 바로 커서 OPEN문 수행이 가능하다.
예제#
다음은 DEPT_CUR를 CLOSE하는 예를 보여준다.
< 예제 프로그램 : cursor1.sc >
EXEC SQL CLOSE DEPT_CUR;
CLOSE RELEASE#
커서를 닫고 커서에 할당된 자원을 해제한다.
구문#
EXEC SQL CLOSE RELEASE <cursor name>;
인자#
- <cursor name>: 커서 이름
설명#
커서 CLOSE RELEASE문은 커서에 할당된 자원을 해제하고 커서 선언문에서 수행한 SQL문을 실행하기 위한 준비 작업 내용도 모두 삭제한다. 만약 커서가 반환할 결과가 남아 있다면 그 결과도 모두 삭제한다. 따라서 커서 CLOSE RELEASE문 수행 후 동일한 커서 이름을 재사용할 경우 커서 선언문, 커서 OPEN문 순서로 수행하여야 한다. 즉, 커서 CLOSE RELEASE문 수행 후 같은 커서 이름으로 커서 OPEN문을 바로 수행할 수 없다.
예제#
다음은 EMP_CUR를 CLOSE RELEASE 하는 예를 보여준다. 이 때, EMP_CUR의 선언문에서 수행된 SQL문의 준비 작업 내용이 삭제되고, EMP_CUR에 할당된 모든 자원이 해제된다.
< 예제 프로그램 : cursor2.sc >
EXEC SQL CLOSE RELEASE EMP_CUR;
동일한 커서 이름 재사용 방법#
여기에서는 동일한 커서 이름을 재사용 하는 경우에 대해서만 언급하기로 한다. 동일한 커서 이름을 반복하여 사용할 경우, 어떤 순서로 사용하여야 하고 주의 사항은 무엇인지에 대해 설명한다.
각 커서 처리 SQL문들의 선후관계#
다음은 동일한 커서 이름을 재사용하고자 할 경우 각 커서 조작문은 어떤 다른 커서 조작문 다음에 사용할 수 있는지에 대해 설명한다.
- DECLARE CURSOR
커서 선언문은 커서 CLOSE문, 커서 CLOSE RELEASE문 후에 수행 가능하다. - OPEN
커서 OPEN문은 FETCH를 끝까지 한 경우 마지막 레코드 FETCH 후에, 또는 커서 CLOSE문 후에 수행 가능하다. - FETCH
커서 FETCH문은 커서 OPEN문 후, 또는 FETCH 후 결과가 SQL_SUCCESS인 경우 커서 FETCH문 후에 수행 가능하다. - CLOSE
커서 CLOSE문은 커서 선언문, 커서 OPEN문, 커서 FETCH문(결과가 SQL_SUCCESS, SQL_NO_DATA 모두 가능) 후에 수행 가능하다. - CLOSE RELEASE
커서 CLOSE RELEASE문은 커서 선언문, 커서 OPEN문, 커서 FETCH문(결과가 SQL_SUCCESS, SQL_NO_DATA 모두 가능), 커서 CLOSE문 후에 수행 가능하다.
커서 처리 SQL문과 호스트 변수와의 관계#
다음은 커서 선언문에 사용된 입력 호스트 변수가 전역 변수인지 지역 변수인지에 따라 커서 처리 SQL문을 어떻게 사용하여야 하는지에 대해 설명한다.
- 커서 선언문에 사용된 호스트 변수가 전역 변수라면 동일한 커서 이름 재사용 시 커서 CLOSE문 후 DECLARE 없이 커서 OPEN문 수행이 가능하다.
- 커서 선언문에 사용된 호스트 변수가 지역 변수라면, 즉 함수내에 커서 선언문이 있다면, 동일한 커서 이름 재사용 하기 위해서는 먼저 그 커서를 CLOSE한 후 커서 선언문을 수행해야 한다. 이유는 커서 선언문 수행 시, 커서 선언문에 사용된 호스트 변수들의 포인터를 내부적으로 저장하고 커서 OPEN문 수행 시 이 저장된 호스트 변수들의 포인터를 사용하는데, 이 호스트 변수들이 지역 변수라면 함수 재 호출 시 포인터가 바뀔 수 있고, 커서 OPEN문을 수행할 때 유효하지 않은 값을 참조하게 된다. 따라서, 함수 재 호출 시에는 매번 커서 선언문을 수행하여 포인터를 저장하도록 해야 한다.
CLOSE와 CLOSE RELEASE#
다음은 커서 CLOSE문과 커서 CLOSE RELEASE문의 차이점에 대해서 설명한다.
- 커서 CLOSE RELEASE문 후에 동일한 커서 이름을 재사용하고자 할 경우 반드시 커서 선언문을 수행하여야 한다. 이유는 커서 CLOSE RELEASE문 수행 시 이 커서에 대한 정보와 자원을 모두 해제하므로 커서 선언문을 수행하여 이 커서에 필요한 자원을 할당받고 SQL문 수행을 위한 준비를 하여야 한다. 따라서 일반적으로 커서를 재사용할 경우 커서 CLOSE RELEASE문을 수행하지 않고 커서 CLOSE문을 수행한다.
- FETCH를 끝까지 수행한 경우(커서 FETCH문의 결과가 SQL_NO_DATA인 경우) 커서 CLOSE문 또는 커서 CLOSE RELEASE문 중 선택하여 수행할 수 있다. 이 커서를 재사용하고자 할 경우 커서 CLOSE문을, 재사용하지 않을 경우 커서 CLOSE RELEASE문을 수행하면 된다. 커서 CLOSE문 후 커서 CLOSE RELEASE문 수행이 가능하지만 커서 닫기 연산을 2번 수행한다는 것은 낭비적이다.
- 정리하자면, 커서를 재사용한다면 CLOSE문을, 재사용하지 않는다면 CLOSE RELEASE문을 사용하길 권장한다. 일반적으로 커서를 1회용으로 사용하는 경우(재사용하지 않는 경우)는 거의 없으므로 커서 CLOSE RELEASE문은 거의 사용되지 않는다. 만약 계속해서 재사용 될 커서를 CLOSE RELEASE -> 커서 선언문 -> 커서 OPEN문 순으로 수행한다면 성능 저하를 초래하게 된다.
예제 프로그램#
cursor1.sc#
$ALTIBASE_HOME/sample/APRE/curosor1.sc 참고
실행결과#
$ is -f schema/schema.sql
$ make cursor1
$ ./cursor1
<CURSOR 1>
------------------------------------------------------
[Declare Cursor]
------------------------------------------------------
Success declare cursor
------------------------------------------------------
[Open Cursor]
------------------------------------------------------
Success open cursor
------------------------------------------------------
[Fetch Cursor]
------------------------------------------------------
DNO DNAME DEP_LOCATION MGR_NO
------------------------------------------------------
1001 RESEARCH DEVELOPMENT DEPT 1 New York 16
1002 RESEARCH DEVELOPMENT DEPT 2 Sydney 13
1003 SOLUTION DEVELOPMENT DEPT Japan 14
2001 QUALITY ASSURANCE DEPT Seoul 17
3001 CUSTOMER SUPPORT DEPT London 4
3002 PRESALES DEPT Peking 5
4001 MARKETING DEPT Seoul 8
4002 BUSINESS DEPT LA 7
------------------------------------------------------
[Close Cursor]
------------------------------------------------------
Success close cursor
cursor2.sc#
$ALTIBASE_HOME/sample/APRE/curosor2.sc 참고
실행결과#
$ is -f schema/schema.sql
$ make cursor2
$ ./cursor2
<CURSOR 2>
------------------------------------------------------
[Declare Cursor]
------------------------------------------------------
Success declare cursor
------------------------------------------------------
[Open Cursor]
------------------------------------------------------
Success open cursor
------------------------------------------------------
[Fetch Cursor]
------------------------------------------------------
ENO DNO SALARY
------------------------------------------------------
2 -1 1500000.00
3 1001 2000000.00
4 3001 1800000.00
5 3002 2500000.00
6 1002 1700000.00
7 4002 500000.00
9 4001 1200000.00
10 1003 4000000.00
11 1003 2750000.00
12 4002 1890000.00
13 1002 980000.00
14 1003 2003000.00
15 1003 1000000.00
16 1001 2300000.00
17 2001 1400000.00
18 4001 1900000.00
19 4002 1800000.00
------------------------------------------------------
[Close Release Cursor]
------------------------------------------------------
Success close release cursor