OracleのSQLで便利だと思った9の機能

自社開発のWebエンジニアに転職して、MySQLをメインで使うようになり、
OracleのSQLで便利だと思った機能をまとめてみました。(一部、MySQLでも使える機能もあり)

01 Any述語で複数式指定

条件式でのOrは、括弧の付け忘れによるバグを誘発しますが、
Any述語やIn述語は、Orを使うのを防いでくれます。

with t(Col1,Col2,Col3) as (select 1,2,3 from dual)
select count(*) as Cnt from t
 where 2 < Any(Col1,Col2,Col3);

| Cnt |
|-----|
|   1 |

02 All述語で複数式指定

with t(Col1,Col2,Col3) as (select 1,2,3 from dual)
select count(*) as Cnt from t
 where 0 = All(Col1,Col2,Col3);

| Cnt |
|-----|
|   0 |

03 リスト形式での比較

数学のベクトルの比較と似ていて、
ExcelからDelete文を作りたい時などに使えます。

with t(Col1,Col2,Col3) as (select 1,2,3 from dual)
select count(*) as Cnt from t
 where (Col1,Col2,Col3) = ((1,2,3));

| Cnt |
|-----|
|   1 |

値を縦に揃えた、Delete文が書けます。

delete from テーブル名 where (Col1,Col2,Col3)
                           =((   1,   2,   3));

04 マルチカラムIn述語

select文を指定

with t(Col1,Col2,Col3) as (select 2,3,4 from dual)
select count(*) as Cnt from t
 where (Col1,Col2,Col3) in(select 2,3,4 from dual);

| Cnt |
|-----|
|   1 |

複数式を指定

with t(Col1,Col2,Col3) as (select 2,3,4 from dual)
select count(*) as Cnt from t
 where (Col1,Col2,Col3) in((1,2,3),
                           (4,5,6),
                           (7,8,9));

| Cnt |
|-----|
|   0 |

値を縦に揃えた、Delete文(複数行指定)が書けます。

delete from テーブル名 where (Col1,Col2,Col3)
                          in((   1,   2,   3),
                             (   4,   5,   6),
                             (   7,   8,   9));

05 dateリテラル

to_date関数のショートカット構文として使えます。

select dump(date '2019-01-01') as Exp1,
       dump(to_date('2019-01-01','yyyy-mm-dd')) as Exp2
  from dual;

|                            Exp1 |                            Exp2 |
|---------------------------------|---------------------------------|
| Typ=13 Len=8: 227,7,1,1,0,0,0,0 | Typ=13 Len=8: 227,7,1,1,0,0,0,0 |

06 TimeStampリテラル

to_TimeStamp関数のショートカット構文として使えます。

select TimeStamp '2019-01-01 13:30:01' as Exp1,
       to_TimeStamp('2019-01-02 13:30:01','yyyy-mm-dd hh24:mi:ss') as Exp2
  from dual;

|                  Exp1 |                  Exp2 |
|-----------------------|-----------------------|
| 2019-01-01 13:30:01.0 | 2019-01-02 13:30:01.0 |

07 sys.odciシリーズ

さくっとデータを作りたいときに使えます。

sys.odciNumberList

select * from table(sys.odciNumberList(1,2,3));

| COLUMN_VALUE |
|--------------|
|            1 |
|            2 |
|            3 |

sys.odciVarchar2List

select * from table(sys.odciVarchar2List('A','B','C'));

| COLUMN_VALUE |
|--------------|
|            A |
|            B |
|            C |

sys.odciDateList

select * from table(sys.odciDateList(date '2019-01-01',date '2019-02-02',date '2019-03-03'));

|         COLUMN_VALUE |
|----------------------|
| 2019-01-01T00:00:00Z |
| 2019-02-02T00:00:00Z |
| 2019-03-03T00:00:00Z |

08 フラッシュバック問合せ

誰かが誤ってテーブルデータをDeleteした時の復旧とか
データ調査で使えます。

経験上
デフォルトのUndoログの設定だと2日前ぐらいのデータまで参照できます。
(金曜のデータを土日をはさんだ月曜に参照しようとしても、ほぼ無理)

-- 2019年01月02日の13時30分01秒のデータをSelectしたい場合
select * from テーブル名 as of TimeStamp TimeStamp '2019-01-02 13:30:01';

09 無名PL/SQLブロック

テストデータをさくっと作りたいときに使えます。
MySQLに、この機能はなくプロシージャを定義しないとダメのようです。