오라클바인딩기술

프로그램에서 DB 에 SQL을 보낼때 파라미터 처리시 고민될때가 가끔 있습니다. Oracle SQL 의 IN 구문을 인용해서 다양한 방식으로 파라미터를 바인딩 하는 법을 고찰해보았습니다. 보통 SI 프로젝트를 하면 개발의 70% 이상은 SQL을 만드는 일인것 같습니다. 프로그래밍할때 어려운 문제를 해결하기위해선 반드시 단계적으로 접근해야하는데 그건 SQL 도 마찬가지 인것 같습니다.

- 작성자 이상옥 (valenny@naver.com / valenny.tistory.com)
- 2009.7.28


- 개요
 
1. 바인딩에 대한고찰
2. 바인딩을 응용하여 파라미터에대해 유동적인(dynamic 한) SQL 작성


- 준비
 
-- 테이블생성
CREATE TABLE hijal_member (
      user_name    VARCHAR2(30),
      user_class    VARCHAR2(30)
);

-- 기초자료 입력
INSERT INTO hijal_member (user_name, user_class) VALUES ("천년전사비", "전사");
INSERT INTO hijal_member (user_name, user_class) VALUES ("쥐잡는소공녀", "마법사");
INSERT INTO hijal_member (user_name, user_class) VALUES ("펠소니야", "마법사");
INSERT INTO hijal_member (user_name, user_class) VALUES ("아룬", "드루이드");
INSERT INTO hijal_member (user_name, user_class) VALUES ("숨매", "사제");
INSERT INTO hijal_member (user_name, user_class) VALUES ("울바르", "전사");





1. 리터럴 vs 바인딩 (JAVA)
 

<구현-1>
 
/* 리터럴 */
String param = "천년전사비";
sql = "SELECT * FROM hijal_member WHERE user_name = '"+param+"'";

/* 바인딩 */
String param = "천년전사비";
sql = "SELECT * FROM hijal_member WHERE user_name = ? ";
... 중략 ...
pstm.setString(1, param);
... 실행부 생략 ...


java(혹은 다른 언어) 에서 DBMS 에 SQL이랑 파라미터를 전달하는 방법은 크게 저 두가지 방식입니다. 리터럴로 작성하거나 바인딩처리(PreparedStatement 이용)하는 방식 이 두방식 모두 잘 작동합니다. 어떻게 작성하든 프로그래머의 입장에서는 모두 임무를 수행했다 라고 할 수 있습니다.
하지만 OTN(Oracle Technology Network) 에선 바인딩을 권장합니다..
그 이유는 "왜 PreparedStatement 를 사용해야하는가에 대한 답"일 것입이다.


2. 하나의 필드와 비교되는 여러 값을 인자로 하는 쿼리 만들기(IN ~)
 
앞서서 본 예제에서 하나의 값을 갖는 인자에대해선 비교적 간단한 SQL을 만들 수가 있었습니다.
하지만 가끔 우리는 하나의 값이 아니라 여러 값을 비교해야만 하는 상황에 놓입니다.
아래의 SQL 문을 보도록 하겠습니다.

<SQL-1>
 
SELECT *
  FROM hijal_member
WHERE user_name IN ('쥐잡는소공녀', '천년전사비');
아주 전형적인 IN 구문을 이용해서 여러 조건에대해 OR 검색을 하는 예입니다.

이제 응용 프로그램에서 이를 어떻게 구현할 것인지 고민해 보도록 하겠습니다.

<구현-2>
 
/* 리터럴 */
String param = "'쥐잡는소공녀','천년전사비'";
sql = "SELECT * FROM hijal_member WHERE user_name IN ("+ param +")";

/* 바인딩 */
String[] param = { "쥐잡는소공녀", "천년전사비" };
sql = "SELECT * FROM hijal_member WHERE user_name IN ( ?, ? )";
... 중략 ...
pstm.setString(1, param[0]);
pstm.setString(2, param[1]);
... 실행부 생략 ...

위처럼 한개의 필드로 두개 이상의 값을 비교해야하는경우 위에처럼 IN 구문을 활용하여 파라미터를 세팅해주면 됩니다. 리터럴 의 경우도 바인딩의 경우처럼 루프를 돌려서 할수 있으나, 여기서는 그냥 파라미터 자체를 조작해서 SQL을 조작하는 방식으로 작성하였습니다.
리터럴은 그냥 쿼리 자체를 변형시키는 방식이므로 매우 다이나믹 쿼리를 만들어 낼 수 있습니다. 하지만, 앞서서도 언급했듯이 우린 가급적 인자를 바인딩처리하여 다이나믹하게 하려는데 촛점을 맞추려 합니다.

<구현-3>
 
/* 프로그래밍언어로 다이나믹한 SQL 만들기 */
String[] param = { "쥐잡는소공녀", "천년전사비" };
sql = "SELECT * FROM hijal_member WHERE user_name";
sql += " IN (";
for(int i=0; i<param.length; i++){
  if(i>0) sql+=", ";
  sql += "? ";
}
sql += " )";
... 중략 ...
for(int i=0; i<param.length; i++){
    pstm.setString(i+1, param[i]);
}
... 실행부 생략 ...

이정도만 되면, 파라미터의 갯수에 관계없이 상당히 유동적인 Data Access Module 로서 활용할정도의 수준은 되었습니다.

프로그래밍언어로 바인딩을 처리하는 부분은 여기서 일단락 하기로 하겠습니다..
이제부터는 SQL 쪽에서 바인딩을 유도하는 법을 순차적으로 풀어보겠습니다.

3. DB측에서 바인딩을 유동적으로 처리하기( IN~)
 
IN 구문의 용법은 괄호안에 인자를 배열시키는 방법이 있고 또 SELECT 절의 각 ROW 로 배열시키는 방법이 있습니다.

<SQL-2>
 
SELECT * FROM hijal_member
WHERE user_name IN (
    SELECT '쥐잡는소공녀' FROM dual
    UNION ALL
    SELECT '천년전사비' FROM dual
);

위의 SQL 은 <SQL-1> 과 동일한 결과를 보여줍니다. 괄호안에 일렬로 배치했을때보다 코딩량은 많아졌지만 무엇인가 더 정규화된 패턴이 눈에 띕니다.

만약 비교되어져야할 인자값이 더 많아진다면, SQL 은 다음처럼 수정되어져야할것입니다.

<SQL-3>
 
SELECT * FROM hijal_member
WHERE user_name IN (
    SELECT '쥐잡는소공녀' FROM dual
    UNION ALL
    SELECT '천년전사비' FROM dual
    UNION ALL
    SELECT '아룬' FROM dual
);
하지만 이런 SQL문은 토드나 SQL*Plus 에서 값을 확인하기위한 즉시성 SQL 이지 프로그래밍시 Data Access Module 을 구성하기에는 무엇인가 부족해보입니다.

첫번째로 파라미터가 정적이어야하는데, 파라미터가 갯수가 가변적으로 보입니다.
파라미터가 가변적인 경우에는 이 앞절에서 다루었던 프로그래밍언어에서 다이나믹한게 바인딩을 처리하는게 오히려 더 어울려 보입니다.

SQL 에서 유동적인 처리를 위해서는 구분자를 갖는 문자열로서 단일 파라미터로 받아 처리하는게 현명해 보입니다. 이 글에서 가장 핵심인 부분이 구분자를 갖는 문자열을 row data 로 표현하는 방법입니다. row data 로 표현이 되기만하면 IN ( SELECT .... ) 절안에 집어넣기만 하면 되니깐요.

<SQL-4>
 
SELECT ROWNUM as rnum
      ,param
      ,dlmtr
  FROM ( SELECT '천년전사비|쥐잡는소공녀' AS param
                ,'|' AS  dlmtr
          FROM dual )
CONNECT BY LEVEL <= LENGTH(param)-LENGTH(REPLACE(param,dlmtr))+1;

결과>
-------+--------------------+---------+
  rnum|    param          | dlmtr 
-------+--------------------+---------+
    1  |천년전사비|쥐잡는소공녀|    |
    2  |천년전사비|쥐잡는소공녀|    |
-------+--------------------+---------+


이 SQL 예제의 핵심은 '천년전사비|쥐잡는소공녀' 라는 두개의 비교인자를 갖는 하나의 인자에 대해 인자의 갯수만큼 ROW 갯수를 만들어 주는 기능을 수행 한다는 것 입니다.
REPLACE(...) 는 문자열을 치환할때 쓰는 함수입니다. LENGTH(...)는 글자수를 구하는 함수입니다.

이제 무엇을 해야 할까요? param 의 내용을 rnum 이라는 index 의 번호에 맞는 배열 요소로 접근하고 싶어지죠? 이제 그 방법을 만들어 봅시다. 오라클의 기본 함수들만 가지고 충분히 해 낼수 있습니다.

<SQL-5>
 
SELECT SUBSTR (
              dlmtr||param||dlmtr,
              INSTR(dlmtr||param||dlmtr,dlmtr,1,idx_no)+1 ,
              (INSTR(dlmtr||param||dlmtr,dlmtr,1,idx_no+1)
                - INSTR(dlmtr||param||dlmtr,dlmtr, 1,idx_no))-1
      ) AS result_str
  FROM ( SELECT '천년전사비|쥐잡는소공녀' AS param
                ,'|' AS dlmtr
                ,1 AS idx_no
          FROM DUAL );

결과>
--------------------
  result_str     
--------------------
  천년전사비
--------------------


무언가 상당한 복잡한 쿼리가 탄생했습니다. 이 SQL 의 핵심은 바로 SUBSTR 함수와 INSTR 함수를 조합해서 idx_no 즉 1번째 요소를 뽑아내는게 핵심입니다. 만약 idx_no 를 2 로 했다면, '쥐잡는소공녀' 를 결과로 리턴했을겁니다.
자 이제 <SQL-4> 와 <SQL-5> 를 조합해서 row data 로 각각의 요소를 끄집어 내보도록 하겠습니다.

<SQL-6>
 
SELECT SUBSTR (
              dlmtr||param||dlmtr,
              INSTR(dlmtr||param||dlmtr,dlmtr,1,rnum)+1 ,
              (INSTR(dlmtr||param||dlmtr,dlmtr,1,rnum+1)
                - INSTR(dlmtr||param||dlmtr,dlmtr, 1,rnum))-1
      ) AS user_name
FROM ( SELECT ROWNUM as rnum
            ,param
            ,dlmtr
        FROM ( SELECT '천년전사비|쥐잡는소공녀' AS param
                      ,'|' AS  dlmtr
                  FROM dual )
      CONNECT BY LEVEL <= LENGTH(param)
                          - LENGTH(REPLACE(param,dlmtr))+1
);

결과>
--------------------
  user_name     
--------------------
  천년전사비
  쥐잡는소공녀
--------------------

갈수록 SQL 이 복잡해져가고있지만, 구분자를 가진 파라미터를 row data 로 pivot 처리 하는데 성공하였습니다.

그럼 hijal_member 테이블에서 이를 취합해봅시다.

<SQL-7>
 
SELECT *
  FROM hijal_member
 WHERE user_name IN (
    SELECT SUBSTR (
                  dlmtr||param||dlmtr,
                  INSTR(dlmtr||param||dlmtr,dlmtr,1,rnum)+1 ,
                  (INSTR(dlmtr||param||dlmtr,dlmtr,1,rnum+1)
                    - INSTR(dlmtr||param||dlmtr,dlmtr, 1,rnum))-1
          )
      FROM ( SELECT ROWNUM as rnum
                ,param
                ,dlmtr
            FROM ( SELECT :param AS param
                          ,:dlmtr AS dlmtr
                    FROM dual )
          CONNECT BY LEVEL <= LENGTH(param)
                              - LENGTH(REPLACE(param,dlmtr))+1 )
 );


파라미터와 구분자를 한번씩 바인딩하여 원하는 결과를 얻을수 있습니다.
 
그런데 이 쿼리 또한 매우 복잡해보이고 지저분해 보입니다.
 
오라클 10 부터는  정규식 패턴을 이용하여 보다 간단하게 작업할 수 있습니다.
 
substr 대신 regexp_substr 을 이용하는 방법입니다.
<SQL-8>
 
SELECT *
  FROM hijal_member
 WHERE user_name IN
    ( SELECT REGEXP_SUBSTR (param,'[^' || dlmtr || ']+',1,LEVEL)
        FROM ( SELECT :param AS param
              ,:dlmtr AS dlmtr
                FROM DUAL)
CONNECT BY LEVEL <= LENGTH (param) - LENGTH (REPLACE (param, dlmtr, '')) + 1);