IT Soldier Sakuri !!

Oracle使い。いつのまにかIT戦士になってしまったさくりの可哀想な奮闘記。

Oracle活用 結果をファイルに出力

今日は起きたら8:00で絶望した。
だって、8:04の電車に乗らなくちゃいけないのに、あと4分しかない。
駅まで走って6分。
これはもう無理だと観念した私は……課長にお腹痛いメールを送りました。
そう、さぼりです。さぼり。
そしてそんな日に限って障害が起きてるっぽい。
課長、ごめんね。明日優しくするから。

さて、SQL*Plusで様々なSQLを実行するに当たって、結果は残しておきたい。
そんなときに使うのが「SPOOL」機能。


spool [好きにファイル名を指定]
から
spool off
までをファイルに書き込んでくれる機能。
ファイルはSQL*Plusを起動した場所に作成される。
こんな感じ。

C:\Users\SAKURI\sql>sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on83 18:43:35 2015

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

18:43:35 SQL> spool Department.txt
18:43:57 SQL> conn hr/hr@XE
接続されました。
18:44:06 SQL> select * from Departments
18:44:23   2  /

DEPARTMENT_ID DEPARTMENT_NAME                                              MANAGER_ID LOCATION_ID
------------- ------------------------------------------------------------ ---------- -----------
           10 Administration                                                      200        1700
           20 Marketing                                                           201        1800
           30 Purchasing                                                          114        1700
           40 Human Resources                                                     203        2400
           50 Shipping                                                            121        1500
           60 IT                                                                  103        1400
           70 Public Relations                                                    204        2700
           80 Sales                                                               145        2500
           90 Executive                                                           100        1700
          100 Finance                                                             108        1700
          110 Accounting                                                          205        1700
          120 Treasury                                                     @                 1700
          130 Corporate Tax                                                @                 1700
          140 Control And Credit                                           @                 1700
          150 Shareholder Services                                         @                 1700
          160 Benefits                                                     @                 1700
          170 Manufacturing                                                @                 1700
          180 Construction                                                 @                 1700
          190 Contracting                                                  @                 1700
          200 Operations                                                   @                 1700
          210 IT Support                                                   @                 1700
          220 NOC                                                          @                 1700
          230 IT Helpdesk                                                  @                 1700
          240 Government Sales                                             @                 1700
          250 Retail Sales                                                 @                 1700
          260 Recruiting                                                   @                 1700
          270 Payroll                                                      @                 1700

27行が選択されました。

18:44:24 SQL> spool off
18:44:33 SQL> exit
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Productionとの接続が切断されました。

C:\Users\SAKURI\sql>

[@]はNULLです。

でもメモ帳で開くと…ガッタガタ…
f:id:itsoldiersakuri:20150803191028p:plain
そんな時は、SPOOL機能でCSVファイルにすればいい!!!

set colsep ',' カラム間の区切り文字をカンマ','にする。
set trimspool on 各項目の右端の空白を無くす。

これを設定するだけですごく簡単になる!!!

C:\Users\SAKURI\sql>sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on83 19:06:58 2015

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

19:06:58 SQL> set colsep ','
19:07:04 SQL> set trimspool on
19:07:16 SQL> spool Department_new.csv
19:07:22 SQL> conn hr/hr@XE
接続されました。
19:07:27 SQL> select * from Departments
19:07:31   2  /

DEPARTMENT_ID,DEPARTMENT_NAME                                             ,MANAGER_ID,LOCATION_ID
-------------,------------------------------------------------------------,----------,-----------
           10,Administration                                              ,       200,       1700
           20,Marketing                                                   ,       201,       1800
           30,Purchasing                                                  ,       114,       1700
           40,Human Resources                                             ,       203,       2400
           50,Shipping                                                    ,       121,       1500
           60,IT                                                          ,       103,       1400
           70,Public Relations                                            ,       204,       2700
           80,Sales                                                       ,       145,       2500
           90,Executive                                                   ,       100,       1700
          100,Finance                                                     ,       108,       1700
          110,Accounting                                                  ,       205,       1700
          120,Treasury                                                    ,@         ,       1700
          130,Corporate Tax                                               ,@         ,       1700
          140,Control And Credit                                          ,@         ,       1700
          150,Shareholder Services                                        ,@         ,       1700
          160,Benefits                                                    ,@         ,       1700
          170,Manufacturing                                               ,@         ,       1700
          180,Construction                                                ,@         ,       1700
          190,Contracting                                                 ,@         ,       1700
          200,Operations                                                  ,@         ,       1700
          210,IT Support                                                  ,@         ,       1700
          220,NOC                                                         ,@         ,       1700
          230,IT Helpdesk                                                 ,@         ,       1700
          240,Government Sales                                            ,@         ,       1700
          250,Retail Sales                                                ,@         ,       1700
          260,Recruiting                                                  ,@         ,       1700
          270,Payroll                                                     ,@         ,       1700

27行が選択されました。

19:07:32 SQL> spool off
19:07:40 SQL> exit
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Productionとの接続が切断されました。

C:\Users\SAKURI\sql>

カンマ区切りで表示されました。
SQL*Plus上ではちょっと見にくいけれど、ファイルを開くと…
f:id:itsoldiersakuri:20150803191042p:plain

こんな感じ。
これならExcelから開けるので、フィルタもかけられて操作がしやすい!
ただし、ひとつだけ注意しなくちゃいけないのは、前ゼロ問題。
CSVは03-3333-3333みたいな、先頭にあるゼロが桁落ちしてなくなってしまうので、
そういう文字列がある場合はこれは使えないんだよね~( ;∀;)