Oracle活用 Excelからデータを取得する③更新できるメンテナンスツール

R…、Rubyやってるよ！
やってるんだけど、まだ特筆できるようなことないから、ね。

ってことで、やっぱりExcelVBAになっちゃうんだなぁ。
派遣さんや一般職の方にユーザ一覧のメンテナンスをさせたいなぁってことがある。
でもSQLとか使えないし…という時のために、
マスタメンテナンスツールを作ってみた。

表示させるところまでは今までと同じ。
でも一番左に黄色く[更新]って列が追加になってるでしょ？

これは、入力規則で[挿入][更新][削除]を追加しているので、
各作業に合わせて選ぶと、その通りの挙動となる仕組み。

[表示クリア]と[データ取得]は前回と基本的にはほぼ同じ。
（2列目からデータを表示させるとか、そのくらい。）

ただし、[データ取得]に関係するところとして、
[特定のセルに文字が入力されたら実行される]って機能が入ってる。
データが1行入ったら、その一番左の列に入力規則で[挿入][更新][削除]が設定されるようになりました。

あとは、[データ更新]ボタン。
これは単純に一番左の列で[挿入][更新][削除]のどれかが選択されていれば、
その通りに動かすよ！ってこと。

' ************************************************************************************************
' ***
' ***  社員マスタメンテナンス
' ***
' ***   概要：OO4Oを使用。Oracleのデータ取得、挿入、更新、削除処理。
' ***
' ***
' ***   作成者：　SAKURI
' ***   作成日：　2015/08/13
' ***
' ************************************************************************************************
Option Explicit

'データクリアボタン押下
Private Sub btnClear_Click()
    '選択した列の内容をクリア
    Range("A3:XFD1048576").ClearContents
    Range("A3:XFD1048576").ClearFormats
    Range("A3:A1048576").Validation.Delete
    'セルA5の選択（複数セル選択した選択範囲のクリアのため）
    Range("A3:A3").Select
    
End Sub

'「全件データ取得」ボタン押下
Private Sub btnAlldata_Click()
On Error GoTo ERR_HANDLER

    Dim OraSession As Object        'セッション
    Dim OraDatabase As Object       'データベース
    Dim rs As Object                'レコードセット
    Dim i As Long
    Dim rownum As Long
    Dim colnum As Integer
    Dim strSQL As String            'SQL文
    Dim maxrownum As Long
    Dim maxcolnum As Long
    
    'すでに表示されている内容のクリア
    Call btnClear_Click
         
    'セッションオブジェクトの生成
    Set OraSession = CreateObject("OracleInProcServer.XOraSession")
    'データベースオブジェクトの生成（接続先を指定）
    Set OraDatabase = OraSession.OpenDatabase("XE", "hr/hr", 0&)
    
    'SQL文
    strSQL = "select * from EMPLOYEES order by 1"

    'SQLを実行してデータを取得
    Set rs = OraDatabase.CreateDynaset(strSQL, 0&)
                                                        
    '[更新]項目
    ActiveSheet.Cells(2, 1) = "更新"
    Range("A2").Interior.Color = RGB(255, 255, 153)
                                                        
    '列名の表示
    rownum = 2
    colnum = 2
    For i = 0 To rs.Fields.Count - 1
        ActiveSheet.Cells(rownum, colnum) = rs(i).Name
        colnum = colnum + 1
    Next
    
    'データの表示（取得したデータを1行ずつExcelに転記）
    rownum = 3
    Do Until rs.EOF
        colnum = 2
        For i = 0 To rs.Fields.Count - 1
            ActiveSheet.Cells(rownum, colnum) = rs(i).Value
            colnum = colnum + 1
        Next
        rs.MoveNext
        rownum = rownum + 1
    Loop
    
    '何行何列目まで表示したか保存
    maxrownum = rownum - 1
    maxcolnum = colnum - 1
        
    '枠線つける
    Range(Cells(2, 1), Cells(maxrownum, maxcolnum)).Borders.LineStyle = xlContinuous
    
    'セル幅自動調整
    Range(Cells(2, 2), Cells(maxrownum, maxcolnum)).Columns.AutoFit
    
    'タイトルに色付け
    Range(Cells(2, 2), Cells(2, maxcolnum)).Interior.Color = RGB(197, 217, 241)
    
    'フィルタ設定
    Range("A2").AutoFilter
                                
    'オブジェクトのクローズ
    rs.Close
    
    '各種オブジェクトの開放
    Set rs = Nothing
    Set OraDatabase = Nothing
    Set OraSession = Nothing
    
    MsgBox ("ヾ(●´∀｀●) ﾃﾞｷﾀﾖｰ♪")

QUIT_OPER:
    'オブジェクト変数用に確保したメモリの開放
    Set rs = Nothing
    Set OraDatabase = Nothing
    Set OraSession = Nothing
    Exit Sub
    
ERR_HANDLER:    'エラー処理
    'エラー番号とエラー内容の表示
    MsgBox Err.Number & ")" & Err.Description
    Err.Clear
    GoTo QUIT_OPER

End Sub

'特定のセルに文字が入力されたら実行される
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim MyRow As Long
    Dim MyCol As Integer
        MyRow = Target.Row
        MyCol = Target.Column
        
        '1行目と2行目は無視
        If MyRow = 1 Or MyRow = 2 Then
            Exit Sub
        End If
              
        '社員NOが入っていたら[更新]列に入力規則を設定
        If MyCol = 2 Then
            ActiveSheet.Cells(MyRow, MyCol - 1).ClearContents
            With ActiveSheet.Cells(MyRow, MyCol - 1).Validation
                .Delete
                .Add Type:=xlValidateList, _
                        Operator:=xlEqual, _
                        Formula1:="挿入,更新,削除"
            End With
        End If
End Sub

'「データ更新」ボタン押下
Private Sub btnIUD_Click()
On Error GoTo ERR_HANDLER

    Dim OraSession As Object        'セッション
    Dim OraDatabase As Object       'データベース
    Dim rownum As Long
    Dim colnum As Integer
    Dim strSQL As String
    Dim i As Long
        
    'セッションオブジェクトの生成
    Set OraSession = CreateObject("OracleInProcServer.XOraSession")
    'データベースオブジェクトの生成
    Set OraDatabase = OraSession.OpenDatabase("xe", "hr/hr", 0&)
    
    '[更新]列がブランクになるまで実施
    rownum = 3
    Do Until ActiveSheet.Cells(rownum, 2) = ""
        
        Select Case ActiveSheet.Cells(rownum, 1)
            Case "挿入"
                
                '初期化
                strSQL = ""
                strSQL = "INSERT INTO EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID," & _
                         "SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES ('" & _
                          ActiveSheet.Cells(rownum, 2) & "','" & ActiveSheet.Cells(rownum, 3) & "','" & ActiveSheet.Cells(rownum, 4) & "','" & _
                          ActiveSheet.Cells(rownum, 5) & "','" & ActiveSheet.Cells(rownum, 6) & "','" & ActiveSheet.Cells(rownum, 7) & "','" & _
                          ActiveSheet.Cells(rownum, 7) & "','" & ActiveSheet.Cells(rownum, 9) & "','" & ActiveSheet.Cells(rownum, 10) & "','" & _
                          ActiveSheet.Cells(rownum, 11) & "','" & ActiveSheet.Cells(rownum, 12) & "')"
                          MsgBox (strSQL)
                OraDatabase.ExecuteSQL strSQL
        
            Case "更新"
            
                '初期化
                strSQL = ""
                strSQL = "UPDATE EMPLOYEES SET FIRST_NAME = '" & ActiveSheet.Cells(rownum, 3) & "' , LAST_NAME = '" & ActiveSheet.Cells(rownum, 4) & "' ," & _
                         "EMAIL = '" & ActiveSheet.Cells(rownum, 5) & "' , PHONE_NUMBER = '" & ActiveSheet.Cells(rownum, 6) & "' ," & _
                         "HIRE_DATE = '" & ActiveSheet.Cells(rownum, 7) & "' , JOB_ID = '" & ActiveSheet.Cells(rownum, 8) & "' ," & _
                         "SALARY = '" & ActiveSheet.Cells(rownum, 9) & "' , COMMISSION_PCT = '" & ActiveSheet.Cells(rownum, 10) & "' ," & _
                         "MANAGER_ID = '" & ActiveSheet.Cells(rownum, 9) & "' , DEPARTMENT_ID = '" & ActiveSheet.Cells(rownum, 10) & "' " & _
                         "WHERE EMPLOYEE_ID = '" & ActiveSheet.Cells(rownum, 2) & "' "
                OraDatabase.ExecuteSQL strSQL
        
            Case "削除"
            
                '初期化
                strSQL = ""
                'delete文作成
                strSQL = "DELETE FROM EMPLOYEES WHERE EMPLOYEE_ID = '" & ActiveSheet.Cells(rownum, 2) & "'"
                OraDatabase.ExecuteSQL strSQL
        
            Case Else
        
        
        End Select
       
       rownum = rownum + 1
    Loop

    
    '各種オブジェクトの開放
    Set OraDatabase = Nothing
    Set OraSession = Nothing
    
    MsgBox ("ｶﾝﾘｮｳｯ (｀･ω･´)ﾉ")
 
QUIT_OPER:
    'オブジェクト変数用に確保したメモリの開放
    Set OraDatabase = Nothing
    Set OraSession = Nothing
    Exit Sub
ERR_HANDLER:    'エラー処理
    'エラー番号とエラー内容の表示
    MsgBox Err.Number & ")" & Err.Description
    Err.Clear
    GoTo QUIT_OPER

End Sub

こーゆーメンテナンス業務って好きなんだけど、
ルーチンワークを自分で持ってると怒られる年齢になってきたので、
業務改善的として挙げてみようかな。

Rubyをちゃんと始めるかなぁ

夏休みも半分過ぎてしまった。
何やってたんだ、私！！！

RilsGirls参加に向けて（抽選待ちだけど）、
やっぱりちょっとはRubyをやっておかないとまずいんじゃないか？

ということで、引っ張り出してきたのはこれ。
たのしいRuby第4版

www.amazon.co.jp

なんとなく簡単そうで、初心者向きそうで、可愛い感じなので、
買っておいたっきりほぼ開いてませんでした。

が、これを今日から始めます。
毎日1章ペースでやればなんとかRailsGirlsに間に合いそうだし…。

よーし、頑張るぞー！！！

Oracle活用 RubyからOracleに接続してみる！

昨日RailsGirls申し込んでみたから、ちょっとRubyを触ってみなくちゃと思って。

だってもし抽選に当たったことを考えると恐ろしい。

1.まったくプログラミング経験のない人たち
2.Webフロンエンド(HTML, CSS)のコーディング経験ぐらいはある人たち
3.コンピュータサイエンスの知識を備えている人たち

こんな感じで分けられるらしいけど、
若干…そう若干プログラミング経験のある私はきっと、
2か3のどちらかになって…一番ダメな子になっちゃう可能性が高すぎる！

ということで、Ruby慣れをしておかなくちゃいけない。
インストールしたっきり全く使ってないRuby…
そんなRubyでWindows8から、Oracleに接続してみたよ！！

Oracleに接続するにはライブラリを追加しなくちゃいけないらしい。

オブジェクト指向スクリプト言語 Rubyにアクセスして[ライブラリ]をクリック。

ライブラリが置いてあるRubyGems.orgにアクセス。

[oci8]で検索。

[ruby-oci8]これ！これをクリック。

最新バージョンをクリック(Windows8なのでx64)して、[Download]をクリック。
※バージョンクリックしてもダウンロードされなくてあれ？あれ？ってなったｗｗ

ご丁寧にインストールコマンドもあるので、コピーしておく。

Rubyのコマンドプロンプトを立ち上げて。

ライブラリをダウンロードした場所に移動して、インストールコマンドを実行

gem install ruby-oci8 -v 2.1.8

数秒で完了！！

サンプルプログラムを作成。
oracle.rb

#拡張ライブラリのロード
require 'oci8'

#オブジェクトの作成 OCI8.new("ユーザ名","パスワード","SID")
my_ora = OCI8.new("hr", "hr","xe")

#sql文発行
my_ora.exec('select * from departments') do |r|
  puts r.join(',')
end

#暫く画面が消えないように。画面コピーしたいから。
sleep(30)

ダブルクリックで実行してみた！
じゃじゃーーーーーん！！！

こんなに簡単に接続できた！！
でもタイトルは表示されないのね。

これで何か作れないかな～♪

RailsGirls Tokyo 5thに申し込んでみた！

Rubyもまともにできないのだけれど、
時代はやっぱりWEBでしょ！という言葉に乗せられて、
Rails Girlsをちょくちょく気にしていたところ、
ついに東京で開催されるっていうので飛びついてみた。

railsgirls-tokyo.doorkeeper.jp

申し込んじゃったよ！
でもたった2日でもう募集人数オーバーしているので、抽選は必至。

IT女子な友達もいないので、参加してITの輪をちょいと広げたい。

抽選を切にお祈り申し上げまっす(>人<)！

Oracle活用 Excelからデータを取得する②ピボットテーブル作っちゃう

今日は朝からちょっと用事があったので、新宿にお出掛けして、
午後はネイルを変えてきました～♪

ネイルが変わっただけで気分は上々です！！

今日は…
Oracle活用 Excelからデータを取得する①の続きで、
取得したデータをピボットテーブルにしちゃいます。

たぶん営業さんとか事務のお姉さんってExcelは普通に使えても、
ピボットテーブルまで使いこなせる人ってなかなかいないと思うんだよね。
でも部署別の売上とか、1年分のデータの月毎とか…色んな形でデータ集計って必要になるはず。

なので、データを表示するだけじゃなくて、集計までしてあげよう！！

前回はこんな感じでデータを表示させました。

今回は少しプログラムを追加して、
こんな感じにシートを追加して、ピボットテーブルを作成しちゃいます。

ソースはこちら。
追加部分は['ADD 2015/08/09 START]から['ADD 2015/08/09 END]までです。

' ************************************************************************************************
' ***
' ***  社員マスタを抽出し、部署毎の人件費（給与）をピボットテーブルで集計する。
' ***
' ***   概要：OO4Oを使用。Oracleのデータ取得処理。
' ***
' ***
' ***   作成者：　SAKURI
' ***   作成日：　2015/08/09
' ***
' ************************************************************************************************
Option Explicit

'「表示クリア」ボタン押下後の処理
Private Sub btnClear_Click()
    '選択した列の内容をクリア
    Range("A5:XFD1048576").ClearContents
    Range("A5:XFD1048576").ClearFormats
    'セルA5の選択（複数セル選択した選択範囲のクリアのため）
    Range("A5:A5").Select
            
End Sub

'「集計」ボタン押下後の処理
Private Sub btnData_Click()
On Error GoTo ERR_HANDLER

    Dim OraSession As Object        'セッション
    Dim OraDatabase As Object       'データベース
    Dim rs As Object                'データセット(レコードセット)
    Dim i As Long
    Dim rownum As Long
    Dim colnum As Integer
    Dim strSQL As String 'SQL文
    Dim maxrownum As Long
    Dim maxcolnum As Long
    Dim ws As Worksheet
    Dim setSheet As Worksheet
    Dim flag As Boolean
            
    'セッションオブジェクトの生成
    Set OraSession = CreateObject("OracleInProcServer.XOraSession")
    'データベースオブジェクトの生成（接続先を指定）
    Set OraDatabase = OraSession.OpenDatabase("XE", "hr/hr", 0&)
    
    'すでに表示されている内容のクリア
    Call btnClear_Click
                                                            
    'SQL文
    strSQL = "select * from EMPLOYEES"

    'SQLを実行してデータを取得
    Set rs = OraDatabase.CreateDynaset(strSQL, 0&)
                                                                                                                
    '列名の表示
    rownum = 5
    colnum = 1
    For i = 0 To rs.Fields.Count - 1
        ActiveSheet.Cells(rownum, colnum) = rs(i).Name
        colnum = colnum + 1
    Next
    
    'データの表示（取得したデータを1行ずつExcelに転記）
    rownum = 6
    Do Until rs.EOF
        colnum = 1
        For i = 0 To rs.Fields.Count - 1
            ActiveSheet.Cells(rownum, colnum) = rs(i).Value
            colnum = colnum + 1
        Next
        rs.MoveNext
        rownum = rownum + 1
    Loop
    
    '何行何列目まで表示したか保存
    maxrownum = rownum - 1
    maxcolnum = colnum - 1
        
    '枠線つける
    Range(Cells(5, 1), Cells(maxrownum, maxcolnum)).Borders.LineStyle = xlContinuous
    
    'セル幅自動調整
    Range(Cells(5, 1), Cells(maxrownum, maxcolnum)).Columns.AutoFit
    
    'タイトルに色付け
    Range(Cells(5, 1), Cells(5, maxcolnum)).Interior.Color = RGB(197, 217, 241)

    'ADD 2015/08/09 START------------------------------------------------

    Set setSheet = ActiveWorkbook.Worksheets("社員マスタ")

    '集計シートがあるかどうかチェック
    For Each ws In Worksheets
        If ws.Name = "集計" Then flag = True
    Next ws
    
    
    If flag = True Then
        '集計シートをActiveにする
        ActiveWorkbook.Worksheets("集計").Activate
    
        '内容を削除
        With ActiveSheet
        Application.DisplayAlerts = False
            If .PivotTables.Count >= 1 Then
              .Delete
            End If
        Application.DisplayAlerts = True
        End With
    End If
    
    '集計シート作成
    Worksheets.Add
    ActiveSheet.Name = "集計"
    
     'ピボットを作成
    ActiveWorkbook.PivotCaches.Create( _
        SourceType:=xlDatabase, _
        SourceData:="社員マスタ!R5C1:R" & maxrownum & "C" & maxcolnum, _
        Version:=xlPivotTableVersion14).CreatePivotTable _
        TableDestination:="集計!R3C1", _
        TableName:="ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1", _
        DefaultVersion:=xlPivotTableVersion14
    Sheets("集計").Select
    
    '行ラベル設定
    With ActiveSheet.PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1").PivotFields("DEPARTMENT_ID")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1").PivotFields("FIRST_NAME")
        .Orientation = xlRowField
        .Position = 2
    End With
        
    '値欄設定と集計方法設定
    ActiveSheet.PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1").AddDataField ActiveSheet.PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1").PivotFields("SALARY"), "合計 / SALARY", xlSum
        
    '表示名変更
    ActiveSheet.PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1").PivotFields("合計 / SALARY").Caption = "SALARY(SUM)"
        
    'ピボットフィールドリスト非表示
    ActiveWorkbook.ShowPivotTableFieldList = False
    'レポートレイアウト表形式
    ActiveSheet.PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1").RowAxisLayout xlTabularRow
    'プラスボタン非表示
    ActiveSheet.PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1").ShowDrillIndicators = False
    'ピボットスタイル淡色9
    ActiveSheet.PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1").TableStyle2 = "PivotStyleLight9"
    
    'タイトル設定
    ActiveSheet.Range("A1:A1").Select
    ActiveSheet.Cells(1, 1) = "部署別給与集計"
    ActiveSheet.Range("A1").Font.Bold = True
    ActiveSheet.Range("A1").Font.Italic = True
    
    'ADD 2015/08/09 END------------------------------------------------
                                
    'オブジェクトのクローズ
    rs.Close
    
    '各種オブジェクトの開放
    Set rs = Nothing
    Set OraDatabase = Nothing
    Set OraSession = Nothing
    
    MsgBox ("ヾ(●´∀｀●) ﾃﾞｷﾀﾖｰ♪")

QUIT_OPER:
    'オブジェクト変数用に確保したメモリの開放
    Set rs = Nothing
    Set OraDatabase = Nothing
    Set OraSession = Nothing
    Exit Sub
ERR_HANDLER:    'エラー処理
    'エラー番号とエラー内容の表示
    MsgBox Err.Number & ")" & Err.Description
    Err.Clear
    GoTo QUIT_OPER
End Sub

以下のGitHubに[Pivot.xlsm]というファイル名でコミットしてあります。github.com

Oracle活用 テーブルの削除と救出

夏休み初日を自堕落に過ごしてしまい、罪悪感が半端ない。
この調子でいったら、「あっ！」という間に夏休み終了だよ…ふえぇ。
明日はちゃんと活動します！

さて、Oracle10gからごみ箱機能というものが追加されて、
テーブルの削除 = 取り返しのつかない所業　ではなくなりました。
でも実際あんまり仕事ではテーブルの削除なんてしないので、
今日はテーブルの削除と、救出について勉強してみました。

最初にこんな感じのテーブルを作って何件かデータを入れておく。

BOONUS テーブル

列名	データ型	サイズ	制約
DEPARTMENT_ID	NUMBER	4	NOT NULL
RATE	NUMBER	5,2

21:01:25 SQL> CREATE TABLE BONUS (
21:01:37   2      DEPARTMENT_ID NUMBER(4) NOT NULL,
21:01:37   3      RATE NUMBER(5,2),
21:01:37   4      CONSTRAINT PK_BONUS PRIMARY KEY(DEPARTMENT_ID)
21:01:37   5  ) TABLESPACE USERS;

表が作成されました。

21:01:37 SQL> select * from bonus;

レコードが選択されませんでした。

21:01:56 SQL> INSERT INTO BONUS VALUES (10,2);

1行が作成されました。

21:05:11 SQL> INSERT INTO BONUS VALUES (20,1.8);

1行が作成されました。

21:05:11 SQL> INSERT INTO BONUS VALUES (100,1.92);

1行が作成されました。

21:05:11 SQL> commit;

コミットが完了しました。

21:05:23 SQL> select * from bonus;

DEPARTMENT_ID       RATE
------------- ----------
           10          2
           20        1.8
          100       1.92

21:05:26 SQL> 

早速テーブルを削除してみる。ドキドキ…

21:05:26 SQL> DROP TABLE BONUS;

表が削除されました。

21:06:03 SQL> select * from bonus;
select * from bonus
              *
行1でエラーが発生しました。:
ORA-00942: 表またはビューが存在しません。


21:06:08 SQL>

こんなの会社で出てきちゃった日にはもう土下座して退職願を提出だよ…
怖い怖い

ここでゴミ箱をのぞくコマンドを…

21:06:08 SQL> SHOW RECYCLEBIN
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
BONUS            BIN$nuntDfzGSim+PLpJ9JzSOA==$0 TABLE        2015-08-08:21:06:03
21:06:32 SQL>

いたよ！いた！いた！！
削除した日時もちゃんと載ってるんだね。

ちなみに「Oracle Database 11g Express Editionのスタート・ガイド 」で見てみると、
こんな感じ。

BIN～って名前になるんだね。
PRIMARY_KEYも一緒にゴミ箱にいるみたい。

ではフラッシュバックで戻してみます！

21:06:32 SQL> FLASHBACK TABLE BONUS TO BEFORE DROP ;

フラッシュバックが完了しました。

21:07:08 SQL>

たったこれだけ。
これだけ？？？

21:07:08 SQL> select * from bonus;

DEPARTMENT_ID       RATE
------------- ----------
           10          2
           20        1.8
          100       1.92

21:07:12 SQL>

ちゃんと中身もありました！よかったね！！

ただし、削除のときにゴミ箱に入れず完全削除をすると
当たり前だけど、戻せませんでした。

21:07:12 SQL> DROP TABLE BONUS CASCADE CONSTRAINT PURGE;

表が削除されました。

21:08:14 SQL> select * from bonus;
select * from bonus
              *
行1でエラーが発生しました。:
ORA-00942: 表またはビューが存在しません。


21:08:16 SQL> SHOW RECYCLEBIN
21:08:18 SQL>

ゴミ箱にない！！！！！

まとめ：テーブル削除はまずゴミ箱に。そのあとでゴミ箱を空にしよう。

テーブルを削除する

DROP TABLE [テーブル名]

制約も含めてテーブルを削除する

DROP TABLE [テーブル名] CASCADE CONSTRAINT;

制約も含めてゴミ箱に入れずに完全に削除する

DROP TABLE [テーブル名] CASCADE CONSTRAINT PURGE;

PURGE恐るべし！！！

ゴミ箱を空にする

PURGE RECYCLEBIN ;

Oracle活用 どっちのSQLにしようか迷ったら実行計画を使ってみる！

明日から8連休の夏休みに突入ぅぅううううう！
気分が開放的になっとります！
夏休みに向けて髪も綺麗に染めて参りましたぁっ！！
ひゃっほーーーう！

Oracle活用 重複排除はGROUP BYではなくてDISTINCTを使うべし！で書いていた
実行計画について調べたのでφ(ﾟ-ﾟ=)ﾒﾓﾆｬﾝ

簡単にいうと…
実行計画とはSQLを実行したときにどんな順番でSQLが処理されていくのかを解説してくれる機能。

どっちのSQLの方がパフォーマンスいいんだろう？
ってときに実行すれば、こっちの方が効率よくテーブルにアクセスしてるじゃん！
ってことがわかる。

実行計画を出す方法①

EXPLAIN PLAN FOR [解析したいSQL]
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

実際にやってみると…

22:46:38 SQL> explain plan for select * from employees where department_id = '110';

解析されました。

22:46:49 SQL> select plan_table_output from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------
Plan hash value: 2056577954

-------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                   |     2 |   138 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES         |     2 |   138 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EMP_DEPARTMENT_IX |     2 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("DEPARTMENT_ID"=110)

14行が選択されました。

22:46:59 SQL>

ええええ！？
「解析されました」ってそれだけ？結果は？結果はもらえないの？
とちょっとびっくりした。
しかも、いちいち実行計画のテーブルにアクセスして結果を見ないといけないのが面倒かも。

と、思ったら、こんな方法があった！
というかこっちの方が一般的みたい。

実行計画を出す方法②

SET AUTOTRACE ON EXPLAIN
[解析&実行したいSQL]
SET AUTOTRACE OFF

SETを打っておけばそれ以降のSQLは実行結果と共に、実行計画も表示される。
最後にまたSETでOFFにしておけばいいみたい。

22:46:59 SQL> SET AUTOTRACE ON EXPLAIN
22:57:08 SQL> select * from employees where department_id = '100';

EMPLOYEE_ID FIRST_NAME                               LAST_NAME
----------- ---------------------------------------- ----------------------------------------------
        108 Nancy                                    Greenberg
        109 Daniel                                   Faviet
        110 John                                     Chen
        111 Ismael                                   Sciarra
        112 Jose Manuel                              Urman
        113 Luis                                     Popp

6行が選択されました。


実行計画
----------------------------------------------------------
Plan hash value: 2056577954

-------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                   |     6 |   414 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES         |     6 |   414 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EMP_DEPARTMENT_IX |     6 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("DEPARTMENT_ID"=100)

22:57:26 SQL> SET AUTOTRACE OFF
22:58:43 SQL>

これなら実行結果も実行計画もいっぺんに出てくるので、楽チン！！

ちょっと単純なSQL過ぎるけど、意味はこんな感じ。

Id	実行順序
Operation	実行内容
Name	アクセスしたTableやIndexの名前
Rows	行数
Byte	アクセスされるバイト数(目安)
Cost(%CPU)	操作コストの見積もり
Time	かかった時間

なので、Operationを見てどんな順番にどんな処理がされているのかを確認して、
Costでどのくらいの処理力が必要なのかを見るって感じかな。

もうちょっと複雑なSQLを実行して、処理を追ってみようかな。