ProcC Style System l Dynamic Cursor l Savepoint

Proc*C 기초 Style System 김도형

강의에서 다루지 않는 내용 l Dynamic Cursor l Savepoint l Large Object: LOB l Collection l Pro*C++ l Compile Option l Multi Thread l Host Array

Pro*C/C++ Precompiler에 대해 l High Level Language Source에 내장 SQL문 을 사용 가능하게 하는 Programming Tool

Window에서의 Pro*C의 설치(1/4) Oracle Programmer Component를 설치하여야 Pro*C/C++를 사용할수 있음

Window에서의 Pro*C의 설치(2/4) l binproc. exe을 확인

Window에서의 Pro*C의 설치(3/4) l precomppublicoraca. h, precomppublicsqlca. h 을 확인

Window에서의 Pro*C의 설치(4/4) l precomplibmsvcora SQL 9. LIB 을 확인

Visual C++의 환경 설정 (1/11) l Project 생성 l Project Name에 proctest( 프로젝트명)를 입력

Visual C++의 환경 설정 (2/11) l 새로운 Source File을 추 가 l 추가 File 종류를 C Source 로 proctest. pc를 추가

Visual C++의 환경 설정 (3/11) l 추가 File 종류를 C Source 로 proctest. c를 추가 l Proctest. pc를 Compile 대 상에서 제외 시킴

Visual C++의 환경 설정 (5/11) l Project 환경설정 l 추가 INCLUDE DIRECTORY : C: oracleora 92precomppubl ic : header file의 경로를 입력 l DEBUG 정보 형식 : Editor Continue용 Program DB를 선 택

Visual C++의 환경 설정 (6/11) l 최적화 : 無効(/Od)를 선택 l C: oracleora 92precompl ibmsvc : Library file의 경 로를 입력

Visual C++의 환경 설정 (7/11) l ora. SQL 9. LIB : Library file Name 입력 l DEBUG 정보 생성 : はい(/ DEBUG)를 선택

Visual C++의 환경 설정 (8/11) l Proc proctest. pc : proc 프 로젝트명. pc 을 입력 l Text Editor의 설정

Visual C++의 환경 설정 (10/11) l Test Source Code의 입력 Proctest. pc

Visual C++의 환경 설정 (11/11) l Project의 Rebuild Ctrl+Alt+F 7 l 실행결과(Ctrl+F 5)

내장 SQL : Host 변수 l Host(Source) Program과 Oracle간의 Data의 통신에 사용 됨 ¡ 출력 호스트 변수 (Oracle -> Host Program) l (예) SELECT에서 리턴된 데이타를 받기 위해 사용되는 변수 ¡ 입력 호스트 변수 (Host Program -> Oracle) l (예) INSERT에서 입력할 데이타를 받기 위해 사용되는 변수 l 내장 SQL에 사용될 때는 변수명 앞에 콜론을 붙여 사용 ¡ Ex) : host_variable l DECLARE SECTION 절에 정의 ¡ Ex) EXEC SQL BEGIN DECLARE SECTION; int host_variable; EXEC SQL END DECLARE SECTION;

내장 SQL : Host 변수 : VARCHAR 형 l VARCHAR을 이용해서 가변 길이 문자열을 취급할수 있음 l Precompiler에 의한 VARCHAR의 해석 VARCHAR username[20]; ↓ struct { unsigned short len; unsigned char arr[20]; } username;

내장 SQL : DECLARE 문 l Host 변수와 Cursor 변수를 선언 ¡ Host 변수의 선언 EXEC SQL BEGIN DECLARE SECTION; int host_variable; EXEC SQL END DECLARE SECTION; ¡ Cursor 변수의 선언 EXEC SQL DECLARE emp_cursor CURSOR FOR SELECT ename, empno, sal FROM emp WHERE deptno = : dept_number;

내장 SQL : CURSOR : CLOSE l FETCH가 끝난 CURSOR의 Resource(Memory 등)를 System에 반환함 ¡Ex) EXEC SQL CLOSE emp_cursor; l CLOSE후의 CURSOR로 부터의 FETCH는 허용되지 않음 l 필요에 의해 CURSOR의 재 사용이 가능함>입력 Host 변수에 값 할당후 재 OPEN등.

내장 SQL : SELECT l Data Base에 질의를 수행 ¡ Ex) EXEC SQL SELECT ename, job, sal + 2000 INTO : emp_name, : job_title, : salary FROM emp WHERE empno = : emp_number; l Oracle은 INTO절의 출력 Host변수에 값을 설정 함 l 추출 Column수와 INTO절의 출력 Host변수의 갯 수는 일치 하여야함 l 질의가 복수의 Record를 반환하는 경우에는 반드 시 CURSOR를 사용

내장 SQL : UPDATE, DELETE l UPDATE ¡Table또는 View의 값을 변경 l. Ex) EXEC SQL UPDATE emp SET sal = : salary, comm = : commission WHERE empno = : emp_number; l DELETE ¡Table또는 View로 부터 Record를 삭제 l. Ex) EXEC SQL DELETE FROM emp WHERE deptno = : dept_number ;

내장 SQL : WHENEVER : Scope l WHENEVER문은 선언문이므로, 위치에 영향을 받음. 즉, WHENEVER문은 Program의 최초의 SQL문 보다 먼저 선언 되어야 함. l 한번 선언된 WHENEVER문은 같은 <condition>으로 새로운 WHENEVER문 이 선언되기 전까지 유효함. Ex) step 1: EXEC SQL WHENEVER SQLERROR STOP; EXEC SQL CONNECT : username IDENTIFIED BY : password; . . . goto step 3; step 2: EXEC SQL WHENEVER SQLERROR CONTINUE; EXEC SQL UPDATE emp SET sal = sal * 1. 10; . . . step 3: EXEC SQL DROP INDEX emp_index; . . .

내장 SQL : WHENEVER : DO. . . EXEC SQL WHENEVER SQLERROR DO handle_insert_error("INSERT error"); EXEC SQL INSERT INTO emp (empno, ename, deptno) VALUES (: emp_number, : emp_name, : dept_number); EXEC SQL WHENEVER SQLERROR DO handle_delete_error("DELETE error"); EXEC SQL DELETE FROM dept WHERE deptno = : dept_number; . . . handle_insert_error(char *stmt) { switch(sqlca. sqlcode) { case -1: /* duplicate key value */. . . break; case -1401: /* value too large */. . . break; default: /* do something here too */. . . break; } } handle_delete_error(char *stmt) { printf("%s¥n¥n", stmt); if (sqlca. sqlerrd[2] == 0) { /* no rows deleted */. . . } else {. . . }

내장 SQL : WHENEVER : DO BREAK / DO CONTINUE #include <sqlca. h> #include <stdio. h> main() { char *uid = "scott/tiger"; struct { char ename[12]; float sal; float comm; } emp; EXEC SQL WHENEVER SQLERROR GOTO whoops; EXEC SQL CONNECT : uid; EXEC SQL DECLARE c CURSOR FOR SELECT ename, sal, comm FROM EMP ORDER BY ENAME ASC; EXEC SQL OPEN c; EXEC SQL WHENEVER NOT FOUND DO BREAK; EXEC SQL WHENEVER SQLERROR DO CONTINUE; while (1) { EXEC SQL FETCH c INTO : emp; printf("%s %7. 2 f %9. 2 f¥n", emp. ename, emp. sal, emp. comm); } EXEC SQL WHENEVER SQLERROR CONTINUE; EXEC SQL CLOSE c; exit(EXIT_SUCCESS); whoops: printf("%. *s¥n", sqlca. sqlerrml, sqlca. sqlerrmc); exit(EXIT_FAILURE); }

내장 SQL : WHENEVER : 실습

내장 SQL : WHENEVER : 실습

내장 SQL : SQLCA struct sqlca { char sqlcaid[8]; /* "SQLCA" 문자 스트링 */ long sqlabc; /* slqca구조체의 길이 */ long sqlcode; /* 에러코드 */ struct { unsigned short sqlerrml; /* 에러 메시지 길이 */ char sqlerrmc[70]; /* 에러 메시지 내용 */ } sqlerrm; char sqlerrp[8]; /* reserved */ long sqlerrd[6]; /* sqlerrp[0] : reserved sqlerrp[1] : reserved sqlerrd[2] : 수행된 행의 개수

내장 SQL : SQLCA sqlerrd[3] : reserver sqlerrd[4] : 파싱 에러 옵셋 sqlerrd[5] : reserved */ char sqlwarn[8]; /* sqlwarn[0] : 경고 플래그 sqlwarn[1] : 문자스트링이 절단된 경우 sqlwarn[2] : 안쓰임. sqlwarn[3] : SELECT문에서 필드 개수와 INTO문의 호스트 변수 개수가일치하지 않음 sqlwarn[4] : DELETE 또는 UPDATE문에서 where절이 없음. sqlwarn[5] : reserved sqlwarn[6] : 안쓰임. sqlwarn[7] : 안쓰임. */ char sqlext[8]; /* reserved */ };

내장 SQL : SQLCA l Sqlerrm : 직전에 실행된 SQL이 Error인 경우 Error Message 가 저장됨 l 필드의 의미 ¡sqlerrml : sqlerrmc에 저장되어 있는 문자열의 길이 ¡sqlerrmc : sqlcode내에 저장되어 있는 Error Code 에 대한 Text Message 가 저장됨. 문자열은 NULL 로 종료하지 않으므로 sqlerrml에서 길이를 확인할 필요가 있음 Ex) sqlca. sqlerrmc[sqlca. sqlerrml]='₩ 0'; printf("%s", sqlca. sqlerrmc);

내장 SQL : CONNECT l Data를 질의하거나 조작하기전에 Pro*C/C++ Program을 Database에 접속시켜야함 l Database에 Login하기위해 CONNECT을 사용 함 Ex) EXEC SQL CONNECT : username IDENTIFIED BY : password ; EXEC SQL CONNECT : usr_pwd ¡ username 및password는 문자형 Host 변수임 ¡ Host 변수 usr_pwd는 /(slash)로 구분된 Username, Password를 포함

내장 SQL : CONNECT l 원격에서 오라클 서버를 접속하려면, 먼저 tnsname. ora 파일을 편집 l tnsname. ora 는 C: oracleora 92networkADMIN에 위치함. (Version에 따라 차이가 있음) Ex) BATCH_UT = ( DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192. 168. 1. 1)(PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = VPMS)) )

내장 SQL : CONNECT EX) char username[10] = "scott"; char password[10] = "tiger"; char db_string[20] = “BATCH_UT"; … EXEC SQL CONNECT : username IDENTIFIED BY : password USING : db_string;

내장 SQL : CONNECT

내장 SQL : COMMIT

내장 SQL : ROLLBACK l ROLLBACK 문은 Host 변수및 Program의 흐름에 영향을 주지 않음 l ROLLBACK TO SAVEPOINT문이 수행되면 일어나는 작업들 ¡ 지정된 SAVEPOINT이후의 Database의 변경은 전부 취소됨 ¡ 지정된 SAVEPOINT이후의 모든 SAVEPOINT는 삭제됨 ¡ 지정된 SAVEPOINT이후에 일어난 모든 Record Lock, Table Lock은 해 제됨 l 다음 예제는 Transaction을 Rollback하고 Oracle과의 연결을 해제함 ¡ Ex) EXEC SQL ROLLBACK WORK RELEASE; l RELEASE Keyword를 사용하므로써, Program에서 사용했던 Oracle Resource(Lock, Cursor등)를 전부 해제하고, Database와의 연결을 종료함. l WHENEVER SQLERROR GOTO문으로 Error처리 Routine으로 분 기한경우, 그 Routine의 ROLLBACK문에서 Error가 발생하면 무한 Loop에 빠질 우려가 있음. 무한 Loop를 방지하기 위해 ROLLBACK문 전에 WHENEVER SQLERROR CONTINUE를 기술 해야 함

내장 SQL : ROLLBACK

내장 PL/SQL : PL/SQL BLOCK l Pro*C/C++에서는 PL/SQL Block도 사용 가능 l SQL문을 사용할 수 있는 곳이라면 어디라도 PL/SQL Block을 사용할 수 있음 l PL/SQL Block을 사용하기 위해서는Keyword EXEC SQL EXECUTE 및 END-EXEC; 사이에 PL/SQL Block을 포함 시켜야함 l Ex)

내장 PL/SQL : 실습 l proc plsql. pc SQLCHECK=SEMANTICS userid=scott/tiger@BATCH_UT

내장 PL/SQL : Stored PL/SQL Call l 다음 예는 raise_salary라는 이름의 Stored Procedure를 호출 EXEC SQL EXECUTE BEGIN raise_salary(: emp_id, : increase); END; END-EXEC; l 다음 예는 emp_actions이란 Package에 속하는 raise_salary란 이 름의 Stored Procedure를 호출 EXEC SQL EXECUTE BEGIN emp_actions. raise_salary(: emp_id, : increase); END-EXEC; l Parameter는 IN, OUT 두종류가 있음 l 내장 PL/SQL이 포함된 Program을 Pre. Compile하는 경우에는 SQLCHECK=SEMANTICS Compile Option을 사용해야 함

내장 PL/SQL : Stored PL/SQL Call : 실습

내장 PL/SQL : Stored PL/SQL Call : 실습

내장 PL/SQL : Stored PL/SQL Call : 실습

내장 PL/SQL : Stored PL/SQL Call : 실습

내장 PL/SQL : Stored PL/SQL Call : 실습 l 실행결과

내장 PL/SQL : 심화문제 l 주어진 emp. data파일의 내용을 EMP table 에 추가 l emp. data파일 형식은 CSV의 Text File l 내장 SQL및 내장 PL/SQL 어느쪽을 사용해 도 무방 l Hint : ¡C function : fopen, fgets, fclose, strtok, strstr, strcpy, strncpy, strcat, strcmp, strlen, sizeof ¡SQL : TO_DATE

내장 PL/SQL : 심화문제 l File emp. data의 내용 ¡ 9369, JANG, CLERK, 7902, 2008/12/17, 800, , 20 ¡ 9499, BAE, SALESMAN, 7698, 2008/02/20, 1600, 30 ¡ 9521, HWANG, SALESMAN, 7698, 2008/02/22, 1250, 500, 30 ¡ 9566, PARK, MANAGER, 7839, 2008/04/02, 2975, , 20 ¡ 9654, KIM, SALESMAN, 7698, 2008/09/28, 1250, 1400, 30 ¡ 9698, MOON, MANAGER, 7839, 2008/05/01, 2850, , 30 ¡ 9782, CHOI, MANAGER, 7839, 2008/06/09, 2450, , 10 ¡ 9788, HAN, ANALYST, 7566, 2008/04/19, 3000, , 20 ¡ 9844, LEE, SALESMAN, 7698, 2008/09/08, 1500, 0, 30 ¡ 9876, RYU, CLERK, 7788, 2008/05/23, 1100, , 20

내장 PL/SQL : 심화문제 l Table EMP의 구조 TABLE SCOTT. EMP ( EMPNO NUMBER(4, 0) NOT NULL, ENAME VARCHAR 2(10), JOB VARCHAR 2(9), MGR NUMBER(4, 0), HIREDATE, SAL NUMBER(7, 2), COMM NUMBER(7, 2), DEPTNO NUMBER(2, 0), CONSTRAINT PK_EMP PRIMARY KEY (EMPNO) USING INDEX, CONSTRAINT FK_DEPTNO FOREIGN KEY (DEPTNO) REFERENCES SCOTT. DEPT (DEPTNO) )