바인딩을 사용해서 좀 줄여보자.
1.객체를 생성한다
create or replace type bindTable
as table of number;
/
2.바인딩을 처리할 함수를 만든다
create or replace function parameterChangeIn(p_str in varchar2) return bindTable
as
l_str long default p_str||',';
l_n number;
l_data bindTable := bindTable();
begin
loop
l_n:= instr(l_str ,',');
exit when (nvl(l_n,0)=0);
l_data.extend;
l_data(l_data.count) := ltrim(rtrim(substr(l_str,1,l_n-1)));
l_str := substr(l_str,l_n+1);
end loop;
return l_data;
end;
/
3.바인딩변수를 선언한다.
variable bind_variable varchar2(30)
exec :bind_variable := '1,3,5,7,99'
4.테스트를 해본다.
select *
from table (cast(parameterChangeIn(:bind_variable) as bindTable));
5.적용하기
select *
from all_users
where user_id in(select *
from table (cast(parameterChangeIn(:bind_variable) as bindTable)));
6.실질적으로 자바의적용할땐
StringBuffer strSQL = new StringBuffer();
strSQL.append(" select * ") ;
strSQL.append(" from all_users ") ;
strSQL.append(" where user_id in(select * ") ;
strSQL.append(" from table (cast(parameterChangeIn(?) as bindTable)) ") ;
psmt = con.prepareStatement(strSQL.toString());
psmt.setString(1,'1,2,3,4,5');
원문)expert one-on-one Oracle