ヘッダーをスキップ

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つ以上の値セットから導出される単一値です。

- 各行の値を評価するために、値式が使用されます。集計関数は、これらの値に対して機能します。

- SQLでファンクションを評価する場合、NULL値は含まれません。DISTINCTを指定すると、冗長値も含まれません。言語をSQL99に設定すると、NULLの削除によりSQLCODEまたはSQLSTATEに対して返される警告が発生します。SQLCODEおよびSQLSTATEの詳細は、付録Cを参照してください。

- 外部参照（第2.2.4.2項を参照）である列名を含む値式が引数としてファンクションに存在する場合は、値式に算術演算子を含めることはできません。（外部参照がファンクションの引数として意味があるのは、HAVING句の副問合せまたは選択リストの副問合せに外部参照が存在する場合のみです。）

- ファンクションをネストすることはできません。つまり、ファンクションの引数として使用する値式には、ファンクションを含めることはできません。

- キーワードALLはSUM、AVG、MAXおよびMINには影響を与えません。たとえば、MAX (ALL EMPLOYEE_ID)を指定することは、MAX (EMPLOYEE_ID)と指定することと同じです。

- VARIANCEおよびSTDDEVには1つの数値式を渡す必要があり、結果はDOUBLE PRECISION値で返されます。結果のデータ型を変更するには、CASTファンクションを使用します。

- VARIANCEおよびSTDDEVでは、平均の計算で自由度1を使用することを想定しているため、除数を（n-1）で指定します。これは、統計サンプルで大量の値が存在する場合は重要ではありません。ただし、小規模なサンプルには適していない可能性があるため、SET FLAGSオプションであるVARIANCE_DOF(0)を使用して現在のセッションのデフォルトを変更できます。有効な値は0と1のみです。

- キーワードALLおよびDISTINCTは、VAR_POP、VAR_SAMP、STDDEV_POPおよびSTDDEV_SAMPの統計関数では無効です。

すべての統計関数には、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 (*)では、結果表に存在する行数が計算されます。これは、引数に特定の列名を指定できない唯一のファンクションです。生成される値式のデータ型はINTEGERです。

- COUNT (value-expr)では、結果表に存在するvalue-exprのうちNULL以外の値の数が計算されます。value-exprは行ごとに評価され、値がNULLでない場合は、カウントが増分されるか値がカウントされます。生成される値のデータ型はINTEGERです。

- COUNT (DISTINCT value-expr)では、結果表に存在する指定されたvalue-exprの個別値の数が計算されます。COUNT DISTINCTファンクションでは、計算する数値から重複値が削除されます。value-exprは行ごとに評価され、値がNULLではなく前に表示された値と異なっている場合は、値がカウントされます。指定されたvalue-exprに含まれるNULL値はカウントされません。生成される値のデータ型はINTEGERです。

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では、標準偏差に対して次の統計関数が提供されています。

- STDDEV
  このファンクションでは標準偏差が計算されます。

- STDDEV_POP
  このファンクションでは、人口に対して標準偏差（平方偏差）が計算されます。自由度が0に固定されたSTDDEV、つまりデフォルト設定であるSET FLAGS 'VARIANCE_DOF(0)'に相当します。

- STDDEV_SAMP
  このファンクションでは、人口のサンプリングのサブセットに対して標準偏差（平方偏差）が計算されます。自由度が1に固定されたSTDDEV、つまりデフォルト設定であるSET FLAGS 'VARIANCE_DOF(1)'に相当します。人口のサンプリングを実行する場合、通常は自由度1を使用します。

### 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では、次の偏差統計関数が提供されています。

- VARIANCE
  値セットの偏差が計算されます。

- VAR_POP
  このファンクションでは人口の偏差が計算されます。自由度が0に固定されたVARIANCE、つまりデフォルト設定であるSET FLAGS 'VARIANCE_DOF(0)'に相当します。

- VAR_SAMP
  このファンクションでは、人口のサンプリングのサブセットに対して偏差が計算されます。自由度が1に固定されたVARIANCE、つまりSET FLAGS 'VARIANCE_DOF(1)'に相当します。人口のサンプリングを実行する場合、通常は自由度1を使用します。

例: 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つのクラスがあります。

- 外部ファンクション機能は、ルーチン定義、実行可能ファイル、および起動など、いくつかの別個の部分で構成されています。ルーチンの実行可能形式を指し示すファンクションを定義します。3GL言語で記述されたルーチンをコーディング、コンパイルおよびリンクします。
  外部ルーチンの作成方法の詳細は、「CREATE ROUTINE文」および「CREATE MODULE文」を参照してください。

- SQLファンクション機能は、ファンクション定義を含むモジュール定義で構成されています。このようなSQLファンクションには、任意のSQL手続き型言語機能（SET TRANSACTION、START TRANSACTION、COMMITおよびROLLBACKのようにトランザクション状態を変更するものを除く）を含めることができ、他のファンクションを起動したり、（CALL文を使用して）他のユーザー定義ルーチンをアクティブ化できるプロシージャをコールできます。
  SQLでのモジュールおよびファンクションの作成方法の詳細は、「CREATE MODULE文」を参照してください。

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

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

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

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

- COMPUTED BYまたはAUTOMATIC ASの値式句を使用している列

- 表制約、列制約またはドメイン制約内にあるCHECK句

- ビュー定義またはカーソル宣言にある選択式

- 複合文のset-assignment-statementの右側またはUPDATE文のSET句

- 選択リストまたはWHERE句

- ドメイン、列またはパラメータのDEFAULT値式句

## 2.6.5 データベース・キー

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

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

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

- 迅速なアクセス: データベース・キーを介した取得は直接的であり、索引付き検索または順次検索をすべて回避します。

- データのロックの削減: アクセスが直接的であるため、データベース・システムでは取得または更新された行のみがロックされます。

- 一意性: CREATE DATABASE文またはDECLARE ALIAS文で指定されるデータベース・キーの有効範囲内では、データベース・キーが一意であることが保証されます。これは、データベース・キーに基づくシングルトンSELECT文から複数行が返されることはなく、同じ行が返されるか、行が削除された場合はエラーが返されることを意味します。

データベース・キーの有効範囲とは、データベース・システムで、特定行のデータベース・キーがその行のみを指し示し、行が削除されてもデータベース・キーを再使用しないことが保証される期間のことです。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キーワードは、次の場合にのみ値式として使用できます。

- 選択リスト項目として使用する場合（第2.8.1項を参照）

      EXEC SQL DECLARE GET_DBKEYS CURSOR FOR
               SELECT DBKEY FROM EMPLOYEES;

- 別の値式をDBKEYキーワードと等価にする基本条件で使用する場合

      EXEC SQL SELECT * FROM EMPLOYEES WHERE DBKEY = :HOST_VAR;

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

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

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

- SQLでデータベース・キーが別のデータ型に変換されることはありませんが、比較およびパラメータへの移動を行うために正確な値が使用されます。Oracle Rdbでは、データベース・キーの値を取得するホスト言語パラメータを固定長文字列として宣言することをお薦めします。

- データベース・キーの長さは変化します。Oracle Rdbデータベースでは、データベース・キーの長さは、実表の場合は8バイト、ビューの場合は8にビューで名前が指定された表の数を掛けたバイト数になります。
  このため、予想される最大長のデータベース・キーを保持できる長さのパラメータを宣言する必要があります。パラメータがデータベース・キーより長い場合は、パラメータ値とデータベース・キーを比較するときにパラメータ値が切り捨てられます。パラメータの長さがデータベース・キーの保持に不十分な場合は、プログラムまたはモジュールの処理時にエラーが返されます。
  データベース・キーの長さを決定するには、次の例に示すようにRDB$RELATIONSシステム表からRDB$DBKEY_LENGTH列の値を取得します。

      SQL> SELECT  RDB$DBKEY_LENGTH FROM RDB$RELATIONS
      cont> WHERE RDB$RELATION_NAME = 'CURRENT_JOB';
       RDB$DBKEY_LENGTH
                     16
      1 row selected

- ファンクション、集計、GROUP BY句、HAVING句、または選択式のUNION句を指定して作成された結果表またはビューの行には、データベース・キーは存在しません。

- ソート（または投影の暗黙的ソート）では、dbkeyを使用してレコードを順次取得できるようにはdbkeyがソートされません。
  このように動作するのは、dbkeyが8 * nバイトの固定長テキスト文字列として処理されるためです。ここで、nに関連する表の数（ビューの場合は1以上になる可能性がある）を表しています。したがって、dbkeyをソートすると、デフォルトのASCII照合順番に応じてテキスト・バイトが配列されます。