[번역] Embadded SQL - 02

목 차
▣ 호스트 변수 ( Host Variables )
▣ 지시자 변수 ( Indicator Variables )
▣ 기본 SQL 문장 ( The Basic SQL Statements )
▣ The DML Returning Clause
▣ 커서 ( Cursors )
▣ 최적화 힌트 ( Optimizer Hints )
▣ CURRENT OF절 ( The CURRENT OF Clause )
▣ 커서문 ( The Cursor Statements )
▣ Non-Scrollable 커서를 사용하는 예제
▣ Scrollable 커서를 사용하는 예제

3) 기본 SQL 문장 (The Basic SQL Statements )
이장에서는 데이타를 조작하고 쿼리하는 문장에 포커스를 두고 설명한다.
데이터를 조작하는 문장인 INSERT,UPDATE,DELETE문을 실행할때, 문장이 실행을
성공했는지 실패했는지 관심을 가질것이다. 이것을 알기위해서 단순히 SQLCA를
체크하면 된다. 체크하는 방법에는 두가지가 있다.

- WHERNEVER문을 체크하는 방법
- SQLCA 변수를 체크하는 방법

아래의 SQL문장들은 오라클의 데이터를 조작하고 쿼리 할 수 있도록 하는데 사용
된다.

아래의 SQL문장들은 명시적인 커서를 선언하고 조작하는데 사용된다.

3-1) SELECT문
SELECT문의 사용예는 아래와 같다.

EXEC SQL SELECT ename, job, sal + 2000
INTO :emp_name, :job_title, :salary
FROM emp
WHERE empno = :emp_number;

SELECT문에서 사용할 수 있는 SQL절은 아래와 같은것들이 있다.

☞ INTO
☞ FROM
☞ WHERE
☞ CONNECT BY
☞ START WITH
☞ GROUP BY
☞ HAVING
☞ ORDER BY
☞ FOR UPDATE OF

3-2) INSERT문
INSERT문의 사용예는 아래와같으며, 테이블이나 VIEW에 행을 추가할때 사용한다.

EXEC SQL INSERT INTO emp (empno, ename, sal, deptno)
VALUES (:emp_number, :emp_name, :salary, :dept_number);

서브쿼리사용
서브쿼리라고하면 SELECT문이 반복적으로 나오는것을 말하며, 이것은 다음과 같은
경우에 사용된다.
☞ SELECT,UPDATE,DELETE문의 WHERE,HAVING,START WITH절에서 비교를 위한 값을
제공한다.
☞ CREATE TABEL, INSERT문에서 삽입될 행을 설정한다.
☞ UPDATE문의 SET의 값을 정의 한다.

아래의 예제는 다른테이블로 행를 복사해서 삽입하는 예제이다.

EXEC SQL INSERT INTO emp2 (empno, ename, sal, deptno)
SELECT empno, ename, sal, deptno FROM emp
WHERE job= :job_title ;

3-3) UPDATE문
UPDATE문은 테이블에서 특정컬럼의 값을 변경할때 사용하며, 사용예는 아래와같다.

EXEC SQL UPDATE emp
SET sal = :salary, comm = :commission
WHERE empno = :emp_number;

아래의 예제는 UPDATE문을 해서 서브쿼리를 사용한 예이다.

EXEC SQL UPDATE emp
SET sal = (SELECT AVG(sal)*1.1 FROM emp WHERE deptno = 20)
WHERE empno = :emp_number;

3-4) DELETE 문
행을 삭제할 경우 사용하는 문장이다. 예제는 아래와 같다.

EXEC SQL DELETE FROM emp
WHERE deptno = :dept_number ;

4) DML RETURNING절
INSERT, UPDATE, DELETE문의 선택적인 DML RETURNING절을 가질 수 있다.
아래는 DML RETURNING 절의 예제이다.

{RETURNING | RETURN} {expr [,expr]}
INTO {:hv [[INDICATOR]:iv] [, :hv [[INDICATOR]:iv]]}

위의 표현식에서 expr은 컬럼값을 표현식이며, hv는 호스트변수,iv는 호스트 지시자
변수이다. 표현식의 숫자는 호스트 변수의 수와 같아야만 한다.
이절은 INSERT,UPDATE후에 행수를 취득할 필요가 없으며, DELETE전에 프로그램에서
레코드를 할 필요가 없다. RETRURNING 절은 불필요한 네트웍 부하를 줄이고, 서버
메모리를 줄일수 있다.
Oracle Dynamic SQL Method 4는 DML RETURNING 절을 지원하지 않는다.

5) 커서(CURSOR)
아래의 문장들은 커서를 조작하고 선언하는데 사용된다.

☞ DECLARE CURSOR
☞ OPEN
☞ FETCH
☞ CLOSE

우선, DECLARE CURSOR문을 이용해서 커서이름을 선언하는 사용한다.
OPEN문을 이용해서 쿼리를 실행하고 쿼리에 맞는 행을 확인한다. FETCH문은 현재행의
값을 취득하는데 사용한다. 모든행의 값을 취득할때까지 반복적으로 FETCH문을
실행할 수 있다.

데이터를 다 취득한후 CLOSE문을 사용해서 커서를 끝낸다.
아래에 설명 부분은 이들 커서제어문을 어떻게 사용하는지 보여주고 있다.

5-1) DECLARE CURSOR문
DECLARE CURSOR문의 사용예는 아래와 같다.

EXEC SQL DECLARE emp_cursor CURSOR FOR
SELECT ename, empno, sal
FROM emp
WHERE deptno = :dept_number;

커서는 전처리기에서 사용되는 인식자이며, 호스트나 프로그램 변수가 아니다.
커서명은 하이픈으로 연결될수 없고, 길이를 가지고 있으면, 31문자 정도로 하는것이
좋으며, ANSI와 호환을 위해서는 18문자 이상되지 말아야한다.

CURSOR와 연관된 SELECT문은 INTO절을 포함할 수 없으며, FETCH절에 사용한다.
DECLARE CURSOR 문은 커서를 참조하는 모든 SQL문장보다 먼저 선언되어야한다.
아래의 예제는 OPEN문이 잘못 사용된 예제이다.

...
EXEC SQL OPEN emp_cursor;
EXEC SQL DECLARE emp_cursor CURSOR FOR
SELECT ename, empno, sal
FROM emp
WHERE ename = :emp_name;

커서 제어문들은 같은 전처리 단위내에서 발생해야한다. 예를들어, A 파일내에서
커서를 선언하고 B파일에서 OPEN 할 수는 없다. 호스트 프로그램에서 DELCARE문은
많이 사용될수 있지만 모든 DECLARE문은 유일해야한다. 즉, 하나의 전처리단위에서
같은 이름으로 커서를 사용할 수 없다.

5-2) OPEN문
OPEN문은 쿼리를 실행하고 ACTIVE SET을 확인하기위해서 사용한다.
OPEN시에 SQLCA에서 SQLERRD의 세번째요소인 행진행수를 0으로 맞춘다.
이시점에 프로그램에서 볼수있는 행은 아무것도 없다. 그것을 FETCH문에 의해서
다루어진다.
OPEN시 커서를 ACTIVE SET의 맨처음 행에 위치시킨다.
커서가 한번 오픈되면, 쿼리의 입력호스트 변수는 다시 커서가 열리기전까지 재 검사를
하지 않는다. 그래서 ACTIVE SET은 변경되지 않는다. 만약 ACTIVE SET을 변경하기고자
한다면 커서를 다시 열어야만 한다.

5-3) FETCH문
FETCH문은 ACTIVE SET으로부터 행의 값을 추출하여 결과에 포함된 출력호스트변수를
지정하는데 사용한다. 커서와 연관된 SELECT문은 INTO절이 삽입될수 없다는것을
기억하라.
아래의 예제는 3개의 호스트변수에 값을 입력하는 예제이다.

EXEC SQL FETCH emp_cursor
INTO :emp_name, :emp_number, :salary;

각각 다른 호스트변수에 같은 커서에서 취득한 데이터를 설정하는 예제를 아래에서
보여주고 있다.

EXEC SQL DECLARE emp_cursor CURSOR FOR
SELECT ename, sal FROM emp WHERE deptno = 20;
...

EXEC SQL OPEN emp_cursor;
EXEC SQL WHENEVER NOT FOUND GOTO ...
for (;;)
{
EXEC SQL FETCH emp_cursor INTO :emp_name1, :salary1;
EXEC SQL FETCH emp_cursor INTO :emp_name2, :salary2;
EXEC SQL FETCH emp_cursor INTO :emp_name3, :salary3;
...

}

ACTIVE SET이 비었거나 아무 데이터도 포함하고 있지 않다면, FETCH는 "no data found"
에러코드를 SQLCA의 sqlcode에 리턴하던지 혹은 SQLSTATE상태 변수 또는 SQLCODE에 리턴
할 것이다.

아래의 상태에서 FETCH하는 경우 에러가 발생한다.

☞ 커서를 OPEN하기 전 상태
☞ "no data found"후에 FETCH한 상태
☞ 커서를 닫은후 FETCH한 상태

5-4) CLOSE문
아래의 예처럼 커서를 닫는다. 커서를 닫는다고 하는것이 오픈되었던 커서에 의해서
서버에 잡혀져 있던 리소스를 해제하는 것이다.

EXEC SQL CLOSE emp_cursor;

출처 - http://younbok.egloos.com/9342724