데이터베이스 HW05-2

학번 : 20021558	성명 : 경규성

2. 교과서 문제 6.2, 6.5

문제 6.2 다음 물음에 간단히 답하시오.

1. 다음 항을 설명하시오: 커서, 내장형 SQL, JDBC, SQLJ, 저장 프로시저

- 커서 : 질의 결과가 인스턴스(테이블)단위로 나오는데 이것을 행단위로 가르켜 fetch할 수 있도록 해준다. host언어에서 이 튜플의 정보를 하나씩 변수로 가져가 쓰면된다.

- 내장형 SQL : host언어안의 sql 문을 말한다. (host언어에 내장된)

- JDBC : 자바프로그램에서 sql문을 실행하기위해 DB에 연결해주는 응용프로그램이다.

- SQLJ : 연결만 된다고 java로 sql을 편하게 이용할 수 없다. JDBC API를 이용하면 static sql 작성에 효율이 떨어져, SQLJ가 필요하다. C언어로 sql 작성시 PRO*C와 같은 역할을 하는 것이 SQLJ다. java를 이용한 내장형 sql의 전처리과정에서 이미 DB에 접속하여 오류나 형식을 검사해 준다.

- 저장프로시저 : DB서버에 sql의 질의를 함수처럼 만들어 저장해 두었다가 호출만으로 일련의 프로시저를 간편하게 이용할 수 있다.

2. JDBC와 SQLJ 사이의 차이점은 무엇인가? 왜 그들은 둘 다 존재하는가?

SQLJ는 내장형 sql을 지원한다. host언어의 변수를 사용할 수 있고, 전처리와 컴파일 단계에서 내장 sql의 문법과 형을 검사해준다. static sql 에 이용하는 것이 좋다.

JDBC는 JAVA코드를 API를 이용하여 DB와 따로 작성, 코드수가 많고, host 언어의 변수를 사용할 수 없어 어렵지만 dynamic 쿼리를 이용하여 실행시간 오류를 검사할 수 있다. dynamic sql 작성 시 JDBC를 이용한다.

3. 저장 프로시저 항을 설명하고, 왜 저장 프로시저가 사용되는지 예를 보이시오.

저장 프로시저는 복잡한 sql문을 capsule화 하여 서버에 저장해두고 클라이언트에서 간단하게 호출하여 사용한다. 매번 sql문을 작성하느라 서버와 클라이언트간의 장기간 채널을 차지하고 있지 않아도 되므로, 트래픽이 감소한다.

매번 작성, 검사, 최적화, 컴파일 등의 과정없이 저장 프로시저를 사용하면 저장 시 짜여진 실행계획과 모든 과정을 마친 실행파일에 호출과 전해받은 인자만으로 간단히 수행된다.

문제 6.5 다음 물음에 답하시오.

1. 왜 내장형 SQL과 SQLJ를 번역하기 위하여 프리컴파일러가 필요한가? 왜 JDBC에 대해서는 프리컴파일러가 필요하지 않은가?

SQLJ는 host안의 작성된 sql과 함께 precompile, bind 과정이 필요하다. bind 과정 중에 DB object에 대한 type, syntax, previlege checking이 이루어지므로, SQL 구문에 정의된 DB Object는 어플리케이션 bind시에 반드시 DB내에 존재하여야 한다. Package를 미리 생성하며, 이는 DB에 의해서 저장되고 관리된다. 어플리케이션 bind후 DB내에 Package가 생성되며, 어플리케이션 실행시, 앞서 bind 과정에 서 생성된 Package의 접근 경로 (access plan 또는 access path)을 이용하여 해당 SQL의 최적화된 수행이 이루어진다. Dynamic Embedded SQL 어플리케이션이 매번 실행시마다 Package를 작성해서 SQL구문을 처 리하는 것에 비해 미리 bind 과정에서 작성된 Package를 바로 사용함으로써, Package 작성 에 필요한 overhead를 덜수 있다. Bind 과정은 최초 한번만 필요하나, 상황에 따라 정기적으로 rebind를 수행할 수 있다. 반복적이고 정형화된 업무 (예약, 잔액 조회등)와 같은 사전에 정의된 (pre-defined) 트랜 잭션 형태의 어플리케이션 구현에 적합하다.

반면 JDBC로 작성되는 sql은 Program내에 Embed 된 SQL 구문이 어플리케이션 개발시가 아닌, 실행시에 정의되어 (완성되어) 처리되는 SQL 구문 형식이므로 Precomplie Bind 과정이 필요없다. Static Embedded SQL의 경우처럼 미리 bind 과정을 거쳐 미리 Package를 생성하는 것이 아니라, 어플리케이션 실행시에 SQL 구문이 완성되어 DB내에 Package를 만들어, 이 Package내의 access plan을 이용한다. 어플리케이션 실행 시점의 (current) DB의 통계 정보를 기초로한 Package가 작성되므로, 매번 실행시마다 최적화된 access plan이 제공된다. Static SQL의 경우, bind 과정에서 미리 생성된 Package는 bind time시의 DB 통계정보를 근거로 작성되므로 실제 어플리케이션 실행 시점의 통계정보와 차이가 날 수 있다. 따라서, 이러한 상황을 방지하기 위해 주기적으로 DB통계정보 갱신을 위한 RUNSTAT 작업 및 어플리케이션 Rebind를 수행한다.

2. SQLJ와 내장형 SQL

SQL 구문을 사용해서 DB Host프로그램을 작성할 때, Host언어 내에 SQL 구문이 정의 되는 방식의 SQL API를 Embedded SQL이라 하며, 정의 형식에 따라 Static Embedded SQL과 Dynamic Embedded SQL로 구분이 된다.

SQLJ는 java를 이용한 내장형 sql 작성시 전처리 단계와 컴파일단계에 미리 DB에 접속하여 문법이나 객체의 오류를 검사해준다. 컴파일시 에러를 검출하여 실행시 에러를 줄임으로써 개발하는데 디버깅이 편하다.

또한 SQLJ는 host언어의 변수를 이용할 수 있어 문장이 간결해지나 JDBC API를 사용할 수 없다. 또 컴파일 시에 sql문과 host언어가 완전히 bind되어야 하기 때문에 dynamic sql을 사용할 수 없다.

3. 다음의 질의에 대하여 Oracle DBMS의 내장형SQL (Embedded SQL)을 사용하여 C 프로그램으로 작성하시오.

Suppliers(sid: integer, sname: string, address: string)

Parts(pid: integer, pname: string, color: string)

Catalog(sid: integer, pid: integer, cost: real)

1) 사용자에게 공급자의 id를 묻고 공급자의 id, 이름, 그 공급자가 공급하는 부품의 id, 부품명을 출력하시오.

2) 사용자에게 부품의 최소 가격을 묻고 최소 가격 이상 되는 모든 부품을 찾은 후, 부품의 id, 부품명, 색깔을 출력하시오.

3) 사용자에게 부품의 가격을 묻고 가장 가까운 가격의 부품을 공급하는 공급자의 id, 이름, 부품의id, 부품명, 가격을 출력하시오.

pro*c 전처리파일 소스코드

#include <stdio.h>

#include <stdlib.h>

EXEC SQL INCLUDE sqlca;

EXEC SQL BEGIN DECLARE SECTION;

int input;

int usid;

char usname[20];

int upid;

char upname[20];

char ucolor[15];

float ucost;

VARCHAR username[20]; // struct { short len; char arr[20]} username;

VARCHAR password[20];

EXEC SQL END DECLARE SECTION;

/* 문제 1번 함수 */

void prob1(){

/* 1) 사용자에게

공급자의 id를 묻고 공급자의 id, 이름,

그 공급자가 공급하는 부품의 id, 부품명을 출력하시오.*/

printf("\n문제1 질문 : 공급자 sid 를 입력하세요: ");	// 사용자에게 공급자의 id 질문

scanf("%d", &input);	// sid를 받아서

/* 입력 값과 같은 sid 를 가진 사람의 정보 select */

EXEC SQL DECLARE c1 CURSOR FOR

SELECT S.sid, S.sname, P.pid, P.pname

FROM Parts P, Catalog C, Suppliers S

WHERE S.sid=C.sid AND C.pid=P.pid AND S.sid = :input;

EXEC SQL OPEN c1;

printf("============================================\n");

printf("\t\t입력 = %d \n", input);

printf("============================================\n");

printf("sid  sname	pid  pname\n");

printf("--------------------------------------------\n");

while(1){

EXEC SQL WHENEVER NOT FOUND DO break;

EXEC SQL FETCH c1 INTO :usid, :usname, :upid, :upname;

printf("%-5d%5s%-5d%5s\n", usid, usname, upid, upname);

}

EXEC SQL CLOSE c1;

}

/* 문제 2번 함수 */

void prob2(){

/* 2) 사용자에게 부품의 최소 가격을 묻고

최소 가격 이상 되는 모든 부품을 찾은 후,

부품의 id, 부품명, 색깔을 출력하시오.  */

printf("\n문제2 질문 : 부품의 최소 가격을 입력하세요: ");

scanf("%d", &input);	// cost를 받아서

/* catalog에서 입력 값 보다 같거나 큰 cost의 정보 select */

EXEC SQL DECLARE c2 CURSOR FOR

SELECT DISTINCT P.pid, P.pname, P.color

FROM Parts P, Catalog C

WHERE C.pid=P.pid AND C.cost >= :input;

EXEC SQL OPEN c2;

printf("============================================\n");

printf("\t\t입력 = %d \n", input);

printf("============================================\n");

printf("pid  pname	color\n");

printf("--------------------------------------------\n");

while(1){

EXEC SQL WHENEVER NOT FOUND DO break;

EXEC SQL FETCH c2 INTO :upid, :upname, :ucolor;

printf("%-5d%5s%5s\n", upid, upname, ucolor);

}

EXEC SQL CLOSE c2;

}

/* 문제 3번 함수 */

void prob3(){

/* 3) 사용자에게 부품의 가격을 묻고

가장 가까운 가격의 부품을 공급하는 공급자의 id,

이름, 부품의id, 부품명, 가격을 출력하시오.*/

printf("\n문제3 질문 : 부품의 가격을 입력하세요: ");

scanf("%d", &input);	/* cost 입력 */

/* catalog의 모든 cost에서 입력 값을 뺀 절대값을 정렬하여

첫번 째 row의 cost 값 과 같은 물건을 파는 정보 select */

EXEC SQL DECLARE c3 CURSOR FOR

SELECT S.sid, S.sname, P.pid, P.pname, C.cost

FROM suppliers S, parts P, catalog C

WHERE S.sid=C.sid AND P.pid=C.pid AND cost = (

SELECT cost

FROM (

SELECT C.cost

FROM suppliers S, parts P, catalog C

WHERE S.sid = S.sid AND P.pid = C.pid

AND C.pid = P.pid

ORDER BY abs(C.cost - :input) asc

)

WHERE rownum=1

);

EXEC SQL OPEN c3;

printf("=========================================================\n");

printf("\t\t입력 = %d \n", input);

printf("=========================================================\n");

printf("sid  sname	pid  pname	cost\n");

printf("---------------------------------------------------------\n");

while(1){

EXEC SQL WHENEVER NOT FOUND DO break;

EXEC SQL FETCH c3 INTO :usid, :usname, :upid, :upname, :ucost;

printf("%-5d%5s%-5d%5s%-5.2f\n", usid, usname, upid, upname, ucost);

}

EXEC SQL CLOSE c3;

}

/* 에러 함수 */

void sqlerror()

{

EXEC SQL WHENEVER SQLERROR CONTINUE;

printf("SQL에러 : \n");

printf("\n% .70s \n", sqlca.sqlerrm.sqlerrmc); /* 에러정보 출력 */

EXEC SQL ROLLBACK RELEASE; /* 잘못된 과정 롤백 */

}

/*메인 함수*/

main() {

int command;

/* 접속 */

strcpy(username.arr, "stu02"); /* 아이디 */

username.len = strlen(username.arr);

strcpy(password.arr, "rudrbtjd"); /* 비밀번호 */

password.len = strlen(password.arr);

EXEC SQL WHENEVER SQLERROR DO sqlerror(); /* 에러나면 에러함수 호출 */

EXEC SQL CONNECT :username IDENTIFIED BY :password USING :"orcl";

/* 아이디, 비밀번호로 'orcl'계정으로 접속 */

printf("\n%s의 오라클 계정으로 접속.. \n", username.arr);

printf("\n### 과제5-2 : 문제번호 입력하세요(1~3) ###\n\n");

printf("# 1 : 문제 1 사용자에게 공급자의 id를 묻고 공급자의 id, 이름, 그 공급자가 공급하는 부품의 id, 부품명을 출력하시오.\n\n");

printf("# 2 : 문제 2 사용자에게 부품의 최소 가격을 묻고 최소 가격 이상 되는 모든 부품을 찾은 후, 부품의 id, 부품명, 색깔을 출력하시오.\n\n");

printf("# 3 : 문제 3 사용자에게 부품의 가격을 묻고 가장 가까운 가격의 부품을 공급하는 공급자의 id, 이름, 부품의id, 부품명, 가격을 출력하시오.\n\n");

while(1){

printf("메뉴입력 : ");

scanf("%d", &command);

switch(command){

case 1 : prob1(); break;

case 2 : prob2(); break;

case 3 : prob3(); break;

case 0 : return;

}

printf("\n( 1 : 문제1,\t");

printf("2 : 문제2,\t");

printf("3 : 문제3,\t");

printf("0 : 종료)\n");

}

}

문제 1 결과화면

문제 2 결과화면

문제 3 결과화면