9. 배열 처리 SQL 문

배열 호스트 변수의 개요

이 장에서는 배열 타입의 호스트 변수를 선언하고 사용하는 방법에 대해서 알아본다.

정의 및 선언

배열 호스트 변수란 호스트 변수의 데이터 타입으로 사용 가능한 데이터 타입들에 대해 1차원 또는 2차원으로 배열 선언된 호스트 변수를 말한다.

문자형 타입과 varchar 타입에 대해 2차원 배열까지 선언이 가능하고 그 외 타입들은 1차원 배열 선언이 가능하다. 예외적으로 pointer 타입은 배열로 선언할 수 없다.

예제

배열 호스트 변수를 선언하는 다양한 예를 보여준다.

[예제 1] 다음은 , 숫자형 타입의 배열 타입을 호스트 변수로 선언하는 예를 보여준다.

< 예제 프로그램 : arrays1.sc >

EXEC SQL BEGIN DECLARE SECTION;
char    a_gno[3][10+1];
char    a_gname[3][20+1];
char    a_goods_location[3][9+1];
int     a_stock[3];
double  a_price[3];
EXEC SQL END DECLARE SECTION;

[예제 2] 다음은 구조체의 배열 타입을 호스트 변수로 선언하는 예를 보여준다.

< 예제 프로그램 : hostvar.h >

EXEC SQL BEGIN DECLARE SECTION;
typedef struct goods
{
    char   gno[10+1];
    char   gname[20+1];
    char   goods_location[9+1];
    int    stock;
    double price;
} goods;
EXEC SQL END DECLARE SECTION;

< 예제 프로그램 : arrays1.sc >

EXEC SQL BEGIN DECLARE SECTION;
goods   a_goods[3];
EXEC SQL END DECLARE SECTION;

[예제 3] 다음은 구성 요소가 배열인 구조체 타입을 호스트 변수로 선언하는 예를 보여준다.

< 예제 프로그램 : arrays1.sc >

EXEC SQL BEGIN DECLARE SECTION;
struct
{
    char    gno[3][10+1];
    char    gname[3][20+1];
    char    goods_location[3][9+1];
    int     stock[3];
    double  price[3];
} a_goods2;
EXEC SQL END DECLARE SECTION;

장점

배열 타입의 호스트 변수를 사용할 경우 성능 향상의 효과가 있다.

다음의 설명을 통해 어떻게 성능 향상의 효과가 있는지 알아보자.

INSERT 문에서 배열 타입의 호스트 변수를 사용하는 경우와 배열 타입이 아닌 호스트 변수를 사용하는 경우를 비교해 보자. 배열 크기가 1000이라고 한다면 배열 타입의 호스트 변수를 사용하는 경우 INSERT 문 한 번 수행 시 1000건의 레코드가 삽입된다. 배열 타입이 아닌 호스트 변수를 사용하는 경우 1000건의 레코드를 삽입하기 위해서는 INSERT 문을 1000번 수행하여야 한다. 이 때 1000번의 데이터베이스 서버와의 통신이 발생한다. 따라서 배열 타입의 호스트 변수를 사용하는 경우, 배열 타입이 아닌 호스트 변수를 사용하는 경우에 비해 통신량이 줄어 성능이 향상된다.

FETCH 문에서 배열 타입의 호스트 변수를 사용하는 경우와 배열 타입이 아닌 호스트 변수를 사용하는 경우를 비교해 보자. 배열 크기가 1000이라고 한다면 배열 타입의 호스트 변수를 사용하는 경우 FETCH 문 한 번 수행 시 1000건의 레코드가 배열 0번째부터 차례로 저장된다. 배열 타입이 아닌 호스트 변수를 사용하는 경우 1000건의 레코드를 가져오기 위해서는 FETCH 문을 1000번 수행하여야 한다. 그러나 이 때 FETCH 문을 수행할 때마다 데이터베이스 서버와의 통신이 발생하지는 않는다. 왜냐하면 내부적으로 미리 일정 건수만큼 데이터베이스 서버로부터 가져와서 저장하고 있다가 FETCH 문을 수행할 때마다 한 건씩 호스트 변수에 저장해 주기 때문이다. 따라서 FETCH 문 수행 시 배열 타입의 호스트 변수를 사용한다고 해서 큰 성능 향상을 기대하기는 어렵다. 다만 FETCH 문의 수행 횟수를 줄임으로써 약간의 성능 향상을 기대할 수 있다.

CONNTYPE과 배열 호스트 변수

CONNTYPE

CONNTYPE은 데이터베이스 서버와의 통신 방법을 결정하는 옵션으로 데이터베이스 서버와 연결할 때 지정할 수 있다. CONNTYPE은 성능에 많은 영향을 미치며, 배열 타입의 호스트 변수 사용시에도 관계가 있다.

CONNTYPE옵션의 설정에 대한 설명은 6장을 참조하기 바란다.

CONNTYPE의 종류

지원하는 CONNTYPE은 TCP, UNIX, IPC가 있다.

CONNTYPE과 호스트 변수의 배열 크기와의 관계

일반적으로 CONNTYPE의 성능은 IPC, UNIX, TCP 순으로 좋다. 그러나 입력 호스트 변수를 배열 타입으로 사용할 경우에는 그렇지 않을 수 있다. 따라서 입력 호스트 변수를 배열 타입으로 사용할 경우에는 CONNTYPE과 배열 크기를 변경하면서 테스트하여 가장 좋은 성능을 내는 CONNTYPE과 배열 크기를 구해서 사용하면 된다.

내장 SQL 문에서 배열 호스트 변수 사용

다양한 내장 SQL 문 안에서 배열 타입의 호스트 변수를 사용할 수 있다.

INSERT 문

다음은 INSERT 문에서 사용할 수 있는 배열 타입들이다.

• 숫자형, 문자형 타입의 배열
• 구조체의 배열
• 구성 요소가 배열인 구조체
• 다음의 확장된 데이터 타입
  • SQL_DATE_STRUCT
  • SQL_TIME_STRUCT
  • SQL_TIMESTAMP_STRUCT
  • SQL_NUMERIC_STRUCT

예제

다음은 INSERT 문에서 배열 호스트 변수를 입력 호스트 변수로 사용하는 예를 보여준다.

< 예제 프로그램 : arrays1.sc >

EXEC SQL BEGIN DECLARE SECTION;
char    a_gno[3][10+1];
char    a_gname[3][20+1];
char    a_goods_location[3][9+1];
int     a_stock[3];
double  a_price[3];
EXEC SQL END DECLARE SECTION;

strcpy(a_gno[0], "X111100001");
strcpy(a_gno[1], "X111100002");
strcpy(a_gno[2], "X111100003");
strcpy(a_gname[0], "XX-201");
strcpy(a_gname[1], "XX-202");
strcpy(a_gname[2], "XX-203");
strcpy(a_goods_location[0], "AD0010");
strcpy(a_goods_location[1], "AD0011");
strcpy(a_goods_location[2], "AD0012");
a_stock[0] = 1000;
a_stock[1] = 1000;
a_stock[2] = 1000;
a_price[0] = 5500.21;
a_price[1] = 5500.45;
a_price[2] = 5500.99;

EXEC SQL INSERT INTO GOODS 
VALUES (:a_gno, :a_gname, :a_goods_location, 
:a_stock, :a_price);

UPDATE 문

다음은 UPDATE 문에서 사용할 수 있는 배열이다.

• 숫자형, 문자형 타입의 배열
• 구성 요소가 배열인 구조체
• 다음의 확장된 데이터 타입
  • SQL_DATE_STRUCT
  • SQL_TIME_STRUCT
  • SQL_TIMESTAMP_STRUCT
  • SQL_NUMERIC_STRUCT

제한 사항

UPDATE 문의 한 호스트 변수가 배열이라면, 모든 호스트 변수가 배열이어야 하고, 각 배열의 크기는 같아야 한다. UPDATE 문의 SET 절과 WHERE절의 호스트 변수로 구조체의 배열은 사용할 수 없고, 아래의 예제처럼 구조체 배열 중 한 개의 배열 요소만 지정하여 사용할 수는 있다.

예:

EXEC SQL BEGIN DECLARE SECTION;
struct tag1 { int i1; int i2; int i3; } var1[10];
EXEC SQL END DECLARE SECTION;

EXEC SQL UPDATE T1 
SET I1 = :var1[0].i1, I2 = :var1[0].i2 
WHERE I1 = :var1[0].i3;     (O)

예제

다음은 UPDATE 문에서 배열 호스트 변수를 입력 호스트 변수로 사용하는 예를 보여준다.

< 예제 프로그램 : arrays1.sc >

EXEC SQL BEGIN DECLARE SECTION;
int   a_eno[3];
short a_dno[3]; 
char  a_emp_tel[3][15+1];
EXEC SQL END DECLARE SECTION;

a_eno[0] = 10;
a_eno[1] = 11;
a_eno[2] = 12;
a_dno[0] = 2001;
a_dno[1] = 2001;
a_dno[2] = 2001;
strcpy(a_emp_tel[0], "01454112366");
strcpy(a_emp_tel[1], "0141237768");
strcpy(a_emp_tel[2], "0138974563");

EXEC SQL UPDATE EMPLOYEES 
SET DNO = :a_dno,
                 EMP_TEL = :a_emp_tel
            WHERE ENO = :a_eno; 

DELETE 문

다음은 DELETE문에서 사용할 수 있는 배열이다.

• 숫자형, 문자형 타입의 배열
• 구성 요소가 배열인 구조체
• 다음의 확장된 데이터 타입
  • SQL_DATE_STRUCT
  • SQL_TIME_STRUCT
  • SQL_TIMESTAMP_STRUCT
  • SQL_NUMERIC_STRUCT

제한 사항

UPDATE 문에서와 마찬가지로 구조체의 배열은 사용할 수 없고 아래의 예제처럼 구조체 배열 중 한 개의 배열 요소만 지정하여 사용할 수는 있다.

예:

EXEC SQL BEGIN DECLARE SECTION;
struct tag1 { int i1; int i2; int i3; } var1[10];
EXEC SQL END DECLARE SECTION;

EXEC SQL DELETE FROM T1 
WHERE I1 = :var1[0].i1 AND 
I2 = :var1[0].i2 AND
I3 = :var1[0].i3;       (O)

예제

다음은 DELETE문에서 배열 호스트 변수를 입력 호스트 변수로 사용하는 예를 보여준다.

< 예제 프로그램 : arrays1.sc >

EXEC SQL BEGIN DECLARE SECTION;
short a_dno[3]; 
EXEC SQL END DECLARE SECTION;

a_dno[0] = 4001;
a_dno[1] = 4002;
a_dno[2] = 2001;

EXEC SQL DELETE FROM EMPLOYEES
           WHERE DNO = :a_dno;

SELECT 문

다음은 SELECT 문에서 사용할 수 있는 배열이다. FETCH 문에서도 아래 배열들을 사용할 수 있으며 제한 사항도 동일하게 적용된다.

• 숫자형, 문자형 타입의 배열
• 구조체의 배열
• 구성 요소가 배열인 구조체
• 다음의 확장된 데이터 타입
  • QL_DATE_STRUCT
  • QL_TIME_STRUCT
  • QL_TIMESTAMP_STRUCT
  • QL_NUMERIC_STRUCT

제한 사항

INTO절의 한 호스트 변수가 배열이라면, INTO 절의 모든 호스트 변수가 배열이어야 한다. WHERE절의 입력 호스트 변수는 배열일 수 없다.

예:

EXEC SQL BEGIN DECLARE SECTION;
int var1;
int var2[10];
int var3[10];
EXEC SQL END DECLARE SECTION;
EXEC SQL SELECT * INTO :var1 
FROM T1 WHERE i1 = :var3;   (X)
또는 
EXEC SQL SELECT * INTO :var2 
FROM T1 WHERE i1 = :var3;   (X)

반환되는 레코드 개수가 배열 크기보다 많을 경우 "Returns too many rows" 오류가 발생한다.

예제

다음은 SELECT 문에서 배열 호스트 변수를 출력 호스트 변수로 사용하는 예를 보여준다. 이 때 입력 호스트 변수는 배열이 아니다.

< 예제 프로그램 : arrays2.sc >

EXEC SQL BEGIN DECLARE SECTION;
short s_dno;
short a_dno[5];
char  a_dname[5][30+1];
char  a_dep_location[5][9+1];
EXEC SQL END DECLARE SECTION;

s_dno = 3000;
EXEC SQL SELECT DNO, DNAME, DEP_LOCATION 
INTO :a_dno, :a_dname, :a_dep_location
            FROM DEPARTMENTS 
WHERE DNO > :s_dno;

FOR 절

배열 타입의 입력 호스트 변수를 사용하는 내장 SQL 문에서 배열 요소 중 일부만을 처리하고 싶은 경우가 있다. 예를 들어 loop문 안에서 FETCH를 하고 이 FETCH된 것을 다시 INSERT할 때 마지막 FETCH 시 FETCH된 건수가 배열 크기보다 작을 수 있다. 이 때 FOR 절을 이용하여 처리 개수를 지정하면 된다.

FOR 절은 배열 타입의 입력 호스트 변수 사용 시 처리할 배열 요소의 개수를 지정하는 기능을 한다. FETCH 문은 출력 호스트 변수를 사용해야 가능하다.

FOR 절을 사용하면, 처리할 배열 요소의 개수는 호스트 변수의 실제 배열 크기에 우선한다. 예를 들어 호스트 변수의 배열 크기는 10이고 FOR 절에서 개수를 5로 지정한다면 배열 요소 중 0번째부터 4번째까지 5개만 처리하게 된다.

내장 SQL 문 수행 시마다 처리 개수가 변경되는 경우, FOR 절을 사용하면 편리하다.

다음은 FOR 절을 사용할 수 있는 내장 SQL 문이다.

• INSERT 문
• UPDATE 문
• DELETE문
• FETCH 문

구문

EXEC SQL FOR <:host_var | constant> { INSERT … | UPDATE … | DELETE …| FETCH …} 

인자

• <:host_var>
  host_var에 저장된 값이 처리 개수가 된다. 이 host_var는 호스트 변수 선언부에서 선언하지 않아도 무방하다.
• <constant>
  처리할 배열 요소 개수를 상수로 지정한다.

주의 사항

FOR 절에서 지정하는 개수는 1 이상이어야 한다.

예:

EXEC SQL BEGIN DECLARE SECTION;
int cnt;
int var1[10];
int var2[10];
EXEC SQL END DECLARE SECTION;

cnt = 5;        (O)
EXEC SQL FOR :cnt INSERT INTO T1 VALUES (:var1, :var2);  
cnt = 0;        (X)
EXEC SQL FOR :cnt INSERT INTO T1 VALUES (:var1, :var2);
cnt = -1;       (X)
EXEC SQL FOR :cnt INSERT INTO T1 VALUES (:var1, :var2);

예제

다양한 SQL 문에서의 FOR 절 사용 예를 보여준다.

[예제 1] 다음은 INSERT 문에서 FOR 절을 사용하는 예를 보여준다. 처리할 배열 요소 개수는 호스트 변수 cnt를 이용하여 지정하고 a_goods의 0번째, 1번째의 값들만 GOODS 테이블에 삽입한다.

< 예제 프로그램 : hostvar.h >

EXEC SQL BEGIN DECLARE SECTION;
typedef struct goods
{
    char   gno[10+1];
    char   gname[20+1];
    char   goods_location[9+1];
    int    stock;
    double price;
} goods;
EXEC SQL END DECLARE SECTION;

< 예제 프로그램 : arrays1.sc >

EXEC SQL BEGIN DECLARE SECTION;
goods   a_goods[3];
int cnt;
EXEC SQL END DECLARE SECTION;

cnt = 2;
EXEC SQL FOR :cnt INSERT INTO GOODS VALUES (:a_goods);

[예제 2] 다음은 UPDATE 문에서 FOR 절을 사용하는 예를 보여준다. 처리할 배열 요소 개수는 상수를 이용하여 지정하고 배열 0번째부터 2건만 처리한다. 즉, a_employee.eno의 0번째, 1번째 값들에 해당하는 레코드의 DNO, EMP_TEL 칼럼값을 각각 a_employee.dno, a_employee.emp_tel의 0번째, 1번째 값으로 변경한다.

< 예제 프로그램 : arrays1.sc >

EXEC SQL BEGIN DECLARE SECTION;
struct
{
    int   eno[3];
    short dno[3];
    char  emp_tel[3][15+1];
} a_employee;
EXEC SQL END DECLARE SECTION;

EXEC SQL FOR 2 UPDATE EMPLOYEES 
SET DNO = :a_employee.dno,
                        EMP_TEL = :a_employee.emp_tel,
JOIN_DATE = SYSDATE
WHERE ENO = :a_employee.eno;

[예제 3] 다음은 DELETE문에서 FOR 절을 사용하는 예를 보여준다. 처리할 배열 요소 개수는 호스트 변수 cnt를 이용하여 지정하고 배열 0번째부터 2건만 처리한다. 즉, a_dno의 0번째, 1번째에 해당하는 레코드만 삭제한다.

< 예제 프로그램 : arrays1.sc >

EXEC SQL BEGIN DECLARE SECTION;
short a_dno[3]; 
int cnt;
EXEC SQL END DECLARE SECTION;

cnt = 2;
EXEC SQL FOR :cnt DELETE FROM EMPLOYEES  WHERE DNO = :a_dno;

[예제 4] 다음은 FETCH 문에서 FOR 절을 사용하는 예를 보여준다. 처리할 배열 요소 개수는 호스트 변수 cnt를 이용하여 지정하고 배열 0번째부터 2건의 데이터를 가져온다.

< 예제 프로그램 : cursor2.sc >

EXEC SQL BEGIN DECLARE SECTION;
/* declare array output host variables */
int     a_eno[ARRAY_SIZE];
short   a_dno[ARRAY_SIZE];
double  a_salary[ARRAY_SIZE];

/* declare input host variables */
double s_salary;

/* declare indicator variables */
SQLLEN a_eno_ind[ARRAY_SIZE];
SQLLEN a_dno_ind[ARRAY_SIZE];
SQLLEN a_salary_ind[ARRAY_SIZE];

int count;
EXEC SQL END DECLARE SECTION;

count = 2;
         /* Using with FOR clause */
         printf("FOR %d FETCH Result\n", count);
         EXEC SQL FOR :count FETCH EMP_CUR
                  INTO :a_eno     :a_eno_ind,
                       :a_dno     :a_dno_ind,

ATOMIC FOR 절

배열 타입의 입력 호스트 변수를 사용하는 내장 SQL 문에서 ATOMIC FOR 절을 사용할 경우 여러 개의 명령문을 묶어 하나의 명령문으로 처리하는 Atomic Array Insert를 수행한다.

따라서 ATOMIC FOR 절을 사용할 때 명령문이 1개라도 실패할 경우 모든 명령문은 실패한다. 결과값 역시 각각의 명령문이 아닌 한 개의 결과값으로 알려준다.

[그림 9‑1] Array Insert와 Atomic Array Insert의 결과값

기존의 Array Insert가 통신 비용을 줄여주는 장점이 있으나, Atomic Array Insert는 추가적으로 명령문 개수를 줄여주기 때문에 매우 빠른 성능을 제공한다.

다음은 Array Insert와 Atomic Array Insert를 비교한 표이다.

구분	Array Insert	Atomic Array Insert
명령문 개수	Array 개수	1개
결과 개수	Array 개수	1개
통신 속도	빠름	매우 빠름

[표 9‑1] Array Insert와 Atomic Array Insert의 차이

구문

EXEC SQL ATOMIC FOR <:host_var | constant> {INSERT … }

인자

• <:host_var>
  host_var에 저장된 값이 처리 개수가 된다. 이 host_var는 호스트 변수 선언부에서 선언하지 않아도 무방하다.
• <constant>
  처리할 배열 요소 개수를 상수로 지정한다.

주의 사항

ATOMIC FOR 절은 INSERT 문에서만 사용할 수 있으며, 다른 DML구문에서는 사용할 수 없다.

LOB 칼럼에 대해 Atomic Array Insert를 수행할 수 있으나, LOB 데이터를 전송할 때 오류가 발생하면 롤백이 되지 않는다. 따라서 이 경우 사용자가 직접 세이브포인트를 이용하여 롤백을 수행해야 한다.

이 외에도 ATOMIC FOR 절을 사용할 때의 몇 가지 제약사항을 다음과 같이 표로 정리한다.

구분	Array Insert	Atomic Array Insert
Foreign Key	정상 동작함	정상 동작함
유일키 제약	정상 동작함	정상 동작함
Not null 제약	정상 동작함	정상 동작함
Check 제약	정상 동작함	정상 동작함
트리거 Each Row	N번 수행됨	N번 수행됨
트리거 Each Statement	N번 수행됨	1번 수행됨
파티션 테이블	정상 동작함	정상 동작함
시퀀스	N번 수행됨	N번 수행됨
SYSDATE	N번 수행됨	1번 수행됨
LOB 칼럼	정상 동작함	오류 발생시 Atomic 속성 보장 못함
프로시져	정상 동작함	정상 동작함
INSERT구문에 SubQuery가 있을 때	Subquery를 위해 새로운 view를 N번 수행함	처음 수행된 view를 봄

[표 9‑2] Atomic Array Insert의 제약사항

예제

EXEC SQL ATOMIC FOR 20 INSERT INTO T1 VALUES( :data );

ONERR 절

배열 타입의 입력 호스트 변수를 사용하는 내장 SQL 문에서 각각의 배열 요소들이 성공했는지 여부를 알 수 있다. 따라서 실패한 배열이 발생할 경우 실패한 리스트만 DML을 사용한 관리가 가능하다.

구문

EXEC SQL ONERR <:ret_code, :err_code> 
{INSERT | UPDATE | DELETE}
또는
EXEC SQL ONERR <:ret_code, :err_code > 
FOR <:cnt | constant> {INSERT | UPDATE | DELETE}

인자

• <:ret_code, :err_code>
  첫번째 호스트 변수에는 SQL 구문의 결과값을 저장하며, 이 변수는 short 타입이어야 한다. 두번째 호스트 변수에는 에러코드를 저장하며, 이 변수는 int 타입이어야 한다.
• <:cnt>
  처리할 배열 요소 개수를 지정하는 호스트 변수이다. 이 변수는 호스트 변수 선언부에서 선언하지 않아도 무방하다.
• <constant>
  처리할 배열 요소 개수를 상수로 지정한다.

주의 사항

• ONERR 절에 사용되는 호스트 변수의 배열 크기는 SQL 구문에서 사용되는 배열의 크기보다 작으면 안된다.
• ONERR 절에 사용되는 호스트 변수의 배열 크기가 FOR 절과 함께 사용되는 경우 FOR 절의 DML 구문에서 지정한 크기보다 작으면 안된다.

예제

EXEC SQL ONERR :err_rc , :err_code 
UPDATE T1 SET c2 = c2+1 WHERE c1 = :var1;
EXEC SQL ONERR :err_rc , :err_code FOR :arr_count 
UPDATE T1 SET c2 = c2+1 WHERE c1 = :var1;

sqlca.sqlerrd

내장 SQL 문에서 배열 타입의 호스트 변수를 사용한 경우, 내장 SQL 문 수행 후 sqlca.sqlerrd 변수를 참조할 수 있다.

이 절에서는 sqlca.sqlerrd 변수에 어떤 수행 결과가 저장되는지에 대해서 설명한다.

sqlca.sqlerrd[2]

호스트 변수가 배열이 아닐 때 UPDATE 문, DELETE문을 수행한 후 이 값을 참조할 수 있다.

호스트 변수가 배열일 때 INSERT 문, UPDATE 문, DELETE문, SELECT 문을 수행 후 이 값을 참조할 수 있다.

다음은 각 내장 SQL 문 수행 후 sqlca.sqlcode가 SQL_SUCCESS일 경우에 sqlca.sqlerrd[2] 변수값의 의미에 대해 설명한다.

INSERT

호스트 변수가 배열이 아니라면 이 값은 1이다. 즉 1건의 레코드가 삽입되었음을 의미한다.

호스트 변수가 배열이라면 수행 성공한 개수가 저장된다. 따라서 이 값은 배열 크기보다 클 수 없다. 예를 들어 배열 크기 3의 호스트 변수를 사용하여 INSERT를 수행하고 3개 모두 성공하였다면 이 값은 3이다. 만약 0번째, 1번째 성공하고 2번째 실패하였다면 이 값은 2가 된다.

그러나 호스트 변수가 배열이라도 Atomic Array Insert를 수행한 경우 모두 성공하면 지정한 배열의 개수만큼 표시되지만, 하나라도 실패한 경우에는 0을 갖게 된다.

UPDATE/DELETE

UPDATE 또는 DELETE된 레코드 개수가 저장된다.

호스트 변수 값의 조건에 맞는 레코드는 하나 이상일 수 있으므로 이 값은 배열 크기보다 클 수 있다.

예를 들어 배열 크기 3의 호스트 변수를 사용하여 UPDATE를 수행하고 3개 모두 성공하였다고 가정하자. 이 경우 0번째 조건에 맞는 레코드가 3개, 1번째 조건에 맞는 레코드가 2개, 2번째 조건에 맞는 레코드가 0개였다면 총 5건의 레코드가 UPDATE 되었으므로 이 값은 5가 된다.

SELECT/FETCH

출력 호스트 변수가 배열이 아니라면 이 값은 무의미(garbage value)하다.

배열이라면 현재 SELECT(FETCH)된 레코드 개수가 저장된다. 즉, FETCH를 여러 번 수행할 경우 이 값이 계속해서 누적되는 것이 아니라 현재 FETCH된 레코드 개수만 저장된다. 따라서 이 값은 배열 크기보다 클 수 없다.

반환된 레코드 개수가 배열 크기보다 작거나 같다면 수행 결과(sqlca.sqlcode)는 SQL_SUCCESS이고 sqlca.sqlerrd[2]에는 반환된 레코드 개수가 저장된다.

반환된 레코드 개수가 없다면 수행 결과(sqlcal.sqlcode)는 SQL_NO_DATA이고 sqlca.sqlerrd[2]에는 0이 저장된다.

예를 들어 배열 크기 10의 호스트 변수를 출력 호스트 변수로 사용하여 SELECT를 수행하였다고 가정해 보자. 조건에 맞는 레코드가 5건이었다면 배열 0번째부터 차례로 5건의 레코드가 출력 호스트 변수에 저장되고 수행 결과(sqlca.sqlcode)는 SQL_SUCCESS이며 sqlca.sqlerrd[2]에는 5가 저장된다.

sqlca.sqlerrd[3]

이 변수에는 배열 타입의 입력 호스트 변수를 사용한 내장 SQL 문을 수행 후 성공한 배열 요소의 개수가 저장된다. 따라서 이 변수 값은 배열 크기보다 클 수 없다. 이 변수를 참조하기 위해서는 다음의 조건을 모두 만족하여야 한다.

• 배열 타입의 입력 호스트 변수를 사용한 내장 SQL 문을 수행 후 참조한다.
• INSERT, UPDATE, DELETE 또는 SQL/저장 프로시저문을 수행 후 참조한다.

Atomic Array Insert를 수행한 경우에는 성공시 1, 실패할 경우 0을 갖는다.

예제

배열 크기 3의 호스트 변수를 사용하여 UPDATE를 수행했다고 가정해보자. 이 때, 배열 0번째, 1번째 값을 이용한 UPDATE는 성공하고 배열 2번째 값을 이용한 UPDATE는 실패했다면 sqlca.sqlerrd[3]에는 2가 저장된다. 그리고 0번째 조건에 맞는 레코드가 3개, 1번째 조건에 맞는 레코드가 2개였다면 sqlca.sqlerrd[2]에는 5가 저장된다.

주의 사항

• sqlca.sqlerrd 변수값은 sqlca.sqlcode가 SQL_SUCCESS가 아니라면 무의미(garbage value)하다. 따라서 sqlca.sqlcode가 SQL_SUCCESS일 경우 sqlca.sqlerrd 변수값을 참조하여야 한다.
• AUTOCOMMIT 모드의 경우 배열 타입의 호스트 변수를 사용할 경우 배열 전체가 하나의 트랜잭션이 아니고 배열 요소 각각이 하나의 트랜잭션이다. 따라서 배열 요소 중 일부는 성공하고 일부는 실패한다면 성공한 트랜잭션의 변경 사항은 데이터베이스 서버에 영구히 저장된다.
  예를 들어 배열 크기 3의 호스트 변수를 사용하여 INSERT를 수행하는데 0번째와 1번째는 성공하고 2번째 실패하였다면 앞의 2건은 테이블에 삽입된다.

제한 사항

배열을 호스트 변수로 사용 할 때 몇 가지 제한 사항이 있다. 다음의 제한 사항들을 잘 숙지하여 프로그램 작성 시 유의하여야 한다.

호스트 변수 선언부 안에서

• 포인터 배열의 호스트 변수는 선언할 수 없고, 내장 SQL 문에서 사용할 수도 없다.

  예:

  EXEC SQL BEGIN DECLARE SECTION;
  char *var1[10]; (X)
  EXEC SQL END DECLARE SECTION;
  

• 1차원 배열만을 허용한다. 예외적으로, 문자열 표현을 위해 char 타입과 varchar 타입에 한해 2차원 배열을 허용한다.

  바른 예)

  EXEC SQL BEGIN DECLARE SECTION;
  char var1[10][10];
  int var2[10];
  EXEC SQL END DECLARE SECTION;
  

  틀린 예)

  EXEC SQL BEGIN DECLARE SECTION;
  char var3[10][10][10];
  int var4[10]10];
  EXEC SQL END DECLARE SECTION;
  

내장 SQL 문 안에서

SELECT 문, CURSOR 문의 입력 호스트 변수는 배열일 수 없다.

예:

EXEC SQL BEGIN DECLARE SECTION;
int var1[10];
int var2[10];
int var3[10];
EXEC SQL END DECLARE SECTION;

EXEC SQL SELECT I1, I2 INTO :var1, :var2 
FROM T1 WHERE I1 = :var3;   (X)

구조체와 배열

호스트 변수로 구조체의 배열 또는 구성 요소가 배열인 구조체를 선언하여 사용할 수 있다.

구조체의 배열

구조체 배열을 선언하고 사용하는 방법을 설명한다.

장점

• 한 테이블의 전체 칼럼에 여러 개의 레코드를 한꺼번에 삽입하고자 할 경우 INSERT 문의 입력 호스트 변수로 사용하면 편리하다.
• 한 테이블의 전체 칼럼을 한꺼번에 여러 건 가져오고자 할 경우 SELECT 문 또는 FETCH 문의 출력 호스트 변수로 사용하면 편리하다.

단점

• 지시자 변수를 지정할 수 없으므로 입력하고자 하는 값이 NULL인 경우 또는 SELECT 또는 FETCH되는 값이 NULL인 경우 사용할 수 없다.

제한 사항

• 2차원 배열은 허용하지 않으므로, 구조체의 배열을 사용할 때 그 구조체의 구성 요소는 배열일 수 없다.

  예:

  EXEC SQL BEGIN DECLARE SECTION;
  struct tag1 { int i1[10]; int i2[10]; } var1[10];       (X)
  EXEC SQL END DECLARE SECTION;
  

• SELECT 문 또는 FETCH 문의 INTO절에 구조체의 배열을 출력 호스트 변수로 사용할 경우, 출력 호스트 변수는 하나만 사용할 수 있다. 즉 다른 출력 호스트 변수들과 함께 사용할 수 없다. 따라서 INTO절에 사용할 호스트 변수가 구조체의 배열이라면 이 구조체의 구성 요소 개수는 SELECT절의 칼럼 개수와 동일해야 한다.

마찬가지로 INSERT 문의 VALUES절에 구조체의 배열을 입력 호스트 변수로 사용할 경우, 입력 호스트 변수는 하나만 사용할 수 있다. 즉 다른 입력 호스트 변수들과 함께 사용할 수 없다. 따라서 VALUES절에 사용할 호스트 변수가 구조체의 배열이라면 이 구조체의 구성 요소 개수는 INSERT 문의 칼럼 개수와 동일해야 한다.

이것은 호스트 변수가 구조체의 배열일 경우, 사용될 모든 호스트 변수를 구조체가 포함해야 한다는 내부적인 규칙 때문이다.

예:

EXEC SQL BEGIN DECLARE SECTION;
struct tag1 { int i1; int i2; } var1[10];
struct tag1 { int i3; int i4; } var2[10];
int var3;
EXEC SQL END DECLARE SECTION;

EXEC SQL SELECT I1, I2 INTO :var1 
FROM T1 WHERE I1 = :var3; (O)
EXEC SQL SELECT I1, I2, I3, I4 INTO :var1, :var2 
FROM T1 WHERE I1 = :var3; (X)

호스트 변수가 구조체의 배열일 경우, 지시자 변수를 지정할 수 없다. 따라서 구조체의 배열을 출력 호스트 변수로 사용할 경우 반환되는 칼럼값이 NULL이 아님을 보장하여야한다.

예:

EXEC SQL BEGIN DECLARE SECTION;
struct tag1 { int i1; int i2; char i3[10]; } var1[10];
struct tag2 { int i1_ind; int i2_ind; int i3_ind; } var1_ind[10];
EXEC SQL END DECLARE SECTION;

EXEC SQL SELECT * INTO :var1 :var1_ind;   (X)

예제

구조체의 배열을 호스트 변수로 선언하고 사용하는 다양한 예를 보여준다.

[예제 1] 다음은 구조체 배열을 INSERT 문의 입력 호스트 변수로 사용하는 예를 보여준다.

< 예제 프로그램 : hostvar.h >

EXEC SQL BEGIN DECLARE SECTION;
typedef struct goods
{
    char   gno[10+1];
    char   gname[20+1];
    char   goods_location[9+1];
    int    stock;
    double price;
} goods;
EXEC SQL END DECLARE SECTION;

< 예제 프로그램 : arrays1.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;
goods   a_goods[3];
EXEC SQL END DECLARE SECTION;

strcpy(a_goods[0].gno, "Z111100001");
strcpy(a_goods[1].gno, "Z111100002");
strcpy(a_goods[2].gno, "Z111100003");
strcpy(a_goods[0].gname, "ZZ-201");
strcpy(a_goods[1].gname, "ZZ-202");
strcpy(a_goods[2].gname, "ZZ-203");
strcpy(a_goods[0].goods_location, "AD0020");
strcpy(a_goods[1].goods_location, "AD0021");
strcpy(a_goods[2].goods_location, "AD0022");
a_goods[0].stock = 3000;
a_goods[1].stock = 4000;
a_goods[2].stock = 5000;
a_goods[0].price = 7890.21;
a_goods[1].price = 5670.45;
a_goods[2].price = 500.99;

EXEC SQL INSERT INTO GOODS VALUES (:a_goods);

[예제 2] 다음은 구조체 배열을 SELECT 문의 출력 호스트 변수로 사용하는 예를 보여준다.

< 예제 프로그램 : 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;
EXEC SQL END DECLARE SECTION;

< 예제 프로그램 : arrays2.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;
short s_dno;
department a_department[5];
EXEC SQL END DECLARE SECTION;

s_dno = 2000;
EXEC SQL SELECT * INTO :a_department
           FROM DEPARTMENTS WHERE DNO < :s_dno;

구성요소가 배열인 구조체

배열을 구성 요소로 가지는 구조체를 선언하고 사용하는 방법을 설명한다.

장점

• 한 테이블의 전체 칼럼에 여러 개의 레코드를 한꺼번에 삽입하고자 할 경우 INSERT 문의 입력 호스트 변수에 사용하면 편리하다.
• 한 테이블의 전체 칼럼을 한꺼번에 여러 건 가져오고자 할 경우 SELECT 문 또는 FETCH 문의 출력 호스트 변수에 사용하면 편리하다.
• 지시자 변수 지정이 가능하여 NULL값 처리가 가능하다.
• 구조체내의 구성 요소만 지정하여 UPDATE 문, DELETE문, SELECT 문의 WHERE절의 호스트 변수에 사용할 수 있다.
• 구조체의 배열은 입력 호스트 변수 리스트 또는 출력 호스트 변수 리스트에 호스트 변수를 하나만 사용할 수 있는 반면, 구성 요소가 배열인 구조체 타입은 입력 호스트 변수 리스트 또는 출력 호스트 변수 리스트에 다른 호스트 변수와 함께 사용할 수 있다.

예제

구성 요소가 배열인 구조체를 선언하고 사용하는 다양한 예를 보여준다.

[예제 1] 다음은 구성 요소가 배열인 구조체를 UPDATE 문의 입력 호스트 변수로 사용하는 예를 보여준다. a_emp_tel_ind에 SQL_NULL_DATA를 지정함으로써 EMP_TEL 칼럼값을 NULL로 변경한다.

< 예제 프로그램 : arrays1.sc >

EXEC SQL BEGIN DECLARE SECTION;
struct
{
    int   eno[3];
    short dno[3];
    char  emp_tel[3][15+1];
} a_employee;
int   a_emp_tel_ind[3];
EXEC SQL END DECLARE SECTION;

/* set host variables */
a_employee.eno[0] = 17;
a_employee.eno[1] = 16;
a_employee.eno[2] = 15;
a_employee.dno[0] = 1003;
a_employee.dno[1] = 1003;
a_employee.dno[2] = 1003;

/* set indicator variables */
a_emp_tel_ind[0] = SQL_NULL_DATA;
a_emp_tel_ind[1] = SQL_NULL_DATA;
a_emp_tel_ind[2] = SQL_NULL_DATA;

EXEC SQL UPDATE EMPLOYEES 
SET DNO = :a_employee.dno,
             EMP_TEL = :a_employee.emp_tel :a_emp_tel_ind,
             JOIN_DATE = SYSDATE
        WHERE ENO > :a_employee.eno;

[예제 2] 다음은 구성 요소가 배열인 구조체를 SELECT 문의 출력 호스트 변수로 사용하는 예를 보여준다.

< 예제 프로그램 : arrays2.sc >

EXEC SQL BEGIN DECLARE SECTION;
short s_dno;
struct 
{
    short dno[5];
    char  dname[5][30+1];
    char  dep_location[5][9+1];
    int   mgr_no[5];
} a_department2;
EXEC SQL END DECLARE SECTION;

s_dno = 2000;
EXEC SQL SELECT * INTO :a_department2
FROM DEPARTMENTS WHERE DNO < :s_dno;

예제 프로그램

arrays1.sc

$ALTIBASE_HOME/sample/APRE/arrays1.sc 참고

실행결과

$ is -f schema/schema.sql
$ make arrays1
$ ./arrays1
<ARRAYS 1>
------------------------------------------------------
[Scalar Array Host Variables With Insert]                         
------------------------------------------------------
3 rows inserted
3 times insert success

------------------------------------------------------
[Structure Array Host Variables With Insert]                      
------------------------------------------------------
3 rows inserted
3 times insert success

------------------------------------------------------
[Arrays In Structure With Insert]                                 
------------------------------------------------------
3 rows inserted
3 times insert success

------------------------------------------------------
[Error Case : Array Host Variables With Insert]                   
------------------------------------------------------
SQLCODE                : -69720
sqlca.sqlerrm.sqlerrmc : [ROW-2] ERR-11058(23000) : The row already exists in a unique index.
[ROW-3] ERR-11058(23000) : The row already exists in a unique index.

1 rows inserted
1 times insert success

------------------------------------------------------
[Scalar Array Host Variables With Update]                         
------------------------------------------------------
3 rows updated
3 times update success

------------------------------------------------------
[Arrays In Structure With Update]                                 
------------------------------------------------------
12 rows updated
3 times update success

------------------------------------------------------
[Scalar Array Host Variables With Delete]                         
------------------------------------------------------
6 rows deleted
3 times delete success

------------------------------------------------------
[For Clause With Insert]                                          
------------------------------------------------------
2 rows inserted
2 times insert success

------------------------------------------------------
[For Clause With Update]                                          
------------------------------------------------------
2 rows updated
2 times update success

------------------------------------------------------
[For Clause With Delete]                                          
------------------------------------------------------
3 rows deleted
2 times delete success

arrays2.sc

$ALTIBASE_HOME/sample/APRE/arrays2.sc 참고

실행결과

$ is -f schema/schema.sql
$ make arrays2
$ ./arrays2
<ARRAYS 2>
------------------------------------------------------
[Scalar Array Host Variables With Select]                         
------------------------------------------------------
DNO      DNAME                          DEP_LOCATION              
------------------------------------------------------
3001     CUSTOMER SUPPORT DEPT          London   
3002     PRESALES DEPT                  Peking   
4001     MARKETING DEPT                 Seoul    
4002     BUSINESS DEPT                  LA       
4 rows selected

------------------------------------------------------
[Structure Array Host Variables With Select]                      
------------------------------------------------------
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
3 rows selected

------------------------------------------------------
[Arrays In Structure With Select]                                 
------------------------------------------------------
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
3 rows selected

------------------------------------------------------
[Error Case : Array Host Variables]                               
------------------------------------------------------
Error : [-594092] Returns too many rows

------------------------------------------------------
[Execute Procedure With Array In-Binding]                         
------------------------------------------------------
Success execute procedure