작 성 자: 김선영 메 일: sunyzero@gmail.com 버 전: 1.8 Oracle Embedded SQL Pro*C : Chap 5 Copyright by SunYoung Kim 작 성 자: 김선영 메 일: sunyzero@gmail.com 버 전: 1.8

Preface Oracle 은 Oracle 의 등록상표입니다. Pro*C 는 Oracle 의 제품입니다. 예제는 지면 제약상 한줄에 여러라인이 코딩될 수 있습니다. 본 자료에 대한 질문사항은 연락처를 참고해주시기 바랍니다. E-mail address sunyzero@gmail.com sunyzero@empal.com

Cursor What is cursor ? cursor 란 SELECT 된 레코드들중에 현재 위치를 가리키는 논리적인 위치정보 ID FETCH 해야 하는 데이터가 복수인 경우에 사용 4단계(DECLARE, OPEN, FETCH, CLOSE)를 거쳐서 사용 DECLARE : 커서 선언. OPEN : 쿼리를 실행하고 결과(Active set)에 커서를 위치시킨다. FETCH : 결과로부터 레코드 단위별로 가져온다. CLOSE : 커서를 닫는다.

Cursor (con't) DECLARE 지시문 SELECT 문을 CURSOR 와 연결하여 선언한다. CURSOR 와 연결되는 SELECT 문에는 INTO 를 쓸 수 없다. DECLARE <cursor_identifier> CURSOR FOR <SQL literal> EXEC SQL DECLARE C CURSOR FOR SELECT NAME FROM E_MEMBERS WHERE AGE > 20;

Cursor (con't) OPEN 지시문 선언된 CURSOR 를 열어서(SQL 실행) Active set 을 만든다. OPEN <cursor_identifier> EXEC SQL OPEN C;

Cursor (con't) FETCH 지시문 Active set 으로부터 데이터를 읽어온다. Active set 에 더이상 데이터가 없다면 NOT FOUND 에러로 감지할 수 있다. FETCH <cursor_identifier> INTO <host_var> [ INDICATOR <ind_var> ] host var. 는 구조체도 될 수 있다. EXEC SQL WHENEVER NOT FOUND DO break; while (1) { EXEC SQL FETCH C INTO :a_name INDICATOR :ind_name; printf("Name : %s\n", a_name); }

Cursor (con't) CLOSE 지시문 열린 커서를 닫고 자원을 해제한다. CLOSE <cursor_identifier> EXEC SQL CLOSE C;

Caution:Cursor (con't) 주의 CURSOR 가 열려있을때 COMMIT 트랜잭션은 ORA-01555 에러 발생 Active set => FETCH 시에 실제 데이터가 읽혀짐 즉, 긴 처리시간을 필요로 하는 경우에 중간에 다른 세션에서 데이터를 변경시키고 COMMIT 하게 되면 롤백 세그먼트를 잃어버리게 됨. = CURSOR 의 OPEN 시점과 현재의 SCN(System Change Number)이 달라짐. 모든 SQL 문은 긴시간동안 수행되는 것은 좋지 않음

Caution:Cursor (con't) 주의 따라서 loop 를 돌면서 FETCH 한 뒤 다른 곳에 데이터를 입력 후 COMMIT ? ANSI 표준이 아님 loop 內 에서 SCN 이 변경되어 롤백 세그먼트를 찾지 못하게 됨(ORA-01555) CURSOR 를 사용하는 경우에 Active set 의 범위를 줄이는것이 좋음. 즉, Active set이 큰 경우라면 롤백 세그먼트의 크기를 충분히 크게 하는게 좋음. 단, auto 로 지정하면 너무 많은 메모리를 소모할 가능성이 있음

Example:Cursor pr6.pc : basic example #define MAX_ITERATION 50 typedef struct e_member { int idnum; char name[21]; short age; char desc[513]; int uniqid; } E_MEMBER; void pr_em(const E_MEMBER *s); int main(int argc, char *argv[]) { int i; E_MEMBER *p_em; if (argc != 3) { printf("Usage : %s <username> <passwd>\n",argv[0]); exit(EXIT_SUCCESS); } if ((p_em = (E_MEMBER *) calloc(1, sizeof(E_MEMBER))) == NULL) { fprintf(stderr, "[%s:%d] Fail: calloc(): %s\n", __FILE__, __LINE__, strerror(errno)); return EXIT_SUCCESS;

Example:Cursor (con't) /* (연습) get_dbconn() 을 이용하여 DB 연결을 한다 */ EXEC SQL DECLARE C CURSOR FOR SELECT IDNUM, NAME, AGE, DESCRIPT, UNIQID FROM E_MEMBERS WHERE AGE > 30; EXEC SQL OPEN C; /* open cursor */ /* 숙제1) uniqid 에 indicator 를 사용한다. */ /* 숙제2): WHENEVER statement 를 제거하고 SQLCODE값으로 대치하도록 함 */ EXEC SQL WHENEVER NOT FOUND DO break; for(i=0; i<MAX_ITERATION; i++) { EXEC SQL FETCH C INTO :p_em->idnum, :p_em->name, :p_em->age, :p_em->desc, :p_em->uniqid; pr_em(p_em); } EXEC SQL CLOSE C; EXEC SQL COMMIT WORK RELEASE; /* (연습) 라이브러리로 교체하여 작성 */ return EXIT_SUCCESS;

ORACA oraca(Oracle Communication Area) sqlca 의 정보보다 자세한 정보를 요구할때 사용 oraca.h : #include 후 EXEC ORACLE OPTION (ORACA=YES) 로 활성화 시킴 /* ORACA */ struct oraca { /* text */ char oracaid[8]; /* Reserved */ /* ub4 */ int oracabc; /* Reserved */ /* Flags which are setable by User. */ /* ub4 */ int oracchf; /* <> 0 if "check cur cache consistncy"*/ /* ub4 */ int oradbgf; /* <> 0 if "do DEBUG mode checking" */ /* ub4 */ int orahchf; /* <> 0 if "do Heap consistency check" */ /* ub4 */ int orastxtf; /* SQL stmt text flag */ #define ORASTFNON 0 /* = don't save text of SQL stmt */ #define ORASTFERR 1 /* = only save on SQLERROR */ #define ORASTFWRN 2 /* = only save on SQLWARNING/SQLERROR */ #define ORASTFANY 3 /* = always save */

ORACA (con't) oraca(Oracle Communication Area) struct { /* ub2 */ unsigned short orastxtl; /* text */ char orastxtc[70]; } orastxt; /* text of last SQL stmt */ /* ub2 */ unsigned short orasfnml; /* text */ char orasfnmc[70]; } orasfnm; /* name of file containing SQL stmt */ /* ub4 */ int oraslnr; /* line nr-within-file of SQL stmt */ /* ub4 */ int orahoc; /* highest max open OraCurs requested */ /* ub4 */ int oramoc; /* max open OraCursors required */ /* ub4 */ int oracoc; /* current OraCursors open */ /* ub4 */ int oranor; /* nr of OraCursor re-assignments */ /* ub4 */ int oranpr; /* nr of parses */ /* ub4 */ int oranex; /* nr of executes */ };

ORACA (con't) oracaid oracabc oraca 구조체의 크기 (byte) oracchf : cur cache consistncy checking flag 0 이 아닌 경우 현재 커서 캐시의 일관성을 체크하도록 함 oradbgf : do DEBUG mode checking flag 0 이 아닌 경우 디버깅 모드로 작동하도록 함 orahchf : do Heap consistency checking flag 0 이 아닌 경우 힙의 일관성을 체크하도록 함

ORACA (con't) orastxtf : SQL stmt text flag #define ORASTFNON 0 /* = don't save text of SQL stmt */ #define ORASTFERR 1 /* = only save on SQLERROR */ #define ORASTFWRN 2 /* = only save on SQLWARNING/SQLERROR */ #define ORASTFANY 3 /* = always save */ struct { } orastxt : text of last SQL stmt orastxtl : orastxtc 의 길이 (byte) orastxtc : orastxtf 조건에 충족할때 SQL 문을 70 B 까지 저장함 struct { } orasfnm : name of file containing SQL stmt orasfnml : orasfnmc 의 길이 (byte) orasfnmc : 현재 SQL 구문을 담고 있는 파일명을 70 B 까지 저장함

ORACA (con't) oraslnr : line nr-within-file of SQL stmt oraca.orastxtf = ORASTFERR 시 .pc 의 오류행 번호, 디버깅시 유효) orahoc : highest max open OraCurs requested 최대 커서 오픈 제한 oramoc : max open OraCursors required 최대로 오픈할 수 있는 커서 수 oracoc : current OraCursors open 현재 사용중인 오픈 커서 수

ORACA (con't) oranor : nr of OraCursor re-assignments 재할당된 커서캐시의 개수 oranpr : nr of parses 파싱된 SQL 문의 개수 oranex : nr of executes 실행된 SQL 문의 개수

ORACA example ORACA 의 사용 error handling 을 하기에 앞서 로깅을 하는 방식으로 라이브러리 수정! #include <oraca.h> EXEC ORACLE OPTION (ORACA=YES); ... (생략) ... EXEC SQL WHENEVER SQLERROR DO branch_ora_err(); oraca.orastxtf = ORASTFERR; void branch_ora_err() { log_ora_err(); /* error logging */ } void log_ora_err() printf("\n%.*s\n", sqlca.sqlerrm.sqlerrml, sqlca.sqlerrm.sqlerrmc); printf("in \"%.*s...\'\n", oraca.orastxt.orastxtl, oraca.orastxt.orastxtc); printf("on line %d of %.*s.\n\n", oraca.oraslnr, oraca.orasfnm.orasfnml, oraca.orasfnm.orasfnmc); error handling 을 하기에 앞서 로깅을 하는 방식으로 라이브러리 수정!