5일차 Interaction: 치환변수

* &

* &&

SQL> l

SQL> 1

SQL> c/&/&&

SQL> r

col의 값을 입력하십시오: ename

* define/undefine ---->SQL*Plus에서 사용할 변수를 선언하거나, 이미 선언된 변수와 그 변수값을 나열한다.

[출처] DEFINE 명령어|작성자 씽아횽

SQL> define col

DEFINE COL             = "ename" (CHAR)

SQL> /

SQL> undefine col

SQL> /

col의 값을 입력하십시오: sal

SQL> define col = job

SQL> /

SQL> def

SQL> def col

* acc[ept]

SQL> ed sv002.sql

accept col prompt '세번째 컬럼 이름을 넣으세요: '

select empno, ename, &col

from emp

order by &col desc;

SQL> @sv002

세번째 컬럼 이름을 넣으세요: sal

SQL> l

* 문자

SQL> select empno, ename, sal

     from emp

     where ename = &col_name;

col_name의 값을 입력하십시오: KING

==> 에러: ORA-00904: "KING": 부적합한 식별자

SQL> /

col_name의 값을 입력하십시오: 'KING'

구   3:      where ename = &col_name

신   3:      where ename = 'KING'

↓ ↓ ↓ 

SQL> l

SQL> c/&col_name/'&col_name'

* new_value

SQL> ed sv003 --> 자동적으로 sql 확장자가 붙습니다

col avg_sal new_value nv_avg_sal

select avg(sal) as avg_sal

from emp

where deptno = 10;

select *

from emp

where sal > &nv_avg_sal;

SQL> @ sv003

  AVG_SAL

----------

2916.66667

구   3: where sal > &nv_avg_sal

신   3: where sal > 2916.66667

    EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO

---------- ---------- --------- ---------- -------- ---------- ---------- ----------

     7566 JONES      MANAGER         7839 81/04/02       2975                    20

     7788 SCOTT      ANALYST         7566 82/12/09       3000                    20

     7839 KING       PRESIDENT            81/11/17       5000                    10

     7902 FORD       ANALYST         7566 81/12/03       3000                    20

SQL> def nv_avg_sal

DEFINE NV_AVG_SAL      = 2916.66667 (NUMBER)

* &n: &1, &2, ..., &9

SQL> ed sv004

select empno, &1

from emp

where sal > &2;

SQL> @ sv004 ename 2000

==============================================================================================================

치환변수(&, &&)를 사용한 새로운 행 삽입

치환변수는 사용자로부터 입력받은 값을 SQL 문장간에 변수 값으로 전달하기 위해 사용한다.
치환변수는 '&(ampersand)' 기호를 사용한다.
치환변수는 입력받을 컬럼과 함께 '&컬럼명'처럼 사용된다.

치환변수의 종류

single ampersand (&)	SQL 문장이 실행될 때마다 새로운 값을 요구함
double ampersand (&&)	처음 SQL문이 실핼될 때 입력된 치환변수 값이 다음 실행시에도 계속 사용됨
ACCEPT	메시지와 함께 치환변수를 입력하게 되며, single ampersand(&)와 같은 기능

치환변수를 사용할 수 있는 절

• WHERE 절

• ORDER BY 절

• 컬럼 표현식

• 테이블 이름

• 전체 SELECT 문

single ampersand(&)

• SQL에서 각각의 변수를 인식하기 위해 '&'를 사용한다.
• 숫자에는 인용부호를 사용하지 않지만, 문자와 날짜에 대해서는 단중 인용부호를 사용한다.
(예: &deptno, '&ename')

SQL> alter session set nls_language='KOREAN';
 
세션이 변경되었습니다.

deptno=&dept_no는 입력되는 값을 받아서 where 절에서 deptno=입력값으로 제한하여 select 함 
SQL> select empno,ename,job,hiredate,deptno from emp
  2  where deptno=&dept_no;
dept_no의 값을 입력하십시오: 10
구   2: where deptno=&dept_no
신   2: where deptno=10
 
     EMPNO ENAME      JOB       HIREDATE           DEPTNO
---------- ---------- --------- -------------- ----------
      7782 CLARK      MANAGER   09-6월 -81             10
      7839 KING       PRESIDENT 17-11월-81             10
      7934 MILLER     CLERK     23-1월 -82             10
 
대소문자 구분없이 입력받기 위하여 UPPER 함수를 사용하였음 
SQL> select * from emp
  2  where UPPER(ename)=UPPER('&name');
name의 값을 입력하십시오: SCOTT
구   2: where UPPER(ename)=UPPER('&name')
신   2: where UPPER(ename)=UPPER('SCOTT')
 
     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
      7788 SCOTT      ANALYST         7566 19-4월 -87           3000            20
 
 
SQL> 

Double ampersand(&&)

매번 사용자로부터 입력받지 않고 입력된 변수의 값을 사용하고자 할 경우에 쓰인다.
single ampersand와 달리 사용자는 한 번만 데이터 값을 입력하면 된다.

SQL> select *
  2  from test
  3  where name='&&name';

name의 값을 입력하십시오: 임효선
구   3: where name='&&name'
신   3: where name='임효선'
 
NAME       HIREDATE
---------- ----------------------------------------
임효선     12-1월 -06
 
SQL> / ☜ 다시 '임효선'을 입력하지 않아도 결과가 출력됨
구   3: where name='&&name'
신   3: where name='임효선'
 
NAME       HIREDATE
---------- ----------------------------------------
임효선     12-1월 -06
 
SQL> / ☜ 다시 '임효선'을 입력하지 않아도 결과가 출력됨
구   3: where name='&&name'
신   3: where name='임효선'
 
NAME       HIREDATE
---------- ----------------------------------------
임효선     12-1월 -06
 
SQL> 

SQL> select empno,ename,job,&&column_name
  2  from emp
  3  order by &order_column;
column_name의 값을 입력하십시오: hiredate   ☜ 처음에만 입력함
구   1: select empno,ename,job,&&column_name
신   1: select empno,ename,job,hiredate
order_column의 값을 입력하십시오: empno
구   3: order by &order_column
신   3: order by empno  
 
     EMPNO ENAME      JOB       HIREDATE
---------- ---------- --------- ----------------------------------------
      7369 SMITH      CLERK     17-12월-80
      7499 ALLEN      SALESMAN  20-2월 -81
      7521 WARD       SALESMAN  22-2월 -81
      7566 JONES      MANAGER   02-4월 -81
      7654 MARTIN     SALESMAN  28-9월 -81
      7698 BLAKE      MANAGER   01-5월 -81
      7782 CLARK      MANAGER   09-6월 -81
      7788 SCOTT      ANALYST   19-4월 -87
      7839 KING       PRESIDENT 17-11월-81
      7844 TURNER     SALESMAN  08-9월 -81
      7876 ADAMS      CLERK     23-5월 -87
      7900 JAMES      CLERK     03-12월-81
      7902 FORD       ANALYST   03-12월-81
      7934 MILLER     CLERK     23-1월 -82
 
14 개의 행이 선택되었습니다.
 
SQL> /
구   1: select empno,ename,job,&&column_name  ☜ 이후 부터는 column_name은 입력하지 않아도 됨
신   1: select empno,ename,job,hiredate
order_column의 값을 입력하십시오: ename
구   3: order by &order_column
신   3: order by ename

     EMPNO ENAME      JOB       HIREDATE
---------- ---------- --------- ----------------------------------------
      7876 ADAMS      CLERK     23-5월 -87
      7499 ALLEN      SALESMAN  20-2월 -81
      7698 BLAKE      MANAGER   01-5월 -81
      7782 CLARK      MANAGER   09-6월 -81
      7902 FORD       ANALYST   03-12월-81
      7900 JAMES      CLERK     03-12월-81
      7566 JONES      MANAGER   02-4월 -81
      7839 KING       PRESIDENT 17-11월-81
      7654 MARTIN     SALESMAN  28-9월 -81
      7934 MILLER     CLERK     23-1월 -82
      7788 SCOTT      ANALYST   19-4월 -87
      7369 SMITH      CLERK     17-12월-80
      7844 TURNER     SALESMAN  08-9월 -81
      7521 WARD       SALESMAN  22-2월 -81
 
14 개의 행이 선택되었습니다.
 
SQL>

double ampersand를 사용하여 한번 입력된 값은 고정적으로 사용된다. 이때 고정된 값을 해제하려면, UNDEFINE 컬럼명처럼 하여 해제한다. (예를 들어, &&column_name으로 정의되었다면, undefine column_name처럼 입력하여 해제)
어떤 값이 define 되었는지 확인하기 위해서는 define 명령을 사용하면 define된 값을 확인할 수 있다.

SQL> define
DEFINE _DATE           = "14-12월-05" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "orcl" (CHAR)
DEFINE _USER           = "SCOTT" (CHAR)
DEFINE _PRIVILEGE      = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1001000300" (CHAR)
DEFINE _EDITOR         = "vi" (CHAR)
DEFINE _O_VERSION      = "Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Production With the Partitioning, OLAP and Data Mining options" (CHAR)
DEFINE _O_RELEASE      = "1001000300" (CHAR)
DEFINE COLUMN_NAME     = "hiredate" (CHAR)

SQL> undefine column_name

SQL> define
DEFINE _DATE           = "14-12월-05" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "orcl" (CHAR)
DEFINE _USER           = "SCOTT" (CHAR)
DEFINE _PRIVILEGE      = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1001000300" (CHAR)
DEFINE _EDITOR         = "vi" (CHAR)
DEFINE _O_VERSION      = "Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Production With the Partitioning, OLAP and Data Mining options" (CHAR)
DEFINE _O_RELEASE      = "1001000300" (CHAR)
DEFINE HIREDATE        = "" (CHAR)
SQL>

---

치환변수를 사용한 새로운 행 삽입
치환변수를 사용하여 새로운 행을 삽입할 수 있다.
• 문자 변수인 경우 &,&&와 함께 변수명을 단중 인용부호(' ')를 사용해야 한다.
• 숫자 변수인 경우에는 &,&&와 함께 변수명만 쓰며, 단중 인용부호(' ')를 사용하지 않아야 한다.

SQL> insert into dept values(
  2   &deptno,'&dname','&loc');

deptno의 값을 입력하십시오: 50
dname의 값을 입력하십시오: MANAGER
loc의 값을 입력하십시오: COREA

구   2:  &deptno,'&dname','&loc')
신   2:  50,'MANAGER','COREA')
 
1 개의 행이 만들어졌습니다.
 
SQL> select * from dept;
 
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
        50 MANAGER        COREA
 
SQL>

SQL> desc test;
 이름                                      널?      유형
 ----------------------------------------- -------- ----------------------------
 NAME                                               VARCHAR2(10)
 HIREDATE                                           DATE

SQL> insert into test values(
  2  '&name','&hiredate');
name의 값을 입력하십시오: 좋은 친구
hiredate의 값을 입력하십시오: 5-3월-06
구   2: '&name','&hiredate')
신   2: '좋은 친구','5-3월-06')
 
1 개의 행이 만들어졌습니다.
 
SQL> /
name의 값을 입력하십시오: 길동무
hiredate의 값을 입력하십시오: 7-4월-06
구   2: '&name','&hiredate')
신   2: '길동무','7-4월-06')
 
1 개의 행이 만들어졌습니다.
 
SQL> select * from test;
 
NAME       HIREDATE
---------- ---------------
임효선     12-1월 -06
좋은 친구  05-3월 -06
길동무     07-4월 -06
 
SQL> 

---

치환변수를 사용한 새로운 행 삽입

치환변수를 사용하여 새로운 행을 삽입할 수 있다.
• 문자 변수인 경우 &,&&와 함께 변수명을 단중 인용부호(‘ ’)를 사용해야 한다.
• 숫자 변수인 경우에는 &,&&와 함께 변수명만 쓰며, 단중 인용부호(‘ ’)를 사용하지 않아야 한다 .
• &&로 변수를 지정하는 경우, define으로 변수를 정의한 것 과 같으므로 undefine으로 해지하여야 한다.

SQL> insert into test values(&id, '&name','&email');
id의 값을 입력하십시오: 4444
name의 값을 입력하십시오: 홍길동
email의 값을 입력하십시오: nugu@arirang.com
구   2: &id, '&name','&email')
신   2: 4444, '홍길동','nugu@arirang.com')

1 개의 행이 만들어졌습니다.

SQL> select * from test;

        ID NAME                 EMAIL
---------- -------------------- -------------------------
      4444 홍길동               nugu@arirang.com


SQL> insert into test values(&&id,‘&&name’,‘&&email’);
id의 값을 입력하십시오: 5555
name의 값을 입력하십시오: nugu2
email의 값을 입력하십시오: nugu2@arirang.co.kr
구   1: insert into test values(&&id,‘&&name’,‘&&email’)
신   1: insert into test values(5555,‘nugu2’,‘nugu2@arirang.co.kr’)

1 개의 행이 만들어졌습니다.

SQL> select * from test;

        ID NAME                 EMAIL
---------- -------------------- -------------------------
      4444 홍길동               nugu@arirang.com
      5555 nugu2                nugu2@arirang.co.kr

SQL> define
DEFINE _DATE           = "2009-10-21" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "orcl" (CHAR)
DEFINE _USER           = "NUGU" (CHAR)
DEFINE _PRIVILEGE      = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1101000600" (CHAR)
DEFINE _EDITOR         = "vi" (CHAR)
DEFINE _O_VERSION      = "Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Product
ion
With the Partitioning, OLAP, Data Mining and Real Application Testing options" (CHAR)
DEFINE _O_RELEASE      = "1101000600" (CHAR)

SQL> insert into test values(&&id, '&&name','&&email');
id의 값을 입력하십시오: 3333
name의 값을 입력하십시오: 좋은이
email의 값을 입력하십시오: aaa@arirang.com
구   1: insert into test values(&&id, '&&name','&&email')
신   1: insert into test values(3333, '좋은이','aaa@arirang.com')

1 개의 행이 만들어졌습니다.

SQL> define
DEFINE _DATE           = "2009-10-21" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "orcl" (CHAR)
DEFINE _USER           = "NUGU" (CHAR)
DEFINE _PRIVILEGE      = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1101000600" (CHAR)
DEFINE _EDITOR         = "vi" (CHAR)
DEFINE _O_VERSION      = "Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Product
ion
With the Partitioning, OLAP, Data Mining and Real Application Testing options" (CHAR)
DEFINE _O_RELEASE      = "1101000600" (CHAR)
DEFINE ID              = "3333" (CHAR)
DEFINE NAME            = "좋은이" (CHAR)
DEFINE EMAIL           = "aaa@arirang.com" (CHAR)
SQL>

출처:http://radiocom.kunsan.ac.kr/lecture/oracle/sql/ampersand_insert.html