freespeedo's blog

未だにVB.netでDataTableを使ったりするが、最近DataTableを使用してLinqのクエリーを作ったりしている。外部結合もできるのだが、ネットで調べるとGroup Joinで外部結合する方法が頻繁に紹介されている…が、この方法だとどうも1つのテーブルとしか外部結合ができない感じ…(私のやり方が悪いのかもしれないが)。

と思ったら、where句を使うことで複数のテーブルと外部結合ができるようだ。ということでテーブル構成は以下の図にある3テーブルを作り、dtMainにdtSub1,dtSub2を外結するイメージで作ってみる。




(1) 「Microsoft Visual Studio Express 2012 for Windows Desktop」を開き、新規で「Windowsフォームアプリケーション」を開く

(2) Form1に適当にDataGridViewをいれておく。



(3) Form1.vbに以下のコードを追記

Public Class Form1

    Public Sub New()

        ' この呼び出しはデザイナーで必要です。
        InitializeComponent()

        ' InitializeComponent() 呼び出しの後で初期化を追加します。
        Construct()
    End Sub

    Private Sub Construct()
        ' テーブル定義
        Dim dtMain As New DataTable
        dtMain.Columns.Add(New DataColumn With {.DataType = System.Type.GetType("System.Int32"), .ColumnName = "ID"})
        dtMain.Columns.Add(New DataColumn With {.DataType = System.Type.GetType("System.String"), .ColumnName = "NAME"})

        Dim dtSub1 As DataTable = dtMain.Clone
        Dim dtSub2 As DataTable = dtMain.Clone
        Dim dtKekka As DataTable = dtMain.Clone
        Dim dtRow As DataRow = Nothing

        ' データインサート
        For i As Integer = 1 To 3
            dtRow = dtMain.NewRow
            dtRow("ID") = i
            dtMain.Rows.Add(dtRow)
            If i = 1 Then
                dtRow("NAME") = "外結DT１"
                dtSub1.ImportRow(dtRow)
            ElseIf i = 3 Then
                dtRow("NAME") = "外結DT２"
                dtSub2.ImportRow(dtRow)
            End If
        Next

        ' 外結クエリ①
        Dim query = From main In dtMain.AsEnumerable _
                    From sub1 In dtSub1.AsEnumerable.Where(Function(m) m.Field(Of Integer)("ID") = main.Field(Of Integer)("ID")).DefaultIfEmpty() _
                    From sub2 In dtSub2.AsEnumerable.Where(Function(m) m.Field(Of Integer)("ID") = main.Field(Of Integer)("ID")).DefaultIfEmpty() _
                    Select New With _
                    { _
                        .id = main!ID, _
                        .name = If(sub1 Is Nothing, If(sub2 Is Nothing, main!NAME, sub2!NAME), sub1!NAME) _
                    }
        For Each row In query
            dtRow = dtKekka.NewRow
            dtRow("ID") = row.id
            dtRow("NAME") = row.name
            dtKekka.Rows.Add(dtRow)
        Next
        Me.DataGridView1.DataSource = dtKekka

    End Sub
End Class

(4) ソリューションをビルド


①の外結クエリのところで実際に外部結合。
dtMainのIDに対し、dtSub1とdtSub2のIDを外結。Select句ではIDはdtMainのものを表示。NAMEはdtSub1に有効なNameがあればそれを表示、なければdtSub2に有効なNameがあればそれを表示、それ以外はdtMainのName(NULL)を表示。という感じ。

ということで実行。



想定通り、IDは全行表示、NAMEはあれば表示された。

Group JoinだとInto句でリスト化を挟むとかイマイチ不明な過程があったが、Where句だとそういう考慮も無くすっきり書けてイメージがとらえやすい感じ。

sub1やsub2でゴリゴリと長ったらしい集計クエリーを書いてももちろん外結してくれるので良い感じ。このサンプルはわざわざDataTableを挟んでるけど、もちろん普通のエンティティデータのクエリでもいけました。


しかしDataTableはAsEnumerable化してもデータ数が多いと集計が遅いね…。一回DataTableのデータをEntityの配列に入れなおして集計すると凄い早い。というか最初からDataTableを使うなよってことですが、何か使ってしまう。 続きを読む

最近PL/SQLでプログラミングしてないと思ったので、リハビリを兼ねつつ、汎用的にテーブルのデータをCSV出力するプロシージャを作ってみる。

現在の環境
OS:Windows 7(Ultimate)
DB:Oracle11gXE


■１．権限付与

今回はadminというスキーマ内のテーブルをCSV出力するプロシージャを作ってみる。

出力先はXEが標準で持ってるDATA_PUMP_DIRディレクトリに出力しようと思うので、adminスキーマにDATA_PUMP_DIRのread, write権限を付与してみる。また出力に使うutl_fileの実行権限も一緒に付与。

SQL*PLUSでSYSでログインして下記を実行。

SQL>grant read, write on directory DATA_PUMP_DIR to admin;
SQL>grant execute on utl_file to admin;


■２．プロシージャの作成

次にadminスキーマにログインして、CSV出力するプロシージャ(csv_out)をコンパイル。

create or replace procedure csv_out (tbl_name in varchar2)
is
    select_str varchar2(2000);
    procedure create_select(tbl in varchar2, record_str out varchar2)
    is
        type rec_type is record(column_name varchar2(30), data_type varchar2(30));
        type tbl_rec_type is table of rec_type;
        tbl_rec tbl_rec_type;
    begin
        record_str := 'select ';
        execute immediate 'select column_name, data_type from user_tab_columns where table_name = :1 order by column_id' bulk collect into tbl_rec using tbl;
        for i in 1 .. tbl_rec.count loop
            record_str := record_str
                || case when tbl_rec(i).data_type in ('VARCHAR2', 'CHAR', 'CLOB') then ' ''"'' || ' else '' end
                || tbl_rec(i).column_name
                || case when tbl_rec(i).data_type in ('VARCHAR2', 'CHAR', 'CLOB') then ' || ''"'' ' else '' end
                || case when i=tbl_rec.last then '' else ' || '','' ||' end;
        end loop;
        record_str := record_str || ' as csv_data from ' || tbl;
    end;

    procedure create_csv(tbl in varchar2, str in varchar2)
    is
        fout utl_file.file_type;
        type csv_data_type is table of varchar2(5000);
        csv_data_tbl csv_data_type;
    begin
        fout := utl_file.fopen('DATA_PUMP_DIR', tbl || '.csv', 'w');
        execute immediate str bulk collect into csv_data_tbl;
        for i in 1 .. csv_data_tbl.count loop
            utl_file.put_line(fout, csv_data_tbl(i));
        end loop;
        utl_file.fclose(fout);
    end;
begin
  -- CSV出力Select文作成
  create_select(upper(tbl_name), select_str);
  -- CSV出力
  create_csv(upper(tbl_name), select_str);
end;
/

昔よくやっていた、CSV出力するためのSELECT文を作ってdbms_outputで出力するというのをPL/SQLに置き換えただけ…。
一応VARCHAR2,CHAR,CLOBだけ2重符号で囲う。


■３．実行

adminスキーマの中のテーブル(******)を適当に指定して、実行してみる。

SQL>execute csv_out('******');

出力先(C:\oraclexe\app\oracle\admin\XE\dpdum)に、******.csvというファイル名で出力されるので確認。



もっと簡単に出力できる方法があったりするんかな？調べてないけど…。というか、たいていCSV出力はsql_developerでやっちゃったりしてる(-_-;)。