[번역] Embedded SQL - 03

목 차
▣ 호스트 변수 ( Host Variables )
▣ 지시자 변수 ( Indicator Variables )
▣ 기본 SQL 문장 ( The Basic SQL Statements )
▣ The DML Returning Clause
▣ 커서 ( Cursors )
▣ 최적화 힌트 ( Optimizer Hints )
▣ CURRENT OF절 ( The CURRENT OF Clause )
▣ 커서문 ( The Cursor Statements )
▣ Non-Scrollable 커서를 사용하는 예제
▣ Scrollable 커서를 사용하는 예제

6) Scrollable Cursors
Scrollable Cursor는 오라클이 SQL문장을 실행하는 곳이며, 실행되는 동안 진행되는
정보를 저장하는 작업영역이다.

커서가 실행될때, 쿼리의 결과는 결과셋(result set)이라고 불리는 행들의 셋에 위치한다.
결과셋을 순차적으로 혹은 비순차적으로 결과를 취득할 수 있다. 비순서적인 결과셋을
Scrollable Cursors라고 부른다.

Scrollable Cursor는 사용자가 결과셋을 행을 앞으로,뒤로, 무작위적 방법으로 접근
할 수 있도록 돕는다.

6-1) Scrollable Cursors의 사용
아래의 문장들은 Scrollable Cursor를 조작하거나 선언하는것을 다룬다.

DECLARE SCROLL CURSOR : DECLARE <cursor name> SCROLL CURSOR문은 쿼리와 관련된
Scrollable Cursor를 명명하는데 사용한다.

OPEN : OPEN문은 일반 커서선언과 같은 방법으로 사용한다.

FETCH : FETCH문은 무작위적 방법으로 요구되는 행을 취득하는데 사용한다.
프로그램은 위아래 혹은 처음과 끝 혹은 무작위로 한행을 취득한다.

아래의 옵션은 FETCH문에 사용할 수 있는 옵션이다.

1. FETCH FIRST 결과셋으로부터 첫번째 행을 취득한다.
2. FETCH PRIOR 현재행으로부터 이전행을 취득한다.
3. FETCH NEXT 현재위치에서 다음행를 취득한다. 이것은 일반 FETCH와 같다.
4. FETCH LAST 결과셋으로부터 마지막 행을 취득한다.
5. FETCH CURRENT 현재행을 취득한다.
6. FETCH RELATIVE n 현재행으로부터 n번째 행을 취득한다. n은 OFFSET이다.
7. FETCH ABSOLUTE n 결과셋의 시작점으로부터 n OFFSET인 n번째행을 취득한다.

아래의 예제는 결과셋으로부터 마지막 레코드를 취득하는 예제이다.

EXEC SQL DECLARE emp_cursor SCROLL CURSOR FOR
SELECT ename, sal FROM emp WHERE deptno=20;
...

EXEC SQL OPEN emp_cursor;
EXEC SQL FETCH LAST emp_cursor INTO :emp_name, :sal;
EXEC SQL CLOSE emp_cursor;

CLOSE : CLOSE문은 일반 커서의 경우와 같은 방법으로 사용하면 된다.

6-2) CLOSE_ON_COMMIT 전처리기 옵션
CLOSE_ON_COMMIT 전처리기 옵션은 COMMIT이 실행될때 모든 커서를 닫을것인지 아닌지를
선택할 수 있는 능력을 제공하는 옵션이다. 만약 MODE = ANSI일때, CLOSE_ON_COMMIT이
기본적으로 YES를 갖는다. 명시적으로 CLOSE_ON_COMMIT=NO으로 설정하면 성능이 향상된다.
왜냐하면, 커서들은 COMMIT이 실행될때 닫히지 않을것이기 때문이다.

6-3) PREFETCH 전처리기 옵션
전처리기의 PREFETCH옵션은 주어진 행수를 미리가지고옮으로해서 더욱 효율적인 쿼리가
되도록한다. 이것은 서버접속수나 전체적인 메모리 사용을 줄일수 있다. PREFETCH옵션값
의 행수는 명시적인 커서를 포함한 모든 쿼리에서 사용한다.

인라인에서 사용될때, PREFETCH옵션은 이런한 커서문장보다 선행되어야 한다.

☞ EXEC SQL OPEN cursor
☞ EXEC SQL OPEN cursor USING host_var_list
☞ EXEC SQL OPEN cursor USING DESCRIPTOR desc_name

PREFTECH값은 0에서 65535로 설정할 수 있다. 기본값은 1이다.

7) Optimizer Hints
Pro*C/C++ 전처리기는 SQL문자에서 최적화 힌트(Optimizer hints)를 지원한다.
힌트는 다음을 위해서 사용할 수 있다.
☞ Optimization approach for a SQL statement
☞ 참조테이블을 위한 접근 경로지정 (Access path for each referenced table)
☞ 조인순서(Join order for a join)
☞ 조인방법적용(Method used to join tables)

힌트는 rule-based 최적화와 cost-based 최적화를 선택하게 한다.
아래의 예제는 작업처리를 최적화하기위한 힌트를 사용한 예제이다.

EXEC SQL SELECT /*+ ALL_ROWS (cost-based) */ empno, ename, sal, job
INTO :emp_rec FROM emp
WHERE deptno = :dept_number;

cost-based optimizer와 optimizer hints에대한 더 많은 정보를 얻기위해서는
Oracle9i Application Developer’s Guide - Fundamentals장을 참조한다.

8) CURRENT OF 절
주어지 커서로부터 취득한 테이타의 최근행을 참조해서 DELETE나 UPDATE문을 사용
할 경우 CURRENT OF 커서명 절을 사용할 수 있다.
아래에 사용예제를 보여주고 있다.

EXEC SQL DECLARE emp_cursor CURSOR FOR
SELECT ename, sal FROM emp WHERE job = ’CLERK’
FOR UPDATE OF sal;
...

EXEC SQL OPEN emp_cursor;
EXEC SQL WHENEVER NOT FOUND GOTO ...
for (;;) {
EXEC SQL FETCH emp_cursor INTO :emp_name, :salary;
...

EXEC SQL UPDATE emp SET sal = :new_salary
WHERE CURRENT OF emp_cursor;
}

8-1) 제한사항
CURRENT OF은 인덱스가 구성된 테이블(index-organized table)에서는 사용할 수 없다.
CURRENT OF은 호스트 배열과 함께사용할 수 없다.
CURRENT OF과 함께 테이블을 조인해서 사용 할 수 없다.
결론적으로, CURRENT OF은 Dynamic SQL과 함께 사용할 수 없다는것이다.

9) 커서문(Cursor Statements)
아래의 예제는 프로그램에서 커서제어문의 전형적인 절차를 보여주고 있다.

...

/* 커서 선언 */
EXEC SQL DECLARE emp_cursor CURSOR FOR
SELECT ename, job
FROM emp
WHERE empno = :emp_number
FOR UPDATE OF job;

/* 커서를 열고 active set을 확인 */
EXEC SQL OPEN emp_cursor;

/* 마지막행을 취득했다면 break */
EXEC SQL WHENEVER NOT FOUND DO break;

/* 반복문에서 데이터 취득 / 처리 */
for (;;)
{
EXEC SQL FETCH emp_cursor INTO :emp_name, :job_title;

EXEC SQL UPDATE emp
SET job = :new_job_title
WHERE CURRENT OF emp_cursor;
}
...

/* 커서를 닫음 */
EXEC SQL CLOSE emp_cursor;
EXEC SQL COMMIT WORK RELEASE;
...

10) Non-Scrollable Cursor를 사용한 예제
아래의 완전한 프로그램은 커서사용법과 FETCH문의 사용법을 보여주고 있다.
부서번호를 입력받고 부서의 모든 사원의 이름을 표시하는 프로그램이다.
FETCH한후의 에러처리부분에 대한 내용을 담고 있다.
FETCH가 실패하면 오라클에러코드는 sqlca.sqlcode에 "no data found"가 리턴된다.

#include <stdio.h>

/* 호스트변수 선언 */
char userid[12] = "SCOTT/TIGER";
char emp_name[10];
int emp_number;
int dept_number;
char temp[32];
void sql_error();

/* SQL Communications Area 포함*/
#include <sqlca.h>

////////////////////////////////////////////////////////////////////////////
// Main 함수
////////////////////////////////////////////////////////////////////////////
void main()
{
emp_number = 7499;
/* 에러처리 */
EXEC SQL WHENEVER SQLERROR do sql_error("Oracle error");

/* 오라클 연결 */
EXEC SQL CONNECT :userid;
printf("Connected.n");

/* 커서 선언 */
EXEC SQL DECLARE emp_cursor CURSOR FOR
SELECT ename
FROM emp
WHERE deptno = :dept_number;

printf("Department number? ");

gets(temp);

dept_number = atoi(temp);

/* 커서오픈 및 active set 확인 */
EXEC SQL OPEN emp_cursor;
printf("Employee Namen");
printf("-------------n");
/* 데이터가 없을때까지 데이터 취득 및 처리 */
EXEC SQL WHENEVER NOT FOUND DO break;
while (1)
{
EXEC SQL FETCH emp_cursor INTO :emp_name;
printf("%sn", emp_name);
}
EXEC SQL CLOSE emp_cursor;
EXEC SQL COMMIT WORK RELEASE;
exit(0);
}

////////////////////////////////////////////////////////////////////////////
// 에러처리 함수
////////////////////////////////////////////////////////////////////////////
void sql_error(msg)
char *msg;
{
char buf[500];
int buflen, msglen;
EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL ROLLBACK WORK RELEASE;
buflen = sizeof (buf);
sqlglm(buf, &buflen, &msglen);
printf("%sn", msg);
printf("%*.sn", msglen, buf);
exit(1);
}

11) Scrollable Cursor를 사용한 완전한 예제
아래의 프로그램은 Scrollable Cursor사용법을 보여주고 있으며, FETCH문을 사용한
다양한 옵션을 사용하는 방법을 보여주고 있다.

#include <stdio.h>
/* 호스트변수 선언 */
char userid[12]="SCOTT/TIGER";
char emp_name[10];
void sql_error();
/* SQL Communications Area 포함*/
#include<sqlca.h>

/////////////////////////////////////////////////////////////////////////////
// 메인 함수
/////////////////////////////////////////////////////////////////////////////
void main()
{
/* 에러 처리 */
EXEC SQL WHENEVER SQLERROR do sql_error("Oracle error");
/* 오라클 연결 */
EXEC SQL CONNECT :userid;
printf("Connected.n");
/* scrollable cursor 선언 */
EXEC SQL DECLARE emp_cursor SCROLL CURSOR FOR
SELECT ename FROM emp;
/* 커서오픈 및 active set 확인 */
EXEC SQL OPEN emp_cursor;
/* 마지막행 취득 */
EXEC SQL FETCH LAST emp_cursor INTO :emp_name;
/* 5 행을 취득 */
EXEC SQL FETCH ABSOLUTE 5 emp_cursor INTO :emp_name;
/* 10 행의 데이터 취득*/
EXEC SQL FETCH RELATIVE 5 emp_cursor INTO :emp_name;
/* 7번행 취득 */
EXEC SQl FETCH RELATIVE -3 emp_cursor INTO :emp_name;
/* 첫번째 행 취득 */
EXEC SQL FETCH FIRST emp_cursor INTO :emp_name;

/* 2번행 취득 */
EXEC SQL FETCH my_cursor INTO :emp_name;
/* 3번행 취득 */
EXEC SQL FETCH NEXT my_cursor INTO :emp_name;
/* 3번행 취득*/
EXEC SQL FETCH CURRENT my_cursor INTO :emp_name;
/* 2번행 취득 */
EXEC SQL FETCH PRIOR my_cursor INTO :emp_name;
}

/////////////////////////////////////////////////////////////////////////////
// 에러처리 함수
/////////////////////////////////////////////////////////////////////////////
void sql_error(msg)
char *msg;
{
char buf[500];
int buflen , msglen;
EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL ROLLBACK TRANSACTION;
buflen = sizeof (buf);
sqlglm(buf, &buflen, &mesglen);
printf("%sn",msg);
printf("%*.sn",msglen,buf);
exit(1);
}

11-1) Positioned Update
ROWID를 사용한 Update의 예제를 보여주고 있다.
자세한 사용법은 "Universal ROWIDs"를 참조한다.

#include <oci.h>
...

OCIRowid *urowid;
...

EXEC SQL ALLOCATE :urowid;
EXEC SQL DECLARE cur CURSOR FOR
SELECT rowid, ... FROM my_table FOR UPDATE OF ...;
EXEC SQL OPEN cur;
EXEC SQL FETCH cur INTO :urowid, ...;

/* Process data */
...

EXEC SQL UPDATE my_table SET ... WHERE CURRENT OF cur;
EXEC SQL CLOSE cur;
EXEC SQL FREE :urowid;

...

출처 - http://younbok.egloos.com/9342723