SQL Oracleの独自拡張はdualや空文字NULL化など特殊な面あり

OracleのSQLについてまとめています。

Oracleは、米国Oracle社が開発したRDBMSで、幅広いプラットフォームに対応しています。最新版では、クラウドや高可用性システムにも対応。

OracleのSQLの独自拡張

SQLには標準SQLに加えて各データベース管理ソフトごとの独自拡張がされています。このため、SQLごとに方言のようなものがあり、単純にコピペで別のSQL管理ソフトのコードは動かないんですね。

中でもOracleは、Microsoft SQL Serverとともに圧倒的なSQLのシェアを誇っています。他のデータベース管理ソフトなどOracleからの移植性を考慮したものが少なくありません。

【関連記事】
▶SQLとはどういうもの？ 独自拡張と標準SQLの大きな違いって、何？

Oracleでテーブル名を省略するには、ダミー表dualを使う

SQL内に「from dual」があれば、これはOracleのSQLコードだとわかるほど。fromを省略できないOracleでは、fromが不要なSQLコードではダミー表のdualを使います。

ダミー表ならではのunionを使った連番生成なども可能です。

select 1+1 from dual;
1+1
-----
2

select rownum from (
  SELECT 1 FROM dual UNION ALL
  SELECT 1 FROM dual UNION ALL
  SELECT 1 FROM dual UNION ALL
  SELECT 1 FROM dual UNION ALL
  SELECT 1 FROM dual UNION ALL
  SELECT 1 FROM dual UNION ALL
  SELECT 1 FROM dual UNION ALL
  SELECT 1 FROM dual UNION ALL
  SELECT 1 FROM dual UNION ALL
  SELECT 1 FROM dual);

ROWNUM
-----
1
2
3
4
5
6
7
8
9
10

【関連記事】
▶SQLのdualは、from省略ができないOracle用のダミー表

OracleとMySQLのSQLの違い

OracleのSQLは標準SQL準拠の部分では他のSQL管理ソフトと互換性があるものの、独自拡張の部分では仕様が異なります。

特に同じ関数名で使い方も機能も違うような場合は混乱のもとになります。

例えば、DECODEという関数はOracleでは引数を比較して比較結果次第で異なる値を返す関数です。

SELECT
DECODE( table1.flg, 1, 'OK', 'NG')
FROM table1;

ところが、MySQLのDECODE関数は、暗号化をおこなうための全く異なる関数なんですね。

【関連記事】
▶実績のあるSQLなのに動かない？移植で問題になりやすいDEOCDEについて解説します

このため、OracleのSQLをMySQLで動くようにするためには独自拡張の部分を書き換える必要があります。例えば、OracleのDECODEはMySQLではCASE文を使って書き換えが可能です。

SELECT
CASE table1.flg
WHEN 1 THEN 'OK'
ELSE 'NG' END
FROM table1;

Oracleは空文字をNULLとして扱う点が特殊

Oracleが他のSQL管理ソフトと比べて独特なのは、NULLの扱いでしょう。

空文字をNULLとして扱う点や、NULLに数値を加算すると結果がNULLになる点など初見だとハマってしまいそうな仕様となっています。

【関連記事】
▶SQL isnullの使い方 NULL判定やNULL置換の方法 case式でも対応可能

Oracleでは特に意識して、NULLを別の値に変換することが重要と言えるでしょう。テーブルがNULL値の場合に別の値を返す関数として、NVL()やNVL2()が用意されています。

NVL2は、指定した値がNULLだった場合とNULL以外の場合で返す値を変えることができる関数です。第一引数がNULL以外なら第二引数を、NULLなら第三引数を返します。

select nvl2(NULL,1,2) from dual;
NVL2(NULL,1,2)
-----
2

select nvl2(1,1,2) from dual;
NVL2(1,1,2)
-----
1

OracleのSQLがGUIから扱えるSQL Developerは管理者用に最適

SQL Developは、GUIを使ってOracleにアクセスできる開発ツールです。カラム名やSQLコードの打ち込み間違いを防げるため、Oracle未経験者のトレーニングに最適です。

【関連記事】
▶SQL Developerを使ってみよう！資格取得の学習にも使える便利なツール

テーブル生成や、既に作成済みのテーブル情報を取り込んで図示化するリバースエンジニアリング機能、簡易的なSQL整形機能も付属しています。Oracleサーバのコンソールにインストールしておくことで、管理者用のツールとしても使えます。

なお、SQL DeveloperはOracleだけではなくMySQLなどの他のSQLにもアクセス可能です。例えば、１つのSQL DeveloperからOracleとMySQLの管理をすることも可能です。

【関連記事】
▶SQL Developerから、MySQLへ接続する方法　エクスポートや整形の使い方は？

OracleのSQL Loaderは、簡易的なデータ取り込みツール

Oracleには、SQL*Loaderというデータ取り込みツールが用意されています。制御ファイルを記述することで、他のシステムからファイルにエクスポートしたファイルを簡単にデータベースに取り込むことができるんですね。

データ取り込み用のプログラム開発が不要な上、制御ファイルで定義した内容や取り込み先のテーブルのデータ型に合わない場合はデータを除外した上「不良レコード」としてログ記録してくれます。

例えば、以下の制御コードを記述することで、カンマ区切りのcsvファイルを取り込むことが可能です。

以下の例では、カンマで区切られたexample.datファイルを、第一カラム、第二カラムとしてexampleテーブルにロードします。

load data
infile 'example.dat'  "var 3"
into table example
fields terminated by ',' optionally enclosed by '"'
(col1 char(5),
 col2 char(7))

取り込み対象のファイルは以下のようになります。

example.dat:
009hello,cd,010world,im,
012my,name is,

【関連記事】
▶SQL LoaderはOracleユーティリティ 制御ファイル定義でお手軽データロード可能

制御ファイルの書き方次第で、固定長ファイルにも対応でき、メインフレームから切り出したファイルを取り込むことも容易です。

まとめ

ポテパンダの一言メモ

• Oracleの独自拡張は、他のデータベース管理ソフトと互換性がない
• OracleNULLの扱いが特殊で、空文字がNULL扱いになる
• Oracleに無償で付属してくるSQL Developerは管理者用ツールとして最適