7 SQL*Plusレポートの書式設定
この章では、最終的なレポートを生成するために問合せ結果の書式を設定する方法について説明します。ここでは、HTML出力を除く次の項目について説明します。
この章を読むときは、コンピュータで、示されている例を実際に試してみてください。始める前に、「SQL*Plusのクイック・スタート 」で説明したHRサンプル・スキーマへのアクセス権限があることを確認してください。
7.1 列の書式設定について
7.1.1 列ヘッダーの変更について
列ヘッダーを表示する場合は、デフォルトのヘッダーを使用するか、またはデフォルトのヘッダーをCOLUMNコマンドで変更できます。次の各項では、デフォルトのヘッダーの導出方法およびCOLUMNコマンドを使用してデフォルトのヘッダーを変更する方法について説明します。詳細は、「COLUMN」コマンドを参照してください。
7.1.1.2 デフォルトのヘッダーの変更
次の形式でCOLUMNコマンドのHEADING句を使用すると、さらに有効な列ヘッダーを定義できます。
COLUMN column_name HEADING column_heading
LAST NAME MONTHLY SALARY COMMISSION
------------------------- -------------- ----------
Russell 14000 .4
Partners 13500 .3
Errazuriz 12000 .3
Cambrault 11000 .3
Zlotkey 10500 .2
ノート:
新しいヘッダーは、別のヘッダーを入力するか、列の書式をリセットするか、またはSQL*Plusを終了するまで有効です。
列ヘッダーを複数の単語に変更するには、COLUMNコマンドの入力時に、新しいヘッダーを一重引用符または二重引用符で囲みます。列ヘッダーを複数の行に表示するには、新しい行を開始する位置に垂直バー(|)を使用します。(SETコマンドのHEADSEP変数の設定を変更すると、垂直バー以外の文字を使用できます。詳細は、「SET」コマンドを参照してください。)
LAST MONTHLY
NAME SALARY COMMISSION
------------------------- ---------- ----------
Russell 14000 .4
Partners 13500 .3
Errazuriz 12000 .3
Cambrault 11000 .3
Zlotkey 10500 .2
LAST MONTHLY
NAME SALARY COMMISSION
========================= ========== ==========
Russell 14000 .4
Partners 13500 .3
Errazuriz 12000 .3
Cambrault 11000 .3
Zlotkey 10500 .2
この時点で、次のように下線文字をダッシュに戻してください。
SET UNDERLINE '-'
ノート:
ダッシュは、引用符で囲む必要があります。囲まない場合、ダッシュは、次の行へコマンドを継続することを示すハイフンとして解釈されます。
例7-1 列ヘッダーの変更
LAST_NAME、SALARYおよびCOMMISSION_PCTに新しいヘッダーを指定してEMP_DETAILS_VIEWからレポートを生成するには、次のコマンドを入力します。
COLUMN LAST_NAME HEADING 'LAST NAME' COLUMN SALARY HEADING 'MONTHLY SALARY' COLUMN COMMISSION_PCT HEADING COMMISSION SELECT LAST_NAME, SALARY, COMMISSION_PCT FROM EMP_DETAILS_VIEW WHERE JOB_ID='SA_MAN';
例7-2 列ヘッダーの分割
SALARYおよびLAST_NAME列にそれぞれMONTHLY SALARYおよびLAST NAMEというヘッダーを付け、新しいヘッダーを2行に分割するには、次のように入力します。
COLUMN SALARY HEADING 'MONTHLY|SALARY' COLUMN LAST_NAME HEADING 'LAST|NAME'
この時点で、次のように/(スラッシュ)コマンドで問合せを再実行します。
/
例7-3 下線文字の設定
ヘッダーに下線を付けるために使用する文字を等号に変更し、問合せを再実行するには、次のコマンドを入力します。
SET UNDERLINE = /
7.1.2 NUMBER列の書式設定について
NUMBER列を表示する場合は、SQL*Plusのデフォルトの表示幅を使用するか、またはCOLUMNコマンドを使用してデフォルトの表示幅を変更します。次の各項では、デフォルトの表示およびCOLUMNコマンドでデフォルトを変更する方法について説明します。書式モデルは、新しい書式モデルを入力するか、または次のコマンドを使用して列の書式をリセットするまで有効です。
COLUMN column_name CLEAR
または、SQL*Plusを終了するまで有効です。
7.1.2.1 デフォルトの表示
NUMBER列の幅は、ヘッダーの幅か、FORMATの幅に符号用の1つの空白を加えた幅のどちらか大きい方に等しくなります。明示的にFORMATを使用しない場合、列の幅は、常にSET NUMWIDTHの値以上になります。
通常、SQL*Plusでは、数値は、SETコマンドのNUMWIDTH変数の値で決まる標準表示幅(通常は10)を最大幅として、必要なだけの桁数で表示されます。可能な場合、SET NUMWIDTHの値より大きい数値は、許容最大文字数で切り上げられるか、または切り捨てられます。数値が大きすぎる場合は、ハッシュが表示されます。
COLUMNコマンドで書式モデルを使用すると、NUMBER列に別の書式を選択できます。書式モデルとは、列の中に数値を表示する方法を表したもので、複数の9を使用して数字の桁を表します。
7.1.2.2 デフォルト表示の変更
COLUMNコマンドを使用すると、次に示すように、書式を設定する列および使用するモデルを識別できます。
COLUMN column_name FORMAT model
列の中の数値にカンマ、ドル記号、山カッコ(負の値を囲む)、および先行0(ゼロ)を追加するには、書式モデルを使用します。値を一定の小数桁数まで丸め、負の値の右側(左側でなく)にマイナス記号を表示し、指数表記で値を表示することもできます。
単一の列に複数の書式モデルを使用するには、使用するモデルを結合して1つのCOLUMNコマンドにします(例7-4を参照)。書式モデルのすべてのリストおよび詳細は、「COLUMN」を参照してください。
LAST MONTHLY
NAME SALARY COMMISSION
------------------------- -------- ----------
Russell $14,000 .4
Partners $13,500 .3
Errazuriz $12,000 .3
Cambrault $11,000 .3
Zlotkey $10,500 .2
ドル記号などの他の書式を使用し、0(ゼロ)値に空白ではなく0(ゼロ)を表示する場合は、表示する値として書式モデル内で0(ゼロ)を使用します。
7.1.3 データ型の書式設定について
データ型を表示する場合は、SQL*Plusのデフォルトの表示幅を使用するか、またはCOLUMNコマンドを使用してデフォルトの表示幅を変更します。書式モデルは、新しい書式モデルを入力するか、または次のコマンドを使用して列の書式をリセットするまで有効です。
COLUMN column_name CLEAR
または、SQL*Plusを終了するまで有効です。このマニュアルでは、データ型に次の型が含まれます。
7.1.3.1 デフォルトの表示
データ型列のデフォルトの幅は、データベース内の列の幅です。LONG列、BLOB列、BFILE列、CLOB列、NCLOB列またはXMLType列のデフォルトの幅は、SET LONGCHUNKSIZEまたはSET LONGのいずれか小さい方の値です。
SQL*Plusでは、書式が設定されていないDATE列のデフォルトの幅と書式は、データベースのNLS_DATE_FORMATパラメータによって決定されます。それ以外の場合、デフォルトの書式の幅はA9です。DATE列の書式設定の詳細は、「COLUMN」コマンドのFORMAT句を参照してください。
データ型のデフォルトの文字位置は、左揃えです。
7.1.3.2 デフォルト表示の変更
データ型またはDATEの表示幅は、COLUMNコマンドと、文字A(英数字の意)の後に列幅を表す数値を続けた書式モデルを使用して変更できます。
COLUMNコマンド内で、書式を設定する列および使用するモデルを次のように指定します。
COLUMN column_name FORMAT model
列ヘッダーより短い幅を指定した場合は、ヘッダーが切り捨てられます。詳細は、「COLUMN」コマンドを参照してください。
LAST MONTHLY
NAME SALARY COMMISSION
---- -------- ----------
Russ $14,000 .4
ell
Part $13,500 .3
ners
Erra $12,000 .3
zuri
z
LAST MONTHLY
NAME SALARY COMMISSION
---- -------- ----------
Camb $11,000 .3
raul
t
Zlot $10,500 .2
key
SETコマンドのWRAP変数をON (デフォルト値)に設定した場合、例7-5に示すように、従業員名は4文字目の後で次の行へ折り返されます。WRAPをOFFに設定した場合、名前は4文字目の後で切り捨てられます。
システム変数WRAPは、すべての列を制御します。列に対するWRAPの設定は、COLUMNコマンドのWRAPPED、WORD_WRAPPEDおよびTRUNCATED句を指定すると上書きできます。これらの句の詳細は、「COLUMN」コマンドを参照してください。この章の後半でCOLUMNコマンドのWORD_WRAPPEDを使用します。
NCLOB、BLOB、BFILEまたはマルチバイトCLOBの各列をWORD_WRAPPEDオプションで書式設定することはできません。NCLOB、BLOB、BFILEまたはマルチバイトCLOBの各列をCOLUMN WORD_WRAPPEDで書式設定した場合、列データはCOLUMN WRAPPEDが適用されたかのように動作します。
ノート:
列のヘッダーは、WRAPの設定、またはどのCOLUMNコマンド句の設定にも関係なく切り捨てられます。
この時点で、次のように列を元の書式に戻します。
COLUMN LAST_NAME FORMAT A10
Building
--------------------
Owned
createXML、extract、text、getStringValの各ファンクション、およびXMLTypeデータの作成と操作の詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。
例7-5 文字列の書式設定
LAST_NAME列の幅を4文字に設定し、現行の問合せを再実行するには、次のように入力します。
COLUMN LAST_NAME FORMAT A4 /
例7-6 XMLType列の書式設定
XMLType列の書式設定方法について説明する前に、XMLType列の定義を含む表を作成し、その表にデータを挿入する必要があります。XMLType列は、他のユーザー定義列と同様の方法で作成できます。XMLType列を含む表を作成するには、次のように入力します。
CREATE TABLE warehouses ( warehouse_id NUMBER(3), warehouse_spec SYS.XMLTYPE, warehouse_name VARCHAR2 (35), location_id NUMBER(4));
warehouse_idおよびwarehouse_specの値を含む新しいレコードを新しいwarehouses表に挿入するには、次のように入力します。
INSERT into warehouses (warehouse_id, warehouse_spec) VALUES (100, sys.XMLTYPE.createXML( '<Warehouse whNo="100"> <Building>Owned</Building> </Warehouse>'));
XMLType列の幅を20文字に設定し、XMLType列を選択するには、次のように入力します。
COLUMN Building FORMAT A20 SELECT w.warehouse_spec.extract('/Warehouse/Building/text()').getStringVal() "Building" FROM warehouses w;
7.1.4 列の表示属性のコピー
複数の列に同じ表示属性を設定する場合は、COLUMNコマンドのLIKE句を使用して入力すると、コマンドの長さを短くすることができます。LIKE句を使用すると、SQL*Plusで、前に定義した列の表示属性が新しい列にコピーされます。ただし、同じコマンドの別の句によって加えられた変更はコピーされません。
LAST MONTHLY
NAME SALARY BONUS
---------- -------- --------
Russell $14,000 $0
Partners $13,500 $0
Errazuriz $12,000 $0
Cambrault $11,000 $0
Zlotkey $10,500 $0
例7-7 列の表示属性のコピー
COMMISSION_PCT列に、SALARYに指定した表示属性と同じ表示属性を指定し、別のヘッダーを指定するには、次のコマンドを入力します。
COLUMN COMMISSION_PCT LIKE SALARY HEADING BONUS
次のように問合せを再実行します。
/
7.1.5 列の表示属性の表示およびリセット
列の現行の表示属性を表示するには、次に示すように、COLUMNコマンドおよびその後に列名のみを指定します。
COLUMN column_name
すべての列の現行の表示属性を表示するには、次のように、後ろに列名または句を指定せずにCOLUMNコマンドを入力します。
COLUMN
列の表示属性をデフォルト値にリセットするには、次に示すようにCOLUMNコマンドのCLEAR句を使用します。
COLUMN column_name CLEAR
columns cleared
例7-8 列の表示属性をデフォルトにリセットする方法
すべての列の表示属性をデフォルトにリセットするには、次のように入力します。
CLEAR COLUMNS
7.1.7 折り返した列の値の後で文字の行を出力する方法
前述のとおり、SQL*Plusのデフォルトでは、列の値が列幅に収まらない場合、その値は追加行に折り返されます。折り返された出力行の後(または各行の後)にレコード・セパレータ文(字または空白の1行)を挿入する場合は、SETコマンドのRECSEPおよびRECSEPCHAR変数を使用します。
RECSEPを使用して、文字の行を出力するタイミングを決定できます。RECSEPをEACHに設定すると、各行の後に文字の行が出力されます。WRAPPEDに設定すると、折返し行の後に出力されます。OFFに設定すると、出力されません。RECSEPのデフォルトの設定は、WRAPPEDです。
RECSEPCHARは、各行に出力される文字を設定します。RECSEPCHARには、任意の文字を設定できます。
列の値が次の行に折り返される場合は、すべての単語を次の行に折り返すこともできます。この場合は、次に示すように、COLUMNコマンドのWORD_WRAPPED句を使用します。
COLUMN column_name WORD_WRAPPED
LAST_NAME JOB_TITLE CITY
------------------------- -------------------- --------
King President Seattle
Kochhar Administration Vice Seattle
President
-------------------------------------------------------
De Haan Administration Vice Seattle
President
-------------------------------------------------------
Russell Sales Manager Oxford
Partners Sales Manager Oxford
Hartstein Marketing Manager Toronto
6 rows selected.
RECSEPをEACHに設定した場合、各行の後(この例では各部門の後)に文字の行が出力されます。
先へ進む前に、次のようにRECSEPをOFFに設定して、レコード・セパレータを出力しないようにします。
SET RECSEP OFF
例7-9 折り返した列の値の後で文字の行を出力する方法
列の値を折り返した後にダッシュの行を出力するには、次のコマンドを入力します。
SET RECSEP WRAPPED SET RECSEPCHAR "-"
最後に、次の問合せを入力します。
SELECT LAST_NAME, JOB_TITLE, CITY FROM EMP_DETAILS_VIEW WHERE SALARY>12000;
この時点で、次のようにJOB_TITLE列の幅を制限し、必要な場合は、SQL*Plusですべての単語を次の行に折り返します。
COLUMN JOB_TITLE FORMAT A20 WORD_WRAPPED
次のように問合せを実行します。
/
7.2 間隔の設定およびサマリー行によるレポートの明確化について
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と組み合せて使用すると、各部門およびすべての部門について給与の合計を含むサマリー行を計算して出力できます。また、平均、最大、最小、標準偏差、平方偏差または行数を含むサマリー行も出力できます。
7.2.1 ブレーク列内の値の重複の抑止
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.
例7-10 ブレーク列内の値の重複の抑止
表示する問合せ結果で重複した部門番号の表示を抑止するには、次のコマンドを入力します。
BREAK ON DEPARTMENT_ID;
次に、バッファに格納されている現行の問合せの例を示します。
SELECT DEPARTMENT_ID, LAST_NAME, SALARY FROM EMP_DETAILS_VIEW WHERE SALARY > 12000 ORDER BY DEPARTMENT_ID;
7.2.2 ブレーク列の値が変更されたときの空白の挿入
ブレーク列内で値が変更されると、常に、空白行の挿入または改ページができます。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.
例7-11 ブレーク列の値が変更されたときの空白の挿入
部門と部門の間に1行の空白行を挿入するには、次のコマンドを入力します。
BREAK ON DEPARTMENT_ID SKIP 1
この時点で、次のように問合せを再実行します。
/
7.2.4 複数の間隔設定技法の使用方法
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.
例7-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
この新しい問合せの実行結果は、次のように表示されます。
7.2.6 ブレーク列の値が変更されたときにサマリー行を計算する方法
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関数およびその機能を示します。
表7-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列の書式を設定する場合は、レポートに挿入する合計欄のサイズも考慮する必要があります。
例7-13 小計の計算および出力
SALARYの合計を部門別に計算するには、最初に、次のように入力して現行のBREAK定義を表示します。
BREAK
次のように現行のBREAK定義が表示されます。
7.2.7 レポートの終わりでのサマリー行の計算
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
7.2.8 複数のサマリー値およびサマリー行の計算
異なる列について同じタイプのサマリー値を計算して出力できます。そのためには、列ごとに別々の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.
例7-15 異なる列についての同じタイプのサマリー値の計算
すべての販売担当者の給与およびコミッションの合計を出力するには、最初に次のCOMPUTEコマンドを入力します。
COMPUTE SUM OF SALARY COMMISSION_PCT ON REPORT
BREAKコマンドを入力する必要はありません。例7-14で入力したBREAKコマンドは、まだ有効です。この時点で、次のように選択した問合せの最初の行を変更し、COMMISSION_PCTを追加します。
1
例7-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;
7.3 ページおよびレポートのタイトルとサイズの定義について
ページとは、画面に表示される1画面全体の情報、またはスプールされる(印刷される)レポートの1ページを指します。各ページの上部タイトルおよび下部タイトルの配置、1ページごとの行数の設定、および各行の幅の設定ができます。
レポートとは、問合せの結果全体を指します。それぞれのレポートにヘッダーおよびフッターを配置し、それらの書式をページの上部タイトルおよび下部タイトルと同じ方法で設定できます。
7.3.1 上部タイトルと下部タイトルおよびヘッダーとフッターの設定
前述のとおり、レポートの各ページの一番上に表示するタイトルを設定できます。また、各ページの一番下に表示するタイトルも設定できます。TTITLEコマンドで上部タイトルを定義し、BTITLEコマンドで下部タイトルを定義します。
それぞれのレポートにヘッダーおよびフッターを設定することもできます。REPHEADERコマンドでレポート・ヘッダーを定義し、REPFOOTERコマンドでレポート・フッターを定義します。
TTITLE、BTITLE、REPHEADERまたはREPFOOTERコマンドでは、コマンド名の後に、位置または書式を指定する1つ以上の句を指定し、その後に、その位置に指定するCHAR値またはその書式を設定するCHAR値を指定します。次のように、複数の句およびCHAR値のセットを挿入できます。
TTITLE position_clause(s) char_value position_clause(s) char_value ... BTITLE position_clause(s) char_value position_clause(s) char_value ... REPHEADER position_clause(s) char_value position_clause(s) char_value ... REPFOOTER position_clause(s) char_value position_clause(s) char_value ...
すべてのTTITLE、BTITLE、REPHEADERおよびREPFOOTER句の詳細は、「TTITLE」コマンドおよび「REPHEADER」コマンドを参照してください。
ACME SALES DEPARTMENT PERSONNEL REPORT
DEPARTMENT_ID LAST_NAME SALARY
------------- ------------------------- ----------
30 Colmenares 2500
30 Himuro 2600
30 Tobias 2800
30 Baida 2900
30 Khoo 3100
30 Raphaely 11000
COMPANY CONFIDENTIAL
6 rows selected.
ACME SALES DEPARTMENT PERSONNEL REPORT
PERFECT WIDGETS
COMPANY CONFIDENTIAL
ACME SALES DEPARTMENT PERSONNEL REPORT
DEPARTMENT_ID LAST_NAME SALARY
------------- ------------------------- ----------
30 Colmenares 2500
30 Himuro 2600
30 Tobias 2800
30 Baida 2900
30 Khoo 3100
30 Raphaely 11000
COMPANY CONFIDENTIAL
6 rows selected.
レポート・ヘッダーの定義を変更せずに非表示にするには、次のように入力します。
REPHEADER OFF
例7-18 ページの上部タイトルおよび下部タイトルの配置
レポートの各ページの一番上および一番下にタイトルを挿入するには、次のように入力します。
TTITLE CENTER - "ACME SALES DEPARTMENT PERSONNEL REPORT" BTITLE CENTER "COMPANY CONFIDENTIAL"
この時点で、次のように現行の問合せを実行します。
/
例7-19 レポートのヘッダーの配置
個々のページにレポート・ヘッダーを中央揃えで配置するには、次のように入力します。
REPHEADER PAGE CENTER 'PERFECT WIDGETS'
この時点で、次のように現行の問合せを実行します。
/
次に示す2ページの出力が表示され、最初のページには新しく設定したREPHEADERが表示されます。
7.3.1.1 タイトル要素の位置決定
前の例に示したレポートは、会社名をさらに強調し、個別の行の両端にレポートの種類および部門名を配置するとさらに体裁が良くなります。また、行サイズを小さくして、タイトルをデータの近くに中央揃えに配置するのも効果的です。
これらの変更を加えるには、次の例に示すように、TTITLEコマンドにいくつかの句を追加し、システム変数LINESIZEをリセットします。
レポートのヘッダーおよびフッターは、REPHEADERコマンドおよびREPFOOTERコマンドを使用してBTITLEおよびTTITLEと同じ方法で書式を設定できます。
A C M E W I D G E T
====================
PERSONNEL REPORT SALES DEPARTMENT
DEPARTMENT_ID LAST_NAME SALARY
------------- ------------------------- ----------
30 Colmenares 2500
30 Himuro 2600
30 Tobias 2800
30 Baida 2900
30 Khoo 3100
30 Raphaely 11000
COMPANY CONFIDENTIAL
6 rows selected.
LEFT、RIGHTおよびCENTER句を使用して、後続の値を行の始め、終わりおよび中央に配置します。1行以上、下へ移動するには、SKIP句を使用します。
結果の最後の行と下部タイトルの間に空白がなくなっていることに注意してください。下部タイトルの最終行は、ページの最終行に表示されます。レポートの最終行から下部タイトルまでの空白の量は、ページ全体のサイズ、上部タイトルが占める行数、そのページ内の行数によって決まります。この例の上部タイトルは、その前の例で示した上部タイトルより3行多く行数をとります。1ページごとの行数の設定方法は、この章の後半で学習します。
下部タイトルの前にn行の空白行を常に出力するには、BTITLEコマンドの先頭にSKIP n句を使用します。たとえば、この例で下部タイトルの前で1行スキップするには、次のコマンドを入力します。
BTITLE SKIP 1 CENTER 'COMPANY CONFIDENTIAL'
例7-20 タイトル要素の位置決定
上部タイトルの位置を設定しなおして人事レポートを再表示するには、次のコマンドを入力します。
TTITLE CENTER 'A C M E W I D G E T' SKIP 1 - CENTER ==================== SKIP 1 LEFT 'PERSONNEL REPORT' - RIGHT 'SALES DEPARTMENT' SKIP 2 SET LINESIZE 60 /
7.3.1.2 タイトル要素のインデント
特定の空白の個数分だけタイトル要素をインデントするには、TTITLEコマンドまたはBTITLEコマンドにCOL句を使用します。たとえば、COL 1を指定すると、後続の値が先頭文字の位置に置かれます。したがって、これはLEFT、つまり0個のインデントに相当します。COL 15を指定すると、タイトル要素が15文字目に配置され、14個分の空白でインデントされます。
ACME WIDGET
SALES DEPARTMENT PERSONNEL REPORT
DEPARTMENT_ID LAST_NAME SALARY
------------- ------------------------- ----------
30 Colmenares 2500
30 Himuro 2600
30 Tobias 2800
30 Baida 2900
30 Khoo 3100
30 Raphaely 11000
COMPANY CONFIDENTIAL
6 rows selected.
例7-21 タイトル要素のインデント
会社名を左揃えで出力し、レポート名を次の行に空白5個分のみインデントして出力するには、次のように入力します。
TTITLE LEFT 'ACME WIDGET' SKIP 1 - COL 6 'SALES DEPARTMENT PERSONNEL REPORT' SKIP 2
この時点で現行の問合せを実行し、結果を表示します。
/
7.3.1.3 長いタイトルの入力
長さが500文字を超えるタイトルを入力する必要がある場合は、SQL*PlusコマンドのDEFINEを使用して、タイトルの各行のテキストを別々の置換変数に格納します。
DEFINE LINE1 = 'This is the first line...' DEFINE LINE2 = 'This is the second line...' DEFINE LINE3 = 'This is the third line...'
その後、次のようにTTITLEコマンドまたはBTITLEコマンド内でそれらの変数を参照します。
TTITLE CENTER LINE1 SKIP 1 CENTER LINE2 SKIP 1 - CENTER LINE3
7.3.2 タイトル内にシステム管理値を表示する方法
現行ページ番号およびその他のシステム管理値をタイトル内に表示するには、システム値の名前をタイトル要素として入力します。たとえば、次のように入力します。
TTITLE LEFT system-maintained_value_name
タイトル内に表示できるシステム管理値は5つあり、そのうち最も一般的なものはSQL.PNO(現行のページ番号)です。タイトル内に表示できるシステム管理値のリストについては、「TTITLE」コマンドを参照してください。
ACMEWIDGET PAGE: 1
DEPARTMENT_ID LAST_NAME SALARY
------------- ------------------------- ----------
30 Colmenares 2500
30 Himuro 2600
30 Tobias 2800
30 Baida 2900
30 Khoo 3100
30 Raphaely 11000
COMPANY CONFIDENTIAL
6 rows selected.
SQL.PNOの書式が空白10個分の幅であることに注意してください。この書式は、TTITLE(またはBTITLE)のFORMAT句を使用して変更できます。
ACME WIDGET 'PAGE:' 1
DEPARTMENT_ID LAST_NAME SALARY
------------- ------------------------- ----------
30 Colmenares 2500
30 Himuro 2600
30 Tobias 2800
30 Baida 2900
30 Khoo 3100
30 Raphaely 11000
COMPANY CONFIDENTIAL
6 rows selected.
例7-22 タイトル内での現行ページ番号の表示
現行ページ番号を会社名とともに各ページの一番上に表示するには、次のコマンドを入力します。
TTITLE LEFT 'ACME WIDGET' RIGHT 'PAGE:' SQL.PNO SKIP 2
この時点で、現行の問合せを再実行します。
/
7.3.4 タイトル内に列の値を表示する方法
プライマリ/ディテール・レポートを作成して、変更されるプライマリ列の値を各ページの上部に表示し、その下に、その値に関するディテール問合せ結果を表示できます。上部タイトル内の列値を参照するには、求める値を変数に格納し、TTITLEコマンド内でその変数を参照します。変数を定義するには、次の書式のCOLUMNコマンドを使用します。
COLUMN column_name NEW_VALUE variable_name
SKIP PAGE句を使用して、ORDER BY句およびBREAKコマンド内にプライマリ列を挿入する必要があります。
Manager: 101
DEPARTMENT_ID LAST_NAME SALARY
------------- ------------------------- ----------
10 Whalen 4400
40 Mavris 6500
70 Baer 10000
100 Greenberg 12000
110 Higgins 12000
Manager: 201
DEPARTMENT_ID LAST_NAME SALARY
------------- ------------------------- ----------
20 Fay 6000
6 rows selected.
ページの一番下に列の値を出力する場合は、次の形式でCOLUMNコマンドを使用します。
COLUMN column_name OLD_VALUE variable_name
プライマリ列の新しい値の検出後、新しいページへのブレークのプロセスの一環として下部タイトルが出力されます。したがって、単にプライマリ列のNEW_VALUEを参照した場合は、次の一連のディテールについて値が取得されます。OLD_VALUEには、ページ・ブレークが開始される前に有効であったプライマリ列の値が記録されます。
例7-24 プライマリ/ディテール・レポートの作成
2人のマネージャの従業員番号を別々のページの一番上にそれぞれ表示し、そのマネージャの部下をマネージャの従業員番号と同じページに表示するレポートを作成するとします。最初に、次のように入力して、現行のマネージャの従業員番号を保持する変数MGRVARを作成します。
COLUMN MANAGER_ID NEW_VALUE MGRVAR NOPRINT
マネージャの従業員番号は、タイトル内に表示されるのみなので、ディテールの列としては出力しません。この例で入力したNOPRINT句を使用すると、MANAGER_ID列は出力されません。
次に、ページ・タイトルにラベルおよび値を挿入し、適切なBREAKコマンドを入力して、最後の例の下部タイトルを非表示にします。
TTITLE LEFT 'Manager: ' MGRVAR SKIP 2 BREAK ON MANAGER_ID SKIP PAGE BTITLE OFF
最後に、次の問合せを入力して実行します。
SELECT MANAGER_ID, DEPARTMENT_ID, LAST_NAME, SALARY FROM EMP_DETAILS_VIEW WHERE MANAGER_ID IN (101, 201) ORDER BY MANAGER_ID, DEPARTMENT_ID;
7.3.5 タイトル内に現在の日付を表示する方法について
タイトルに値を入力するだけで、レポートに日付を付けることができます。非定型のレポートの場合はこの方法でも十分ですが、同じレポートを繰り返し実行する場合は、レポートの実行時に日付が自動的に付くようにするとより効果的です。この機能は、現在の日付を保持する変数を作成して使用します。
事前に定義した置換変数_DATEを参照して、他の変数の場合と同様に、現在の日付をタイトルに表示できます。
LOGINファイルまたはSELECT文に指定されている日付書式モデルによって、SQL*Plusでの日付の表示書式が決まります。日付書式モデルの詳細は、『Oracle Database SQL言語リファレンス』を参照してください。LOGINファイルの詳細は、「LOGINファイルの変更」を参照してください。
これらのコマンドは、対話形式でも入力できます。詳細は、「COLUMN」を参照してください。
7.3.6 ページ・サイズの設定
通常、レポートの1ページには、SETコマンドのNEWPAGE変数で設定された行数分の空白行、上部タイトル、列ヘッダー、問合せ結果および下部タイトルが含まれています。SQL*Plusでは、長すぎて1ページに収まらないレポートは複数の連続したページに表示され、各ページに独自のタイトルおよび列ヘッダーが付けられます。SQL*Plusで各ページに表示されるデータの量は、現行のページ・サイズによって決まります。
-
上部タイトルより前の行数: 1
-
上部タイトルからページ一番下までの1ページ当たりの行数: 14
-
1行当たりの文字数: 80
これらの設定は、ご使用のコンピュータ画面のサイズ、または用紙サイズ(印刷の場合)に合うように変更できます。
ページの長さはシステム変数PAGESIZEを使用して変更できます。ページの長さの変更は、レポートの印刷時などに必要な場合があります。
各ページの始めから上部タイトルまでの行数を設定するには、SETコマンドのNEWPAGE変数を使用します。
SET NEWPAGE number_of_lines
NEWPAGEを0(ゼロ)に設定した場合、SQL*Plusでは、新しいページを開始するために0(ゼロ)行がスキップされ、改ページ文字が表示および印刷されます。ほとんどのタイプのコンピュータ画面では、改ページ文字を使用すると画面が消去され、カーソルが1行目の先頭に移動されます。レポートの印刷時に改ページ文字が検出されると、その検出位置がページの最後ではない場合でも、次のページの一番上の印字位置までプリンタが移動します。NEWPAGEをNONEに設定した場合、レポートのページ間で空白行または改ページ文字は出力されません。
1ページの行数を設定するには、次のようにSETコマンドのPAGESIZE変数を使用します。
SET PAGESIZE number_of_lines
出力でタイトルを正しく中央揃えするために行サイズを小さくできます。また、幅の広い用紙に出力するために行サイズを大きくすることもできます。行の幅は、次のようにSETコマンドのLINESIZE変数を使用して変更できます。
SET LINESIZE number_of_characters
ACME WIDGET PERSONNEL REPORT
01-JAN-2001
FIRST LAST MONTHLY
DEPARTMENT_ID NAME NAME SALARY
------------- -------------------- ------------------------- --------
90 Steven King $24,000
90 Neena Kochhar $17,000
90 Lex De Haan $17,000
80 John Russell $14,000
80 Karen Partners $13,500
20 Michael Hartstein $13,000
6 rows selected.
この時点で、次のように入力して、PAGESIZE、NEWPAGEおよびLINESIZEをデフォルト値にリセットします。
SET PAGESIZE 14 SET NEWPAGE 1 SET LINESIZE 80
これらの変数の現在の設定値を表示するには、次のようにSHOWコマンドを使用します。
SHOW PAGESIZE SHOW NEWPAGE SHOW LINESIZE
SQL*PlusコマンドのSPOOLを使用して、問合せ結果をファイルに格納できます。また、ご使用のコンピュータのデフォルト・プリンタでの印刷もできます。
例7-25 ページ・サイズの設定
ページ・サイズを66行に設定し、各ページの始まりで画面をクリア(またはプリンタを改ページ)し、行サイズを70に設定するには、次のコマンドを入力します。
SET PAGESIZE 66 SET NEWPAGE 0 SET LINESIZE 70
この時点で、次のコマンドを入力し実行して、結果を表示します。
TTITLE CENTER 'ACME WIDGET PERSONNEL REPORT' SKIP 1 - CENTER '01-JAN-2001' SKIP 2
この時点で、次のように問合せを実行します。
COLUMN FIRST_NAME HEADING 'FIRST|NAME'; COLUMN LAST_NAME HEADING 'LAST|NAME'; COLUMN SALARY HEADING 'MONTHLY|SALARY' FORMAT $99,999; SELECT DEPARTMENT_ID, FIRST_NAME, LAST_NAME, SALARY FROM EMP_DETAILS_VIEW WHERE SALARY>12000;
7.4 問合せ結果の格納および印刷について
問合せ結果は、印刷する前にワード・プロセッサで編集する場合、あるいは書状、電子メールまたはその他の文書に挿入する場合、ファイルに格納します。
画面に表示したまま問合せ結果をファイルに格納するには、次の形式でSPOOLコマンドを入力します。
SPOOL file_name
ファイル名の後にピリオドおよび拡張子を指定しない場合、SPOOLを使用すると、ファイル名にデフォルトのファイル拡張子が追加され、そのファイルは出力ファイルとして認識されます。このデフォルトは、オペレーティング・システムによって異なりますが、ほとんどのホストではLSTまたはLISです。/dev/nullや/dev/stderrなどのシステム生成ファイルにスプールする場合、拡張子は追加されません。詳細は、ご使用のオペレーティング・システムのプラットフォーム固有のOracleのマニュアルを参照してください。
次の形式でSPOOLコマンドを入力してスプーリングをOFFにするまで、SQL*Plusでは情報がファイルにスプールされ続けます。
SPOOL OFF
7.4.1 フラット・ファイルの作成
異なるソフトウェア製品間でのデータの移動時に、フラット・ファイル(エスケープ文字、ヘッダーまたは余分な埋込み文字を含まないオペレーティング・システム・ファイル)を使用する必要がある場合があります。たとえば、Oracle Netを使用していない場合、Oracle9iからOracle Database 10gへデータを移行するには、SQL*Loaderで使用できるようにフラット・ファイルを作成する必要があります。
SQL*Plusでフラット・ファイルを作成するには、最初に次のSETコマンドを入力します。
SET NEWPAGE 0 SET SPACE 0 SET LINESIZE 80 SET PAGESIZE 0 SET ECHO OFF SET FEEDBACK OFF SET VERIFY OFF SET HEADING OFF SET MARKUP HTML OFF SPOOL OFF
これらのコマンドの入力後、前述のSPOOLコマンドを使用してフラット・ファイルを作成します。
SET COLSEPコマンドは、列に線を付ける場合に有効です。詳細は、「SET」コマンドを参照してください。
7.4.3 プリンタへの結果の出力
問合せ結果を印刷するには、前述のとおり、結果をファイルにスプールします。その後、SPOOL OFFではなく、次のようにコマンドを入力します。
SPOOL OUT
スプールが停止し、スプールされたファイルの内容がコンピュータの標準(デフォルト)プリンタにコピーされます。SPOOL OUTを使用すると、スプール・ファイルは印刷後に削除されません。
A C M E W I D G E T
EMPLOYEE REPORT PAGE: 1
DEPARTMENT LAST NAME MONTHLY SALARY
---------- ------------------------- --------------
20 Hartstein $13,000
********** --------------
sum $13,000
80 Russell $14,000
Partners $13,500
********** --------------
sum $27,500
90 King $24,000
Kochhar $17,000
De Haan $17,000
********** --------------
sum $58,000
--------------
sum $98,500
COMPANY CONFIDENTIAL
6 rows selected.
例7-26 プリンタへの問合せ結果の出力
最終レポートを生成し、結果をスプールして印刷するには、次のコマンドを含むスクリプトEMPRPTを作成します。
最初に、EDITを使用して、オペレーティング・システムのテキスト・エディタでスクリプトを作成します。
EDIT EMPRPT
次に、テキスト・エディタを使用して次のコマンドをファイルに入力します。
SPOOL TEMP CLEAR COLUMNS CLEAR BREAKS CLEAR COMPUTES COLUMN DEPARTMENT_ID HEADING DEPARTMENT COLUMN LAST_NAME HEADING 'LAST NAME' COLUMN SALARY HEADING 'MONTHLY SALARY' FORMAT $99,999 BREAK ON DEPARTMENT_ID SKIP 1 ON REPORT COMPUTE SUM OF SALARY ON DEPARTMENT_ID COMPUTE SUM OF SALARY ON REPORT SET PAGESIZE 24 SET NEWPAGE 0 SET LINESIZE 70 TTITLE CENTER 'A C M E W I D G E T' SKIP 2 - LEFT 'EMPLOYEE REPORT' RIGHT 'PAGE:' - FORMAT 999 SQL.PNO SKIP 2 BTITLE CENTER 'COMPANY CONFIDENTIAL' SELECT DEPARTMENT_ID, LAST_NAME, SALARY FROM EMP_DETAILS_VIEW WHERE SALARY>12000 ORDER BY DEPARTMENT_ID; SPOOL OFF
出力を画面に表示しない場合は、ファイルの始めにSET TERMOUT OFFを追加し、ファイルの終わりにSET TERMOUT ONを追加します。テキスト・エディタでファイルを保存し、終了します(自動的にSQL*Plusに戻ります)。この時点で、次のようにEMPRPTスクリプトを実行します。
@EMPRPT
画面には、次のように出力され(TERMOUTをOFFに設定した場合を除く)、ファイルTEMPにスプールされます。