8. SQL 프로그래밍 기법

1. 개요

1) 개요

응용 프로그램에서 SQL 데이터베이스를 접근하는 기법들에 대해 정리한다. 

이전 절에서 정리한 SQL 문법은 데이터 베이스 연산들을 위한 구조에 대해 정리한 것이다. 

 

ORACLE REMBS와 같은 대부분의 DB 시스템은 "대화식 인터페이스"라고 SQL 명령문을 모니터 상에서 입력하고 DB 시스템에서 실행할 수 있다. 

예를 들어 ORACLE REMBS를 컴퓨터에 설치하고 SQLPLUS 명령을 실행하면 ORACLE이 제공하는 대화식 인터페이스가 시작되며 이곳에 SQL 명령어나 질의들을 여러줄 입력하고 끝에 세미콜론과 엔터키를 입력하여 DB 시스템에서 명령어들을 실행할 수 있다. 

위와 같은 대화식 인터페이스를 통해서 가끔 실행되는 임의의 질의를 수행하는데는 편리하지만 주로

실제로 DB의 동작은 응용 프로그램과 같은 프로그램을 통해 실행한다. 

아래 절에서는 응용 프로그램에서 DB를 연동하는 방법에 대해 소개한다. 

 

2-1) 방법1: 범용 프로그래밍 언어에 포함된 DB 명령문

DB 문장을 호스트 프로그래밍 언어 내에 내포시키는 방법이다. 

응용 프로그램 내에서 DB문장을 식별하기 위해 접두어로 EXECSQL을 붙힌다. 

그럼 DBMS에 의해 이 접두어를 스캔하고 DB를 실행하게 되는 것이다. 

 

DB문장들은 DMS가 제공하는 내부 함수에 대한 호출문으로 교체된다. => 내포된 SQL이라고 한다. 

 

2-2) 방법2: 범용 프로그래밍 언어에 포함된 DB 명령문

응용 프로그램에서 DB에 접근하기 위한 API 즉 인터페이스를 제공하는 방법이다. 

Java에서는 JDBC 클래스 라이브러리가 있다. 

 

 

2. DB 모델과 프로그래밍 언어의 차이점 : 임피던스 불일치

관계 DB의 구조 

테이블, 열, 행, 데이터 타입의 구조를 가진다. 

 

● 임피던스 불일치1

프로그래밍 언어의 데이터 타입과 관계 DB 모델에서 사용가능 한 애트리뷰트 데이터 타입이 서로 일치하지 않는다. 

=> 각 애트리뷰트 타입을 해당 프로그래밍 언어에서 호환 가능한 타입으로 바인딩해줘야 한다. 

 

● 임퍼던스 불일치2

하나의 테이블 형태의 자료구조를 프로그래밍 언어에서 적절한 자료구조로 바인딩하는 것이 필요하다.

질의 결과 내의 투플들에 대해 반복적인 처리를 위해 커서 또느 반복 변수가 사용된다. 

 

 

3. 내포된 SQL

이 절에서는 SQL 문장을 c언어 Java 내에 냎시키는 기법을 설명한다.

c언어 Java와 같은 SQL 문장을 내포시킬 수 있는 언어를 호스트 언어라고하는데 

c언어는 내포된 SQL로 사용되며 SQLJ라고 알려진 Java를 이용한 방법이 있다. 

 

1) 데이터 베이스 연결하기

응용 프로그램이 데이터 베이스 연결을 설정하기 위해서는 SQL 문장은 아래와 같은 형식을 가진다. 

CONNECT TO <서버 이름> AS <연결 이름.
AUTHORIZATION <사용자 계정과 암호> ;

한 사용자 프로그램에서 여러개의 데이터베이스 서버를 접근할 수 있기 때문에 연결이 여러개가 될 수 있다. 하지만 특정 시점에서 한 개의 연결만 활성화 된다. 

아래 명령어를 통해 현재 활성화된 연결을 다른 DB 시스템으로 변경할 수 있다. 

SET CONNECTION <연결 이름> ;

아래 코드는 연결이 종료한다. 

DISCONNECT <연결 이름> ;

 

2-1) c언어 예시1 : 공유 변수, 통신 변수 선언 

● 공유 변수

여기서 부터 예시 코드는 DB 연결이 설정되어 있고 그 연결이 활성화 되어 있다고 가정한 뒤의 작성하는 코드이다.

내포된 SQL 문장들은 C프로그램에서 사용할 시 EXEC SQL 키워드로 시작하여 호스트 언어(c언어)와 구별한다. 

이 문장은 프리컴파일러가 내포된 SQL 문장들을 호스트 언어로부터 쉽게 분리하게 된다. 

SQL 문장은 END-EXEC 또는 세미콜론으로 끝나게 된다. 

int a;
EXEC SQL BEGIN DECLARE SECTION;
char name[10];
float salary;
int number
varchar address[30];
int SQLCODE; CHAR SQLSTART[6];
EXEC SQL END DECLARE SECTION;

내포된 SQL 문장 내에서 특별하게 선언된 C 프로그램 변수를 참조 할 수 있다. 

이런 변수들은 C 프로그램과 SQL문장 양쪽에서 사용할 수 있게 해준다. 이를 공유 변수라고 한다. 

공유 변순는 위 예시와 같이 SQL 안에서 C언어의 데이터 타입으로 선언이 가능하다. 공유 변수는 

C 프로그램내에서는 그냥 변수 명으로 접근하며 된고 SQL 문장 내에서는 변수 앞에 콜론을 붙여야한다. 

따라서 변수 명이 애트리뷰트 이름과 같아도 콜론이 공유변수임을 식별해주기 떄문에 가능함.

그리고 공유 변수는 SQL내부에서 선언하는데 위와 같이

EXEC SQL BEGIN DECLARE SECTION;

EXEC SQL END DECLARE SECTION;

내에서 선언된다. 

위 코드는 공유 변수를 선언한 예시이다.

 

SQL에서 타입 중 INTEGER, SMALLINT, REAL, DOUBLE은 C언어에서는 long, short, float, double로 대응된다. 

위 예시코드에서 이상한 타입인 varchar 타입이 있는데 SQL에서 고정 길이 및 가변 길이 문자열은 CHAR[I], VARCHAR[I]와 같은데 이는 C언어에서 char[i+1] 과 varchar[i+1]로 대응된다. (+1은 \0 문자) 

varchar는 표준 c타입이 아니지만 c가 sql 프로그래밍을 위해 사용딜 때 이 타입을 사용할 수 있다. 

 

 

● 통신 변수

위 예시 코드를 보면 SQLCODE와 SQLSTATE 변수가 있는데 이 변수는 DBMS의 예와, 오류 조건들을 프로그램에게 알려주기 위한 두 개의 특별한 통신 변수이다. 

SQLCODE는 SQL 회사들이나 제품에 대해 표준화 되어 있지 않다. 

SQLCODE의 값이 0이면 SQL 문장이 DBMS에 의해 성공적으로 수행되었음을 나타낸다. 

0보다 크면 질의 결과 내에 더 이상 이용 가능한 데이터가 없음을 나타낸다. 

0보다 작으면 오류가 발생한 것이다. 

SQLCODE는 SQLCA라고 불리는 레코드 구조체 내의 한 필드이다. 따라서 SQLCA.SQLCODE라는 형식으로 참조를 해야하며 SCALCA에 대한 정의를 C프로그램 내에 포함 시켜야한다. 그 코드는 아래 코드와 같다. 

EXEC SQL include SQLCA;

 

반면 SQLSTATE는 표준화 되어 있다. '0000'의 값은 정상 값이며 이 값이 아닌 값은 오류나 예외를 표시한다. 

SQLSTATE를 사용하는 것을 권장함.

 

 

2-2) c언어 예시2 : 질의 결과가 단일 레코드인 경우 

loop = 1;
while(loop) {
	prompt("input of Social Scurity Number>>", ssn); //ssn 공유 변수에 값을 입력 받는다. 
    EXEC SQL
    SELECT Fname, Lname INTO :fname, :lname
    FROM EMPLOYEE 
    WHERE Ssn = :ssn; //SQL 끝
    
    if(SQLCODE == 0 ) printf("fname, lname)
}

위 예시는 사용자로부터 값을 하나 입력 받고 그 값을 공유 변수 ssn에 저장한다. 그리고 SQL 문으로 :ssn의 값과 Ssn 애트리트뷰트 값이 서로 같은 투플 들에서 Fname과 Lname의 값들을 가져오는데 

INTO절을 설명하면 DB 레코드로부터 검색된 애트리뷰트 값들을 유지할 프로그램 변수를 지정하는 것이다. 

INTO 절은 SELECT 질의 결과가 한 개의 레코드인 경우에만 사용할 수 있으며 (INTO 절의 변수에 그 레코드 값을 저장)

여러 레코드들이 검색된 경우에는 에러가 발생한다. 여러 레코드를 취급하는 방법은 예시3에서 다룬다. 

 

여기까지 핵심 내용=>

위 코드는 SQL 질의 결과가 단일 레코드인 경우 INTO 절의 공유 변수를 선언하여 그 변수의 값에 결과를 저장할 수 있다. C 프로그램에서는 공유변수 이므로 접근하여 값을 읽게 된다. 

 

2-3) c언어 예시3 : 커서를 사용하여 질의 결과 처리

prompt("input Department Name>>", dname); //dname은 공유 변수
EXEC SQL
SELECT Dnumber INTO :dnumber
FROM DEPARTMENT 
WHERE Dname = :dname; //첫 번째 sql문 끝. dname에 사용자 입력 값 저장

EXEC SQL DECLARE C1 CURSOR FOR
SELET Fname, Lname
FROM EMPOYEE
WHERE Dno = :dnumber; //두 번째 sql 문 끝

EXEC SQL OPEN C1; //세 번째 sql문
EXEC SQL FETCH FROM C1 INTO :fname, :lname;//네 번째 sql문

while(SQLCODE ==) { 
  printf("name is: %s, %s\n", fname, lname);
  
  EXEC SQL FETCH FROM C1 INTO :fname, lname; // 다섯 번째 sql문
}

질의 결과가 여러개인 경우 C 프로그램에서 검색된 투플들을 쫒아가며 한 번에 하나씩 처리해야한다. 

이때 C 프로그램에서는 커서의 개념을 사용한다. 

 

EXEC SQL DECLARE C1 CURSOR FOR

에서부터 EXEC SQL OPEN C1; 문 사이에 지정된 질의의 커설를 만드는 것임 

OPEN을 해야 커서가 동작되는 것이고 FETCH를 통해 iterator를 구현하게 된다. 

while문안에서 fetch를 호출하여 iterator가 구현되며 더 이상 레코드가 없는 경우 SQLCODE는 양수로 지정된다. 

 

 

2-4) c언어 예시4 : 동적 SQL을 사용하여 실행 시점에 질의 명시

 

 

3-1) Java언어 예시1 : 내포된 SQL 명령어

SQLJ란 자바에서 SQL을 내포하기 위한 방법으로써 여러 업체에서 채택한 표준이라할 수 있다. 

JDBC란 자바에서 클래스 라이브러리와 함수 호출을 통해 SQL 데이터베이스를 접근하는데 사용되는 방법으로

SQLJ는 JDBC 이후에 개발된 방법이다. 

 

SQLJ 번역기는 SQL문장을 JAVA로 변환되고 이는 JDBC 인터페이스를 통해 실행될 수 있다. 

따라서 SQLJ를 사용하기 위해서는 JDBC 드라이버르 설치해야한다. 

import java.sql.*;
import java.io.*;
import sqlj.runtime.*;
import sqlj.runteim.ref.*;
import oracle.sqlj.runtime.*;


DefaultContext obj = oracle.getConnection("<url>", "<user nmae>", "<password>", true);
DefaultContext.setDefaultContext(obj);

위 명령어는 자바에서 우선 url 이름의 위치한 ORACLE 데이터베이스에 사용자 이름, password를 가지고 로그인하여 매 문장마다 "자동 종료" 하도록 하는 코드이다. 

"자동 종료"란 매 명령이 실행되면 그 결과를 DB에 바로 반영하는 것을 의미한다. 

자바에서 내포된 SQL을 사용하기 위해서 위와 같이 자바에서 DB가 연동되었다고 가정한다. 

 

name  readEntry("input name"); //name 변수에 사용자부터 값을 입력 받음
try{
  #sql {
    SELECT Fname, Lname INTO :fanem, lname 
    FROM EMPLOYEE 
    WHERE Name = :name
  };
} catch(SQLException se) {
  System.out.println("error");
}

System.out.println(fname+","+lname);

자바에서도 역시 프로그램 변수의 개념이 사용되는데 SQL 코드와 자바 코드에서 둘다 접근이 가능한 변수이다. 

SQL 문에서는 :을 붙혀 사용하고 자바 코드에서는 그냥 사용한다. 

 

자바에서 내포된 SQL문장은 #sql {}; 괄호 내부에 작성된다. 

그리고 자바에서는 오류를 다루기 위해 예외 개념이 사용된다. 이는 c언에서 내포된 SQL에서 SQLCODE, SQLSTART와 비슷한 역할을 한다. 

위 예제는 SELECT 절이 반환하는 값들이 하나의 값이기 때문에 반복자 없이 INTO 절로 바로 작성이 가능하다.

아래 예시는 SLEECT 절에서 반환되는 값들이 여러개여 반복자를 통해 접근하는 예시를 다룬다. 

 

3-2) Java언어 예시2: 반복자를 사용한 질의 처리

SQLJ의 반복자는 질의 결과 내의 레코드들의 모임과 연관된 객체타입으로 2가지 타입이 있다.

1. 명명된 반복자 
2. 위치 반복자

아래 코드는 명명된 반복자의 예시이다. 

#sql iterator Emp(String name, int age, String Address);
Emp e = null; //명명된 반복자 타입의 Emp 선언
#sql e = {
	SELECT name, age, Address
	From PERSON
	WHERE (...)
};

whlie(e.next()) {
	System.out.println(e.name+ "," + e.age + "," + e.Address);
}

e.close();

명명된 반복자 타입으로 지정된 애트리뷰트 이름은 질의 결과 내에 있는 애트리뷰트 이름과 일치해야 한다. 

명명된 반복자는 질의 결과의 애트리뷰트의 이름과 타입의 목록을 표시할 수있다. 

 

아래 코드는 위치 반복자의 예시이다. 

#sql iterator Emppos(String, int, String);
Emppos e = null;
#sql e = {
	SELECT name, age, Address
	FROM PERSON
	WHEHE (...)
};

#sql {FETCH: e INTO :name, :age, :ad};
while(!e.endFetch()) {
	System.out.println(name+","+age+","+ad);
	#sql {FETCH: e INTO :name, :age, :ad};
}
e.close();

결국 위치 반복자 역시 결과는 동일한데 FETCH 작업을 추가로해줘야하는데 이는 내가 원하는 프로그램 변수에 질의 겨로가의 값을 할당할 수 있다는 장점이 있다. 

 

4. 함수 호출, 클래스 라이브러리를 통한 DB 프로그래밍