OracleのSQLについてまとめています。
OracleのSQLはANSI SQL準拠
Oracleは、データベースの人気ランキング1位のDBMSです。大規模なシステムに組み込んだときに、件数が増加してもパフォーマンスが落ちにくい点などが評価され、多くの業務システムに組み込まれています。
関連）DB-Engines Ranking – popularity ranking of database management systems
ちなみに2位はMySQL、3位はSQL Server、4位はPostgreSQLと続きます。
開発現場でも最も多く使われているDBMSと言えるでしょう。OracleのSQLはANSI SQLに準拠しています。しかし、Oracle独自の拡張も多く、他のDBMSに慣れた人には扱いづらい点もあるかも知れません。
当記事では、Oracle独自の拡張についてご紹介します。
Oracleのダミー表dual
Oracleでは、select文などでfromを省略することができません。
その場合、ダミー表のdualを使います。以下は、システム日付（SYSDATE）取得時に、fromを省略してエラーになった例です。
select SYSDATE from dual; SYSDATE ----- 23-APR-20 select SYSDATE; ORA-00923: FROM keyword not found where expected
【関連記事】
▶SQLのdualは、from省略ができないOracle用のダミー表
Oracleでは、空文字をNULLとして扱う
Oracleでは、文字列型のカラムに空文字（からもじ）が入っていると、NULLとして扱われてしまいます。
NULLは単純に文字列と比較しようとすると、扱いがめんどうなんですよね。そのため、簡易的にNULLを変換するためのNVLやNVL2という独自の関数が用意されています。
以下は、NVL2を使って、manager_idがNULLなら1、NULL以外なら0を返す例です。
select employee_ID, first_name, last_name, nvl2(manager_id, 0, 1) from HR.employees; EMPLOYEE_ID FIRST_NAME LAST_NAME NVL2(MANAGER_ID,0,1) ---------------------------- 100 Steven King 1 101 Neena Kochhar 0 102 Lex De Haan 0 103 Alexander Hunold 0 104 Bruce Ernst 0 105 David Austin 0 106 Valli Pataballa 0 107 Diana Lorentz 0 108 Nancy Greenberg 0 109 Daniel Faviet 0 110 John Chen 0
【関連記事】
▶SQLのNVL2はOracle独自のNULL比較関数 任意値を返すことができるのが特徴
SQLで条件分岐するdecode
Oracleにはcase式の簡易版とでも言うべき、decodeという独自関数が用意されています。以下は、country_idに応じて、ITならItaly、JPならJapanなどコード変換をdecode関数でおこなっている例です。
SELECT location_id,
DECODE (country_id, 'IT', 'Italy',
'JP', 'Japan',
'US', 'United States',
'Other')
FROM locations WHERE location_id < 2000;
LOCATION_ID, EXP
1000, Italy
1100, Italy
1200, Japan
1300, Japan
1400, United States
1500, United States
1600, United States
1700, United States
1800, Other
1900, Other
10 rows found.
【関連記事】
▶SQL decodeは、Oracleの条件分岐でデータ変換が可能 case式で代用できる
decodeはOracleの旧バージョン時代にまだcase式が使えなかったときに導入された関数です。最近のバージョンのOracleを使用するなら、decodeではなくcase式を使いましょう。
Oracleは正規表現マッチングが可能
Oracleには、REGEXP_REPLACEなどの正規表現ができる関数が独自に用意されています。
SELECT names "names",
REGEXP_REPLACE(names, '^(\S+)\s(\S+)\s(\S+)$', '\3, \1 \2')
AS "names after regexp"
FROM famous_people
ORDER BY "names";
結果:
names names after regexp
-------------------- --------------------
John Quincy Adams John Quincy Adams
Harry S. Truman Truman, Harry S.
John Adams John Adams
John Quincy Adams Adams, John Quincy
John_Quincy_Adams John_Quincy_Adams
5 rows selected.
【関連記事】
▶SQLの正規表現は、各DBMSが拡張機能で対応 日本語文字列の対応は苦手
Oracle以外の主要DBMSも、正規表現マッチングは独自拡張で対応しているようです。なお、Oracleの正規表現は一見便利なのですが、対象が日本語の場合、予想外のマッチングをすることがあります。
Oracleの正規表現は、ASCIIデータに対してはPOSIX標準の演算子をサポートしています。しかし、日本語など多言語拡張では、一部の正規表現演算子がサポートされていないんですね。
参考）Oracle公式ドキュメント：データベース・アプリケーションでの正規表現の使用
日本語データに関しては、「一部の正規表現は可能」という程度に考えておくと良いでしょう。
OracleのPL/SQLは手続き型の処理を記述可能
一般的なDBMSではストアドプロシジャやストアドファンクション内でのみ可能な、手続き型の処理をOracleのPL/SQLではBEGIN/ENDブロックで記述できます。
DECLARE
some_condition BOOLEAN;
pi NUMBER := 3.1415926;
radius NUMBER := 15;
area NUMBER;
BEGIN
/* Perform some simple tests and assignments */
IF 2 + 2 = 4 THEN
some_condition := TRUE;
/* We expect this THEN to always be performed */
END IF;
/* This line computes the area of a circle using pi,
which is the ratio between the circumference and diameter.
After the area is computed, the result is displayed. */
area := pi * radius**2;
DBMS_OUTPUT.PUT_LINE('The area is: ' || TO_CHAR(area));
END;
/
バッチ処理的な流れを、一般のSQLとして記述できるんですね。これはこれで便利なのですが、javaなどのプログラム部分が担当する箇所と、SQLが担当する箇所の境界があいまいになって良くない、という意見もあります。
まとめ
- Oracleは、ANSI準拠のSQLを採用しているが、独自拡張も多い
- Oracleは、PL/SQLで手続き型の処理を記述可能
- Oracleの正規表現は、日本語データに対しては対応していない演算子がある
