| SQL*Plus®ユーザーズ・ガイドおよびリファレンス リリース1 (12.1) B71396-03 |
|
 前 |
 次 |
SQLのSELECTコマンドでORDER BY句を使用する場合、順序を指定された列(または式)に同じ値がある行は、同時に表示されます。SQL*PlusのBREAKおよびCOMPUTEコマンドを使用して、レコードのサブセットを作成し、各サブセットの後に空白またはサマリー行を追加すると、出力をユーザーにとってより有効なものにできます。
BREAKコマンド内に指定する列は、ブレーク列と呼ばれます。 ORDER BY句にブレーク列を挿入すると、意味のあるレコードのサブセットを作成できます。その後、同じBREAKコマンド内でサブセットに書式設定を追加でき、COMPUTEコマンドにブレーク列を指定して(合計、平均などを含む)サマリー行を追加できます。
SELECT DEPARTMENT_ID, LAST_NAME, SALARY FROM EMP_DETAILS_VIEW WHERE SALARY > 12000 ORDER BY DEPARTMENT_ID;
DEPARTMENT_ID LAST_NAME SALARY
------------- ------------------------- ----------
20 Hartstein 13000
80 Russell 14000
80 Partners 13500
90 King 24000
90 Kochhar 17000
90 De Haan 17000
6 rows selected.
このレポートをさらに有効に使用するには、BREAKを使用してDEPARTMENT_IDをブレーク列として設定します。BREAKを使用すると、DEPARTMENT_ID値の重複を抑止できます。また、部門と部門の間で、空白行の入力または改ページができます。BREAKをCOMPUTEと組み合せて使用すると、各部門およびすべての部門について給与の合計を含むサマリー行を計算して出力できます。また、平均、最大、最小、標準偏差、平方偏差または行数を含むサマリー行も出力できます。
BREAKコマンドをデフォルトで使用すると、指定された列または式の値の重複を抑止できます。したがって、ORDER BY句で指定した列の値が重複しないようにするには、次のようにBREAKコマンドを最も単純な形式で使用します。
BREAK ON break_column
注意:
BREAKコマンドに列または式を指定する場合は、必ず、その列または式を指定したORDER BY句を使用してください。ORDER BY句を使用しない場合は、列の値を変更すると常にブレークが発生します。
DEPARTMENT_ID LAST_NAME SALARY
------------- ------------------------- ----------
20 Hartstein 13000
80 Russell 14000
Partners 13500
90 King 24000
Kochhar 17000
De Haan 17000
6 rows selected.
例6-10 ブレーク列内の値の重複の抑止
表示する問合せ結果で重複した部門番号の表示を抑止するには、次のコマンドを入力します。
BREAK ON DEPARTMENT_ID;
次に、バッファに格納されている現行の問合せの例を示します。
SELECT DEPARTMENT_ID, LAST_NAME, SALARY FROM EMP_DETAILS_VIEW WHERE SALARY > 12000 ORDER BY DEPARTMENT_ID;
ブレーク列内で値が変更されると、常に、空白行の挿入または改ページができます。n行の空白行を挿入するには、次の形式でBREAKコマンドを使用します。
BREAK ON break_column SKIP n
ページをスキップするには、次の形式でBREAKコマンドを入力します。
BREAK ON break_column SKIP PAGE
DEPARTMENT_ID LAST_NAME SALARY
------------- ------------------------- ----------
20 Hartstein 13000
80 Russell 14000
Partners 13500
90 King 24000
Kochhar 17000
De Haan 17000
6 rows selected.
例6-11 ブレーク列の値が変更されたときの空白の挿入
部門と部門の間に1行の空白行を挿入するには、次のコマンドを入力します。
BREAK ON DEPARTMENT_ID SKIP 1
この時点で、次のように問合せを再実行します。
/
ORDER BY句に複数の列が存在し、各列の値の変更時に空白を挿入するとします。BREAKコマンドは、入力すると常に前のBREAKコマンドと置き換えられます。したがって、1つのレポート内で様々な間隔設定技法を使用する場合、または順序を指定された複数の列で値が変更されたときに空白を挿入する場合は、1つのBREAKコマンドに複数の列およびアクションを設定する必要があります。
Page: 1
DEPARTMENT_ID JOB_ID LAST_NAME SALARY
------------- ---------- ------------------------- ----------
20 MK_MAN Hartstein 13000
Page: 2
DEPARTMENT_ID JOB_ID LAST_NAME SALARY
------------- ---------- ------------------------- ----------
80 SA_MAN Russell 14000
Partners 13500
Page: 3
DEPARTMENT_ID JOB_ID LAST_NAME SALARY
------------- ---------- ------------------------- ----------
90 AD_PRES King 24000
AD_VP Kochhar 17000
De Haan 17000
6 rows selected.
例6-12 間隔設定技法の統合
次のように入力します。
SELECT DEPARTMENT_ID, JOB_ID, LAST_NAME, SALARY FROM EMP_DETAILS_VIEW WHERE SALARY>12000 ORDER BY DEPARTMENT_ID, JOB_ID;
この時点で、DEPARTMENT_IDの値が変更されたときに1ページをスキップし、JOB_IDの値が変更されたときに1行スキップするには、次のコマンドを入力します。
BREAK ON DEPARTMENT_ID SKIP PAGE ON JOB_ID SKIP 1
SKIP PAGEが有効になったことを示すには、次のように入力して、ページ番号付きのTTITLEを作成します。
TTITLE COL 35 FORMAT 9 'Page:' SQL.PNO
この新しい問合せの実行結果は、次のように表示されます。
BREAKコマンドでレポートの行をサブセットに編成した場合、各サブセット内の行に対して様々な計算を実行できます。この計算は、SQL*PlusのCOMPUTEコマンドの関数を使用して実行します。BREAKコマンドおよびCOMPUTEコマンドを同時に指定し、次の形式で入力します。
BREAK ON break_column COMPUTE function LABEL label_name OF column column column ... ON break_column
BREAKコマンドには、複数のブレーク列、および行のスキップなどのアクションを挿入できます。ただし、COMPUTEコマンドでONの後に指定した列は、BREAKコマンドのONの後にも指定する必要があります。COMPUTEコマンドと同時に使用しているときに、BREAKコマンドに複数のブレーク列およびアクションを挿入するには、これらのコマンドを次の形式で使用します。
BREAK ON break_column_1 SKIP PAGE ON break_column_2 SKIP 1 COMPUTE function LABEL label_name OF column column column ... ON break_column_2
COMPUTEコマンドは、対応するBREAKコマンドがない場合は機能しません。
COMPUTEは、NUMBER列および(場合によっては)すべてのタイプの列に対して実行できます。詳細は、「COMPUTE」コマンドを参照してください。
次の表に、COMPUTE関数およびその機能を示します。
表6-1 COMPUTE関数
| 関数 | 機能 |
|---|---|
SUM |
|
MINIMUM |
列内の最小値を計算する |
MAXIMUM |
列内の最大値を計算する |
AVG |
列内の値の平均を計算する |
STD |
列内の値の標準偏差を計算する |
VARIANCE |
列内の値の平方偏差を計算する |
COUNT |
列内の値のNULL以外の値の数を計算する |
NUMBER |
列内の行数を計算する |
COMPUTEコマンド内で指定した関数は、OFの後およびONの前に入力したすべての列に適用されます。計算後の値は、順序が指定された列の値が変更されると、別々の行に出力されます。
ON REPORT計算およびON ROW計算のラベルは、1列目に表示されます。それ以外の場合、ラベルはON句で指定した列に表示されます。
COMPUTE LABELを使用すると、計算ラベルを変更できます。計算後の値のラベルを定義しない場合は、略称化されていないファンクション・キーワードが出力されます。
計算ラベルを非表示にするには、ブレーク列で、COLUMNコマンドのNOPRINTオプションを使用します。詳細は、「COMPUTE」コマンドを参照してください。COMPUTEが実行されている列に対してNOPRINTオプションを使用すると、COMPUTEの実行結果も非表示になります。
break on DEPARTMENT_ID page nodup
on JOB_ID skip 1 nodup
この時点で、次のCOMPUTEコマンドを入力し、現行の問合せを実行します。
COMPUTE SUM OF SALARY ON DEPARTMENT_ID /
DEPARTMENT_ID JOB_ID LAST_NAME SALARY
------------- ---------- ------------------------- ----------
20 MK_MAN Hartstein 13000
************* ********** ----------
sum 13000
DEPARTMENT_ID JOB_ID LAST_NAME SALARY
------------- ---------- ------------------------- ----------
80 SA_MAN Russell 14000
Partners 13500
************* ********** ----------
sum 27500
DEPARTMENT_ID JOB_ID LAST_NAME SALARY
------------- ---------- ------------------------- ----------
90 AD_PRES King 24000
AD_VP Kochhar 17000
De Haan 17000
************* ********** ----------
sum 58000
6 rows selected.
計算ラベルを出力せずに部門10および20の給与の合計を計算するには、次のように入力します。
COLUMN DUMMY NOPRINT; COMPUTE SUM OF SALARY ON DUMMY; BREAK ON DUMMY SKIP 1; SELECT DEPARTMENT_ID DUMMY,DEPARTMENT_ID, LAST_NAME, SALARY FROM EMP_DETAILS_VIEW WHERE SALARY>12000 ORDER BY DEPARTMENT_ID;
DEPARTMENT_ID LAST_NAME SALARY
------------- ------------------------- ----------
20 Hartstein 13000
----------
13000
80 Russell 14000
80 Partners 13500
----------
27500
90 King 24000
90 Kochhar 17000
90 De Haan 17000
----------
58000
6 rows selected.
レポートの終わりで給与を計算するには、次のように入力します。
COLUMN DUMMY NOPRINT; COMPUTE SUM OF SALARY ON DUMMY; BREAK ON DUMMY; SELECT NULL DUMMY,DEPARTMENT_ID, LAST_NAME, SALARY FROM EMP_DETAILS_VIEW WHERE SALARY>12000 ORDER BY DEPARTMENT_ID;
DEPARTMENT_ID LAST_NAME SALARY
------------- ------------------------- ----------
20 Hartstein 13000
80 Russell 14000
80 Partners 13500
90 King 24000
90 Kochhar 17000
90 De Haan 17000
----------
98500
6 rows selected.
NUMBER列の書式を設定する場合は、レポートに挿入する合計欄のサイズも考慮する必要があります。
例6-13 小計の計算および出力
SALARYの合計を部門別に計算するには、最初に、次のように入力して現行のBREAK定義を表示します。
BREAK
次のように現行のBREAK定義が表示されます。
BREAKコマンドおよびCOMPUTEコマンドを次の形式で使用すると、列内のすべての値に基づいてサマリー行を計算し出力できます。
BREAK ON REPORT COMPUTE function LABEL label_name OF column column column ... ON REPORT
LAST_NAME SALARY
------------------------- ----------
Russell 14000
Partners 13500
Errazuriz 12000
Cambrault 11000
Zlotkey 10500
----------
TOTAL 61000
総合計(または、総平均、総最大値など)を小計(または小平均など)とともに出力するには、BREAKコマンドにブレーク列およびON REPORT句を挿入します。その後、次のように、ブレーク列用に1つのCOMPUTEコマンドを入力し、ON REPORTを計算するためにもう1つのCOMPUTEコマンドを入力します。
BREAK ON break_column ON REPORT COMPUTE function LABEL label_name OF column ON break_column COMPUTE function LABEL label_name OF column ON REPORT
異なる列について同じタイプのサマリー値を計算して出力できます。そのためには、列ごとに別々のCOMPUTEコマンドを入力します。
1* SELECT LAST_NAME, SALARY
APPEND , COMMISSION_PCT;
最後に、修正した問合せを次のように実行し、結果を表示します。
/
LAST_NAME SALARY COMMISSION_PCT
------------------------- ---------- --------------
Russell 14000 .4
Partners 13500 .3
Errazuriz 12000 .3
Cambrault 11000 .3
Zlotkey 10500 .2
---------- --------------
sum 61000 1.5
同じブレーク列について、複数のサマリー行を出力することもできます。そのためには、次のようにCOMPUTEコマンドに各サマリーについての関数を挿入します。
COMPUTE function LABEL label_name function LABEL label_name function LABEL label_name ... OF column ON break_column
OFの後およびONの前に複数の列を挿入した場合、COMPUTEによって、指定したそれぞれの列についての値が計算され、出力されます。
DEPARTMENT_ID LAST_NAME SALARY
------------- ------------------------- ----------
30 Colmenares 2500
Himuro 2600
Tobias 2800
Baida 2900
Khoo 3100
Raphaely 11000
************* ----------
avg 4150
sum 24900
6 rows selected.
例6-15 異なる列についての同じタイプのサマリー値の計算
すべての販売担当者の給与およびコミッションの合計を出力するには、最初に次のCOMPUTEコマンドを入力します。
COMPUTE SUM OF SALARY COMMISSION_PCT ON REPORT
BREAKコマンドを入力する必要はありません。例6-14で入力したBREAKコマンドは、まだ有効です。この時点で、次のように選択した問合せの最初の行を変更し、COMMISSION_PCTを追加します。
1
例6-16 同じブレーク列についての複数のサマリー行の計算
販売部門の給与の平均および合計を計算するには、最初に、次のBREAKコマンドおよびCOMPUTEコマンドを入力します。
BREAK ON DEPARTMENT_ID COMPUTE AVG SUM OF SALARY ON DEPARTMENT_ID
この時点で、次の問合せを入力して実行します。
SELECT DEPARTMENT_ID, LAST_NAME, SALARY FROM EMP_DETAILS_VIEW WHERE DEPARTMENT_ID = 30 ORDER BY DEPARTMENT_ID, SALARY;
