11 Static SQL 구현을 위한 기법들

(1) IN-List 항목이 가변적이지만 최대 경우 수가 적은 경우

!Static SQL 구현01.png!

• LP회원을 선택하는 그림 4-15 팝업 창에서 사용자가 선택한 LP회원 목록을 선택하고 자 할 때， Static 방식으로 SQL을 작성하려면 아래 7개 SQL을 미리 작성해야함.

select * from LP회원 where 회원번호 in (01')
select * from LP회원 where 회원번호 in (01', '02')
select * from LP회원 where 회원번호 in (01', '03')
select * from LP회원 where 회원번호 in (01', '02', '03')
select * from LP회원 where 회원번호 in (02')
select * from LP회원 where 회원번호 in (02', '03')
select * from LP회원 where 회원번호 in (03')

• Static SQL로 구현

select * from LP회원 where 회원번호 in ( :a, :b, :c )

• 4개의 체크 박스에 각각 내부적으로 'all' , '01' '02' , ' 03' 값이 부여돼 있다고 가정하고 decode문을 사용

select * from LP회원
where 회원번호 in ( decode(:a, 'all', '01', :b)
                  , decode(:a, 'all', '02', :c)
                  , decode(:a, 'all', '03', :d) )

(2)IN-List 항목이 가변적이고 최대 경우 수가 아주 많은 경우

!Static SQL 구현02.png!

• 가능한 경우 수가 너무 많아 Static SQL로 작성하기가 어려움, 바인드 변수를 사용하는 것도 쉽지 않다.
• SQL 조건절에는 대개 좌변에 컬럼을 두고 우변에는 그것과 비교할 상수 또는 변수를 위치시킨다 하지만 여기서는 생각을 바꿔 컬럼과 변수 위치를 서로 바꿔보자.

select * from 수시공시내역
where 공시일자 = :일자
and :inlist like '%' || 분류코드 || '%'

• 분류코드를 ,로 구분하여 String 변수에 담아 바인딩, :inlist := '01,03,08,14,17,24,33,46,48,53'

select * from 수시공시내역
where 공시일자 = :일자
and INSTR(:inlist, 분류코드) > 0

• (1) IN-List를 사용할 때

select * from 수시공시내역
where 공시일자 = :일자
and 분류코드 in ( ... )

• (2) like 또는 instr 함수를 사용할 때

select * from 수시공시내역
where 공시일자 = :일자
and INSTR(:inlist, 분류코드) > 0

• 소수 항목만으로 조회하거나 인텍스 구성이 분류코드 + 공시일자일 때는 인텍스를 좀더 효율적으로 액세스할 수 있는 방법
• <방법1> 작은 테이블 Full Scan으로 NL 조인 방식으로 분류코드 값을 해당 테이블 조회

select /*+ ordered use_nl(B) */ B.*
from ( select 분류코드
          from 수시공시분류
          where INSTR(:inlist, 분류코드) > 0 ) A
	     , 수시공시내역 B
where B.분류코드 = A.분류코드  

• <방법2> 분류코드를 String형 변수에 담아 바인딩
  :inlist := '01030814172433464853'

se1ect /*+ ordered use n1(B) */ B.*
frαn (se1ect substr( : in1ist, (rownum-1) * 2+1, 2)
from 수시공시분류
where rownum <= 1ength ( :inlist) / 2) A
' 수시공시내역 B
where B . 분류코드 A . 분류코드  

• <방법3> 〈방법 2>와 같은 방식이라고 할 수 있다. 다만， dual 테이블을 이용해 집합을 동적으로 생성

select /*+ ordered use nl (B) */ B.*
from (select substr (: inlist, (level-l )*2+1, 2)
from dual
connect by level <= length( :inlist) / 2) A
, 수시공시내역 B
where B . 분류코드 A. 분류코드

• (3) 체크 조건 적용이 가변적인 경우
  주식종목에 대한 회원사=증권사별 거래실적을 집계하는 쿼리

select 회원번호, SUM(체결건수), SUM(체결수량), SUM(거래대금)
from   일별거래실적 e
where  거래일자 = :trd_dd
and    시장구분 = '유가'
and exists (
	select 'x'
	from 종목
	where 종목코드 = e.종목코드
	and     코스피종목편입여부 = 'Y'
)
group by 회원번호

Static SQL

select 회원번호, SUM(체결건수), SUM(체결수량), SUM(거래대금)
from   일별거래실적 e
where  거래일자 = :trd_dd
and    시장구분 = '유가'
and exists (
	select 'x'
	from 종목
	where 종목코드 = e.종목코드
	and   코스피종목편입여부 = decode(:check_yn, 'Y', 'Y', 코스피종목편입여부)
)
group by 회원번호

select 회원번호, SUM(체결건수), SUM(체결수량), SUM(거래대금)
from   일별거래실적 e
where  거래일자 = :trd_dd
and    시장구분 = '유가'
and exists (
  select 'x' from dual where :check_yn = 'N'
	union all 
	select 'x'
	from  종목
	where 종목코드 = e.종목코드
	and   코스피종목편입여부 = 'Y'
	and   :check_yn = 'Y'
)
group by 회원번호 

• (4) select-list가 동적으로 바뀌는 경우
  사용자 선택에 따라 화변에 출력해야 할 항목이 달라지는 경우

!Static SQL 구현03.png!

/* 1 : 평균 2: 합계 */
if( pfmStrCmpTrim(INPUT->inData.gubun, "1", 1) == 0){
	snprintf(..., " avg(계약수), avg(계약금액), avg(미결제약정금액) ");
} else {
	snprintf(..., " sum(계약수), sum(계약금액), sum(미결제약정금액) ");
}

/* 1 : 평균 2: 합계 */
decode(:gubun, '1', avg(계약수), sum(계약수)),
decode(:gubun, '1', avg(계약금액), sum(계약금액)),
decode(:gubun, '1', avg(미결재약정금액), sum(미결재약정금액)),

• (5) 연산자가 바뀌는 경우

!Static SQL 구현04.png!

where  거래미형성률       between :min1 and :max1
and    일평균거래량       between :min2 and :max2
and    일평균거래대금     between :min3 and :max3
and    호가스프레드비율   between :min4 and :max4
and    가격연속성         between :min5 and :max5
and    시장심도           between :min6 and :max6
and    거래체결률         between :min7 and :max7

거래량 도메인은 9자리 숫자형이고 정수 값만 허용하므로 입력 가능한 최소값은 0 최대값은 999.999.999이다.
따라서 시용자가 1000주를 입력하면 사용자가 선택한 비교 연산자에 따라 아래와 같이 Between 시작값과 종료값을 바인딩