2013/10/17
2013/10/08
15.プロシージャ(PROCEDURE)と関数(FUNCTION)
■プロシージャ(Procedure)とは？
→特定の作業を実行する名前のあるPL/SQL BLOCKだ。
→パラメーターを受けられ、繰り返し使用できるBLOCKだ。
→通常、連続実行または複雑なトランザクションを実行するPL/SQL BLOCKをデータベースに
保存するために作る。
■プロシージャの文法
■プロシージャ作成例
■プロシージャ実行例 EXECUTEを使用してプロシージャを実行する
■関数（Function）とは？
→通常、計算後の値を返す為に関数を使用するケースが多い
→構成はほとんどプロシージャと似ているが、INパラメータのみ使用できる
→返すデータタイプをRETURN文に宣言しなければいけない
→PL/SQLブロック内でRETURN文を通じて値を返す
■関数文法
■関数作成例
■関数実行例
→特定の作業を実行する名前のあるPL/SQL BLOCKだ。
→パラメーターを受けられ、繰り返し使用できるBLOCKだ。
→通常、連続実行または複雑なトランザクションを実行するPL/SQL BLOCKをデータベースに
保存するために作る。
■プロシージャの文法
CREATE OR REPLACE procedure name
IN argument
OUT argument
IN OUT argument
IS
[変数の宣言]
BEGIN --->必須
[PL/SQL Block]
--SQL文、PL/SQL制御文
[EXCEPTION] --->選択
--errorが発生する時実行する文章
END; --->必須
・CREATE OR REPLACEを使用して作る
・ISでPL/SQLのブロックを始める
・LOCAL変数はISとBEGINの間に宣言する
■プロシージャ作成例
--プロシージャの名前はupdate sal --update salプロシージャは社員番号を受けて給与をアップさせる --プロシージャの終わりは「/」を表記する SQL> CREATE OR REPLACE PROCEDURE update_sal 2 /* IN Parameter */ 3 (v_empno IN NUMBER) 4 5 IS 6 7 BEGIN 8 9 UPDATE emp 10 set sal = sal * 1.1 11 WHERE empno = v_empno; 12 13 COMMIT; 14 15 END update_sal; 16 / プロシージャが作成されました。 SQL>
■プロシージャ実行例 EXECUTEを使用してプロシージャを実行する
SQL> EXECUTE update_sal(7369); PL/SQLプロシージャが正常に完了しました。 SQL> --社員番号7369の社員の給与が10%アップされたか確認してみよう
■関数（Function）とは？
→通常、計算後の値を返す為に関数を使用するケースが多い
→構成はほとんどプロシージャと似ているが、INパラメータのみ使用できる
→返すデータタイプをRETURN文に宣言しなければいけない
→PL/SQLブロック内でRETURN文を通じて値を返す
■関数文法
--PL/SQLブロックには少なくとも１つのRETURN文が必要だ --PL/SQL Blockは関数が実行する内容を定義した本体部分た CREATE OR REPLACE FUNCTION function name [(argument...)] RETURN datatype --datatypeは返す値のdatatypeだ IS [PL/SQL Block] --RETURN文必須 RETURN 変数; END;
■関数作成例
SQL> CREATE OR REPLACE FUNCTION FC_update_sal 2 (v_empno IN NUMBER) 3 4 --RETURNされる変数のデータタイプ定義は必須 5 RETURN NUMBER 6 7 IS 8 9 --%type変数を使用 10 v_sal emp.sal%type; 11 12 BEGIN 13 14 UPDATE emp 15 SET sal = sal * 1.1 16 WHERE empno = v_empno; 17 18 COMMIT; 19 20 SELECT sal 21 INTO v_sal 22 FROM emp 23 WHERE empno = v_empno; 24 25 --RETURNは必須 26 RETURN v_sal; 27 28 END; 29 / ファンクションが作成されました。 SQL>
■関数実行例
--まず関数が返す値を保存する変数を宣言
SQL> VAR salary NUMBER;
--EXECUTEを使用して関数を実行する
SQL> EXECUTE :salary := FC_update_sal(7900);
PL/SQLプロシージャが正常に完了しました。
--PRINT文で出力
SQL> PRINT salary;
SALARY
----------
1149.5
SQL>
2013/10/01
14.PL/SQL概要
■PL/SQLとは？
→PL/SQLはOracle's Procedural Language extension to SQLの訳
→SQL文で変数定義、条件処理(IF)、反復処理(LOOP,WHILE,FOR)等を使え、
オラクルに内蔵されているProcedure Languageである
→DECｌARE文を利用して定義され、宣言文の使用は選択事項
→PL/SQL文はブロック構造になっていて、PL/SQL自身がコンパイルエンジンを持っている
■PL/SQLの長所
→PL/SQL文はBLOCK構造で多数のSQL文を纏めてQRACLE DBに送り処理するので、処理速度が早い
→より強力なプログラムを作成するために大BLOCKの中に小BLOCKを持てる
→VARIABLE,CONSTANT,CURSOR,EXCEPTIONを定義し、SQL文とProcedural文で使用する
→単純、複雑なデータ形の変数を宣言する。
→テーブルのデータ構造とコラム名に準じて動的変数を宣言できる
→EXCEPTION処理ルーティンを利用してOracle Server Errorを処理する
→ユーザー定義エラーを宣言し、EXCEPTION処理ルーティンで処理可能だ
■PL/SQL Block Structure
→PL/SQLはプログラムを論理的なブロックに分ける構造化されているブロック言語だ
→PL/SQLブロックは宣言部(オプション)、実行部(必須)、例外処理部(オプション)で構成され、
BEGINとENDキーワードは必ず記述する
→PL/SQLブロックで使用する便数はブロックに対して論理的に宣言し、使用出来る
・Declarative Section(宣言部)
→変数、定数、CURSOR、USER_DEFINE Exception宣言
・Executable Section(実行部)
→SQL、反復文、条件文実行
→実行部はBEGINで始まりENDで終了する
→実行文はプログラム内容が入る部分で必須
・Exception Handling Section(例外処理)
→例外に関する処理
→一般的にエラーを定義し処理する部分で選択事項
■PL/SQLプログラムの作成要領
→PL/SQLブロック内では一文が終了するたびにセミコロン「;」を使用する
→ENDの後にセミコロン「;」を使用し１ブロックの終わりを明記する
→PL/SQLブロックの作成はエディターでファイルに作成出でき、SQLプロンプトで直接作成できる
→SQL*PLUS環境ではDECLAREやBEGINのキーワードでPL/SQLブロックが始まる
→1行コメント：--
→複数行コメント：/* */
→PL/SQLブロックは行に「/」が有ると終了される
■PL/SQLブロックの形
→PL/SQLはOracle's Procedural Language extension to SQLの訳
→SQL文で変数定義、条件処理(IF)、反復処理(LOOP,WHILE,FOR)等を使え、
オラクルに内蔵されているProcedure Languageである
→DECｌARE文を利用して定義され、宣言文の使用は選択事項
→PL/SQL文はブロック構造になっていて、PL/SQL自身がコンパイルエンジンを持っている
■PL/SQLの長所
→PL/SQL文はBLOCK構造で多数のSQL文を纏めてQRACLE DBに送り処理するので、処理速度が早い
→より強力なプログラムを作成するために大BLOCKの中に小BLOCKを持てる
→VARIABLE,CONSTANT,CURSOR,EXCEPTIONを定義し、SQL文とProcedural文で使用する
→単純、複雑なデータ形の変数を宣言する。
→テーブルのデータ構造とコラム名に準じて動的変数を宣言できる
→EXCEPTION処理ルーティンを利用してOracle Server Errorを処理する
→ユーザー定義エラーを宣言し、EXCEPTION処理ルーティンで処理可能だ
■PL/SQL Block Structure
→PL/SQLはプログラムを論理的なブロックに分ける構造化されているブロック言語だ
→PL/SQLブロックは宣言部(オプション)、実行部(必須)、例外処理部(オプション)で構成され、
BEGINとENDキーワードは必ず記述する
→PL/SQLブロックで使用する便数はブロックに対して論理的に宣言し、使用出来る
・Declarative Section(宣言部)
→変数、定数、CURSOR、USER_DEFINE Exception宣言
・Executable Section(実行部)
→SQL、反復文、条件文実行
→実行部はBEGINで始まりENDで終了する
→実行文はプログラム内容が入る部分で必須
・Exception Handling Section(例外処理)
→例外に関する処理
→一般的にエラーを定義し処理する部分で選択事項
■PL/SQLプログラムの作成要領
→PL/SQLブロック内では一文が終了するたびにセミコロン「;」を使用する
→ENDの後にセミコロン「;」を使用し１ブロックの終わりを明記する
→PL/SQLブロックの作成はエディターでファイルに作成出でき、SQLプロンプトで直接作成できる
→SQL*PLUS環境ではDECLAREやBEGINのキーワードでPL/SQLブロックが始まる
→1行コメント：--
→複数行コメント：/* */
→PL/SQLブロックは行に「/」が有ると終了される
■PL/SQLブロックの形
・Aninymous Block
→名前の無いブロックを意味し、実行するためプログラム内で宣言され実行時には実行のため
PL/SQLエンジンに送られる
→先行コンパイラープログラムとSQL*Plusまたはサーバー管理者からブロックを内蔵できる。
・Procedure
→特定の作業を実行出来る名前のあるPL/SQLブロックで、パラメーターを受けられ、
繰り返し使用できる
→通常、連続実行または複雑なトランザクションを実行するPL/SQLブロックをDBへ
保存するために作る
・Function
→通常、計算を行い結果を返す為に関数を使用する
→大体の構成がプロシーザーと似ているが、INパラメーターのみ使用可能で、
必ず返す値のデータタイプをRETURN文に宣言しなければならない
またPL/SQLブロック内でRETURN文で必ず値を返す必要がある。
2013/08/30
13-01.データディクショナリ(Data Dictionary)とは
■データ・ディクショナリ（Data Dictionary)とは
・DBA_XXXX
-DBA権限が有るユーザーのみ検索できるデータ・ディクショナリですべてのオラクルデータベースのオブジェクトの情報が見れる。
-SELECT ANY TABLE権限があるユーザーも問合せ可能で、他のユーザーが問合せするには前に「SYS.」の接頭語をつけなければいけない。
■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
・読み取り専用で提供されるテーブルやビューの集合でデータベース全般の情報を提供する。
・オラクルデータベースはコマンドが実行される毎にデータ・ディクショナリにアクセスする。
・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の情報を取得できる。
■CONNECT_BY_ISLEAF
・階層構造クエリでROW最下位レベルかどうかを返す。最下位なら1、じゃなければ0
■SYS_CONNECT_BY_PATH
・階層構造クエリで現在のROWのPATH情報を簡単に検索できる。
・下記のようにLeaf Nodeだけ、全体PATH情報が出るように作成できる。
■ORDER SIBLINGS BY ・階層構造クエリで相関関係を維持しながら整列させる
■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を意味する。
この上位階層と下位階層の関係を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の活用
-LEVEL Pseudocolumnを利用すると階層構造クエリを様々に活用できる。
-下記例は、LEVELに空白を追加し、階層構造を一目で見れるようにした例
■以下はLEVEL別で給与合計と社員数を検索する例
■PRIORの活用
・PRIOR演算子をSELECTで使用してみよう。
・下記は社員の管理者をPRIOR演算子を利用して検索する例
■Bottom Up検索例
・上の例を逆順（下位から上位へのツリー構造、Bottom Up）で検索する。
-またの名を再帰クエリ (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
2013/08/28
11-07.サブクエリ（Subquery）【UNION [ALL], INTERSECT, MINUS演算子】
■UNION [ALL]、INTERSECT、MINUS演算子
-UNIONとUNION ALLの違いは整列作業があるか、ないか。
-UNIONは重複を除去のため整列を実行するので性能が落ちる
-UNION、MINUS、INTERSECTは全体範囲をアクセスする整列作業を実行するので一部範囲の処理が不可能。
■UNION
-UNIONは２つのテーブル結合で、結合する２つのテーブルの重複しない値を返す。
■UNION ALL
-UNIONと同じだけど、重複する値も返す。
■INTERSECT
-INTERSECTは2行の集合で共通の行を返す
■MINUS
-MINUSは最初のSELECT文による結果行の中で、2つ目のSELECT文による結果行に存在しない行を返す。
-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の例
・NULLを返すOuter Joinの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を使える
-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に渡す。
■Nonpairwise Subquery -WHEREで２つ以上のSubqueryが使われ結果をMainqueryに渡す。
■Null Values in a Subquery
-サブクエリからNUKK値が返されるとMainqueryでは何も返さない。
-結果が２つ以上のコラムを返す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を使用するのが望ましい
■ANY演算子
-ANY演算子はSubqueryの複数の結果からどれか１つだけでも条件に合えば行を返す
■ALL演算子
-ALL演算子はSubquery結果すべてが条件に合えば行を返す
■EXISTS演算子
-EXISTS演算子はSubqueryデータが存在するかをチェックしTRUE/FALSEを返す。
-EXISTSには必ずメインクエリと繋がるJOIN条件が必要。
-subqueryで結果行が見つかったら、inner queryを中断し、TRUEを返す。
-１つ以上の行を返す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
-比較演算子（=,>,>=,<,<=,<>,!=）だけ使用される。
-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行以上検索する
-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の条件に利用される処理
2013/08/27
10-04.集計関数（Aggregate function）【CUBE】
■CUBE operator
-下記、ROLLUPの例にて部署別JOBに該当する給与と社員数を出力した。
しかし、部署別で各JOBの給与と社員数また、各JOB別の給与合計と社員数を見るためには
2つのROLLUPを使用する必要がある。
CUBEを使用して簡単なSQL文で上記結果が得られる
■GROUPING()関数
-GROUPING関数はROLLUP、CUBE両方で使用できる
-GROUPING関数は該当ROWがGROUP BYにより算出されたROWの場合0を返し、
ROLLUPかCUBEにより算出されたROWの場合は1を返す。
※該当ROWが結果集合により算出されたデータなのか、ROLLUPやCUBEにより算出された
データかを見分けるための関数！
-下記、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されたデータとそのデータの合計を出力できる。
部署別に人数と給与合計を出力する例
-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の後に追加すれば良い
下記例は部署別にグループ化し、部署番号、人数、給与の平均、給与の合計を検索
下記例は業務別にグループ化して業務、人数、平均給与、最高給与、最低給与、合計を検索
-GROUP BYは集計関数無しでも使える（DISTINCTと似た感じ…）
-下記例はGROUP BYのグループ化のみの役割
■DISTINCTとGROUP BY
-DISTINCTは主にUNIQUE（重複解消）なコラム、レ-コードを検索する場合使用する。
-GROUP BYはデータをグループ化して結果を検索する時に使用する。
-DISTINCTでGROUP BYも処理できるクエリ―もある。
-２つともOracle9iまではsortを利用しデータを作ったが、Oracle10gからはHashを利用して処理している。
しかし、GROUP BYとDISTINCTは各自特有の機能がある
集計関数を使用して特定グループに区分する時はGROUP BY、特定グループに区分しないで重複したデータを除去する場合はDISTINCTを使用するのが望ましい。
■HAVING
-WHEREでは集計関数を使用出来ない
-HAVINGは集計関数を持って条件比較をする時に使用する
-HAVINGはGROUP BYと一緒に使用される
下記例は社員数が3名を超える部署と社員数を検索する例
下記例は給与合計が5000を超えるJOBに関してJOBと給与の合計を検索する例、
但しSALESは除外して給与合計の降順で整列した。
■GROUPING SETS
-GROUPING SETS関数はGROUP BYの拡張した形でGROUP BYに複数のグループ化条件を設定できる。
-GROUPING SETS関数の結果は各GROUP条件を実施した結果をUNION ALLした結果と同じ
-GROUPING SETS関数でUNION ALL等を使用して複雑に作成されたSQL文をシンプルにまとめられる。
下記はGROUPING SETS関数とGROUP BY, UNION ALLを使用して同じ結果を出力する例
-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：検索された行の数を返す。
■MAX：コラム値の中で最大値を返す
■MIN：コラム値の中で最小値を返す
■AVG：平均値を返す
■SUM：検索されたコラムの合計を返す。
■STDDEV：標準偏差を返す。
■集計関数の例
-複数行またはテーブル全体行から１つの結果値を返す関数
-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値を他の値に替える時に使用、全てのデータタイプに適用可能
■NVL2
-NVL2はNVLとDECODEの概念を合わせた関数
-NVL2(expr, expr1, expr2)
-expr値がNULLじゃない場合、expr1を返し、NULLの場合はexpr2を返す
■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関数と似ている
-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関数使用可能
一般的に集計関数使用時は給与合計が行で検索されるが、
DECODEとMAX関数を使用すると列で値を表示できる
■CASE
-CASE関数はDECODE関数でカバー出来ない比較演算を解決できる関数
-DECODE関数での比較演算はGREATEST、LEAST等の関数を使用するが、
CASE関数では条件演算子をすべて使用できる。
-CASE関数はIF..THEN..ELSEと似ている。WHEN句の次に色んな条件を入れられる。
WHEN句の次に演算子が来る例
-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 ........
2013/08/26
09-05.Sing-Row Functions【その他関数】
■DUMP
-DUMPはバイトサイズと該当データタイプのコードを返す
※※ 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
-検索した値の中、最大値を返す
■LEAST
-検索した値の中、最小値を返す
■UID, USER
-UID現在ユーザーのID番号を返す
-USERはユーザーをVARCHAR2で返す
■USERENV
-セッション環境情報を返す
・ENTRYID：使用可能なAuditing entry Identifierを返す
・LABEL：現在セッションのLabelを返す
・LANGUAGE：現在セッションで使用中の言語とテリトリー値を返す
・SESSIONID：Auditing Session IDを返す
・TERMINAL：現在セッションターミナルのOS IDを返す
■VSIZE
-該当文字のBYTE数を返す。該当文字がNULLならNULLを返す
-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タイプのデータを特定の文字タイプに変換できる
・Date Formatの変換例
・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関数のフォーマットで使用可能。
■TO_NUMBER
-CHAR、VARCHAR2データタイプをNUMBERタイプに変換する。
-TO_NUMBER(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”は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秒
■SYSTIMESTAMP
-SYSTIMESTAMP関数を使用すると現在の日時を得られる
-最小単位=10億分の1秒
■ADD_MONTHS(a, b)
-ADD_MONTHS関数はaの日付にbの月を足した値を返す。
■MONTHS_BETWEEN(a1, a2)
-a1とa2の間の月の数をNUMBERタイプで返す
■LAST_DAY(d)
-その月の最終日を返す
■NEXT_DAY(d, c1)
-入力しは曜日に対して今度該当の日付を返す
-曜日は１（日）～７（土）を入力
■ROUND(d[,F])
-Fに指定された単位に四捨五入する。
-Fが年度なら年度で四捨五入、Fが省略されると日が一番近い日に四捨五入される。
■TRUNC(d[,F])
-Fに指定した単位で切り捨てを行なう
-日付関数は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の略称で文字列を結合した結果を返す。「||」演算子と同じ役割。
・INITCAP(char), LOWER(char), UPPER(char)
-INITCAP：文字列の最初も文字を大文字に変換
-LOWER：文字列を小文字に変換
-UPPER：文字列を大文字に変換
・LPAD（char1, n[, char2]）. RPAD(char1, n[, char2])
-LPAD：文字列の左に指定文字を埋める
-RPAD：文字列の右に指定文字を埋める
※nは返す文字列の長さを表し、char1の文字列がnより大きい場合、char1をn個の文字列を返す
・SUBSTR（char, m, [n]）, SUBSTRB(char, m, [n])
-SUBSTR関数はm番目から長さがn個の文字列を返す
-mが負数の場合後ろからm番目文字からn個の文字を返す
-SUBSTRB関数でBはByte単位で処理するを意味する
・LENGTH（char）, LENGTHB(char)：文字列の長さを返す
・REPLACE(char1, str1,str2)：文字列の特定文字を別文字に変換する
・INSTR(char1, str1, m, n)
-文字列が含まれているかを調査して文字列の位置を返す
-指定の文字列が無ければ0が返される
-char1 : 指定文字、str1 : 検索文字、m : 開始位置、n : 検索順位
・TRIM（char1[,char2]）, LTRIM(char1[,char2]), RTRIM(chaar1[,char2])
-TRIM：特定文字を削除する。削除する文字を入力しないと空白が削除される
-LTRIM：左から文字を削除する。削除する文字を入力しないと左の空白が削除される
-RTRIM：右から文字を削除する。削除する文字を入力しないと右の空白が削除される
-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
2013/08/23
09-01.Sing-Row Functions【数値関数(Numeric Functions)】
■Numberic Functions（数値関数）
-数値を引数で受けNUMERタイプの値を返す関数
・ABS(n)：絶対値を返す
・CEIL(n)：引数に対して、それ以上で、最小の整数値を返す
・FLOOR(n):引数に対して、それ以下で、最小の整数値を返す。
・MOD(m,n)：mをnで割った余りを返す。nが0の場合、mを返す
・ROUND(n,[m]):四捨五入結果を返す。mは小数点以下の桁数
・TRUNC(n,m)：切り捨て結果を返す。mは小数点以下の桁数
-数値を引数で受け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>
2013/08/22
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は消去される
■SAVEPOINTとROLLBACK TO
-SAVEPOINTはユーザーがトランザクション作業を複数のセグメントに分割出来るようにする作業
-SAVEPOINTは部分的なROLLBACKをするためのトランザクションのチェックポイントを定義
-トランザクションはデーター処理の１単位
-オラクルサーバで発生する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値を検索する時に使用
■INNER JOIN
-結合時、「,」を省略しINNER JOINを追加し、WHERE句の代わりにON句を使用
-INNERは省略可能
-下記、2つの検索結果は同じ
■NATURAL JOIN
-Equi Joinと結果は同じ
-２つのテーブルの同じ名前を持つコラムはすべて結合される
-同一コラムを内部で検索するのでテーブルAliasを使うとエラーになる
-同一コラムが２つ以上の場合、JOIN～USINGで結合されるコラムを制御できる
-下記２つの結果は同じ
■JOIN～USING
-USING文を使用するとNATURAL JOINでコラムを選択して結合できる
-USING句内に含まれるコラムにAliasを指定するとエラーになる
■ON
-結合条件を指定できる
-すべての論理演算及びサブクエリを指定できる
-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の比較
Outer Joinを使用するテーブルに追加条件があれば、「（+）」演算子を使用する
■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）に結合させるコラムの値がない場合
-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は左側のテーブル（下記例の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機能を使用する
■Non-Equi Join
-テーブルのどのcolumnも結合するテーブルのcolumnと一致しない場合使用。
-演算子は「=」以外を使う（BETWEEN AND, IS NULL, IS NOT NULL, IN, NOT IN）
-ほぼ使用しない。
■Self Join
-Equi Joinと同じだが、１つのテーブル内で結合するのが違い。
-１つのテーブルに対して２つのaliasを使用し、FROM句に２つのテーブルを使用するように結合
■Cartesian Product「直積結合」
-AとB、２つのテーブルを結合する時、JOIN条件を省略しAテーブルの全ての行がBテーブルの全ての行と結合される事
-可能な全ての行の結合が出力され、過負荷の危険もある
-テーブルの数がNならCartesian Productを避けるためには最低N-1の等条件をSELECT文に入れ、各テーブルのコラムが最低一回は条件句に参照されなければいけない。
-CROSS JOIN使用でCartesian Product値を得られる。
-２つ以上のテーブルを結合してデーターを検索する方法
-通常２つ以上の行の共通値、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>
2013/08/20
08-04.データー操作語（DML）【Alias】
■テーブル別名（Alias）
-テーブルAliasでcolumnを単純、明確にできる。
-現在のSELCT文のみ有効
-テーブルAｌiasは長さ30文字まで可能
-テーブルAliasは意味を付与するべき
-FROM句にテーブルAlias設定時、該当テーブルAliasはSELECT文でテーブル代わりに使用
-テーブル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文法
■SQL文、作成方法
-SQL文は大、小文字を区別しない。
-SLQ文は1行または複数行に入力できる。
-一般的にキーワードは大文字で入力する、
他の全ての単語（テーブル名、列名…）は小文字で入力（お勧め）
-SQL文の最後には”;”を記載しコマンドの終わりを表す
■WHERE句に使用できるSELECT演算子
■IN、NOT IN演算子
-IN演算子
-NOT IN演算子
■BETWEEN演算子
■LIKE演算子
-STRING値に対するワイルドカード検索の為、LIKE演算子を使用
-%：複数の文字列を表すワイルドカード
-_：1文字を表すワイルドカード
-ESCAPE：ワイルドカード文字を一般文字のように使用したい場合
ex)WHERE name LIKE '%\_y%' ESPAPE '\'
-LIKE演算子は大、小文字を区別する
-UPPER()ファンクション使用で大小文字の区分無しで出力できる
■LIKE演算子例
■ORDER BY
-ORDER BYはデーターの整列の為に使用（ASC[昇順、デフォルト]、DESC[降順]）
-データーベースに保存されているデーターの検索に使用する。
■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例１
■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>
2013/08/19
08-01.データー操作語（DML）【データーの追加、修正、削除】
■INSERT
-INSERTコマンドはテーブルへデーターを追加する。
■UPDATE
-テーブル内のデーターを修正（変更）する。
■DELETE
-使用しないデーターを削除する。
-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行が削除されました。
登録:
投稿 (Atom)
