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

11127 ワード
Sqlplus HTML Format Excel HTML テキストリンク

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;