Database - 10장

db 프로그래밍

목적 : 대화식 인터페이스와 달리 응용 프로그램에서 db에 access하기 위해서

왜?
대화식 인터페이스는 편리하나 기능이 충분하지 않다.

 

db 프로그래밍 접근방법

-범용 프로그래밍 언어에 db 명령어를 내장시키는 방법
precompiler or preprocessor를 통해 embedded 프로그램을 처리

-db 함수 라이브러리 사용
host 언어에서 db호출을 위해 사용하는 API를 제공

-새로운 db언어 사용
DBPL, impedance mismatch를 최소화 할 수 있다.

 

impedance mismatch

 

-두 프로그래밍 환경 사이에서 (DB<->host) 데이터 타입이나 프로그래밍 사이의 불일치를 이야기한다

ex) type mismatch -> 두 언어간의 타입 표시에 차이가 존재 (바인딩 작업이 필요)
ex) 언어처리 방식 : db는 한번에 하나의 집합 처리 그러나 프로그래밍 언어는 한번에 한 record씩 처리함(iterator 도입하여 해결)

DB프로그래밍의 전형적인 상호작용 순서

-Client program이 db 서버에 연결을 요구
- '' 질의어를 보냄
-상호작용을 하다 더이상 db에 접근이 필요 없다면 연결을 종료

Embedded sql

 

-EXEC SQL ... END_EXEC (... <-에 sql문 위치시킴)
Embedded sql내에서 host의 변수를 사용할 수 있는데 이것을 shared variables이라 하며
이것이 sql내에서 쓰인다면 colon을 붙혀서 어디쪽 변수인지 구분해준다.

 

DECLARE SECTION에서 C변수를 선언, db는 이 변수들이 sql내에서 쓰일 수 있다는걸 알게된다 . db와 프로그램 사이에 에러/예외가 생기면 SQLCODE,SQLSTATE를 통해 알려준다.(지정된 변수)

CONNECT TO 서버이름 AS 접속명 권한 username , password active는 connection, connect를 끝내는것을 disconnect

DBMS와 program사이 소통

=0: DBMS가 성공적으로 쿼리 수행함
>0:(or = 100): 쿼리 결과에서 더이상 가능한 결과가 없다
<0: 에러

SQL STATE 
5자리 code로 되어있고
00000이면 에러나 예외 없음, 다른 변수는 에러나 예외를 알려준다.

오라클의 경우 SQLCA. 형식으로 구조체로 선언하고 , include를 통해 포함시킨다.

 

반복문 동안, 주민번호 입력받고 해당되는 직원의 정보를 출력 embedded sql문에서 검색하는 기능을 수행한다. into에서 C언어 변수로 select값이 들어오고, SQLCODE==0이면(성공) 결과값을 출력한다.

 

검색결과가 하나의 tuple이므로 impedance mismatch가 발생하지 않으나
검색결과가 여러개일 경우 처리가 불가능하다.

 

이럴땐 cursor(iterator)를 이용해 다중 튜플을 처리한다.
cursor : 다중 튜플을 처리하는데 필요한 일종의 검색결과를 임시 저장한 파일의 한 튜플을 가리키는 포인터
-Open cursor
db에서 검색결과를 가져와서 첫번째 row의 position을 point한다
-Fetch
커서를 다음 튜플로 이동시키는 것
-Close cursor
커서가 필요없으면 커서를 닫는 기능

 

부서이름을 입력받아서 그 부서에 근무하는 직원정보를 검색해서 출력하고
그 직원의 급여를 인상시켜 주는 프로그램

4번의 EMP라는 CUROSOR를 선언하는 모습
7번- 써주지 않으면 UPDATE된 내용이 없어져 버린다
8번- EMP를 실제로 실행시키는 실행문 

9번-fectch문을 통해서 cursor를 하나 다음으로 이동시켜서 데이터를 가져온다

 

open cursor문 실행, dbms는 해당되는 쿼리를 실행하고 query 결과를 임시적 file에 저장하고
cursor는 제일 첫째 record 바로 앞의 위치를 가리킨다.
fetch실행시 cursor가 다음칸으로 이동, 데이터를 가져온다

 

Dynamic SQL

 

embedded sql을 쓸경우 sql이 변경되면 전체 프로그램을 컴파일 해야한다.
그래서 runtime에 sql을 입력해서 바로 실행할 수 있게 해주는게 Dynamic sql이다.

이런 Dynamic sql은 검색되는 속성의 타입이나 숫자를 컴파일 타임에 미리 알 수 없기 때문에
처리가 복잡해지는 경우가 있다.

 

Prepare : 입력된 문자열을 compile 시키는 명령 , Execute : 실행명령 , Execute immediate : 한번에 prepare과 execute를 수행하는 명령

자바에서 embedded사용법

표준 : SQLJ sql statement를 자바로 변경해준다
-JDBC인터페이스를 통해 수행시켜준다

-java,sql 같은 클래스를 import해주어야 한다

 

java는 #sql내부에다가 코드를 쓴다. 앞서 본 c의 경우와 비슷하다, SQLException = SQLcode, SQLstate와 비슷한 역할을 한다.

SQLJ도 두 타입의 iterator을 제공한다.

-named iterator : 변수 이름을 지정해서 매칭시켜준다
-positional iterator : 나타난 순서대로 매칭시켜준다

-FETCH 연산은 앞서 한것과 동일하다

 

그림 named iterator을 이용해 출력하는 프로그램 e = cursor, Emp e = pointer, e.next = fetch

그림 positional iterator 이용 9 - 변수선언 없이 type만 나열함, 나머지는 동일한 과정을 갖는다.

함수호출을 이용한 db프로그래밍 : SQL/CLI , JDBC

-Embedded SQL은 한번 쿼리가 embedded되고 나면 변경이 힘들다
API : 동적인 프로그래밍이 가능해진다.
장점 : preprocessor가 필요하지 않다
단점 : SQL에 오류가 있으면 runtime에 발생한다.

 

10번 sql명령어 처럼 생겼으나 아니다

SQL call level interface

-SQL 표준의 일부
Microsoft가 개발했던 ODBC를 표준으로 개발했다
-동일 프로그램에서 다수의 db에 접근할수 있게 도와준다
-필요한 library를 include해서 사용한다
-SQL문을 동적으로 생성하고 전달을 할 수 있다.
(C의 4가지 유형의 record structure을 이용해 관리한다)

 

Components of SQL/CLI

Environment record : db connection과 system 환경정보 관리
connection record: 특정 connection을 위한 정보 관리
statement record: sql문장을 위한 정보 관리
description record : 튜플이나 변수에 대한 정보 관리(튜플 속성개수, 타입정보, 함수호출에 필요한 parameters수)

 

과정

-SQL/CLI 라이브러리 선언
-4개의 component를 위한 record에 대한 pointer = handle variables
(SQLHSTMT, SQLHDBC, SQLHENV , SQLHDESC)

-SQLAllocHandle을 이용해 해당되는 pointer변수를 세팅
3가지 parameters를 가진다.
handle1 : 해당되는 pointer에 data들이 저장되는 container
handle2 : 새로 생성되는 record에 대한 pointer를 가짐 

 

3개의 변수
stmt1 : sql문을 위해 필요한 정보
con1 :특정 connection을 위해 필요한 정보
env1 : db 접속환경에 관련된 정보를 가지고 있는 변수

SQLRETURN : SQL/CLI를 통해 넘어오는 return code ( error/exception 검출)

ret1부터 순차적으로 코드가 돌아가는 구조 
6- SQL_HANDLE_ENV : env1을 선언하기 위한 parameter
SQL_NULL_HANDLE : enviroment가 가장 큰 외곽 구조 이므로 자기를 포함하는 handle은 NULL
&env1 : 결과 전달받음

7-Env1이 생성되면 그 내부에 ret2가 선언되고 (DBC관련) 그 선언된 container record를 &con1이 가르킨다는 것을 얘기한다 
8-SQLConnect, 해당되는 connection안에서 수행되야 하므로 con1에서 수행되며 parameters들을 입력받는다
9-statement들을 수행

 

과정

-SQLPrepare을 이용해 SQL을 번역하고
-SQLExecute로 실행하고
-SQLBindCol로 C변수와 바인딩하고
-SQLFetch로 C변수에 가져온다.

 

10- SQL준비, sql문이 제공되고 'Ssn=?'에서 ?는 paramter인데 나중에 전달해준다(SQLBindParamter에서)
SQL_NTS = 쿼리 길이를 byte를 넣어줘야 하는데 자동으로 길이를 계산해준다.
11 - Ssn 입력받음
12 - ?와 Ssn을 매칭시켜준다 SQLBindParamter( stmt1 , 1=? 를 의미 , 타입, 입력값, 길이, 실제길이 계산)
13 - stmt1 실행
15,16 - SQLBindCol은 select문의 값을 받아온다.  
17 - 검색결과를 가져오는 SQLFetch.
18 -  (ret2에 error가 없으면 출력)

다중튜플을 검색하는 iterator가 필요한 경우

JAVA

 

JDBC 

-자바 프로그래밍을 위한 SQL connection 함수 호출
-JDBC함수를 이용하면 JDBC드라이버를 지원하는 어느 DBMS든 접근 가능
-JDBC는 프로그램이 동시에 여러 DB에 access 가능하게함

 

4 - jdbc 드라이버 호출
5 - 예외처리, 각 jdbc함수호출에 체크 가능
12 - connection 작업
13  - 로그인

14-? = paramter, sql문을 string에 표현 
15 - 해당 쿼리 준비 
16 - 입력받음 
17 - parameter 초기화 
18 - 첫 paramter을 ssn에 매칭(타입에 따라 setInterger()등 사용법이 다름) 
19 - Resultset은 cursor,iterator와 비슷한 역활

스탭1

스탭2, Resultset : 검색 결과를 담는 2차원 table형태

multiple tuple 검색 질의 15,16 - 정수 dno을 tostring으로 sql쿼리와 한번에 결합시켰다 . 17- createstatement 는 prepare와 같은 역할

prepare와 createstatement의 차이, createstatement의 경우 쿼리를 한번만 수행할경우 편리하다

database stored procedures ( 함수같은 개념인듯...?)

db 서버에 저장되는 유저가 지우지 않는이상 보존되는 procedures/functions 

장점: 많은 어플리케이션에서 사용한다면 개발할 필요없이 사용가능
서버에서 바로 실행되기 때문에 cc 줄일 수 있음
view같은 것을 모델링할때 융통성을 제공해준다

 

No_of_emps가 COUNT(*)를 넘겨 받은뒤 IF문을 수행한다

stored procedure 이용 방법

외부 프로그램을 이용해 작성하여 링크, 디비 내부에서 작성

-stored function
return절을 사용한다
-calling a procedure or fucntion
call문을 통해 호출할 수 있다.

 

SQL PSM

SQL/PSM저장 프로시저 모듈을 작성하기위한 sql 표준

SQL+ 저장 프로시저/함수 + 추가적 프로그래밍 구조를 통해 작성을 한다.

이런 함수는 SQL의 WHERE 문에 바로 적용할 수 있는 편의성이 있다