데이터베이스(7), 내포된 SQL

데이터베이스(7), 내포된 SQL

2022. 10. 25. 04:36ㆍ강의 내용 정리/데이터베이스

728x90

내포된 SQL(embedded SQL)

1. 내포된 SQL

cf) 질의를 하는 두 가지 방법

(1) 사용자가 DBMS에 질의어를 입력하면 인터렉티브하게 사용할 수 있다.

(2) 호스트 언어에 포함된 질의어로 질의를 보낸다.

이때 호스트 언어라고 하는 것은 SQL을 포함하고 있는 언어를 의미한다. 자바나 C언어, 파이썬 등이 호스트 언어로서 사용될 수 있다. 이렇게 사용하는 이유는 SQL 언어가 일반 프로그래밍 언어만큼의 표현력을 가지지 않았기 떄문이다. 따라서 SQL만으로는 표현하지 못하는 것들이 있게 된다. 따라서 SQL로 표현할 수 없는 거는 호스트 언어를 통해 처리하게 된다. 조건문, 반복문, 입출력 등과 같은 동작, 상호작용, 질의 결과를 GUI로 보내는 등의 기능이 없기에 호스트 언어를 사용한다.

호스트 언어와 SQL에서 지원하는 데이터 구조가 달라서 impedance mismatch가 발생할 수 있다. 프로그래밍 언어는 튜플을 다루지만 SQL은 튜플의 집합(테이블)로 이를 사용하기에 데이터 구조가 맞지 않게 된다. 이는 커서를 통해 해결한다.

1) 내포된 SQL문의 컴파일 과정

호스트 언어 내에 프로그램을 작성하고, preprocessor(전컴파일러)를 통해 컴파일하면 호스트 언어는 그대로 나오지만 내포된 SQL은 함수 호출문으로 바뀐다. 이러한 함수는 ODBC, JDBC에서 제공하는 함수로 변환되는 것이다. 이는 변환된 프로그램으로 바뀐다. 따라서 C로 변환을 한다면 .C의 확장자로 바뀌게 된다.

이를 컴파일러를 통해 컴파일을 하면 오브젝트 파일이 만들어진다. 리눅스/유닉스에서는 .o, 윈도우에서는 .obj 형태로 바뀌게 된다. 이후 링커를 통해 SQL 라이브러리 등을 합쳐서 실행 프로그램(.exe)이 나오게 된다. 왼쪽 부분이 내포된 SQL과 관련된 내용이 된다.

cf) Pro*C

오라클에서 C 프로그램에 SQL문을 내포시키는 방법.

SQL문이 포함된 소스 파일의 확장자는 .pc가 된다.

2) 호스트 변수(host variable)

SQL 문에 포함되는 호스트 언어(ex) c프로그램)의 변수이다.

SQL문에 입력하거나 결과를 호스트 언어로 가져올 때 사용한다.

SQL문에서 이를 사용할 때에는 호스트 변수라는 것을 지시하기 위해 :을 붙여준다.

DECLARE SECTION을 사용해서 선언한다.

임베디드 SQL임을 알려주기 위해 EXEC SQL을 사용해서 이를 알려준다. 이후 BEGIN/END DECLARE SECTION을 사용해서 호스트 변수를 선언하는 부분을 알려준다.

3) 내포된 SQL의 종류

(1) 정적인 SQL문

프로그램이 작성되는 시점에 정해진 SQL문

empno를 키보드로 부터 입력을 받아서 사원번호에 해당하는 직급을 받아야하기에 이를 :no라는 호스트 변수로 전달받게 된다.

SQL문의 결과를 받기 위해 INTO 뒤에 있는 변수에 값을 저장할 수 있게 된다. 따라서 이 또한 호스트 변수가 사용되고 있다. 어트리뷰트가 두 개 이상이라면 INTO 뒤에도 두 개 이상의 호스트 변수가 ,로 구분되어져서 사용될 수 있다.

(2) 동적인 SQL문

프로그램이 수행 중에 변경될 수 있는 SQL문

불완전한 SQL문: 컴파일 이전에는 불완전한 SQL문이지만 수행되면서 완전한 SQL문이 된다.

ex) 도서관 상세 검색을 할 때 여러 검색 조건을 넣을 수 있다. 이때 검색을 할 때 제목을 검색할지, 저자를 검색할지 등등은 동적으로 계속 바뀔 수 있다. 이에 따라 이는 동적인 SQL문으로 작성한다.

EXEC SQL UPDATE ~질의어가 되면 이는 정적인 SQL문이다. 하지만 위의 경우에는 호스트 언어 상에서는 문자열로 존재하게 된다. 따라서 이는 동적 언어가 된다.

동적 SQL문을 실행시키기 위해서는 PREPARE를 먼저하고, 이후에 EXECUTE를 사용해서 실행한다. 이는 아래와 같이 한번에 할 수 있다. 질의 최적화 문제 때문에 일반적으로는 PREPARE/EXECUTE를 분리해서 사용한다. 만약 질의문을 한번만 사용하는 경우에는 한줄로 위와 같이 사용한다.

cf) PREPARE

SQL이 주어지면 이를 가장 빠른 관계 대수문으로 바꿔주는 질의 최적화를 하게 된다. PREPARE는 질의를 최적화를 하라는 의미가 된다. 정적인 SQL문은 질의 최적화를 할 수 있고, 동적으로 실행될 때 이를 처리한다. 그러나 동적인 SQL문은 컴파일 단계에서 최적화를 할 수 없으니 동적인 단계에서 사용하게 된다. 질의 최적화는 자원의 소요가 많이 되기 때문에 PREPARE는 한번만 사용하는 것이 좋다. 이후 EXECUTE는 여러번 사용해도 된다.

ex) 경희대학교 도서관 웹의 상세 검색을 할 때 동적인 SQL문 설계

상세 조건에 대한 필터는 Predicate로 받게 되기에 이를 분석해야한다. 각 Predicate는 다음과 같이 구성될 수 있다.

(attr, cmp_type, value)

enum CmpType(KEYWORD, FULLMATCH, HALFMATCH);
vector<Predicate> pl; // predicate list

// pl:
// [0]: ('저자', FULLMATCH, '홍길동')
// [1]: ('제목', KEYWORD', 'DATABASE')


sqlStmt = "Select * from Books where";

for (Predicate p : pl) {
        switch(p.cmp_type) {
            case KEYWORD:
                pStr = p.attr + "like % " + p.value + "%";
                break
            case FULLMATCH:
                pStr = p.atrr + "=" + p.value;
                break
            case HALFMATCH:
                ...
        }
        sqlStmt = sqlStmt + pStr + "AND"; // 마지막 프레디킷인 경우에는 이를 처리하지 않는다.
    }

ex) 호스트 변수

내포된 SQL을 사용하기 위해 EXE SQL INCLUDE SQLCA.H를 사용한다.

*uid: user id를 의미하며 이름과 이메일 주소를 입력해서 들어가게 된다.

CONNECT 뒤에 나오는 것은 JDBC에서는 달라진다.

수행이 끝난 뒤에 COMMIT WORK;을 수행한다. 문제가 발생한 경우에는 수행한 것을 되돌리기 위해 ROLLBACK WORK;을 수행한다.

4) CURSOR를 통한 불일치 문제(Impedance mismatch) 해결

불일치 문제를 해결하기 위해 커서가 사용된다. 커서는 현재 위치를 가리킬 때 사용하는 말이다. 두 개 이상의 튜플들을 검색하는 SQL문의 경우에는(SQL문의 결과가 2개 이상인 경우) 반드시 커서를 선언하고 사용해야한다.

DECLARE CURSOR문을 사용해서 커서를 정의한다.
OPEN cursor문을 통해 질의를 수행하고, 질의 수행 결과의 첫번째 튜플 이전을 커서가 가리키도록 한다. 이것이 커서의 현재 튜플이 된다.
그 다음에 FETCH문은 커서를 다음 튜플로 이동하고, 그 튜플의 애트리뷰트 값들을 FETCH문에 명시된 호스트 변수들에 복사한다.
모든 동작을 수행한 뒤에는 CLOSE cursor를 사용해 커서를 닫아준다.

DECLARE SECTION: 호스트 변수를 선언한다.
DECLARE '커서이름' CURSOR FOR 'SQL 문'을 적어서 사용한다.
OPEN title_cursor를 사용하면 SQL문을 수행한다.
마지막의 EXEC SQL FETCH title_cursor INTO :title;을 사용하면 해당 커서의 내용을 title에 전달한다. 여러번 사용할수도 있다.

5) WHENEVER

whenever라는 루프 내에 fetch문을 사용할 수 있다.

각 케이스를 설정해 루프를 빠져나오는 조건을 적어줄 수 있다.

ex) EXEC SQL WHENEVER NOT FOUND GOTO NotFoundLabel: 데이터가 없으면 NotFoundLabel로 이동한다.

6) CURSOR를 이용한 UPDATE

커서의 현재 값을 업데이트할 수 있다. 이때 CURRENT OF절을 사용한다.

CURSOR를 선언할 때 FOR UPDATE OF 키워드를 선택적으로 추가할 수 있다.

FOR UPDATE OF를 프로그램에서 사용하면 가독성이 좋아질 수 있기에 이러한 이유로 FOR UPDATE OF를 사용한다.

위의 예시에서는 title을 업데이트하기위해 CURSOR를 사용하겠다는 의미가 된다.

인덱스를 사용해서 정의된 테이블인 경우에는 사용할 수 없고, 한 테이블의 하나의 애트리뷰트만 업데이트할 수 있다.

7) SQLCA

SQL문에서 에러가 발생했을 때 이를 알려준다.

SQL문이 수행될 때마다 SQLCA 코드 값이 변경된다. SQLCA 코드 값이 0이면 마지막에 수행된 마지막의 내포된 SQL문이 성공적으로 끝났음을 의미한다. 그렇지 않으면 에러나 경고가 있었다는 의미가 된다.

(1) ORACA

SQLCA에서 확장해서 만든 구조체이다.

while문 내부에서 fetch를 하기에 SQLCODE가 계속 바뀌게 된다. 마지막에 0이 아니라면 에러가 발생한 것이다.

위에서 본 sqlcode와는 다르다. 의미는 동일하다.

8) 에러 메세지: sqlglm() 함수

사람들이 이해할 수 있는 형태로 에러코드를 출력하기 위한 변수

9) SQLSTATE 상태 변수

mode를 ANSI로 사용하면 이를 사용할 수 있다. SQLSTATE를 사용하기 위해선 총 6자리로 선언해야한다. (NULL 포함해야하기 때문)

10) 지시 변수

호스트 변수에 대한 정보를 2바이트 정수로 표현한다. 호스트 변수 바로 다음에 지시 변수를 선택적으로 사용할 수 있다. 명시적으로 INDICATOR 키워드를 사용해서 지시변수를 나타낼 수 있다.

위의 지시변수는 SQL의 출력을 해석하는 방법이다. 0>인 경우에는 20까지 저장가능한데 DB에는 25 데이터가 주어져있는 경우를 의미한다.

아래의 지시변수는 SQL의 입력을 해석하는 방법이다.