Ch09. SQL 프로그래밍 기법 소개

1. 데이터베이스 프로그래밍

대화식 인터페이스와 달리 응용 프로그램에서 데이터베이스를 접근하기 위한 방식

 

사용하는 이유?

- 대화식 인터페이스는 편리하지만, 기능이 충분하지 않음

- 그래서 대부분의 데이터베이스 연산은 응용프로그램을 통해 일어남(ex 웹 어플리케이션)

 

데이터베이스 프로그래밍 접근방법 세가지

1. 범용 프로그래밍 언어에 데이터베이스 언어를 임베디드하는 방법(프리컴파일러 or 프리프로세서)
2. 데이터베이스 함수의 라이브러리 사용(호스트언어에서 데이터베이스를 호출하기 위함. API 제공하는 것)
3. 새로운 데이터베이스 언어를 사용하는 것(DBPL) - 이 경우 임피던스 불일치(impedance mismatch)가 최소화될 수 있음

 

임피던스 불일치(impedance mismatch)란?

두 프로그래밍(데이터베이스, 호스트 언어) 환경 사이에서 데이터 타입 같은 데이터의 불일치를 말함

• type mismatch and incompatibilities -> binding을 해야함
• set vs record-at-a-time processing -> DB는 한 번에 하나의 집합을 처리하는데, 범용 프로그래밍은 한 번에 한 레코드만 처리함 -> 중간에 차이를 최소화하기 위해 iterator를 사용함

 

2. 임베디드 접근 방식

데이터베이스 프로그래밍 환경에서 접속을 해야함

• 클라이언트 프로그램은 데이터베이스 서버에 연결을 개방해야한다.
• 클라이언트 프로그램은 데이터베이스에 질의어를 보내고 받으면서 상호작용함
• 더 이상 데이터베이스 접근이 필요가 없을 때, 클라이언트 프로그램은 접속을 종료함

SQL문은 호스트 언어에서 사용할 때 EXEC SQL .... END-EXEC를 사용함

이때 ... 내부에서는 호스트 언어의 변수도 사용할 수 있어서 이런 변수를 shared variable이라고 함

shared variable은 SQL변수인지 호스트 언어의 변수인지 몰라서 콜론을 앞에 붙임

 

C언어에서 EXEC SQL BEGIN DECLARE SECTION; .... EXEC SQL END DECLARE SECTION;을 입력하고, ...안에 변수를 입력하면 ....에 나온 변수들이 SQL에서도 사용할 수 있다는 것을 알 수 있음

 

SQLCODE와 SQLSTATE는 예외와 오류 조건들을 프로그램에 알려주기 위해 사용하는 변수임

 

데이터베이스에 연결하기

한 프로그램이 여러개를 연결할 수 있지만, 하나의 데이터베이스만 활성화할 수 있음

데이터베이스 연결 설정

CONNECT TO server-name AS connection-name 

AUTHORIZATION user-account-name-and-password

 

현재 활성화된 연결을 다른 것으로 변경할 때

SET CONNECTION connection-name

 

연결이 필요없을 때

DISCONNECT connection-name

 

변수 SQLCODE와 SQLSTATE

SQLCODE

• = 0 : 쿼리가 성공적으로 수행되었음
• > 0(or = 100) : 질의 결과 내에 더 이상 이용 가능한 데이터(레코드)가 없음을 나타냄
• < 0: 오류

SQLSTATE

• ='00000' : 어떤 오류나 예외가 발생하지 않았음
• =다른 값들 : 오류나 예외를 표시함

ORACLE

• SQLCA : 구조체를 포함
• SQLCA.SQLCODE : EXEC SQL include SQLCA로 사용함

 

3. 커서(cursor(iterator))를 이용한 질의 결과 처리

C프로그램은 한 번에 하나씩 처리해야되서 커서를 사용해 질의 결과를 한 번에 한 튜플씩 처리할 수 있게함

 

OPEN CURSOR를 사용하여 커서를 생성. 데이터베이스 질의어의 결과의 첫 행(튜플)을 포인터로 가르키고 있음

FETCH는 다음 튜플로 이동함

CLOSE CURSOR는 커서를 닫음

 

 

4. 동적 SQL을 사용하여 실행 시점에 질의 명시

임베디드 SQL은 다른 질의를 실행하려고 할 때, 프로그램 코드를 수정해야함. 그래서 런타임에서 바로 SQL을 실행하게 만드는 방법임

하지만 이 방법은 SQL 질의에 의해 검색될 애트리뷰트들의 타입과 개수를 프로그램 작성때 알 수 없어서 만들기 복잡함

 

 

5. 자바에서 임베디드 SQL문 사용하기

SQLJ : 자바에서 임베디드 SQL문을 위한 표준임

SQLJ는 SQL 상태를 JAVA로 변환해주고, 이때 JDBC 인터페이스를 통해 수행이 됨

SQLJ를 자바에서 쓰기 위해서는 java.sql을 사용하면 댐

 

자바에서 SQLException은 SQLCODE나 SQLSTATE와 비슷한 역할을 함

ssn = readEntry("Enter a SSN: ");
try{
	#sql{select FNAME, LNAME, ADDRESS, SALARY
    	into :fname, :lname, :address, :salary
        from EMPLOYEE where SSN = :ssn };
}
catch (SQLException se){
	System.out.println("SSN does not exist: ", +ssn);
    return;
}
System.out.println(fname+" "+lname+...);

 

 

SQLJ에서도 여러 튜플을 처리하기 위해 iterator를 사용하는데, 두 가지 타입을 지원함

• named iterator : 변수 이름을 지정해서 매칭해주는 것
• positional iterator : 나타낸 순서대로 매칭해주게 됨

FETCH는 위에 언급한 임베디드 SQL과 동일한 역할을 함

 

 

 

6. 함수 호출을 이용한 DB 프로그래밍

SQL표준을 위한 SQL/CLI 방식과 JAVA를 위한 JDBC가 있음

임베디드 SQL는 한 번 쿼리가 임베디드되면 변경이 불가능함. API방식은 동적으로 가능해짐

 

SQL Call Level Interface

• SQL 표준의 일부 - ODBC를 표준으로 개발함
• 동일한 데이터베이스에서 다수의 인터페이스를 접근할 수 있게 해줌
• 필요한 라이브러리를 include해서 사용해야함
• 이러면 이제 프로그램내에서 동적으로 생성할 수 있음
  • 이 것은 4가지 종류의 레코드에 유지되고 있음

1. 환경 레코드(Environment record)는 하나 이상의 데이터베이스 연결을 추적하고 환경 정보를 유지하는데 사용
2. 연결 레코드(Connection record)는 특정 데이터베이스 연결을 위해 필요한 정보를 기억
3. 문장 레코드(Statement record)는 한 SQL 문장을 위해 필요한 정보를 유지
4. 설명 레코드(Description record)는 튜플이나 매개변수들에 대한 정보를 유지

 

7. C에서 SQL/CLI 프로그래밍 단계

1. SQL/CLI 라이브러리를 불러옴
2. 핸들 변수들(위에 나온 레코드)을 컴포넌트 위에 선언함 (called: SQLHSTMT, SQLHDBC, SQLHENV, SQLHDESC)
3. SQLAllocHangle을 사용하여 환경 레코드와 문장 레코드를 설정함
   SQLAllocHandle(<handle_type>, <handle_1>, <handle_2>)
   
   • <handle_1>은 새로 생성된 핸들이 포함될 컨테이너를 가르킴
   • <handle_2>는 핸들 타입에 있는 타입으로 새로 생성된 레코드를 가리키는 포인터(핸들)임
4. SQL/CLI 함수 SQLPrepare를 사용해 준비함
5. 프로그램 변수를 파라미터로 바운드함
6. SQLExecute를 사용하여 SQL 문장을 실행함
7. 실행결과를 c변수에 SQLBindCol을 사용하여 바인딩해줌
8. 검색 결과를 SQLFetch를 사용하여 가져옴

 

8. 자바에서 데이터베이스 연결

JDBC는 자바 프로그래밍에서의 SQL 연결 함수로 부름

JDBC를 지원하는 어느 DBMS에서도 다 접근이 가능함

동시에 여러개 데이터베이스를 접근할 수 있으며 각각의 DB를 데이터 소스(data source)라고 부름

 

 

JDBC 데이터베이스 접근 순서

1. JDBC 라이브러리 가져오기 (java.sql.*)
2. JDBC 드라이버 로드
3. 적절한 변수를 생성
4. 연결 객체를 생성
5. 문장 클래스로부터 문장 객체를 생성
6. 문장 매개 변수 설정
7. 문장 매게 변수들을 바인딩
8. SQL문장을 실행
9. 쿼리 결과를 처리

 

 

9. 데이터베이스 저장 프로시저

저장 프로시저란 데이터베이스 서버의 DBMS에 저장된 프로그램 모듈을 말함

SQL/PSM은 SQL에 범용 프로그래밍 언어 구조를 포함하도록 확장된 SQL의 확장이며, 저장 프로시저를 작성하는데 사용함

 

저장 프로시저 장점

• 많은 응용에서 필요로 할 경우, 중복될 필요 없이 공통으로 사용할 수 있음
• 서버에서 바로 실행이 되기 떄문에 통신 비용을 줄일 수 있음
• 뷰 모델링 능력을 향상시킬 수 있음

저장 프로시저를 사용하는 방법

• DBPL을 이용하거나, External Language를 사용하거나
• Stored function을 사용해서 RETURNS를 사용하거나
• CALL문을 사용할 수도 있음

 

SQL/PSM은 지속적 저장 모듈을 작성할 수 있도록 허용하는 SQL 표준의 한 부분이다.

SQL + 저장 프로시저 / 함수 + 추가적 프로그래밍 구조로 작성함