[Oracle] 오라클 '&' 문자 치환변수 사용하기 :: 마이자몽

데이터를 확인하기 위해 쿼리를 작성하고 확인하고... 같은 쿼리내에서 특정 값만 변경해서 데이터를 확인하고 싶을때 어떻게 하시나요?

예를들어 특정 부서에 어떤 직원들이 있는지 확인하고 싶은데... 영업부서를 확인하고 인사부서의 직원들을 확인하기 위해 WHERE절의 Value값을 계속 변경해가면서 확인하시나요? 한 두개면 몰라도... 부서가 20개씩 있고 이러면 일일히 쿼리내용 변경해가면서 확인하기 힘들잖아요? 그럴때 치환변수 '&'를 사용하시면 좀 더 편하게 작업을 할 수 있습니다.

 

치환변수에 대해서 알아보기 위해 Oracle의 scott 계정안에 있는 EMP 테이블을 갖고 실습해 보겠습니다.

 

 

오라클 치환변수

오라클 '&' 문자

SELECT * FROM EMP WHERE SAL >= 1000;

EMP 테이블에서 월급이 1000이상인 데이터를 출력해봤습니다. 그런데 2000이상의 데이터도 보고 싶고 3000이상만의 데이터도 보고싶고... 위 쿼리의 조건값을 변수로 사용해서 계속 변경해가면서 보고싶다는 생각이 드네요. 이럴때 '&' 문자를 이용해서 치환변수를 사용해줍니다.

 

 

숫자형 치환변수

SELECT * FROM EMP WHERE SAL >= &SALARY;

'&' 문자를 사용해서 치환변수명을 지정해주고 쿼리 실행을 하면 윈도우에서는 입력창이 띄워집니다.

해당 입력란에 원하는 값을 넣고 확인을 선택해주면 해당 치환변수의 값으로 결과를 뿌려줍니다.

 

문자형 치환변수

SELECT * FROM EMP WHERE JOB = &JOB_NAME;

문자형으로된 데이터도 치환변수를 사용해서 데이터를 보고 싶었었는데... 부적합한 식별자라는 오류가 발생합니다.

이는 치환변수를 문자형이 아닌 칼럼명으로 인식했기 때문입니다.

 

SELECT * FROM EMP WHERE JOB = '&JOB_NAME';

작은 따옴표 ' 를 사용해서 둘러주면 해당 치환변수를 문자형으로 인식합니다.

 

치환변수 칼럼으로 사용하기

문자형으로 치환변수를 사용하려다가 에러가 발생했을때 이는 문자형이 아닌 칼럼명으로 인식했기때문에 오류가 발생한 것 입니다. 그럼 치환변수를 사용을 칼럼을 유동적으로 바꿔 줄 수 있는 변수로 사용 가능할까요?

 

SELECT EMPNO, ENAME, &COL_NAME FROM EMP;

COL_NAME이라는 치환변수르 칼럼명으로 사용해봤습니다. SAL 칼럼의 정보를 보기 위해 입력창에 SAL을 입력했더니 이상없이 해당 열의 정보를 출력할 수 있었습니다.

 

칼럼을 치환변수에 사용할수 있다는 내용을 알게되고나서 딱 한가지가 머리속으로 떠올랐습니다.

그럼 복잡하고 긴 쿼리에서 칼럼에 대한 정보를 치환변수에 넣어서 변경해가면서 보면 진짜 편하겠다는 생각....

 

SELECT EMPNO, ENAME, &COL_NAME FROM EMP ORDER BY &COL_NAME;

그래서 해당 치환변수를 하나 늘려서 작동이 가능한지.. 확인해봤는데... 지정한 치환변수마다 입력창이 띄워지면서 모두 입력했어야합니다.

이렇게만 되더라도 업무가 많이 편해지긴 했는데... 일일히 또 입력해주는 귀찮은 업무가 또 생겨버립니다. 그래서 치환변수를 한번 선언하면 해당 세션에서 계속 저장하는 방법이 있습니다.

 

오라클 '&&'  문자

SELECT EMPNO, ENAME, &&COL_NAME FROM EMP ORDER BY &COL_NAME;

가장 처음에 오는 치환변수에 '&' 문자를 2개 사용하여 '&&' 문자로 만들어줘서 사용하면 한번의 입력창에서 입력하여 해당 세션에서 계속 사용할 수 있습니다.

 

여러 조건으로 데이터를 확인하는 업무를 볼때 유용하게 사용할 수 있습니다. 그런데, 문득 생각해보니... 해당 세션에서 치환변수가 계속 저장되면... 다시 쿼리를 실행해서 다른 행의 데이터를 보고 싶거나 다른 조건의 데이터를 볼려면 치환변수명을 계속 바꿔줘야하나??? 아니면 세션을 계속 끊었다가 접속해야하나?? 뭔가 불편할 것같은 생각이 들어 찾아봤는데 이런 문제들은 2가지 방법으로 해결할 수 있었습니다.

 

치환변수 선언  DEFINE

DEFINE COL_NAME = "SAL";
SELECT EMPNO, ENAME, &COL_NAME FROM EMP ORDER BY &COL_NAME;

DEFINE 명령을 사용하여 치환변수를 저장할 수 있습니다.

 

치환변수 해제 UNDEFINE

UNDEFINE COL_NAME = "SAL";

DEFINE한 치환변수를 해제하기 위해서 UNDEFINE 명령을 사용하고 다시 새롭게 같은 변수명으로 정의할 수 있습니다.