조건부 쿼리의 구현을 치환 변수가 아닌 SPOOL을 이용해서 구현하는 예제

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