Static SQL

JSP환경에서는 많이 봐왔는데 ASP에서도 쓰는군요.
근데 이것은 Oracle에서만 효과를 보는건지. 함 찾아봐야겠군요.
현재는 MS-SQL에 적용을 해봣는데 아직까지는 아무이상이 없네요. ㅎㅎ

김영준(poo97@nownuri.net)님의 글을 퍼왔습니다.
==============================================

만약 Static SQL을 잘 쓰신다면 일반적으로 잘 알려진 방법 

ASP에서 Stored Procedure보다 더 나은 성능을 얻게 되실 수 있으리라 장담합니다.

글구 DB를 오라클로 쓰시는 분들께 정말 권해드리고 싶습니다.
(Oracle과 MTS는 뻑(bug)가 있다나요..)

자 다음과 같은 순서로 강의를 진행하려고 합니다.

1. Static SQL에 대해
2. Static SQL이 왜 빠를까?
3. 실질적인 Static SQL에 대해
4. Static SQL을 마치며..

그럼 1단원을 시작해볼까요...

아 참 전 ASP와 Oracle기준으로 이 강좌를 진행해나가겠습니다.
(이 점 유의하시길.)

1.Static SQL에 대해
*.Static SQL이 무엇일까?
우리는 일반적으로 쿼리를 작성합니다. 
만약 아래와 같은 테이블이 있다고 생각을 해보죠.
(예)
CREATE TABLE employee
(
empno        varchar2(4) primary key,       /* 사원번호 컬럼 */
empname     varchar2(20) not null,       /* 사원이름 컬럼 */
empaddr        varchar2(200) not null,       /* 사원주소 컬럼 */
empcphone   varchar2(11),           /* 사원핸드폰 컬럼 */
empstatus   varchar2(1)               /* 사원상태 컬럼 */
);

위와 같은 사원테이블이 있을 때 사번이 44번인 사람의 정보를 보고 싶다고 한다면 보통 이런 식으로 쿼리를 만들 것입니다. -_-;;
(ex)SELECT * FROM employee WHERE empno = '44'

만약 사번이 45인 사람의 정보를 보고 싶다면 위의 SQL에서 마지막비교되는 상수값이 44에서 45로 바꿜 것입니다.
(SQL1)SELECT * FROM employee WHERE empno = '44'

(SQL2)SELECT * FROM employee WHERE empno = '45'

그런데 데이터 베이스에서는 불행히도 SQL1과 SQL2를 서로 다른 Query로  보고 이것을 새롭게 파싱하고 DB의 Shared Pool에는 SQL1과 SQL2가  각각 다른 내용으로써 저장됩니다.
(참고: Shared Pool이란 컴퓨터에서 Cache Memory와 유사한 것으로 컴퓨터에서 어떤 프로그램을 실행시킬 때 필요한 데이터를 Memory에 올려서 쓰는데 그 내용중 일부는 cache memory라는 곳에 저장되어 만약 어떤 데이터의 요청이 일어난다면 cache memory를 먼저 찾아보고 내용이 없다면 memory에서 다시 찾아서 cache memory로 올리는 방식과 아주 유사합니다.
Shared Pool은 DB에서 사용자가 실행한 SQL문이 저장되는 영역입니다. 그러므로 만약 사용자가 SQL1을 사용하고 이것을 다시 사용한다면 DBMS는 Shared Pool에서 먼저 동일한
SQL이 존재하는지 찾고 나서 존재한다면 이미 파싱해놓은 내용을 가져오고 그렇지 않다면 다시 파싱을 하게 됩니다. )

SQL1과 SQL2는 거의 동일한 문자열과 빈칸등으로 되어 있지만 empno를 비교하는 상수가 다르므로 각각 다른 SQL이 됩니다.
그렇다면 SQL1과 SQL2를 동일한 SQL로 만들려면 어떻게 하면 될까요??

앗 벌써 눈치를 채신 분들이 계시군요..
그렇습니다.
(SQL3)SELECT * FROM employee WHERE empno = :inputnum

위와 같은 SQL로 대치될 수 있겠죠..

비교되는 상수값의 위치에다가 변수를 넣는거죠..
('무하하하, 난 역쉬 똑똑해!'라고 자만하시는 분들 있으시겠죠..)

(똑똑하십니다.  d_(-_-;;) (GOOD) )

근데 위와 같은 SQL은 토드나 SQL*PLUS에서는 사용할 수 있지만 일반적인 ASP나 VB에서는 사용이 불가능하답니다.
그래서 ASP에서 지원하는 방식으로 변수를 선언해야겠죠..
여기서 잠깐...

유식해지기 위해서 고급적인 단어 몇 개를 배워보기로 하죠. (제가 전 직장에서 같이 일한 과장님과 강남에서 술을 먹는데 

그 분이 외국계회사에서 일을 하시는지라 공부를 빡시게 하셔서 엄청 고급약어(?) 들을 쓰시는데 뽀대있어 보이더군요..
아직도 그 단어들에 대해서는 정확한 이해가 ~~~~~   쿨럭....   윤과장님 죄송해유... -_-;;;)

위의 :inputnum이라고 선언한 것은 parameter binding이라고 합니다.
parameter binding하니까 프로그래밍 언어론 시험칠 때 열씨미 외웠던 기억이...  제가 실력은 미진해도 무늬는 컴공이거든요.. 쿨럭-_-;;)
(참조) 이렇게 파라미터 바인팅 변수를 사용하는 SQL을 대용량데이터베이스책에서는 Static SQL이라고 명명하고 있습니다.
이것의 원리는 SQL1과 SQL2는 상수가 empno를 비교하는 값에 실제 상수가 들어가 있는데 반해 이것은 메모리의 주소값이 들어가 있다나요...(Maybe... ^_^;;)
전 옛날에 배운 C의 Pointer가 생각이 나더군요..
포인터 변수 쓰면 그곳에다 어떤 값들을 넣을 수 있잖아요..
(좀 유사하다고 생각하지 않으세요?--> 아니라고 생각하신다구요.. 그럼 말구..)

3. 실질적인 Static SQL에 대해 Static SQL을 쓰기위해서는 ADODB의 Command객체를 사용해야 합니다.
갑자기 깜찍이님의 Stored Procedure강좌가 생각이 나는군요..
CREATE TABLE employee
(
empno        varchar2(4) primary key,
empname     varchar2(20) not null,
empaddr        varchar2(200) not null,
empcphone   varchar2(11),
empstatus   varchar2(1)
);

강좌 1에서 사용하던 employee 테이블이죠..
(잊어버리셨을까 해서 다시 보여드립니다.
저 착하죠.. ^.^  )

<%    Option Explicit    %>

<!-- #include virtual = "/include/adovbs.inc"    -->

<%
Dim ConObj, DbCmd, InsSql

'(1)
Set ConObj = Server.CreateObject("ADODB.Connection")
ConObj.Open "test","scott", "tiger"

'(2) 
Set DbCmd = Server.CreateObject("ADODB.Command")
Set DbCmd.ActiveConnection = ConObj

'(3) 
InsSql = "INSERT INTO employee(empno, empname, empaddr, empcphone, empstatus) " &_
"VALUES(?, ?, ?, ?, ?)"

'(4) 
with DbCmd
.CommandText = InsSql
.CommandType = adCmdText

'(5) 
.Parameters.Append    .CreateParameter("@empno", adVarChar, adParamInput, 4)
.Parameters.Append    .CreateParameter("@empname", adVarChar, adParamInput, 20)
.Parameters.Append    .CreateParameter("@empaddr", adVarChar, adParamInput, 200)
.Parameters.Append    .CreateParameter("@empcphone", adVarChar, adParamInput, 11)
.Parameters.Append    .CreateParameter("@empstatus", adVarChar, adParamInput, 1)

'(6)    
.Parameters("@empno") = "0001"
.Parameters("@empname") = "김영준"
.Parameters("@empaddr") = "부산시 서구 남부민동"
.Parameters("@empcphone") = "01101011010"
.Parameters("@empstatus") = "1"

'(7)     
.Execute
End with

'(8)
Set DbCmd = Nothing
Set ConObj = Nothing
%>

자 위의 예제 소스를 보고는 어떤 느낌이 드시나요..
아니 이 놈이 뭐가 있는 것처럼 뻥만 치더니 알고 보니 아무것도 아니잖아.
뭐 그렇게 생각하셔도 할 수 없습니당. -_-;;;
자 그럼 순서대로 설명을 해볼까요.
아 참 먼저 간단한 팁 하나와 Static SQL을 꼭 해주어야 할 것들을 대해서 설명하겠습니다.
팁: Option Explicit를 꼭 사용하세요.
메모리 관리에도 좋구 변수 사용시 짱입니다요.
Static SQL사용하기 전에 먼저 adovbs.inc파일을 꼭 include해주셔야 합니다.
(왜냐구요, Command객체에서 사용되는 상수들이 adovbs.inc파일안에 모두 들어가 있기 때문에 꼭 필요합니다.)

(1)은 당근 DB에 컨넥션을 맺는것이죠.. (먼저 ODBC를 잊지 말고 세팅해주어야겠죠..)
(2)는 Command객체를 생성해야겠죠.. (왜냐구요.. Static SQL은 Command객체를 사용해서만이 지원이 가능하거든요..)
(3)변수선언해서 여기다가 Static SQL을 변수에다 저장해줍니다.
여기서 중요한 것은 Binding Parameter인데 눈치채셨겠지만 당연히 '?'입니다.  실망하셨다구요. 어쩔 수 없죠.   히  (@_@);;;;  
(4)그런 다음 With DbCmd라는 것을 쓰는데 이것은 With DbCmd와 End With내에서는 DbCmd의 property들을 사용하는 거죠.. 
CommandText는 말 그래도 명령문장인데 여기다가 위에서 변수에다 저장한 SQL을 넣어주죠..
(참고) .CommandText = "INSERT INTO employee(empno, empname, empaddr, empcphone, empstatus) " &_
"VALUES(?, ?, ?, ?, ?)"

위와 같은 방식으로 (3)부분을 생략하고 바로 SQL을 적어줄 수도 있습니다.

CommandType이 있는데요.
깜찍이님 강좌보신분들은 아실텐데요.
이 부분에다가 adCmdStoredProc를 쓰면 스토어드 프로시져 쓸 수 있다는 것 아시죠.
(다들 공부를 열씨미 하시니까 다 아실꺼에요.)
저처럼 adCmdText를 사용하시면 그냥 SQL을 사용하실 수 있습니다.
예를 들자면 

with DbCmd
.CommandText = "INSERT INTO employee(empno, empname, empaddr, empcphone, empstatus) "  &_
"VALUES('0001', '곰탱이', '서울시 서초구 양재동', '0111010101', '1')"
.CommandType = adCmdText
.Execute
End With

굳이 Static SQL을 쓰지 않으신다면 위와 같이 Command 객체를 사용해서 Query를 실행시키시지 마시길...
(왜냐하면 소스만 쓸데없이 길어지니까요..)

(5)Binding Parameter들의 변수타입과 사이즈를 정해주는 부분입니다.
일반적인 SQL을 실행시키는 것보다는 asp소스가 길어지죠..
그러나 성능향상을 위해서 소스 몇 줄이 길어지는 것은 감수를 해야죠.

눈치가 빠르신 분들은 이미 눈치채셨겠지만요..
@empno라고 해놓은 것을 볼 수 있습니다. DB테이블의 empno컬럼이 위치할 자리에 들어갈 Binding Parameter라는 것을 알리는 것이죠.
이런 걱정을 하시는 분들이 있겠죠.
만약 똑같은 이름의 변수를 써야 하면 어떻게 하냐고
(예) SELECT empname
FROM employee
WHERE empno = ?
AND empno = ?

위와 같은 경우에는 바인딩 파라미터를 어떻게 설정해야 할까요?
저의 입사동기인 창현씨가 정답을 알아냈습니다.(사실 제가 입사일짜는 며칠이 빠릅니당. 무하하)

For i = 0 To 1
.Parameters.Append    .CreateParameter("empname"&CStr(i), adVarChar, adParamInput, 20)
Next

위와 같이 골뱅이를 빼버리면 대입해주는 파라미터 순서대로 바인딩 파라미터가 세팅이 됩니다.
앗 3편까지 쓰는데 4시간이 걸리는군요..
(휴 힘들군요.. 시원한 아이스크림이라도 먹었으면)

(6) (5)에서 바인팅 파라미터을 세팅했다면 그에 대한 값들을 넣어주어야겠죠.

(5)번에서 예로 들었던 Static SQL에 대해서 바인딩 파라미터 값넣어주는 방법입니다.
For i = 0 To 1
.Parameters("empname"&CStr(i)) = CStr(emp(i))
Next

(주의)'@'을 사용하지 않고 바인팅 파라미터 세팅을 해줄때는 순서가 무척 중요합니다.
주의하시길...

(7)그런 다음 .Execute로 실행을 해주시면 됩니다.

그런데 만약 우리가 사용하고자 하는 SQL이 Select문이었다면 어떻게 될까요?

그럼 어떻게 해서 값을 가져와야 될까요?

만약 SELECT문을 Static SQL로 쓰는 경우 .Execute문을 밖으로 빼어냅니다.

그런 다음 Set ObjRecSet = DbCmd.Execute라고 설정하면 SELECT문에서 반환된 SQL문의 결과값이 ObjRecSet으로 저장되겠죠..

그런 다음 지지고(?) 볶으면 되겠죠..

(8)아 힘드셨죠.

마지막 Set DbCmd = Nothing입니다.

이 놈의 Command객체가 재사용이 불가능하다는 것을 알았습니다.

Static SQL을 하나 쓰고 나서는 Nothing해주고 나서 다시 (1)에서 (9)까지의 과정을 다시 거치셔야 합니다.

이 과정이 약간 번거로워서 그렇지 DB쪽에 걸리는 부하는 현저히 

줄어들게 됩니다.
(큰 사이트일 경우 더욱 더 빛을 발하겠죠..)