SQL
oracle

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に、この機能はなくプロシージャを定義しないとダメのようです。