[강정식의 오라클 이야기]Dynamic SQL 사용방법 13 20 48,227

by 강정식 [강정식] dynamicsql dynamicquery dynamic sql dynamic query [2008.05.29 16:41:54]


Dynamic SQL 사용방법

안녕하세요. 강정식입니다.
이번에는 Q&A 게시판에 자주 올라오는 질문 중 Dynamic SQL 내용이 자주 올라오는것 같아
이번 기회에 그 용도와 사용방법을 설명해 드리고자 글을 쓰게 되었습니다.

이해를 돕기 위해 EMP 테이블과 DETP 테이블을 이용한 어플리케이션 화면을 가지고 설명을
드리겠습니다.


[EMP 데이터]


[DEPT 데이터]


[EMP 테이블과 DETP 테이블을 이용하여 만든 어플리케이션 화면]


만약 위의 화면처럼 3개의 선택조건이 주어져 있고 이를 바탕으로 FIND 할 때 데이터를 가져오게
하려면 다음과 같이 2개의 방식으로 SQL을 작성하실 수 있으실겁니다.

   

즉, STATIC SQL은 SQL이 고정된 상태로 선택조건들을 모두 포함하기 위해 선택조건이 안 들어올
경우를 대비하기 위해 NVL() 함수를 사용하였지만, DYNAMIC SQL은 조건이 들어올 때만 WHERE 조건에
조건이 추가될 수 있게 만드는 것입니다. 물론 위에 DYNAMIC SQL을 표현한 것처럼 IF ELSE 구문을
사용하면 SYNTAX ERROR가 나오기 때문에 다른 방법으로 접근을 해야 하지만요.

그럼 STATIC SQL과 DYNAMIC SQL을 비교하면서 설명을 드리겠습니다.

[STATIC과 DYNAMIC 비교]


결국 STATIC SQL이라는 것은 고정된 SQL형태를 만든 뒤에 이 SQL형태로 모든 조건들을 처리해야 하는 반면,
DYNAMIC SQL은 여러 로직으로 조건에 해당되는 SQL을 변수에 담아서 만든 뒤에 DBMS를 콜하기 때문에
보다 풍부한 SQL을 작성할 수 있습니다.

이처럼 DYNAMIC SQL이 STATIC SQL보다 여러 장점이 있는데도 불구하고 잘 사용되지 않는건 개발 난이도도 높고
개발시간도 현저히 늘어난다는 것입니다. 또한 STATIC SQL이 직관적으로 볼 수 있는 반면 DYNAMIC SQL은
로직으로 SQL을 만들어나가는 것이기 때문에 직관적이지 못하죠.

이제 STATIC SQL과 DYNAMIC SQL의 차이점을 알았으니 위에서 설명한 어플리케이션 화면을 PROCEDURE에서 어떻게
개발하는지 살펴보도록 하겠습니다.

[STATIC SQL, DYNAMIC SQL 처리방식 비교]


                     [STATIC SQL]                                                               [DYNAMIC SQL]


1. STATIC SQL 개발패턴


2. DYNAMIC SQL 개발패턴


이제 어느정도 STATIC과 DYNAMIC 비교가 되시나요? 제 개인적으로는 OLTP처럼 다양한 조건을 수용해야 할 경우에는
DYNAMIC SQL로 개발하는것이 좋을 것 같고, 배치작업을 수행할 때는 STATIC SQL로 개발하시는것이 좋은 것 같습니다.

여러분들은 어떻게 생각하시나요?

ps. 글 보시다가 궁금하거나 이상한 점 있으시면 리플 달아주시기 바랍니다.

blog : http://blog.naver.com/xsoft

by 박종정 [2008.05.29 17:02:46]
서로 장단점은 있겠지만..
저는 정적쿼리요^^ 동적쿼리는 개인적으로 지양하는 편임..ㅎㅎ

by 현 [2008.05.29 18:44:19]
좀 생각을 해보게 하는 내용이네요...
저는 ....
STATIC 선호, 필요에 따라 DYNAMIC 사용.

결국 그때 그?? 다르네요.....

by 강정식 [2008.05.29 19:56:51]
동적쿼리를 지양하시는 이유는 어떤것들이 있나요?

by 현 [2008.05.29 20:35:08]
저는 지양하지는 않아요.
단지 sql을 짤 때 로직을 집어 넣는 롤을 하지 않다보니 그렇구요,
한방 sql 을 짜야 하는 경우가 많으니까 정적sql을 선호하게 되는거죠.
개발자에게 조언을 할때는 동적 sql로 하라고 얘기는 많이 합니다..


by 허용운 [2008.05.30 08:11:45]
잘보고 가요~ :D

by 이지웅 [2008.05.30 09:38:40]
잘봤습니다~ 화이팅~ ^^

by 유정완 [2008.05.30 10:59:58]
조건이 들어오는 값이 10개정도이면 open ~ using도 더 복잡하겠네요
간단한 방법이 없나요?

by 강정식 [2008.05.30 11:41:03]
네.. dynamic sql의 아킬레스건을 정확히 짚으셨습니다.
이 부분을 로직으로 만드는 것 때문에 dynamic이 더욱 어렵게 느껴지는데요.
이 부분은 저도 좀더 내용을 찾아보고 말씀드리겠습니다.

by 궁금 [2008.05.30 12:49:56]
먼저 궁금한점은 보통 프로시져에서 커서를 쓸때 Ref cursor 를 많이 쓰지 않나요?

by 궁금 [2008.05.30 12:52:15]
그리고 반대로 OLTP 상에서 더욱 정적 쿼리를 써야하지 않을까하는 생각도 드는게. 동적 쿼리의 가장 단점은 매번 파싱이 일어나는건데.. 자주 호출되는 페이지의 쿼리는 프로시져로 정적 쿼리로 만든후 메모리에 keep 해놔야 하지 않을까요.

by finecomp [2008.05.30 15:51:08]
Dynamic 쿼리도 Static과 마찬가지로 한번만 파싱하고 바인딩하여 수행할 수 있고 물론 Array Processing도 가능합니다.
말씀하신 구분은 SQL에 입력상수값을 Literal 방식으로 처리하느냐 Bind방식으로 처리하느냐의 구분인 듯 합니다...^^;

by 궁금 [2008.05.30 23:50:20]
finecomp 님이 말씀하시는 파싱은 하드파싱인거 같구요.. ^^
제가 말씀드리는것은 소프트 파싱입니다.
제가 알고 있기로는 Static Query 를 써야 Library cache pin 을 잡고 있기 때문에 소프트 파싱을 하지 않는걸로 알고 있습니다. ^^;;
제가 잘못알고 있으면 자세히 설명을 부탁드립니다.

by 궁금 [2008.05.30 23:52:57]
또한 위의 쿼리가 Static 과 Dynamic 으로 써야만 하는 이유는
테이블 모델을 조금만 더 신경 쓴다면 해결할수 있을것도 같습니다.
제가 비지니스 로직을 다 알수 없기에 정확한 답변은 드릴수는 없지만 그럴것같아서 말입니다. ^^;; 주저리주저리 말이 많았습니다..
그리고 정식님의 글 때문에 많은것을 다시 생각하게 되었습니다.
감사합니다.

by 박종정 [2008.06.02 15:37:13]
저의 개인적인 이유는 위에 언급하셨듯이 동적쿼리의 직관적이지 못한 면과
유지보수의 어려움 때문에 사용하지 않고 있습니다.
그리고 상당수 쿼리는 정적쿼리로 구현이 가능하다 생각되며,
또 개인적으로...프로시저에서 쿼리 내의 로직(동적)보다 하나의 쿼리를 실행하기 위한 분기및 변수값의 변형을 주고 있으며, 위의NVL()과 같이 NULL 일때와 NOT NULL 일때의 각실행계획이 잡히지 않도록 STATIC으로도 해결가능하리라 봅니다. 번외로 두가지 타입의 실행계획이 뜬다는 것보다 NULL일때의
풀스캔을 탄다는것이 꺼림직스럽죠~..^^* 이런 꺼림직스런것도 잡고 가야겠죠~

그리고 NVL() 처리시 무조건 2개의 실행계획이 잡히는 것보다..
드라이빙 테이블의 인덱스의에 컬럼절에 영향을 받지 않을까하는
저의 짧은 소견입니다.

감사합니다.

by 강정식 [2008.06.05 17:21:08]
모두 좋은 의견 주셔서 감사드립니다. ^^

by 손님 [2009.04.06 18:36:13]
STATIC과 DYNAMIC 비교 굳입니다.

by 장선웅 [2009.11.17 10:08:23]
이제야~ 잘봤습니다~ 감사!

by 남반장 [2011.06.10 09:38:37]
요점만 쉽게 설명하시지 구지 저런문법들 사용하시면서 설명을 할필요가 있나 싶네요~~저같으면 좀더 이해하기 쉽게 설명할것같은데..
딴지는 아니구요..보면서 이걸 왜이렇게 설명을 하나하는 생각이 들어서요...

by 까망소 [2012.09.26 15:35:47]
DYNAMIC SQL 이 고민이었데 이렇게 좋은 설명을 해주셔서 무척이나 감사합니다. *^ㅡ^)/

by 롤초보 [2014.04.07 11:36:56]
덧붙여서 참조할 내용이 있어서 Oracle Article을 첨부합니다.
PL/SQL 에서의 Static vs Dynamic 에서의 Perforamce 의 경우 Parse 의 Count 가 흥미롭네용~_~
http://www.gurubee.net/article/19612
댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입