ヘッダーをスキップ
Oracle Rdb SQLリファレンス・マニュアル
リリース7.2
E06178-01
  目次
目次
索引
索引

戻る
戻る
 
次へ
次へ
 

2.6.2.28 TRUNCファンクション

TRUNCファンクションでは、引数として2つの数値式(n、m)が受け入れられます。最初の値式nが小数点以下m桁で切り捨てられた値が結果となります。値式mに負の値を指定すると、小数点以上の桁で切り捨てることができます。mのデータ型は、スケールなしの数値(TINYINT、SMALLINT、INTEGERまたはBIGINT)である必要があります。省略すると、mはデフォルトでゼロになります。

次に例を示します。


SQL> select avg (salary_amount)
cont> from salary_history
cont> where employee_id = '00164';

  3.857350000000000E+004
1 row selected

SQL> select trunc (avg (salary_amount)) as SAL edit using 'sz(9).99'
cont> from salary_history
cont> where employee_id = '00164';
           SAL
      38574.00
1 row selected

2.6.2.29 UPPERファンクション

UPPERファンクションでは、値式に含まれるすべての小文字が大文字に変換されます。このファンクションは、データベース内の値式で整合性を保持する場合に便利です。

値式の結果がNULLの場合は、NULL値が返されます。

例: UPPERファンクションの使用

UPPERファンクションを使用して、サンプルの人事データベースからDEPARTMENT_NAMEに含まれる小文字を大文字に変換します。


SQL> SELECT DEPARTMENT_NAME, UPPER(DEPARTMENT_NAME)
cont>       FROM DEPARTMENTS
cont>       LIMIT TO 3 ROWS;
 DEPARTMENT_NAME
 Corporate Administration         CORPORATE ADMINISTRATION
 Electronics Engineering          ELECTRONICS ENGINEERING
 Large Systems Engineering        LARGE SYSTEMS ENGINEERING
3 rows selected

UPPERファンクションを使用する場合、値式のキャラクタ・セットのルールに従って大文字に変換されます。たとえば、値式のキャラクタ・セットが簡体とASCIIの場合、ASCII文字のみが大文字に変換されます。簡体文字は変換されません。

2.6.2.30 USERファンクション

USERファンクションでは、リクエストに対して現在アクティブなユーザー名を指定します。このファンクションは(CURRENT_USER)ファンクションのシノニムです。定義者権限のストアド・プロシージャでは、定義者のユーザー名が返されます。他のすべてのリクエストでは、コール側ルーチンの現在のユーザー名が返され、コール側ルーチンが存在しない場合は現在のセッション・ユーザー名が返されます。データ型はCHAR(31)になります。

例: USERファンクションの使用

例1: 複数のユーザーが売上の記録に使用するアプリケーションについて考えてみます。表の列にUSERを割り当てることによって、アプリケーションで営業担当者が識別されます。


EXEC SQL
INSERT INTO SALES_LOG
        (DATE, AMOUNT, SALES_PERSON)
        VALUES
        (:SALE_DATE, :SALE_AMOUNT, USER)
END-EXEC

例2: これにより、営業担当者は自分の売上の記録を簡単に取得できます。


SQL> SELECT * FROM SALES_LOG
cont>   WHERE SALES_PERSON = USER;
 DATE                           AMOUNT   SALESPERSON
  5-DEC-1988 00:00:00.00           578   FIELDMAN
1 row selected
SQL>

2.6.3 集計関数

集計関数では、結果表に存在する行の集まりに対して単一値が計算されます。集計関数は統計関数とも呼ばれます。

表2-26では、これらの関数と計算値について説明します。

表2-26 集計関数
ファンクション名 計算値
COUNT 結果表内の行数または列値
SUM 値セットの合計
STDDEV (標準偏差)平方偏差であり、ソース式と同じ単位で表す。
AVG 値セットの平均
MAX 値セットの最大値
MIN 値セットの最小値
VARIANCE 平均値からの変動の統計的なメジャー

通常、集計関数には次の注意事項が適用されます。集計関数は、1つ以上の値セットから導出される単一値です。

すべての統計関数には、FILTER句が用意されています。この句を使用すると、COUNTファンクション、MAXファンクション、MINファンクション、SUMファンクション、AVGファンクション、STDDEVファンクションおよびVARIANCEファンクションに含まれる値を制限できます。

統計関数からデータを削除して、生成されたレポートでデータを1回のパスで処理できるようにするには、FILTERを使用できます。次の例は、FILTER句の適用方法を示しています。


SQL> select
cont>   max (salary_amount) filter (where salary_end is null),
cont>   max (salary_amount) filter (where salary_end is not null),
cont>   min (distinct salary_amount) filter (where salary_end = salary_start),
cont>   min (distinct salary_amount) filter (where salary_end > salary_start)
cont> from
cont>   salary_history
cont> where
cont>   employee_id = '00164'
cont> group by
cont>   employee_id;

      51712.00        50000.00            NULL        26291.00
1 row selected
SQL>

以降の項では、集計関数について詳細に説明します。

2.6.3.1 COUNTファンクション

COUNTファンクションには、次の3つの形式があります。

COUNTファンクションを適用する結果表に値が存在しない場合は、ゼロが返されます。

例: COUNTファンクションの使用

COUNT (*)ファンクションを使用して、人事データベースから従業員数を求めます。COUNT (DISTINCT)ファンクションを使用して、従業員が住む異なる州を求めます。


SQL> SELECT COUNT (*) FROM EMPLOYEES;

         100
1 row selected
SQL> SELECT COUNT (DISTINCT STATE) FROM EMPLOYEES;

           3
1 row selected

2.6.3.2 SUMファンクション

SUMファンクションでは、引数の値式で指定された値の合計が計算されます。SUMファンクションを適用する結果表に行が存在しない場合は、NULL値が返されます。

SUMファンクションでは、数値またはINTERVALデータ型の値を参照する必要があります。結果の格納に十分な大きさの同じ汎用データ型(固定小数点または浮動小数点)の値が返されます。

言語がANSI/ISO SQL規格に設定されており、いずれかの値がNULLの場合は、警告メッセージが返されます。

例: SUMファンクションの使用

SUMファンクションを使用して、会社の年間給与総額を計算します。SUMファンクションでは、この計算の結果表として使用するビューCURRENT_SALARY内の列SALARY_AMOUNTに存在するNULL以外のすべての値が使用されます。


SQL> SELECT SUM(SALARY_AMOUNT) FROM CURRENT_SALARY;

             3192279.00
1 row selected

SUMファンクションが適用される$32,000を超える給与が存在しないため、次の選択からはNULLが返されます。


SQL> SELECT SUM(SALARY_AMOUNT) FROM CURRENT_SALARY
cont> WHERE SALARY_AMOUNT > '32000';

             NULL
1 row selected

2.6.3.3 AVGファンクション

AVGファンクションでは、引数の値式で指定された値の平均が計算されます。AVGファンクションを適用する結果表に行が存在しない場合は、NULL値が返されます。

AVGファンクションでは、数値またはINTERVALデータ型の値を参照する必要があります。返される値は、数式の場合は浮動小数点データ型、またはINTERVALデータ型になります。

値がNULLの場合は行が欠落しているとみなされ、言語がANSI/ISO SQL規格に設定されている場合は警告メッセージが返されます。

例: AVGファンクションの使用

AVGファンクションを使用して、現在の全従業員の平均給与を求めます。


SQL> SELECT AVG(SALARY_AMOUNT) FROM CURRENT_SALARY;

  3.192279000000000E+004
1 row selected

2.6.3.4 MAXファンクション

MAXファンクションでは、引数の値式で指定された値の最大値が計算されます。MAXファンクションを適用する結果表に値が存在しない場合は、NULL値が返されます。

MAXファンクションでは、LIST OF BYTE VARYINGを除くすべてのデータ型について、引数の値と同じデータ型の値が返されます。

例: MAXファンクションの使用

MAXファンクションを使用して、従業員に支払われる最大給与を求めます。


SQL> SELECT MAX(SALARY_AMOUNT) FROM CURRENT_SALARY;

      93340.00
1 row selected

2.6.3.5 MINファンクション

MINファンクションでは、引数の値式で指定された値の最小値が計算されます。MINファンクションを適用する結果表に値が存在しない場合は、NULL値が返されます。

MINファンクションでは、LIST OF BYTE VARYINGを除くすべてのデータ型について、引数の列と同じデータ型の値が返されます。

例: MINファンクションの使用

MINファンクションを使用して、従業員に支払われる最小給与を求めます。


SQL> SELECT MIN(SALARY_AMOUNT) FROM CURRENT_SALARY;

       8687.00
1 row selected

2.6.3.6 STDDEVファンクション

STDDEV(標準偏差)ファンクションでは、平方偏差が計算され、ソース式と同じ単位で表されます。

Oracle Rdbでは、標準偏差に対して次の統計関数が提供されています。

2.6.3.7 VARIANCEファンクション

VARIANCEファンクションでは、平均値からの変動が計算されます。

偏差は次の統計式から計算されます。

variance = <sum symbol><limits symbol>i = 1n x _i2 - 1 n
[ <sum symbol><limits symbol>i = 1n x _i ]2 n - 1

ここで

xiはxのいずれかの要素です。

nはセットxに含まれる要素数です。nが1の場合、偏差は0と定義されます。

Oracle Rdbでは、次の偏差統計関数が提供されています。

例: STDDEVファンクションおよびVARIANCEファンクションの使用

次の例は、数値式でVARIANCEおよびSTDDEVを使用した場合の結果を示しています。


SQL> SELECT VARIANCE (ALL salary_amount)
cont> FROM salary_history WHERE salary_end IS NULL;

  4.396628811574747E+008
1 row selected
SQL> SELECT VARIANCE (DISTINCT salary_amount),
cont>       COUNT (DISTINCT salary_amount)
cont> FROM salary_history WHERE salary_end IS NULL;

  4.399944817652031E+008            99
1 row selected
SQL> SELECT STDDEV (DISTINCT salary_amount), COUNT (DISTINCT salary_amount)
cont> FROM salary_history WHERE salary_end IS NULL;

  2.097604542722968E+004            99
1 row selected
SQL> SELECT STDDEV (salary_amount) AS STDDEV
EDIT USING '$$$,$$$,$$9.99'
cont> FROM salary_history WHERE salary_end IS NULL;
         STDDEV
     $20,968.14
1 row selected

2.6.4 ユーザー定義ファンクション

ユーザー定義ファンクションを使用すると、3GLホスト言語(CまたはSQLモジュール言語など)またはSQL手続き型言語で記述されたサブプログラムを実行できます。ユーザー定義ファンクションには、次の2つのクラスがあります。

ファンクション定義は、表やビューなどの他のスキーマ・オブジェクトと同様に、データベースに存在します。ユーザー定義ファンクションの名前を表示するには、SHOW FUNCTION文を使用します。詳細および例は、「SHOW文」を参照してください。

最後に、SQLインタフェースによって自動的に起動するには、SQL文の中でルーチンを参照します。

次の図は、ユーザー定義ファンクションを起動する方法を示しています。


ユーザー定義のファンクションは、値式を指定できる任意の場所から起動できます。外部ファンクションを起動できる場所には、次のものがあります。

2.6.5 データベース・キー

データベース・キー(dbkey)とは、データベース内の特定の表の行を指す内部ポインタのことです。アプリケーション・プログラムでは、SQL文でDBKEYキーワードまたはROWIDキーワードを使用して、表の行のデータベース・キーを参照できます。ROWIDキーワードは、DBKEYキーワードのシノニムです。

データベース・キーは値式とみなされます。そのため、選択式の一部として指定できます。

データベース・キーを指定して行を取得するSQL文には、次の利点があります。

データベース・キーの有効範囲とは、データベース・システムで、特定行のデータベース・キーがその行のみを指し示し、行が削除されてもデータベース・キーを再使用しないことが保証される期間のことです。ATTACH文、CREATE DATABASE文、DECLARE ALIAS文およびIMPORT文で、データベース・キーの有効範囲をトランザクション期間(デフォルト)またはデータベースへのアタッチ期間として指定できます。

トランザクション全体でデータベース・キーを使用する予定のアプリケーションでは、DBKEY SCOPE IS ATTACH句を使用してデータベースを宣言する必要があります。これにより、多くのトランザクションで特定の表の行に対してデータベース・キーをプログラムで使用できるようになります。別のユーザーが行を削除すると、新しく挿入した行に対してデータベース・キーを再使用できなくなるため、データベース・キーは引き続き有効になります。DBKEYをさらに参照すると、エラーが生成されます。

DBKEYを使用する場合、すべてのユーザーがDBKEY SCOPE IS TRANSACTIONを使用してページが更新されるまで、ページの一部の領域は再利用されません。この領域のオンライン再利用を可能にするRMU RECLAIMコマンドも参照してください。

注意

Oracle Rdbでは、DBKEY SCOPE IS ATTACHではなく、DBKEY SCOPE IS TRANSACTIONを使用してデータベース・ページ上の領域を再利用することをお薦めします。

アプリケーションでデータベース・キーを使用するには、次の手順を実行します。

  1. DBKEY SCOPE IS ATTACH句を指定するデータベースを宣言します。

  2. 読取り専用トランザクションを開始します。

  3. 目的の行の列およびデータベース・キーで構成される、結果表を指定するカーソルを宣言します。

  4. カーソルをオープンします。

  5. 結果表の行をフェッチし、それをパラメータに格納します。

  6. トランザクションをコミットして、データベース・リソースのロックを解除します。

  7. パラメータに格納された表の行を表示し、行の値を変更する機会をアプリケーション・ユーザーに提供します。

  8. アプリケーション・ユーザーが行の値を変更する場合は、値の変更をアプリケーション・ユーザーに許可する前に、別のセットのパラメータに行をコピーします。このコピーは、データベースから取得された元の行であることを表します。

  9. 読取り/書込みトランザクションを開始し、認可識別子を指定するSELECT INTO文を使用して行を取得し、行をロックして他のユーザーが変更できないようにします。取得した行を、前の手順で作成したコピーと比較することによって、(読取り専用トランザクションが開始してから)他のユーザーが行を変更していないことを確認します。他のユーザーが行を変更した場合は、変更された値がアプリケーションで上書きされない可能性があります。

  10. データベース内の行が読取り専用トランザクションで取得された元の行と一致する場合は、ユーザーの値とデータベース値を比較することによってアプリケーション・ユーザーが変更を加えたことを確認します。

  11. アプリケーション・ユーザーが行を変更した場合は、データベース・キーを使用してUPDATE文で行を指定します。

  12. トランザクションをコミットします。

  13. パラメータに格納された別の表の行を表示し、処理を繰り返します。

SQLでは、値式が有効なすべてのコンテキストでDBKEYキーワードを使用できるわけではありません。対話型SQLでは、DBKEYリテラルは無効です。DBKEYキーワードは、次の場合にのみ値式として使用できます。

また、INSERT文のRETURNING DBKEY句は、挿入された行に対してデータベース・キーを返すようSQLに指示します。


EXEC SQL INSERT INTO TEMP VALUES (:REAL_VAR)
        RETURNING DBKEY INTO :DBKEY_VAR;

データベース・キーを使用する場合には、次の制限に注意してください。