ヘッダーをスキップ

Oracle Rdb SQLリファレンス・マニュアル リリース7.2 E06178-01 |
| 目次 | 索引

戻る | 次へ |

## 2.6.6 文字列連結演算子

文字列連結演算子（||）を使用すると、2つの文字値式を連結できます。連結値式は、いずれかの文字データ型に属している必要があります。また、連結値式のキャラクタ・セットは同一である必要があります。

例: 連結演算子（||）の使用

前述の例は、自動変換が有効になっていないと、互換性のないキャラクタ・セットを連結できないことを示しています。最初に、列を目的のキャラクタ・セットに変換する必要があります。TRANSLATEファンクションの詳細は、第2.6.2.25項を参照してください。

## 2.6.7 演算式と演算子

演算式は、1つ以上の数値式を算術演算子と組み合せることによって形成される値式です。演算式を文で使用すると、SQLでは式に関連付けられている数値が計算され、その数値が文の実行時に使用されます。

テキスト値がリテラルであるか、パラメータに格納されているか、または表列に格納されているかにかかわらず、テキスト値は演算式内で使用できません。

演算式のオペランドのいずれかがNULL値の場合、生成される値もNULLになります。

算術演算子とその機能を次に示します。

    +       Addition
    -       Subtraction
    *       Multiplication
    /       Division

算術演算子と値式を区切るために空白を使用する必要はありません。

カッコを使用すると、SQLで実行される演算操作の順序を制御できます。SQLでは、一般的な優先順位が使用されます。つまり、演算式は次の順序で評価されます。

1. カッコ内の値式

2. 乗算と除算、左から右

3. 加算と減算、左から右

演算式内で日時に関する変数と定数を使用できます。日時データ型の詳細は、第2.3.2項を参照してください。日時データ型をリテラルとして使用する方法の詳細は、第2.4.3項を参照してください。

日時演算には、次の制限が適用されます。

- 日付演算でDATE VMSデータ型は使用できません。CASTファンクション（CAST(VMS_COL AS TIMESTAMP(2))）を使用するか、またはDATE VMSドメインをDATE ANSIまたはTIMESTAMPに変更する必要があります。

- 減算演算では、時間隔修飾子を日時データ型で使用する必要があります。

- 一部の減算演算では、YEAR-MONTH期間またはDAY-TIME期間のいずれかを結果として生成できます。たとえば、タイムスタンプからタイムスタンプを減算したり、日付からタイムスタンプを減算する場合は、該当する修飾子を次のように指定する必要があります。

      SQL> CREATE TABLE ORDER_TABLE
      cont> (PART_NUM INT,
      cont> ORDER_LOGGED TIMESTAMP(2),
      cont> DELIVERY_DATE TIMESTAMP(2),
      cont> TIME_TO_DELIVER COMPUTED BY (DELIVERY_DATE - ORDER_LOGGED) DAY(2)
      cont> TO MINUTE, SLOW_DELIVERY COMPUTED BY EXTRACT(DAY FROM
      cont> (DELIVERY_DATE - ORDER_LOGGED) DAY(2)) - 30);

- TIMEに日数を加算したり、TIMEから日数を減算することはできません。結果が、許容されるTIMEの範囲を超えることになります。期間日時の列は、HOURSからSECONDのサブセットである必要があります。

- DATEに時間を加算したり、DATEから時間を減算することはできません。期間日時の列は、DAYSのみである必要があります。

日時と期間の値の有効な演算のリストは、表2-27を参照してください。

表2-27 日時と期間の値の有効な演算子
オペランド1 | 演算子 | オペランド2 | 結果のデータ型
--- | --- | --- | ---
日時 | -- | 日時 | 期間
日時 | +または-- | 期間 | 日時
期間 | + | 日時 | 日時
期間 | +または-- | 期間 | 期間
期間 | *または/ | 数値 | 期間
期間 | / | 数値 | 期間
| -- | 期間 | 期間

1この演算では期間が否定になります。

例: 演算式の使用

例1: ビューでの演算式の使用

演算式は、ビュー定義文で使用できます。この例では、健康保険の給与控除を計算するビューを定義しています。

- ビュー定義の選択式では、ビューの行が現在の給与の行に制限されています（SALARY_END IS NULL）。

- ビューの列には、従業員の名前および週ごとの控除の列が組み込まれています。この控除は、次に示すように各従業員の年間給与から演算式を使用して計算されます（週間給与の5%）。

    SQL> CREATE VIEW DEDUCT
    cont>  (LAST_NAME, FIRST_NAME, AMOUNT)
    cont>    AS SELECT
    cont>      E.LAST_NAME, E.FIRST_NAME,
    cont>      (S.SALARY_AMOUNT / 52) * 0.05
    cont>        FROM EMPLOYEES E, SALARY_HISTORY S
    cont>        WHERE E.EMPLOYEE_ID = S.EMPLOYEE_ID
    cont>        AND
    cont>        S.SALARY_END IS NULL;
    SQL> SELECT LAST_NAME, FIRST_NAME,
    cont> CAST(AMOUNT AS BIGINT(2)) FROM DEDUCT;
     LAST_NAME        FIRST_NAME
     Toliver          Alvin                         49.72
     Smith            Terry                         11.23
     Dietrich         Rick                          17.79
       .
       .
       .

例2: UPDATE文での演算式の使用

演算式は、値の格納に使用できます。この例では、従業員の給与を次の3つの手順で変更しています。

1. 給与締め日を本日に設定することによって、従業員の以前の給与を表すSALARY_HISTORY表の行がUPDATE文で変更されます。

2. 以前のEMPLOYEE_IDおよびSALARY_END日付（先ほど本日に変更した日付）を使用して、INSERT文で新規行が格納されます。

3. 次に示すように、INSERT文の演算式では、以前の給与を使用して新規の給与額が計算されます（OLD.SALARY_AMOUNT * 1.1）。

    SQL> ATTACH 'FILENAME mf_personnel';
    SQL> --
    SQL> -- Modify the salary data for employee
    SQL> -- with ID 164, adding an ending date:
    SQL> --
    SQL> UPDATE SALARY_HISTORY
    cont>        SET SALARY_END = CAST(CURRENT_DATE AS DATE VMS)
    cont>        WHERE
    cont>                EMPLOYEE_ID = '00164'
    cont>                AND
    cont>                SALARY_END IS NULL;
    1 row updated
    SQL> --
    SQL> -- Store a new salary by calculating a 10% raise:
    SQL> --
    SQL> INSERT INTO SALARY_HISTORY
    cont>                (EMPLOYEE_ID, SALARY_START, SALARY_AMOUNT)
    cont>        SELECT OLD.EMPLOYEE_ID,
    cont>                OLD.SALARY_END,
    cont>                (OLD.SALARY_AMOUNT * 1.1)
    cont>        FROM SALARY_HISTORY OLD
    cont>        WHERE
    cont>                OLD.EMPLOYEE_ID = '00164'
    cont>                AND
    cont>                OLD.SALARY_END = CAST(CURRENT_DATE AS DATE VMS);
    1 row inserted
    SQL> --
    SQL> -- Check the results.
    SQL> --
    SQL> SELECT  S.EMPLOYEE_ID,
    cont>        S.SALARY_START,
    cont>        S.SALARY_END,
    cont>        S.SALARY_AMOUNT
    cont> FROM    SALARY_HISTORY S
    cont> WHERE   S.EMPLOYEE_ID = '00164'
    cont> ORDER BY S.SALARY_END DESC ;
     EMPLOYEE_ID   SALARY_START   SALARY_END    SALARY_AMOUNT
     00164          2-Dec-1993    NULL             $56,883.20
     00164         14-Jan-1983     2-Dec-1993      $51,712.00
     00164         21-Sep-1981    14-Jan-1983      $50,000.00
     00164          2-Mar-1981    21-Sep-1981      $26,291.00
     00164          5-Jul-1980     2-Mar-1981      $26,291.00
    5 rows selected
    SQL> ROLLBACK;

例3: 日付演算式での月の使用

1993年1月31日に1か月を追加すると、追加したとおりに月が増えますが、月の有効な日付になるようにSQLによって日付がリセットされます。たとえば、次のように入力します。

    SQL> ATTACH 'FILENAME corporate_data';
    SQL> SELECT EMPLOYEE_ID, LAST_REVIEW
    cont> FROM ADMINISTRATION.PERSONNEL.JOB_HISTORY
    cont> WHERE EMPLOYEE_ID = '00164';
     EMPLOYEE_ID   LAST_REVIEW
     00164         NULL
     00164         NULL
    2 rows selected
    SQL> UPDATE ADMINISTRATION.PERSONNEL.JOB_HISTORY
    cont> SET LAST_REVIEW = DATE'1993-01-31' + INTERVAL'1' MONTH
    cont> WHERE EMPLOYEE_ID = '00164';
    2 rows updated

この場合、出力は次のようになります。

    SQL> SELECT EMPLOYEE_ID, LAST_REVIEW
    cont> FROM ADMINISTRATION.PERSONNEL.JOB_HISTORY
    cont> WHERE EMPLOYEE_ID = '00164';
     EMPLOYEE_ID   LAST_REVIEW
     00164         1993-02-28
     00164         1993-02-28
    2 rows selected

## 2.6.8 条件式

条件式は高度な値式で、条件式を使用すると、アプリケーションは式内で代替情報を返すことができます。表2-28は、Oracle RdbでサポートされているANSI/ISO SQL規格の条件式を示しています。

表2-28 条件式
式名 | 説明
--- | ---
ABS | ABSは、nの絶対値を返す。
NULLIF | NULLIFは、2つの値式が等しい場合にNULLを代入し、等しくない場合は最初の値を返す。
COALESCE | COALESCEは、一連の値式から最初のNULL以外の値を返すか、NULL以外の値がない場合はNULLを返す。
NVL | NVLは、一連の値式から最初のNULL以外の値を返すか、NULL以外の値がない場合はNULLを返す。NVLは、COALESCEのシノニム。
NVL2 | NVL2の最初の値式がNULLでない場合は、2番目の値式を返す。NVL2の最初の値式がNULLの場合は、3番目の値式を返す。
GREATEST | GREATESTは、最大のNULL以外の値を返す。
LEAST | LEASTは、最小のNULL以外の値を返す。
CASE | CASEは、式の結果を変更する。CASEでは、NULL値の生成または変換も実行できる。
DECODE | srch1からsrchnまでexprを比較して、一致を見つける。一致が見つかると、対応する結果をresnに返す。一致が見つからなかった場合、デフォルト値を返す（指定されている場合）。指定されていない場合はNULLを返す。

以降の項では、これらの式のSQL実装について説明します。

### 2.6.8.1 ABSファンクション

ABSファンクションでは、渡された値式がNULLに評価されるとNULLが返されます。結果のデータ型は、渡された値式と同じです。このデータ型では、TINYINT、SMALLINT、INTEGER、BIGINT、REAL、FLOAT、DOUBLE PRECISION、INTERVAL、DECIMAL、NUMERICおよびNUMBERの各データ型のスケール変更された値がサポートされます。

絶対値ファンクション（ABS）では、値式がNULLに評価されるとNULLが返されます。値式によってゼロよりも小さい値に評価された場合、この値は否定され、正の値が返されます。値がゼロ以上の場合は、変更されずに返されます。たとえば、ABS (-1)では値1が返されます。

ABS (a)は、CASE式と同じです。

        case
            when a < 0 then - a
            else a
        end

使用方法:

- SQL_FUNCTIONSスクリプトには、ストアド定義（プロシージャ、ファンクション、トリガー、ビューなど）用、またはABS外部ファンクション定義を現在使用しているコンパイルされたアプリケーション用のABS外部ファンクション定義が含まれています。ただし、ABSへの新規の参照では、今後は新規の組込み条件式が使用されます。

- 外部ファンクションを引き続き使用するアプリケーションでは、ABSファンクション名をデリミタで囲む必要があります。

      SQL> set quoting rules 'SQL92';
      SQL> select "ABS" (v) from T;

  デリミタ付きの名前によって、ファンクション定義が強制的に使用されます。

SQL_FUNCTIONSスクリプトの詳細は、付録Gを参照してください。

例: 日付の減算のINTERVAL結果でのABSファンクションの使用

    SQL> select
    cont>    ABS ((birthday - current_date) year(3))
    cont> from employees
    cont> order by employee_id
    cont> limit to 10 rows;

      054
      047
      047
      064
      068
      062
      044
      069
      050
      074
    10 rows selected

例: 統計関数でのABSの使用

    SQL> -- what is the average time in a job for each employee
    SQL> -- exclude anyone on there first job
    SQL> select
    cont>    employee_id,
    cont>    AVG (ABS (EXTRACT (MONTH FROM (job_start - job_end) month (4))))
    cont>           as "Average Job" edit using '--,---,--9.99" years"'
    cont> from JOB_HISTORY
    cont> where employee_id < '00200'
    cont> group by employee_id
    cont> having COUNT (*) > 1;
     EMPLOYEE_ID           Average Job
     00164                 14.00 years
     00165                 22.67 years
     00166                 20.00 years
     00167                 14.50 years
     00168                 26.33 years
     00169                 22.67 years
    ...etc...
     00197                 26.33 years
     00198                 37.00 years
     00199                 35.00 years
    30 rows selected
    %RDB-I-ELIM_NULL, null value eliminated in set function

### 2.6.8.2 COALESCE式およびNVL式

COALESCE式とNVL式では、一連の値式から最初のNULL以外の値が返されます。

SQLでは、NULL以外の値が返されるまでCOALESCE式またはNVL式の各値式が評価されます。COALESCE式またはNVL式で指定されたすべての列にNULL値が含まれる場合、NULLが返されます。

生成される式のデータ型は、リスト内のすべての値式が変換可能な共通のデータ型です。たとえば、COALESCE(SALARY_AMOUNT, ESTIMATED_BONUS, 1.23E+5)の結果は、少なくとも1つの引数が浮動小数点値であるため、DOUBLE PRECISIONになります。

次の例では、EMPLOYEES表のMIDDLE_INITIAL列に格納されたNULL値がハイフンに置換されます。

    SQL> SELECT FIRST_NAME, LAST_NAME, MIDDLE_INITIAL,
    cont> COALESCE(MIDDLE_INITIAL, '-')
    cont> FROM EMPLOYEES
    cont> WHERE LAST_NAME LIKE 'L%';
     FIRST_NAME   LAST_NAME        MIDDLE_INITIAL
     Jo Ann       Lapointe         C                C
     Hope         Lapointe         NULL             -
     Stan         Lasch            P                P
     Norman       Lasch            NULL             -
     Peter        Lengyel          A                A
     Peter        Lonergan         V                V
    6 rows selected

### 2.6.8.3 CASE式

式の結果を変更すると便利な場合があります。たとえば、WORK_STATUSという表列にデータ0、1および2が含まれており、それぞれ非アクティブ、フル・タイムおよびパート・タイムを意味しているとします。データベースに格納する場合、単一の文字の方が文字の定義よりも効率的です。ただし、単一の文字での定義は常に直観的に理解できるわけではありません。

データベースから導出した情報に基づくNULL値を生成したり、反対にNULL値をゼロ（0）などのより具体的な値に変換する場合もあります。CASE式を使用すると、これらの問題を簡単に解決できます。

CASE式には、次の2つタイプがあります。

- 単純: 2つの値式がそれぞれ等しいものと一致

- 検索: 副問合せなどの複合条件を使用可能

単純CASE式の例を次に示します。

    SQL> SELECT LAST_NAME, FIRST_NAME,
    cont>   CASE STATUS_CODE
    cont>     WHEN '1' THEN 'Full time'
    cont>     WHEN '2' THEN 'Part time'
    cont>     WHEN NULL THEN 'Unknown'
    cont>     ELSE 'Inactive'
    cont>   END
    cont> FROM EMPLOYEES;
     LAST_NAME        FIRST_NAME
     Smith            Terry        Part time
     O'Sullivan       Rick         Full time
       .
       .
       .
     Sarkisian        Dean         Part time
     Stornelli        James        Full time
     Hall             Lawrence     Full time
     Mistretta        Kathleen     Full time
     James            Eric         Inactive
     MacDonald        Johanna      Full time
     Dement           Alvin        Full time
     Blount           Peter        Full time
     Herbener         James        Full time
     Ames             Louie        Full time
    100 rows selected

SQLでは、CASEキーワードの後の1次値式に一致する最初のWHEN句が見つかると、THEN句が評価されます。一致する値が見つからない場合は、ELSE句が評価されます。ELSE句がない場合、NULL値が返されます。次に例を示します。

    SQL> SELECT PRODUCT_NAME,
    cont>   CASE
    cont>      WHEN QUANTITY <= 0 THEN 'On back order'
    cont>      WHEN QUANTITY > 0 THEN
    cont>         CAST(QUANTITY AS VARCHAR(10)) || ' in stock'
    cont>   END
    cont> FROM INVENTORY;
     PRODUCT_NAME
     Staples-boxes     20 in stock
     Staplers-each     3 in stock
     Tape-rolls        On back order
     Calendars-each    25 in stock
     Tape disp.-each   On back order
     Desk cleaner      NULL
    6 rows selected

検索CASE式の例を次に示します。

    SQL> SELECT PRODUCT_NAME,
    cont>   CASE
    cont>      WHEN QUANTITY <= 0 THEN 'On back order'
    cont>      WHEN QUANTITY > 0 THEN
    cont>         CAST(QUANTITY AS VARCHAR(10)) || ' in stock'
    cont>      ELSE                                          -- must be NULL
    cont>        'New Item - awaiting stock'
    cont>   END
    cont> FROM INVENTORY;
     PRODUCT_NAME
     Staples-boxes     20 in stock
     Staplers-each     3 in stock
     Tape-rolls        On back order
     Calendars-each    25 in stock
     Tape disp.-each   On back order
     Desk cleaner      New Item - awaiting stock
    6 rows selected

前述の例に示しているように、検索CASE式では各WHEN句で任意の式を使用できます。単純CASE式は、検索CASE式の簡略化された方法です。

単純CASE式および検索CASE式では、WHEN句の値式のデータ型が比較可能であり、THEN句の値式のデータ型が比較可能である必要があります。

CASE式のすべての副問合せが評価されます。この評価は、CASE式によって条件付けされた副問合せの結果であり、実際の評価ではありません。

副問合せで返されるのは最大で1つの行と列である必要がありますが、複数の行が返された場合は、次の例外が生成されます。

    %RDB-E-MULTIPLE_MATCH, record selection criteria should identify only one
    record; more than one record found

回避策として、次のいずれかの句を副問合せに追加します。

- LIMIT TO 1 ROW
  これにより、1行のみ返されるようになります。次に例を示します。

         .
         .
         .
      cont> WHEN A IS NOT NULL
      cont> THEN (SELECT A FROM T WHERE B = Y
      cont>       LIMIT TO 1 ROW)
         .
         .
         .

  WHEN条件では、この行が有効でない場合は無視されます。

- 副問合せ条件内でWHEN句ブールを複製します。
  次に例を示します。

      SQL> --
      SQL> -- Change the following syntax from
      SQL> --
         .
         .
         .
      cont> WHEN A IS NOT NULL
      cont> THEN (SELECT A FROM T WHERE B = Y)
         .
         .
         .
      SQL> --
      SQL> -- to include the Boolean inside the subquery
      SQL> --
         .
         .
         .
      cont> WHEN A IS NOT NULL
      cont> THEN (SELECT A FROM T WHERE B = Y AND A IS NOT NULL)
         .
         .
         .

  この例では、WHEN句がFALSEと評価されると、副問合せのWHERE条件もFALSEと評価されるため、行は返されません。

前述のいずれの場合も、正しい結果が問合せから返されます。

### 2.6.8.4 DECODEファンクション

DECODEファンクションでは、一致が見つかるまで、指定された各検索値に対して式が比較されます。一致が見つかると、対応する結果フィールドに結果が返されます。一致が見つからなかった場合、デフォルト値が返されます（指定されている場合）。指定されていない場合はNULLが返されます。

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

    SQL> SELECT employee_id, last_name, first_name,
    cont> DECODE (status_code, '1', 'Full time',
    cont>                      '2', 'Part time')
    cont> FROM employees
    cont> LIMIT TO 5 ROWS;
     EMPLOYEE_ID   LAST_NAME       FIRST_NAME
     00165         Smith           Terry        Part time
     00190         O'Sullivan      Rick         Full time
     00187         Lasch           Stan         Full time
     00169         Gray            Susan        Full time
     00176         Hastings        Norman       Full time
    5 rows selected