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
登録:
投稿 (Atom)
