치환변수란?
SQL*Plus 에서는 치환변수라는 기능으로 WHERE구의 조건값을 동적으로 지정할 수 있습니다. 조건값 만이 다른 유사한 SQL을 반복해서 사용할 때 편리합니다.
&치환변수명
&&치환변수명&기호가 1개인 치환변수에 대입된 값은 사용 후에 파기됩니다.
& 기호가 2개인 치환변수는 대입된 값을 유지합니다.
이 글의 예시는 아래 글의 초기 데이터를 사용했습니다.
&치환변수 예시
SQL 실행 시에 프롬프트가 표시되어 치환변수에 값을 대입합니다. emp테이블에서 지정한 값의 deptno에 해당하는 데이터를 출력하는 SQL문입니다.
SELECT empno, ename, job FROM emp
WHERE deptno = #SELECT문을 실행시키면 아래와 같이 num이라는 치환변수의 값을 입력하게 됩니다. 10을 입력하자, deptno가 10인 데이터가 출력됩니다.
SQL> SELECT empno, ename, job FROM emp
2 WHERE deptno = &deptnum;
Enter value for deptnum: 10
old 2: WHERE deptno = &num
new 2: WHERE deptno = 10
EMPNO ENAME JOB
---------- ---------- ---------
7782 CLARK MANAGER
7839 KING PRESIDENT
7934 MILLER CLERK치환변수는 WHERE구 뿐만이 아니라 SQL 문의 다양한 구에서 사용할 수 있습니다.
아래의 SQL문은 SELECT구, FROM구, WHERE구, ORDER BY구 네 가지의 치환변수를 사용하는 예시입니다.
SELECT empno, ename, &colname
FROM &tablename
WHERE deptno = &deptnum
ORDER BY &colname;컬럼명, 테이블명, 컬럼 deptno의 값 등을 입력 받으며, 입력받아 완성된 SQL문의 결과가 출력되는 것을 확인할 수 있습니다.
SQL> SELECT empno, ename, &colname
2 FROM &tablename
3 WHERE deptno = &deptnum
4 ORDER BY &colname;
Enter value for colname: job
old 1: SELECT empno, ename, &colname
new 1: SELECT empno, ename, job
Enter value for tablename: emp
old 2: FROM &tablename
new 2: FROM emp
Enter value for deptnum: 20
old 3: WHERE deptno = &deptnum
new 3: WHERE deptno = 20
Enter value for colname: ename
old 4: ORDER BY &colname
new 4: ORDER BY ename
EMPNO ENAME JOB
---------- ---------- ---------
7902 FORD ANALYST
7566 JONES MANAGER
7369 SMITH CLERK
&&치환변수 예시
SQL 실행 시에 프롬프트가 표시되어 치환변수에 값을 대입한 값이 유지되어, 같은 변수에는 값이 자동적으로 셋팅됩니다.
값은 SQL*Plus를 종료할 때까지, 혹은 UNDEFINE에 의해 명시적으로 값을 해방할 때까지 유지됩니다.
SELECT empno, ename, &&colname
FROM &&tablename
WHERE deptno = &deptnum
ORDER BY &&colname;SELECT구의 ‘&&’으로 ”로 지정한 값이 ORDER BY구의 ‘&’ 에 자동적으로 격납되기 때문에, 입력이 필요 없는 것을 알 수 있습니다.
이 SELECT문을 재실행하면 ‘&’의 값은 유지되기 때문에 입력을 요구하지 않습니다. &&치환변수와 &치환변수를 동시에 사용한 SQL문을 실행시켜보겠습니다.
SQL> SELECT empno, ename, &&colname
2 FROM &&tablename
3 WHERE deptno = &deptnum
4 ORDER BY &&colname;
Enter value for colname: deptno
old 1: SELECT empno, ename, &&colname
new 1: SELECT empno, ename, deptno
Enter value for tablename: emp
old 2: FROM &&tablename
new 2: FROM emp
Enter value for deptnum: 30
old 3: WHERE deptno = &deptnum
new 3: WHERE deptno = 30
old 4: ORDER BY &&colname
new 4: ORDER BY deptno
EMPNO ENAME DEPTNO
---------- ---------- ----------
7499 ALLEN 30
7521 WARD 30
7654 MARTIN 30
7698 BLAKE 30
7844 TURNER 30
7900 JAMES 30
6 rows selected.
SQL> SELECT empno, ename, &&colname
2 FROM &&tablename
3 WHERE deptno = &deptnum
4 ORDER BY &&colname;
old 1: SELECT empno, ename, &&colname
new 1: SELECT empno, ename, deptno
old 2: FROM &&tablename
new 2: FROM emp
Enter value for deptnum: 20
old 3: WHERE deptno = &deptnum
new 3: WHERE deptno = 20
old 4: ORDER BY &&colname
new 4: ORDER BY deptno
EMPNO ENAME DEPTNO
---------- ---------- ----------
7369 SMITH 20
7566 JONES 20
7902 FORD 20같은 SQL문을 2회 실행시킨 결과, 첫번째에는 &&colname, &&tablename, &deptnum의 값을 모두 입력 받았지만 두번째에는 &deptnum의 값만을 입력 받으며 &&colname, &&tablename의 값은 첫번째 실행했을 때 입력 받은 값을 유지하고 있는 것을 확인 할 수 있습니다.
비교하는 값이 문자, 날짜일 경우에는 치환변수를 싱글쿼테이션으로 감싸주어야 합니다.
