SQL*Plus 스크립팅

-- 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

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

/**
 -- 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

-- 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 컬럼의 값을 사용하게끔 조건부 쿼리를 구현한것임 

-- STEP1. 다음과 같이 MAKE_TEMP.SQL 파일을 작성함
--        1) SPOOL 결과를 완벽한 형태의 SQL*Plus 스크립트로 사용할수 있도록 SET명령을 사용해서 TERMOUT, HEADING, VERIFY, FEEDBACK, TIMING, SCAN 등의 불필요한 옵션을 모두 OFF시킴
--        2) 그리고 SPOOL 명령을 이용해서 출력결과를 TEMP.SQL 파일에 기록함
--        3) DBMS_DB_VERSION 패키지를 이용해서 11g인지 10g 인지를 구분함

set echo off
set termout off
set pagesize 0
set heading off
set verify off
set feedback off
set serveroutput on
set timing off
set scan off

spool temp.sql

begin
      dbms_output.put_line('select sid, event, sql_id ');
      if dbms_db_version.version >= 11 then
         dbms_output.put_line(', (sysdate - sql_exec_start)*24*60*60 as elapsed ');
      end if;
      dbms_output.put_line('from v$session ');
      dbms_output.put_line('where sid = &1; ');
end;
/

spool off

set echo on
set termout on
set pagesize 100
set heading on
set verify on
set feedback on
set serveroutput off
set timing on
set scan on



-- STEP2. 이제 다음과 같이 MAKE_TEMP.SQL 파일이 실행함

SQL> @make_temp



-- STEP3. 성공적으로 실행이 끝나면 다음과 같은 내용을 가진 TEMP.SQL 파일이 생성될 것임

select sid, event, sql_id
      ,(sysdate - sql_exec_start)*24*60*60 as elapsed
from   v$session
where  sid = &1;



-- STEP4. 만일 실행 환경이 11g 가 아니라 10g라면 TEMP.SQL 파일의 내용은 다음과 같이 바뀔 것임

select sid, event, sql_id
from   v$session
where  sid = &1;



-- STEP5. 이렇게 생성된 TEMP.SQL파일을 새로운 SQL*Plus 스크립트 파일로 사용할 수 있음

SQL> @temp &sid

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



-- STEP6. 즉 다음과 같은 단순한 호출 만으로 복잡해 보이는 조건부 쿼리를 구현할 수 있음

SQL> @make_temp
SQL> @temp &sid