kagamihogeの日記

kagamihogeの日記です。

OracleのSQL Developerが便利

DB Oracle SQL Developer

製品情報 - Oracle SQL Developer

Oracleから無償ダウンロードできるSQL Developerというツールがかなり便利だったので紹介してみる。なおこのエントリで使用するOracle SQL Developerのバージョンは3.0です……が、ちょくちょく追記しているため、3.0or4.0以降のバージョンを基に書かれている部分もあります。

å°Žå…¥

SQL Developerは要JDK。
JDKが入れてなければ、JDK同梱版（このzipファイルにはJDK1.6.0_11が含まれています）の方を選べばOK。
JDKがローカルマシンに入れてあれば Oracle SQL Developer ダウンロード からファイルをダウンロードして解凍、sqldeveloper.exeを実行するだけ。コチラは起動時にJDKのパスを求められるので、それを入れる。ちなみに、JDK 1.6.0_11以降を使うべし、と書いてあるので、それ以前のバージョンで動くかどうかは知らない。

4.0について

接続

何はともあれOracleに接続しないと何もできないので、「接続」を右クリックして「接続の作成」を選ぶとこの画面が出てくる。

この時点からしてかなり便利なんだが、接続タイプを色々えらぶことができる。おなじみのSID指定から、TNSのネットワーク別名は当然として。また、接続タイプに拡張を選ぶとカスタムJDBC URLで接続ができる。Java屋さん的には地味にありがたい。接続タイプについて→SQL DeveloperのOracle接続について調べたこと - kagamihogeの日記

ちなみにOracle XEのJDBC URLは、ローカルのマシンにOracleインストールした場合は↓な感じ。

jdbc:oracle:thin:@localhost:1521:XE

なおsysで入りたい場合はユーザー名を下記にすればよい。または「ロール」のコンボボックスをSYSDBAにして接続する。

sys as sysdba

基本画面

接続すると、とりあえずこんな感じ。左側にエクスプローラ、右側にタブとよくある構成。


クエリの作成と実行

ドラッグ＆ドロップでSQLがつくれる。Accessとかではおなじみなアレ。

SQLのキー補完。SEL..とかまで打つと候補に「SELECT * FROM」を出してくれたり、テーブル名・列名を途中まで打てば補完してくれる。また、EclipseライクにCTRL+Spaceを打てば補完候補が出てくるのもありがたい。

エディタ内に複数個のSQLが書かれてる場合、F5を押せば全部実行してくれる。また、それぞれのクエリをいっこずつ走らせたい場合は、カーソルをそのクエリ行に持っていってCtrl+Enterする。

処理結果がタクサンある場合は、50件*1ずつフェッチされる。クエリ結果のビューでスクロールしていけば、ズルズルと次の結果が表示されていく。クエリ結果のコピー＆ペーストも可能。Ctrl+Aで全件選択→エクセルにコピペが出来る。データが50件以上の場合、Ctrl+A押した段階でフェッチが走り、全件取得にいく。コピペしないならCtrl + Endで良い。行単位でコピーしたい場合、行番号が表示されているところをクリックすれば対象行が選択状態になる（複数件選択可）ので、Ctrl+Cすればクリップボードにコピーされる。


クエリの実行方法その他

ワークシート上の複数のSQLを一度に実行したい場合、下記スクショのように複数選択状態にしてCtrl+F5する。

また、下記スクショのように、サブクエリやインラインビューだけを選択してCtrl+F5して実行することも可能。

さらに、下記スクショのように、クエリの一部分を選択してCtrl+F5して実行することも可能。

SQLエディタ・コード・テンプレート

よく使うクエリやテキストを登録しておくことで、入力補完候補にその登録したテキストを出すことができる。ツール -> プリファレンス -> データベース -> SQLエディタ・コード・テンプレート、から設定する。下記の例では、TO_DATE...を頻繁に使うと想定して、登録したところ。エディタで、TOと打つ（かCtrl+Spaceを打つ）と補完候補が表示される。


スニペット

よく使うコードがある場合はスニペットも便利。表示 -> スニペット、でビューが表示される。ここからエディタにドラッグ＆ドロップすれば、コードが挿入される。

コード・テンプレートと異なるのは、スニペットはコードの断片に分かりやすい名前や説明を付けられる。また、自分用のカテゴリも追加できるので整理もしやすい。ただし、Ctrl+Spaceなどのキー補完候補には出てこない。

テンプレートもスニペットも、良く使うクエリの断片を呼び出しやすくする機能にはかわりないんで、住み分けは自分で工夫の余地がある。

ローカルのファイル

表示 -> ファイル、でファイルのナビゲータが表示される。ローカルに*.sqlなどでファイルを保存していてそれを実行したい場合などはここから呼び出せる。

SQL履歴

表示 -> SQL履歴（F8）で、下記のビューが表示される。過去やちょっと前のクエリをもっかい実行したい、という場合に重宝する。SQL履歴から、指定のクエリをエディタにドラッグ＆ドロップするか、クエリをダブルクリックすれば、エディタにそのクエリが挿入される。また、入力フォーカスのところでフィルタをかけて候補を絞り込める。

SQLのチューニング

実行計画が見たい場合、タブの上部にある「実行計画」のボタンとか適当なクエリの上で右クリック→実行計画（F10）から見れる。

自動トレース（F6）はこんな感じ。

実行計画・自動トレースで表示される項目の設定は、プリファレンス -> データベース -> 自動トレース/実行計画　で行う。なお、キャプチャ画像のように値が0ばかりだと非常に見辛いので「0個の統計をスキップ」にチェックを入れれば大分見やすくなる。*2

SQLチューニングアドバイザを押すと、他にも色々と情報が得られる（らしい）。Oracle DB側にOracle Tuning Pack（有償）がインストールされていなければならないようなので試す事ができていない。

データのエクスポート

テーブルなりクエリの実行結果なりをファイルに吐き出す機能。いわゆるエクスポートだが、なぜか「アンロード」という異様にわかりにくい名前になっている。英単語的にはunloadであってるのかもしれんけど、カタカナだとなんかすごい違和感が……*3

クエリの実行結果のところで右クリック→アンロード、エクスプローラからテーブルを選んで右クリック→アンロード。フォーマットはCSV,TXTだけでなくXLSも選べる。別テーブルに対する複数のINSERT文としても吐き出せる。


データのインポート

SQL Developerからテーブルにデータを入れる - kagamihogeの日記 テーブルへの追加・編集・削除とインポートについてはコチラ。
SQL Developerのインポート機能でken_all.csvを読み込んでみる - kagamihogeの日記 も参考になるかもしれません。

CREATE TABLEのGUI

SQL Developerの表作成機能についてのメモ - kagamihogeの日記 テーブルの作成についてはコチラ。

ファンクション

ファンクションの実行

CALLやらFUNCTIONやらで呼び出さないといけなかったりして、PL/SQLに慣れていないと、このあたりは何かとめんどくさい。その場合、エクスプローラから実行したいファンクションを右クリック⇒実行（Ctrl+F10）すると、下記のダイアログが出てきてPL/SQLブロックのテンプレートを表示してくれるので、それを適当に編集して実行すればよい。


デバッグ実行

Eclipse等IDEを使ったことがあればお馴染みの機能。下記の図では7行目に赤い丸印が付いているが、ここがブレークポイントで、この丸印の場所をマウスでクリックすればブレークポイントの設置・解除（F5）ができる。そのあと、天道虫アイコンのデバッグ実行（Ctrl+Shift+F10）すると、この行で一時停止する。あとは、一行ずつ進めたい場合はステップ実行（F8）したり、再開（F9）させたりする。


フラッシュバック


SCNを確認できる程度だけど万一の時には知っておくと便利かも。

参考：Oracle Flashback QueryをSQL Developerでつかう - kagamihogeの日記

Oracle以外のデータベースへの接続

Oracle以外のDBへも接続できる。

MySQL

ツール -> プリファレンス -> サード・パーティJDBCドライバ　の　エントリ追加で別途ダウンロードしたJDBCドライバのjarのパスを指定する。そのあと、接続の作成を開くと下記のようにMySQLのタブが増えている。ユーザ名、パスワード、ホスト名、ポートを入れて、データベースの選択ボタンを押すとその右のプルダウンメニューに一覧が表示される。これでSQL DeveloperからMySQLに接続できる。

MySQL 5で接続確認。

MySQL 5.xの場合、mysql-connector-java-5.0.4-bin.jarが必要です。これは、mysql-connector-java-5.0.4.zipに含まれています。「ヘルプ」→「更新のチェック」経由でも入手できます。(最新のMySQLドライバ5.1は使用しないでください。)
Oracle SQL Developerのインストール 1.6 SQL Developerに対するデータベースの動作保証(Oracleおよびサード・パーティ)　より抜粋

とか書いてある（2012/01/17時点）のが注意点。

SQL Server

ヘルプ -> 更新の確認からThird Party SQL Developer Extensionsを選択して次の画面でJTDS JDBC Driver を選んでインストール。

そのあと、接続の作成を開くと下記のようにSQL Serverのタブが増えているので、項目に適切な値を入力すればSQL Serverに接続できる。

SQL Server 2005 Express Editionで接続確認。でもよく考えたら単にGUIのインタフェースが欲しいだけならSQL ServerはMicrosoft SQL Server Management Studio使えばいいですよね。

Sybase - Adaptive Server Enterprise

SQL Serverの項で書いたJTDS JDBC Driverのインストールが終われば準備OK。

Adaptive Server Enterprise 15.7 Developer's Editionで接続確認。なお、コレをwindows上で適当にデフォでインストールすると、ユーザー名はsaでパスワードは空でホスト名はコンピュータ名（が勝手に入ってると思う）になっている。

なお、SQL Anyware 12は接続できないようだ。「SQL Anyware エラー-110 : アイテム'jtds000001'はすでに存在しています。」というエラーが発生する。ココ によると「SQL Developer 3.0はAdaptive Server Enterprise 12,15 のみのサポートでSQL Anywhereは対象外」とのこと。

DB2

まず、 db2jcc.jarおよびdb2jcc_license_cu.jarを適当なディレクトリに配置する。

そのあと、ツール -> プリファレンス -> データベース -> サード・パーティJDBCドライバでエントリの追加で先の2つのjarファイルを選択する。そのあと、接続を開くと下記のようにDB2のタブが増えている。あとは適切な値を入力すればDB2に接続できる。

DB2 Express-C v10.1.0.0で接続確認。

Access

任意の接続名とmdbファイルのパスを入力すれば、特に事前準備無しに接続できる。

Microsoft Access 2000で作成したmdbファイルで接続確認。

PostgreSQL

俺が試してみた範囲内では、つなげないっぽい。サードパーティJDBCドライバに追加してやればいけるかなぁ、と思ったけど、接続タブにPostgreSQLが出て来ない。Oracle SQL Developerのインストール の「1.6 SQL Developerに対するデータベースの動作保証(Oracleおよびサード・パーティ)」を見ると、サポートするDBは、DB2, Access, SQL Server, MySQL, Sybase, Teradataとあるので、公式情報的にはPostgreSQLは繋げないもののようだ（2012/01/17）

ちなみに、個人的にはPostgresSQLに接続可能なA5:SQL Mk-2 - フリーの汎用SQL開発ツール/ER図ツールも軽量なデータベースクライアントとして気に入っている。

TimesTen

TimesTenにSQL Developerから接続する - kagamihogeの日記

移行機能

いわゆるデータベース・マイグレーション。ASEのサンプルDBã‚’Oracleに移行をやってみたエントリはコチラ→Oracle SQL Developerのマイグレーション機能を使ってみる - kagamihogeの日記

その他

ちょっとしたTips的なもの。

日付やタイムスタンプ型の書式設定

デフォルトだと、11-11-01みたいに表示されるんで、これを2011/11/01みたいに書式を変えたい場合、下記の設定項目を変更する。ツール > プリファレンス > データベース > NLS で、辿れる。


日付書式 RR-MM-DD
タイムスタンプ書式 RR-MM-DD HH24:MI:SSXFF
タイムスタンプTZ書式 RR-MM-DD HH24:MI:SSXFF TZR

となってるのを、例えば下記のようにする。

日付書式 YYYY/MM/DD
タイムスタンプ書式 YYYY/MM/DD HH24:MI:SS
タイムスタンプTZ書式 YYYY/MM/DD HH24:MI:SSXFF TZR
SQLエディタの入力補完を大文字にしたい

Oracleに発行するクエリは全部大文字にしたいんや、という場合の設定。プリファレンス -> コード・エディタ -> 構文補完インサイトの「入力時に大/小文字を変更」をUpper Caseにする。

SQLワークシート上の「大文字/小文字/INITCAP Ctrl+引用符」ボタンでも随時整形ができる。整形したSQLを選択状態にして、ボタンを押すと別のルールで整形がされる。
f:id:kagamihoge:20140104164951p:plain
ボタンを一回ずつ押していったとき、どのような変化があるかのサンプルが下記。

SELECT TABLE_NAME, STATUS FROM USER_TABLES WHERE TABLE_NAME = 'HOGE';--Upper Case
select table_name, status from user_tables where table_name = 'HOGE';--Lower Case
SELECT table_name, status FROM user_tables WHERE table_name = 'HOGE';--Upper Keywords
select TABLE_NAME, STATUS from USER_TABLES where TABLE_NAME = 'HOGE';--Lower Keywords, Upper Identifiers
Select Table_Name, Status From User_Tables Where Table_Name = 'HOGE';--Initial Cap

個人的にはUpper Keywords, Lower Caseが欲しいのだがプリファレンスには無い。ただし、このボタンを順繰りに押せば結果的にそれになる（Lower Case経由Upper Keywords）ので、ブログにSQLを載せる場合はこのボタンで整形をしています。

実行結果を別タブで開きたい

下図のように、あるクエリの実行結果のタブはそのままで、別のクエリを実行したらその結果は別のタブで開きたい、などの場合。実行結果タブの左上にあるピンのアイコンをクリックすると、そのタブはピン留めされるが如く固定される。あと、タブ右クリック -> 名前変更で、タブの名前をすきなものに変えられる。

実行結果のフェッチ件数を変えたい

デフォルトでは50件ずつフェッチするが、このサイズを変えたい場合。プリファレンス -> データベース -> 拡張 の「SQL配列フェッチ・サイズ」を変更する。なお（最大500）という注意書きがあり、500より大きい数値は設定出来ないようだ。大きいテーブルをウッカリと全件取得してしまうのも困りものだということだろう。

スクリプトの出力の件数を変えたい

文の実行（Ctrl + Enter）ではなく、スクリプトの実行（F5）を実行すると「スクリプトの出力」タブにテキスト形式で出力される。この機能は、デフォルトでは5000行までしか実行結果を出力しない。下記スクショでは、1万行入っているテーブルに対し全件検索クエリをスクリプトの実行（F5）したところ。「スクリプト結果では5,000行のみがサポートされます」とあり、以降の検索結果が省略されている。

この件数の変更は、ツール -> プリファレンス -> データベース -> ワークシート の「スクリプトで印刷する最大行数」で可能。MAXは分からないが1000000000とか入力できた。

フィルタ

表データなどを一覧表示する画面にある、フィルタについて。簡単に言えば、そのオブジェクトに対して条件節を記述して表示を絞り込むためのもの。WHEREのうしろっかわを書いて表示を絞り込めると思えば分かりやすい。

下記の画面キャプチャ例は、v$parameterを開いて「NAME LIKE 'optimizer%' AND TYPE < 3」の条件でフィルタをかけてみた様子。

問い合わせ結果を列単位でコピーしたい

表計算ライクに結果が表示されるため列でコピーしたくなるのが人情というものだが、RDBの性質上、列指定でコピぺするのは難しい。ただ、エクスポートで近い動きは出来る。やり方は、まずコピペしたい列だけ表示するクエリを実行し、その結果をエクスポートするとき、フォーマットをtextにして保存先をクリップボードにする。これで列をコピペに近いことは出来る。ただ、Excelとは違うので件数が多い場合の負荷には注意したい。

もう一つのやり方。問い合わせ結果のビューの列ヘッダーをクリックすることで、その列のみ全行選択できるので、そのあとCTRL + Cでコピーできる。ただし、現在フェッチされた行のみ選択される点に注意が必要。つまり、デフォルトではフェッチ件数は50件なので、結果件数が51件以上あっても50件しかコピーされない。もちろん、問い合わせ結果が全件フェッチされていれば全件コピーされる。
f:id:kagamihoge:20150812165104j:plain

テーブルの列名取得

適当なSQLを実行したあと、問い合わせ結果の列ヘッダーをCTRL + クリックで列名を取得したい列を複数選択する。そのあと、右クリック -> 選択して列ヘッダーをコピー、をする。

f:id:kagamihoge:20150812164317j:plain

あくまでもSQL書かずにやりたい場合。表のエクスポートを選択したあと、下記画像のようにする。「DDLのエクスポート」のチェックを外し、「データのエクスポート」「ヘッダー」にチェックを入れる。フォーマット（csvで良いと思うけど）とか終了文字とか囲み文字とかはお好みで。「別名保存」はファイルに保存するのが面倒ならCtrl+Cしたのと同等な「クリップボード」にしておく。

「オブジェクトのWHERE」に「1=0」を入れる。この条件によって、表のデータは1件もエクスポートされず、ヘッダー行つまりカラム名だけクリップボードに出力される。あとは貼り付けるだけ。

複数窓

SQLを書くワークシートが複数欲しいなら、ツール -> SQLワークシートを選ぶ。

一つの接続設定からは、一つの接続しか確立できないようだ。ただし、中身は同一だが異なる接続設定を作成し、それらに対して接続を確立すれば、複数セッションで複数窓が出来る。とはいえ、複数窓の分だけOracleのセッションを確立することになるので、環境にもよるが、むやみやたらとOracleの資源を使うのはよろしくないと思われる。

3.0から3.1へ設定を移行したい

基本的には3.1をダウンロードして実行するだけ。Oracle SQL Developer 3.0から3.1へ設定のインポート - kagamihogeの日記

SQL Developerを動かすJavaのバージョンを変えたい

sqldeveloper\bin\sqldeveloper.confにSetJavaHomeという行があるのでこれの値を別ディレクトリに変えればよい。ただし当然だがサポートされてるバージョンじゃないと起動しない。試しに1.5に変えてみたが、Java version 1.5.0_14 not supported. The minimum version required is 1.6.0_04.とかエラーが出た。

jdk1.7で動く？


バージョン3.1.07.42では上記のようにサポート外との警告が出る。動くかどうかは試していない。
バージョン4.0では1.7以上となった。SQL Developer 4.0 Release Notes

トレースファイルを開く


trcファイルをドラッグ＆ドラップorファイルを開く。とりあえずファイルが見れますってだけで、さすがにTKPROFほど本格的な整形はしてくれないっぽい。

nullの表示変更


プリファレンス -> データベース -> 拡張 -> 「次のようにNull値を表示」「背景色を使用したNullの表示」を変更することで、nullを識別しやすくできる。上記の画像は、それぞれ「####ぬる####」「PINK」と指定した例。

表などオブジェクトを開くときシングル・クリックをダブル・クリックにする

プリファレンス -> データベース -> ObjectViewer の「シングル・クリックでオブジェクトをオープン」で切り替えが可能。
f:id:kagamihoge:20140116162404p:plain

SVN

SQL DeveloperのSVN機能のメモ - kagamihogeの日記

30 SQL Developer Tips in 30 Days

SQL Developer開発者が書いた30個のTips記事の翻訳。

参考にした情報源など

おまけ

これでフリーってどういうことなの……ってレベルのツールなんですが。難点が無いわけでもなく。Javaベースだからか起動が割とモッサリしてるし、メモリもそれなりに食う（この記事書くのにさわってる程度の使用で200MBくらい消費）　なので、ちょろっとSQL試したいだけのときは、sqlplusとかコマンドラインから叩くなりCSEなりの軽量ツール使ったほうがいいっぽい感じ。

ともあれタダでコレってのは中々に素晴らしいです。

*1:後述する「実行結果のフェッチ件数を変えたい」の項を参照

*2:個人的にはSQL*Plusから取得した方が見やすい。

*3:3.1からは標記はアンロードからエクスポートになっている様子。