スポンサーリンク

060-SQL

これで安心!すぐに忘れるOracle「オラクル」SQLの忘備録!

目次

SQLはすぐに忘れて中々覚えられない!

管理はSQLは良く使いますが、毎日というわけではありませんので毎回すぐ忘れます。

使おうと思ったら「アレ・・・?」となり毎回「Google先生」で検索です。

これが結構面倒で案外時間がかかるのです。

そこで、管理人がこれまでの経験で良く使用する「SQL文」をこの記事にまとめました。

また、あまり使用頻度が少ない「SQL文」も参考程度に掲載しております。

ちなみに、管理人の職場のデータベースは、「Oracle」ですので、原則「Oracle」で使えるSQL文のみとなります。

「DB2」や「MicrosoftSQLServer」や「MySQL」には対応していないSQL文もあり、また「Oracle」でも全てバージョンで機能するとは限りませんので、ご注意ください。

スポンサーリンク

この記事の主な内容について

SQLは、主に

FROM → WHERE → GROUP BY → HAVING → SELELT → ORDER BY

の順に読み込みますので、概ねその順でSQL文を紹介しています。

関連するSQL文、構文、関数等について調べたい場合は、「CTRL」+「F」キーで検索してください。

ピボットテーブル

一部のSQL文で、カラムが1つだけのピボットテーブル(pivot. table)を使用しています。
ピボットテーブルを作成する場合のSQL文は、次のとおりです。

ピボットテーブルを作成するSQL文

 
CREATE TABLE pivot ( 
 x NUMBER
 ); 

サンプルテーブルと各テーブルの相関関係について

この記事で度々出てくるSQLの文例で使っているサンプルテーブルの内容は次のとおりですので参考にしてください。

尚、このサンプルテーブルに記載されていないカラムを例文で使用する場合も有ります。

テーブル例1・自動車

テーブル1:自動車メーカー(maker)
テーブル2:自動車名テーブル(automobile)
テーブル3:部品テーブル(buhin_table)

各テーブルの主キー・外部キー

主キー外部キー
makerautomobile
1N
maker_idmaker_id

各テーブルの主キー・外部キー

主キー外部キー
automobilebuhin_table
1N
car_idcar_id

テーブル1:自動車メーカー(maker)

テーブル名maker
カラムmaker_id
カラムmaker_name
カラム・・・

テーブル2:自動車名メーカー(automobile)

テーブル名automobile
カラムcar_id
カラムmaker_id
カラム・・・

テーブル3:部品テーブル(buhin_table)

テーブル名buhin_table
カラムbuhin_id
カラムcar_id
カラム・・・

テーブル例2・バイク

テーブル1:バイクメーカー(maker)
テーブル2:バイク名テーブル(bike)
テーブル3:部品テーブル(parts)

各テーブルの主キー・外部キー

主キー外部キー
makerparts
1N
maker_namemaker_name

各テーブルの主キー・外部キー

主キー外部キー
bikeparts
1N
bike_idbike_id

テーブル1:バイクメーカー(maker)

テーブル名maker
カラムmaker_id
カラムmaker_name
カラム・・・

テーブル2:バイク名(bike)

テーブル名bike
カラムbike_id
カラムbike_name
カラム・・・

テーブル3:バイクパーツ(parts)

テーブル名parts
カラムparts_id
カラムbike_id
カラムmaker_name
カラム・・・

テーブル例3・世帯属性

テーブル1:個人テーブル(kojin_place)
テーブル2:世帯テーブル(setai_place)

各テーブルの主キー・外部キー

主キー外部キー
kojin_placesetai_place
1N
setai_nosetai_no

テーブル1:個人テーブル(kojin_place)

テーブル名kojin_place
カラムseiri_no
カラムsetai_no
カラムstart_ymd
カラムend_ymd
カラム・・・

テーブル2:世帯テーブル(setai_place)

テーブル名setai_place
カラムsetai_no
カラムseiri_no
カラムzokusei
カラムstart_ymd
カラムend_ymd
カラム・・・

テーブル例4・自動車部品

テーブル1:パーツグループテーブル(assy)
テーブル2:自動車部品テーブル(car_of_parts)

各テーブルの主キー・外部キー

主キー外部キー
assycar_of_parts
1N
parts_idparts_id

テーブル1:パーツグループテーブル(assy)

テーブル名assy
カラムassy_id
カラムparts_id
カラムparts_name
カラムmaker_name
カラム・・・

テーブル2:自動車部品テーブル(car_of_parts)

テーブル名car_of_parts
カラムassy_id
カラムparts_name
カラムparts_no
カラムupper_parts_no
カラムmaker_name
カラム・・・
スポンサーリンク

Oracle(オラクル)でデータ選択する

「SELECT文」・「クエリ(query)」

Oracle(オラクル)から(「テープル」・「ビュー」・「テーブルとビューの組み合わせ」)等を抽出する場合は、「SELECT文」・「クエリ(query)」を使用します。

SELECT式リスト
 FROM データソース
 WHERE 述部

GROUP BY 式リスト
HAVING 述部

ORDER BY 式リスト

結果として「SELECT文」は行とカラム(列)を抽出します。

この結果を、「result set」と言います。

Oracle(オラクル)のSELECT句

「SELECT句」内のそれぞれの式は、SQL文が抽出する結果(カラム)となります。

「SELECT句」内の式は

①単純なカラム名
②入力したカラム値から新しい値を生成する
③どのカラムとも全く関係ない場合
④その他

があります。

Oracle(オラクル)でカラム名を設定

「SELECT句」で、SLQ文で抽出してほしい個々のデータ要素を記述します。

「式リスト」のそれぞれの項目は、SQL文で抽出される結果(カラム)となります。

「FROM句」内の複数の各テーブルを(,)カンマで区切リ記述します。

列記した最後のテーブルには、(,)を付けませんので注意してください。

例文

SELECT bike_name, kakaku
FROM bike
;

このSQL文を実行すると、次の結果(カラム)が抽出されます。

bike_namekakaku
ZX-10R1700000
1290SDR2000000
GSX-S10001200000
Ninja10001300000

抽出されたそれぞれの結果(カラム)には名前が付きます。

例文では、 データベースに元々設定されているカラム名がカラム名として抽出されます。

Oracle(オラクル)でアスタリスクで略記(ワイルドカード)する

テーブルのすべてのカラムを抽出したいときは、「*」アスタリスクのみを記述します。

例文

SELECT * FROM bike;
bike_idmaker_namebike_namekakaku
001KAWASAKIZX-10R1700000
002KTM1290SDR2000000
003SUZUKIGSX-S10001200000
04KAWASAKINinja10001300000

SQL文を実行するときに「*」アスタリスクを使用すると、カラム名の入力を省略できます。

しかし、プログラムコードでアスタリスクを使用する場合は、テーブルのカラムが変更される場合もある為、注意が必要です。(使用しない方が無難)

プログラムで設定されているカラム数と「*」で抽出されるカラムの数が異なるとエラーとなります。

また、「*」アスタリスクを使用すると、全てのカラムをクライアントに返すので、ネットワーク帯域幅を圧迫します。

この為、プログラムにSQL文を記述する時には一般的に「*」を使わずカラムを明示します。

また、次の例文の様にテーブル名に、アスタリスクまたはカラム名を付けることもできます。

例文

SELECT b.*, p.maker_name
FROM bike b, parts p
WHERE b.bike_id = p.bike_id
; 

「SELECT文」内の項目が、特定のカラムと「*」アスタリスクで全カラムを抽出したい場合は、上記のSQL文の様に記述します。

カラムは、何度でも明示することができます。。

次のSQL文は、「bike_id」を2回抽出します。

1回目は、明示的にカラムを「SELECT」のリストに並べ(bike_id式)、

2回目は「bikeテーブル」のすべてのカラムを返す「cd.*」を記述しています。

例文

SELECT bike_id, bike.*
FROM bike
; 

MS-ACCESSの「Query」をGUIで作成すると思わず、例文の様になることが多いですが、実際にSQL文を打ち込む場合に、この様な記述をする事はあまり無いと思います。

Oracle(オラクル)で式を記述

計算式等の中でカラム名を使用することができます。

次のSQL文は、価格(price)を15%割引しています。

例文

SELECT bike_name, kakaku * 0.85
FROM bike
;

Oracleでは多数の関数を利用できます。

次のSQL文では、「SELECT文」内に「ROUND関数」を使用して、割引後の価格を小数3桁で四捨五入しています。

例文

SELECT bike_name, ROUND (kakaku * 0.85,3)
FROM bike; 

Oracle(オラクル)でのdual

Oracleでは、「dual」という名前の特殊なテーブルに対してSQL文を実行し、現在の日時を抽出することができます。

例文

SELECT SYSDATE
FROM dual
;

Oracleの「dual」は、1行だけのテーブルです。

OracleはSQL文が返した行ごとに式を実行しますので、上記のSQL文は1行且つ1つの値だけを抽出します。

次の例文の様に「bikeテーブル」に対してクエリを実行し、結果の全ての行に現在の日時を抽出することもできます。

例文

SELECT bike_name,
    ROUND (kakaku * 0.85,2) price, 
    SYSDATE
FROM bike
;

Oracle(オラクル)で結果(カラム)に別名を付ける

「SELECT文」の結果(カラム)には、名前が付いています。

次は、SQL文とその結果(カラム)を示しています。

例文

SELECT bike_name, ROUND (kakaku * 0.85,2)
FROM bike
;

結果

bike_nameROUND (PRICE * 0.85,2)
ZX-10R1455000
GSX-S10001020000

プログラムでカラムにアクセスするときにはカラム名を使用することが多いので、カラムの名称は非常に重要となります。

例えば、「Javaプログラム」で「JDBC」を使用するには、現在の結果の行から、次の様に「bike_name」を取得できます。

例文

bike_name = rslt.getString("bike_name");

例文の様にカラム名を扱うのは容易ですが、次の様にコードを書きたい場合はどうでしょう?

例文

kakaku = rslt.getString("ROUND (kakaku * 0.85,2)");

このコードでは、割引率を変更した場合にエラーとなります。

この問題を避ける為には、SQLでは「SELECT文」内のカラムに「別名」を設定します。

別名を設定するには、式の直後に「AS」で区切り、別名を記述します。

次のSQL文は、カラムと式に別名を付けています。

例文

SELECT bike_name AS name,
    ROUND (kakaku * 0.85,2) AS kakaku
FROM bike
;
namekakaku
ZX-10R1455000
GSX-S10001020000

但し、「bike_name」の様な単純なカラム名に別名をつけることは、一般的にはありません。

しかし、「kakaku」カラムに適用している「ROUND」の様な式を使う時は、別名を使用する方が分かり易くなります。

別名をつけておけば、仮に式に変更が起こっても、エラーとなることを防止できます。

例えばこのSQL文では、どんな価格設定の式を使っても、カラム名は常に「kakaku」となりますので、割引率が変更となってもエラーとなることはありません。

Oracle(オラクル)で名前で大小文字と句読点を扱う方法

Oracle(オラクル)のSQL文は、大文字と小文字を区別しません。

例えば、テーブル名とカラム名の場合は、常に大文字に変換して実行されます。

例文

SELECT bike_name AS name,
    round (kakaku * 0.85,2) AS kakaku
from bike
; 

しかし、Oracle(オラクル)では次の様に扱われます。

例文

SELECT BIKE_NAME AS NAME, 
    ROUND (KAKAKU * 0.85,2) AS KAKAKU
FROM BIKE
;

二重引用符で囲めば、大文字と小文字を区別できます。

次のSQL文では、二重引用符を使用して、大文字と小文字を混ぜた別名を付けています。

別名に空白を入れるには、二重引用符で囲みます。

例文

SELECT bike_name AS CD "Name",
    ROUND (kakaku *  0.85,2) AS "Kakaku"
FROM bike
;
NameKakaku
ZX-10R1455000
GSX-S10001020000

カラム名とテーブル名に大文字と小文字、空白、特殊な文字を混ぜ込むには、この機能を使用します。

例えば、マイクロソフトのACCESSで、次のテーブルを作成することを想定すると・・・

例文

CREATE TABLE "BIKE Table" (
    "BIKE ID" NUMBER,
    "BIKE Name" VARCHAR2 (35),
    "Kakaku" NUMBER (4,2),
    "maker" VARCHAR2 (15),
CONSTRAINT
    PRIMARY KEY ("BIKE ID"),
CONSTRAINT
    UNIQUE ("BIKE Name"),
CONSTRAINT
    FOREIGN KEY ("maker")
    REFERENCES maker)
;

この様なややこしいカラム名を扱うのは、かなり大変だと思います。

このテーブルのSQL文を簡単にするには、IDをクォートします。

例文

SELECT "BIKE Name" AS name, 
    ROUND ("Kakaku" * 0.85,2) AS kakaku
FROM "BIKE Table"
; 

この様な問題を避けるには、できる限り単純な名前をカラムに付けるべきです。

Oracle(オラクル)の「SELECT文」でサブクエリを使用する

サブクエリを「SELECT文」に埋め込むことができます。

しかし正確に1つの行と1つのカラムを抽出することが、サブクエリは求められます。

サブクエリを「SELECT文」で記述するには、括弧でサブクエリを囲みます。

また、結果のカラム名が単純になる様に別名をカラムに設定したほうがコードからカラムを簡単に参照できます。

例えば、次のSQL文は、それぞれのバイクのパーツ数を抽出します。

例文

SELECT bk.bike_name,
    ROUND (bk.kakaku * 0.85,2) AS kakaku,
    (SELECT COUNT(*) 
     FROM parts pa
     WHERE pa.bike_id = bk.bike_id) AS kazu_parts
FROM bike bk
;

サブクエリには相関が「有る場合」も「無い場合」もあります。

サブクエリには相関がありますので、このSQL文では取り込んだテーブルを参照します。

Oracle(オラクル)でカラム名を修飾する

カラム名にテーブル名をつけて参照先のテーブルを明言することができます。

2つのテーブルに同じ名前のカラムが存在することがあります。

この為、複数のテーブルを扱うSQL文を書くときには、参照先のテーブルを明言することがが特に重要となります。

カラムの参照先のテーブルを明言するには、「テーブル名.カラム名」の様にドット表記を使用します。

例えば次の様に記述します。

例文

SELECT bike.bike_name, 
    ROUND(bike.kakaku * 0.85,2) AS kakaku
FROM bike
; 

カラムの参照先のテーブルを明言する場合に、テーブル名が結果のカラム名の一部となることはありません。

このSQL文では、バイク名のカラムは「bike.bike_name」ではなく「baike_name」という名前のままとなります。

しかし、ラウンド式には「bike.kakaku」と記述しているので、式の結果のカラム名には、「bike.」が付いてしまします。

こういう場合には、カラムに別名を付けて対応します。

Oracleでは、カラムの参照先のテーブルを明言するとき、さらに「テーブルの参照先のスキーマ名」を明言することもできます。

例文

SELECT test.bike.bike_name
FROM test.bike; 

このSQL文は、「test」スキーマの「bike」テーブルからタイトルを抽出します。

Oracle(オラクル)でのALLとDISTINCT

「ALL」と「DISTINCT」又は「UNIQUE」を使えば、「SELECT」の操作で、重複した行を結果から取り除くかどうかを選択できます。

Oracle(オラクル)ですべての行を取得

「ALL」を使用すれば「SELECT文」のデフォルトの動作となります。

「WHERE句」と「HAVING句」で設定した条件を満たす全ての行を抽出することが、デフォルトの動作です。

例えは、次の2つのSQL文は同等であり同じ結果となります。

例文

SELECT ALL kakaku
FROM bike
; 
SELECT kakaku 
FROM bike
; 

「ALL」は、「SELECT」の直後に記述する必要があります。

Oracle(オラクル)で結果から重複した行を削除する

「DISTINCT」を使用すれば「SELECT文」で、重複した行を削除できます。

例えば、バイクの価格を調べるには、次の様なSQL文を実行します。

例文

SELECT kakaku
FROM bike
;
kakaku
1000000
1200000
1300000
1300000
1300000

このSQL文では、テーブル内のすべての価格を出力しています。

130万円が3回出力されています。

仮に100種類のバイクの価格が130万円の場合は、同じ価格が100回も表示されます。

バイクの価格帯を知りたいだけなら、同じ価格を何度も表示させる必要はありません。

「DISTINCT」使えば結果の重複を削除できます。

例文

SELECT DISTINCT kakaku
FROM bike
; 
kakaku
1000000
1200000
1300000

複数のカラムで「DISTINCT」を設定する場合は、カラムの組み合わせはユニークである必要があります。

例えば、次のSQL文は、「bike_name」と「kakaku」について重複しない結果を抽出します。
例文

SELECT DISTINCT baike_name, kakaku
FROM bike
; 

但し、「DISTINCT」を使用するとレスポンスが悪くなる場合があります。

「DISTINCT」は結果から重複した行を取り除きますが、その際にはデータのソートが必要となります。

例えば「DISTINCT」を「WHERE句」と「HAVING句」で設定した際は、結果の行を全て精査しないと重複した行を削除することができません。

つまり全ての結果が出るまでは、アプリケーション側に結果を返すことはできない事となります。

その為、Oracleは、結果を1時的に保持し、その結果をソートした後に、重複した行を削除しなければなりません。

結果を精査、ソート後に重複を削除してから、ようやくその結果をプリケーションに返し始めることができる為、時間が掛かりレスポンスが落ちます。

Oracle(オラクル)でのFROM句

「FROM句」で取り出したいデータのソースを設定します。

方法は、「SELECT文」の「FROM句」で、1つのテーブルまたはビューを設定することです。

例文

SELECT buhin_name
FROM  buhin_table //テーブルの場合
WHERE car_id=10
; 
SELECT *
FROM car_buhins. //ビューの場合
WHERE car_name='zc33s'
; 

別ユーザが所有している場合や、別スキーマにある場合でも、Oracleではテーブルまたはビューを選択することができます。

その場合は、「.」ドットを使用して、テーブル名またはビュー名が参照するスキーマ名を明言します。

例文

SELECT *
FROM betsu_user.car_buhins //ビューの場合
WHERE car_name='zc33s'; 

このSQL文は、「betsu_user」というユーザが所有する「car_buhins」というビューからカラムを抽出します。

Oracle(オラクル)のFROM句でテーブルに別名を設定する

「FROM句」でテーブルの式を書くとき、テーブルに別名を設定することができます。

例えば、テーブル別名を使用しないで次の様に記述すると・・・

例文

SELECT buhin_table.buhin.name, buhin_table.buhin_url
FROM buhin_table
WHERE buhin_table.car_id = 20
; 

SQL文が無駄に長くなってしまいます。

しかし、テーブルに別名を使用すれば、次の様に記述できます。

SELECT bt.buhin.name, bt.buhin_url
FROM buhin_table bt
WHERE bt.car_id = 20
; 

例えば、結合またはサブクエリで、カラム名が不明瞭になるような場合には、テーブルの別名設定は不可欠となります。

次のSQL文は、部品名のリストを返し、同じ自動車IDの部品の個数も抽出します。

例文

SELECT a1.buhin_name, 
    (SELECT count(*) 
     FROM buhin_table a2 
     WHERE a2.car_id = a1.car_id) AS cnt
FROM buhin_table a1
;

この例文では、テーブル名が同じなので、別名を使わずに記述することはできません。

テーブルに別名を使用したので、「buhin_table」を2回参照することができるのです。

Oracle(オラクル)のFROM句でサブクエリを使用する

「FROM句」でサブクエリを使う事もできます。

この様なサプクエリはインラインビュー(inline view)と呼ばれます。

相関があってはいけませんので、サブクエリでは主要なクエリのカラムは参照できません。

例えば、次のSQL文は、全ての自動車名を返し、さらに部品のメーカー名が「MITSUBISHI」であるものを抽出します。

例文

SELECT c.car_name, a.buhin_name
FROM automobile c 
    LEFT OUTER JOIN 
        (SELECT * 
         FROM buhin_table
         WHERE maker_name = 'MITSUBISHI') a
    ON c.car_id = a.car_id; 

メーカー名が「MITSUBISHI」である部品の一時的なテーブルを、このサブクエリで作成します。

そのテーブルを、「automobile」テーブルに結合します。

例文がやや難解ですが、結合は左外部結合ですので、(メーカー名が「MITSUBISHI」である部品が無い自動車名)も結果に表示されます。

Oracle(オラクル)のFROM句で集合を使用する

Oracleバージョン8以降では、ネストしたテーブルカラムと「VARRAY(可変サイズの配列)」カラムが使用できます。

集合型については、例えば次の様なSQL文を実行できます。

例文

SELECT bk.bike_id, pt.parts_no, pt.parts_name
FROM bike bk, TABLE (parts_list) pt
WHERE bk.bike_name='ZX-10R'
; 

…..

bike_idparts_noparts_name
101tire
102brake
103wheel
104clatch
105cable

このSQL文で、「FROM句」の「TABLE関数」は、「bike」に結合するテーブルとネストしたテーブルカラム「parts_list」を別のものとして扱います。

結合条件は暗黙的ですので、「bike」の各行に、「parts_list」カラムのすべての部品を結びつけます。

Oracle(オラクル)のFROM句でパーティションとサブパーティションを使用する

Oracleバージョン8以降で、パーティションテーブルを使用できます。

次の様に、特定のパーティションにSQL文を実行して、そのパーティションだけの行を返すことができます。

例文

SELECT *
FROM maker PARTITION (MITSUBISHI)
;

サプパーティションにSQL文を実行するには、「SUBPARTITION」キーワードを使用します。

例文

SELECT *
FROM maker SUBPARTITION (MITSUBISHI01)
WHERE maker_id = 10
; 

Oracle(オラクル)のWHERE句

「WHERE句」を使用すれば、SQL文の結果を、限定できます。

例えば、次のSQL文は、部品名の全てを抽出します。

例文

SELECT parts_name
FROM parts
;

通常は、何らかの条件により特定の行だけを抽出する場合が多く、テープルの全ての行を表示することはありません。

次のSQL文は、自動車名が「MIRAGE」である「MITSUBISHI」製の部品名だけを抽出しています。

例文

SELECT parts_name
FROM parts
WHERE maker_name = 'MITSUBISHI' 
    AND car_id IN(
        SELECT car_id
        FROM automobile
        WHERE car_name='MIRAGE')
;  

このSQL文では、等価条件の(=)を使用して「MITSUBISHI」製かどうかを判断し、(IN)を使用して自動車名が「MIRAGE」かどうかを判断しています。

Oracle(オラクル)のORDER BY句

「ORDER BY」を使用すれば、抽出した結果をソートできます。

例えば、自動車名でソートし、次に部品名でソートするには、次の様に記述します。

例文

SELECT c.car_name, a.buhin_name
FROM buhin_table a INNER JOIN automobile c
        ON a.car_id = c.car_id
ORDER BY c.car_name, a.buhin_name
;

特に何も設定しない場合は、昇順のソートになります。

それぞれのカラムのソート順を変更するには、「ASCENDING(昇順)」と「DESCENDING(降 順)」を使用します。(略記「ASC」・「DESC」)。

次のSQL文は、上記のSQL文と同等だが、自動車名・部品のメーカー名・部品名の順にソートしています。

例文

SELECT c.car_name, a.buhin_name
FROM buhin_table a INNER JOIN automobile c
    ON a.car_id = c.car_id
ORDER BY c.car_name,
     a.maker_name ASC,
     a.buhin_name ASC
; 

「SELECT文」にないカラムでもソートできることを、このSQL文は示しています。

「ORDER BY」を使えば、次のSQL文の様に、サプクエリの結果でソートすることも可能です。

例文

SELECT c.car_name, a.buhin_name
FROM buhin_table a INNER JOIN automobile c 
            ON a.car_id = c.car_id
ORDER BY (SELECT COUNT(*),
     FROM buhin_table a2
     WHERE a2.car_id = a.car_id) DESC,
    c.car_name,
    a.maker_name ASC,
    a.buhin_name ASC
; 

このSQL文の「ORDER BY句」は、相関のあるサブクエリの結果を次の順でソートしています。

部品の数(降順)・自動車名・部品のメーカー名・部品名の順で表示される。

スポンサーリンク

Oracle(オラクル)での述部「WHERE・HAVING」

SQL文の「WHERE句(およびHAVING句)」で書く条件が、述部(predicate)でありその述部が作用する行と抽出する行を決定します。

述部のSQL文は次のとおりです。

SELECT parts_name
FROM parts
WHERE maker = "KAWASAKI'
; 

「maker = ‘KAWASAKI’」が、このSQL文の述部であり、「KAWASAKI」製による部品名だけを抽出しています。

使用できる比較演算式の一覧は次のとおりです。

比較演算子機説明
!= ・ <> ・ ^=等しくないかどうか
<小さいかどうか
<=以下かどうか
=等しいかどか
>=以上かどうか
BETWEEN指定の範囲に値が入るかどうか
EXSISTS指定の条件に一致する行が存在するかどうか
IN指定の一組の値またはサブクエリが返した一組の他に値が含まれるかどうか
IS (NOT) NULLNULLかどうか
LIKE値がパターンに値が一致するかどうか
REGAP_LIKE正規表現で記述したパターンに値が一致するかどうか

Oracle(オラクル)の述部でのグループ比較「ANY・SOME・ALL」

比較演算式(「BETWEEN」の前に並んでいるもの)と一緒に、「ANY」・「SOME」・「ALL」を使用することで、1つの値を1組の値と比較することができます。

例文

SELECT parts_name
FROM parts
WHERE bike_id = ANY(2, 5)
; 

または、次のSQL文でも良い。

SELECT parts_name
FROM parts
WHERE bike_id = ANY
    (SELECT bike_id
    FROM bike WHERE kakaku <= 1,500,000)
;

値を明示的に並べたリストと比較するために最初のSQL文では「ANY」を使用しています。
少なくとも1つの値が真になるべきだという条件を「ANY」で定めます。

「SOME」は 「ANY」と同等の結果を抽出します。

「ALL」を使用すれば、すべての値が真になるべきだという条件となります。

Oracle(オラクル)で左側に複数の値を設定する

Oracleでは、比較の左側に複数の値を設定できます。

例えば、各バイクの中で部品の長さ (parts_length)が最も長いものに一致する部品を全て抽出するには、次の様に記述します。

例文

SELECT s1.bike_id, s1.parts_length, s1.parts_name
FROM parts s1
WHERE (s1.parts_id, s1.parts_length) 
    = (SELECT parts_id, MAX(parts_length) 
        FROM parts s2. 
        WHERE s1.bike_id = s2.bike_id)
; 

単純な比較演算式だけを使用すると、このSQL文の様にサブクエリは1つの行を抽出します。

複数の値を設定するには、「ANY」・「SOME」・「ALL」を使用します。

例文

SELECT bike_id, parts_length, parts_name
FROM parts
WHERE (bike_id, parts_length) = ANY((500),(600))
; 

または、1つ以上の値を返すサブクエリを使用してもよいです。

例文

SELECT bike_id, parts_length, parts_name
FROM parts
WHERE (bike_id, parts_length)
    = ANY (SELECT bike_id, MAX (parts_length) 
     FROM parts GROUP BY bike_id)
;

このSQL文も、それぞれのバイクの中で最も長い部品一致するものをすべて抽出します。

管理人は、前述の例文よりもこちらの方が理解し易いです。

このSQL文ではサブクエリに相関はない為、すべての候補のリストを一度に抽出します。

Oracle(オラクル)の「EXISTS・NOT EXISTS」の述部

「EXISTS」と「NOT EXISTS」を使用すれば、設定した一組の条件に一致する行が存在するかどうかを判断できます。

例えば、「KAWASAKI」によるパーツが少なくとも1つ入っているバイクを全て見つけるには、次の例文の様に記述します。

例文

SELECT bike_id, parts_name
FROM bike
WHERE EXISTS (SELECT * FROM parts
        WHERE bike.bike_id = parts.bike_id
        AND parts.maker_name = 'KAWASAKI')
; 

このSQL文で、逆に「NOT EXISTS」に置き換えれば「KAWASAKI」の部品を使っていないバイクをすべて見つけることができます。

一般に、「EXISTS」の述部で使用しているサプクエリは相関があります。

このSQL文のサブクエリは、バイクテーブルの中から現在のバイクのすべての部品を調べます。

Oracle(オラクル)のINの述部

値が、一組の値の中にあるかどうかをテストするときに、「IN」を使用します。

一組の値を、サブクエリの結果として抽出することも、リテラル値のリストとして列挙しすることもできます。

次のSQL文は、一組のリテラル値を列挙しています。

例文

SELECT bike_id, parts_no, parts_name
FROM parts
WHERE bike_id IN (5,10)
ORDER BY bike_id, parts_no
;

次はサブクエリを使用した例文です。

これは、前述の「EXISTS」を使用したSQL文を、「KAWASAKI」による部品を含むバイクのリストを返すよう書き換えたもです。

例文

SELECT bike_id, parts_name
FROM bike
WHERE bike_id IN 
    (SELECT bike_id
     FROM parts
     WHERE maker_name ='KAWASAKI')
;

但し、「NULL」には気を付けてください。

「IN」の述部で使用したサブクエリが、たとえ1行についてでも「NULL値」を抽出したとすれば、「IN・NOT IN」の結果は決して真になりません。

常に「NULL」になってしまい、SQL文はエラーとなり思ったように作動しません。

Oracle(オラクル)のBETWEENの述部

「BETWEEN」を使用すれば、値が特定の範囲に入るかどうかを判定する事ができます。

例えば次の様に記述します。

例文

SELECT parts_name
FROM parts
WHERE parts_length
BETWEEN 300 AND 500
; 

「<=」と「=>」の比較演算子機を使用して「BETWEEN」と同等の結果を抽出する事ができます。

例文

SELECT parts_name
FROM parts
WHERE parts_length >= 300
AND parts_length <= 500
;

「BETWEEN」で述部を記述するときは最少値を最初に記述する必要がありますので注意してください。

Oracle(オラクル)の「LIKE・NOT LIKE」の述部

パターンマッチングを判定するには、「LIKE」と「NOT LIKE」を述部に記述します。

パーセント (%)とアンダーバー(_)を使用することで、「LIKE」は、任意個の文字または1文字に一致するかどうかを判定します。

例えば、 部品名に「tire」という単語が含まれている部品をすべて見つけるには、次の様 に記述します。

例文

SELECT parts_name
FROM parts
WHERE parts_name LIKE '%tire%'
; 

代わりに「NOT LIKE」を使用すれば、部品名に「tire」が含まれていない部品を全て見つける事ができます。

エスケープ文字を使用することで、パターンマッチングに設定する文字を、その文字どおりに使用する事ができます。

例えば、部品名に「%」を含まない部品すべて見つけるには、次の様に記述します。

例文

SELECT parts_name
FROM parts
WHERE parts_name NOT LIKE '%/%%' ESCAPE '/'
;

また、「LIKEC」・「LIKE2」・「LIKE4」も使用できます。

これらは、それぞれ「Unicodeの文字・UCS2・UCS4」を扱います。

Oracle(オラクル)の正規表現

Oracleでは、正規表現(regex:regular expression) を使用する事もできます。

スポンサーリンク

Oracle(オラクル)でのグループ化と要約

SQLで、さまざまな方法でデータを要約するには、複数の行をグループ化してセット「組」にまとめます。

そして、最終的に1つの組につき1行を抽出します。

そのためには「GROUP BY句」・「HAVING句」等の集計関数を使用します。

Oracle(オラクル)での集計関数「COUNT」

入力として一組の値セットを受け取り行グループの各行から1つずつ受け取るのが、集計関数(aggregate function)です。

出力としては1つの値を抽出します。

よく使われる集計関数は「COUNT」です。

この関数は、カラムの中にある「NULL」以外の値の個数を全て数えます。

例えば次のSQL文は、「serial_no」の個数を数えています。

例文

SELECT COUNT (serial_no)
    FROM buhin_table
;

「ALL」または「DISTINCT」を付ける事により、「NULL」以外の値をすべて入力として使用するのか、それとも重複した値を除外するのかを設定できます。

例文

SELECT COUNT(DISTINCT serial_no),
    COUNT(ALL serial_no)
    FROM buhin_table
;

基本はALLがデフォルトです。

つまり「COUNT(式)」は「COUNT (ALL 式)」と同等です。

ワイルドカード引数としてアスタリスク(*)を渡すことも、特殊な関数である「COUNT」では可能です。

例文

SELECT COUNT(*) 
    FROM buhin_table
;

「COUNT(*)」を使用すると、列の値でなく行を数えます。

「COUNT(*)」を使用すれ ば、「NULL」かどうかは関係有りません。

カラムだけに適用されるのであり、行全体には適用されないのが「NULL」の概念である為です。

次に一般に利用できる集計関数をまとめておきます。

集計関数説明
AVG (X)一組の数値セットの平均(average) を返す
COUNT(X)一組の値セットにある非NULLの値の個数を数える
MAX(X)一組の値セットの中で最大値を返す
MEDIAN(X)一組の値セットの中央値(median) を返す!(中央値は補間されることがあります)
MIN(X)一組の値セットの中で最小値を返す
STDDEY(X)一組の数値セットの標準偏差を返す
SUM(X)一組の値セットの合計を求める
VARIANCE(X)一組の数値セットの統計的分散を返す

Oracle(オラクル)のGROUP BY

テーブル内のすべての行でなく、行のグループに適用するときに、集計関数を使うと便利です。

その為には「GROUP BY句」を使用します。

次のSQL文は、それぞれの自動車名(car_name)ごとに部品 (buhin_table)の個数を数えています。

例文

SELECT c.car_name, COUNT(*)
    FROM automobile c INNER JOIN buhin_table a
    ON c.car_id = a.car_id
GROUP BY c.car_name;

この様にSQL文を実施すると「GROUP BY句」の設定に基いて、データベースは行を分類してグループにまとめます。

グループ化の例

ZC33Sdoor
ZC33Slight
ZC33Sloof
CW5Wengine
CW5Wtire
EA1Atank

グループに1行しか入っていないこともあります。

グループ化するとき、限られた範囲でソート処理が働きます。

行をグループ化するのに必要なところまで上記のSQL文では、ソート処理が働きます。

グループを作成した後で、それぞれのグループに一度ずつ集計関数が働きます。

上記のSQL文では、集計関数を適用しなかったカラムは、1つの値に「圧縮」され、各グループごとに「COUNT(*)」の結果を抽出します。

例の結果

zc33s3
cw5w2
ea1a
ZC33S3
CW5W2
EA1A1

実際の問題として、多数の詳細な行が1行に集積されるので、「GROUP BY句」のリストに設定していないすべてのカラムに集計関数を適用する必要があります。

Oracle(オラクル)での便利な「GROUP BY」のテクニック

次にOracle(オラクル)での「GROUP BY」SQL文を書くときの便利なテクニックを紹介します。

Oracle(オラクル)での「GROUP BY」のリストを短縮する方法

「GROUP BY」SQL文の「SELECT文」と同じカラムを、「GROUP BY句」でも設定するのは面倒です。

例えば次のSQL文では、「car_id(自動車ID)」 が決まれば「buhin_name(部品名)」も決まります。

例文

SELECT c.car_id, c.car_name, COUNT(*)
    FROM automobile c INNER JOIN buhin_table a
    ON c.car_id = a.car_id
GROUP BY c.car_id, c.car_name
; 

この様な例文の場合、2つのカラムでグループ化するよりもむしろ、「car_id」カラムだけでグループ化するほうが、ソートキーが短くなるので効率的です。

ディスクの無駄な使用も減りますし、グループ化のためのソートも高速になります。

次に1つの方法を例示します。

例文

SELECT c.car_id, Max(c.car_name), COUNT(*)
    FROM automobile c INNER JOIN buhin_table a
    ON c.car_id = a.car_id
GROUP BY c.car_id
; 

ここでは、「car_name」カラムを「GROUP BY句」から取り除いています。

その代替として、「SELECT文」で、同じカラムに「MAX関数」を適用しています。

これにより、自動車名も使用しながら「GROUP BY」以外の式を、すべて集計関数で囲まれなければならないという規則を同時に満たしています。

同じ「car_id」のグループ内では、すべての部品名が同じなので「MAX」は、その自動車だけをします。

Oracle(オラクル)で結合前にグループ化

前述の「GROUP BY」のSQL文では、グループ化の操作の前に結合を実施しています。

集計処理の前でなく集計処理の後で結合を実施するようにSQL文を書き直すには、サブクエリを使用します。

例文

SELECT c.car_name, agg.carcount
    FROM automobile c INNER JOIN (
        SELECT car_id, COUNT(*) AS carcount
        FROM buhin_table
        GROUP BY car_id) agg
    ON c.car_id = agg.car_id
; 

結合の前に集計処理を実施するので、少ない行を結合するというのが、この様に書き直した時の利点です。

「GROUP BY」の操作と集計処理を実施した行は、「buhin」テープルのデータを含まないので、さらにディスクの無駄な使用とメモリ使用量が減る可能性があります。

Oracle(オラクル)のHAVING

「HAVING句」を使用すれば「GROUP BY」SQL文が返す行に制限を課す事ができます。

例えば、2つ以上の部品名がある自動車だけを選ぶには、次の様に記述します。

例文

SELECT c.car_name, COUNT (*) 
    FROM automobile C INNER JOIN buhin_table a
    ON c.car_id = a.car_id
GROUP BY c.car_name
HAVING COUNT(*) > 1
; 

「HAVING句」を設定するには、集計処理が必須条件となります。

次の例文の様に自動車の部品名を数えます。

例文

SELECT c.car_name, COUNT (*) 
    FROM automobile C INNER JOIN buhin_table a
    ON c.car_id = a.car_id
GROUP BY c.car_name
HAVING c.car_name IN ('ZC33S','CW5W')
:

このSQL文は良くない例です。

本来は「WHERE句」で自動車名に制限を課すほうが容易です。

SELECT c.car_name, COUNT (*) 
    FROM automobile C INNER JOIN buhin_table a
    ON c.car_id = a.car_id
WHERE c.car_name IN ('ZC33S','CW5W')
GROUP BY c.car_name
:

「GROUP BY」の処理に渡す行数を減らすには「WHERE句」を使用したほうが容易です。

集計処理を実施すべき行数もソートすべき行数も減らせます。

集計した値にフィルタをかける必要がある場合には「GROUP BY」で処理した行に「HAVING句」でフィルタを掛けると容易です。

Oracle(オラクル)でのGROUP BYの拡張機能「ROLLUP・CUBE・GROUPING SETS」

「GROUP BY」の便利な拡張機能として「ROLLUP」・「CUBE」・「GROUPING SETS」があります。

次にこれらの関数とOracleの拡張機能の出力を処理するのに役立つ「スカラ関数」を紹介します。

Oracle(オラクル)のROLLUP

「ROLLUP」は、各グループごとに要約した行を抽出します。

例文

SELECT cy.maker_name, c.car_name,
    COUNT(a.buhin_id) cnt
FROM (maker cy INNER JOIN automobile c
    ON cy.maker_id = c.maker_id)
   INNER JOIN buhin_table a
    ON c.car_id = a.car_id
GROUP BY ROLLUP (cy.maker_name, c.car_name)
HAVING COUNT (a.buhin_id) > 1
; 

このSQL文の出力は次の様になります。

例文の結果

MAKER_NAMECAR_NAMECNT
TOYOTACOLOLA3
TOYOTA3
HONDACIVIC5
HONDA5
MITSUBISHIDELICA3
MITSUBISHIMIRAGE3
MITSUBISHI6
14

「GROUP BY」は、自動車メーカーごとに通常の要約を抽出している。

「ROLLUP」は、他のすべての自動車名ことの要約と、すべての行の要約を追加している。
この出力を見ると、「MITSUBISHI」に6つの部品があることが分かります。

「ROLLUP」を「GROUP BY」のすべてのカラムに適用する必要はありません。

次のSQL文は、それぞれの自動車メーカーと自動車名ごとに数えますが、前述のSQL文の様な総計を省略しています。

例文

GROUP BY cy.maker_name, ROLLUP(c.car_name) 

管理人は、「ROLL UP」は基本的に使いません。

SQL文が入れ子になってかなり複雑となります。

Oracle(オラクル)のCUBE

「CUBE」の機能は「ROLL UP」よりさらに進んでいます。

総計も出力してくれるだけで無く、設定したカラムのあらゆる組み合わせについて要約を抽出します。

例文

SELECT cy.maker_name, c.car_name,
    COUNT(a.buhin_id) cnt
FROM (maker cy INNER JOIN automobile c
    ON cy.maker_id = c.maker_id)
      INNER JOIN buhin_table a
    ON c.car_id = a.car_id
WHERE cy.maker_name = 'MITSUBISHI
GROUP BY CUBE (cy.maker_name, c.car_name)
;

例文の結果

MAKER_NAMECAR_NAMECNT
DELICA3
3
MIRAGE3
MITSUBISHI3
MITSUBISHIDELICA3
MITSUBISHI3
MITSUBISHIMIRAGE3
14

Oracle(オラクル)のGROUPING SETS

望みのグループを設定するには、Oracle9から導入された拡張機能である「GROUPING SETS」を使用します。

例文

SELECT cy.maker_name, c.car_name,
    COUNT (a.buhin_id) cnt
    FROM (maker cy
    INNER JOIN automobile c 
    ON cy.maker_id = c.maker_id)
    INNER JOIN buhin_table a 
    ON c.car_id = a.car_id
GROUP BY 
    GROUPING SETS (cy.maker_name, c.car_name)
    HAVING COUNT (a.buhin_id) > 1
;

例文の結果

MAKER_NAMECAR_NAMECNT
TYOTA3
HONDA5
MITSUBISHI6
COLORA3
CIVIC5
DELICA3
MIRAGE3

このSQL文で、「GROUPING SETS」は、自動車メーカーおよび自動車名ごとに集計しています。

2つのSQL文を実行しなければ、「GROUPING SETS」と同じ結果を取得することはできません。

Oracle(オラクル)のその他の関連関数

Oracleの拡張機能「GROUP BY」を使用するとき、次の関数も利用すると便利です。

GROUPING(カラム)

「CUBE」・「ROLLUP」・「GROUPING SETS」の結果が「NULL」のカラム値なら「1」を抽出、そうでなければ「0」を抽出します。

GROUPING_ID(カラム,カラム、・・・)

「GROUPING」と同様ですが、拡張機能「GROUP BY」の結果について、それぞれのカラムが「NULL」かどうかに応じて「1」または「0」を設定したビットベクトルを抽出します。

GROUP_ID()

「CUBE」・「ROLLUP」・「GROUPING SETS」の出力について、重複した行を区別することができます。

この関数は、重複した「n行」について、各行ごとに「0~n-1」の値を抽出します。

この戻り値を利用して、重複した行をいくつ残しておくかを決めることができます。

重複した行をすべて削除するには、「HAVING GROUP_ID() =0」とします。

スポンサーリンク

Oracle(オラクル)で使う条件分岐式「CASE式」

SQL文でif-then-elseの機能を実施するには、「CASE式」を使います。

コード化した値を人間にとって読みやすい形に置き換えたり、条件に応じて関数を実行する等のいろいろな事を「CASE式」で実現できます。

Oracle(オラクル)での単純CASE式

単純CASE式で、値に条件を付けて、別の値(戻り値)を抽出する事ができます。

例文1

CASE 値 0
 WHEN 値1 THEN 戻り値1
 [WHEN 値2 THEN 戻り値2
  ...] 
 [ELSE 他の場合の戻り値]
END

例文2

管理人は、主に下の例文を良く使います。

「CASE WHWEN」の数だけ、「END」を付けることが必要ですが、構文複雑にならずに容易に使えます。

CASE WHEN 値0 = 値1 THEN 戻り値1 ELSE
CASE WHEN 値0 = 値2 THEN 戻り値2 ELSE
CASE WHEN 値0 = 値3 THEN 戻り値3 ESLE
ELSE 戻り値4
END END END

等価条件による入力値を「WHEN句」内で「単純CASE式」と直接連結する事ができます。

一致する「WHEN区」がなく、「ELSE」の設定もなければ、CASE式は「NULL」を結果として抽出します。

Oracle(オラクル)での検索CASE式

「TRUE」または「FALSE」の条件リストを使って、検索CASE式で「値(戻り値)」を抽出することができます。

複数の「WHEN区」中から、条件の評価が「TRUE」となる最初の「WHEN区」が動作する。

例文1

CASE
 WHEN 条件1 THEN 戻り値1
 [WHEN 条件2 THEN 戻り値2
  ...] 
 [ELSE 他の場合の戻り値]
END

例文2

この場合でも管理人は、こちらを良く使います。「CASE WHWEN」の数だけ、「END」を付けることを忘れずに!

CASE WHEN 値の条件1 THEN 戻り値1 ELSE
CASE WHEN 値の条件2 THEN 戻り値2 ELSE
CASE WHEN 値の条件3 THEN 戻り値3 ESLE
ELSE 戻り値4
END END END
CASE WHEN ~ THEN ~ ELSE
CASE WHEN ~ THEN ~ ELSE
CASE WHEN ~ THEN ~ ELSE
・・・
ELSE ~ END END END ・・・

という構文の方が比較的忘れにくいです。

単純CASE式の場合と同様、「TRUE」になる条件が無く、「ELSE」の設定も無ければ、CASE式は「NULL」を抽出します。

複数の条件がTRUEのなる場合には、最初の条件が有効になります。

スポンサーリンク

Oracle(オラクル)でのテーブル結合の方法「INNER JOIN・OUTER JOIN」

複数のテーブルのデータを組み合わせる事で、結果を結合する事ができます。

内部結合(inner join)」と「外部結合(outer join)」により結果を結合する事ができます。

結合の構文は、「JOIN句」を使用するとテーブル名を(,)カンマで区切る方法の2つがあります。

Oracle(オラクル)での結合の概念について

(,)で区切る方法から学習すると、結合について理解し易くなります。

「FROM句」の2つのテーブルをカンマで区切ってら並べれば結合する事ができます。

例えば、国内(kuni = japan)の自動車(car_name)と部品(parts_name)のリストを検索するには、次の様に記述します。

例文

SELECT _car_name, buhin_name
FROM automobile, buhin_table
WHERE kuni = 'JAPAN'
;
car_namebuhin_name
DELICAroof
DELICAengine
MIRAGEtire
MIRAGEwheel

実際には前述のSQL文では、正確に結果を抽出することはできません。

理由は、同じ部品がすべての自動車に有るか否かが不明だからです。

この問題は、このSQL文の結合が自動車と部品の有り得る組み合わせの全て表示させる「デカルト結合(Cartesian join)」 となっている為です。

この様な組み合わせを、実際に使うことは殆どありませんが、「デカルト積(Cartesian product)」や「直積」と言います。

原則として全ての結合は「デカルト積」で始まります。

その中から、条件を設定し、抽出したい行を選択します。

旧式の結合構文であれば、「WHERE句」を使います。

例文

SELECT _ab.car_name, bt.buhin_name
FROM automobile ab, buhin_table bt
WHERE ab.kuni = 'JAPAN'
AND ab.cer_id = bt.car_id
;
car_namebuhin_name
DELICAroof
DELICAengine
MIRAGEtire
MIRAGEwheel

正しい結果を抽出するには、この様に記述します。

次の様な条件が「WHERE句」に記述されていることに注意してください。

AND ab.cer_id = bt.car_id

一般的にこの様な条件を「結合条件(join condition)」と言います。

2つのテーブルを連結するには、この様な結合条件を設定します。

まずこのSQL文では「FROM句」にカンマで区切って並べた2つのテーブルから、デフォルトの「デカルト積」を生成しています。

次にその結果が「WHERE句」に渡され「AND ab.cer_id = bt.car_id」とう条件設定により、不要な行を全て削除し結果を抽出します。

これらの結合手順は、概念上の動作となりますが、概念的なデフォルトの「デカルト積」を知っていれば、正しい結合のSQL文を理解し易くなります。

(結合を実施するとき、デフォルトの「デカルト積」を使用することは原則ありません)

Oracle(オラクル)でのクロス結合「CROSS JOIN」

「クロス結合(CROSS JOIN)」は「デカルト積(直積)」です。

クロス結合を実施するには、次の様に記述します。

例文

SELECT *
FROM automobile
CROSS JOIN buhin_table
; 

「CROSS JOIN」は、(,)での結合と異なり新しい結合の構文です。

間違えて結合条件を省略して「デカルト積」を生成することを防止するには、この構文を使用します。

Oracle(オラクル)での内部結合「INNER JOIN」

あるテーブル内の各行を、別のテーブル内で対応する1つまたは複数の行に関連付けるには「内部結合(INNER JOIN)」を使用します。

結合対象のテーブルから、2つの関連しあった行を取り出し、それらの行の値を組み合わせた行を抽出することが、内部結合です。

Oracle(オラクル)での内部結合の構文

次のSQL文は、内部結合の構文です。

「automobile」テーブルと 「buhin_table」テーブルを結合して、自動車ごとに部品のリストを作成している。

例文

SELECT c.car_name, a.buhin_name
FROM automobile c INNER JOIN buhin_table a
    ON c.car_id = a.car_id
; 
car_namebuhin_name
DELICAroof
DELICAengine
MIRAGEtire
MIRAGEwheel

「INNER JOIN」が、2つのテーブルの間で内部結合を実施しています。

2つの行が関連しているという条件を設定するには「ON句」で結合条件を記述します。

ます概念上はデフォルトの「デカルト積」が作られます。

次に不要な行の組み合わせを削除するために結合条件が使用されます。

テーブルの順序は、内部結合におて特に問題ありません。

「INNER」はオプションですので、省略する事もできます。

次のSQL文は、上記のSQL文と同じ結果となります。

例文

SELECT c.car_name, a.buhin_name
FROM automobile c JOIN buhin_table a
    ON c.car_id = a.car_id
; 

結合クエリで「WHERE句」を使用することもできます。

例えば、国産メーカーだけを選択するには、次の様に記述します。

例文

SELECT c.car_name, a.buhin_name
FROM automobile c JOIN buhin_table a
    ON c.car_id = a.car_id
WHERE a.kuni = 'JAPAN'
;

SQL文は先に結合の結果を返します。

次に結合した行のうち「WHERE条件」を満たす行を抽出する為に「WHERE句」を適用しています。

Oracle(オラクル)での結合の優先順位と括弧

原則として複数の結合があるときには、左から右の順に結合します。

例えば次のSQL文では、「maker」と「automobile」を結合し、その結果を「buhin_table」に結合しています。

例文

SELECT *
FROM maker Y JOIN automobile c 
    ON y.maker_id = c.maker_id
    JOIN buhin_table a
    ON c.car_id = a.car_id
;

明示的に操作の順序を設定するには、括弧で囲みます。

次のSQL文のは、上記のSQL文との同じ結果となります。

例文

SELECT *
FROM ( maker Y JOIN automobile c
    ON y.maker_id = c.maker_id)
    JOIN buhin_table a
    ON c.car_id = a.car_id
; 

次のSQL文の様に「automobile」と「buhin_table」の結合の後で、その結果を「maker」結合させると、結合の順序が逆になります。

「maker」に結合するための「ON」がSQL文の最後になります。

例文

SELECT *
FROM maker Y JOIN (automobile c
    JOIN buhin_table a
    ON y.car_id = c.car_id)
    ON c.maker_id = a.maker_id
;

外部結合を実施するとき、括弧と結合の順序が重要です。

括弧を移動しても内部結合だけを実施するなら、結果は変わりません。

これは、結果として生じる行は、内部結合の操作を何度実施したとしても、関係する各テーブルからの1つの行を抽出する為です。

Oracle(オラクル)でのUSING句

「ON句」を「USING句」に置き換えることにより、2つのテーブルの結合を定義するカラムの名前が同じ場合や、結合条件が等価条件になる場合でも、簡単に結合を記述できます。
同じ名前のカラムに同じ値が入っている必要のある条件を等価条件と言い、この様な結合を「等価結合(equi-join)」や「等結合」と言います。

例文

SELECT c.car_name, a.buhin_name
FROM buhin_table a
    JOIN automobile c
    USING (car_id)
    WHERE a.kuni = 'JAPAN'; 

例文を実際に実行するとは、Oracleではエラーとなります。

Oracleでは、「ON」による結合と「USING」による結合には、重要な意味上の違いがある為です。

結合対象の「c.car_id」と「a.car_id」をそれぞれのテーブルから取り出し、2つの「city_id」カラムを抽出するのが「ON」による結合です。

一方で1つの「car_id」カラムだけを抽出し、別名をつけることがでいないのが「USING」による結合となります。

エラーとなる例文(「city_id」が2つ)

SELECT c.car_id, a.car_id, a.buhin_name
FROM buhin_table a
    JOIN automobile c
    USING (car_id)
;

エラーにならない例文(「city_id」が1つ)

SELECT car_id, a.buhin_name
FROM buhin_table a
    JOIN automobile c
    USING (car_id)
;

いろいろ試してみましたが、Oracleでの「ON」結合と「USING」結合との間の違いについては、管理人には理解できませんでした。

Oracle(オラクル)での自然結合「NATURAL JOIN句」

「USING句」よりもさらに簡単な記述があります。

結合が等価結合であり、明示的に結合条を設定せずに、名前が共通の全てのカラムに基づき2つのテーブルを結合する場合には「NATURAL JOIN」を使用します。

Oracleでは、「NATURAL JOIN」で結合したカラムに別名を付ける事はできませんので注意してください。

例文

SELECT car_id, a.buhin_name
FROM buhin_table a NATURAL JOIN automobile c; 

「NATURAL JOIN」は、プログラムコードの中にSQL文を使っている場合は、使わないでください。

これは、テーブルに何らかのカラムを追加した際に、偶然でも結合先のテーブルのカラムと名前が同じであった場合に「NATURAL JOIN」のSQL文の意味が変わってしまうことがある為です。

「NATURAL JOIN」を使用するなら、一度実行するだけのSQL文で使用した方が安全です。

Oracle(オラクル)での非等価結合

これまでの結合は、全て等価結合でした。

つまり、対応する2つのテーブルのカラムについて、対応する行に全く同じ値が入ってる事が条件でした。

しかし条件として「非等価結合(NON-EQUI-JOIN)」を記述する場合もあります。

次のSQL文は、世帯の住所の設定期間と、個人の住所の設定期間が合致しているかと調べる為に、世帯の住所テーブル「setai_place」と個人の住所テーブル「seiri_place」を比較しています。

例文

SELECT *
FROM setai_place w
    INNER JOIN seiri_place be
    ON w.setai_no = be.setai_no
    AND w.start_ymd <= be.start_ymd
    AND w.end_ymd >= be.end_date; 

この単純なSQL文は、非等価条件の分かりやすい例文ですが、日付のカラムが「NULL」になる可能性については配慮していません。

このSQL文では、「世帯の住所の設定期間内に個人の住所の設定期間が入っている事」を「非等価結合」で判断している事が重要なポイントです。

Oracle(オラクル)での外部結合「OUTER JOIN」

結果のそれぞれの行に結合した両方のテーブルの行が含まれてなくても良い結合が、外部結合「OUTER JOIN」となります。

一方または両方のテーブルがオプションとなります。

外部結合を使用すれば、それぞれのテーブルに一致する行が無くても結果を抽出できます。

Oracle(オラクル)での左外部結合「LEFT OUTER JOIN」

「左外部結合(LEFT OUTER JOIN)」を使用すれば、対応する行が他のテーブルに存在するかどうかに関係なく、1つのテーブルからすべての行を抽出する事ができます。

次のSQL文では、どの自動車にも使われてない部品があっても、全ての部品を抽出します。

「buhin_tabl」の「car_id」が「NULL」の場合でも抽出されます。

例文

SELECT c.car_name, a.buhin_name
FROM buhin_table a
    LEFT OUTER JOIN automobile c 
    ON c.car_id = a.car_id; 
car_namebuhin_name
DELICAroof
DELICAengine
abs
vsc

左端のテーブルが必須テーブルとなるのが、左外部結合です。

このSQL文の場合は 左端の「buhin_table」テーブルとなります。

SQL文の結果の各行は、必須テーブル「buhin_table」の各行に対応します。

一方で、「automobile」テープルはオプションテーブルとなります。

部品に対応する自動車があれば、結果は内部結合と同じになります。

部品に対応する自動車の行が存在しなければ、部品について返された行には、自動車テープルの「car_id」の値の代わりに「NULL」が入ります。

「USING句」および「NATURAL句」と括弧を内部結合で使用したのと同じ方法で、外部結合でも使用することができます。

このSQL文では、最後の2つの自動車名が「NULL」となります。

これは、対応する行の「car_id」が「NULL」になっている又は、部品に対応する自動車が存在しない為です。

Oracle(オラクル)の外部結合でNULLを評価する

外部結合で、「NULL」かどうかを判断する場合は注意が必要です。

次のSQL文は、国産若しくは製造国不明の自動車をすべて出力し、部品が存在しない自動車も抽出しようとしています。

例文

SELECT a.buhin_name, c.car_name
    FROM automobile c
    LEFT OUTER JOIN buhin_table a
    ON c.car_id = a.car_id
WHERE a.kuni = 'JAPAN'
    OR a.kuni IS NULL
;

「buhin_table」 テーブルの「kuni」に「NOT NULL」の条件を設定している場合のみ、このSQL文が有効となります。

そうでない場合には、次の様なSQL文を使った方が良いです。

例文

SELECT a.buhin_name, c.car_name
    FROM automobile c
    LEFT OUTER JOIN buhin_table a
    ON c.car_id = a.car_id
WHERE a.kuni = 'JAPAN'
    OR a.car_id IS NULL
;

「a.car_id」はテーブル「buhin_table」主キーですので、「NULL」にすることはありません。

この為「a.city_id IS NULL」という条件が、テーブル「buhin_table」を含まないことを示す事であり、同時に外部結合の結果となります。

Oracle(オラクル)での右外部結合「RIGHT OUTER JOIN」

右外部結合「RIGHT OUTER JOIN」は左外部結合と同等です。

異なるのは、必須テーブルが右端のテーブル(リストの2番目のテーブル)になることです。

例文は次の通りです。

例文

SELECT c.car_name, a.buhin_name
    FROM automobile c
    RIGHT OUTER JOIN buhin_table a
    ON c.car_id = a.car_id
;

このSQL文は、左外部結合のSQL文と同等である。

「buhin_table」が必須テーブルであり、「automobile」テーブルはオプションテーブル となります。

違いは「FROM句」に並べたテーブルの順番のみとなります。

通常は結合をすべて「左外部結合」で記述し、左右の混乱を避ける様にします。

Oracle(オラクル)での完全外部結合「FULL OUTER JOIN」

場合によっては、外部結合で両方のテーブルをオプションとして扱いたいことがあります。

この様なな結合を「完全外部結合 (FULL OUTER JOIN)」と言います。

例えば次の様に記述します。

例文

SELECT c.car_name, a.buhin_name
    FROM buhin_table a
    FULL OUTER JOIN automobile c
    ON c.car_id = a.car_id; 
car_namebuhin_name
DELICAroof
DELICAengine
abs
vsc
MIRAGE
MIRAGE

このSQL文は、部品がない自動車と自動車に使われてない部品に加えて、自動車と部品を結びつけた標準的な内部結合の結果を同時に抽出します。

Oracle(オラクル)固有の外部結合「(+)」

Oracleでは、オプションのテーブルを設定するために、結合の全ての条件で、オプションのテーブルのカラムにサフィックス「(+)」を追加しています。

次のSQLは、すべての自動車を出力し、さらに部品のない自動車もすべて抽出します。

例文

SELECT a.buhin_name, c.car_name
    FROM car c, buhin_table a
    WHERE c.car_id = a.car_id (+)
;

管理人は、Oracle(オラクル)メインですので、普段から「(+)」を使用しています。

MS-ACCESSも良く使いますが、ACCESSでは、「LEFT OUTER JOIN ・・・ ON」を使います。
正直なところ、Oracle(オラクル)に慣れてしまうと、「JOIN句」は覚え難く使いづらい印象があります。

SQL文自体も長くなりがちです。

述部でカラムの順序を逆にしても、同じ結果が得られます

「(+)」の位置で決まります。

例文

ELECT a.buhin_name, c.car_name
    FROM car c, buhin_table a
    WHERE a.car_id(+) = c.car_id
;

Oracle(オラクル)では、等価条件として「=」・「=」が使うこともできますが、新しいSQL文では、使わない方が良い様です。

原則には、結合を書くときは、ANSI規格の「JOIN句」を使用する方が、Oracle(オラクル)以外のデータベースでも使用でき汎用的だと言われています。

しかし管理人的には「(+)」の方が楽です。

例文

ELECT a.buhin_name, c.car_name
    FROM car c, buhin_table a
    WHERE a.car_id *= c.car_id
;

スポンサーリンク

Oracle(オラクル)でのユニオンクエリ「UNION・INTERSECT」

2つ以上のクエリの結果を「結合」するには、「ユニオンクエリ」を使用します。

「UNION」・「INTERSECT」等を使用します。

Oracle(オラクル)でのUNIONとUNION ALL

「UNION」を使用すれば、2つの「SELECT文」の結果を1つの結果に結合できます。

重複行が削除されて、結果として表示されます。

「UNION ALL」を使用すれば、重複した行も結果として抽出できます。

Oracle(オラクル)でのUNION

次の「UNION」SQL文は、「automobile」を必須テープルとして外部結合を抽出しています。

最初の「SELECT」は、「buhin_table」と「automobile」を結合しています。

2番目の「SELECT」は、部品のない自動車を抽出しています。

例文

SELECT c.car_name, a.buhin_name
  FROM automobile c, buhin_table a
  WHERE c.car_id = a.car_id 

UNION

SELECT c.car_name, ''
  FROM automobile c
  WHERE c.car_id NOT IN
    (SELECT a.car_id
     FROM buhin_table a 
     WHERE a.car_id IS NOT NULL)
ORDER BY c.car_name, a.buhin_name
;

Oracle(オラクル)でのUNION ALL

次の2つのSQL文は、「UNION」と「UNION ALL」の違いを表しています。

例文

SELECT car_id
  FROM buhin_table
  WHERE kuni IS NOT NULL

UNION ALL

SELECT car_id
 FROM buhin_table
 WHERE maker_name = 'MITSUBISHI'
; 
SELECT car_id
  FROM buhin_table
  WHERE kuni IS NOT NULL

UNION

SELECT car_id
  FROM buhin_table
  WHERE maker_name = 'MITSUBISHI'
;

どちらのクエリも、生産国が分かっている部品を使っている自動車と部品のメーカーが「MITSUBISHI」である自動車のリストを生成しています。

この2つのSQL文の違いは、「UNION」クエリは重複を取り除いて一度しか結果に抽出しない事に対して「UNION ALL」は重複した自動車を何回でも結果に抽出することです。

「UNION」と「UNION ALL」では、処理時間が異なります。

「UNION」で重複を削除するにはソート処理が必要になります。

この為、クライアントに表示するには、すべての行を結果として抽出してからソートしなければなりません。

この為、処理に時間が掛かります。

Oracle(オラクル)での処理の順序

3つ以上の「SELECT文」をユニオンで結合するSQL文を書くとき、ユニオン操作の順序を括弧で設定することができます。

例文

SELECT car_id
  FROM automobile
  WHERE car_name IS NULL 

UNION ALL 

(SELECT car_id
  FROM buhin_tble
  WHERE kuni IS NOT NULL

UNION

SELECT car_id
  FROM buhin_tble
  WHERE maker_name = 'MITSUBISHI')
; 

このSQL文は、まず組み合わせが「UNION」なので、重複を削除した2番目と3番目の「SELECT文」の結果を先に結合します。

次に、「UNION ALL」の操作を行い、重複は削除せずに名前のない自動車を取り出します。

したがって、このSQL文では、名前のない自動車について結果が重複する事となります。

順序を明記しないと、ユニオンは原則として上から下への順序で動作します。

ただし、「INTERSECT」が「UNION」よりも優先する場合は異なります。

Oracle(オラクル)でのMINUS

「MINUS」を使用すれば、1つのクエリの結果から別のクエリの結果を「差し引く」事ができます。

また、括弧で操作の順序を設定することもできます。

次のSQL文は、「buhin_table」から取り出した自動車のリストから、自動車名のない自動車を削除しています。

次にその結果を、自動車テーブルの全ての行から削除します。

したがって、このSQL文の結果には、自動車名が無い自動車が必ず含まれます。

例文(括弧の位置に注意)

SELECT car_id
  FROM automobile

MINUS 
  (SELECT car_id
  FROM buhin_table

MINUS
  SELECT car_id
  FROM automobile
  WHERE car_name IS NULL)
; 

Oracle(オラクル)でのINTERSECTとINTERSECT ALL

「INTERSECT」を使用すれば、2つのSELECTの結果セットに共通な行を見つける事ができます。

「INTERSECT ALL」を使用すれば、重複した行を削除しません。

Oracle(オラクル)でのINTERSECT

次のSQL文の「INTERSECT」は、自動車とメーカーの両方に同じ名前がある場合を抽出します。

例文

SELECT car_name
FROM automobile

INTERSECT

SELECT maker_name

FROM maker
; 

ソートまたはハッシュ演算により、2つの結果セットに共通な行を判定できます。

重複を削除したうえで、それぞれの名前を一度だけ抽出します。

Oracle(オラクル)でのINTERSECT ALL

「INTERSECT ALL」を使用すれば、重複した行を含めて結果を抽出できます。

例として、次の様なデータを扱う場合を想定します。

MITSUBISHI
MITSUBISHI
TOYOTA
TOYOTA
HONDA

INTERSECT 又は INTERSECT ALL

MITSUBISHI
MITSUBISHI
TOYOTA 

「INTERSECT」は次の結果となります。

MITSUBISHI
TOYOTA

一方、「INTERSECT ALL」なら次の結果となります。

MITSUBISHI
MITSUBISHI
TOYOTA 

「MITSUBISHI」は、両方の結果に2回ずつ抽出されているので、最後の結果にも2回抽出されます。

「TOYOTA」は、2番目の結果に1回だけ抽出されているので、最後の結果には1回だけ抽出されます。

スポンサーリンク

Oracle(オラクル)SQLで使う階層構造(親子データ)

次の例のとおりパーツグループテーブル「assy」と自動車部品テーブル「car_of_parts」を用いて、Oracle(オラクル)での階層改造の親子関係のデータを扱うSQL文について紹介します。

パーツグループテーブルテーブル「assy」は、複数の下位の自動車部品テーブル「car_of_parts」で構成され、その部品はさらに複数の下位の部品で構成されています。

この様な階層が、最下位の個別部品まで繰り返されている事を想定しています。

Oracle(オラクル)でのCONNECT BY構文

「CONNECT BY」構文と関連の関数を使用すれば、Oracleで階層SQL文を記述する事ができます。

Oracleでの「CONNECT BY・START WITH・PRIOR」

「START WITH」で開始ノードを設定し、「CONNECT BY」で親子関係を設定することにより、階層内のデータを抽出する事ができます。

次のSQL文では、「car_of_parts」テーブルから、「assy_id」が500台で、「assy_id」と「assy_group」が合致するものが抽出される。

例文

SELECT assy_id, parts_name, upper_parts_no
    FROM car_of_parts
    START WITH assy_id = 500
    CONNECT BY upper_parts_no = PRIOR assy_id; 

結果

assy_idassy_nameupper_parts_no
500DELICA
501Transmission500
511clutch501
512diffgear501
513missiongear501
521gear1513
523gear3513

「TART WITH句」で、Oracleが検索すべき最初の行を設定します。

このSQL文のでは、部品番号(assy_id) が、500で始まっています。

「CONNECT BY」句を使用することにより、親の行と子の行の関係を設定できます。

この部品表では階層を下に降りるとき、子の「upper_parts_no」が親の「assembly_id」に一致していることが必要となります。

条件を「START WITH upper_parts_no IS NULL」と条件を設定する事により、全ての部品と下位の部品を出力できます。

「CONNECT BY」SQL文のキーワード「PRIOR」は、親の行のカラムの値を返という意味です。

「PRIOR」は、親子関係を定義するときに使用することが多いですが、「SELECT文」や「WHERE句」などから親の行の値を参照したいときにも、「PRIOR」を使用できます。

Oracleで「CONNECT BY」内に「WHERE句」を使う

実用性は低いですが、「CONNECT BY」を使うSQL文に「WHERE句」を付ける事もできます

実際には「START WITH」を使って条件設定する事が多いです。

Oracle(オラクル)で「CONNECT BY」と結合を同時に使う

「CONTECT BY」を使うSQ文と「結合」を同時に実施する場合があります。

動作の順序は、次のとおりです。

①最初に結合(最初に結合の述部)を実施します。

②結合の操作で抽出された行に、「CONNECT BY」を適用します。

③「CONNECT BY」の操作で抽出された結果に、「WHERE句」の述部が適用されます。

但し、SQL文の対象となる階層からノードを削除するような結合を記述してはいけません。

Oracle(オラクル)での階層的ソート

Oracleの「CONNECT BY」構文では、親ノードの後に直下の子が続き、それぞれの子の後にさらに直下の子が続くという順番となります。

「CONNECT BY」を使ったSQL文の中で、標準的な「ORDER BY句」を書くことは実用的ではありません。

何故ならソート処理がデータの階層的な順序を削除する為です。

ただし、新しい「ORDER SIBLINGS BY句」を使用して、階層を削除することなく各レベルを独立してソート出来る機能が、Oracleバージョン9からは実装されています。

次のSQL文では、「car_of_parts」テーブルから、「assy_id」が500以上で、「assy_id」と「upper_parts_no」が合致するものが抽出されますが、親子関係が崩れずにソートされます。

例文

SELECT assy_id, parts_name, upper_parts_no
    FROM car_of_parts
    START WITH assy_id = 500
    CONNECT BY upper_parts_no = PRIOR assy_id
    ORDER SIBLINGS BY parts_name; 

結果

assy_idassy_nameupper_parts_no
500DELICA
610Engine500
620starter610
621starter_motor620
622starter_relay620
630piston610
631piston_ring630
632piston_seal630
640silinder610
650crank_case610
660crank610

このSQL文では、階層の各レベルごとに別々にソートが働いていますが、それにも拘わらず、それぞれの親の後に直下の子が続いています。

階層は消えないで残っている例となります。

(オラクル)での階層データのループ

場合によっては、ある行の子がその行の親になってしまうという様に、階層データが崩れてしまう事があります。

この場合は、無限ループとなってしまいます。

その様な問題を避ける為には、「CONNECT BY句」に「NOCYCLE」を追加し、「SELECT文」に疑似カラム「CONNECT_BY_ISCYCLE」を追加すると解決できます。

例文

SELECT RPAD(" ", 2* (LEVEL-1)) || parts_name AS parts_name,
        maker_name,
        CONNECT_BY_ISCYCLE
    FROM car_of_parts START WITH assy_id = 500
    CONNECT BY NOCYCLE assy_id = PRIOR upper_parts_no
;

「NOCYCLE」は、Oracleがデータの再帰ループをたどることを防止してくれます。

行の子が親または祖先でもある場合は、「CONNECT_BY_ISCYCLE」は「1」を抽出します。

Oracle(オラクル)での「CONNECT BY関数」と「操作」

「CONNECT BY」を使うSQL文を書くときに利用できる関数と「操作」が、Oracleバージョン10以降に実装されています。

CONNECT_BY_ISCYCLE

行の子が祖先でもあるとき、「1」を返し、そうでなければ「0」を抽出します。

「CONNECT BY NOCYCLE」と一緒に使用することが必要です。

CONNECT_BY_ISLEAF

子を持たない行に対して「1」を返し、子を持つ行に対して「0」を抽出します。

CONNECT_BY_ROOT(カラム)
CONNECT_BY_ROOT カラム

ルート行の値を抽出します。

LEVEL

階層のルートノードに対して「0」を返し、ルート直下のノードに対して「1」を返し、次のレベルのノードに対して「2」を抽出します。

また、それ以降も同様に続いていきます。

階層の出力を文字下げして表示するときに、「LEVEL」を利用することがあります。

たとえば、 それぞれのレベルごとに2つの空白で文字下げする場合は、

RPAD(' ',2* (LEVEL-1)) || 先頭カラム

という記述で対応できます。

PRIOR(カラム)
PRIOR カラム

行の親の値を抽出します。

SYS_CONNECT_BY_PATH (カラム, 区切り文字) 

ルートから現在のノードまでのパス上にあるカラム値を連結した結果を抽出します。

それぞれのカラム値の前に、設定の区切り文字が付きます。

前述のSQL文の「SELECT文」に

SYS_CONNECT_BY_PATH (assy_id,'/')

を追加すると、Oracleバージョン9以降の場合は、結果として(親ID/子ID)が抽出されます。

Oracle(オラクル)での階層SQL文のまとめ

管理人は、いろいろOracle(オラクル)の階層SQL文を試しましたが、SQL文の書き方が悪いのか上手くいきませんでした。

これが上手くいくと所属コード(部→課→係等)をソートしながら階層表示できるようになるので便利なのですが、どうやらデータ構造自体が上手く親子関係になっていない様でした。

役所のデータベースのデータ構造を概ね見てみましたが、多くが外部キーを使っての結合を使っている様で、階層構造のテーブルは殆どないか、データがしっかりとい整理されていないかのどちらかでした。

スポンサーリンク

Oracle(オラクル)でのサブクエリ

次の様にほとんどのSQL文内でサブクエリを使用することができます。

SELECT 文の SELECTリスト
SELECT 文の FROM 句
SELECT 文の  WHERE 句
SELECT 文のORDER BY 句
INSERT...SELECT...FROM 句
UPDATE 文の SET 句
・・・等・・・

サブクエリ「SELECT文のFROM句」文内では、ビューの様な動作をします。

この為、データを与えるテーブルと同じ動作となります。

「INSERT」・「DELETE」・「UPDATE」等のSQL文の対象としてビューと同様に、サブクエリを使用することができます。
例えば次のSQL文ように記述できます。

例文

DELETE
FROM (SELECT * FROM buhin_table
    WHERE tanka >= 5000)
WHERE maker_name IS NULL
;

この文は、単価が5,000円以上の部品の内、メーカー名の無いものを削除しています。

Oracle(オラクル)でのWITH句

「SELECT文」を無駄に繰り返さない様にするには、「WITH句」を使用します。

次の「SELECT文」は、平均よりも多数の部品名のある自動車を抽出する為に、同じサブクエリを2回も実行しています。

例文

SELECT  al.car_id,
    COUNT(*) AS parts_count,
    (SELECT AVG(buhin_count)
     FROM
        (SELECT a2.car_id,
            COUNT(*) AS buhin_count
         FROM buhin_tabble a2
         GROUP BY a2.car_id)) AS avg_parts_count

FROM buhin_table al
GROUP BY al.car_id
HAVING COUNT(*) > 
    (SELECT AVG(buhin_count)
     FROM
        (SELECT a2.car_id, COUNT(*) AS buhin_count 
         FROM buhin_table a2
         GROUP BY a2.car_id))
; 

このS様なQL文は、かなり難解となります。

また、変更が生じた際には、サブクエリを2箇所も修正する必要がありますので、修正を失敗する可能性が高くなります。

サブクエリの記述を一度だけにするには、「WITH」を使用しSQL文を書き直します。

例文

WITH average_buhin_count AS
(SELECT 
    AVG(buhin_count) AS avg_parts_count
 FROM
    (SELECT a2.car_id,
        COUNT(*) AS buhin_count
    FROM buhin_table a2
    GROUP BY a2.car_id))

SELECT al.car_id, COUNT(*) AS parts_count,
    (SELECT avg_parts_count
     FROM average_buhin_count)
FROM buhin_table a1
GROUP BY a1.car_id
HAVING COUNT(*) > 
    (SELECT avg_parts_count 
     FROM average_buhin_count)
; 

このSQL文を見ても分かる様に「WITH句」は、サプクエリの重複を完全に排除できるものではありません。

しかし、主要な文では単純な「SELECT文」だけでサブクエリを扱う様に、複雑なSQL文を1つのサブクエリ内に収納することができます。

相関のあるサブクエリとWITH

相関のないサブクエリを「WITH句」で抽出するのは比較的簡単です。

しかし、相関のあるサブクエリを抽出する場合は、「WITH句」のサブクエリに相関は出ない様に、十分な検討が必要となります。

次のSQL文は、それぞれの部品名についてメーカー名を返すために、相関のあるサブクエリを使用しています。

例文

SELECT buhin_name, 
    (SELECT maker_name 
     FROM maker ct JOIN autobmobile c 
        ON ct.maker_id = c.maker_id
     WHERE c.car_id = a.car_id)
FROM buhin_table a
WHERE (SELECT maker_name, 
    FROM maker ct JOIN automobile c
    ON ct.maker_id = c.maker_id, 
    WHERE c.car_id = a.car_id) = 'MITSUBISHI'
; 

このSQL文を「WITH」で書き直す方法の1つは、「WITH」のサブクエリで結合を実施し、メインの「SELECT」で2つのサプクエリに相関関係を残すことが必要です。

例文

WITH subSQLbun AS
    (SELECT car_id, maker_name
     FROM maker ct JOIN automobile c
     ON ct.maker_id = c.maker_id)

SELECT buhin_name,
    (SELECT maker_name
     FROM  subSQLbun ss
     WHERE a.car_id = ss.car_id)
FROM buhin_table a
WHERE (SELECT maker_name
    FROM subSQLbun ss
    WHERE a.car_id = ss.car_id) = MITSUBISHI'
;

しかしこの方法はあまり良くありません。

メインの「SELECT文」の2つのサブクエリから、1行しが減っていません。

もう1つの方法は、メインの「SELECT文」で、サブクエリを部品テーブルに結合する方法です。

例文

WITH subSQLbun AS
    (SELECT car_id, maker_name
     FROM maker ct JOIN automobile c
     ON ct.maker_id = c.maker_id)

SELECT a.buhin_name, ss.maker_name
FROM buhin_table a JOIN subSQLbun ss
    ON a.car_id = ss.car_id
WHERE ss.maker_name = 'MITSUBISHI'
; 

こちらの記述の方がすっきりしています。

また、「FROM句」でサブクエリを次の様に記述する事で、結合を使用する事もできます。
例文

SELECT a.buhin_name, ss.maker_name
FROM buhin_table a JOIN
    (SELECT car_id, maker_name
     FROM maker ct JOIN automobile c 
     ON ct.maker_id = c.maker_id) ss 
   ON a.car_id = ss.car_id
WHERE ss.maker_name = 'MITSUBISHI'; 

これは、場合によっては「WITH句」を使わない方がSQL文が容易となる例文です。

前例の「WITH句」のサブクエリと比較すれば、このSQL文の「SELEC文T」の構造が分かります。

コードが簡潔となり分かり易くなる事が、このSQL文で「WITH」を使用する利点の1つです。

スポンサーリンク

Oracle(オラクル)での関数の使い方

Oracle(オラクル)では、多くの関数を使用することができます。

SQL文の中から関数を呼び出して、データの処理や変換ができます。

例えば、「UPPER関数」を使用すれば、大文字と小文字の区別をしない方法で検索する事ができます。

例文

SELECT author, title
    FROM book
    WHERE UPPER(title) = 'GUNDAM BOOK'; 

そのカラムのインデックスを使用できなくなる事があるので「WHERE句」または「HAVING句」でカラムに関数を適用する場合は、注意が必要となります。

たとえば、数を使用すると「book」テーブルに「title」のインデックスがついている場合には、関このインデックスを使用できなくなります。

一方、上記のSQL文でインデックスを使用するには、関数インデックスでテーブルに「 UPPER(title)」のインデックスを付ける事が必要となります。

この様な問題があるので、SQL文を書く場合は「WHERE句」で重要な検索カラムに関数を適用しないように注意してください。

関数にはいろんなタイプがありますが、中でも便利な「スカラ関数(scalar function)
」を紹介します。

これらの関数をSQL文から実行すると、行につき1つの値を抽出します。

これらの関数のカテゴリは次のとおりです。

日付関数 
数値関数と数学関数
三角関数
文字列関数

これらのカテゴリ外の関数もいくつかあります。

最も便利なのは、1つの型から別の型にデータを変換する変換関数です。

例文

 (conversion function) 

その他では、1つの結果を抽出する為に、複数の行の値を結合する集計関数があります。

例文

(aggregate function)

Oracle(オラクル)での日付関数の使い方

日付関数は、最も便利な変換関数の1つです。

他にも便利な日時関数がいくつかあります。

Oracle(オラクル)で現在の日時を取得する方法

Oracle(オラクル)では「現在の日付」・「現在の時刻」・「日付と時刻の両方」等、「日時」について多様な値を抽出する関数があります。

たとえば、Oracleが 実装している「SYSDATE」は、サーバOSの現在の日時を示す「DATE値」を抽出します。

例文

SELECT SYSDATE FROM dual; 

これで、サーバーのシステム日時が表示されます。

Oracle(オラクル)の現在日時の関数の種類

Oracleでは、現在の日時情報を返すために、次の様な関数があります。

CURRENT_DATE

セッションの時間帯における現在の日付を、DATE型の値で抽出します。

CURRENT_TIMESTAMPC[(精度)]

セッションの時間帯における現在の日時を、「TIMESTAMP WITH TIME ZONE型」の値で抽出します。

日時の値の精度として、秒の小数部の桁数を設定します。(デフォルトは6)。

LOCALTIMESTAMP[(精度)]

「CURRENT_TIMESTAMP」と同様だが、時間帯による時間差のない「TIMESTAMP」の値を抽出します。

SYSDATE

サーバの現在の日時を「DATE型」の値で抽出します。

SYSTIMESTAMP [(精度)]

サーバの現在の日時を、「TIMESTAMP WITH TIME ZONE型」の値で抽出します。

DBTIMEZONE

データベースサーバの時間帯を、「UTC(Coardinated Universal Time:協定世界時)」からの時間差として、「[+|-] hh:mi」 という形式で抽出します。

SESSIONTIMEZONE

セッションの時間帯を、「UTC」からの時間差として、「[+|-]hh:mi」 という形式で抽出します。

SYS_EXTRACT_UTC (日時)

「TIMESTAMP WITH TIME ZONE」の値から、「UTC」の日時を抽出します。

Oracle(オラクル)での四捨五入と切り捨ての方法

Oracleでは、日時の特定の要素に四捨五入(ROUND) または切り捨て(TRUNC)を、「DATE」の値に対して実施することができます。

次のSQL文は、最も近い月への四捨五入と切り捨てを行っています。

例文

SELECT SYSDATE,
    ROUND (SYSDATE, 'Mon'). 
    TRUNC (SYSDATE, 'Mon') 
    FROM dual
;

切り捨て(TRUNCATION)とは、設定した要素よりも小さい単位の要素に合わせて値を設定することです。

日にちの最小値は1なので、日付を切り捨てると1日になります。

出力フォーマットに時刻を設定したとすれば、「00:00:00(午前0時)」に切り捨てとなります。

四捨五入(rounding)とは、設定した要素の最も近い値に設定することです。

例文では、最も近い月に四捨五入しています。

システム日時の当該年又は翌年の1月1日のどちらか近い方となります。

この場合も、時刻は「00:00:00」となります。

Oracle(オラクル)での便利な日付関数

Oracleには、日付の演算を実行するのに便利な関数が用意されています。

次の関数はいずれも「DATE型」の値を使って「DATE型」の値を抽出します。

ADD_MONTHS(日付, 整数) 

日付の月に整数の値を加算します。

日付が月の最終日なら、結果は、加算後の月の最終日になります。

日付の月よりも、加算後の月の日数のほうが少ない場合も、結果は加算後の月の最終日になります。

LAST_DAY (日付)

日付を含む月の最終日を抽出します。

NEXT_DAY(日付, 曜日) 

日付よりも後の日のうち、設定の曜日が最初に現れる日付を抽出します。

曜日は、セッションの現在の日付言語にとって有効な曜日の名前または略名でなければ正常に動作しません。

(この値を確認するには、「NLS_SESSION_PARAMETERS」を調べると分かります)

この関数は、日付の曜日が引数の曜日に一致するときに設定の曜日が次に現れる日付を抽出します。

MONTHS_BETWEEN(後の日付、前の日付) 

2つの日付の間にある月の数を計算します。

これは、「後の日付-前の日付」という計算に該当します。

日付の順序は特に問題ありませんが、2番目の日付のほうが後の場合には、結果は負(マイナス)となります。

両方の日付が各月の同じ日である場合、または両方の日付が各月の最終日の場合には、結果の月数は整数になります。

そうでない場合、Oracleは、月の日数を「31」 として計算し、さらに入力した日付の時刻の要素も考慮して結果を抽出します。

Oracle(オラクル)での数値関数と数学関数

Oracle(オラクル)で使用できる便利な数値関数と数学関数は次のとおりです。

ABS(数値)

設定した数値の絶対値を抽出します。

CEIL(数値) またはCEILING(数値)

設定した数値以上の最少の整数を抽出します。

負(マイナス)の値の場合、絶対値が小さい値のほうが大きい値を意味することに注意が必要です。

「CEIL(5.5)」 は「6」になり、「CEIL (-5.5)」 は「-5」になります。

EXP(数値)

数学定数e(2.71828183)の数値乗を抽出します。

FLOOR(数値)

設定した数値以下の最大の整数を抽出します。

負(マイナス)の値の場合、絶対値が大きい値のほうが小さい値を意味することに注意が必要です。

「FLOOR (5.5)」は「5」になり、「FLOOR (-5.5)」 は「-6」になります。

LN(数値)

設定した数値の自然対数を抽出します。

LOG(基数,数値)

基数を底とする数値の対数を抽出します。

MOD(被除数,除数)

被除数を除数で割った余りを抽出します。

NANVIL(値,代替値)

値が浮動小数点のNaN(Not-a-Number:非数)の場合に代替の値を抽出します。

値が「NaN」なら代替値を返し、そうでなければ元の値を抽出します。

REMAINDER(被除数,除数)

MODと同じく、被除数を除数で割った余りを抽出します。

ROUND(数値[,位置]) 

数値を、位置に設定した小数部の桁で四捨五入します。

位置を設定しなければ、整数の値に四捨五入します。

小数点の左の桁で四捨五入するには、位置に負(マイナス)の値を設定します。

SIGN(数値)

数値の符号を示す。数値が「負・ゼロ・正」のいずれかに応じて、「SIGN」はそれ ぞれ「-1・0・1」を抽出します。

TRUNC(数値L,精度])

設定した数値を、精度に設定した小数部の桁で切り捨てます。

精度を設定しない場合には「0の桁」で切り捨てられます。

小数点の左の桁で切り捨て、それ以下の桁をゼロにするには、精度に負(マイナス)の値を設定します。

Oracle(オラクル)での三角関数

Oracle(オラクル)には一通りの三角関数があります。

関数の名前は次のとおりです。

三角関数を知っていれば、関数の名前を見れば、意味が分かるでしょう。

管理人は、すっかり忘れていますし、また仕事上でも殆ど使うことが無い関数です。

当たり前ですが多くの場合は「+・-・×・÷」の四則計算で足ります。

関数名称
ACOSコサイン
ASINサイン
ATANタンジェント
COSラジアン
COSラジアン
SINラジアン
SINHラジアン
TANラジアン
TANHラジアン

ATANの特殊な形式としてATAN2もあります。

ATAN2(x,y)

この関数はATAN(x/y) を抽出します。

Oracle(オラクル)での文字列関数

Oracle(オラクル)での一般的な文字列操作の関数の使用方法について紹介します。

Oracle(オラクル)で文字列を検索

Oracle(オラクル)での検索関数

Oracle(オラクル)では、文字列の中にある部分文字列の位置を見つけるには、「INSTR関数」を使用します。

INSTR(文字列, 部分文字列L,位置し出現回]]) 

オプションとして、検索を開始する位置を設定できます。

さらに何番目に現れる部分文字列を探したいかを、出現回に設定できます。

出現回に負(マイナス)の値を設定すると、文字列の末尾から検索ができます。

「REGEXP_INSTR」という検索関数が、Oracle(オラクル)のバージョン10では実装されています。

「INSTR・INSTRB・INSTR2・INSTR4」はそれぞれ、入力した「文字セットの文字・バイト・Unicodeコードポイント(UCS2)・UCS4(2バイト 固定長のUnicodeを4バイト表現に拡張したもの)を単位に動作します。

Oracle(オラクル)で文字列内のテキストを置換する

文字列に対して検索置換の操作を実行するには「REPLACE関数」を使用します。

REPLACE(文字列,検染し、置換])

置換の引数を省略すると、文字列の中に現れる検索文字列が全て削除されます。

「REGEXP_REPLACE」とう検索置換関数が、Oracle(オラクル)バージョン10では実装されています。

Oracle(オラクル)で部分文字列を取り出す

「SUBSTR関数」を使用すれば、文字列の開始位置から「設定の長さ分」の文字を取り出す事ができます。

SUBSTR(文字列,開始位置し、長さ]) 

文字列の先頭の位置は原則「1」です。

開始位置が「0」にすると「1」を設定したのと同じ動作となります。

開始位置が負(マイナス)なら、文字列の末尾から戻って数えます。

長さの設定を省略すると、文字列の開始位置から末尾までのすべての文字を取得します。
「REGEXP_SUBSTR」という文字列取出関数が、Oracle(オラクル)バージョン10では、実装されています。

Oracle(オラクル)での関数「SUBSTR・SUBSTRB・SUBSTR2・SUBSTRA」は、それぞれ、入力した文字セットの文字・バイト。Unicodeコードポイント(UCS2)・UCS4(2バイト固定長のUnicodeを4バイト表現に拡張したもの)を単位に動作します。

Oracle(オラクル)で文字列の長さを測定する

「LENGTH関数」を使用すれば、文字列の長さを測定できます。

LENGTH(文字列)

Oracle(オラクル)の関数「LENGTH・LENGTHB・LENGTH2・LENGTH4」はそれぞれ、 入力した文字セットの文字・バイト・Unicodeコードポイント(UCS2)・UCS4(2バイト固定長のUnicodeを4バイト表現に拡張したもの)を単位に動作します。

Oracle(オラクル)で文字列を連結する

「CONCAT関数」を使用すれば、2つの文字列を連結することができます。

CONCAT(文字列, 文字列)

「CONTACT」よりも文字列連結オペレータ「||」を使用する方が簡単です。

文字列1 || 文字列2

管理人は、主にこちらを使っています。

Oracle(オラクル)で不要な文字を削除する

「LTRIM・RTRIM・TRIM」は文字列から不要な文字を削除します。

TRIM(文字列)
TRIM(文字 FROM 文字列)
TRIM(オプション [文字] FROM 文字列) 

オプション ::= {LEADING | TRAILING | BOTH} 

「TRIM」を使用すれば、文字列の「先頭(LEADING)・末尾(TRAILING)・または両 方(BOTH)」から、設定の文字を削除できます。

文字のデフォルトは1つの空白文字となります。

オプションのデフォルトは「BOTH」になります。

「LTRIM・RTRIM」は「TRIM」と同様の機能となります。

LTRIM(文字列[, 不要な文字])
RTRIM(文字列[, 不要な文字])

設定の不要な文字を文字列の先頭(左側)から削除するには、「LTRIM」を使用します。

設定の不要な文字を文字列の末尾(右側)から削除するには、「RTRIM」を使用します。

削除したい不要な「文字列」は、「不要な文字の引数」に設定します。

「TRIM」は削除したい文字列を1つだけ設定できます。

「RTRIM」と「LTRIM」では、削除したい文字列を全て設定することにより多数の文字列を一度に削除することができます。

例えば、先頭と末尾の「句読点と空白」を削除したい場合は、次の様に記述します。

RTEIM(LTRIM(文字列,'.,! '),'.,! ')

Oracle(オラクル)で文字を変換する

「TRANSLATE関数」を使用すれば、文字列内の文字を変換することができます。

TRANSLATE (文字列, 元の文字, 変換先の文字) 

この関数は、文字列を検索し、元の文字に含まれている文字が現れると、変換先の文字の該当する文字に置き換えてくれます。

例えば、すべての数字をA~Jの文字に (1をAに、2をBに、…0をJに)、変換するには、次の様に設定する

例文

SELECT TRANSLATE ("This pen costs $10.15',
    '1234567890','ABCDEFGHIJ') 
FROM. dual
;

結果

This book costs $AJ.AE

文字列にある文字のうち、元の文字にない文字はそのまま残ります。

また、文字が元の文字に含まれるが、変換先の文字にない場合、その文字は削除されます。

Oracle(オラクル)で文字列の大文字小文字を変換する

「UPPER関数」または「LOWER関数」を使用すれば、文字列の全ての文字を大文字または小文字に変換する事ができます。

UPPER(文字列)
LOWER(文字列) 

「INITCAP(文字列)」を使用すれば、単語の最初の文字を大文字に、他の文字を小文字に変換できます。

Oracle(オラクル)でのその他の関数

Oracleには「日付・数値・文字列」のカテゴリに外でも、便利な2つの関数が実装されています。

GREATEST(値、値...])

値のリストの中から最大の値を返す。

入力する値は、「数値・日付・文字列」のどれでも構いません。

LEAST(値,値...])

値のリストの中から最小の値を返す。入力する値は、「数値・日付・文字列」のどれでも構いません。

スポンサーリンク
スポンサーリンク

Oracle(オラクル)でのデータ型変換

1つのデータ型から別のデータ型へ変換を実施することができます。

例えば「字列を数値」にまたは「数値を日時」に変換する等があります。

思わぬ結果が抽出される可能性があるので、暗黙的に型変換を実施することには注意が必要です。

例えば、次のSQL文では、「自動車ID(数値)」と「文字定数(リテラル)’1’」の型の不一致を扱うことができます。

例文

SELECT * FROM automobile
WHERE car_id = '1'; 

この文では、インデックスの使い方にどんな影響が出るのかも分かりません。

mた「car_id」の値「1」がテキストに変換されるか、それとも「’1’」が数値に変換されるかも不明です。

次にOracleでのデータ型変換について紹介します。

その前に、ANSI/ISO規格の「CAST関数」と「EXTRACT関数」について紹介します。

Oracle(オラクル)でのANSI/ISOのCAST関数

「ANSIISO規格」に型変換を明示的に調整することができる「CAST関数」があります。

例えば、「car_id」の値「’1’」を数値に変換したければ、次の様に記述します。

例文

SELECT * FROM automobile
WHERE car_id = CAST('1' AS NUMBER); 

このSQL文では、Oracleのデータ型「NUMBER」を使用しています。

「CAST関数」の一般的なフォーマットは次のとおりです。

CAST(値 AS データ型) 

設定した値が文字列の場合には、データベースの「変換先のデータ型」に合わせた内部表現と同様にする必要が有ります。

Oracleのデフォルトの「DATE」フォーマットは一般に「DD-MON-RR (DDは日・MONは月の略名・RRは年の最後の2桁)」ですので、例えば次の「CAST」の例文は正しく動作します。

CAST('20-MAR-1900' AS DATE) 

しかし、次の様に日付が別のフォーマットになっていると、「CAST」はエラーとなります。

CAST('03-20-1900 AS DATE) 

「CAST」を使用すれば、逆に「数値」を「テキスト値」に変換することもできます。

例文

SELECT CAST (car_id AS VARCHAR2 (10)), car_name
FROM autobomile
;

テキストから数値または日付に型を変換するに当たり「CAST」は汎用性が低いので、異なる入力フォーマットを扱えない場合が多数あります。

Oracle(オラクル)でのANSI/ISOのEXTRACT関数

ANSI/ISOには、日時の値から要素を抜き出すことができる「EXTRACT関数」があります。

EXTRACT(要索キーワード FROM 日時)

要索キーワード ::= {SECOND|MINUTE|HOUR|DAY|MONTH|YEAR)

「EXTRACT」は常に数値を抽出します。

Oracleでは、さらに「TIMEZONE_HOUR・TIMEZONE_MINUTE・TIMEZONE_REGION・TIMEZONE_ABBR」の要素も扱うことができます。

「TIMEZONE_REGION・TIMEZONE_ABBR」は例外的であり文字列を抽出します。

Oracle(オラクル)での日時変換(Oracle)

Oracleでは、次の関数を使用して、日時の型を互いに変換することができます。

TO_CHAR({日時 | 間隔}, フォーマット)
TO_DATE(文字列, フォーマット)
TO_TIMESTAMP(文字列, フォーマット)
TO_TIMESTAMP_TZ(文字列,フォーマット)
TO_DSINTERVAL('D HH:MI:SS')
TO_YMINTERVAL ('Y-M')

「テキスト値」から「日時の値」に変換するには、「フォーマット文字列」で「フォーマット」を設定します。

「テキスト表記」に変換するには、「フォーマット文字列」で「フォーマット」を設定します。

フォーマット文字列の要素の意味については、次のとおりです。

AM・PM・A.M.・P.M.午前または午後の指定(ピリオドなし、またはピリオドつき)
BC・AD・B.C.・A.D.紀元前(BC)または紀元後(AD)の指定(ピリオドなし
CC世紀のみ
D週初めからの日にち(NLS_TERRITORYで、どの曜日が1日目になるかが決まる)
DAY・Day・day曜日の名前(フォーマットの大小文字が出力に反映)
DD月初からの日にち
DDD年初からの日にち
DL長い日付フォーマット(出力のみ。TSのみと組み合わせる)
DS短い日付フォーマット(出力のみ。TSのみと組み合わせる)
DY・Dy・dy曜日の略名(フォーマットの大小文字が出力に反映)
E日本、台湾、タイの年号の略名(入力のみ)
EE年号の正式名
FF・FF1~FF9秒の小数部(TIMESTAMPの値だけで使用。常に2つのFを使用すること。
FM変換結果から空白を取り除くかどうかを切り換え
FX入力データとフォーマットモデルのパターンが正確に一致することを要求
HH・HH12時の12時間表記(1~12。HH12は出力のみ)
HH24時の24時間表記(0~23)
IW年初からのISO週番号(年初の木曜日を含む週が第1週。出力のみ)
IYY・IY・IISO規格の年表記の最後の3桁・2桁・1桁の数字(出力のみ) (出力のみ)
IYYYISO規格の年表記(出力のみ)
Jユリウス日(紀元前4713年1月1日が1日目)
MI
MM月番号
MON・Mon・mon月の略称(フォーマットの大小文字が出力に反映)
MONTH・Month・month月の名前(フォーマットの大小文字が出力に反映)
Q四半期(出力のみ)
RMローマ数字の月番号
RR年の最後の2桁(世紀を示すスライドウィンドウは00~49/50~99、つまり00~49は2000年代、50~99は1900年代)
RRRR年の4桁表記(入力では2桁も可。スライドウィンドウはRRと同様) 世紀(紀元前は負の値。出力のみ)
SCC世紀(紀元前は負の値。出力のみ)
SP数を英単語で表記することを示すサフィックス
SPTH数を英単語の序数で表記することを示すサフィックス
SS
SSSSS午前0時からの秒数
SYEAR・SYear・syear年を英単語で表記(紀元前は負の値。フォーマットの大小文字が出力に反映。出力のみ)
SYYYY年の4桁表記(紀元前は負の値)
TH数を序数で表記することを示すサフィックス
TS短い時刻フォーマット(出力のみ。DLまたは DSのみと組み合わせる)
TZD時間帯(タイムゾーン)の略
TZHLUTC(Coordinated Universal Time:協定世界時)からの時間帯の時間差
TZMUTCからの時間帯の分差
TZR時間帯の地域
W月初からの週番号(1~5。週1は月の初日から7日目まで。出力のみ)
WW年初からの選番号(1~53。出力のみ)
X小数点を表す各地の基数文字(アメリカ英語ではビリオド)
Y,YYYカンマつきの4桁の年
YEAR・Year・year年を英単語で表記(フォーマットの大小文字が出力に反映。出力のみ)
YYY・YY・Y年の最後の3桁・2桁・または1桁
YYYY年の4桁表示

間隔の型に変換する「TO_DSINTERVAL関数」と「TO_YMINTERVAL関数」は例外的なものです。

前述のSQL文に示すように、1つの「フォーマット」しか扱う事ができません。

いずれの場合も「フォーマット文字列」はオプションであり、無設定でも問題ありませんん。

入力値が次のデフォルトのフォーマットに従っていれば、フォーマットの設定を省略できます。

日付:NLS_DATE_FORMAT
タイムスタンプ:NLS_TIMESTAMP_FORMAT
時間帯つきのタイムスタンプ:NLS_TIMESTAMP_TZ_FORMAT

「NLS」の設定を確認するには「NLS_SESSION_PARAMETERS」を調べます。

変換のSQL文は次のとおりです。

例文

 
SELECT zokusei
FROM setai
WHERE setai_id = 5
AND start_ymd
    >= TO_DATE('Apr 20, 2000','Mon ad, yyyy')
;
SELECT zokusei
FROM setai
WHERE setai_id = 5
AND start_ymd
    >= TO_DATE ('20-Apr-1000 8:00 AM',
           'dd-mon-yyyy hh:mi am'); 
SELECT zokusei,
    TO_CHAR(start_ymd,'Month dd, yyyy hh:mi PM)
FROM setai
WHERE setai_id = 5
; 

間隔を変換する関数(名前に「INTERVAL」のつく関数)を除いて、他の全ての変換関数では、3番目のオプションの引数として、次の形式の「NLS」パラメータという文字列引数を設定できます。

NLS_DATE_LANGUAGE=言語 

この引数により、日付の月の名前などに使用する言語を設定できます。

「TO_YMINTERVAL」は「NLS」パラメータ引数を取りません。

「TO_DSINTERVAL」は、オプションで次の形式の引数をとり、「d」に小数点記号、「g」に桁区切り記号を設定します。

NLS_NUMERIC_CHARACTERS=dg

さらに、Oracleには、数値から間隔の値を生成する関数が2つあります。

NUMTODSINTERVAL (数値,単位)

入力した数値を「INTERVAL DAY TO SECOND」の値に変換する。

単位として「’DAY’・’HOUR’・’MINUTE’・’SECOND’」のいずれかを設定します。

NUMTOYMINTERVAL(数値,単位) 

入力した数値を「INTERVAL YEAR TO MONTH」の値に変換する。

単位として「YEAR・MONTH」のどちらかを設定します。

Oracle(オラクル)での数値変換

Oracleでは、次の関数を使用して、数値型を互いに変換できます。

 
TO_NUMBER(文字列, フォーマット) 
TO_CHAR(数値,フォーマット)

TO_BINARY_DOUBLE(数値)
TO_BINARY_FLOAT(数値) TO_NUMBER(数値) 
TO_NUMBER数値) 

TO_BINARY_DOUBLE(文字列, フォーマット)
TO_BINARY_FLOAT(文字列, フォーマット) 

これらの関数で扱える数値フォーマットの要素を次に示します。 プレフィックス:ゼロの値を空白で返す

$プレフィックス:ドル記号($)
,(カンマ)桁区切り記号の位置をカンマで指定(代わりにGの使用を検討すべき)
.(ピリオド)小数点の位置をピリオドで指定(代わりにDの使用を検討すべき)
0有効な数字。頭のゼロをゼロと表示
9有効な数字。頭のゼロを空白で表示
B
CISO通貨記号の位置を指定
D小数点の位置を指定
EEEEサフィックス:科学表記を使用
FMプレフィックス:頭と後ろの空白を削除
G桁区切り記号の位償を指定
Lローカルの通貨記号の位置を指定
MIサフィックス:マイナス(-)記号を後ろにつける
PRサフィックス:負の値を角括弧(<>)で囲む
RN・rnローマ数字(大文字または小文字。出力のみ)
Sプレプレックス:プラス記号(+)またはマイナズ記号 (-)を先頭につける
TM・TM9.TMEプレフィックス:最小の文字数を使用(最小テキスト。出力のみ。TM9は10進表記。TMEは科学表記)
Uユーロ記号の位置を指定
VVの右側の桁数をnとし、Vの左側の値に「10のn乗」を掛ける
X16進数を使用(出力のみ。頭をゼロで埋めるには、先頭に0をつける。頭と後ろの空白を削除するには、先頭にFMをつける)

「TO_NUMBER」と「TO_CHAR」を使用すれば、「NUMBER」と「VARCHAR2」の間で互いに変換する事ができます。

例文

SET SERVEROUTPUT ON
DECLARE 
    X NUMBER;
    Y NUMBER;
    Z NUMBER; 
BEGIN
    x := TO_NUMBER ('25.59','999D999' );
    y := TO_NUMBER ('1.12345E+5','9.99999EEEE');
    z := TO_NUMBER (123456789.12');

    DBMS_OUTPUT.PUT_LINE(
        TO_CHAR(x,'099.999'));
    DBMS_OUTPUT.PUT LINE(
        TO_CHAR(y, '99.9EEEE'));
    DBMS_OUTPUT.PUT_LINE 
        TO_CHAR(z,'$999G999G999D99'));
    DBMS_OUTPUT.PUT_LINE
        TO_CHAR(1900,'RN'));
END; 
/
025.590
1.123E+05
$123,456,789.12
MCM

IEEE754浮動小数点型として「BINARY_FLOAT」と「BINARY_DOUBLE」が、Oracleバージョン10からは実装されました。

これらの型を、新しい変換関数で扱うことができます。

SELECT TO_BINARY_FLOAT('25.59','999D99')
FROM dual
;
SELECT TO_BINARY_DOUBLE('1.12345E+2','9.99999EEEE')
FROM dual
; 

また、各種の数値型を互いに自由に変換する為に、変換関数を使用する事ができます。

DECLARE
    x NUMBER := 1;
    y BINARY_FLOAT;
    z BINARY_DOUBLE;
BEGIN
    y := TO_BINARY_FLOAT(x);
    z := TO_BINARY_DOUBLE(y); 
    x := TO_NUMBER(z);
    DBMS_OUTPUT.PUT_LINE (x);
END
;
/

Oracle(オラクル)のさまざまな型変換

それぞれ次の関数を使用すれば、「LOB」(Large Object)またはテキストデータ(char) を「CLOB」(Character Large Object)または「NCLOB」(National Character Large Object)に変換する事ができます。

TO_CLOB({lob|char})
TO_NCLOB({lob|char)) 

それぞれ次の関数を使用すれば、データベースまたは該当の言語の文字セット(National Character Set)に変換する事ができます。

TO_CHAR({nchar|clob|nclob})
TO_NCHAR({char|clob|nclob}) 

次の関数をを使用すれば、シングルバイト文字とマルチバイト文字との間で相互に変換する事ができます。

TO_MULTI_BYTE(文字列)
TO_SINGLE_BYTE(文字列) 

次の様にに記述すれば、「LONG」と「LONG RAW」の値を「BLOB (Binary Large Object)」と「CLOB」に変換する事ができます。

INSERT INTO target_table (blobカラム,clobカラム)
    SELECT TO_LOB(long_raw), TO_LOB(long)
    FROM ソーステーブル
;

この様な方法で、「INSERT文」に与えるサブクエリの「SELECT」のリストだけで「TO_LOB」を使用することがでます。

スポンサーリンク

Oracle(オラクル)でのデータ更新

「UPDATE文」を使用すれば、テーブル内の既存のデータを変更できます。

1つの行を更新できるだけでなく、条件により複数の行を更新することもできます。

「UPDATE文」に複数の値を設定することも、サブクエリで新しい値を生成することもできます。

Oracle(オラクル)での単純な更新

単純な「UPDATE」は次の形式になる。

例文

UPDATE テーブル
SET カラム = 値,カラム = 値...
WHERE 述部

この形式では、更新したい1つまたは複数の行を述部で設定します。

更新したいカラムごとに「カラム = 値の組」を、いくつでも設定できます。

例文

UPDATE maker
SET website = 'maker_homepage.com'
WHERE maker_name = 'KAWASAKI'
;

新しい値を1つだけ設定するときには、1つの行だけを更新したいことが一般的です。

その様な場合、「WHERE句」の述部で、行を特定する条件を設定する必要があります。

多数の行を更新するには、式を使用して次の様な「UPDATE」を記述します。

UPDATE bike
SET bike_name = UPPER(bike_name), 
    kakaku = ROUND((kakaku * 1.10),2)
;

このSQL文は、バイクの価格を「10%上げ」さらに「各バイク名を大文字」に変換しています。

Oracle(オラクル)でサブクエリの結果で更新

サブクエリを使用して、新しい値を設定することもできます。

その1つの方法は、更新したい各カラムごとに、別々のサブクエリを書くことです。

UPDATE テーブル SET カラム = (サブクエリ),カラム = (サブクエリ), .... 

例文

UPDATE bike
SET total_parts = (
    SELECT SUM(parts_id) 
    FROM parts
    WHERE parts.bike_id = cd.bike_id); 

この様なサブクエリは、常に相関している必要があります。

また、1つの行と1つのカラムだけを抽出しなければなりません。

このSQL文のサブクエリは、「パーツテーブル」の「パーツIDの数を合計」し、その値をバイクテーブルの「total_parts」に合計部品数をカラムに格納します。

また複数のカラムの値を返すサプクエリを記述する方法もあります。

その場合、次の様に更新したいカラムの個数と、抽出する値の個数が一致しなければなりません。

UPDATE テーブル
SET (カラム、カラム, ...) = (サブクエリ)

例文

UPDATE bike
SET (first_parts, maker_name) = (
    SELECT parts_name, maker_name
    FROM parts
    WHERE parts.bike_id = bike.bike_id 
    AND parts_id = 1)
; 

このSQL文は、各バイクの最初のパーツ名とメーカー名を、バイクテーブルに格納している。

Oracle(オラクル)でのビューとサブクエリを更新

「DELETE」の場合と同様に、更新文の対象としてビューまたはサブクエリを設定することができます。

例文

UPDATE bike 
SET bike_name = INITCAP(bike_name); 

一般的には、ビューまたはサブクエリに対して更新を実施するために、特定のビューの行を1つのテーブルの行に明確に対応させる必要があります。

Oracle(オラクル)でのパーティションを更新

Oracleでは、更新を特定のパーティションやサプパーティションに限定することができます。

UPDATE テーブル PARTITION パーティション名 

または次の様に設定する。

UPDATE テーブル SUBPARTITION サブパーティション名 

プログラムにパーティションとサブパーティションの名前を埋め込む時には、DBA(データベース管理者)がパーティションを変更することが有り得るので注意が必要です。

場合によっては、Oracleの最適化プログラムが、「WHERE句」の述部に基づいてパーティションを適切に取り除くことがあります。

Oracle(オラクル)で更新したデータを抽出する

Oracleでは、次の形式の「UPDATE」が使用することで、更新した行に関する情報を抽出する事ができます。

UPDATE ...
SET ...
WHERE ...
RETURNING 式 [,式...]
INTO 値 [,値...]

1行だけを更新すると、Oracleは結合変数に値を渡して抽出します。

複数の行を更新すると、Oracleは結合配列に値を渡して抽出します。

スポンサーリンク

Oracle(オラクル)でのデータ削除

「DELETE文」を使用すれば、テーブルから行を削除できます。

一般的な例文は次のとおりです。

DELETE
FROM データソース
WHERE 述部

DELETEでは、データソースとしてテーブルを設定することも他のターゲットタイプを設定することも可能です。

しかし、テーブルからデータが、最終的には削除されます。

例文のとおりにSQL文を実行するときは、「ROLLBACK」ロールバックを事前に実行しておく事により、データを完全な状態に戻すことができます。

次の「DELETE」のSQL文では、テーブルのすべての行を単純に削除しています。

例文

DELETE FROM parts;

一般的には、「WHERE句」で条件を設定し、削除すべき1つまたは複数の行を設定します。

次のSQL文は、「KAWASAKI」というメーカー名のパーツをすべて削除しています。

これは、テーブルエイリアス(別名)の使用例も示しています。

例文

DELETE FROM parts s
WHERE s.maker_name = 'KAWASAKI'
; 

次はもっと複雑な「DELETE文」のSQL文であり、サブクエリを使用して、パーツのないバイクをすべて削除しています。

例文

DELETE FROM bike c
WHERE c.bike_id NOT IN (
    SELECT DISTINCT s.bike_id
    FROM parts s);

「WHERE句」を書く場合は、述部の記述には「SELECT文」を記述する際と同様の記述をしないと、「SELECT文」を書く場合と同じ問題が発生します。

Oracle(オラクル)で全行を削除

データベースでは各行の削除をログに記録する必要があるので、「DELETE」でテープルからすべての行を削除すると入出力の負荷が高くなり、時間が掛かります。

そこで、「TRUNCATE TABLE文」を使用すれば、ログに記録しないで、すばやくテーブルを空にする事ができます。

但し「TRUNCATE文」を「ROLLBACK」することはできませんので、注意してください。

「TRUNCATE」を使用すれば、瞬間的にテーブルのデータが空になります。

やり直しはできませんので、十分に注意してください。

次の例文は、partsテーブルから、すべての行を削除しています。

例文

TRUNCATE TABLE parts;

Oracleには、テーブルに割り当てられている領域を保存する方法があります。

例文

TRUNCATE TABLE patrs REUSE STORAGE; 

「REUSE STORAGE」オプションを使用すれば、データの削除後に同じ量のデータを再びテーブルに投入する場合に、割り当て済みのテーブル領域を保持することができます。

Oracle(オラクル)でビューとサブクエリから削除

「DELETE」の対象は、テーブルだけではなく、ビューまたはサブクエリも対象とできます。

例えば次の例文の「DELETE」は、「KAWASAKI」というメーカー名の部品のうち、「長さが50センチを越えるもの」を全て削除しています。

例文

DELETE FROM (
    SELECT * FROM parts
    WHERE maker_name = 'KAWASAKI') parts_query
WHERE parts_query.parts_length > 500
;

Oracle(オラクル)では、「ビューまたはサブクエリに対する削除」を最終的に「テーブルからの行の削除に置き換える必要」がある為、「ビューとサブクエリからの削除」については、さまざまな制限を与えています。

例えば「ビューまたはサブクエリの1行」を、「該当するテーブルの1行」だけに明確に置き換えることができなければ、「ビューまたはサブクエリから行を削除できない」可能性が発生する為です。

Oracle(オラクル)でパーティションから削除

Oracleでは、特定のパーティションから削除することができる。

例文

DELETE FROM maker PARTITION (jpn_maker)
WHERE maker_name = 'MITSUBISHI'
; 

又、特定のサプパーティションからも削除することもできます。

例文

DELETE FROM maker SUBPARTITION (jpn_maker01)
WHERE maker_name = 'MITSUBISHI'
;

しかし実際には、パーティションやサブパーティションの名前をSQL文の中に書き込む事はありません。

例文

DELETE FROM maker
WHERE maker_name = 'MITSUBISHI' 
AND kuni = 'JP'
;

この例で取り上げた3つの「DELETE」は、どれも同等の結果となります。

3番目のSQL文では、Oracleは、「WHERE句」の述部の「kuni」から、どのパーティションを探すべきかを判定しています。

Oracle(オラクル)で削除したデータを抽出する

Oracleでは、次の形式の「DELETE」を使用できます。

これは、削除した行に関する情報を抽出します。

DELETE FROM ...
WHERE ...
    RETURNING 式 [,式...] 
[BULK COLLECT] INTO 変数 [,変数...」 

式には、一般にカラム名を設定するか、またはカラム名で構成します。

「ROWID」を設定することも可能です。

「PL/SQL」と互換性のある型の変数または結合変数を、データの代入先の変数に設定しなければなりません。

代入先の変数として「PL/SQL」の集合型を設定し、さらに「BULK COLLECT」キーワードを使用しなければ、複数の行を「DELETE」で削除する事ができません。

例文

DECLARE
    TYPE maker_id_array IS ARRAY (100) OF NUMBER; 
    maker_ids maker_id_array;
BEGIN
    DELETE FROM maker
    RETURNING maker_id BULK COLLECT INTO maker_ids;
END;
/ 

適切な個数の適切な型のフィールドが入っているレコードを設定する事で、それぞれの式毎の代入先の変数設定の代替とできます。

スポンサーリンク

Oracle(オラクル)でのデータ挿入方法

「INSERT文」を使用すれば、テーブルに新しい行を挿入する事ができます。

1行だけ挿入することもサブクエリの結果を挿入することもできます。

Oracle(オラクル)で行を挿入する

次の例文の様に「INSERT」を使用すれば、テーブルに1行を追加する事ができます。

INSERT INTO table
(カラム,カラム, カラム...)
VALUES (値,値,値...) 

次のSQL文は、「maker」テーブルにバイクメーカーを追加しています。

「VALUES句」は、テーブル名の後に並べたカラムに対応します。

例文

INSERT INTO maker
(maker_id, maker_name)
VALUES ('20','KAWASAKI')
; 

「INSERT文」でカラムを省略した場合は、テーブル作成時に設定したデフォルト値の「NULL」が自動的に挿入されます。

例文

INSERT INTO maker
(maker_name)
VALUES ('KAWASAKI')
; 

「DEFAULT」を使用すれば、明示的にカラムにデフォルト値を設定することを示す事ができます。

例文

INSERT INTO maker
(maker_id, maker_name)
VALUES ('30', DEFAULT)
; 

明確に「NULL」値をカラムに挿入する場合は「NULL」を使用すれば、デフォルト値が「NULL」以外の値になっている場合でも対応できます。
例文

INSERT INTO maker
(maker_id, maker_name)
VALUES ('60', NULL)
; 

カラムのリストを省略する場合は、テーブル作成時に設定したカラムの順序で、「VALUES」リストにカラムの値を並べます。

例文

INSERT INTO maker
VALUES ('60', 'SUZUKI')
; 

新しいカラムが対象のテーブルい追加された場合には、追加がエラーとなる為、一度きりの挿入でない場合は、カラムのリストを設定しておきます。

Oracle(オラクル)での挿入の対象

「INSERT」の対象はテーブルだけでなく、ビューやサブクエリにも挿入することもできます。

例文

INSERT INTO (SELECT * FROM bike)
    (bike_id, bike_name, kakaku)
     VALUES ('20', 'Ninja1000', '1200000')
; 

Oracleでは、ビューとサブクエリにインラインビューの様に挿入することも可能です。

Oracle(オラクル)でサブクエリを挿入する

サブクエリを利用すれば、次の様に多数の行を挿入することができます。

INSERT INTO テーブル (カラム, カラム...) 
    (SELECT 式, 式...
     FROM ソーステーブル
     ...)

この形式の「INSERT」では、「SELECT文」は、追加対象のテーブルに並んでいるカラムと一致するカラムを抽出する様に記述しておく必要があります。

なお、サブクエリは括弧で囲みます。

サブクエリは、どの様な構文であっても、有効な「SELECT文」であれば問題ありません。

サブクエリが「0行・1行・または多数の行」を抽出する場合でも特に問題ありません。

次のSQL文は、複数の行を挿入するものです。

例文

INSERT INTO parts (bike_id, parts_name, maker_name)
    (SELECT bike_id, parts_name, 'YAMAHA'
     FROM parts
     WHERE bike_id=20)
; 

このINSERTは、「bike_id=20」の部品リストを、メーカー名を「YAMAHA」として、「parts」テーブルに追加しています。

Oracle(オラクル)でのダイレクトパスインサート

Oracleでは、サブクエリからの行を挿入するとき、特にその処理性能を高める事ができる「ダイレクトパスインサート(direct-path insert)」という挿入方法が実装されています。

「APPEND」を使用すれば、この様な挿入を実行できます。

例文

INSERT /*+ APPEND */
    INTO parts (bike_id, parts_name, maker_name)
    (SELECT bike_id, parts_name, 'YAMAHA'
     FROM parts
     WHERE bike_id=20)
; 

データベースのバッファキャッシュの使用を回避する方法が、「ダイレクトパスインサート」です。

直接「データファイル」に「データ」を書き込みます。

また、既存の空きスペースを再利用せずに、高水位標(high-water mark)よりも上の領域に書き込みます。

Oracle(オラクル)で挿入した値を抽出する

挿入したばかりの値を返す方法があります。

例えばテーブルの主キー(プライマリキー)が自動的に増加する場合や挿入したばかりの行について、どんな値のキーが割り当てられたかを調べる場合に使用します。

その場合は、「SELECT文」で行を取り戻す代わりに、「RETURNING句」を使用します。

例文(SQL*Plus)

VARIABLE atai1 VARCHAR2 (25);
VARIABLE atai2 VARCHAR2 (25);

INSERT INTO maker (maker_id, maker_name) 
VALUES ('50', 'HONDA')
RETURNING maker_name INTO :atai1, :atai2
;

「SQL*Plus」で、結合変数を生成するには「VARIABLE」コマンドを使用します。

このSQL文では2つのカラムを抽出しています。

複数のカラムを抽出するには、カラム名と結果の変数をカンマで区切ります。

RETURNING カラム1, カラム2, … INTO :値1, :値2…

Oracle(オラクル)で複数のテーブルに挿入する

Oracleでは、「INSERT」を実行することで、一度に複数のテーブルに行を挿入する事ができます。

サブクエリの結果の「どの行をどのテーブルに挿入するか」を述部で選択する事も、「複数のテーブルに無条件で挿入」することもできます。

述部を書いて選択する場合、1つ成功したところで停止するのか、複数のテーブルに行を挿入するのか選択する事もできます。

Oracle(オラクル)で無条件で複数のテーブルに挿入する

サブクエリの結果を複数のテーブルに挿入するには、「INSERT ALL」を使用します。

例文

INSERT ALL
    INTO buhin_catalog1 (id, maker_name)
    VALUES (buhin_id, maker_name)

    INTO buhin_catalog2 (id, buhin_name)
    VALUES (buhin_id, UPPER (buhin_name))

    SELECT buhin_id, buhin_name, maker_name
    FROM buhin_table
; 

この「INSERT」は、「buhin_table」の値を別々の2つのテーブルに挿入しています。

サブクエリの結果で抽出されたデータを、2つのテーブルに別々に挿入しています。

Oracle(オラクル)で条件つきで複数のテーブルに挿入する

「WHEN句」を使用すれば、条件に基づいて複数のテーブルに挿入する事ができます。

次の「INSERT」は、「buhin_id」の等価条件式(=)に基づき3つのテーブルに分けてデータを挿入している。

例文

INSERT ALL
 WHEN buhin_id = 10 THEN
    INTO buhin_table101
    VALUES (buhin_id, buhin_name, maker_name, car_id)

 WHEN buhin_id IN (20,30) THEN
    INTO buhin_table201
    VALUES (buhin_id, buhin_name, maker_name, car_id)

 ELSE
    INTO buhin_table999
    VALUES (buhin_id, buhin_name, maker_name, car_id)

    SELECT * FROM buhin_table
; 

この文の「ELSE句」は、どの基準にも一致しない行をすべて「buhin_table999」テーブルに挿入しています。

「ELSE句」は付けなくても問題ありません。

「ELSE句」を省略すれば、どの「WHEN条件」にも一致しない行を無視する事ができます。

Oracle(オラクル)での「ALL対FIRST」

条件付きで複数のテーブルに「INSERT」で挿入する場合に、それぞれの「WHEN句」でサブクエリが返したすべての行を抽出するには「ALL」を設定します。

行が複数の句の基準を満たす場合、該当の複数のテーブルに挿入することができます。

しかし「INSERT FIRST」を使えば、最初に一致する「WHEN句」の後で処理を停止する事ができます。

スポンサーリンク

Oracle(オラクル)でのデータマージの方法

Oracleバージョン8からは、「MERGE文」が実装されています。

「MERGE」使用すれば、行が存在するときには更新し、行が存在しないときには挿入する事ができます。

基本的な例文は次のとおりとなります。

MERGE INTO テーブル 名称
USING データソース ON(存在テスト)
WHEN MATCHED THEN UPDATE
    SET カラム = 値, カラム = 値...
WHEN NOT MATCHED THEN INSERT 
    (カラム, カラム, ...)
    VALUES (値, 値, ....

データソース :: = { テーブル | ビュー | (サブクエリ) }

新しいと思われる「メーカー名」を「maker」テーブルにマージするには、次の例文の様に記述します。

例文

MERGE INTO maker c
USING
    (SELECT * FROM new_makers) nc
     ON (c.maker_id = nc.mker_id)

WHEN MATCHED THEN UPDATE
    SET c.maker_name = nc.maker_name,
         c.skuni = nc.kuni

WHEN NOT MATCHED THEN INSERT
    (maker_id, maker_name, kuni)
    VALUES (nc.maker_id, nc.maker_name, nc.kuni)
;

「WHERE」条件を「UPDATE」と「INSERT」の両方の操作に付ける事もできます。

さらに、「UPDATE」操作の後で、「DELETE」で行を削除することも可能です。

例文

MERGE INTO maker c
    USING (SELECT * FROM new_makers) nc
    ON (c.maker_id = nc.maker_id)

WHEN MATCHED THEN UPDATE
    SET c.maker_name = nc.maker_name, 
      c.kuni = nc.kuni
    WHERE c.maker_name <> nc.maker_name
    DELETE WHERE c.maker_id = 30

WHEN NOT MATCHED THEN INSERT
    (maker_id, maker_name, kuni)
    VALUES (nc.maker_id, nc.maker_name, nc.kuni)
    WHERE nc.maker_name IS NOT NULL
; 

この例文では、「maker_id」が同じだが「maker_name」が異なる場合は、「maker_name」を更新し、さらに更新後の「maker_id=30」のレコードを全て削除しています。

また、「maker_name」が「NULL」でない名前の新しいレコードを追加しています。

更新後の削除を、「DELETE WHERE」で実施しており、「MERGE文」で更新の対象とならない行は、削除の対象にりません。

スポンサーリンク

Oracle(オラクル)SQLでのNULL値取り扱いについて

SQL文を書くときは、「NULLと3値論理(真、偽、未定義の3つの値で構成した論理)」を理解することが重要です。

一部の例外を除いては、式に「NULL」が含まれる場合は、その結果自身も「NULL」になる為です。

比較関数を使ったどんな式を記述をしても必ず「NULL」となります。

述部でNULLを扱う

「NULL」を他の値との比較は、標準的な比較関数ではできません。

例えば、次の例文ではテープルのすべての行を抽出すことはできません。

例文

SELECT * FROM exp_table
WHERE name = 'abcde' 
OR name <> 'abcde'
;

この例文では、名前が「abcde」であるか、「abcde」でないかのどちらかとなります。

しかし、名前が「NULL」の場合は、「name = ‘abcde’」とう条件に、「name <> ‘abcde’」条件にも一致しません。

その為、「IS NULL・IS NOT NULL」という「NULL値」を見つける為の記述がSQLには用意されています。

「abcde」以外のすべての名前(名前がNULLを含む)を探すには、次の様に設定します。

例文

SELECT * FROM exp_table
WHERE name <> 'abcde' 
OR name IS NULL
; 

同様に、「NULL」以外の値と明示的に照合するために、「IS NOT NULL」を使用することができます。

CASE(条件分岐式)で「NULL」を扱う場合

「CASE式」を使用して「NULL」になる可能性のあるデータを扱う事が容易である場合があります。

例えば、必ず「NULL」以外の名前(name)を取得したければ、次の様に設定することになります。

例文

SELECT serial_id,
CASE WHEN name IS NOT NULL THEN name 
ELSE '****no data***' END
FROM exp_table: 

Oracle(オラクル)の関数で「NULL」を扱う

「NULL」になる可能性 のあるデータを扱う場合は、Oracleの関数「DECODE」・「COALESCE」・「NVL」・「NVL2」を使用する事もできます。

「COALESCE(合体という意味)」は、Oracleバージョン9から実装された関数です。

この関数は、任意のリストを引数にとり最初に見つかった 「NULL」以外の値を抽出します。

次の例文では、「name」が「NULL」なら「no data」を返し、NULLでなければ「name」を抽出します。

例文

SELECT serial_id, COALESCE(name, '***no data***') 
FROM city
: 

「name」が「NULL」なら「no data」を返し、NULLでなければ「name」を抽出します。

引数を複数設定することもできますが、少なくとも1つは「NULL」以外であることを確認しておく必要があります。

すべての引数が「NULL」なら「COALESCE」は「NULL」を抽出します。

「NVL」は「COALESCE」と同様ですが、引数を2つだけ受け取ります。

次の例文では、「name」が「NULL」なら「no data」を返し、NULLでなければ「name」を返すが、結果は「COALESCE」と同等となります。

例文

SELECT serial_id, NVL(name, '***no data***') 
FROM city
: 

「NVL2」は、最初の値が「NULL」かどうかに応じて、2つの値のうち一方を抽出します。

次の例文では、「name」又は「kn_name」が「NULL」なら「no data」を返し、NULLでなければ、「kn_name」を返す。

例文

SELECT serial_id, NVL2(name, kn_name, '***no data***') 
FROM city
: 

「DECODE」は、インラインIF文と同様であり、さらに「NULL」を扱う手段となります。

次の例文では「name」が「NULL」なら「no data」を抽出し「nihon」なら「tarou」を抽出し、そうでなければ「jirou」を抽出します。

例文

SELECT serial_id,
    DECODE(name, NULL, '***no data***','nihon','tarou','jirou') 
FROM city
: 
スポンサーリンク
ブログランキング・にほんブログ村へ

Oracle(オラクル)での正規表現

Oracleバージョン10では、次の正規表現関数を実装しています。

REGEXP_INSTR(対象文字列, パターン
[,位置し,出現回
[,オプション
[,照合パラメータ]]]])

REGEXP LIKE (対象文字列, パターン
[,照合パラメータ])

REGEXP_REPLACE(対象文字列, パターン
[,置換文字列
[,位置 [,出現回
[,照合パラメータ]]]])

REGEXP_SUBSTR(対象文字列パターン
[,位置 [, 出現回
[,照合パラメータ]]])

引数については次のとおりです。

対象文字列

検索の対象となる文字列。

パターン

検索したいテキストのパターンを記述した正規表現であり、この式の長さが512バイトを越えてはいけません。

置換文字列

置き換えるテキストであり、対象文字列の中でパターンが出現するたびに、置換文字列に置き換えます。

後方参照を使用して、パターン内の部分式に一致する値を参照することができます。

位置

検索を開始すべき文字位置であり、デフォルトは1文字目かつ値は正でなければなりません。

出現回

パターンの何番目の出現を見つけるかを示し、デフォルトは1番目です。

パターンの2番目の出現を見つけたければ2を、パターンの3番目の出現を見つけたけ
れば3を設定します。

オプション

パターンの先頭の文字位置を返すには「0」(デフォルト値)を設定します。

ターンの最後の文字位置を返すには1を設定します。

照合パラメータ

正規表現のパターンマッチングの方法をデフォルトから変更するために、一組のオプションを文字列の形で設定します。

次のオプションを、任意の組み合わせと任意の順序で設定できます。

「'i'」
大文字と小文字を区別しないで照合

「'c'」
大文字と小文字を区別して照合

「'n'」
ピリオド(.)を改行文字として照合

「'm'」
チルダ(^)とドル記号($)を、それぞれ検索対象の文字列内の行の先頭と末尾に対応させます。
通常は、チルダとドル記号を、検索対象の文字列の先頭と末尾に対応させます。

デフォルトで大文字と小文字の区別をするかどうかは、「NLS_SORT引数の設定」で決まります。

次の表は、これらの関数で使用できる正規表現のメタキャラクタの一覧です。

比較演算子機説明
/メタキャラクタをエスケープ
/1~/9後方参照を使用して、部分式に一致する値を参照(置換文字列の引数では/1~/500を使用できます)
.任意の文字に一致
^行の先頭に一致
$行の末尾に一致
[…]一組の文字のどれかに一致
[^…]一組の文字のどれかに一致しない
[.xx.]照合の要素を囲む
[:文字クラス:]大括弧の式の中で文字クラスを指定([:digit:]・ [:alpha:]・[:upper:]など)
[=文字リスト=]等価クラスを指定
*0個以上に一致
+1個以上に一致
?0個又は1個に一致
{x}・{x,y}・{x,}それぞれx回、x回~y回、少なくともx回の出現に一致
;選択肢を区切る
(…)部分式を定義
スポンサーリンク

Oracle(オラクル)でのリテラル

Oracle(オラクル)では、固定のリテラル値をSQL文に埋め込む方法を定めています。

一般に、簡単な比較として「テキスト」と「数値のリテラル」がありますが、この2つには微妙に異なっています。

次に、「日付」と「時刻」のリテラルは非常に複雑となっています。

Oracle(オラクル)でのテキストリテラル

Oracle(オラクル)では、テキストリテラルを一重引用符で囲みます。

'This is a pen'

文字列の中に引用符を埋め込む必要があるときには、2つの引用符を使用します。

'This isn''t a pen' 

SQL文では、リテラルの中にある2つの引用符を、1つの引用符として扱われ上記の例文は次の通り表現されます。

This isn't a pen

該当の言語の文字セットを使用してリテラルを生成するには、テキストリテラルの先頭にプレフィックスとして「N」または「n」を付けます。

N'This is a pen'

n'This is a pen'

Oracleバージョン10では、引用符として別の区切り文字を設定することができます。

代替の引用符は必ず2文字であり、先頭の区切り文字には先に一重引用符を付けます。

末尾の区切り文字には後ろに一重引用符をつけます。

例えば、区切り文字として「'[*]’」を使用する場合は、次の様に記述します。

Q'[This isn't a pen]'

q'[This isn't aapen]' 

先頭の区切り文字として「(・{・[」を使用する場合は特別であり、これらに対応する末尾の区切り文字はそれぞれ「]・}・)」としなければなりません。

通常は、先頭と末尾で同じ区切り文字を使用します。

Q’|This is a pen|’

引用符の区切り文字として「空白・タプ・リターン文字」は使えまません。

Oracle(オラクル)での数値リテラル

数値リテラルでは、規格に基づいて数字を記述します。

568
658.45
+355
-333.58

小数点をつけない数字は、整数と見なされます。

Oracleでは、後ろに「F」または「D」をつけて、「FLOAT」または「DOUBLE」を設定できます。

587D
625.56F
+365d
-654.46f

浮動小数点の定数を記述する時は、科学表記を使用することもできます。

325.65E+65
845.98e-85

これらのリテラルは、

325.65×10の65乗・845×10の-23乗(845÷10の23乗)を表しています。

Oracle(オラクル)での日時リテラル

Oracle(オラクル)Lは、日付、時刻、タイムスタンプのリテラルについて、次のフォーマットを定義しています。(時刻は24時間表記)

DATE 'yyyy-mm-dd'
TIME 'hh:mi:ss [{+|-}hh:mi]'
TIMESTAMP 'yyyy-mm-dd hh:mi:ss [{+|-}hh:mi]'

次の3つの例文はそれぞれ

2000年01月01日
午前10時00分
太平洋標準時の2000年01月01日00時00分

を表現しています。

DATE '2000-01-01'
TIME '10:00:00'
TIMESTAMP '2000-01-01 00:00:00 -8'

Oracle(オラクル)での日時間隔リテラル

Oracle(オラクル)は「INTERVAL YEAR TO MONTH」リテラルを次の様に定義しています。

INTERVAL '年-月' YEAR TO MONTH
INTERVAL '年' YEAR
INTERVAL '月' MONTH 

上記のフォーマットだけでなく、年の桁数を設定する方法が、Oracleバージョン9以降では実装されていいます。(設定しない場合は2桁となります)。

INTERVAL '50-10' YEAR TO MONTH
INTERVAL '2012' YEAR (4) 

Oracle(オラクル)は「INTERVAL DAY TO SECOND」リテラルを次の様に定義しています。

 
INTERVAL 'dd hh:mi:ss.ff' DAY TO SECOND
INTERVAL 'hh:mi' HOUR TO MINUTE
INTERVAL 'mi' MINUTE
等々・・・

「INTERVAL DAY TO SECOND」リテラルで、日から秒の範囲で、任意の時刻の要素を設定できます。

Oracleバージョン9以降では、日(dd)と秒の小数部 (ff) が、デフォルトで2桁となります。

スポンサーリンク

Oracle(オラクル)でのフラッシュバッククエリ

フラッシュバッククエリ「flashback query)」という機能が、Oracle(オラクル)バージョン9では実装されています。

これは、過去のいつかの時点で存在していたデータを抽出する機能です。

フラッシュバッククエリを開始するには、「FROM句」でテーブル名の後に「AS OF」を使

例文

SELECT a.buhin_name
FROM buhin_table
AS OF TIMESTAMP TIMESTAMP '2000-01-01 01:01:01.01' a
WHERE a.car_id = 10

テーブル別名の位置は、かつてテーブルが存在していた日時の後に付けます。

「AS OF」を置く場所は、テーブル名と自分のクエリでテーブルにつけたい別名の間になります。

「TIMESTAMP」が2つ記述されていますが、問題ありません。

「AS OF TIMESTAMP」は、フラッシュバックの対象をタイムスタンプの形で与えること意味している為です。

そのタイムスタンプを、変数またはリテラル(定数)で設定します。

例文では、「TIMESTAMP」で始まる「TIMESTAMP」リテラルを使用しています。

SQL文のそれぞれのテーブル(またはビュー)毎に、「AS OF」に別の引数を設定でき、また引数を何も設定する必要はありません。

例文

SELECT a.buhin_name, c.car_name
FROM buhin_table
  AS OF TIMESTAMP 
    TIMESTAMP '2000-01-01 01:01:01.01' a
  INNER JOIN automobile
  AS OF TIMESTAMP 
    TIMESTAMP '2000-01-01 10:01:01.01' c
  ON a.car_id = c.car_id; 

フラッシュバックの基準として、タイムスタンプを使用する代わりに、「SCN (System Change Number : システム変更番号)」を利用することもできます。

例文

SELECT a.buhin_name, c.car_name
  FROM automobile c,
    (SELECT * 
     FROM buhin_table
     WHERE kuni = 'JAPAN')
     AS OF SCN 12345678 a
WHERE c.car_id = a.car_id (+)
; 

フラッシュバックの対象を設定するには、タイムスタンプよりも「SCN」のほうが正確です。

何故なら、タイムスタンプは、最終的には5分間隔で記録される「SCN」に変更され、次に過去5日間のデータベース履歴しか保持されない為です。

スポンサーリンク

Oracle(オラクル)でのトランザクション管理

トランザクション(transaction)とは、一組の操作の集合を意味します。

トランザクションは、その内の全ての操作が完了した状態になるか、又はすべての操作が完了していない状態のどちからしかありません。

トランザクション環境で作業をする場合は、トランザクションの開始方法と終了方法を知っておく必要があります。

さらに、トランザクションの様々な特性の設定方法も知る必要があります。

Oracle(オラクル)でのトランザクションを開始

Oracle(オラクル)の場合は、常にトランザクションが実行されています。

接続した後で最初に実行する「SQL文」が、暗黙のうちにトランザクションを開始することとなります。

また、トランザクションが終了した後で、次に実行するSQL文が、暗黙のうちにトランザクションを開始します。

Oracleのデフォルトのトランザクションタイプは、原則として読み書きモードです。

明示的にトランザクションを開始するには、「SET TRANSACTION」を使用します。

SET TRANSACTIONオプション [NAME トランザクション名]
オプション ::=
READ {ONLY | WRITE}
| ISOLATION LEVEL {SERIALIZABLE | READ COMMITTED}
| USE ROLLBACK SEGMENT セグメント名

オプションと引数は次のとおりです。

NAME 'トランザクション名'

トランザクションの名前を設定します。(255バイトまで)。

「COMMIT」を実行す ると、トランザクション名はトランザクションのコメントとして保存され、 「COMMIT」のコメントを上書きします。

トランザクション名は、特に分散トランザクションで役に立ちます。

READ ONLY

トランザクションを読み出し専用にします。

つまり、トランザクション開始後のコミット済みの変更を「参照」しません。

READ WRITE

デフォルトのトランザクションタイプにします。

つまり、文レベルの読み取り一貫性を維持した読み書きトランザクションにします。

ISOLATION LEVEL SERIALIZABLE

S読み書きモードの直列化可能なトランザクションにします。

ISOLATION LEVEL READ COMMITTED 

デフォルトのOracleのトランザクションの動作をします。

USE ROLLBACK SEGMENT セグメント名 //廃止されました

「SET TRANSACTION文」のSQL文は次のとおりです。

例文

SET TRANSACTION READ ONLY;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; 

SET TRANSACTION 
    ISOLATION LEVEL READ COMMITTED;
    NAME 'Delete all attractions';

分散トランザクションの名前を設定し、そのトランザクションが失敗すると、その名前が「DBA_2PC_PENDING テーブルのTRAN_COMMENTカラム」に出現します。

Oracle(オラクル)でトランザクションを終了する

トランザクションを終了し、トランザクションによる変更を永久的に反映ずるには、次の様に「COMMIT文」を実行します。

例文

COMMIT [WORK]

Oracleでは、オプションで「COMMENT句」を設定できます。

例文

COMMIT [WORK] [COMMENT 'コメント']

「WORK」は、ANSI/ISOのSQL規格で認められたオプションですが、通常は省略されます。

Oracleでは、トランザクションを開始するときに「SET TRANSACTION」で設定した名前が、トランザクションをコミットするときに設定するコメントを上書きします。

Oracleでは、分散トランザクションを強制的にコミットしたいとき、次の記述を使用します。

例文

COMMIT [WORK] FORCE
    {'ローカルトランザクションID' | 'グローバルトランザクションID'}
    (SCN) 

分散トランザクションを特定するには、ローカルまたはグローバルのトランザクションIDを使用します。

「DBA_2PC PENDING ビュー」からトランザクションIDを取得できます。

「SCN (System Change Number: システム変更番号)」を割り当てることもできますし、設定しない場合は、現在のSCNが割り当てられます。

Oracle(オラクル)でトランザクションを中止する

「ROLLBACK文」を使用すれば、Oracle(オラクル)でトランザクションを中止する事ができます。

例文

ROLLBACK [WORK]

「COMMIT」の場合と同様、「WORK」は通常は、省略されます。

「ROLLBACK」でトランザクションをロールバックすると、そのトランザクションの変更が全て元に戻ります。

Oracleでは、次の記述を使用すれば、分散トランザクションを強制的にロールバックする事ができます。

例文

ROLLBACK [WORK] FORCE
    {'ローカルトランザクションID' | 'グローバルトランザクションID'}

分散トランザクションを特定するには、ローカルまたはグローバルのトランザクションIDを使用します。

「DBA_2PC PENDING ビュー」からトランザクションIDを取得できます。

Oracle(オラクル)でトランザクションのセーブポイントまでロールバックする方法

トランザクションの全体をロールバックする代わりに、トランザクションの一部だけをロールバックすることができます

そのためには、セーブポイント「savepoint」というマークをトランザクションに付ける必要があります。

次の記述で設定できます。

例文

SAVEPOINT セーブポイント名 

次の記述を使用すれば、いずれかのセーブポイントにロールバックする事ができます。

例文

ROLLBACK [WORK] TO セーブポイント名

次に例文を示します。

例文

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
UPDATE maker SET kuni = UPPER (kuni);
SAVEPOINT kuni_upper_cased;
DELETE FROM buhin_table;
ROLLBACK TO kuni_upper_cased;
COMMIT
;

このトランザクションは、すべての状態表記を大文字に設定します。

「buhin_table」テーブルに対するDELETEを元に戻すため、「UPDATE文」の後に設定したセーブポイントまで、ROLLBACKでロールバックしています。

スポンサーリンク

まとめ

基本的なSQL文は、このページで紹介しているとおり次のとおり!

SELECT
    a.id
    COUNT(*)

FROM
    table1 a
    table2 b

WHERE
    a.id = b.id

HAVING
    COUNT(*) > 1

後は、左外部結合、削除、挿入等を、目的に応じて使い分ける!

ブログ開設に必要なドメイン取得、サーバーレンタル、ASPの登録等は、こちらのサイトから!

スポンサーリンク
スポンサーリンク

-060-SQL

執筆者:


comment

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

関連記事

code1-eyecatch

SQLの基本はここから「射影」select文!

スポンサーリンク SQL 射影 続いて、射影を行ってみます。 射影は、カラム(列)を抽出することです。 先ほどの、テーブル(表)を使って、実践していきます。 射影の例は、次のとおりです。 select …

code1-eyecatch

SQLの基本はここから「選択」select文!

SQL 選択 続いて、選択を行ってみます。 選択は、行を抽出することです。 先ほどの、テーブル(表)を使って、実践していきます。 選択の例は、次のとおりです。 select  * from  koji …

code1-eyecatch

SQL文の基本はここから「選択と射影」select~from~where~and!

SQL 選択と射影 続いて、選択と射影を同時に行ってみます。 選択は、行を抽出すること、射影は、列を選択することです。 先ほどの、テーブル(表)を使って、実践していきます。 select  kojin …

code1-eyecatch

SQL・サンプルデータベース・MS-ACCESS!これで実際に体験してみよう!

これまでの、基本をMS-ACCESS(マイクロソフトアクセス)に纏めてみました。 スポンサーリンク SQLサンプデータベース(MS-ACCESS)ダウンロード ここからダウンロード クエリーのデザイン …

code1-eyecatch

SQLを無料で使える環境を作る方法!A5:SQL Mk-2は無料で高機能!

まずは、SQLを使える環境整備です。 まず、SQLが使えるソフトウエアを用意します。 A5:SQL Mk-2は、フリーウエアでも高機能 フリーウエアであれば、A5:SQL Mk-2 が高機能で良いかと …