프로그램에서 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 문을 보도록 하겠습니다. 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; i0) sql+=", "; sql += "? "; } sql += " )"; ... 중략 ... for(int i=0; i SELECT * FROM hijal_member WHERE user_name IN ( SELECT '쥐잡는소공녀' FROM dual UNION ALL SELECT '천년전사비' FROM dual ); 위의 SQL 은 과 동일한 결과를 보여줍니다. 괄호안에 일렬로 배치했을때보다 코딩량은 많아졌지만 무엇인가 더 정규화된 패턴이 눈에 띕니다. 만약 비교되어져야할 인자값이 더 많아진다면, SQL 은 다음처럼 수정되어져야할것입니다. 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 .... ) 절안에 집어넣기만 하면 되니깐요. 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 의 번호에 맞는 배열 요소로 접근하고 싶어지죠? 이제 그 방법을 만들어 봅시다. 오라클의 기본 함수들만 가지고 충분히 해 낼수 있습니다. 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 로 했다면, '쥐잡는소공녀' 를 결과로 리턴했을겁니다. 자 이제 와 를 조합해서 row data 로 각각의 요소를 끄집어 내보도록 하겠습니다. 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 테이블에서 이를 취합해봅시다. 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 을 이용하는 방법입니다. 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);