13-01.データディクショナリ(Data Dictionary)とは

■データ・ディクショナリ（Data Dictionary)とは

　・読み取り専用で提供されるテーブルやビューの集合でデータベース全般の情報を提供する。

　・オラクルデータベースはコマンドが実行される毎にデータ・ディクショナリにアクセスする。

　・DB作業の間、Oracleはデータ・ディクショナリを読みオブジェクトの存在とユーザーに適切なAccess権限が有るかを確認する。また、Oracleはデータ・ディクショナリを更新しながらDATABASE構造、監査、ユーザー権限、データ等の変更を反映していく。

　・データ・ディクショナリに保存される内容

　　-オラクルのユーザー情報

　　-オラクル権限とロール情報

　　-データベースのスキーマオブジェクト(TABLE, VIEW, INDEX, CLUSTER, SYNONYM, SEQUENCE...)情報

　　-ユニーク制約条件に関する情報

　　-オラクルデータベースの関数はプロシザー及びトリガーに関する情報

　　-その他、一般DATABASE情報

■データ・ディクショナリの分類

　・ALL_XXXX

　　-ALL_で始まるデータ・ディクショナリで、特定ユーザーが検索可能なすべてのデータ・ディクショナリを意味する。

　　-検索しようとするオブジェクトが自分のではなくても、そのオブジェクトにアクセスできる権限を持っているなら、ALL_XXXXビューで検索可能。

SQL> SELECT table_name, tablespace_name
  2  FROM ALL_TABLES;

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
DUAL                           SYSTEM
SYSTEM_PRIVILEGE_MAP           SYSTEM
TABLE_PRIVILEGE_MAP            SYSTEM
STMT_AUDIT_OPTION_MAP          SYSTEM
AUDIT_ACTIONS                  SYSTEM
WRR$_REPLAY_CALL_FILTER        SYSAUX
HS_BULKLOAD_VIEW_OBJ           SYSTEM
HS$_PARALLEL_METADATA          SYSTEM
HS_PARTITION_COL_NAME          SYSTEM
HS_PARTITION_COL_TYPE          SYSTEM
HELP                           SYSTEM
....

　・DBA_XXXX
　　-DBA権限が有るユーザーのみ検索できるデータ・ディクショナリですべてのオラクルデータベースのオブジェクトの情報が見れる。

　　-SELECT ANY TABLE権限があるユーザーも問合せ可能で、他のユーザーが問合せするには前に「SYS.」の接頭語をつけなければいけない。

--SYSユーザーで接続し、下記を実施してみよう
SQL> SELECT OWNER, OBJECT NAME
  2  FROM SYS.DBA_OBJECTS;

■V$_XXXX
　・Dynamic Performance Viewとも言う。現在のDatabaseの状態に関する情報で、DBAのみアクセスが許可されている。
　・主にDBAのモニタリング作業用情報を提供し、X$テーブルをベースとするビューである。

■X$_XXXX
　・X$ビューはV$ビューで見れない情報を見せてくれる
　・X$テーブルはオラクルメモリ情報を見られるSQLインターペースビューでOracleデータベースの隠れている領域の１つだ。

■Oracle Database全体データディクショナリ
　・http://docs.oracle.com/cd/B28359_01/server.111/b28320/index.htm

12-02.階層クエリの活用

Oracle10gからCONNECT BYで提供するCONNECT_BY_ROOT, SYS_CONNECT_BY_PATH, CONNECT_BY_ISLEAF機能について…

■CONNECT_BY_ROOT
　・階層構造クエリでLEVELが0の最上位ROWの情報を取得できる。

SQL> SELECT LPAD(' ', 4*(LEVEL-1)) || ename ename, empno,
  2  CONNECT_BY_ROOT empno "Root empno", level
  3  FROM emp
  4  START WITH job = 'PRESIDENT'
  5  CONNECT BY PRIOR empno = mgr;

ENAME                     EMPNO Root empno      LEVEL
-------------------- ---------- ---------- ----------
KING                       7839       7839          1
    JONES                  7566       7839          2
        SCOTT              7788       7839          3
            ADAMS          7876       7839          4
        FORD               7902       7839          3
            SMITH          7369       7839          4
    BLAKE                  7698       7839          2
        ALLEN              7499       7839          3
        MARTIN             7654       7839          3
        TURNER             7844       7839          3
        JAMES              7900       7839          3
    CLARK                  7782       7839          2
        MILLER             7934       7839          3

13行が選択されました。

■CONNECT_BY_ISLEAF
　・階層構造クエリでROW最下位レベルかどうかを返す。最下位なら1、じゃなければ0

SQL> SELECT LPAD(' ', 4*(LEVEL-1)) || ename ename, empno,
  2         CONNECT_BY_ISLEAF "leaf", level
  3  from emp
  4  START WITH job = 'PRESIDENT'
  5  CONNECT BY NOCYCLE PRIOR empno = mgr;

ENAME                     EMPNO       leaf      LEVEL
-------------------- ---------- ---------- ----------
KING                       7839          0          1
    JONES                  7566          0          2
        SCOTT              7788          0          3
            ADAMS          7876          1          4
        FORD               7902          0          3
            SMITH          7369          1          4
    BLAKE                  7698          0          2
        ALLEN              7499          1          3
        MARTIN             7654          1          3
        TURNER             7844          1          3
        JAMES              7900          1          3
    CLARK                  7782          0          2
        MILLER             7934          1          3

13行が選択されました。

■SYS_CONNECT_BY_PATH
　・階層構造クエリで現在のROWのPATH情報を簡単に検索できる。

--SQL*Plusできれいに見るために
SQL> COL PATH FORMAT A40

--SYS_CONNECT_BY_PATH例
SQL> SELECT LPAD(' ', 4*(LEVEL-1)) || ename ename, empno,
  2         SYS_CONNECT_BY_PATH(ename, '/') "PATH"
  3    FROM emp
  4   START WITH job='PRESIDENT'
  5  CONNECT BY PRIOR empno=mgr;

ENAME                     EMPNO PATH
-------------------- ---------- ----------------------------------------
KING                       7839 /KING
    JONES                  7566 /KING/JONES
        SCOTT              7788 /KING/JONES/SCOTT
            ADAMS          7876 /KING/JONES/SCOTT/ADAMS
        FORD               7902 /KING/JONES/FORD
            SMITH          7369 /KING/JONES/FORD/SMITH
    BLAKE                  7698 /KING/BLAKE
        ALLEN              7499 /KING/BLAKE/ALLEN
        MARTIN             7654 /KING/BLAKE/MARTIN
        TURNER             7844 /KING/BLAKE/TURNER
        JAMES              7900 /KING/BLAKE/JAMES
    CLARK                  7782 /KING/CLARK
        MILLER             7934 /KING/CLARK/MILLER

13行が選択されました。

　・下記のようにLeaf Nodeだけ、全体PATH情報が出るように作成できる。

SQL> SELECT LEVEL, SUBSTR(SYS_CONNECT_BY_PATH(ename, ','), 2) path
  2  FROM emp
  3  WHERE CONNECT_BY_ISLEAF = 1
  4  START WITH mgr IS NULL
  5  CONNECT BY PRIOR empno = mgr;

     LEVEL PATH
---------- ----------------------------------------
         4 KING,JONES,SCOTT,ADAMS
         4 KING,JONES,FORD,SMITH
         3 KING,BLAKE,ALLEN
         3 KING,BLAKE,MARTIN
         3 KING,BLAKE,TURNER
         3 KING,BLAKE,JAMES
         3 KING,CLARK,MILLER

7行が選択されました。

■ORDER SIBLINGS BY ・階層構造クエリで相関関係を維持しながら整列させる

SQL> COL ename FORMAT A25
SQL> COL ename2 FORMAT A10

--ORDER SIBLINGS BY例
SQL> SELECT LPAD(' ', 4*(LEVEL-1)) || ename ename,
  2         ename ename2, empno, level
  3  FROM emp
  4  START WITH job = 'PRESIDENT'
  5  CONNECT BY NOCYCLE PRIOR empno = mgr
  6  ORDER SIBLINGS BY ename2;

ENAME                     ENAME2          EMPNO      LEVEL
------------------------- ---------- ---------- ----------
KING                      KING             7839          1
    BLAKE                 BLAKE            7698          2
        ALLEN             ALLEN            7499          3
        JAMES             JAMES            7900          3
        MARTIN            MARTIN           7654          3
        TURNER            TURNER           7844          3
    CLARK                 CLARK            7782          2
        MILLER            MILLER           7934          3
    JONES                 JONES            7566          2
        FORD              FORD             7902          3
            SMITH         SMITH            7369          4
        SCOTT             SCOTT            7788          3
            ADAMS         ADAMS            7876          4

13行が選択されました。

--きれいに整列された。



--ORDER BY例
SQL> SELECT LPAD(' ', 4*(LEVEL-1)) || ename ename,
  2         ename ename2, empno, level
  3  FROM emp
  4  START WITH job = 'PRESIDENT'
  5  CONNECT BY NOCYCLE PRIOR empno = mgr
  6  ORDER BY ename2;

ENAME                     ENAME2          EMPNO      LEVEL
------------------------- ---------- ---------- ----------
            ADAMS         ADAMS            7876          4
        ALLEN             ALLEN            7499          3
    BLAKE                 BLAKE            7698          2
    CLARK                 CLARK            7782          2
        FORD              FORD             7902          3
        JAMES             JAMES            7900          3
    JONES                 JONES            7566          2
KING                      KING             7839          1
        MARTIN            MARTIN           7654          3
        MILLER            MILLER           7934          3
        SCOTT             SCOTT            7788          3
            SMITH         SMITH            7369          4
        TURNER            TURNER           7844          3

13行が選択されました。

--変な整列になっている。

12-01.階層クエリ(hierarchical query)とは

■階層クエリ（Hierarchical Queries)とは？
　-またの名を再帰クエリ (recursive query)と言う。
　-再帰的な問い合わせを行う SELECT ステートメントである。階層構造を持つデータなどに使う。
　　って言ってもよく分かんないんで、実例で見てみよう。

　-scottユーザーのempテーブルを見ると、empnoとmgrコラムがあり、
　　mgrコラムのデータは各社員の管理者empnoを意味する。

SQL> SELECT empno, ename, sal, mgr FROM emp;

     EMPNO ENAME             SAL        MGR
---------- ---------- ---------- ----------
      7369 SMITH             800       7902
      7499 ALLEN            1600       7698
....

13行が選択されました。

--社員SMITHの管理者の社員番号は7902
--社員ALLENの管理者の社員番号は7698

この上位階層と下位階層の関係をORACLEではSTART WITHとCONNECT BYを使って
簡単に検索できる。

■階層構造クエリSynctax
・START WITH
　-階層問合せ文のルートで使用される行を指定する。
　-サブクエリを使用できる。

・CONNECT BY
　-階層クエリで上位階層と下位階層の関係を定義できる。
　-PRIOR演算子と一緒に使用して階層構造で表現できる。
　-CONNECT BY PRIOR 下位コラム=上位コラム：上位から下位へのツリー構造(Top Down)
　-CONNECT BY 下位コラム=PRIOR 上位コラム：下位から上位へのツリー構造（Bottom Up）
　-CONNECT BY NOCYCLE PRIOR：NOCYCLEパラメータを利用してループ防止
　-サブクエリを使えない

・LEVEL Pseudocoolumn
　-LEVELは階層構造クエリ結果のDepthを表現するコラム

・ORDER SIBLINGS BY
　-ORDER SIBLINGS BYを使用すると階層構造クエリで簡単に整列できる。

・CONNECT BY実行順番は以下になる。
　-１．START WITH
　-２．CONNECT BY
　-３．WHERE

■階層構造クエリ例
　-基本例：職業PRESIDENTを基準に階層構造で検索する例。

--LEVELコラムでdepthが見れる
--JONESの管理者はKINGでSCOTTの管理者はJONES
--上下の階層構造を検索できる
SQL> SELECT LEVEL, empno, ename, mgr
  2  FROM emp
  3  START WITH job = 'PRESIDENT'
  4  CONNECT BY PRIOR empno = mgr;

     LEVEL      EMPNO ENAME             MGR
---------- ---------- ---------- ----------
         1       7839 KING
         2       7566 JONES            7839
         3       7788 SCOTT            7566
         4       7876 ADAMS            7788
         3       7902 FORD             7566
         4       7369 SMITH            7902
         2       7698 BLAKE            7839
         3       7499 ALLEN            7698
         3       7654 MARTIN           7698
         3       7844 TURNER           7698
         3       7900 JAMES            7698
         2       7782 CLARK            7839
         3       7934 MILLER           7782

13行が選択されました。

■LEVELの活用 　
　-LEVEL Pseudocolumnを利用すると階層構造クエリを様々に活用できる。
　-下記例は、LEVELに空白を追加し、階層構造を一目で見れるようにした例

--SQL*PLUSできれいに見るための設定
SQL> COL ename FORMAT A20;

--左にLEVEL程の空白を追加して階層構造を検索
SQL> SELECT LEVEL, LPAD(' ', 4 * (LEVEL-1)) || ename ename, empno, mgr, job
  2  FROM emp
  3  START WITH job = 'PRESIDENT'
  4  CONNECT BY PRIOR empno = mgr;

     LEVEL ENAME                     EMPNO        MGR JOB
---------- -------------------- ---------- ---------- ---------
         1 KING                       7839            PRESIDENT
         2     JONES                  7566       7839 MANAGER
         3         SCOTT              7788       7566 ANALYST
         4             ADAMS          7876       7788 CLERK
         3         FORD               7902       7566 ANALYST
         4             SMITH          7369       7902 CLERK
         2     BLAKE                  7698       7839 MANAGER
         3         ALLEN              7499       7698 SALESMAN
         3         MARTIN             7654       7698 SALESMAN
         3         TURNER             7844       7698 SALESMAN
         3         JAMES              7900       7698 CLERK
         2     CLARK                  7782       7839 MANAGER
         3         MILLER             7934       7782 CLERK

13行が選択されました。

■以下はLEVEL別で給与合計と社員数を検索する例

SQL> SELECT LEVEL, SUM(sal) total, COUNT(empno) cnt
  2  FROM emp
  3  START WITH job = 'PRESIDENT'
  4  CONNECT BY PRIOR empno = mgr
  5  GROUP BY LEVEL
  6  ORDER BY LEVEL;

     LEVEL      TOTAL        CNT
---------- ---------- ----------
         1       5000          1
         2       8275          3
         3      12600          7
         4       1900          2

■PRIORの活用
　・PRIOR演算子をSELECTで使用してみよう。

　・下記は社員の管理者をPRIOR演算子を利用して検索する例

--SQL*PLUSできれいに見るための設定
SQL> COL mgrname FORMAT A10;

--SELECTで"PRIOR ename mgrname"を確認してみる
SQL> SELECT LEVEL, LPAD(' ', 4*(LEVEL-1)) || ename ename,
  2         PRIOR ename mgrname,
  3  empno, mgr, job
  4  FROM emp
  5  START WITH job = 'PRESIDENT'
  6  CONNECT BY PRIOR empno = mgr;

     LEVEL ENAME                MGRNAME         EMPNO        MGR JOB
---------- -------------------- ---------- ---------- ---------- ---------
         1 KING                                  7839            PRESIDENT
         2     JONES            KING             7566       7839 MANAGER
         3         SCOTT        JONES            7788       7566 ANALYST
         4             ADAMS    SCOTT            7876       7788 CLERK
         3         FORD         JONES            7902       7566 ANALYST
         4             SMITH    FORD             7369       7902 CLERK
         2     BLAKE            KING             7698       7839 MANAGER
         3         ALLEN        BLAKE            7499       7698 SALESMAN
         3         MARTIN       BLAKE            7654       7698 SALESMAN
         3         TURNER       BLAKE            7844       7698 SALESMAN
         3         JAMES        BLAKE            7900       7698 CLERK
         2     CLARK            KING             7782       7839 MANAGER
         3         MILLER       CLARK            7934       7782 CLERK

13行が選択されました。

■Bottom Up検索例
　・上の例を逆順（下位から上位へのツリー構造、Bottom Up）で検索する。

--SQL*PLUSできれいに見るための設定
SQL> COL ename FORMAT A20;

--enameを基準にBottom Upで検索する例
SQL> SELECT LEVEL, LPAD(' ', 4*(LEVEL-1)) || ename ename, empno, mgr, job
  2  FROM emp
  3  START WITH ename = 'SMITH'   --最下位ノード値を記載
  4  CONNECT BY empno = PRIOR mgr;

     LEVEL ENAME                     EMPNO        MGR JOB
---------- -------------------- ---------- ---------- ---------
         1 SMITH                      7369       7902 CLERK
         2     FORD                   7902       7566 ANALYST
         3         JONES              7566       7839 MANAGER
         4             KING           7839            PRESIDENT

11-07.サブクエリ（Subquery）【UNION [ALL], INTERSECT, MINUS演算子】

■UNION [ALL]、INTERSECT、MINUS演算子
　-UNIONとUNION ALLの違いは整列作業があるか、ないか。
　-UNIONは重複を除去のため整列を実行するので性能が落ちる
　-UNION、MINUS、INTERSECTは全体範囲をアクセスする整列作業を実行するので一部範囲の処理が不可能。

■UNION
　-UNIONは２つのテーブル結合で、結合する２つのテーブルの重複しない値を返す。

--部署番号を検索するUNION例
SQL> SELECT deptno FROM emp
  2  UNION
  3  SELECT deptno FROM dept;

    DEPTNO
----------
        10
        20
        30
        40

■UNION ALL
　-UNIONと同じだけど、重複する値も返す。

--部署番号を検索するUNION ALL例
SQL> SELECT deptno FROM emp
  2  UNION ALL
  3  SELECT deptno FROM dept;

    DEPTNO
----------
        20
        30
        20
        30
        30
        10
        20
        10
        30
        20
        30
        20
        10
        10
        20
        30
        40

17行が選択されました。

■INTERSECT
　-INTERSECTは2行の集合で共通の行を返す

--部署番号を検索するINTERSECT例
SQL> SELECT deptno FROM emp
  2  INTERSECT
  3  SELECT deptno FROM dept;

    DEPTNO
----------
        10
        20
        30

■MINUS
　-MINUSは最初のSELECT文による結果行の中で、2つ目のSELECT文による結果行に存在しない行を返す。

--社員がいない部署を検索するMINUS例
SQL> SELECT deptno FROM dept
  2  MINUS
  3  SELECT deptno FROM emp;

    DEPTNO
----------
        40

11-06.サブクエリ（Subquery）【Scalar Subquery】

■Scalar Subqueryとは
　-SELECT句で使用するSubquery


・一般的なScalar Subqueryの例

--職業が'MANAGER'の社員名と部署名を出力する例
SQL> SELECT ename,
  2         (SELECT dname FROM dept d WHERE d.deptno = e.deptno) dept
  3  FROM emp e
  4  WHERE job = 'MANAGER';

ENAME      DEPT
---------- --------------
JONES      RESEARCH
BLAKE      SALES
CLARK      ACCOUNTING

・NULLを返すOuter JoinのScalar SubQuery例

--Scalar Subqueryは一致する値が無いとNULLを返すのでOuter Joinと同じ
--下記は部署別最高給与情報を検索する例
--40部署には社員がいないので最高給与が検索されない。
SQL> SELECT d.deptno, d.dname,
  2         (SELECT MAX(sal)
  3          FROM emp
  4          WHERE deptno = d.deptno) sal
  5  FROM dept d;

    DEPTNO DNAME                 SAL
---------- -------------- ----------
        10 ACCOUNTING           5000
        20 RESEARCH             3000
        30 SALES                2850
        40 OPERATIONS


--同じ結果のOuter Join例
SQL> SELECT d.deptno, d.dname, MAX(e.sal)
  2  FROM dept d, emp e
  3  WHERE d.deptno = e.deptno(+)
  4  GROUP BY d.deptno, d.dname
  5  ORDER BY d.deptno;

    DEPTNO DNAME          MAX(E.SAL)
---------- -------------- ----------
        10 ACCOUNTING           5000
        20 RESEARCH             3000
        30 SALES                2850
        40 OPERATIONS

11-05.サブクエリ（Subquery）【Inline View(FROM句 Subquery)】

■Inline Viewとは？
　-FROM句に入るSubquery
　-FROM句で求めるデータを検索し、仮想の結果を作ってJoinしたり、仮想の結果を再び検索する時に使用
　-Inline View内には別のInline Viewを使える

--20部署の平均給与より高くて、20部署所属ではない管理者を検索
SQL> SELECT b.empno, b.ename, b.job, b.sal, b.deptno
  2  FROM (SELECT empno
  3        FROM emp
  4        WHERE sal > (SELECT AVG(sal)
  5                     FROM emp
  6                     WHERE deptno = 20)) a, emp b
  7  WHERE a.empno = b.empno
  8  AND b.mgr is NOT NULL
  9  AND b.deptno != 20;

     EMPNO ENAME      JOB              SAL     DEPTNO
---------- ---------- --------- ---------- ----------
      7698 BLAKE      MANAGER         2850         30
      7782 CLARK      MANAGER         2450         10

SQL>

11-04.サブクエリ（Subquery）【Multiple-Column Subquery】

■Multiple-Column Subqueryとは？
　-結果が２つ以上のコラムを返すSubquery

■Pairwise Subquery
　-Subqueryが一回実行されて２つ以上のコラムを検索してMainqueryに渡す。

SQL> SELECT empno, sal, deptno
  2  FROM emp
  3  WHERE (sal, deptno) IN (SELECT sal, deptno
  4                          FROM emp
  5                          WHERE deptno = 30
  6                          AND comm is NOT NULL);

     EMPNO        SAL     DEPTNO
---------- ---------- ----------
      7499       1600         30
      7654       1250         30
      7844       1500         30

■Nonpairwise Subquery 　-WHEREで２つ以上のSubqueryが使われ結果をMainqueryに渡す。

SQL> SELECT empno, sal, deptno
  2  FROM emp
  3  WHERE sal IN (SELECT sal
  4                FROM emp
  5                WHERE deptno = 30
  6                AND comm is NOT NULL)
  7  AND deptno IN (SELECT deptno
  8                 FROM emp
  9                 WHERE deptno = 30
 10                 AND comm is NOT NULL);

     EMPNO        SAL     DEPTNO
---------- ---------- ----------
      7844       1500         30
      7654       1250         30
      7499       1600         30

■Null Values in a Subquery
　-サブクエリからNUKK値が返されるとMainqueryでは何も返さない。

11-03.サブクエリ（Subquery）【Multiple-Row Subquery】

■Multiple-Row Subqueryとは？
　-１つ以上の行を返すSubquery
　-論理演算子(IN, NOT IN, ANY, ALL, EXISTS)のみ使用可能

■IN演算子
　-IN演算子は１つのコラムに複数の'='条件を持つ場合使用
　-ORはINを含む。INで表現できるのはORでも表現できる。
　-しかし、ORでの表現をINで表現出来ない場合もある。（ORでLIKEなどを使用した場合）
　-INは必ず１つのコラムが比較されるので後々INDEX構成の参考に有利
　-結論的にはORよりINを使用するのが望ましい

--部署別に給与が一番高い社員の情報を出力
SQL> SELECT empno,ename,sal,deptno
  2  FROM emp
  3  WHERE sal IN (SELECT MAX(sal)
  4                FROM emp
  5                GROUP BY deptno);

     EMPNO ENAME             SAL     DEPTNO
---------- ---------- ---------- ----------
      7698 BLAKE            2850         30
      7788 SCOTT            3000         20
      7839 KING             5000         10
      7902 FORD             3000         20

■ANY演算子
　-ANY演算子はSubqueryの複数の結果からどれか１つだけでも条件に合えば行を返す

--SALEMANより給与が高い社員の名前と給与を出力
SQL> SELECT ename, sal
  2  FROM emp
  3  WHERE deptno != 20
  4  AND sal > ANY(SELECT sal
  5                FROM emp
  6                WHERE job = 'SALESMAN');

ENAME             SAL
---------- ----------
KING             5000
BLAKE            2850
CLARK            2450
ALLEN            1600
TURNER           1500
MILLER           1300

■ALL演算子
　-ALL演算子はSubquery結果すべてが条件に合えば行を返す

--すべてのSALESMANより給与が高い社員の社員名と給与を出力
SQL> SELECT ename, sal
  2  FROM emp
  3  WHERE deptno != 20
  4  AND sal > ALL(SELECT sal
  5                FROM emp
  6                WHERE job = 'SALESMAN');

ENAME             SAL
---------- ----------
CLARK            2450
BLAKE            2850
KING             5000

■EXISTS演算子
　-EXISTS演算子はSubqueryデータが存在するかをチェックしTRUE/FALSEを返す。
　-EXISTSには必ずメインクエリと繋がるJOIN条件が必要。
　-subqueryで結果行が見つかったら、inner queryを中断し、TRUEを返す。

--empテーブルで社員が所属した部署番号のみ検索する場合、
--検索しようとする対象はdeptテーブルだが、empテーブルとJoinし部署番号をチェックする必要がある。
--２つのテーブルの関係が1:Mなのでempテーブルをすべてアクセスし、DISTINCTで重複を除去しなければならない。
SQL> SELECT DISTINCT d.deptno, d.dname
  2  FROM dept d, emp e
  3  WHERE d.deptno = e.deptno;

    DEPTNO DNAME
---------- --------------
        10 ACCOUNTING
        20 RESEARCH
        30 SALES


--EXISTSを使用するSubqueryに変更！
--検索する対象のみFROMに入れてempテーブルはチェックだけするためにEXISTSに入れる
--これにより処理速度が早くなる。
SQL> SELECT d.deptno, d.dname
  2  FROM dept d
  3  WHERE EXISTS
  4      (SELECT 1
  5       FROM emp e
  6       WHERE e.deptno = d.deptno);

    DEPTNO DNAME
---------- --------------
        20 RESEARCH
        30 SALES
        10 ACCOUNTING

11-02.サブクエリ（Subquery）【Single-Row Subquery】

■Single-Row Subquery
　-SELECT文で１つの行（値）のみ返すQuery
　-比較演算子（=,>,>=,<,<=,<>,!=）だけ使用される。

--empnoが7369のjobを検索し
--jobが'CLERK'の社員の名前とjobを返す
SQL> SELECT ename, job
  2  FROM emp
  3  WHERE job = (SELECT job
  4               FROM emp
  5               WHERE empno = 7369);

ENAME      JOB
---------- ---------
SMITH      CLERK
ADAMS      CLERK
JAMES      CLERK
MILLER     CLERK

11-01.サブクエリ（Subquery）【サブクエリとは】

■Subqueryとは？
　-SubqueryはSQL文の内部に含まれる、別のSQL文のこと。
　-SELECT、UPDATE、DELETE、INSERTのようなDML文とCREATE TABLEやVIEWのWHERE、HAVINGでも使用される。
　-通常SubqueryはMain Query以前に1回実行される。
　-Subqueryはカッコで閉じる。
　-Subqueryは演算子の右側に位置する。

■サブクエリの区別
　-Sing-Rowサブクエリ：SELECT文から1行のみ検索する
　-Multiple-Rowサブクエリ：SELECT文から1行以上検索する

　-Multiple-Columnサブクエリ：SELECT文から1列以上検索する

　-FROM句のサブクエリ(INLINE VIEW)：FROM句に来るサブクエリでVIEWのような機能をする

　-Multiple-Rowサブクエリ：SELECT文から1行以上検索する

　-相関関係サブクエリ：Mainqueryのコラム１つがSubqueryの条件に利用される処理

10-04.集計関数（Aggregate function）【CUBE】

■CUBE operator
　-下記、ROLLUPの例にて部署別JOBに該当する給与と社員数を出力した。

SQL> SELECT b.dname, a.job, SUM(a.sal) sal,
  2         COUNT(a.empno) emp_count
  3  FROM emp a, dept b
  4  WHERE a.deptno = b.deptno
  5  GROUP BY ROLLUP(b.dname, a.job);

DNAME          JOB              SAL  EMP_COUNT
-------------- --------- ---------- ----------
SALES          CLERK            950          1
SALES          MANAGER         2850          1
SALES          SALESMAN        4350          3
SALES                          8150          5   ---> SALES部署の給与合計と社員数
RESEARCH       CLERK           1900          2
RESEARCH       ANALYST         6000          2
RESEARCH       MANAGER         2975          1
RESEARCH                      10875          5   ---> REASERCH部署の給与合計と社員数
ACCOUNTING     CLERK           1300          1
ACCOUNTING     MANAGER         2450          1
ACCOUNTING     PRESIDENT       5000          1
ACCOUNTING                     8750          3   ---> ACCOUNTING部署の給与合計と社員数
                              27775         13   ---> 全体給与合計と社員数

しかし、部署別で各JOBの給与と社員数また、各JOB別の給与合計と社員数を見るためには
2つのROLLUPを使用する必要がある。

SQL> SELECT b.dname, a.job, SUM(a.sal) sal, COUNT(a.empno) emp_count
  2  FROM emp a, dept b
  3  WHERE a.deptno = b.deptno
  4  GROUP BY ROLLUP(b.dname, a.job)
  5  UNION
  6  SELECT ' ', job, SUM(sal) sal, COUNT(empno) emp_count
  7  FROM emp
  8  GROUP BY ROLLUP(job);

DNAME          JOB              SAL  EMP_COUNT
-------------- --------- ---------- ----------
               ANALYST         6000          2　--->2回目のROLLUP結果
               CLERK           4150          4
               MANAGER         8275          3
               PRESIDENT       5000          1
               SALESMAN        4350          3
                              27775         13  
ACCOUNTING     CLERK           1300          1　--->ここから1回目のROLLUP結果
ACCOUNTING     MANAGER         2450          1
ACCOUNTING     PRESIDENT       5000          1
ACCOUNTING                     8750          3
RESEARCH       ANALYST         6000          2
RESEARCH       CLERK           1900          2
RESEARCH       MANAGER         2975          1
RESEARCH                      10875          5
SALES          CLERK            950          1
SALES          MANAGER         2850          1
SALES          SALESMAN        4350          3
SALES                          8150          5
                              27775         13

CUBEを使用して簡単なSQL文で上記結果が得られる

SQL> SELECT b.dname, a.job, SUM(a.sal) sal, COUNT(a.empno) emp_coun
  2  FROM emp a, dept b
  3  WHERE a.deptno = b.deptno
  4  GROUP BY CUBE(b.dname, a.job);

DNAME          JOB              SAL  EMP_COUNT
-------------- --------- ---------- ----------
                              27775         13  --->JOB別の給与総計と社員数
               CLERK           4150          4
               ANALYST         6000          2
               MANAGER         8275          3
               SALESMAN        4350          3
               PRESIDENT       5000          1
SALES                          8150          5
SALES          CLERK            950          1
SALES          MANAGER         2850          1
SALES          SALESMAN        4350          3
RESEARCH                      10875          5
RESEARCH       CLERK           1900          2
RESEARCH       ANALYST         6000          2
RESEARCH       MANAGER         2975          1
ACCOUNTING                     8750          3
ACCOUNTING     CLERK           1300          1
ACCOUNTING     MANAGER         2450          1
ACCOUNTING     PRESIDENT       5000          1

--CUBEはCross-Tabに対するSummaryを出力する
--ROLLUP結果のItem Total値とColumn Total値を出力できる

■GROUPING()関数
　-GROUPING関数はROLLUP、CUBE両方で使用できる
　-GROUPING関数は該当ROWがGROUP BYにより算出されたROWの場合0を返し、
　　ROLLUPかCUBEにより算出されたROWの場合は1を返す。
　
　※該当ROWが結果集合により算出されたデータなのか、ROLLUPやCUBEにより算出された
　　データかを見分けるための関数！

SQL> SELECT b.dname, a.job, SUM(a.sal), COUNt(a.empno) emp_count,
  2         GROUPING(b.dname) "D", GROUPING(a.job) "S"
  3  FROM emp a, dept b
  4  WHERE a.deptno = b.deptno
  5  GROUP BY CUBE(b.dname, a.job);

DNAME          JOB       SUM(A.SAL)  EMP_COUNT          D          S
-------------- --------- ---------- ---------- ---------- ----------
                              27775         13          1          1
               CLERK           4150          4          1          0
               ANALYST         6000          2          1          0
               MANAGER         8275          3          1          0
               SALESMAN        4350          3          1          0
               PRESIDENT       5000          1          1          0
SALES                          8150          5          0          1
SALES          CLERK            950          1          0          0
SALES          MANAGER         2850          1          0          0
SALES          SALESMAN        4350          3          0          0
RESEARCH                      10875          5          0          1
RESEARCH       CLERK           1900          2          0          0
RESEARCH       ANALYST         6000          2          0          0
RESEARCH       MANAGER         2975          1          0          0
ACCOUNTING                     8750          3          0          1
ACCOUNTING     CLERK           1300          1          0          0
ACCOUNTING     MANAGER         2450          1          0          0
ACCOUNTING     PRESIDENT       5000          1          0          0

10-03.集計関数（Aggregate function）【ROLLUP 】

■ROLLUP operator
　-ROLLUPはGROUP BYと一緒に使用され、GROUP BYによりグループ化された結果に対して
　　もっと詳細な情報を返す時に使用する。
　-SELECTにROLLUPを使用してSELECTされたデータとそのデータの合計を出力できる。

--GROUP BYでJOB別の給与合計を出力する例
SQL> SELECT job, SUM(sal)
  2  FROM emp
  3  GROUP BY job;

JOB         SUM(SAL)
--------- ----------
CLERK           4150
SALESMAN        4350
PRESIDENT       5000
MANAGER         8275
ANALYST         6000



--ROLLUPを使用してJOB別給与合計と総計を出力する例
SQL> SELECT job, SUM(sal)
  2  FROM emp
  3  GROUP BY ROLLUP(job);

JOB         SUM(SAL)
--------- ----------
ANALYST         6000
CLERK           4150
MANAGER         8275
PRESIDENT       5000
SALESMAN        4350
               27775  --給与合計の総計が追加された！

部署別に人数と給与合計を出力する例

--GROUP BYを使用した結果
SQL> SELECT b.dname, a.job, SUM(a.sal) sal,
  2         COUNT(a.empno) emp_count
  3  FROM emp a, dept b
  4  WHERE a.deptno = b.deptno
  5  GROUP BY b.dname, a.job;

DNAME          JOB              SAL  EMP_COUNT
-------------- --------- ---------- ----------
SALES          MANAGER         2850          1
SALES          CLERK            950          1
ACCOUNTING     MANAGER         2450          1
ACCOUNTING     PRESIDENT       5000          1
ACCOUNTING     CLERK           1300          1
SALES          SALESMAN        4350          3
RESEARCH       MANAGER         2975          1
RESEARCH       ANALYST         6000          2
RESEARCH       CLERK           1900          2

--部署別の人数と給与合計が見にくい
--部署に該当するJOB別給与と社員数を足して計算しなけれないけない。


--ROLLUPを使用すると簡単に結果がでる。
SQL> SELECT b.dname, a.job, SUM(a.sal) sal,
  2         COUNT(a.empno) emp_count
  3  FROM emp a, dept b
  4  WHERE a.deptno = b.deptno
  5  GROUP BY ROLLUP(b.dname, a.job);

DNAME          JOB              SAL  EMP_COUNT
-------------- --------- ---------- ----------
SALES          CLERK            950          1
SALES          MANAGER         2850          1
SALES          SALESMAN        4350          3
SALES                          8150          5   ---> SALES部署の給与合計と社員数
RESEARCH       CLERK           1900          2
RESEARCH       ANALYST         6000          2
RESEARCH       MANAGER         2975          1
RESEARCH                      10875          5   ---> REASERCH部署の給与合計と社員数
ACCOUNTING     CLERK           1300          1
ACCOUNTING     MANAGER         2450          1
ACCOUNTING     PRESIDENT       5000          1
ACCOUNTING                     8750          3   ---> ACCOUNTING部署の給与合計と社員数
                              27775         13   ---> 全体給与合計と社員数

10-02.集計関数（Aggregate function）【GROUP BY、 HAVING、GROUPING SETS 】

■GROUP BY
　-GROUP BYはデータを条件付きのグループとして設定できる
　-設定するグループのコラム名をSELECTとGROUP BYの後に追加すれば良い

--部署別の社員数検索
SQL> SELECT '2013年' year, deptno 部署番号, count(*) 社員数
  2  FROM emp
  3  GROUP BY deptno
  4  ORDER BY COUNT(*) DESC;

YEAR     部署番号     社員数
------ ---------- ----------
2013年         30          5
2013年         20          5
2013年         10          3

下記例は部署別にグループ化し、部署番号、人数、給与の平均、給与の合計を検索

SQL> SELECT deptno, COUNT(*), ROUND(AVG(sal)) "給与平均",
  2         ROUND(SUM(sal)) "給与合計"
  3  FROM emp
  4  GROUP BY deptno;

    DEPTNO   COUNT(*)   給与平均   給与合計
---------- ---------- ---------- ----------
        30          5       1630       8150
        20          5       2175      10875
        10          3       2917       8750

下記例は業務別にグループ化して業務、人数、平均給与、最高給与、最低給与、合計を検索

SQL> SELECT job "業務", COUNT(empno) "人数", ROUND(AVG(sal),1) "平均給与",
  2         MAX(sal) "最高給与", MIN(sal) "最低給与",
  3         SUM(sal) "給与合計"
  4  FROM emp
  5  GROUP BY job;

業務            人数   平均給与   最高給与   最低給与   給与合計
--------- ---------- ---------- ---------- ---------- ----------
CLERK              4     1037.5       1300        800       4150
SALESMAN           3       1450       1600       1250       4350
PRESIDENT          1       5000       5000       5000       5000
MANAGER            3     2758.3       2975       2450       8275
ANALYST            2       3000       3000       3000       6000

6行が選択されました。

　-GROUP BYは集計関数無しでも使える（DISTINCTと似た感じ…）
　-下記例はGROUP BYのグループ化のみの役割

SQL> SELECT deptno
  2  FROM emp
  3  GROUP BY deptno;

    DEPTNO
----------
        30
        20
        10

■DISTINCTとGROUP BY
　-DISTINCTは主にUNIQUE（重複解消）なコラム、レ-コードを検索する場合使用する。
　-GROUP BYはデータをグループ化して結果を検索する時に使用する。
　-DISTINCTでGROUP BYも処理できるクエリ―もある。
　-２つともOracle9iまではsortを利用しデータを作ったが、Oracle10gからはHashを利用して処理している。

--DISTINCTでのUNIQUE化処理
SQL> SELECT DISTINCT deptno FROM emp;

    DEPTNO
----------
        30
        20
        10


--GROUP BYでのUNIQUE化処理
SQL> SELECT deptno FROM emp GROUP BY deptno;

    DEPTNO
----------
        30
        20
        10

--結果は同じ！

しかし、GROUP BYとDISTINCTは各自特有の機能がある

集計関数を使用して特定グループに区分する時はGROUP BY、特定グループに区分しないで重複したデータを除去する場合はDISTINCTを使用するのが望ましい。

--下記の例はDISTINCTを使用するのが効率的
SQL> SELECT COUNT(DISTINCT d.deptno) "重複除去数",
  2         COUNT(d.deptno) "全体数"
  3  FROM emp e, dept d
  4  WHERE e.deptno = d.deptno;

重複除去数     全体数
---------- ----------
         3         13


--集計関数が必要な場合はGROUP BYを使用する
SQL> SELECT deptno, MIN(sal)
  2  FROM emp
  3  GROUP BY deptno;

    DEPTNO   MIN(SAL)
---------- ----------
        30        950
        20        800
        10       1300

■HAVING
　-WHEREでは集計関数を使用出来ない
　-HAVINGは集計関数を持って条件比較をする時に使用する
　-HAVINGはGROUP BYと一緒に使用される

下記例は社員数が3名を超える部署と社員数を検索する例

SQL> SELECT b.dname, COUNt(a.empno) "社員数"
  2  FROM emp a, dept b
  3  WHERE a.deptno = b.deptno
  4  GROUP BY dname
  5  HAVING COUNT(a.empno) > 3;

DNAME              社員数
-------------- ----------
RESEARCH                5
SALES                   5

下記例は給与合計が5000を超えるJOBに関してJOBと給与の合計を検索する例、
但しSALESは除外して給与合計の降順で整列した。

SQL> SELECT job, SUM(sal) "給与合計"
  2  FROM emp
  3  WHERE job != 'SALES'　　　　　　--SALESは除外
  4  GROUP BY job　　　　　　　　　　--業務別にGROUP BY
  5  HAVING SUM(sal) > 5000　　　　　--給与合計が5000を超える条件
  6  ORDER BY SUM(sal) DESC;　　　　 --給与合計の降順で整列

JOB         給与合計
--------- ----------
MANAGER         8275
ANALYST         6000

■GROUPING SETS
　-GROUPING SETS関数はGROUP BYの拡張した形でGROUP BYに複数のグループ化条件を設定できる。
　-GROUPING SETS関数の結果は各GROUP条件を実施した結果をUNION ALLした結果と同じ
　-GROUPING SETS関数でUNION ALL等を使用して複雑に作成されたSQL文をシンプルにまとめられる。

下記はGROUPING SETS関数とGROUP BY, UNION ALLを使用して同じ結果を出力する例

--GROUPING SETS使用
SQL> SELECT deptno, job, SUM(sal)
  2  FROM emp
  3  GROUP BY GROUPING SETS(deptno, job);

    DEPTNO JOB         SUM(SAL)
---------- --------- ----------
        30                 8150
        20                10875
        10                 8750
           CLERK           4150
           SALESMAN        4350
           PRESIDENT       5000
           MANAGER         8275
           ANALYST         6000


--GROUP BYとUNION ALL使用
SQL> SELECT NULL deptno, job, SUM(sal)
  2  FROM emp
  3  GROUP BY job
  4  UNION ALL
  5  SELECT deptno, NULL job ,SUM(sal)
  6  FROM emp
  7  GROUP BY deptno;

    DEPTNO JOB         SUM(SAL)
---------- --------- ----------
           CLERK           4150
           SALESMAN        4350
           PRESIDENT       5000
           MANAGER         8275
           ANALYST         6000
        30                 8150
        20                10875
        10                 8750

10-01.集計関数（Aggregate function）【集計関数とは？ 】

■集計関数とは？
　-複数行またはテーブル全体行から１つの結果値を返す関数
　-GROUP BYを使用してグループ毎、１つの結果に纏められる。
　-HAVINGを使用して集計関数を利用した条件比較ができる。
　-MIN、MAX関数はすべてのタイプに利用できる。
　-一般的に使われている集計関数はAVG、COUNT、MAX、MIN、SUM等がある。

■COUNT：検索された行の数を返す。

SQL> SELECT COUNT(deptno) FROM dept;

COUNT(DEPTNO)
-------------
            4

■MAX：コラム値の中で最大値を返す

--salコラム値の中で最大値を返す
SQL> SELECT MAX(sal) salary FROM emp;

    SALARY
----------
      5000

■MIN：コラム値の中で最小値を返す

--salコラム値の中で最小値を返す
SQL> SELECT MIN(sal) salary FROM emp;

    SALARY
----------
       800

■AVG：平均値を返す

--30部署の社員平均給与を小数点1桁以下で四捨五入
SQL> SELECT ROUND(AVG(sal),1) salary
  2  FROM emp
  3  WHERE deptno = 30;

    SALARY
----------
      1630

■SUM：検索されたコラムの合計を返す。

--30部署の社員給与の合計
SQL> SELECT SUM(sal) salsry
  2  FROM emp
  3  WHERE deptno = 30;

    SALSRY
----------
      8150

■STDDEV：標準偏差を返す。

--30部署社員の給与標準偏差を返す
SQL> SELECT ROUND(STDDEV(sal), 3) salary
  2  FROM emp
  3  WHERE deptno = 30;

    SALARY
----------
   726.808

■集計関数の例

--部署別社員数、最大給与、最小給与、給与合計、平均給与を検索する
QL> SELECT deptno 部署番号, COUNT(*) 社員数,
 2         MAX(sal) 最大給与, MIN(sal) 最小給与,
 3         SUM(sal) 給与合計, ROUND(AVG(sal)) 平均給与
 4  FROM emp
 5  GROUP BY deptno
 6  ORDER BY SUM(sal) DESC;

 部署番号     社員数   最大給与   最小給与   給与合計   平均給与
--------- ---------- ---------- ---------- ---------- ----------
       20          5       3000        800      10875       2175
       10          3       5000       1300       8750       2917
       30          5       2850        950       8150       1630

09-07.Sing-Row Functions【NVL, NVL2, NULLIF, COALESCE 】

■NVL
　-NVL関数はNULL値を他の値に替える時に使用、全てのデータタイプに適用可能

--マネージャーがない値を0に替えて出力
SQL> SELECT empno, NVL(mgr, 0) mgr
  2  FROM emp
  3  WHERE deptno = 10;

     EMPNO        MGR
---------- ----------
      7782       7839
      7839          0
      7934       7782

■NVL2
　-NVL2はNVLとDECODEの概念を合わせた関数
　-NVL2(expr, expr1, expr2)
　-expr値がNULLじゃない場合、expr1を返し、NULLの場合はexpr2を返す

--マネージャーがいる場合は1を無ければ2を出力する例
SQL> SELECT empno, NVL2(mgr, 1, 0) mgr
  2  FROM emp
  3  WHERE deptno = 10;

     EMPNO        MGR
---------- ----------
      7782          1
      7839          0
      7934          1

■NULLIF
　-NULLIF（expr1, expr2）
　-expr1とexpr2が同じならNULLを返し、違うならexpr1を返す
　-CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END


■COALESCE
　-COALESCE(expr1, expr2, expr3, .....)
　-expr1がNULLじゃないならexpr1を返し、NULLならCOALESCE(expr2, expr3, .....)値を返す
　-NVL関数と似ている

SQL> SELECT COALESCE(comm, 1) ,comm FROM emp;

COALESCE(COMM,1)       COMM
---------------- ----------
               1
             300        300
               1
            1400       1400
               1
               1
               1
               1
               0          0
               1
               1
               1
               1
               1
               1

15行が選択されました。

09-06.Sing-Row Functions【DECODEとCASE】

■DECODE
　-DECODE関数は条件を加えてデータを違う値とかコラムに出力できる。
　-DECODE(VALUE, IF1, THEN1, IF2, THEN2....)で使用
　-VALUE値がIF1の場合、THEN1を返し、VALUE値がIF2の場合、THEN2値を返す。
　-DECODE関数内にDECODE関数使用可能

--部署番号が10ならACCOUNTING、20ならRESEARCH、30ならSALES、その他はOPERATIONSを出力する
SQL> SELECT deptno, DECODE(deptno, 10, 'ACCOUNTING' ,
  2                                20, 'RESEARCH' ,
  3                                30, 'SALES', 'OPERATIONS') name
  4  FROM dept;

    DEPTNO NAME
---------- ----------
        10 ACCOUNTING
        20 RESEARCH
        30 SALES
        40 OPERATIONS

--10部署は給与の合計を、20部署は最大値を、30部署は最小値を出力する
SQL> SELECT deptno, DECODE(deptno, 10, SUM(sal),
  2                                20, MAX(sal),
  3                                30, MIN(sal)) sal
  4  FROM emp
  5  GROUP BY deptno;

    DEPTNO        SAL
---------- ----------
        30        950
        20       3000
        10       8750


--部署別の給与合計を出力する
SQL> SELECT deptno, NVL(SUM(DECODE(deptno, 10, sal)), 0) deptno10,
  2                 NVL(SUM(DECODE(deptno, 20, sal)), 0) deptno20,
  3                 NVL(SUM(DECODE(deptno, 30, sal)), 0) deptno30,
  4                 NVL(SUM(DECODE(deptno, 40, sal)), 0) deptno40
  5  FROM emp
  6  GROUP BY deptno;

    DEPTNO   DEPTNO10   DEPTNO20   DEPTNO30   DEPTNO40
---------- ---------- ---------- ---------- ----------
        30          0          0       8150          0
        20          0      10875          0          0
        10       8750          0          0          0

一般的に集計関数使用時は給与合計が行で検索されるが、
DECODEとMAX関数を使用すると列で値を表示できる

--部署別の給与合計を行で出力
SQL> SELECT d.deptno, NVL(SUM(e.sal), 0) sal
  2  FROM emp e, dept d
  3  WHERE e.deptno(+) = d.deptno
  4  GROUP BY d.deptno
  5  ORDER BY d.deptno;

    DEPTNO        SAL
---------- ----------
        10       8750
        20      10875
        30       8150
        40          0


--部署別の給与合計を列で出力
SQL> SELECT MAX(NVL(SUM(DECODE(deptno, 10, sal)), 0)) deptno10,
  2         MAX(NVL(SUM(DECODE(deptno, 20, sal)), 0)) deptno20,
  3         MAX(NVL(SUM(DECODE(deptno, 20, sal)), 0)) deptno30,
  4         MAX(NVL(SUM(DECODE(deptno, 20, sal)), 0)) deptno40
  5  FROM emp
  6  GROUP BY deptno;

  DEPTNO10   DEPTNO20   DEPTNO30   DEPTNO40
---------- ---------- ---------- ----------
      8750      10875      10875      10875

■CASE
　-CASE関数はDECODE関数でカバー出来ない比較演算を解決できる関数
　-DECODE関数での比較演算はGREATEST、LEAST等の関数を使用するが、
　　CASE関数では条件演算子をすべて使用できる。
　-CASE関数はIF..THEN..ELSEと似ている。WHEN句の次に色んな条件を入れられる。

--上記DECEODE例をCASE関数で検索した例
SQL> SELECT deptno,
  2  CASE deptno
  3    WHEN 10 THEN 'ACCOUNTING'
  4    WHEN 20 THEN 'RESEARCH'
  5    WHEN 30 THEN 'SALES'
  6    ELSE 'OPERATIONS'
  7  END as "Dept Name"
  8  FROM dept;

    DEPTNO Dept Name
---------- ----------
        10 ACCOUNTING
        20 RESEARCH
        30 SALES
        40 OPERATIONS

WHEN句の次に演算子が来る例

--給与別に違うUP率を適用
SQL> SELECT ename,
  2     CASE
  3             WHEN sal < 1000 THEN sal+(sal*0.8)
  4             WHEN sal BETWEEN 1000 AND 2000 THEN sal+(sal*0.5)
  5             WHEN sal BETWEEN 2001 AND 3000 THEN sal+(sal*0.3)
  6             ELSE sal+(sal*0.1)
  7     END sal
  8  FROM emp;

ENAME             SAL
---------- ----------
SMITH            1440
ALLEN            2400
JONES          3867.5
MARTIN           1875
BLAKE            3705
CLARK            3185
SCOTT            3900
KING             5500
TURNER           2250
ADAMS            1650
JAMES            1710
........

09-05.Sing-Row Functions【その他関数】

■DUMP
　-DUMPはバイトサイズと該当データタイプのコードを返す

--16の代わりに8を入れると8進数に、10を入れると10進数に変換される
--Lenはenameの該当byte数
SQL> SELECT ename, DUMP(ename,16) "16進数"
  2  FROM emp
  3  WHERE ename = 'ALLEN';

ENAME
----------
16進数
--------------------------------------------------------------------------------
ALLEN
Typ=1 Len=5: 41,4c,4c,45,4e

※※　DATATYPE の定義　※※

1 VARCHAR2(size) [BYTE | CHAR]
1 NVARCHAR2(size)
2 NUMBER(p,s)
8 LONG
12 DATE
23 RAW(size)
24 LONG RAW
69 ROWID
96 CHAR(size)[BYTE | CHAR]
96 NCHAR(size)
112 CLOB
112 NCLOB
113 BLOB
114 BFILE
180 TIMESTAMP (fractional_seconds_precision)
181 TIMESTAMP (fractional_seconds_precision) WITH TIME ZONE
182 INTERVAL YEAR (year_precision) TO MONTH
183 INTERVAL DAY (day_precision) TO SECOND (fractional_seconds_precision)
208 UROWID [(size)]
231 TIMESTAMP (fractional_seconds_precision) WITH LOCAL TIME ZONE

■GREATEST
　-検索した値の中、最大値を返す

SQL> SELECT GREATEST(10, 100, 5, -7) FROM DUAL;

GREATEST(10,100,5,-7)
---------------------
                  100

■LEAST
　-検索した値の中、最小値を返す

SQL> SELECT LEAST(10, 100, 5,-7) FROM DUAL;

LEAST(10,100,5,-7)
------------------
                -7

■UID, USER
　-UID現在ユーザーのID番号を返す
　-USERはユーザーをVARCHAR2で返す

SQL> SELECt USER, UID FROM DUAL;

USER                                  UID
------------------------------ ----------
SCOTT                                  84

■USERENV
　-セッション環境情報を返す

　・ENTRYID：使用可能なAuditing entry Identifierを返す
　・LABEL：現在セッションのLabelを返す
　・LANGUAGE：現在セッションで使用中の言語とテリトリー値を返す
　・SESSIONID：Auditing Session IDを返す
　・TERMINAL：現在セッションターミナルのOS IDを返す

SQL> SELECT USERENV('ENTRYID') FROM DUAL;

USERENV('ENTRYID')
------------------
                 1

SQL> SELECT USERENV('LANGUAGE') FROM DUAL;

USERENV('LANGUAGE')
----------------------------------------------------
JAPANESE_JAPAN.JA16SJISTILDE

SQL> SELECT USERENV('SESSIONID') FROM DUAL;

USERENV('SESSIONID')
--------------------
              210381

■VSIZE
　-該当文字のBYTE数を返す。該当文字がNULLならNULLを返す

SQL> SELECT VSIZE(ename), ename
  2  FROM emp
  3  WHERE deptno = 30;

VSIZE(ENAME) ENAME
------------ ----------
           5 ALLEN
           6 MARTIN
           5 BLAKE
           6 TURNER
           5 JAMES

09-04.Sing-Row Functions【変換関数(Conversion Functions)】

■TO_CHAR
　-TO_CHAR関数はDATEタイプ、NUMBERタイプを文字タイプに変換する関数。
　-TO_CHAR(number), TO_CHAR(number, format), TO_CHAR(date, format)...

・Number Formatの変換例
　-NUMBERタイプのデータを特定の文字タイプに変換できる

--「,」例
SQL> SELECT TO_CHAR(12345678, '999,999,999') comma FROM DUAL;

COMMA
------------
  12,345,678


--小数点例
SQL> SELECT TO_CHAR(123.45678, '999,999,999.99') period FROM DUAL;

PERIOD
---------------
         123.46


--「$」例
SQL> SELECT TO_CHAR(12345678, '$999,999,999') dollar FROM DUAL;

DOLLAR
-------------
  $12,345,678


--Local通貨単位例(日本の場合、\に自動変換される)
SQL> SELECT TO_CHAR(12345678, 'L999,999,999') local FROM DUAL;

LOCAL
----------------------
           \12,345,678


--左に0を挿入
SQL> SELECT TO_CHAR(123, '09999') zero FROM DUAL;

ZERO
------
 00123


--16進数に変換
SQL> SELECT TO_CHAR(123, 'XXXX') hexadecimal FROM DUAL;

HEXAD
-----
   7B

・Date Formatの変換例

--年、月、日、時、分、秒
SQL> SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') "sysdate" FROM DUAL;

sysdate
-------------------
2013-08-26 16:04:39


--365日中、何日目か検索
SQL> SELECT TO_CHAR(SYSDATE, 'DDD') "Day do year" FROM DUAL;

Day
---
238


--53週中、何週目か検索
SQL> SELECT TO_CHAR(SYSDATE, 'IW') "Week of year" FROM DUAL;

We
--
35


--該当月の名前検索
SQL> SELECT TO_CHAR(SYSDATE, 'MONTH') "Name of month" FROM DUAL;

Name o
------
8月

・Date Format Elements

※WWとIWは1年の何週目（1～53）かを検索するフォーマットだが、
　”WW”は1日から7日までを1週目とする。
　”IW”は実際のカレンダーに合わせて計算される。

■TO_DATE
　-TO_DATE関数はCHAR,VARCHAR2タイプをDATEタイプに変換する。
　-TO_DATE（char, format）で使用。
　-上記Date Format Elementsで”W”、”WW”フォーマット以外はTO_DATE関数のフォーマットで使用可能。

SQL> SELECT TO_DATE('2013-08-26', 'YYYY-MM-DD') FROM DUAL;

TO_DATE(
--------
13-08-26

■TO_NUMBER
　-CHAR、VARCHAR2データタイプをNUMBERタイプに変換する。
　-TO_NUMBER(char)で使用。

SQL> SELECT TO_NUMBER('12310616') FROM DUAL;

TO_NUMBER('12310616')
---------------------
             12310616

09-03.Sing-Row Functions【日付関数(Datetime Functions)】

■Datetime Functions（日付関数）
　-日付関数はNUMBERもしくはDATE型の値をRETURNする。

■SYSDATE
　-SYSDATE関数を使用すると現在日時（システム基準）を得られる
　-最小単位：1秒

SQL> SELECT TO_CHAR(SYSDATE, 'RRRR-MM-DD HH24:MI:SS') "現在時刻" FROM DUAL;

現在時刻
-------------------
2013-08-26 12:33:00

SQL> SELECT TO_CHAR(SYSDATE-1, 'RRRR-MM-DD HH24:MI:SS') "1日前現時刻" FROM DUAL;

1日前現時刻
-------------------
2013-08-25 12:34:08

SQL> SELECT TO_CHAR(SYSDATE-1/24, 'RRRR-MM-DD HH24:MI:SS') "1時間前" FROM DUAL;

1時間前
-------------------
2013-08-26 11:35:41

SQL> SELECT TO_CHAR(SYSDATE-1/24/60, 'RRRR-MM-DD HH24:MI:SS') "1分前" FROM DUAL;

1分前
-------------------
2013-08-26 12:36:04

SQL> SELECT TO_CHAR(SYSDATE-1/24/60/10, 'RRRR-MM-DD HH24:MI:SS') "6秒前" FROM DUAL;

6秒前
-------------------
2013-08-26 12:38:22

SQL>
SQL> SELECT TO_CHAR(SYSDATE-(5/24 + 30/24/60 + 10/24/60/60),'RRRR-MM-DD HH24:MI:SS') "5時間30分10秒前" FROM DUAL;

5時間30分10秒前
-------------------
2013-08-26 07:09:55

SQL>

■SYSTIMESTAMP
　-SYSTIMESTAMP関数を使用すると現在の日時を得られる
　-最小単位=10億分の1秒

SQL> SELECT TO_CHAR(SYSTIMESTAMP, 'RRRR-MM-DD HH24:MI:SS.FF3') "現在時刻" FROM DUAL;

現在時刻
-----------------------------
2013-08-26 13:47:24.033

SQL> SELECT TO_CHAR(SYSTIMESTAMP, 'RRRR-MM-DD HH24:MI:SS.FF9') "現在時刻" FROM DUAL;

現在時刻
-----------------------------
2013-08-26 13:47:42.649000000

SQL> SELECT TO_CHAR(SYSTIMESTAMP -1/24, 'RRRR-MM-DD HH24:MI:SS') "1時間前" FROM DUAL;

1時間前
-------------------
2013-08-26 12:49:12

SQL> SELECT TO_CHAR(SYSTIMESTAMP -1/24/60, 'RRRR-MM-DD HH24:MI:SS') "1分前" FROM DUAL;

1分前
-------------------
2013-08-26 13:51:05

■ADD_MONTHS(a, b)
　-ADD_MONTHS関数はaの日付にbの月を足した値を返す。

--SYSDATE使用例
SQL> SELECT TO_CHAR(ADD_MONTHS(SYSDATE,3), 'RRRR-MM-DD') "date" FROM DUAL;

date
----------
2013-11-26


--TIMESTAMP使用例
SQL> SELECT TO_CHAR(ADD_MONTHS(SYSTIMESTAMP,3), 'RRRR-MM-DD') "date" FROM DUAL;

date
----------
2013-11-26

■MONTHS_BETWEEN(a1, a2)
　-a1とa2の間の月の数をNUMBERタイプで返す

SQL> SELECT MONTHS_BETWEEN(TO_DATE('2013-08-25', 'RRRR-MM-DD') ,
  2                        TO_DATE('2013-07-01', 'RRRR-MM-DD')) "Month"
  3  FROM DUAL;

     Month
----------
1.77419355

■LAST_DAY(d)
　-その月の最終日を返す

SQL> SELECT SYSDATE today, LAST_DAY(SYSDATE) lastday FROM DUAL;

TODAY    LASTDAY
-------- --------
13-08-26 13-08-31

■NEXT_DAY(d, c1)
　-入力しは曜日に対して今度該当の日付を返す
　-曜日は１（日）～７（土）を入力

SQL> SELECT SYSDATE today, LAST_DAY(SYSDATE) lastday FROM DUAL;

TODAY    LASTDAY
-------- --------
13-08-26 13-08-31




SQL> SELECT NEXT_DAY(TO_DATE('20130826', 'RRRRMMDD'), 3) "8月最終火曜日" FROM DUAL;

8月最終
--------
13-08-27

■ROUND(d[,F])
　-Fに指定された単位に四捨五入する。
　-Fが年度なら年度で四捨五入、Fが省略されると日が一番近い日に四捨五入される。

SQL> SELECT TO_CHAR(ROUND(TO_DATE('2013-08-26 15:00:01' ,
  2                               'RRRR-MM-DD HH24:MI:SS'), 'YEAR'),
  3                 'RRRR-MM-DD HH24:MI:SS')
  4  FROM DUAL;

TO_CHAR(ROUND(TO_DA
-------------------
2014-01-01 00:00:00

SQL> SELECT TO_CHAR(ROUND(TO_DATE('2013-08-26 15:00:01',
  2                               'RRRR-MM-DD HH24:MI:SS'), 'MONTH'),
  3                 'RRRR-MM-DD HH24:MI:SS')
  4  FROM DUAL;

TO_CHAR(ROUND(TO_DA
-------------------
2013-09-01 00:00:00

SQL> SELECT TO_CHAR(ROUND(TO_DATE('2013-08-26 15:00:01',
  2                               'RRRR-MM-DD HH24:MI:SS'), 'DD'),
  3                 'RRRR-MM-DD HH24:MI:SS')
  4  FROM DUAL;

TO_CHAR(ROUND(TO_DA
-------------------
2013-08-27 00:00:00

SQL> SELECT TO_CHAR(ROUND(TO_DATE('2013-08-26 15:00:01',
  2                               'RRRR-MM-DD HH24:MI:SS')),
  3                 'RRRR-MM-DD HH24:MI:SS')
  4  FROM DUAL;

TO_CHAR(ROUND(TO_DA
-------------------
2013-08-27 00:00:00

■TRUNC(d[,F])
　-Fに指定した単位で切り捨てを行なう

SQL> SELECT TO_CHAR(TRUNC(TO_DATE('2013-08-26 15:00:01' ,
  2                               'RRRR-MM-DD HH24:MI:SS'), 'YEAR'),
  3                 'RRRR-MM-DD HH24:MI:SS')
  4  FROM DUAL;

TO_CHAR(TRUNC(TO_DA
-------------------
2013-01-01 00:00:00

SQL> SELECT TO_CHAR(TRUNC(TO_DATE('2013-08-26 15:00:01' ,
  2                               'RRRR-MM-DD HH24:MI:SS'), 'MONTH'),
  3                 'RRRR-MM-DD HH24:MI:SS')
  4  FROM DUAL;

TO_CHAR(TRUNC(TO_DA
-------------------
2013-08-01 00:00:00

SQL> SELECT TO_CHAR(TRUNC(TO_DATE('2013-08-26 15:00:01' ,
  2                               'RRRR-MM-DD HH24:MI:SS'), 'DD'),
  3                 'RRRR-MM-DD HH24:MI:SS')
  4  FROM DUAL;

TO_CHAR(TRUNC(TO_DA
-------------------
2013-08-26 00:00:00

SQL> SELECT TO_CHAR(TRUNC(TO_DATE('2013-08-26 15:00:01' ,
  2                               'RRRR-MM-DD HH24:MI:SS')),
  3                 'RRRR-MM-DD HH24:MI:SS')
  4  FROM DUAL;

TO_CHAR(TRUNC(TO_DA
-------------------
2013-08-26 00:00:00

09-02.Sing-Row Functions【文字列関数(Character Functions)】

■文字列関数
　-CHAR, VARCHAR2タイプの引数をVARCHAR2タイプの値を返す関数

・CONCAT(char1, char2)：Concatenationの略称で文字列を結合した結果を返す。「||」演算子と同じ役割。

--CONCAT例
SQL> SELECT CONCAT('mission-oraclemaster.','blogspot')||'.jp' name FROM DUAL;

NAME
--------------------------------
mission-oraclemaster.blogspot.jp

SQL>

・INITCAP(char), LOWER(char), UPPER(char)
　-INITCAP：文字列の最初も文字を大文字に変換
　-LOWER：文字列を小文字に変換
　-UPPER：文字列を大文字に変換

--INITCAP, LOWER, UPPER例
SQL> SELECT INITCAP('oracle-master') name FROM DUAL
  2  UNION ALL
  3  SELECT UPPER('oracle-master') name FROM DUAL
  4  UNION ALL
  5  SELECT LOWER('oracle-master') name FROM DUAL;

NAME
-------------
Oracle-Master
ORACLE-MASTER
oracle-master

・LPAD（char1, n[, char2]）. RPAD(char1, n[, char2])
　-LPAD：文字列の左に指定文字を埋める
　-RPAD：文字列の右に指定文字を埋める
　※nは返す文字列の長さを表し、char1の文字列がnより大きい場合、char1をn個の文字列を返す

--LPAD,RPAD例
SQL> SELECT LPAD('oracle-master', 15, '*') name FROM DUAL
  2  UNION ALL
  3  SELECT RPAD('oracle-master', 15, '*') name FROM DUAL;

NAME
---------------
**oracle-master
oracle-master**

・SUBSTR（char, m, [n]）, SUBSTRB(char, m, [n])
　-SUBSTR関数はm番目から長さがn個の文字列を返す
　-mが負数の場合後ろからm番目文字からn個の文字を返す
　-SUBSTRB関数でBはByte単位で処理するを意味する

--3番目の文字列から返す
SQL> SELECT SUBSTR('oracle-master', 3) name FROM DUAL;

NAME
-----------
acle-master


--3番目から4つの文字列を返す
SQL> SELECT SUBSTR('oracle-master', 3, 4) name FROM DUAL;

NAME
----
acle


--後から3番目から２つの文字列を返す
SQL> SELECT SUBSTR('oracle-master', -3, 2) name FROM DUAL;

NA
--
te


--DBがUTF-8の場合
SQL> SELECT SUBSTRB('オラクルマスター' , 1) name FROM DUAL
  2  UNION ALL
  3  SELECT SUBSTRB('オラクルマスター' , 3) name FROM DUAL;

NAME
----------------
オラクルマスター
ラクルマスター

・LENGTH（char）, LENGTHB(char)：文字列の長さを返す

--文字列のLENGTHを検索する例
--DBがUTF-8の場合
SQL> SELECT LENGTH('オラクルマスター') len FROM DUAL
  2  UNION ALL
  3  SELECT LENGTHB('オラクルマスター') len FROM DUAL;

       LEN
----------
         8
        16

・REPLACE(char1, str1,str2)：文字列の特定文字を別文字に変換する

--REPLACE例
SQL> SELECT REPLACE('oracle-master','oracle','db') name FROM DUAL;

NAME
---------
db-master

--大文字、小文字を区別する
SQL> SELECT REPLACE('Oracle-Master', 'oracle', 'DB') name FROM DUAL
  2  UNION ALL
  3  SELECT REPLACE('Oracle-master', 'Oracle', 'DB') name FROM DUAL;

NAME
-------------
Oracle-Master
DB-master

・INSTR(char1, str1, m, n)
　-文字列が含まれているかを調査して文字列の位置を返す
　-指定の文字列が無ければ0が返される
　-char1 : 指定文字、str1 : 検索文字、m : 開始位置、n : 検索順位

--指定した文字「OK」が無いので0を返す
SQL> SELECT INSTR('CORPORATE FLOOR' , 'OK') idx FROM DUAL;

       IDX
----------
         0

--ORがある位置2を返す。左から比較する。
SQL> SELECT INSTR('CORPORATE FLOOR' , 'OR') idx FROM DUAL;

       IDX
----------
         2

--左から3番目からORを検索し位置を返す
SQL> SELECT INSTR('CORPORATE FLOOR', 'OR' , 3) idx FROM DUAL;

       IDX
----------
         5

--左から3番目から比較し、ORが2回目に検索された位置を返す
SQL> SELECT INSTR('CORPORATE FLOOR', 'OR', 3, 2) idx FROM DUAL;

       IDX
----------
        14

・TRIM（char1[,char2]）, LTRIM(char1[,char2]), RTRIM(chaar1[,char2])
　-TRIM：特定文字を削除する。削除する文字を入力しないと空白が削除される
　-LTRIM：左から文字を削除する。削除する文字を入力しないと左の空白が削除される
　-RTRIM：右から文字を削除する。削除する文字を入力しないと右の空白が削除される

--oと空白を削除するTRIM例
SQL> SELECT TRIM('o' FROM 'oracle-master') name FROM DUAL;

NAME
------------
racle-master

SQL> SELECT TRIM('o' FROM 'oracle-master') name FROM DUAL
  2  UNION ALL
  3  SELECT TRIM('  oracle-master  ') name FROM DUAL;

NAME
-------------
racle-master
oracle-master


--左側の文字列TRIM例
--空白の場合、左の空白だけ削除される
SQL> SELECT LTRIM('orcle-master' , 'oracle') name FROM DUAL
  2  UNION ALL
  3  SELECT REPLACE(LTRIM(' oracle-master '), ' ', '*') name FROM DUAL;

NAME
--------------
-master
oracle-master*


--右側の文字列TRIM例
--空白の場合、右の空白だけ削除される
SQL> SELECT RTRIM('oracle-master', 'master') name FROM DUAL
  2  UNION ALL
  3  SELECT REPLACE(RTRIM(' oracle-master '), ' ', '*') name FROM DUAL;

NAME
--------------
oracle-
*oracle-master

09-01.Sing-Row Functions【数値関数(Numeric Functions)】

■Numberic Functions（数値関数）
　-数値を引数で受けNUMERタイプの値を返す関数

・ABS(n)：絶対値を返す

SQL> SELECT ABS (-1.234) "ABS" FROM DUAL;

       ABS
----------
     1.234

SQL>

・CEIL(n)：引数に対して、それ以上で、最小の整数値を返す

--CEIL例（+）
SQL> SELECT CEIL(10.1234) "CEIL" FROM DUAL;

      CEIL
----------
        11

--CEIL例（-）
SQL> SELECT CEIL(-10.1234) "CEIL" FROM DUAL;

      CEIL
----------
       -10

SQL>

・FLOOR(n):引数に対して、それ以下で、最小の整数値を返す。

--FLOOR例(+)
SQL> SELECT FLOOR(10.1234) "FLOOR" FROM DUAL;

     FLOOR
----------
        10


--FLOOR例(-)
SQL> SELECT FLOOR(-10.1234) "FLOOR" FROM DUAL;

     FLOOR
----------
       -11

・MOD(m,n)：mをnで割った余りを返す。nが0の場合、mを返す

SQL> SELECT MOD(9,4) "MOD" FROM DUAL;

       MOD
----------
         1

SQL>

・ROUND(n,[m]):四捨五入結果を返す。mは小数点以下の桁数

--ROUND例
SQL> SELECT ROUND(192.153, 1) "ROUND" FROM DUAL;

     ROUND
----------
     192.2

SQL> SELECT ROUND(192.153, -1) "ROUND" FROM DUAL;

     ROUND
----------
       190

SQL>

・TRUNC(n,m)：切り捨て結果を返す。mは小数点以下の桁数

--TRUNC例
SQL> SELECT TRUNC(7.5597, 2) "TRUNC" FROM DUAL;

     TRUNC
----------
      7.55

SQL> SELECT TRUNC(789.5597, -2) "TRUNC" FROM DUAL;

     TRUNC
----------
       700

SQL>

08-06.データー操作語（DML）【トランザクション(commit, rollback, savepoint)】

■トランザクション（TRANSACTION）とは？
　-トランザクションはデーター処理の１単位
　-オラクルサーバで発生するSQL文が１つの論理的単位で成功または失敗する一連の単位がトランザクション
　-オラクルサーバはトランザクションを根拠にデーターの一貫性を保証する
　-トランザクションはデーターを変更するDMLで構成される（COMMIT, ROLLBACK, SAVEPOINT）

・TARNSACTIONの開始
　-実行可能なSQL文が最初に実行される時

・TRANSACTIONの終了
　-COMMITまたはROLLBACK
　-DDLまたはDCL文の実行（自動COMMIT）
　-障害またはシステム衝突（CRASH）
　-deadlock発生
　-ユーザーの正常終了

・自動COMMIT（DDL:CREATE, ALTER, DROP　/　DCL:GRANT, REVOKE）は下記の場合、発生
　-DDL,DCL文が完了する時
　-明示的にCOMMIT、ROLLBACK無しでSQL*PLUSを正常に終了した場合

・自動ROLLBACKは下記の場合、発生
　-SQL*PLUSを非常に終了した場合
　-非常終了、SYSTEM FAILURE


■COMMITとROLLBACK
　-以前のCOMMITから次のCOMMIT前までの作業が１つのトランザクションであり、
　　COMMITとROLLBACKはトランザクションの単位でDBで発生した作業を保存、削除することを言う
　-COMMIT：変更の保存
　-ROLLBACK:変更の取消

・COMMITとROLLBACKの長所
　-データーの一貫性を提供する
　-データーの永久的に変更する前、データーの変更を確認できる
　-関連作業を論理的にグループ化出来る
　-COMMIT,SAVEPOINT,ROLLBACK文でTRANSACTIONの論理を制御できる

・COMMITやROLLBACK以前のデーター状態
　-データー以前の状態に復旧可能
　-現在のユーザーはSELECT文でDML作業結果を確認できる
　-他のユーザーはSELECT文で現在ユーザーが使用したDML文の結果を確認できない
　-変更された行はロックされ他のユーザーは変更できない

・COMMIT以後のデーター状態
　-DBにデーターを永久に変更
　-データーの変更以前状態は完全消失
　-他のユーザーも検索できる
　-変更された行のロックが解除され他ユーザーが変更できる
　-すべてのSAVEPOINTは消去される

SQL> SELECT empno FROM emp WHERE empno = 7521;

     EMPNO
----------
      7521

SQL> DELETE FROM emp WHERE empno = 7521;

1行が削除されました。

SQL> COMMIT;

コミットが完了しました。


--１行を削除して、COMMIT文でデーターを永久に保存した
--１トランザクションの終了と新しいトランザクションの発生を意味する


SQL> SELECT empno FROM emp WHERE empno = 7521;

レコードが選択されませんでした。

--結果表示タイミングを見る為に時間を表示させる
SQL> SET TIME ON
16:48:50 SQL> INSERT INTO emp(empno, ename, hiredate)
16:50:18   2  VALUES (9000, 'test', sysdate);

1行が作成されました。

16:50:19 SQL> COMMIT;

コミットが完了しました。

16:50:23 SQL> SELECT empno FROM emp WHERE empno = 9000;

     EMPNO
----------
      9000

16:50:27 SQL> DELETE FROM emp WHERE empno = 9000;

1行が削除されました。

16:50:48 SQL> SELECT empno FROM emp WHERE empno = 9000;

レコードが選択されませんでした。

--empno 9000のデーターを追加してCOMMITでデーターを保存した後、
--データーを削除してSELECTをすると、データーは検索されない

--しかし、別ユーザーからはCOMMIT、ROLLBACKする前に登録した
--empno 9000のデーターが検索できる
16:50:59 SQL> SHOW USER
ユーザーは"SYS"です。
16:51:11 SQL> SELECT empno FROM scott.emp WHERE empno = 9000;

     EMPNO
----------
      9000

16:51:15 SQL>
--データーベースではこれを一貫性と言う

--scottユーザーに戻る
16:51:20 SQL> ROLLBACK;

ロールバックが完了しました。

--以前のトランザクション（COMMIT）発生後から今発生したROLLBACK前までの
--作業を取り消す

--検索するとempno 9000のデーターが検索される
16:51:48 SQL> SELECT empno FROM emp WHERE empno = 9000;

     EMPNO
----------
      9000

16:51:53 SQL>

■SAVEPOINTとROLLBACK TO
　-SAVEPOINTはユーザーがトランザクション作業を複数のセグメントに分割出来るようにする作業
　-SAVEPOINTは部分的なROLLBACKをするためのトランザクションのチェックポイントを定義

SQL> INSERT INTO emp(empno, ename, hiredate)
  2  VALUES (1000, 'test2', sysdate);

1行が作成されました。

--SAVEPOINTを作成
SQL> SAVEPOINT A;

セーブ・ポイントが作成されました。

SQL> INSERT INTO emp(empno, ename, hiredate)
  2  VALUES (1001, 'test3', sysdate);

1行が作成されました。

SQL> INSERT INTO emp(empno, ename, hiredate)
  2  VALUES (1002, 'test4', sysdate);

1行が作成されました。

SQL> SELECT empno, ename
  2  FROM emp
  3  WHERE empno IN (1000, 1001, 1002);

     EMPNO ENAME
---------- ----------
      1000 test2
      1001 test3
      1002 test4

SQL> DELETE FROM emp
  2  WHERE empno IN (1000, 1001, 1002);

3行が削除されました。

SQL> SELECT empno, ename
  2  FROM emp
  3  WHERE empno IN (1000, 1001, 1002);

レコードが選択されませんでした。


--SAVEPOINTまでロールバックされる
SQL> ROLLBACK TO A;

ロールバックが完了しました。

SQL> SELECT empno, ename
  2  FROM emp
  3  WHERE empno IN (1000, 1001, 1002);

     EMPNO ENAME
---------- ----------
      1000 test2

SQL>

--SAVEPOINTまでROLLBACKが実行された
--その結果、最初に追加したデーターは残り、SAVEPOINT後に追加したデーターは削除された

08-05.データー操作語（DML）【Join03--CROSS JOIN, INNER JOIN, NATURAL JOIN, USING, ON】

■CROSS JOIN
　-Cartesian Product値を検索する時に使用

SQL> SELECT ename FROM emp CROSS JOIN dept;

ENAME
----------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS

.......

56行が選択されました。

SQL>

■INNER JOIN
　-結合時、「,」を省略しINNER JOINを追加し、WHERE句の代わりにON句を使用
　-INNERは省略可能
　-下記、2つの検索結果は同じ

--INNER JOIN使用例
SQL> SELECT e.empno, e.ename
  2  FROM dept d INNER JOIN emp e
  3  ON d.deptno = e.deptno;

     EMPNO ENAME
---------- ----------
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7844 TURNER
      7876 ADAMS

     EMPNO ENAME
---------- ----------
      7900 JAMES
      7902 FORD
      7934 MILLER

14行が選択されました。


--普通のSQL文
SQL> SELECt e.empno, e.ename
  2  FROM dept d, emp e
  3  WHERE d.deptno = e.deptno;

     EMPNO ENAME
---------- ----------
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7844 TURNER
      7876 ADAMS

     EMPNO ENAME
---------- ----------
      7900 JAMES
      7902 FORD
      7934 MILLER

14行が選択されました。

■NATURAL JOIN
　-Equi Joinと結果は同じ
　-２つのテーブルの同じ名前を持つコラムはすべて結合される
　-同一コラムを内部で検索するのでテーブルAliasを使うとエラーになる
　-同一コラムが２つ以上の場合、JOIN～USINGで結合されるコラムを制御できる
　-下記２つの結果は同じ

--NATURAL JOINを使用したSQL文
SQL> SELECT empno, ename, deptno
  2  FROM emp NATURAL JOIN dept;

     EMPNO ENAME          DEPTNO
---------- ---------- ----------
      7369 SMITH              20
      7499 ALLEN              30
      7521 WARD               30
      7566 JONES              20
      7654 MARTIN             30
      7698 BLAKE              30
      7782 CLARK              10
      7788 SCOTT              20
      7839 KING               10
      7844 TURNER             30
      7876 ADAMS              20

     EMPNO ENAME          DEPTNO
---------- ---------- ----------
      7900 JAMES              30
      7902 FORD               20
      7934 MILLER             10

14行が選択されました。


--通常のSQL文
SQL> SELECT e.empno, e.ename, d.deptno
  2  FROM emp e, dept d
  3  WHERE e.deptno = d.deptno;

     EMPNO ENAME          DEPTNO
---------- ---------- ----------
      7369 SMITH              20
      7499 ALLEN              30
      7521 WARD               30
      7566 JONES              20
      7654 MARTIN             30
      7698 BLAKE              30
      7782 CLARK              10
      7788 SCOTT              20
      7839 KING               10
      7844 TURNER             30
      7876 ADAMS              20

     EMPNO ENAME          DEPTNO
---------- ---------- ----------
      7900 JAMES              30
      7902 FORD               20
      7934 MILLER             10

14行が選択されました。

■JOIN～USING
　-USING文を使用するとNATURAL JOINでコラムを選択して結合できる
　-USING句内に含まれるコラムにAliasを指定するとエラーになる

--JOIN～USING使用例
SQL> SELECT e.empno, e.ename, deptno
  2  FROM emp e JOIN dept d USING(deptno);

     EMPNO ENAME          DEPTNO
---------- ---------- ----------
      7369 SMITH              20
      7499 ALLEN              30
      7521 WARD               30
      7566 JONES              20
      7654 MARTIN             30
      7698 BLAKE              30
      7782 CLARK              10
      7788 SCOTT              20
      7839 KING               10
      7844 TURNER             30
      7876 ADAMS              20

     EMPNO ENAME          DEPTNO
---------- ---------- ----------
      7900 JAMES              30
      7902 FORD               20
      7934 MILLER             10

14行が選択されました。

■ON
　-結合条件を指定できる
　-すべての論理演算及びサブクエリを指定できる

--テストのため、scottユーザーで下記データーを追加
SQL> INSERT INTO bonus(ename, job, sal) VALUES('SMITH', 'CLERK', 500);

1行が作成されました。

--ON使用例（multi-table joins）
SQL> SELECT e.empno, e.ename, e.sal
  2  FROM emp e JOIN dept d ON (e.deptno = d.deptno)
  3             JOIN bonus b ON (b.ename = e.ename)
  4  WHERE e.sal IS NOT NULL;

     EMPNO ENAME             SAL
---------- ---------- ----------
      7369 SMITH             800

08-05.データー操作語（DML）【Join02--Outer Join (LEFT, RIGHT, FULL OUTER JOIN)】

■Outer Joinとは？
　-Equi Joinは結合しようとする２つのテーブルの片方コラムに値がないとデーターを表示できない
　-同一条件で結合条件を果たす値が無い行を検索するためにOuter Joinを使用する
　-Outer Joinの演算子は「(+)」
　-結合時、値が無い側に「(+)」が位置する
　-Outer Join演算子は片方のみ使用できる

■Outer Joinの例

Equi JoinとOuter Joinの比較

--Equi Joinで部署番号を検索
SQL> SELECT DISTINCT(e.deptno), d.deptno, d.dname
  2  FROM emp e, dept d
  3  WHere e.deptno = d.deptno;

    DEPTNO     DEPTNO DNAME
---------- ---------- --------------
        20         20 RESEARCH
        30         30 SALES
        10         10 ACCOUNTING


--Outer Joinで部署番号を検索
SQL> SELECT DISTINCT(e.deptno), d.deptno
  2  FROM emp e, dept d
  3  WHere e.deptno(+) = d.deptno;

    DEPTNO     DEPTNO
---------- ----------
        10         10
                   40
        30         30
        20         20

Outer Joinを使用するテーブルに追加条件があれば、「（+）」演算子を使用する

--ename LIKE条件に(+)演算子が無い場合
SQL> SELECT DISTINCT(a.deptno), b.deptno
  2  FROM emp a, dept b
  3  WHERE a.deptno(+) = b.deptno
  4  AND a.ename LIKE '%';

    DEPTNO     DEPTNO
---------- ----------
        10         10
        30         30
        20         20


--ename LIKE条件に(+)演算子追加で正常にデーター検索ができる
SQL> SELECT DISTINCT(a.deptno), b.deptno
  2  FROM emp a, dept b
  3  WHERE a.deptno(+) = b.deptno
  4  AND a.ename(+) LIKE '%';

    DEPTNO     DEPTNO
---------- ----------
        10         10
                   40
        30         30
        20         20

■LEFT, RIGHT, FULL Outer Join
　-Oracle9iからANSI/ISO SQL標準のLEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOINを使える。

・LEFT OUTER JOIN
　-LEFT OUTER JOINは右側のテーブル（下記例のemp）に結合させるコラムの値がない場合

--LEFT OUTER JOIN例
SQL> SELECT DISTINCT(e.deptno), d.deptno
  2  FROM dept d
  3  LEFT OUTER JOIN emp e
  4  ON d.deptno = e.deptno;

    DEPTNO     DEPTNO
---------- ----------
        10         10
                   40
        30         30
        20         20

・RIGHT OUTER JOIN
　-RIGHT OUTER JOINは左側のテーブル（下記例のemp）に結合させるコラムの値がない場合

--RIGHT OUTER JOIN例
SQL> SELECT DISTINCT(e.deptno), d.deptno
  2  FROM emp e
  3  RIGHT OUTER JOIN dept d
  4  ON e.deptno = d.deptno;

    DEPTNO     DEPTNO
---------- ----------
        10         10
                   40
        30         30
        20         20

・FULL OUTER JOIN

　-FULL OUTER JOINは両方のテーブルにOUTER JOINを使用する場合

--FULL OUTER JOIN例
SQL> SELECT DISTINCT(e.deptno), d.deptno
  2  FROM emp e
  3  FULL OUTER JOIN dept d
  4  ON e.deptno = d.deptno;

    DEPTNO     DEPTNO
---------- ----------
        10         10
                   40
        30         30
        20         20

08-05.データー操作語（DML）【Join01--Equi Join, Non_Equi Join, Self Join】

■Joinとは？
　-２つ以上のテーブルを結合してデーターを検索する方法
　-通常２つ以上の行の共通値、Primary Key及びForeign Keyを利用して結合する
　-SELECT文でJoin結合するには最低１つのコラムがその２つのテーブルで共有されなければいけない

■Join方法と方式
　-方法：Equi Join（等結合、内部結合）、Nom-Equi Join、Self Join、Outer Join
　-方式：Nested Loop Join、Sort Merge Join、Hash Join

■Equi Join
　-一般的に使用されているEquality Condition（＝）による結合
　-Equi Joinの性能を上げるにはIndex機能を使用する

--deptテーブルとempテーブルを結合
SQL> SELECT e.empno, e.ename, d.dname
  2  FROM dept d, emp e
  3  WHERE d.deptno = e.deptno;

     EMPNO ENAME      DNAME
---------- ---------- --------------
      7369 SMITH      RESEARCH
      7499 ALLEN      SALES
      7521 WARD       SALES
      7566 JONES      RESEARCH
      7654 MARTIN     SALES
      7698 BLAKE      SALES
      7782 CLARK      ACCOUNTING
      7788 SCOTT      RESEARCH
      7839 KING       ACCOUNTING
      7844 TURNER     SALES
      7876 ADAMS      RESEARCH

     EMPNO ENAME      DNAME
---------- ---------- --------------
      7900 JAMES      SALES
      7902 FORD       RESEARCH
      7934 MILLER     ACCOUNTING

14行が選択されました。


--INNER JOINを使用した結合
--「,」の代わりにINNER JOIN使用可能、INNERは省略可能。
　JOINの条件はON句に記載。

SQL> SELECT e.empno, e.ename, d.dname
  2  FROM dept d
  3  INNER JOIN emp e
  4  ON d.deptno = e.deptno;

     EMPNO ENAME      DNAME
---------- ---------- --------------
      7369 SMITH      RESEARCH
      7499 ALLEN      SALES
      7521 WARD       SALES
      7566 JONES      RESEARCH
      7654 MARTIN     SALES
      7698 BLAKE      SALES
      7782 CLARK      ACCOUNTING
      7788 SCOTT      RESEARCH
      7839 KING       ACCOUNTING
      7844 TURNER     SALES
      7876 ADAMS      RESEARCH

     EMPNO ENAME      DNAME
---------- ---------- --------------
      7900 JAMES      SALES
      7902 FORD       RESEARCH
      7934 MILLER     ACCOUNTING

14行が選択されました。



--NATURAL JOINでの結合
--NATURAL JOINは同一コラムを内部的にすべて結合するのでON句は省略可能
SQL> SELECT e.empno, e.ename, d.dname
  2  FROM dept d
  3  NATURAL JOIN emp e;

     EMPNO ENAME      DNAME
---------- ---------- --------------
      7369 SMITH      RESEARCH
      7499 ALLEN      SALES
      7521 WARD       SALES
      7566 JONES      RESEARCH
      7654 MARTIN     SALES
      7698 BLAKE      SALES
      7782 CLARK      ACCOUNTING
      7788 SCOTT      RESEARCH
      7839 KING       ACCOUNTING
      7844 TURNER     SALES
      7876 ADAMS      RESEARCH

     EMPNO ENAME      DNAME
---------- ---------- --------------
      7900 JAMES      SALES
      7902 FORD       RESEARCH
      7934 MILLER     ACCOUNTING

14行が選択されました。



--JOIN～USINGを使用した結合
--NATURAL JOINはUSING文を使用することにより、コラムを選択し結合できる。
SQL> SELECT e.empno, e.ename, deptno
  2  FROM emp e
  3  JOIN dept d
  4  USING (deptno);

     EMPNO ENAME          DEPTNO
---------- ---------- ----------
      7369 SMITH              20
      7499 ALLEN              30
      7521 WARD               30
      7566 JONES              20
      7654 MARTIN             30
      7698 BLAKE              30
      7782 CLARK              10
      7788 SCOTT              20
      7839 KING               10
      7844 TURNER             30
      7876 ADAMS              20

     EMPNO ENAME          DEPTNO
---------- ---------- ----------
      7900 JAMES              30
      7902 FORD               20
      7934 MILLER             10

14行が選択されました。

■Non-Equi Join
　-テーブルのどのcolumnも結合するテーブルのcolumnと一致しない場合使用。
　-演算子は「=」以外を使う（BETWEEN AND, IS NULL, IS NOT NULL, IN, NOT IN）
　-ほぼ使用しない。

--empテーブルとsalgradeテーブルのNon-Equi Join
SQL> SELECT e.ename, e.sal, s.grade
  2  FROM emp e, salgrade s
  3  WHERE e.sal
  4  BETWEEN s.losal
  5  AND s.hisal;

ENAME             SAL      GRADE
---------- ---------- ----------
SMITH             800          1
JAMES             950          1
ADAMS            1100          1
WARD             1250          2
MARTIN           1250          2
MILLER           1300          2
TURNER           1500          3
ALLEN            1600          3
CLARK            2450          4
BLAKE            2850          4
JONES            2975          4

ENAME             SAL      GRADE
---------- ---------- ----------
SCOTT            3000          4
FORD             3000          4
KING             5000          5

14行が選択されました。

SQL>

■Self Join
　-Equi Joinと同じだが、１つのテーブル内で結合するのが違い。
　-１つのテーブルに対して２つのaliasを使用し、FROM句に２つのテーブルを使用するように結合

--社員の担当マネージャを検索するSelf Join
SQL> SELECT e.ename, a.ename "Manager"
  2  FROM emp e, emp a
  3  WHERE e.empno = a.mgr;

ENAME      Manager
---------- ----------
FORD       SMITH
BLAKE      ALLEN
BLAKE      WARD
KING       JONES
BLAKE      MARTIN
KING       BLAKE
KING       CLARK
JONES      SCOTT
BLAKE      TURNER
SCOTT      ADAMS
BLAKE      JAMES

ENAME      Manager
---------- ----------
JONES      FORD
CLARK      MILLER

13行が選択されました。

SQL>

■Cartesian Product「直積結合」
　-AとB、２つのテーブルを結合する時、JOIN条件を省略しAテーブルの全ての行がBテーブルの全ての行と結合される事
　-可能な全ての行の結合が出力され、過負荷の危険もある
　-テーブルの数がNならCartesian Productを避けるためには最低N-1の等条件をSELECT文に入れ、各テーブルのコラムが最低一回は条件句に参照されなければいけない。
　-CROSS JOIN使用でCartesian Product値を得られる。

--CROSS JOINを使用してCartesian Product値を得る。
SQL> SELECT e.empno, e.ename, d.dname
  2  FROM dept d CROSS JOIN emp e;

     EMPNO ENAME      DNAME
---------- ---------- --------------
      7369 SMITH      ACCOUNTING
      7499 ALLEN      ACCOUNTING
      7521 WARD       ACCOUNTING
      7566 JONES      ACCOUNTING
      7654 MARTIN     ACCOUNTING
      7698 BLAKE      ACCOUNTING
      7782 CLARK      ACCOUNTING
      7788 SCOTT      ACCOUNTING
      7839 KING       ACCOUNTING
      7844 TURNER     ACCOUNTING
      7876 ADAMS      ACCOUNTING

     EMPNO ENAME      DNAME
---------- ---------- --------------
      7900 JAMES      ACCOUNTING
      7902 FORD       ACCOUNTING
      7934 MILLER     ACCOUNTING
      7369 SMITH      RESEARCH
      7499 ALLEN      RESEARCH
      7521 WARD       RESEARCH
      7566 JONES      RESEARCH

.........

56行が選択されました。

SQL>

08-04.データー操作語（DML）【Alias】

■テーブル別名（Alias）
　-テーブルAliasでcolumnを単純、明確にできる。
　-現在のSELCT文のみ有効
　-テーブルAｌiasは長さ30文字まで可能
　-テーブルAliasは意味を付与するべき
　-FROM句にテーブルAlias設定時、該当テーブルAliasはSELECT文でテーブル代わりに使用

--社員数が3人以上の部署の部署名と社員数を検索
SQL> SELECT a.dname, b.cnt
  2  FROM dept a, (SELECT deptno, COUNT(empno) cnt
  3                FROM emp
  4                GROUP BY deptno) b
  5  WHERE a.deptno = b.deptno
  6  AND b.cnt > 3;

DNAME                 CNT
-------------- ----------
SALES                   6
RESEARCH                5

SQL>


-上記クエリでは3つのAliasを使用
-1．depテーブルを別名aに
-2．部署の社員数COUNT(empno)を別名cntに
-3．部署別社員数を検索するクエリを別名bに

※別名(Alias)はコラムだけでなく、クエリ、テーブルにも使用できる。

08-03.データー操作語（DML）【SELECT文】

■SELECT
　-データーベースに保存されているデーターの検索に使用する。

■SELECT文法

SELECT [DISTINCT] {*, column [alias],...}
FROM table_name
[WHERE condition]
[ORDER BY {column, expression} [ASC | DESC]];


-DISTINCT:重複している行を削除するオプション
-*:テーブルの全てのcolumnを出力
-alias:該当columnについて別名前を付与時に使用
-table_name:対象のテーブル名
-WHERE：条件を加えて行を検索
-ORDER BY:検索結果の整列オプション

■SQL文、作成方法
　-SQL文は大、小文字を区別しない。
　-SLQ文は1行または複数行に入力できる。
　-一般的にキーワードは大文字で入力する、
　　他の全ての単語（テーブル名、列名…）は小文字で入力（お勧め）
　-SQL文の最後には”;”を記載しコマンドの終わりを表す

--empnoとenameを「社番」と「名前」のコラム別名(alias)で出力する
--alias使用時にはasキーワードは省略可能

SQL> SELECT empno 社番, ename 名前
  2  FROM emp
  3  WHERE deptno = 10;

      社番 名前
---------- ----------
      7782 CLARK
      7839 KING
      7934 MILLER

SQL>

■WHERE句に使用できるSELECT演算子

■IN、NOT IN演算子
　-IN演算子

--社番が7900, 7934の社員の社番と名前を出力
SQL> SELECT empno, ename
  2  FROM emp
  3  WHERE empno IN (7900, 7934);

     EMPNO ENAME
---------- ----------
      7900 JAMES
      7934 MILLER

SQL>

　-NOT IN演算子

--社番が7900, 7934ではない社員の社番と名前を出力
SQL> SELECT empno, ename
  2  FROM emp
  3  WHERE empno NOT IN (7900, 7934);

     EMPNO ENAME
---------- ----------
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7844 TURNER
      7876 ADAMS
      7902 FORD

12行が選択されました。

SQL>

■BETWEEN演算子

--ANDを利用しで２つの条件を結合した検索結果を表示する

--給与が3000～5000の社員を表示
SQL> SELECT empno, ename
  2  FROM emp
  3  WHERE sal BETWEEN 3000 AND 5000;

     EMPNO ENAME
---------- ----------
      7788 SCOTT
      7839 KING
      7902 FORD

SQL>

■LIKE演算子
　-STRING値に対するワイルドカード検索の為、LIKE演算子を使用
　-%：複数の文字列を表すワイルドカード
　-_：1文字を表すワイルドカード
　-ESCAPE：ワイルドカード文字を一般文字のように使用したい場合
　　ex)WHERE name LIKE '%\_y%' ESPAPE '\'
　-LIKE演算子は大、小文字を区別する
　-UPPER()ファンクション使用で大小文字の区分無しで出力できる

■LIKE演算子例

--'K'文字が入っている社員情報確認
--UPPER()ファンクションは'k'を大文字の'K'に変換しで認識させる
SQL> SELECT empno,ename
  2  FROM emp
  3  WHERE UPPER(ename) LIKE '%K%';

     EMPNO ENAME
---------- ----------
      7698 BLAKE
      7782 CLARK
      7839 KING


--'_'を利用したLIKE検索
SQL> SELECT empno,ename
  2  FROM emp
  3  WHERE UPPER(ename) LIKE '_I%';

     EMPNO ENAME
---------- ----------
      7839 KING
      7934 MILLER

SQL>

■ORDER BY
　-ORDER BYはデーターの整列の為に使用（ASC[昇順、デフォルト]、DESC[降順]）

--名前をASCで整列
SQL> SELECT empno, ename
  2  FROM emp
  3  WHERE deptno = 30
  4  ORDER BY ename ASC;

     EMPNO ENAME
---------- ----------
      7499 ALLEN
      7698 BLAKE
      7900 JAMES
      7654 MARTIN
      7844 TURNER
      7521 WARD

6行が選択されました。

--2番目コラムをデフォルト(ASC)順で整列
SQL> SELECT empno, ename
  2  FROM emp
  3  WHERE deptno = 30
  4  ORDER BY 2;

     EMPNO ENAME
---------- ----------
      7499 ALLEN
      7698 BLAKE
      7900 JAMES
      7654 MARTIN
      7844 TURNER
      7521 WARD

6行が選択されました。

SQL>

--上記２つは同じ結果になる

08-02.データー操作語（DML）【MERGE文】

■MERGE文
　-MERGE文は条件により、データーの追加、更新、削除を一気にできる。
　-該当行が存在する場合、UPDATE(DELETEを含められる)を実施し、
　　新しい行の場合INSERTを実施する。
　-対象テーブルに対するUPDATE/INSERT条件はON～により決定される。
　-MERGE文でCLOB使用時、アップデートする内容が2000bytesが超えると
　　ORA-00600エラーが発生し、patch set 11.2.0.2バージョンで解決できる。


■MERGE Syntax

MERGE [hint]
    INTO [schema.]{table | view} [t_alias]
    USING { [schema.]{table|view}
           | subquery
          } [t_alias]
    ON (condition)
WHEN MATCHED THEN
UPDATE SET column = { expr | DEFAULT }
           [, column = { expr | DEFAULT }]...
[DELETE where_clause]
WHEN NOT MATCHED THEN
INSERT [(column [, column]...)]
VALUES ({ expr [, expr]... | DEFAULT})

-INTO:DATAをUPDATE、INSERTされるテーブルまたはビューを指定。
-USING:比較するSOURCEテーブルまたはビュー、サブクエリを指定。
-ON:UPDATE、INSERTをする条件で、該当条件を果たすDATAがあればWHEN MATCED句を実施、
    なければWHEN NOT MATCHED以下を実施する。
-WHEN MATCHED:ON条件句がTRUEであるROWに実施する内容(UPDATE,DELETEも可能)
-WHEN NOT MATCHED:ON条件句に合うROWがない場合実施する内容（INSERT）

■MERGE例１

--部署番号20,30の社員が存在すれば給与を10%アップする。
　存在しなければ給与が1000より多い社員情報を登録する。（INSERT, UPDATE）

--部署番号10,20の社員情報を持つテーブルを作成
SQL> CREATE TABLE emp_merge_test
  2  AS SELECT empno, deptno, sal FROM emp WHERE deptno IN (10, 20);

表が作成されました。


--データーの確認
SQL> SELECT * FROM emp_merge_test;

     EMPNO     DEPTNO        SAL
---------- ---------- ----------
      7369         20        800
      7566         20       2975
      7782         10       2450
      7788         20       3000
      7839         10       5000
      7876         20       1100
      7902         20       3000
      7934         10       1300

8行が選択されました。

--社員が存在すれば給与10%UP、なければINSERT
SQL> MERGE INTO emp_merge_test m
  2  USING ( SELECT empno, deptno, sal
  3          FROM emp
  4          WHERE deptno IN (20,30)) e
  5  ON (m.empno = e.empno)
  6  WHEN MATCHED THEN
  7    UPDATE SET m.sal = ROUND(m.sal*1.1)
  8  WHEN NOT MATCHED THEN
  9  INSERT (m.empno, m.deptno, m.sal)
 10  VALUES (e.empno, e.deptno, e.sal)
 11  WHERE e.sal > 1000
 12  ;

10行がマージされました。


--部署番号20の社員達の給与が20％UPされて、部署番号30が登録されているか確認
SQL> SELECT * FROM emp_merge_test;

     EMPNO     DEPTNO        SAL
---------- ---------- ----------
      7369         20        880
      7566         20       3273
      7782         10       2450
      7788         20       3300
      7839         10       5000
      7876         20       1210
      7902         20       3300
      7934         10       1300
      7844         30       1500
      7698         30       2850
      7499         30       1600

     EMPNO     DEPTNO        SAL
---------- ---------- ----------
      7521         30       1250
      7654         30       1250

13行が選択されました。


--次の例のため30部署のデーターを削除
SQL> DELETE FROM emp_merge_test WHERE deptno = 30;

5行が削除されました。

SQL> COMMIT;

コミットが完了しました。

■MERGE例２

--10部署の給与を10%UPし20部署の社員情報は削除、30部署の給与を30%UPする(INSERT,UPDATE,DELETE)

--データーの確認
SQL> SELECT * FROM emp_merge_test m;

     EMPNO     DEPTNO        SAL
---------- ---------- ----------
      7369         20        880
      7566         20       3273
      7782         10       2450
      7788         20       3300
      7839         10       5000
      7876         20       1210
      7902         20       3300
      7934         10       1300

8行が選択されました。


--MERGE文作成
SQL> MERGE INTO emp_merge_test m
  2  USING emp e
  3  ON (m.empno = e.empno)
  4  WHEN MATCHED THEN
  5     UPDATE SET m.sal = ROUND(m.sal*1.1)
  6     DELETE WHERE (m.deptno  = 20)
  7  WHEN NOT MATCHED THEN
  8  INSERT (m.empno, m.deptno, m.sal)
  9  VALUES (e.empno, e.deptno, ROUND(e.sal*1.2));

14行がマージされました。


--変更後の確認
SQL> SELECT * FROM emp_merge_test;

     EMPNO     DEPTNO        SAL
---------- ---------- ----------
      7782         10       2695
      7839         10       5500
      7934         10       1430
      7844         30       1800
      7521         30       1500
      7654         30       1500
      7698         30       3420
      7499         30       1920
      7900         30       1140

9行が選択されました。


--テストテーブルの削除
SQL> DROP TABLE emp_merge_test;

表が削除されました。

SQL>

08-01.データー操作語（DML）【データーの追加、修正、削除】

■INSERT
　-INSERTコマンドはテーブルへデーターを追加する。

--INSERTの文法
INSERT INTO table_name(column1, column2, ...)
VALUES (データー, 'データー', ...);

INSERT INTO table_name(column1, column2, ...)
SELECT column1, column2, ...
FROM table_name
WHERE 条件；

-実際のデーターはVALUES（）内へ入力し文字列は''で囲む
-各データーの区別は","
-テーブル名横の（）省略時には自動ですべてのコラムをVALUES()内へ入力する。

--INSERT例

--すべてのデーターを入力する場合
SQL> INSERT INTO emp
  2  VALUES (7369, 'SMITH', 'CLERK', 7902, TO_DATE('80/12/17'), 800, NULL, 20);

1行が作成されました。


--入力したいデーターのみINSERT
SQL> INSERT INTO dept (deptno, dname)
  2  VALUES (10, 'ACCOUNTING');

1行が作成されました。


--SELECT文を使ったINSERT
SQL> INSERT INTO dept2
  2  SELECT * FROM dept;
INSERT INTO dept2

■UPDATE
　-テーブル内のデーターを修正（変更）する。

--UPDATE文法
UPDATE table_name
SET column1 = 値(修正内容), column2 = 値, ...
WHERE 条件

--UPDATE例

--変更前確認
SQL> SELECT * FROM emp;

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 13-08-19        800                    20
      7499 ALLEN      SALESMAN        7698 13-08-19       1600        300         30
      7521 WARD       SALESMAN        7698 13-08-19       1250        500         30
      7566 JONES      MANAGER         7839 13-08-19       2975                    20
      7654 MARTIN     SALESMAN        7698 13-08-19       1250       1400         30
      7698 BLAKE      MANAGER         7839 13-08-19       2850                    30
      7782 CLARK      MANAGER         7839 13-08-19       2450                    10
      7788 SCOTT      ANALYST         7566 13-08-19       3000                    20
      7839 KING       PRESIDENT            13-08-19       5000                    10
      7844 TURNER     SALESMAN        7698 13-08-19       1500          0         30
      7876 ADAMS      CLERK           7788 13-08-19       1100                    20

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
      7900 JAMES      CLERK           7698 13-08-19        950                    30
      7902 FORD       ANALYST         7566 13-08-19       3000                    20
      7934 MILLER     CLERK           7782 13-08-19       1300                    10

14行が選択されました。


--社員番号7902の社員の部署番号を30に修正
SQL> UPDATE emp
  2  SET deptno = 30
  3  WHERE empno = 7902;

1行が更新されました。

--部署番号20の社員たちの給与を10%UP
SQL> UPDATE emp
  2  SET sal = sal * 1.1
  3  WHERE deptno = 20;

4行が更新されました。

--すべての社員の入社日を本日に修正
SQL> UPDATE emp
  2  SET hiredate = SYSDATE;

14行が更新されました。

--変更後の確認
SQL> SELECT * FROM emp;

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 13-08-19        880                    20
      7499 ALLEN      SALESMAN        7698 13-08-19       1600        300         30
      7521 WARD       SALESMAN        7698 13-08-19       1250        500         30
      7566 JONES      MANAGER         7839 13-08-19     3272.5                    20
      7654 MARTIN     SALESMAN        7698 13-08-19       1250       1400         30
      7698 BLAKE      MANAGER         7839 13-08-19       2850                    30
      7782 CLARK      MANAGER         7839 13-08-19       2450                    10
      7788 SCOTT      ANALYST         7566 13-08-19       3300                    20
      7839 KING       PRESIDENT            13-08-19       5000                    10
      7844 TURNER     SALESMAN        7698 13-08-19       1500          0         30
      7876 ADAMS      CLERK           7788 13-08-19       1210                    20

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
      7900 JAMES      CLERK           7698 13-08-19        950                    30
      7902 FORD       ANALYST         7566 13-08-19       3000                    30
      7934 MILLER     CLERK           7782 13-08-19       1300                    10

14行が選択されました。

SQL>

■DELETE
　-使用しないデーターを削除する。

--DELETE文法
DELETE FROM table_name WHERE 条件

--DELETE例

--社員番号7902の社員のデーターを削除
SQL> DELETE FROM emp
  2  WHERE empno = 7902;

1行が削除されました。

--平均給与より小さい給与の社員削除
SQL> DELETE FROM emp
  2  WHERE sal < (SELECT AVG(sal) FROM emp);

8行が削除されました。

--すべての行を削除
SQL> DELETE FROM emp;

5行が削除されました。

07-03.テーブル【テーブル管理】

■テーブルコラムの管理
　テーブルのコラムは　ADD, MODIFY, DROP演算子により管理できる。

■ADD
　テーブルへ新しいコラムを追加

-- VARCHAR2データタイプのaddrコラムをempテーブルへ追加

SQL> ALTER TABLE emp ADD (addr VARCHAR2(50));

表が変更されました。

SQL>

■MODIFIY
　テーブルのコラムを修正及びNOT NULLコラムに変更できる。
　コラムが既にデーターを持っている場合、他のデータータイプに変更できない。

-- enameコラムをVARCHAR2、サイズ50に修正した例

SQL> ALTER TABLE emp MODIFY (ename VARCHAR2(50));

表が変更されました。

SQL> ALTER TABLE emp MODIFY (ename VARCHAR2(50) NOT NULL);

表が変更されました。

SQL>

■DROP
　テーブルコラムを削除またはテーブル制約条件を削除できる。

-- コラムの削除文法
ALTER TABLE table_name DROP COLUMN column_name


-- 制約条件の削除例

SQL> ALTER TABLE emp2 DROP PRIMARY KEY;

表が変更されました。


-- CASCADEと一緒に使用すると外部キーにより参照されている主キーも削除できる。

SQL> ALTER TABLE dept DROP CONSTRAINT dept_pk_depno CASCADE;

表が変更されました。

SQL>

■既存テーブルのコピー
　-既存テーブルを一部及び全て複製するときはサブクエリを持つCREATE TABLEコマンドで簡単にコピーできる。
　-制約条件、トリガー、テーブル権限はコピーできない。
　-制約条件はNOT NULL条件のみコピーできる。

-- テーブル複製文法

CREATE TABLE [schema.]table_name 
 [LOGGING | NOLOGGING]
 [....]
AS
 subquery


-- テーブル複製例
-- empテーブルを複製してemp6テーブルを作成


SQL> SELECT * FROM emp;

     EMPNO ENAME                                              JOB              MGR HIREDATE        SAL       COMM     DEPTNO ADDR
---------- -------------------------------------------------- --------- ---------- -------- ---------- ---------- ---------- ----------
      7369 SMITH                                              CLERK           7902 13-08-13        800                    20
      7499 ALLEN                                              SALESMAN        7698 13-08-13       1600        300         30
      7521 WARD                                               SALESMAN        7698 13-08-13       1250        500         30
      7566 JONES                                              MANAGER         7839 13-08-13       2975                    20
      7654 MARTIN                                             SALESMAN        7698 13-08-13       1250       1400         30
      7698 BLAKE                                              MANAGER         7839 13-08-13       2850                    30
      7782 CLARK                                              MANAGER         7839 13-08-13       2450                    10
      7788 SCOTT                                              ANALYST         7566 13-08-13       3000                    20
      7839 KING                                               PRESIDENT            13-08-13       5000                    10
      7844 TURNER                                             SALESMAN        7698 13-08-13       1500          0         30
      7876 ADAMS                                              CLERK           7788 13-08-13       1100                    20

     EMPNO ENAME                                              JOB              MGR HIREDATE        SAL       COMM     DEPTNO ADDR
---------- -------------------------------------------------- --------- ---------- -------- ---------- ---------- ---------- ----------
      7900 JAMES                                              CLERK           7698 13-08-13        950                    30
      7902 FORD                                               ANALYST         7566 13-08-13       3000                    20
      7934 MILLER                                             CLERK           7782 13-08-13       1300                    10

14行が選択されました。

SQL> SELECT * FROM emp6;
SELECT * FROM emp6
              *
行1でエラーが発生しました。:
ORA-00942: 表またはビューが存在しません。


SQL> CREATE TABLE emp6
  2  AS SELECT * FROM emp;

表が作成されました。

SQL> SELECT * FROM emp6;

     EMPNO ENAME                                              JOB              MGR HIREDATE        SAL       COMM     DEPTNO ADDR
---------- -------------------------------------------------- --------- ---------- -------- ---------- ---------- ---------- ----------
      7369 SMITH                                              CLERK           7902 13-08-13        800                    20
      7499 ALLEN                                              SALESMAN        7698 13-08-13       1600        300         30
      7521 WARD                                               SALESMAN        7698 13-08-13       1250        500         30
      7566 JONES                                              MANAGER         7839 13-08-13       2975                    20
      7654 MARTIN                                             SALESMAN        7698 13-08-13       1250       1400         30
      7698 BLAKE                                              MANAGER         7839 13-08-13       2850                    30
      7782 CLARK                                              MANAGER         7839 13-08-13       2450                    10
      7788 SCOTT                                              ANALYST         7566 13-08-13       3000                    20
      7839 KING                                               PRESIDENT            13-08-13       5000                    10
      7844 TURNER                                             SALESMAN        7698 13-08-13       1500          0         30
      7876 ADAMS                                              CLERK           7788 13-08-13       1100                    20

     EMPNO ENAME                                              JOB              MGR HIREDATE        SAL       COMM     DEPTNO ADDR
---------- -------------------------------------------------- --------- ---------- -------- ---------- ---------- ---------- ----------
      7900 JAMES                                              CLERK           7698 13-08-13        950                    30
      7902 FORD                                               ANALYST         7566 13-08-13       3000                    20
      7934 MILLER                                             CLERK           7782 13-08-13       1300                    10

14行が選択されました。

SQL>

■テーブルスペース変更
　ORACLE 8iからは　ALTER TABLE ～ MOVE TABLESPACEコマンドで簡単にテーブルスペースを変更できる。

--テーブルスペース変更文
ALTER TABLE table_name Move TABLESPACE tablespace_name;

--テーブルスペース変更例
--scottユーザーへ権限付与
SQL> GRANT CREATE TABLESPACE, ALTER TABLESPACE, DROP TABLESPACE TO scott WITH ADMIN OPTION;

権限付与が成功しました。

--scottユーザーで接続
SQL> conn scott/tiger
接続されました。
SQL> show user
ユーザーは"SCOTT"です。

--test01テーブルスペース作成
SQL> CREATE TABLESPACE test01 DATAFILE 'test01.dbf' SIZE 20M;

表領域が作成されました。

--テーブルスペースの変更
SQL> ALTER TABLE emp
  2  MOVE TABLESPACE test01;

表が変更されました。

SQL>

■テーブルのTRUNCATE
　-テーブルをTruncateするとテーブルの安部手の行が削除され、使用された領域が解除される
　-TRUNCATE TABLEはDDLなのでロールバックデーターは作成されない。
　-DELETEコマンドでデーターを削除するとROLL BACKコマンドで復旧できるが、TRUNCATEデーターを削除すると復旧できない。
　-外部キー参照中のテーブルはTRUNCATEできない。
　-TRUNCATEコマンドを使用すると削除トリガーが実行できない。

--TRUNCATE文法

TRUNCATE TABLE [schema.]table_name;

■テーブル削除（DROP TABLE）

--テーブル削除文法
DROP TABLE [schema.]table_name [CASCADE CONSTRAINTS];

--empテーブル削除

SQL> DROP TABLE emp;

表が削除されました。

--CASCADE CONSTRAINTは外部キーにより参照している主キーを含むテーブルの場合
--主キーを参照してる外部キー条件も一緒に削除する。

SQL> DROP TABLE emp CASCADE CONSTRAINT;