Oracle Rdb7

2.6.6 文字列連結演算子

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

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

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

2.6.7 演算式と演算子

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

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

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

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

+       Addition
-       Subtraction
*       Multiplication
/       Division

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

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

カッコ内の値式
乗算と除算、左から右
加算と減算、左から右

演算式内で日時に関する変数と定数を使用できます。日時データ型の詳細は、第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: ビューでの演算式の使用

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

ビュー定義の選択式では、ビューの行が現在の給与の行に制限されています（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つの手順で変更しています。

給与締め日を本日に設定することによって、従業員の以前の給与を表すSALARY_HISTORY表の行がUPDATE文で変更されます。
以前のEMPLOYEE_IDおよびSALARY_END日付（先ほど本日に変更した日付）を使用して、INSERT文で新規行が格納されます。
次に示すように、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