[번역] 호스트배열 (Host Arrays) - 02

목 차
▣ 왜 배열을 사용하는가?
▣ 호스트 배열의 선언
▣ SQL문에서의 배열사용
▣ 배열로 Select하기
▣ 배열과 Insert하기
▣ 배열과 Update하기
▣ 배열과 Delete하기
▣ FOR절 사용
▣ WHERE절 사용
▣ 배열 구조체
▣ Mimicking CURRENT OF

4) 배열로 select하기

: select 문장에서 출력변수로 호스트배열을 사용할 수 있다. 아래의 예제는 select시 리턴되는 행

의 수를 알고 있을 경우 배열을 선언하고 사용하는 예제이다.

char emp_name[50][20];

int emp_number[50];

float salary[50];

EXEC SQL SELECT ENAME, EMPNO, SAL

INTO :emp_name, :emp_number, :salary

FROM EMP

WHERE SAL > 1000;

위의 예제에서와는 반대로 50개 이상의 행이 리턴 되는 경우는 더 큰 배열을 선언하던지 아니면

FETCH문을 사용하기 위해서 커서를 선언해야만 한다.

4-1) 커서 Fteches

: SELECT시 리턴될 행의 수를 알지 못할 경우, 아래와 같이 작성하면 된다. 아래의 예제의 경우

20개의 행을 각 FETCH시 마다 리턴해주는 예제이다.

int emp_number[20];

float salary[20];

EXEC SQL DECLARE emp_cursor CURSOR FOR

SELECT empno, sal FROM emp;

EXEC SQL OPEN emp_cursor;

EXEC SQL WHENEVER NOT FOUND do break;

for (;;)

{

EXEC SQL FETCH emp_cursor

INTO :emp_number, :salary;

/* process batch of rows */

...

}

...

마지막 FETCH 작업 시에 실제적으로 리턴된 행수가 몇인지 체크하는 것을 잊지 마라.

4-2) sqlca.sqlerrd[2]의 사용

: insert,update,delete,select into 문에서 sqlca.sqlerrd[2]는 처리된 행수를 나타내고,

fetch문에 대해서는 처리된 행의 합계를 나타낸다.

fetch와함께 호스트 배열을 사용할 때 가장 최근의 반복에서 리턴된 행수를 구하려면,

이전값에서 현재 sqlca.sqlerrd[2]값을 빼야한다. 아래의 예제는 가장최근의 fetch에 의해

리턴된 행의 수를 결정하는 예제이다.

int emp_number[100];

char emp_name[100][20];

int rows_to_fetch, rows_before, rows_this_time;

EXEC SQL DECLARE emp_cursor CURSOR FOR

SELECT empno, ename

FROM emp

WHERE deptno = 30;

EXEC SQL OPEN emp_cursor;

EXEC SQL WHENEVER NOT FOUND CONTINUE;

/* initialize loop variables */

rows_to_fetch = 20; /* number of rows in each "batch" */

rows_before = 0; /* previous value of sqlerrd[2] */

rows_this_time = 20;

while (rows_this_time == rows_to_fetch)

{

EXEC SQL FOR :rows_to_fetch

FETCH emp_cursor

INTO :emp_number, :emp_name;

rows_this_time = sqlca.sqlerrd[2] - rows_before;

rows_before = sqlca.sqlerrd[2];

}

...

배열연산을 하는 동안 에러가 발생했을 때 sqlca.sqlerrd[2]는 유용하게 사용된다. 처리과정중에

에러가 발생한 행에 멈추게 되며, sqlerrd[2]는 성공적으로 처리된 행의 수를 가지게 된다.

4-3) Fetch된 행수

: 각 fetch는 배열크기만큼 리턴을 한다. 아래의 경우는 배열크기보다 적은 행수가 리턴되는

경우이다.

# 리턴된 데이터가 없는경우 ‘no data found’가 리턴된 경우라고 보면된다.

# select시 리턴 받는 총 행수가 배열의 크기보다 적은 수를 리턴하는 경우이다.

# 행을 처리하는 동아 에러가 감지된 경우 fetch가 실패하고 오라클에서 에러코드를 리턴한다.

아래의 예제를 보면 리턴된 행의 수를 알 수 있다.

EXEC SQL OPEN cursor1;

EXEC SQL OPEN cursor2;

EXEC SQL FETCH cursor1 INTO :array_of_20;

/* now running total in sqlerrd[2] is 20 */

EXEC SQL FETCH cursor2 INTO :array_of_30;

/* now running total in sqlerrd[2] is 30, not 50 */

EXEC SQL FETCH cursor1 INTO :array_of_20;

/* now running total in sqlerrd[2] is 40 (20 + 20) */

EXEC SQL FETCH cursor2 INTO :array_of_30;

/* now running total in sqlerrd[2] is 60 (30 + 30) */

4-4) Sample Program 3: Host Array

: 아래의 데모 프로그램은 Pro*C/C++에서 쿼리를 작성할 때 호스트배열을 어떻게 사용하는지

보여주고 있다.

/* * sample3.pc * 호스트 배열 * 이 프로그램은 오라클에 연결하고, 커서를 선언하고 커서를 연후, 배열에 fetch를 하고 * 결과를 print_rows()라는 함수를 사용하여 표시한다. */ #include <stdio.h> #include <string.h> #include <sqlca.h> #define NAME_LENGTH 20 #define ARRAY_LENGTH 5 /* 오라클 연결의 다른 방법 */ char *username = "SCOTT"; char *password = "TIGER"; /* 호스트 구조체 tag 선언 */ struct { int emp_number[ARRAY_LENGTH]; char emp_name[ARRAY_LENGTH][NAME_LENGTH]; float salary[ARRAY_LENGTH]; } emp_rec; /* 함수선언 */ void print_rows(); /* 프로그램 출력처리 */ void sql_error(); /* 에러처리 */ main() { int num_ret; /* 리턴되는 행 수 */ /* ORACLE 연결 */ EXEC SQL WHENEVER SQLERROR DO sql_error("Connect error:"); EXEC SQL CONNECT :username IDENTIFIED BY :password; printf("nConnected to ORACLE as user: %sn", username); EXEC SQL WHENEVER SQLERROR DO sql_error("Oracle error:"); /* FETCH를 위해 커서 선언 */ EXEC SQL DECLARE c1 CURSOR FOR SELECT empno, ename, sal FROM emp; EXEC SQL OPEN c1; /* 행수 초기화 */ num_ret = 0; /* 배열 fetch 반복 ( NOT FOUND가 참이 되었을 때 ) */ EXEC SQL WHENEVER NOT FOUND DO break; for (;;) { EXEC SQL FETCH c1 INTO :emp_rec; /* 행수를 출력 */ print_rows(sqlca.sqlerrd[2] - num_ret); num_ret = sqlca.sqlerrd[2]; /* 행수를 재설정 */ } /* 마지막 fetch후 남은 행수를 출력 */ if ((sqlca.sqlerrd[2] - num_ret) > 0) print_rows(sqlca.sqlerrd[2] - num_ret); EXEC SQL CLOSE c1; printf("nAu revoir.nnn"); /* 연결 종료 */ EXEC SQL COMMIT WORK RELEASE; exit(0); } /* 행수 출력함수 */ void print_rows(n) int n; { int i; printf("nNumber Employee Salary"); printf("n------ -------- ------n"); for (i = 0; i < n; i++) printf("%-9d%-15.15s%9.2fn", emp_rec.emp_number[i], emp_rec.emp_name[i], emp_rec.salary[i]); } /* 에러 출력 함수 */ void sql_error(msg) char *msg; { EXEC SQL WHENEVER SQLERROR CONTINUE; printf("n%s", msg); printf("n% .70s n", sqlca.sqlerrm.sqlerrmc); EXEC SQL ROLLBACK WORK RELEASE; exit(1); }

4-5) Sample Program : Host Arrays Using Scrollable Cursor

: 아래의 예제 프로그램은 scrollable Cursor를 사용해서 호스트 배열을 어떻게 사용하는지 예제

를 보여주고 있다.

주의) 결과셋에서 행의 수를 결정하기 위해서 FETCH LAST를 하는 것을 주의 깊게 살펴보자.

/* * 이프로그램은 hr/hr 스키마를 사용한다. 이프로그램을 실행하기전에 이 스키마가 존재 * 하는지 확인하기 바란다. */ #include <stdio.h> #include <string.h> #include <stdlib.h> #include <sqlca.h> #define ARRAY_LENGTH 4 /* 사용자 아이디와 비밀번호 */ char *username = "hr"; char *password = "hr"; /* 호스트 구조체 선언 */ struct emp_rec_array { int emp_number; char emp_name[20]; float salary; } emp_rec[ARRAY_LENGTH]; /* 퀴리의 결과를 출력 */ void print_rows() { int i; for (i=0; i<ARRAY_LENGTH; i++) printf("%d %s %8.2fn", emp_rec[i].emp_number, emp_rec[i].emp_name, emp_rec[i].salary); } /* 오라클 에러 처리 */ void sql_error(char *msg) { EXEC SQL WHENEVER SQLERROR CONTINUE; printf("n%s", msg); printf("n% .70s n", sqlca.sqlerrm.sqlerrmc); EXEC SQL ROLLBACK WORK RELEASE; exit(EXIT_FAILURE); } void main() { int noOfRows; /* 행수 */ /* 에러처리 */ EXEC SQL WHENEVER SQLERROR DO sql_error("Connect error:"); /* 데이터베이스 연결 */ EXEC SQL CONNECT :username IDENTIFIED BY :password; /* 에러처리 */ EXEC SQL WHENEVER SQLERROR DO sql_error("Oracle error:"); /* scrollable mode에 커서를 선언 */ EXEC SQL DECLARE c1 SCROLL CURSOR FOR SELECT employee_id, first_name, salary FROM employees; EXEC SQL OPEN c1; EXEC SQL WHENEVER SQLERROR DO sql_error("Fetch Error:"); /* 이것은 dummy fetch로서 결과셋에서 행수를 찾는다. */ EXEC SQL FETCH LAST c1 INTO :emp_rec; /* 결과셋의 행수는 sqlca.sqlerrd[2]값으로 주어진다. */ noOfRows = sqlca. sqlerrd[2]; printf("Total number of rows in the result set %d:n", noOfRows); /* ARRAY_LENGTH만큼의 행수를 취득한다. */ EXEC SQL FETCH FIRST c1 INTO :emp_rec; printf("******************** DEFAULT : n"); print_rows(); /* 다음 ARRAY_LENGTH 행수를 취득한다. */ EXEC SQL FETCH NEXT c1 INTO :emp_rec; printf("******************** NEXT : n"); print_rows(); /* 3번째 ARRAY_LENGTH 만큼 취득 */ EXEC SQL FETCH ABSOLUTE 3 c1 INTO :emp_rec; printf("******************** ABSOLUTE 3 : n"); print_rows(); /* 현재 ARRAY_LENGTH 를 취득 */ EXEC SQL FETCH CURRENT c1 INTO :emp_rec; printf("******************** CURRENT : n"); print_rows(); /* 현재 커서위치에서 ARRAY_LENGTH 행을 두번째 offset으로 취득 */ EXEC SQL FETCH RELATIVE 2 c1 INTO :emp_rec; printf("******************** RELATIVE 2 : n"); print_rows(); /* 다시 첫번째 ARRAY_LENGTH 행수를 취득 */ EXEC SQL FETCH ABSOLUTE 0 c1 INTO :emp_rec; printf("******************** ABSOLUTE 0 : n"); print_rows(); /* 커서를 닫는다. */ EXEC SQL CLOSE c1; /* 데이터베이스 종료 */ EXEC SQL COMMIT WORK RELEASE; exit(EXIT_SUCCESS); }

4-6) 호스트 배열 제한사항

: SELECT문의 WHERE절에서 호스트 배열을 사용할 수는 없다. ( 단 서버쿼리 제외)

SELECT나 FTECH 절의 INTO에서 호스트배열과 호스트 변수를 혼용해서 사용할 수 없다.

5) Inserting with Arrays

: insert 문에서 입력변수로 호스트 변수를 사용할 수 있다. 배열에 요소가 부적절하다면, 입력된 행

수를 제어하기 위해서 FOR 절을 사용할 수 있다. 호스트 배열을 가지고 입력하는 예제가 아래에 있다.

char emp_name[50][20];

int emp_number[50];

float salary[50];

/* 호스트 배열을 채운다. */

...

EXEC SQL INSERT INTO EMP (ENAME, EMPNO, SAL)

VALUES (:emp_name, :emp_number, :salary);

입력된 누적행수는 sqlca.sqlerrd[2] 찾을 수 있다.

아래의 예제는 한번에 한행씩 입력되는 예제로 위의 예제보다는 효율성이 떨어지는 예제이다.

왜냐하면 각행이 입력될때마다 서버의 호출이 일어 나기 때문이다.

for (i = 0; i < array_size; i++)

EXEC SQL INSERT INTO emp (ename, empno, sal)

VALUES (:emp_name[i], :emp_number[i], :salary[i]);

5-1) 배열 Insert인 경우 제한사항

: Insert 문의 Values 절에 배열포인터를 사용할 수 없다.

Insert 문의 values 절에 호스트 배열과 호스트 변수를 혼용 할 수 없다.

6) 배열을 사용한 UPDATE

: 아래의 예제처럼 UPDATE 문에 호스트 배열을 사용 할 수 있다.

int emp_number[50];

float salary[50];

/* populate the host arrays */

EXEC SQL UPDATE emp SET sal = :salary

WHERE EMPNO = :emp_number;

Update된 축적행수는 sqlerrd[2]에서 찾을 수 있다. 이 수는 update cascade에 의해서 처리된

행을 포함하지는 않는다. 배열의 요소가 부적절하다면, update된 행의 수를 제한 하기위해서 SQL FOR 절을 사용할 수 있다.

6-1) 배열로 UPDATE 시 제한 사항

: 호스트 변수와 호스트배열을 혼용해서 SET 혹은 WHERE 절에 사용할 수 없으며, UPDATE문에 CURRENT

OF 절에 호스트 배열을 사용할 수 없다.

7) 배열을 사용한 DELETE 처리

: DELETE 문에서 입력변수로 호스트 배열을 사용할 수 있다. WHERE 절에 호스트 배열을 사용해서

반복적으로 DELETE 문을 실행 할 수 있다. 호스트 배열을 사용해서 삭제하는 예제는 아래와같다.

...

int emp_number[50];

/* populate the host array */

...

EXEC SQL DELETE FROM emp

WHERE empno = :emp_number;

삭제된 누적 행수는 sqlerrd[2]에서 얻을 수 있다.

7-1) 배열을 사용한 DELETE 처리시 제한사항

: where 절에 호스트변수와 호스트배열을 사용할 수 없다.

DELETE 문에 CURRENT OF 절에 호스트 배열을 사용할 수 없다.

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