Dynamic SQL의 사용

Dynamic SQL의 사용

보통의 어플리케이션 프로그램에서는 SQL문이 확정된 후 프로그램에 적용한다.

그러나, 다이나믹 SQL의 사용하는 경우는 사용자의 입력에 의거해서 SQL문이 작성되거나

다른 STATIC SQL문의 결과에 의해서 SQL문이 생성되는 경우 다이나믹SQL을 사용하게 된다.

비교될 컬럼이 변경되는 경우(WHERE절), 참조할 테이블이 변경되어야 하는 경우, INSERT, UPDATE시의 컬럼이 변경되는 경우이 있다.

Method	SQL문
1	QUERY문이 아니면서 호스트 변수가 없는 경우
2	QUERY문이 아니면서 호스트 변수가 정해지지 않은 경우
3	호스트 변수와 SELECT컬럼이 정해진 QUERY문인 경우
4	호스트 변수와 SELECT컬럼이 정해지지 않은 QUERY문인 경우

< Method 1 >

이 방법은 Dynamic SQL문을 작성하고 “EXECUTE IMMEDIATE”를 사용하여 즉시 실행한다.

SQL문은 QUERY문이 아니어야 하고 - (SELECT문)- 입력용 호스트 변수가 존재하지 않아야 한다.

Method 1은 SQL문이 매번 실행될 때마다 PARSING을 한다.

'DELETE FROM EMP WHERE DEPTNO = 20'

'GRANT SELECT ON EMP TO scott'

< Method 2 >

이 방법은 Dynamic SQL문을 작성한 후 “PREPARE”와 “EXECUTE” 명령에 의해 실행한다.

SQL문은 QUERY문이 아니어야 하고, 여기에서 사용된 호스트변수는 프리컴파일 시점에서는데이타 타입과 위치는 정해져 있어야 한다.

'INSERT INTO EMP (ENAME, JOB) VALUES (:emp_name, :job_title)'

'DELETE FROM EMP WHERE EMPNO = :emp_number

이 방법에서는 SQL문은 단 한번만 PARSING하게 되고 호스트 변수의 값을 달리하고 여러번 실행시킬 수 있다.

CREATE나 GRANT같은 DDL 문은 PREPAREd된 후에 실행 할 수 있다.

< Method 3 >

이 방법은 Dynamic SQL문을 작성한 후 “PREPARE”, “DECALRE”, "OPEN", "FETCH", "CLOSE" 와 같은 커서 명령으로 실행한다.

SELECT-LIST와 입력 호스트변수의 데이타타입과 위치는프리컴파일 시점에서는 정해져야 한다.

이 방법은 query문이어야 한다.

'SELECT DEPTNO, MIN(SAL), MAX(SAL) FROM EMP GROUP BY DEPTNO'

'SELECT ENAME, EMPNO FROM EMP WHERE DEPTNO = :dept_number'

< Method 4 >

이 방법은 실행 시점까지 SELECT-LIST와 호스트변수의 데이타 타입과 갯수, 위치를 모를 경우사용하는 방법이다.

'INSERT INTO EMP () VALUES ()' 'SELECT FROM EMP WHERE DEPTNO = 20'

위의 네가지 방법 모두 Dynamic SQL문을 charactor string에 저장하고, "EXEC SQL"과 ";"는생략한다.

Method 2와 3은 입력 호스트변수의 위치와 데이타타입을 프리컴파일 시점까지 정해져있어야 한다.

Method 4는 가장 유연성이 좋다. 반면에 복잡한 코딩이 들어가도 Dynamic SQL의 개념을알고 있어야 하기 때문에 잘 사용하지 않는다.

보통 Method 4는 Method 1, 2, 3으로 해결 할 수 없는 경우에 사용한다.

만약 프리컴파일 옵션을 DBMS=V6나 DBMS=V6_CHAR인 경우 SQL문을 배열에 저장하기 전에 BLANK를 채워줘야 한다.

그렇게 함으로서 변수를 CLEAR해 준다. 특히 배열을 다른 SQL문에서 다시 사용할 경우에는 특히 중요하다.

항상 SQL문을 저장하기 전에 호스트 스트링을 초기화해야 한다.

오라클에서는 NULL-TERNIMATE는 스트링의 마지막이라고 인식하지 못하고 SQL문의 일부로인식하기 때문에 사용하여서는 안된다.

만일 프리컴파일 옵션을 DBMS=V7로 했을 경우, 스트링의 값은 "PREPARE", 또는 "EXECUTE IMMEDIATE"하기 전에 NULL-TERNIMATE로 끝을 맺어줘야 한다.

DBMS옵션의 값을 개의치 않을 경우에는 다이나믹 SQL문을 저장할 변수로 VARCHAR를 쓸 경우,

VARCHAR의 length를 "PREPARE"나 "EXECUTE IMMEDIATE"를 실행하기 전에 정확하게 SET해주어야 한다.

Method 1

결과값이 단순히 SUCCESS나 FAILURE인 간단하고 호스트변수를 사용하지 않는 다이나믹SQL문일 경우 사용한다.

이 방법은 "EXECUTE IMMEDIATE"를 사용하여 다이나믹 SQL문을 실행시킨다.

EXEC SQL EXECUTE IMMEDIATE {:host_string | string_literal };

char sql_stmt[132];

....

for (;;){

printf("Enter SQL statement: ");

gets(sql_stmt);

if(*sql_stmt == '\0') break;

EXEC SQL EXECUTE IMMEDIATE :sql_stmt;

}

EXEC SQL EXECUTE IMMEDIATE 'REVOKE RESOURCE FROM MILLER';

Method 2

이 방법으로 실행할 경우 2번의 작업을 거쳐야 한다.

다이나믹SQL문은 QUERY문이어서는 안되고 첫번째로 PREPARE하고 EXECUTE되어 진다.

SQL문에는 호스트변수와 INDICATOR변수를 가질 수 있다.

PREPARE문은 한번만 수행하면 되고 EXECUTE문은 다른 호스트 변수값으로 여러 번 수행할 수 있다.

더 나아가서 COMMIT이나 ROLLBACK문장을 수행하고 나서도 다시 PREPARE할 필요가 없다.(LOG OFF이나 RECONNECT가 아닌 경우)

EXEC SQL PREPARE statement_name FROM {:host_string | :string_literal };

PRAPARE 명령은 SQL문을 PARSING하고 이름을 부여한다.

위에서 statement_name은 호스트변수, 프로그램 변수가 아니고 프리컴파일러가 사용할 임시적인 변수로 DECALRE SECTION에 기술할 필요가 없다.

EXEC SQL EXECUTE statement_name [USING host_variable_list];

:host_variable_list = :host_variable[:indicator1]

[, :host_variable[:indicator2], ...]

EXECUTE는 PARSING된 SQL문을 "USING" 절의 호스트변수를 이용하여 수행한다.실제로 사용되는 모습은 아래와 같다.

...

int emp_number;

char delete_stmt[120], search_cond[40], temp[10];

...

strcpy(delete_stmt, "DELETE FROM EMP WHERE EMPNO = :n AND ");

printf("다음의 SQL문에서 검색조건을 입력하여 완성하시오.\n");

printf("%s\n", delete_stmt); gets(search_cond);

strcat(delete_stmt, search_cond);

EXEC SQL PREPARE sql_stmt FROM :delete_stmt;

for(;;){

printf("사원번호를 입력하세요: ");

get(temp);

emp_number = atoi(temp);

if (emp_number == 0) break;

EXEC SQL EXECUTE sql_stmt USING :emp_number;

}

......

USING절의 이용

SQL문이 EXECUTE될 때, USING절에 입력된 호스트변수의 값은 PREPARE된 다이나믹 SQL문에서 대응되는 위치에 대치된다.

PREPARE된 다이나믹SQL문에서 각각의 변수 위치는 USING절의각각의 다른 호스트변수와 대치되어야 한다.

Method 3

이 방법은 Method 2와 비슷하나 PREPARE문장과 함께 커서를 선언하고 조작 하는게 필요하다.

QUERY문을 사용할 수 있다. 사실상 다이나믹SQL문이 QUERY문이면 Method3이나 4를 사용해야한다.

SELECT-LIST의 컬럼의 갯수와 호스트 변수의 갯수가 프리컴파일 시점에서는 정해져 있어야 한다.

실행시점 전까지는 테이블명과 컬럼명등 데이타베이스 OBJECT의 이름은 정해져야 한다.

데이타베이스 OBJECT의 이름은 호스트변수를 사용하지 못한다.

조건절(WHERE)이나 GROUP BY절, ORDER BY절 역시 실행 시점까지 정해져야 한다.

EXEC SQL PREPARE statement_name FROM { :host_string | string_literal };

EXEC SQL DECALRE cursor_name CURSOR FOR statement_name;

EXEC SQL OPEN cursor_name [USING host_variable_list ];

EXEC SQL FETCH cursor_name INTO host_variable_list;EXEC SQL CLOSE cursor_name;

PREPARE는 다이나믹SQL문을 PARSING하고 이름을 부여한다. 아래의 예제는 select_stmt문자열을 sql_stmt로 이름을 부여한다.

char select_stmt[132] = "SELECT MGR, JOB FROM EMP WHERE SAL < :salary";

EXEC SQL PREPARE sql_stmt FROM :select_stmt;

보통 WHERE절은 실행 시점에서 터미널로부터 입력되거나 어플리케이션 프로그램에서자동으로 생성되게 한다.

DECLARE명령은 PREPARE에 의해 붙여진 이름의 SQL문으로 커서를 정의한다.

EXEC SQL PREPARE sql_stmt FROM :select_stmt EXEC SQL DECLARE emp_cursor CURSOR FOR sql_stmt;

위의 예에서 sql_stmt와 emp_cursor는 호스트변수나 프로그램의 변수로 선언할 필요가 없다.

OPEN명령은 커서에 메모리를 할당하고, 입력된 호스트변수를 BIND하고, QUERY를 실행시키고, 실행에 의해 나오는 데이타를 active set으로 설정한다.

OPEN명령은 커서를 ACTIVE SET의 첫번째 ROW에 위치하게 되고, sqlerrd[2]를 0으로 set한다.

EXEC SQL OPEN emp_cursor USING :salary;

FETCH명령은 INTO절에 대응되는 호스트 변수에ACTIVE SET에서 ROW를 리턴한다.

만약 더 이상의 데이타가 없으면 오라클은 sqlca.sqlcode의 값을 1403을 설정하고"no data found"를 리턴한다.

EXEC SQL FETCH emp_cursor INTO :mgr_number, :job_title;

CLOSE명령은 커서를 무효화 시킨다. 커서를 CLOSE하고 난 후에는 더 이상 FETCH는 실행되지않는다.

EXEC SQL CLOSE emp_cursor;