SQL Developerからテーブルにデータを入れる

INSERTやUPDATE文書きましょうね、という話ではあるんですが。GUIでやりたいときもあるものなのでやり方をメモ。環境はSQL Developer 3.1, Oracle 11g xeです。他のバージョンや別のRDBMSへは上手く行かないかもしれないのであしからず。

とりあえず適当なテーブルを作り、これをサンプルとして機能を見ていく。

CREATE TABLE A_IMPORT 
(
  A_NUM       NUMBER 
, A_VARCHAR2  VARCHAR2(20) 
, A_DATE      DATE 
, A_TIMESTAMP TIMESTAMP 
);

テーブルにデータを直接編集で追加＆更新＆削除する

追加（INSERT）

DBに接続後、左側のエクスプローラーから対象のテーブルを選んだあと、そのテーブルのデータというタブを選択する。テーブルに入ってるデータがここで参照できる。

その状態で行の挿入（Ctrl+I）ボタンを押すと上のように、緑色の枠で囲われた行が増える。あとは各カラムに適当な値を入れていって、変更のコミット（F11）をすればINSERT文が発行されてテーブルに行が追加される。発行されるクエリはこんな感じ。

INSERT INTO "KAGAMIHOGE"."A_IMPORT" (A_NUM, A_VARCHAR2, A_DATE, A_TIMESTAMP) 
VALUES ('3', 'ほげほげ', 
    TO_DATE('2012/06/13 19:48:46', 'YYYY/MM/DD HH24:MI:SS'), 
    TO_TIMESTAMP('2012/06/13 19:50:18.824000000', 'YYYY/MM/DD HH24:MI:SS.FF'))

日付型はカラムの右端にあるエンピツマークをクリックすると下図のような編集ダイアログが開く。今日の設定ボタンを押すと現在日付・時刻が入力される。入力ボックス右端の上下ボタンで、入力フォーカスのところの単位で増減が可能。

編集（UPDATE）

フィルタ欄に検索条件いわゆるWHERE句の右側に書くヤツを入力することで更新したい行を絞り込める。

カラムを編集すると行番号の欄が青色でマーキングされる。あとはコミットすれば値がテーブルに反映される。

発行されるクエリはこんな感じ。このテーブル主キーないんすけどどうやって行をピンポイントでUPDATEしてるんだと思いきや、ROWIDとORA_ROWSCN使ってるんですね。

UPDATE "KAGAMIHOGE"."A_IMPORT" SET 
    A_VARCHAR2 = 'ほげほげ', 
    A_DATE = TO_DATE('2012/06/14', 'YYYY/MM/DD') 
WHERE ROWID = 'AAAFHVAAEAAAAq1AAC' AND ORA_ROWSCN = '794661'

削除（DELETE）

行を選択して、選択した行の削除（Ctrl+D）ボタンを押す。複数行選択可能。この削除ボタンを押すと行番号の欄が赤色でマーキングされる。あとはコミットすればDELETE文が発行されてテーブルから行が削除される。

なお、これはSQL Developerの話とはちょっとずれるが行の挿入と削除は別々にやったほうが良い。例えば、下記のような操作を行ったあとにコミットを押したとする。

これの結果は、3行目がDELETEされ4,9行目がINSERTされる。経験上、割とこういうの凡ミスして謎のデータが挿入されて困るような気がするんで、あまりやらない方が良いかなーと。行の挿入ボタン押しすぎちゃったわーくらいなら削除ボタンで消しても良いと思うけど、やっべ意味わかんなくなったーとなったら変更のロールバック（F12）ボタンが安定行動なんじゃねーのと思います。

TO_DATEやTO_TIMESTAMPの日付フォーマット

テーブル編集すると裏ではINSERTやUPDATEが走るわけですが、TO_DATEやTO_TIMESTAMPの日付フォーマットは、プリファレンス -> データベース -> NLS の設定が使われているようだ。参考：NLS 関連パラメータ - オラクル・Oracleをマスターするための基本と仕組み

テーブルにコピペしたい

エクセルからOracleにコピペしたいんや、というのはちょくちょくある。

上図を元に解説すると、SQL Developer側で増やしたい行数分を行の挿入ボタンを押して増やしておく（ここでは4行分）。そのあとエクセルで範囲をコピー（A1からD4）、ペーストを開始したい行（3行目）を選択して貼り付けを実行。コミットをするとINSERTが行数分走る。

エクセルから〜とか書いたけどコピペ元はtsv形式なら良いみたい。

エクセルからコピペでデータ入れるにはA5:SQL Mk-2 - フリーの汎用SQL開発ツール/ER図ツールが便利。参考：
Oracleで適当な件数のテストデータつくる - kagamihogeの日記

インポート

エクセルのデータをそのまんまOracleに投入したいんや、というのもちょくちょくある。

テーブルを右クリック、データのインポートを選ぶと、テキスト・SQL・PL/SQL・CSV,TSVはともかくとして、xls, xlsxがあるのが目を引く。ので、さっき使ったエクセルのファイルをインポートすることを試してみる。

まず、俺の手元の環境であるところのExcel 2000は.xlsファイルの文字コードがShift_JIS *1 なので、インポートファイルのエンコーディングを変更する*2。でないとエクセルからINSERTを生成するところで文字化けしてしまい上手くいかない。

インポートでエクセルファイルを選ぶと下記のウィザードが起動される。列3と列4の日付のフォーマットがYYYY/MM/DDになっていないがこれは後述。

ここいらまでは特に書くことなし。

日付型に関してはインポート時はここで日付フォーマットをステップ1で表示されてるものに会わせた形式を指定しなければならない。上図の例だと、DATEにMM/DD/YYYY、TIMESTAMPにMM/DD/YYYY HH24:MI 等と指定しないといけない。