SqlplusでデータのExcel形式ファイル出力

SQLPlus

SqlplusよりHTML（疑似Excel）フォーマットの出力方法

---

Windows系のSqlplusを使います。
生成したファイルの開く時、Excelは確認メッセージが表示されますが、無視していい。

sqlplus

SET LINESIZE 1000 PAGES 50000
SET TRIMSPOOL ON
SET MARKUP HTML ON SPOOL ON ENTMAP ON PREFORMAT OFF -
HEAD " -
 <STYLE type='text/css'> -
  body {font:10pt 'Meiryo UI',Helvetica,sans-serif; color:black; background:White;} -
  p {   font:10pt 'Meiryo UI',Helvetica,sans-serif; color:black; background:White;} -
  table,tr{font:10pt 'ＭＳ Ｐゴシック',Helvetica,sans-serif; color:Black; background:#f7f7e7; -
        padding:0px 0px 0px 0px; margin:0px 0px 0px 0px; white-space:nowrap;} -
  th {  font: 10pt 'ＭＳ Ｐゴシック',Helvetica,sans-serif; color:#336699; background:#cccc99; mso-text-control:shrinktofit; padding:0px 0px 0px 0px;} -
  td {  font: 10pt 'ＭＳ Ｐゴシック',Helvetica,sans-serif; mso-text-control:shrinktofit; mso-number-format:'\@'; padding:0px 0px 0px 0px;} -
  span {  font:10pt 'Meiryo UI'; color:black; background-color:White;} -
  h1 {  font:16pt 'ＭＳ Ｐゴシック',Helvetica,Geneva,sans-serif; color:#336699; background-color:White; -
        border-bottom:1px solid #cccc99; margin-top:0pt; margin-bottom:0pt; padding:0px 0px 0px 0px;} -
  h2 {  font: 10pt 'ＭＳ Ｐゴシック',Helvetica,Geneva,sans-serif; color:#336699; background-color:White; -
        margin-top:4pt; margin-bottom:0pt;} a {font:9pt 'ＭＳ Ｐゴシック',Helvetica,sans-serif; color:#663300; -
        background:#ffffff; margin-top:0pt; margin-bottom:0pt; vertical-align:top;} -
 </STYLE>-
" -
BODY "TEXT='black'" -
TABLE "WIDTH='90%' BORDER='1' cellspacing='0' cellpadding='1'"

SET NULL (nil)
ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'; 
ALTER SESSION SET NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH24:MI:SS.FF'; 
ALTER SESSION SET NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH24:MI:SS,FF';

VARIABLE KEY_NO_1 VARCHAR2(10)
VARIABLE KEY_NO_2 VARCHAR2(10)
VARIABLE KEY_NO_3 VARCHAR2(10)

EXECUTE :KEY_NO_1 := 1
EXECUTE :KEY_NO_2 := 2
EXECUTE :KEY_NO_3 := 0

------------------------------------------------------------
@setColumns.SQL
------------------------------------------------------------
-- ここから：ファイルに出力する設定 START
------------------------------------------------------------
VARIABLE BASEPATH VARCHAR2(50)
EXECUTE :BASEPATH := 'LOCAL'

SET VERIFY OFF
SET TIMING OFF
SET HEADING ON

COLUMN baseworkpath NEW_VALUE base_path NOPRINT
SELECT :BASEPATH baseworkpath FROM DUAL;

------------------------------------------------------------
-- createWorkPath.bat
------------------------------------------------------------
-- @if (1==1) /*
-- @ECHO OFF
-- 
-- ECHO  %1
-- 
-- CScript //Nologo //E:JScript "%~f0" %1
-- 
-- EXIT /B 0
-- 
-- rem */
-- 
-- @end
-- 
-- getYYYYMMDD = function() {
--     var dd = new Date();
--     var yy = dd.getYear();
--     var mm = dd.getMonth() + 1;
--     var dd = dd.getDate();
--     if (yy < 2000) { yy += 1900; }
--     if (mm < 10) { mm = "0" + mm; }
--     if (dd < 10) { dd = "0" + dd; }
--     
--     return ( "" + yy ) + ( "" + mm ) + ( "" + dd ) ;
-- }
-- 
-- captureWorkFolder = function(argc, argv) {
--   var FSO = new ActiveXObject("Scripting.FileSystemObject");
--   
--   var workBasePath = argv(0);
--   
--   if ( !FSO.FolderExists(workBasePath) ) {
--     FSO.CreateFolder(workBasePath);
--   }
--   
--   var workPath = workBasePath + "\\" + getYYYYMMDD();
--   
--   if ( !FSO.FolderExists(workPath) ) {
--     FSO.CreateFolder(workPath);
--   }
--   
--   FSO = null;
-- }
-- 
-- captureWorkFolder(WScript.arguments.length, WScript.arguments)
------------------------------------------------------------

HOST createWorkPath.bat &base_path.

VARIABLE w_RESULT_FILE VARCHAR2(420)
DECLARE
    w_DBDAY   NUMBER := 0; -- 220;
    w_PATH    VARCHAR2(200) := '.\' || :BASEPATH || '\'  ||TO_CHAR(SYSDATE - w_DBDAY, 'YYYYMMDD') ;
    w_TIME    VARCHAR2( 20) := TO_CHAR(SYSDATE - w_DBDAY, 'YYMMDD_HH24MISS');
    w_PREFIX  VARCHAR2(200) := 'PREFIX_';
    w_NOS     VARCHAR2(200) := :KEY_NO_3 || '_' || :KEY_NO_1 || '_' || :KEY_NO_2;
BEGIN
    :w_RESULT_FILE := w_PATH || '\' || w_PREFIX || w_TIME || '_' || w_NOS || '.xls';
END;
/

--SET PAGESIZE 0
COLUMN runtime NEW_VALUE runtime_text NOPRINT
SELECT :w_RESULT_FILE RUNTIME FROM DUAL;

SET  SERVEROUTPUT ON SIZE UNLIMITED

SET TRIMSPOOL ON
SPOOL &runtime_text.

------------------------------------------------------------
-- ここまで：ファイルに出力する設定 START
------------------------------------------------------------

--------------------------------------------------------------------------------
-- TBL_NAME1
--------------------------------------------------------------------------------
    SET MARKUP HTML ENTMAP OFF
    PROMPT <BR><SPAN>【サブテーブル TBL_NAME1】</SPAN>
    SET MARKUP HTML ENTMAP ON
    SELECT * FROM TBL_NAME1  ME  WHERE EXISTS (SELECT 1 FROM TBL_NAME2 S WHERE S.KEY_NO_3 = ME.KEY_NO_3 AND S.KEY_NO2 IN (:KEY_NO_1,:KEY_NO_2))
    UNION
    SELECT * FROM TBL_NAME1  ME  WHERE ME.KEY_NO_3 = :KEY_NO_3;



    SET MARKUP HTML ENTMAP OFF
    PROMPT <BR><SPAN>【メインテーブル TBL_NAME0】</SPAN>
    SET MARKUP HTML ENTMAP ON
    SELECT * FROM TBL_NAME0  ME  WHERE EXISTS (SELECT 1 FROM TBL_NAME2 S WHERE S.KEY_NO = ME.KEY_NO AND S.KEY_NO2 IN (:KEY_NO_1,:KEY_NO_2))
    UNION
    SELECT * FROM TBL_NAME0  ME  WHERE EXISTS (SELECT 1 FROM TBL_NAME2 S WHERE S.KEY_NO = ME.KEY_NO AND S.KEY_NO_3 = :KEY_NO_3) ORDER BY 1, 2;

SPOOL OFF
SET MARKUP HTML OFF SPOOL OFF
SET TERMOUT ON

host start &runtime_text.

EXIT;