치환변수의 사용법을 설명하는 간단한 예제

-- Note: 다음과 같이 치환변수 &SID 를 선언하고 그 값을 12로 지정함

SQL> define sid = 12

SQL> select sid, event
  2  from   v$session
  3  where  sid = &sid;
  
구   3: where  sid = &sid
신   3: where  sid = 12

       SID EVENT
---------- ----------------------------------------------------------------
        12 SQL*Net message from client

SELECT문의 컬럼 리스트를 치환 변수로 정의하는 예제

SQL> define columns = "sid, event, p1";

SQL> select &columns
  2  from   v$session
  3  where  sid = &sid;
  
구   1: select &columns
신   1: select sid, event, p1
구   3: where  sid = &sid
신   3: where  sid = 12

       SID EVENT                                                            P1
---------- ---------------------------------------------------------------- ----------
        12 SQL*Net message from client                                      1111838976

파라미터의 사용법을 설명하는 간단한 예제

/**
 -- STEP1. 아래와 같이 SESSION.SQL 파일을 정의함
 --        1) 첫번째 파라미터(&1)의 값을 다시 치환변수 &SID 저장함
 --        2) 그리고 그 값을 이용해서 V$SESSION 뷰를 검색함
 **/

SQL> --ed session.sql
SQL> /*
SQL> define sid = &1
SQL>
SQL> select sid, event
SQL> from   v$session
SQL> where  sid = &sid;
SQL> */



-- STEP2. SESSION.SQL 파일을 호출하면서(@ 명령), 12라는 값을 첫번째 파라미터로 사용함

SQL> @session 12



-- STEP3. 아래와 같이 12의 값이 사용되어 원하는 결과를 얻게됨

SQL> define sid = &1
SQL>
SQL> select sid, event
  2  from   v$session
  3  where  sid = &sid;
구   3: where  sid = &sid
신   3: where  sid = 12

       SID EVENT
---------- ----------------------------------------------------------------
        12 SQL*Net message from client

ACCEPT 명령을 설명하는 간단한 예제

/**
 -- STEP1. 아래와 같이 SESSION2.SQL 파일을 정의함
 --        1) ACCEPT SID2 DEFAULT &SID 명령에 의해 치환변수 &SID에 의해 지정된 디폴트 값을 사용할 수 있도록 함
 **/

SQL> /*
SQL> define sid = &1
SQL> accept sid2 number default &sid prompt 'sid to monitor[&sid]: '
SQL>
SQL> select sid, event
SQL> from   v$session
SQL> where  sid = &sid2;
SQL> */



-- STEP2. 그리고 SESSION2.SQL 파일을 호출하면서 12라는 값을 첫번째 파라미터로 사용함

SQL> @session2 12



-- STEP3. 그러면 다음과 같이 디폴트 값이 12로 지정된 상태로 ACCEPT 명령이 수행됨

SQL> define sid = &1
SQL> accept sid2 number default &sid prompt 'sid to monitor[&sid]: '
sid to monitor[12]:
SQL> select sid, event
  2  from   v$session
  3  where  sid = &sid2;
구   3: where  sid = &sid2
신   3: where  sid =         12

       SID EVENT
---------- ----------------------------------------------------------------
        12 SQL*Net message from client

COLUMN NEW_VALUE 명령을 설명하는 간단한 예제

-- STEP1. 아래와 같이 COLUMN 명령을 실행하면 SID라는 컬럼의 결과 값을 SID 라는 치환변수에 저장하라는 의미가됨

SQL> -- "column new_value" command
SQL> col sid new_value sid



-- STEP2. 아래와 같은 쿼리를 수행하면 치환변수 &SID에 135의 값이 저장됨

SQL> select sid from v$session where client_info = 'session1';

       SID
----------
        12



-- STEP3. 저장된 치환변수는 아래와 같이 사용하면 됨
SQL> select sid, event
  2  from   v$session
  3  where  sid = &sid;
구   3: where  sid = &sid
신   3: where  sid =         12

       SID EVENT
---------- ----------------------------------------------------------------
        12 SQL*Net message from client

치환 변수를 이용하여 조건부 쿼리를 구현하는 예제

-- STEP1. 아래와 같이 COLUMN .. NEW_VALUE 명령을 이용해서 치환변수 _IS_11G 와 _IS_10G를 선언함

SQL> -- conditional logic using substitution variable
SQL> col is_11g new_value _IS_11G format a10
SQL> col is_10g new_value _IS_10G format a10



-- STEP2. 그리고 V$VERSION 뷰에서 버전 값을 읽어서 IS_11G 컬럼과 IS_10G 컬럼이 11g이상일 경우 에는 " "과 "--" , 10g 이하일 경우에는 각각 "--" , " "의 값을 지니도록함

SQL> with v as (
  2    select to_number(substr(banner, instr(banner, 'Release ')+8
  3          ,instr(banner, '.') - instr(banner,'Release ')-8)) as version
  4    from   v$version
  5    where  rownum = 1
  6  ) select case when version >= 11 then '' else '--' end as is_11g
  7          ,case when version <= 10 then '' else '--' end as is_10g
  8    from   v;

IS_11G     IS_10G
---------- ----------
           --


/**
 -- STEP3. "--" 값은 SQL*Plus에서는 주석을 나타내는 문자임
 --        따라서 10g 이하일 경우에는 치환변수 _IS_11G 의 값이 "--"이 되어 주석으로 처리되게 할수 있으며, 이 원리를 이용해서 다음과 같이 쿼리를 작성함
 **/

SQL> select sid, event, sql_id &_IS_11G , (sysdate - sql_exec_start)*24*60*60 as elapsed
  2  from   v$session
  3  where  sid = &sid;
구   1: select sid, event, sql_id &_IS_11G , (sysdate - sql_exec_start)*24*60*60 as elapsed
신   1: select sid, event, sql_id  , (sysdate - sql_exec_start)*24*60*60 as elapsed
구   3: where  sid = &sid
신   3: where  sid =         12

       SID EVENT                                                              SQL_ID           ELAPSED
---------- ---------------------------------------------------------------- ------------- ----------
        12 SQL*Net message from client                                      9tz4qu4rj9rdp



-- STEP4. 즉 11g 이상일 경우에만 SQL_EXEC_START 컬럼의 값을 사용하게끔 조건부 쿼리를 구현한것임