[SQL] 내포된 SQL

내포된 SQL

SQL이 호스트 언어의 완전한 표현력을 갖고 있지 않기 때문에 모든 질의를 SQL로 표현할 수 없음

또한 SQL은 조건문(IF), 반복문(WHILE), 입출력 등과 같은 동작, 사용자와의 상호 작용 등의 기능을 가지고 있지 않음

따라서 DB에 접근하는 부분은 SQL이 맡고 SQL에 없는 기능은 호스트 언어로 작성해야 함

SQL 컴파일 과정

편집기로 호스트 언어 안의 내포된 SQL을 가지는 프로그램을 작성한다.

이 프로그램을 전컴파일러(preprocessor)로 처리한다.

그러면 내포된 SQL이 호스트 언어와 함수 호출문으로 변환이 된다.

변환된 프로그램은 C, java 컴파일러 등에 의해 오브젝트 프로그램을 생성한다.

링커는 오브젝트 프로그램과 SQL 라이브러리를 링크를 해서 .exe 확장자를 갖는 실행 프로그램으로 만든다.

 

Pro*C

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

소스 파일의 확장자는 .pc

.pc 파일을 Pro*C를 통하여 컴파일하면 .c인 소스 프로그램이 생성됨

 

호스트 변수

SQL문에 포함된 C 프로그램의 변수

호스트 언어와 SQL 문 사이에 통신을 위해 사용됨

호스트 변수를 SQL 문에서 사용할 때에는 콜론(:)을 붙여 사용

DECLATE SECTION을 통해 선언한다. (Oracle은 사용하지 않고도 선언 가능, 표준은 아님)

 

정적인 SQL

C 프로그램에 포함된 완전한 SQL

입력값과 출력 데이터를 위해 C 프로그램의 변수들을 포함할 수 있다.

프로그램의 경우 화면에서 결과를 보려고 하는 것이 아니라 프로그램 내에서 내용을 받아 사용해야 하므로 INTO를 통해 변수를 지정한다.

동적인 SQL

불완전한 SQL문

일부 또는 전부 질의가 수행될 때 입력 가능

SQL 구조를 완전히 알 필요 없음

컴파일 시점에 SQL 문을 알지 못함

 

질의 최적화 : SELECT문에서 관계 대수식으로 변환할 때 수행 속도가 가장 빠른 관계 대수식을 골라냄

정적인 경우에는 SQL이 알려져 있기 때문에 질의 최적화 수행 가능

동적인 경우에는 컴파일 타임에 질의 최적화 불가능, 프로그램이 실행되는 실시간에 질의 최적화 실행해야 함.

prepare : 질의 최적화

execute : 질의 시행

prepare와 execute를 합쳐 execute immediate로 표현할 수도 있다.

prepare는 시간이 오래 걸린다. (가능한 관계 대수 중 가장 빠른 관계대수식을 뽑아내기 때문에 처리 시간이 오래 걸림)

1번 수행하고 끝난다면 execute immediate를 쓸 수도 있다.

여러 번 사용한다면 prepare로 뽑고 execute만 여러 번 사용해 시간이 많이 걸리는 prepare 연산을 1번만 실행한다.

 

불일치 문제

호스트 언어는 단일 변수 및 레코드 위주의 처리(튜플 위주의 방식)를 지원하는 반면

SQL은 데이터 레코드들의 처리(집합 위주의 방식)를 지원하기 때문에 불일치 문제가 발생한다.

이를 해결하기 위해 커서를 사용한다. 

커서

DECLARE CURSOR문을 사용하여 커서를 정의한다.

OPEN cursor문을 통해 질의를 수행하고, 질의 수행 결과 첫 번째 튜플의 전을 커서가 가리키게 한다.

FETCH문을 통해 커서를 다음 튜플로 이동시키고 그 튜플의 애트리뷰트 값들을 FETCH문에 명시된 호스트 변수들에 복사한다.

CLOSE cursor문을 통해 커서를 닫는다.

 

커서 사용 예시

위의 예시는 title에 속하는 튜플이 1개이기 때문에 FETCH를 한 번만 사용하였다.

여러 튜플을 읽어오기 위해서는 루프 내에 FETCH문을 사용해야 한다.

FETCH문에서 결과 집합이 비었거나 더 이상 가져올 튜플이 없다면 'no data found'를 발생시킨다.

SQL 문의 WHENEVER NOT FOUND에서 이를 감지한다.

 

WHENEVER

자동적인 에러 검사와 에러 처리를 위한 구문이다.

WHENEVER <조건> <동작>

<조건>

- NOT FOUND : WHERE 절을 만족하는 튜플이 없거나 SELECT INTO 혹은 FETCH가 row를 리턴하지 않을 때

- SQLERROR : 에러가 발생했을 때

- SQLWARNING : 경고가 발생했을 때

<동작>

- CONTINUE : WHENEVER문을 사용하지 않은 것과 같은 효과

- DO (function call | CONTINUE | BREAK) : 함수 호출 / 계속 진행 / 멈춤

- STOP : 프로그램 종료, COMMIT 되지 않은 작업은 ROLLBACK

 

커서를 통한 UPDATE

커서의 현재 튜플을 업데이트하려면 CURRENT OF 절을 사용하면 된다.

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

=> CURRENT OF 절이 있으면 개발자가 추가하지 않아도 전처리기가 알아서 추가한다.

Index-organized 테이블에는 사용이 금지되고, 한 테이블의 애트리뷰트들만 수정 가능하다.

 

SQLCA (SQL 통신 영역)

C 프로그램에 내포된 SQL문에 발생하는 에러들을 사용자에게 알려준다.

Sqlcode라는 멤버 변수가 가장 중요하고 널리 사용된다.

Sqlcode의 값이 0이면 마지막에 내포된 SQL문이 성공적으로 끝났음을 의미한다.

 

에러 메시지  : SQLGLM()

에러가 난 것을 먼저 확인하고 사용해야 한다.

에러가 나지 않았는데 SQLGLM()을 사용하면 Undefined statement가 반환된다.

=> WHENEVER로 사용하거나 SQLCODE 값이 0이 아닐 때

 

지시 변수

NULL 값 여부 등 호스트 변수에 추가적인 정보를 제공한다. 2바이트 정수로 표현된다.

호스트 변수 다음에 지시 변수를 선택적으로 추가할 수 있다.

지시 변수 앞에도 콜론(:)을 붙인다. 또는 호스트 변수와 지시 변수 사이에 INDICATOR 키워드를 사용해도 된다.

지시 변수 예시

truncated value : 원래 변수에서 일부만 가져온 것. 원래 변수의 길이를 저장할 수도, 저장하지 않을 수도 있음