Oracle 치환 변수

스폰서 링크
Oracle DB
스폰서 링크

치환변수란?

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의 값은 첫번째 실행했을 때 입력 받은 값을 유지하고 있는 것을 확인 할 수 있습니다.

비교하는 값이 문자, 날짜일 경우에는 치환변수를 싱글쿼테이션으로 감싸주어야 합니다.

제목과 URL을 복사했습니다