막장개발자의 블로그

원래 Static SQL이란 카테고리가 만들어 진 것보다 이 포스트를 작성한 게 먼저였으나, 쓰고보니 달랑 이 글만 올려놓기 보다는 Static SQL에 대해서 한 번 정리해보고자 하는 생각이 들어 한동안 비공개로 잠들어있던 비운의(?) 포스트이다.

Static한 SQL을 구사하는 데에 가끔 까다롭게 느껴지는 것이 사용자 선택이나 입력사항을 Static하게 구성하는 것이다.
예를 들어, 사용자가 A,B,C 항목을 선택했다고 할 때

Select column1, column2, column3
From Table1
Where column1 in ('A', 'B', 'C')
와 같은 SQL을 작성해야 한다고 하자.

사용자가 몇 개를 선택할지 알 수 없기 때문에 아래와 같은 Binding Parameter처리가 불가능한 것은 당연하다.
Select column1, column2, column3
From Table1
Where column1 in (:v1, :v2, :v3)


이런 경우 Dynamic SQL을 작성하는 경우가 많았고, 뭔가 '이건 아닌데..' 하면서도 다른 대안이 없었다.  
그러던 중,  데브피아에서 박정진(bleujin)님의 글을 읽고 방법을 찾았는데 Cartesian Product와 사용자 정의 함수를 이용하여 멋지게 문제를 해결한 방법이었다. 이 자리를 빌어 다시 한번 감사드린다.
시간이 되시는 분들은 링크를 타고 원문을 한번 읽어보시기 바라며, 강좌와 팁란에서 이름으로 검색하시면 주옥같은 글들을 더 보실 수 있을 것이다.

그동안 잘 써먹었었는데, 최근 oracle용으로 이 함수가 필요해 찾아보니 예전에 작성했던 건 Backup을 안해놨고 어디 있는지 찾기도 귀찮아 그냥 다시 작성했고 나중에 또 못찾을까봐-_- 여기 올려두기로 했다.
v_FullString : 전체 문자열(‘a|b|c|d|’)
v_DivString : 구분자(’|’)
v_Index : 몇 번째 내용(항목)을 가지고 올 것인가

oracle에서 getIndexString 함수를 이용하여 변하는 사용자 입력값을 Static SQL로 변환하는 방법은 다음와 같다.

간단히 설명하자면, copy_t는 복제용 Table이며, 보통 1~31까지의 값을 갖는 Table이다.
In 절 내의 SQL에서 Where 조건에 유효한 Join 조건이 기술되지 않았으므로 tbl1과 tbl2의 곱만큼 복제(Cartesian Product)가 일어나게 되고 이 복제된 Row들이 getIndexString을 거치면서 원하는 값들을 가진 Row로 가공되어 In 절에 공급되게 된다.

Where tbl2.no1 <=  (Length(tbl1.fullStr) - Length(replace(tbl1.fullStr, tbl1.divStr, ‘’)) / Length(tbl1.divStr)) 조건은
Where tbl2.no1 <=  (Length(‘a|b|c|d|e|f|’) –Legnth(‘abcdef’) / Length(‘|’)) 과 같으므로
결국 tbl2.no1 <= 6(들어온 입력값의 갯수)가 되고, 따라서 들어온 입력값만큼 복제가 일어나게 된다.

getIndexString 사용자정의 함수는 no1의 값에 따라 전체문제열에서 구분된 항목을 뽑아오는 함수이므로 a, b, c, d, e, f가 In절에 공급되게 된다.

너무 깔끔하지 않은가? Static한 SQL에 한번 맛을 들이고 나면 실력이 모자라 어쩔 수 없이 Dynamic SQL을 사용하게 되는 경우 웬지모를 죄책감을 가지게 되며, 끊임없이 해결방법을 찾아 헤메이게 될 것이다.