[번역] 데이터유형과 호스트 변수 (Datatypes and Host Variables ) - 03

목 차

▣ 오라클 데이터 유형 (Oracle Datatypes)
▣ 호스트 변수 ( Host Variables )
▣ 지시자 변수 ( Indicator Variables )
▣ VARCHAR 변수 ( VARCHAR Variables )
▣ 커서 변수 ( Cursor Variables )
▣ CONTEXT 변수 ( CONTEXT Variables )
▣ Universal ROWIDs
▣ 호스트 구조체 ( Host Structures )
▣ 포인터 변수 ( Pointer Variables )
▣ Globalization Support
▣ NCHAR 변수 ( NCHAR Variables )

5) 커서 변수 ( Cursor Variables )
커서 변수의 장점은 다음과같다.
▶ 쉬운 유지보수
커서 변수가 열리는 스토어드 프로시져내로 쿼리가 집중된다.
만약 커서가 변경될 필요가 있으면, 한곳의 커서내용만 변경하면되고 각 어플리케이션
을 변경할 필요가 없다.
▶ 편리한 보안성
어플리케이션의 사용자는 Pro*C/C++응용프로그램이 서버에 연결할때 사용한 사용자이름
이 된다. 사용자는 쿼리에서 사용된 테이블의 읽기 권한이 아닌 실행권한을 가져야만
한다. 이러한 것은 테이블의 컬럼에 접근하는 것을 제한 할 수 있다.

5-1) 커서 변수 선언
SQL_CURSOR 유형을 사용해서 커서 변수를 선언할 수 있다. 예를 들면 아래와 같다.

EXEC SQL BEGIN DECLARE SECTION;
sql_cursor emp_cursor; /* a cursor variable */
SQL_CURSOR dept_cursor; /* another cursor variable */
sql_cursor *ecp; /* a pointer to a cursor variable */
...
EXEC SQL END DECLARE SECTION;
ecp = &emp_cursor; /* assign a value to the pointer */

대소문자를 가리지는 않으나 대소문자를 혼용해서 사용해서는 않된다.
커서변수는 함수의 인자로 념겨서 사용할 수 있으며, 함수를 커서변수를 리턴하도록
선언할 수 도 있고 포인터 변수로도 리턴가능하다.

5-2) 커서 변수 할당
커서 변수를 사용하기에 앞서 커서를 할당해야한다. 전처리 명령어인 ALLOCATE명령어
를 사용해서 커서를 할당한다. 예를 들어, 위의 예제에서 선언한 SQL_CURSOR emp_cursor를
할당하기위해서 아래와 같은 문을 작성한다 :

EXEC SQL ALLOCATE :emp_cursor;

커서 변수를 위해서 할당된 메모리는 CLOSE 문이 실행될때나 연결이 닫힐경우 해제된다.
EXEC SQL CLOSE :emp_cursor;

5-3) 커서 변수 열기
오라클 데이터베이스 서버상에서 커서변수는 열어야한다.SQL OPEN 명령어로 커서 변수를
열수는 없다. 예를 들어, 데이터베이스에 저장된 아래의 PL/SQL 패키지를 고려해 보자.

CREATE PACKAGE demo_cur_pkg AS
TYPE EmpName IS RECORD (name VARCHAR2(10));
TYPE cur_type IS REF CURSOR RETURN EmpName;
PROCEDURE open_emp_cur ( curs IN OUT cur_type, dept_num IN NUMBER);
END;

CREATE PACKAGE BODY demo_cur_pkg AS
CREATE PROCEDURE open_emp_cur (
curs IN OUT cur_type,
dept_num IN NUMBER) IS
BEGIN
OPEN curs FOR
SELECT ename FROM emp
WHERE deptno = dept_num
ORDER BY ename ASC;
END;
END;

위의 패키지가 저장된후, Pro*C/C++프로그램에서는 open_emp_cur커서를 사용할 수 있다.
아래의 예제를 보자.
...
sql_cursor emp_cursor;
char emp_name[11];
...
/* 커서변수 할당 */
EXEC SQL ALLOCATE :emp_cursor;
...
/* 서버에서 커서를 OPEN */
EXEC SQL EXECUTE
begin
demo_cur_pkg.open_emp_cur(:emp_cursor, :dept_num);
end;
;

EXEC SQL WHENEVER NOT FOUND DO break;

for (;;)
{
EXEC SQL FETCH :emp_cursor INTO :emp_name;
printf("%sn", emp_name);
}
...
PL/SQL 블록내에서 커서를 OPEN하기위해서, 블록내에 커서를 아래의 예처럼 선언해야
한다.

sql_cursor emp_cursor;
int dept_num = 10;
...

EXEC SQL EXECUTE
BEGIN
OPEN :emp_cursor FOR SELECT ename FROM emp
WHERE deptno = :dept_num;
END;
END-EXEC;
...

아래의 예제는 커서절과 함께 임베디드 SQL문을 사용해서 커서변수를 OPEN하는 경우를
보여주고 있다.

...
sql_cursor emp_cursor;
...
EXEC ORACLE OPTION(select_error=no);
EXEC SQL
SELECT CURSOR(SELECT ename FROM emp WHERE deptno = :dept_num)
INTO :emp_cursor FROM DUAL;
EXEC ORACLE OPTION(select_error=yes);
위의 예제처럼, cursor절이 포함된 쿼리를 사용하기전에,SELECT_ERROR옵션을 NO로 설정
해야만 한다. 이것음 부모 커서의 취소를 막고 프로그램이 에러없이 작동되도록한다.


5-4) 커서 변수 해제와 닫기

커서변수를 닫기위해서 CLOSE명령어를 사용한다. 예제는 아래와같다.

EXEC SQL CLOSE :emp_cursor;

할당된 커서 변수는 재사용이 가능하지만, 서버와 연결이 끊겼다거나, 재연결된경우
커서변수를 재할당 해야만 한다.

EXEC SQL FREE :emp_cursor;

할당된 커서를 할당해제하기위해서는 위와같이 사용하면된다.

5-5) 예제 (cv_demo.sql 과 sample11.pc )

아래의 예제는 PL/SQL 스크립트와 Pro*C/C++프로그램으로 커서변수를 사용하는 방법을
보여주고 있다.

/******************************************************************************
* cv_demo.sql
******************************************************************************/

-- PL/SQL source for a package that declares and
-- opens a ref cursor

CONNECT SCOTT/TIGER;

CREATE OR REPLACE PACKAGE emp_demo_pkg as
TYPE emp_cur_type IS REF CURSOR RETURN emp%ROWTYPE;
PROCEDURE open_cur(curs IN OUT emp_cur_type, dno IN NUMBER);
END emp_demo_pkg;

CREATE OR REPLACE PACKAGE BODY emp_demo_pkg AS
PROCEDURE open_cur(curs IN OUT emp_cur_type, dno IN NUMBER) IS
BEGIN
OPEN curs FOR SELECT *
FROM emp WHERE deptno = dno
ORDER BY ename ASC;
END;
END emp_demo_pkg;

/******************************************************************************
* sample11.pc
*
* 커서변수를 사용해서 EMP 테이블에서 데이터를 취득한다.
* EMP_DEMO_PKG 패키지 내의 open_cur를 PL/SQL 프로시져 에서 연다.
*
******************************************************************************/

#include <stdio.h>
#include <sqlca.h>
#include <stdlib.h>
#include <sqlda.h>
#include <sqlcpr.h>

//////////////////////////////////////////////////////////////////////
// 에러처리 함수
//////////////////////////////////////////////////////////////////////
void sql_error(msg)
char *msg;
{
size_t clen, fc;
char cbuf[128];
clen = sizeof (cbuf);

sqlgls((char *)cbuf, (size_t *)&clen, (size_t *)&fc);

printf("n%sn", msg);
printf("Statement is--n%sn", cbuf);
printf("Function code is %ldnn", fc);

sqlglm((char *)cbuf, (size_t *) &clen, (size_t *) &clen);

printf ("n%.*sn", clen, cbuf);

EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL ROLLBACK WORK RELEASE;

exit(EXIT_FAILURE);
}

//////////////////////////////////////////////////////////////////////
// 메인 함수
//////////////////////////////////////////////////////////////////////
void main()
{
char temp[32];

EXEC SQL BEGIN DECLARE SECTION;

char *uid = "scott/tiger";
SQL_CURSOR emp_cursor;
int dept_num;

struct
{
int emp_num;
char emp_name[11];
char job[10];
int manager;
char hire_date[10];
float salary;
float commission;
int dept_num;
} emp_info;

struct
{
short emp_num_ind;
short emp_name_ind;
short job_ind;
short manager_ind;
short hire_date_ind;
short salary_ind;
short commission_ind;
short dept_num_ind;
} emp_info_ind;

EXEC SQL END DECLARE SECTION;

EXEC SQL WHENEVER SQLERROR do sql_error("Oracle error");
/* 오라클 연결 */
EXEC SQL CONNECT :uid;
/* 커서변수 할당 */
EXEC SQL ALLOCATE :emp_cursor;
/* 데이터가 없을 경우 반복문 EXIT */
EXEC SQL WHENEVER NOT FOUND DO break;
for (;;)
{
printf("nEnter department number (0 to exit): ");
gets(temp);
dept_num = atoi(temp);
if (dept_num <= 0)
break;
EXEC SQL EXECUTE
begin
emp_demo_pkg.open_cur(:emp_cursor, :dept_num);
end;
END-EXEC;

printf("nFor department %d--n", dept_num);
printf("ENAME SAL COMMn");
printf("----- --- ----n");

/* EMP 테이블의 데이터를 취득, 지시자 구조체 사용 */

for (;;)
{
EXEC SQL FETCH :emp_cursor
INTO :emp_info INDICATOR :emp_info_ind;

printf("%s ", emp_info.emp_name);
printf("%8.2f ", emp_info.salary);

if (emp_info_ind.commission_ind != 0)
printf(" NULLn");
else
printf("%8.2fn", emp_info.commission);
}
}

/* 커서 닫기 */
EXEC SQL WHENEVER SQLERROR CONTINUE;

EXEC SQL CLOSE :emp_cursor;

/* 오라클 연결 종료 */
EXEC SQL ROLLBACK WORK RELEASE;

exit(EXIT_SUCCESS);
}

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