2010年12月08日

11.表の作成と管理

●データベースオブジェクト
表やビュー、索引などのデータベースの中で扱われるものをデータベースオブジェク
トという。

データベースオブジェクト
・表…データを格納する。
・ビュー…1つ以上の表からのデータのサブセット。データは保持しない
・順序…一意な数値を生成する
・索引…問合せのパフォーマンスを向上させる目的で使用される。
・シノニム…オブジェクトの別名

・ネーミング規則
データベースオブジェクトは、スキーマ内で一意でなければならない。

オブジェクト名のネーミングルール
・文字で開始する必要がある
・長さ30バイト以下
・英文字、記号が使用可能
・Oracleの予約語(例:CREATE,TABLE)は使用できない。
・同一のスキーマ内で一意な表名、同一表内で一意な列名でなければならない
・大文字/小文字は区別されない

●スキーマ
スキーマ…オブジェクトの集合
例)販売管理システム=商品、売上などの複数の表=オブジェクトの集合
Oracleでは、スキーマを所有するユーザー名をスキーマ名とする。
ユーザーが、SQLにおいてオブジェクト名を指定する場合、自スキーマ内のオブジェ
クトとして解釈される。
他スキーマのオブジェクトを扱うときには、次のようにオブジェクト名の前にスキー
マ名を指定する
スキーマ名.オブジェクト名

例)SYSTEMユーザーが他スキーマ(STUDY)の部門表を検索する
SQL>SELECT * FROM study.部門;

※ユーザーはそれぞれ1つのスキーマを所有する

●表
表は、リレーショナルデータベースのデータを格納するモデル。
表を作成するときには、表名、列名、列のデータ型、制約などを指定する。
・表の作成
表の作成には、CREATE TABLE文を使用する。
 
構文)
CREATE TABLE 表名
   [列名 データ型 [ DEFAULT 式]
   [ ,列名 データ型 ] ・・・)
   [ TABLESPACE 表領域名 ]
 
例)
SQL>CREATE TABLE 大阪社員
    ( 社員番号 NUBMER,
     社員名  VARCHAR2(20),
     入社日  DATE;
 
・DEFAULTオプション
DEFAULTオプションを使用することにより、列にデフォルト値を設定することができる。
列に値がない行を挿入すると、DEFAULTオプションで指定された値が設定される。
これにより、NULL値が挿入されることを防止できる。
デフォルト値には、リテラル、式、またはSYSDATEなどのSQL関数を使用できる。

※デフォルト値に指定する式や値は列のデータ型に一致させる必要がある。
デフォルト値を設定してある列にNULLを挿入したい場合は、明示的にNULLを指定する。
 
●データ型
CHAR(size)・・・固定長文字データ
VARCHAR2(size)・・・可変長文字データ
LONG・・・可変長文字データ(最大2GB)
CLOG・・・文字データ(最大4GB)
NUMBER(p,s)・・・可変長数値データ
DATE・・・日付と時刻の値
RAW および LONG RAW・・・RAWバイナリデータ
BLOB・・・バイナリデータ(最大4GB)
BFILE・・・外部ファイルに格納されるバイナリデータ(最大4GB)
ROWID・・・表名の行の一意のアドレスを表す、Base64の記数法
 
 
●制約
・制約の作成
制約には、列単位に定義する「列制約構文」と、表単位に定義する「表制約構文」がある。
制約を作成するには、CREATE TABLE文で、制約条件を列または表単位に定義する。
列制約構文と表制約構文は混在して使用することができる。
NOT NULL制約は列制約でのみ定義可能。
複数の列を組み合わせて1つの制約を定義する場合は表制約で定義する必要がある
 
列制約構文)
CREATE TABLE 表名
( 列名 データ型 [ CONSTRAINT 制約名 ] 制約
        [[ CONSTRAINT 制約名 ] 制約 ・・・]
[ , 列名 データ型 [ CONSTRAINT 制約名 ] 制約
        [[ CONSTRAINT 制約名 ] 制約 ・・・]])
 
表制約構文)
CREATE TABLE 表名
( 列名 データ型
 [ , 列名 データ型 ] ・・・
  , [ CONSTRAINT 制約名 ] 制約
  [ , [ CONSTRAINT 制約名 ] 制約 ・・・])

※制約名はオブジェクトのネーミング規則に従う。また、スキーマ内の他のオブジェクト名と重複してはいけない。
制約名を省略すると、SYS_Cn(nには一意な数値)の形式で名前が付けられる。
制約はデータディクショナリーに格納される。
 
●NOT NULL制約
NOT NULL制約はNULL値を禁止する。列制約構文のみで指定可能
 
例)NOT NULL制約の定義
SQL>CREATE TABLE test1
    (col1 NUMBER CONSTRAINT test1_col1_nn NOT NULL,
     col2 VARCHAR2(10));
 
●UNIQUE制約
指定された列または列の組み合わせに同じ値は入力されることを禁止する
同じ列にNOT NULLが定義されていない限り、1つ以上のNULL値を入力できる
→UNIQUE制約はNULL以外の値が重複していないかを評価する
Oracleは、値が重複していないことを評価するために、UNIQUE制約を定義すると、
暗黙的に制約名と同じ名前の一意索引を作成する。
 
例)列制約構文を使用して制約を定義する
SQL>CREATE TABLE test2
    (col2 NUMBER CONSTRAINT test2_col2_U UNIQUE,
     col2 VARCHAR2(10));
 
例)複合UNIQUE制約の定義
SQL>CREATE TABLE test3
    (col1 NUMBER,
     col2 VARCHAR2(10),
     CONSTRAINT col1_col2_U UNIQUE(col1,col2));
 
※複合UNIQUE制約の場合、列単独で見ると値が重複していも、列の組み合わせで値が重複していなければ、行を挿入することができる。
 
●CHECK制約
明示的に入力条件を定義し、条件を満たすデータのみが入力可能。 
 
例)列制約構文と表制約構文を使用したCHECK制約
SQL>CREATE test4
    (col1 NUMBER CONSTRAINT test4_col1_CK
               CHECK (col1 BETWEEN 100 AND 999),
    col2 CHAR(1),
    CONSTRAINT test4_col2_CK CHECK (col2 IN ('M','F')));
 
CHECK制約の場合は、CHECKキーワードの後ろに「(列名 条件)」を記述するので、
列制約構文でも表制約構文でも記述の仕方は同じ。

CHECK制約は問合わせの条件と同じ要素を使用できるが、次の指定はできない。
・CURRVAL,NEXTVAL,LEVEL,ROWNUM疑似列の参照
・SYSDATE,UID,USER,USERENV関数の使用
・別の行の値を参照する問合せ
 
●PRIMARY KEY制約
表名の行データを一意に特定できる列または列の組み合わせの中で主たるもおを主キー(PRIMARY KEY)という。
PRIMARY KEY制約は、自動的にUNIQUE制約とNOT NULL制約が不可される。
複数の列を組み合わせた複合主キーは表制約構文のみで指定可能。
Oracleは、値が重複していないことを評価するために、PRIMARY KEY制約を定義すると、制約名と同じ名前の一意索引を暗黙的に作成する。
 
例)PRIMARY KEY制約を列制約構文で定義する
SQL>CREATE TABLE test5
    (col1 NUMBER CONSTRAINT test5_PK PRIMARY KEY,
     col2 VARCHAR2(10));

※PRIMARY KEY制約を宣言できるのは、表にただ1つだけ。
 
●FOREIGN KEY(外部キー)制約
FOREIGN KEY制約は、行と行の間の関係付けを保つために使用される。
子表(参照する側)の参照列のことを外部キー(FOREIGN KEY)という。
親表(参照される側)の参照される列のことを親キーという。
親キーはREFERENCES句を用いて指定する。
外部キーが参照する先は、同じ表の異なる列でもかまわない。
親キーは、主キー(PRIMARY KEY)または一意キー(UNIQUE)のいずれかでなくてはいけない。
 
列制約構文)
CREATE TABLE 表名
 (列名 データ型 CONSTRAINT 制約名 REFERENCES 参照表[(参照列)],・・・) 
※参照列は省略することができる。省略した場合は、参照表の主キー列を参照する。
例)部門表を参照するFOREIGN KEY制約
SQL>CREATE TABLE 部門
(部門番号 NUMBER CONSTRAINT 部門_PK PRIMARY KEY,
部門名 VARCHAR2(20));

SQL>CREATE TABLE 社員
(社員番号 NUMBER CONSTRAINT 社員_PK PRIMARY KEY,
社員名 VARCHAR2(20),
部門番号 NUMBER CONSTRAINT 社員_部門_FK
                REFERENCES 部門(部門番号));
※外部キーの値は、親表に存在する値またはNULLでなければならない。
(NOT NULL制限が同時に定義されていない場合に限る)

表制約構文)
CREATE TABLE 表名
(列名 データ型,
・・・,
CONSTRAINT 制約名 FOREIGN KEY(外部キー列) REFERENCES 参照表[(参照列)],
・・・)
 
表制約構文の場合は「FOREIGN KEY(外部キー列)」を記述する必要がある。
参照先の参照列は省略することができる。省略した場合、参照先の主キー列を参照する。

例)
SQL>CREATE TABLE 社員2
    (社員番号 NUMBER CONSTRAINT 社員2_PK PRIMARY KEY,
     社員名 VARCHAR2(20),
     上司番号 NUMBER,
     部門番号 NUMBER,
     CONSTRAINT 社員2上司_FK FOREIGN KEY (上司番号)
     REFERENCES 社員2(社員番号));
 


●ON DELETEキーワード
FOREIGN KEY制約には、親表の行が削除されたら、子表の該当行も一緒に削除するという定義ができる。
ON DELETE CASCADEキーワードを明記することで、参照されている親表の行を削除する際に、
エラーを起こさずに、参照している行も一緒に削除することができる。
 
例)CREATE TABLE 注文明細
(注文明細番号 NUMBER CONSTRAINT 注文M_PK PRIMARY KEY,
注文番号    NUMBER CONSTRAINT 注文M_注文番号_FK PREFERENCE 注文ヘッダー(注文番号)
                                          ON DELETE CASCADE,
商品名      VARCHAR2(20),
数量        NUMBER);
 
FOREIGN KEY制約で指定できるキーワード
ON DELETE CASCADE 参照している親表の行が削除されたら、子表の該当行も一緒に削除する
ON DELETE SET NULL 参照している親表の行が削除されたら、子表の該当行の外部キー列の値をNULLにする


●既存表からの新しい表の作成
 
構文)
CREATE TABLE 表名
[(列名,列名,・・・)]
AS 副問合せ
 
例)
CLEATE TABLE 社員_営業 AS
SELECT 社員番号,社員名,給与 FROM 社員
WHERE 部門バング = 30;

 
列名を明記する方法)
CLEATE TABLE 部門別給与合計
(部署NO,給与合計,人数)
AS SELECT 部門番号,SUM(給与),COUNT(*)
FROM 社員
GROUP BY 部門番号;







 
SELECT リストに列別名を指定する方法)
CLEATE TABLE 部門別給与合計
AS SELECT 部門番号 AS 部門NO,
         SUM(給与) AS 給与合計,
         COUNT(*) AS 人数
 FROM 社員
 GROUP BY 部門番号;
 
<注意点>
・列定義には、列名、デフォルト値、整合性制約を含めることができる。
・列を指定する場合、列数を副問合せのSELECTリストと一致させなければならない。
・表は指定された列名で作成され、副問合せによって取り出された行が表に挿入される。
・列を指定しない場合、表の列名は、副問合せのSELECTリストの列名と同じ。
・副問合せのSELECTリストに式を指定する場合は、列別名を指定する。
・NOT NULL制約とデータ型のみコピーされる
・NOT NULL以外の制約はコピーされない。














tetora119 at 22:59|PermalinkComments(4)TrackBack(0)

2010年12月06日

10.データの操作

●INSERT文の基本構文
INSERT文は表に新たな行を追加したいときに使用するSQL。
INTO句の後ろには対象となる表名、VALUES句の後ろに続くかっこの中には追加したい
列の値を記述する。

構文)
INSERT INTO
 [(列名,列名,・・・)]
  VALUES (値,値,・・・)

・値を挿入する列を表名の後ろに明示的に指定する。
・指定する順序は任意でよい。
・VALUES句で指定する値は、挿入する列の記述順に指定する。
・VALUES句の値は、挿入する列の数およびデータ型と一致していなければならない。
・全列に値を指定したい場合は、すべての列を任意の順序で指定するか、表名の後ろ
の列名のリストを省略することができる。省略した場合、値のリストは表作成時に定
義した列の順序に従って記述する。

例)社員番号列、社員名列、部門番号列を指定して挿入する
SQL>INSERT INTO 社員 (社員番号,社員名,部門番号)
  VALUES (9999,'佐藤',20);

例)列名のリスト省略する
SQL>INSERT INTO 社員
  VALUES (1111,'武田','営業',7788,
  TO_DATE('2010-04-01','yyyy-mm-dd'),240000,50000,30);

●NULL値の挿入
列の値にNULL値を指定する場合は、NULLキーワードを使用する

例)
SQL>INSERT INTO 社員
 VALUES (2222,'鈴木','業務',7788,
  TO_DATE('2010-10-01','yyyy-mm-dd'),600000,NULL,30);

特定の列だけを指定したINSERT文を実行した場合、指定されなかった列にはNULL値が
挿入される。ただし、NULL値以外のデフォルト値があらかじめ定義されている場合は、
その値が挿入される。

NULL値以外にも、今日の日付を返すSYSDATE関数を使用することもできる。
SQL>INSERT INTO 社員 (社員番号,社員名,入社日,部門番号)
  VALUES (5555,'高田',SYSDATE,30);

※挿入時に起こり得るエラーは次の順で評価される。
(1)NOT NULL列における値の欠落(NOT NULL制約違反)
(2)重複値による一意違反(主キー制約、一意制約違反)
(3)チェック制約違反
(4)外部キー制約違反
(5)データ型の不一致
(6)有効桁数を超える値

●UPDATE文の基本構文
UPDATE文は、既存行の列の値を変更したいときに使用するSQL。
SET句に変更したい列名と値、WHERE句に変更の対象となる行の条件を記述する

構文)
UPDATE
 SET 列名 = 値 [,列名 = 値,列名 = 値,・・・]
 [WHERE 変更の対象となる行の条件]

※WHERE句は省略することができるが、省略すると全行が更新される。

例)社員'五十嵐'の担当を'部長'に更新する場合
SQL>UPDATE 社員
 SET 職務 = '部長'
 WHERE 社員名 = '五十嵐';

※SET句は定数だけでなく、関数や計算式などを指定することができる。
例)社員'五十嵐'の給与を2倍に更新する場合
SQL>UPDATE 社員
 SET 給与 = 給与*2
 WHERE 社員名 = '五十嵐'

・複数列の更新
SET句では「,」を使用し、複数列を指定できる。
例)社員'五十嵐'の担当を'主任'、給与を1/2に更新する場合
SQL>UPDATE 社員
  SET 職務 = '主任', 給与 = 給与/2
  WHERE 社員名 = '五十嵐';

※UPDATE文でデータの更新が行われると、Oracleは行レベルロックを実行する

●DELETE文の基本構文
DELETE文は、既存行を削除したいときに使用するSQL

構文)
DELETE [FROM] 
  [WHERE 削除の対象となる行の条件]

※WHERE句は省略することができるが、省略すると全行が削除される。
※DELETE文は、FROMキーワードを省略できる。

例)
SQL>DELETE FROM 社員
WHERE 社員番号 = 1111;


DELETEでは行が削除されるが、表は削除されない。
表そのものを削除する場合は、DROP TABLE文を使用する。

●TRUNCATE(表の切り捨て)文
表からすべての行を削除する場合には、TRUNCATE TABLE文を使用する。
DELETEとは以下の点で異なる。
・データ定義(DDL)文であり、行の削除をロールバックできない
・すべての行を削除する(条件を指定できない)
・表の削除トリガーが実行されない
・参照整合性制約の親表の場合は、エラーとなる(事前に制約を無効にしておくこと
が必要)

●副問合せを用いたINSERT文
INSERT文のVALUES句の代わりに副問合せを使用することができる。
結果的に、INSERT対象の表に、副問合せで使用した表の行をコピーすることになる。
INSERT句に指定した列の数およびデータ型は、副問合せの列の数およびデータ型に一
致させる必要がある。

例)VALUES句の代わりに副問合せを使用した場合
SQL>INSERT INTO 営業 (EMPNO,NAME,HIREDATE,SAL,COM)
  SELECT 社員番号,社員名,入社日,給与,歩合給
  FROM 社員
  WHERE 職務 = '営業';

※副問合せが1件も返さなくても、INSERT文はエラーにならない。
1件も返されない場合、0件を挿入することになる。

●副問合せを用いたUPDATE文
UPDATE文のSET句およびWHERE句で副問合せを使用することができる。
WHERE句で使用する場合、副問合せが戻す値と一致する行がUPDATEの対象となる。

例)UPDATE文のWHERE句で副問合せを使用した場合
SQL>UPDATE 社員
  SET 給与 = 給与 + 20000
  WHERE 給与 = (SELECT MIN(給与) FROM 社員);

例)UPDATE文のSET句で副問合せを使用した場合
SQL>UPDATE 社員
  SET 給与 = (SELECT MAX(給与) FROM 社員
        WHERE 職務 = '営業')
    ,歩合給 = (SELECT MAX(給与) FROM 社員
        WHERE 職務 = '営業')
  WHERE 社員番号 = 7844;

例)上の更新は下のように記述することができる
SQL>UPDATE 社員
  SET (給与,歩合給) = (SELECT MAX(給与),MAX(歩合給)
             FROM 社員
             WHERE 職務 = '営業')
  WHERE 社員番号 = 7844;

●副問合せを用いたDELETE文

例)DELETE文のWHERE句で副問合せを使用した場合
SQL>DELETE FROM 社員
  WHERE 給与 = (SELECT MAX(給与) FROM 社員);

●トランザクションとは
ORACLEでいうトランザクションとは、ORACLEに接続してから、あるいは前回のコミッ
ト(またはロールバック)から、次のコミット(またはロールバック)までの一連の
処理(SELECT,UPDATE,INSERT,DELETE)のこと。

●トランザクションを制御するコマンド
データ定義言語(DDL)やデータ制御言語(DCL)は、1文でトランザクションを構成する。
データ操作言語(DML)は任意の数の文でトランザクションを構成する。

・トランザクションを制御するSQL文
SAVEPOINT name     現行のトランザクションにセーブポイントのマーク付けを
する
ROLLBACK TO SAVEPOINT マーク付けしたセーブポイント以降のトランザクションを
取り消す
ROLLBACK  現行のトランザクション内の変更(DMLによる操作)をすべて取り消し、
トランザクションを終了する
COMMIT   現行のトランザクション内の変更(DMLによる操作)をすべて確定し、ト
ランザクションを終了する

●トランザクション終了前の状態
DML対象となる行はロックされる。そのため、他のユーザーはその行を問い合わせる
ことはできるが、(変更操作前のデータが表示される)該当行に対してDML操作を行
うことはできない。トランザクションが終了するまで待機する。
ロックを取得したトランザクションが終了すると、ロックは解除される。そして、待
機していたトランザクションに制御が移る。
トランザクションは次のいずれかが発生すると終了する。
・COMMITまたはROLLBACKの発行
・DDL文(CREATE、ALTER、TRUNCATE、DROPなど)の実行
・DCL文(GRANT、REVOKEなど)の実行
・SQL*PLUS、SQL Developerの終了
・マシン障害やシステムクラッシュの発生
※DDL文またはDCL文は、自動的にコミットされるため、暗黙的にトランザクションが
終了する

●COMMIT
トランザクションの確定を行うコマンドをコミット(COMMIT)という。
一度コミットしたトランザクションを取り消す(ROLLBACK)ことはできない。
SQL>COMMIT;

COMMIT後の状態は次のようになる
・データの変更内容がデータベースに反映される
・すべてのユーザーが変更後の内容を検索することができる
・ロックが解除される
・セーブポイントが消去される

●ROLLBACK
トランザクションの取り消しを行うコマンドを、ロールバック(ROLLBACK)という。ロー
ルバックは、1つのSQL文だけを取り消すのではなく、トランザクション全体を取り消
す。
SQL>ROLLBACK;

ROLLBACK後の状態は次のようになる
・変更前の状態にリストア(復元)される
・ロックが解除される

●文レベルロールバック
トランザクションの進行中に1つのDML文でエラーが発生した場合は、その文のみがRO
LLBACKされる。
ORACLEでは暗黙のセーブポイントが実装されているため、エラーが発生した文より前
に実行されていたDML操作による変更は保持される。

●セーブポイント
ロールバック(ROLLBACK)する位置を指定するコマンドを、セーブポイント(SAVEPOINT)
という。ロールバックする際にセーブポイントを指定することで、セーブポイント以
降の処理(セクション)のみ取り消すことができる。

SQL>SAVEPOINT A;
SQL>ROLLBACK TO A;

※1つのトランザクション中で同一のセーブポイントを使用すると、最初のセーブポ
イントは消去される。

●暗黙的なトランザクション処理
・暗黙のコミット
暗黙のコミット(自動コミット)によって終了されたトランザクションは、明示的な
コミットと同様、ロールバックすることはできない。
暗黙のコミットは次の状況で発生する
・DDL文またはDCL文を発行した
・SQL*PLUSおよびSQL Developerを正常に終了した

SQL*PLUSにはSET AUTOCOMMIT {ON|OFF}のコマンドがある。
ONにすると、各DML文は実行されるとすぐにコミットされ、ロールバックすることは
できない。OFFに設定すると、明示的(または暗黙的)にコミットされるまでロール
バックすることができる。

・暗黙のロールバック
エラーが発生したSQL文はロールバックされる。また、障害が発生した場合、ORACLE
は回復処理の際にトランザクションをロールバックする。これらの自動的に行われる
ロールバックを暗黙のロールバックという。
暗黙のロールバックは次の状況で発生する
・SQL*PLUSおよびSQL Developerの異常終了時
・システム障害

●読取りの一貫性
読取りの一貫性とは、検索を始めた時点のデータを検索が終わるまで保証することを
いう。読取りの一貫性の目的は、すべてのユーザーに対して、DML操作の開始前の最
後のコミット時点でのデータが表示されるようにすること。
読取りの一貫性は自動的に実現される。変更時には、変更開始前のデータのコピーが
取り出され、UNDOセグメントの中に書き込まれる。変更を発行したユーザーを除くす
べての読取りユーザーには、UNDOセグメントのデータが表示される。

●ロック
ロックとは、同一のデータに対して、複数のトランザクションが同時に書き込むこと
を防止するメカニズム。Oracleデータベースのロックは自動化されているために、ユー
ザーは基本的に何も設定する必要はない。
DMLは行ロックを発生させる。WHERE句の条件に一致した行にロックを設定する。ロッ
クが設定されている行を、他のユーザーが更新することはできない。ロックが解除さ
れるのを待たなければいけない。ロックは、トランザクション終了時に解除される。

・SELECT ~FOR UPDATE
検索(SELECT)の際にロックをかけておき、トランザクションが終了するまで、他のユー
ザーに更新されないようにすることができる。通常、SELECT文はロックを取得しない
が、FOR UPDATE句を使用することで、問合せ対象の行にロックをかけることができる。

SELECT 列リスト FROM 表
[WHERE 条件]
FOR UPDATE [ OF 列名 [{ NOWAIT|WAIT n }]

FOR UPDATE句を付けると、SELECT文で対象となる行に対して、行レベルの排他ロック
が取得される。NOWAITを指定すると、既に他のセッションによってロックが取得され
ている場合は、ロック待ちせずに、エラーで返すことができる。
WAITを指定しないと、先に取得されたロックが解除されるまで、SELECT FOR UPDATE
は待機(WAIT)状態になる。

待機時間(秒数)を明示的に指定することもできる

例)SQL>SELECT 社員名,給与 FROM 社員
    WHERE 社員番号 = 7399
    FOR UPDATE WAIT 5;

結合を使用したSELECT文にもFOR UPDATE句をしようすることができる。問合せ対象の
すべての表の行にロックが取得される。
「FOR UODATE OF 列名」を指定した場合、指定した列名を保有する表の行のみがロッ
クされる。


tetora119 at 23:42|PermalinkComments(0)TrackBack(0)

9.集合演算子の使用

●集合演算子
UNION ALL・・・各問合せで選択された重複を含むすべての行
UNION・・・各問合せで選択された重複を除くすべての行
MINUS・・・最初の問合せによって選択されたが、2番目以降の問合せでは選択されなかった重複を除くすべての行
INTERSECT・・・すべての問合せで共通に選択された重複を除くすべての行

●UNIONおよびUNION ALL演算子の使用方法

例)社員番号名列と部門番号列を使用したUNION ALL
SQL>SELECT 社員番号 FROM 社員
    UNION ALL
    SELECT 部門番号 FROM 部門;

例)社員番号名列と部門番号列を使用したUNION
SQL>SELECT 社員番号 FROM 社員
    UNION ALL
    SELECT 部門番号 FROM 部門;

※UNIONを使用した場合、重複値を取り除くための過程により行がソートされて表示される。

●MINUS演算子の使用
MINUS演算子は、「最初の問合せによって選択されたが、2番目以降の問合せでは選択されなかった重複を除くすべての行」を戻す。

例)部門表の部門番号列と社員表の部門番号列を使用したMINUS
SQL>SELECT NVL(部門番号,99) FROM 部門
      MINUS
    SELECT NVL(部門番号,99) FROM 社員;

●INTERSECT演算子の使用方法
INTERSECT演算子は、「すべての問い合わせで共通に選択された重複を除くすべての
行」を戻す。

例)SQL>SELECT NVL(部門番号,99) FROM 部門
    INTERSECT
    SELECT NVL(部門番号,99)  FROM 社員;

※MINUS演算子とINTERSECT演算子では、UNION演算子と同様に、結果は重複行を除い
てソートされる。また、INTERSECTは内部結合と同等の結果を返すことができる。

●集合演算子の使用ガイドライン
(1)最初の問合せの列名または列別名が、結果の列名に使用される
(2)SELECT句において、列の数と各問合せの対応する列のデータ型を同じにする
※型変換は行わない(CHAR型とVARCHAR型は同じデータ型グループ(文字)のため、
エラーにはならない)
※結合する列が、長さの同じCHAR型である場合、戻り値はその長さのCHAR型になる
※異なる長さのCHAR型同士の場合は、長さは長いほうに合わせ、型はVARCHAR2型にな
る。※問合せのいずれかの列がVARCHAR2型の場合、戻り値はVARCHAR2型になる
(3)集合演算子は、副問合せで使用することが可能
(4)集合演算子は2つ以上使用することが可能
(5)UNION ALL以外の結果は、重複行を除いて戻される。
(6)UNION ALL以外の結果は、デフォルトでは1列目の値で昇順にソートされる
(7)ORDER BY句は文の最後のみ使用可能(各問合せでの使用は不可)
(8)ORDER BY句では、最初の問合せの列名または列別名を使用する





tetora119 at 00:18|PermalinkComments(1)TrackBack(0)