Oracle sqlplus에서 변수 사용하기.

오라클의 SQL 언어를 수행할 수 있는 tool인 sqlplus에서는 변수를 지원한다.

변수는 다들 잘 알고 있듯이 특정 변경되는 값을 변수값으로 입력 받아 이를 해당 변수와 치환해주게 된다. SQLPLUS에서 지원하는 변수는 2가지로 변수값이 지속되지 않는 변수 temporary variable과 그 변수 값이 지속되어 해당 값을 삭제, 재정의 또는 sqlplus를 빠져나갈때 까지 유지하는 defined variable이 있다.

Temporary variable의 경우 SQL구문의 수행 시 해당 변수값을 입력 받아 사용되며 동일한 SQL이 재수행 되더라도 해당 변수값을 다시 묻게 된다.

SQL> SELECT product_id, name, price
  2  FROM products
  3  WHERE product_id = &product_id_var;

Enter value for product_id_var: 2
old   3: WHERE product_id = &product_id_var
new   3: WHERE product_id = 2

PRODUCT_ID NAME                                PRICE
---------- ------------------------------ ----------
         2 Chemistry                              30

SQL> /
Enter value for product_id_var: 3
old   3: WHERE product_id = &product_id_var
new   3: WHERE product_id = 3

PRODUCT_ID NAME                                PRICE
---------- ------------------------------ ----------
         3 Supernova                           25.99

위의 예를 보면 product_id_var 변수의 값이 3으로 치환되고 이에 대한 결과를 보여준다. 이러한 old/ new 값에 대한 output은 VERIFY option을 이용해 출력을 제어할 수 있다.

SQL> SET VERIFY OFF
SQL> /

Enter value for product_id_var: 4

PRODUCT_ID NAME                                PRICE
---------- ------------------------------ ----------
         4 Tank War                            13.95

다시 변수에 대한 old/ new value를 출력하려면 VERIFY option을 on으로 설정한다.

SQL> SET VERIFY ON

또 "SET DEFINE" 명령을 이용해 변수 앞의 ampersand (&)도 바꿔 줄 수 있다. UNIX 상에서 해당 character가 다른 의미로 사용되고 있는 경우를 제외하면 별로 사용할 일은 없을 듯 하지만..

SQL> SET DEFINE '#'
SQL> SELECT product_id, name, price
  2  FROM products
  3  WHERE product_id = #product_id_var;

Enter value for product_id_var: 5
old   3: WHERE product_id = #product_id_var
new   3: WHERE product_id = 5

PRODUCT_ID NAME                                PRICE
---------- ------------------------------ ----------
         5 Z Files                             49.99

다시 원래의 ampersand (&)로 바꾸려면 다음과 같이 수행해 준다.

SQL> SET DEFINE '&'

가끔 동일한 변수를 같은 SQL문에서 사용하게 되는데 이때 동일한 변수라도 사용한 개수만큼 다시 값을 묻게 된다. 이러한 현상을 막기 위해서는 ampersand를 두개로 지정해 준다.  (&&)

SQL> SELECT name, &col_var
  2  FROM &table_var
  3  WHERE &col_var = &col_val;

Enter value for col_var: product_type_id
old   1: SELECT name, &col_var
new   1: SELECT name, product_type_id

Enter value for table_var: products
old   2: FROM &table_var
new   2: FROM products

Enter value for col_var: product_type_id
Enter value for col_val: 1
old   3: WHERE &col_var = &col_val
new   3: WHERE product_type_id = 1

NAME                           PRODUCT_TYPE_ID
------------------------------ ---------------
Modern Science                               1
Chemistry                                    1

You can avoid having to repeatedly enter a variable by using &&. For example:

SELECT name, &&col_var
FROM &table_name
WHERE &&col_var = &col_val;

Defined Variable은 선언된 변수를 여러번 사용이 가능하다. 이 값은 재정의 되거나, 변수값이 삭제되거나, SQLPLUS를 빠져나가기 전까지 사용가능하다. Defined variable은 "DEFINE" 명령을 통해 변수값이 설정, 조회하며 "UNDEFINE"명령에 의해 변수값이 삭제된다. 또 "ACCEPT" 명령을 통해 해당 변수값을 유저로 부터 받아들일 수 있다.

SQL> DEFINE product_id_var = 7
SQL> DEFINE product_id_var

DEFINE PRODUCT_ID_VAR          = "7" (CHAR)

SQL> SELECT product_id, name, price
      2  FROM products
      3  WHERE product_id = &product_id_var;

old   3: WHERE product_id = &product_id_var
new   3: WHERE product_id = 7

ACCEPT 명령은 해당 변수의 type과 입력 받을 format, prompt 형태 등의 다양한 지정이 가능하다. HIDE option의 경우 9i 이전엔 asterisk characters (*)로 보여주지만 10g 부터는 아무런 문자를 출력하지 않는다.

ACCEPT variable_name [type] [FORMAT format] [PROMPT prompt] [HIDE]

SQL> ACCEPT customer_id_var NUMBER FORMAT 99 PROMPT 'Customer id: '
Customer id: 5

SQL> ACCEPT date_var DATE FORMAT 'DD-MON-YYYY' PROMPT 'Date: '
Date: 12-DEC-2006

SQL> ACCEPT password_var CHAR PROMPT 'Password: ' HIDE
Password: *******

현재 설정되어 있는 변수값을 clear 하기 위해서는 UNDEFINE 명령이 사용된다. 물론 재정의나 SQLPLUS를 logout 하면 해당 값은 clear 되지만 다른 script를 계속 수행한다면 예상치 못한 상황을 만나지 않기 위해 깨끗이 정리할 필요가 있다.

SQL> UNDEFINE customer_id_var
SQL> UNDEFINE date_var
SQL> UNDEFINE password_var