Oracle Rdb7

2.6.2.17 ROUNDファンクション

ROUNDファンクションでは、引数として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 round (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.19 SESSION_USERファンクション

SESSION_USERファンクションでは、現在アクティブなセッション・ユーザー名が返されます。

セッション・ユーザー名が返されない場合は、システム・ユーザー名が返されます。データ型はCHAR(31)になります。

2.6.2.20 SIZEOFファンクション

SIZEOF（またはVSIZE）ファンクションでは、データ型の値式の最大長がオクテット単位で計算されます。値式の長さまたは記憶域幅が返されます。値式の結果が複数バイト・キャラクタ・セットのテキスト文字列になる場合は、OCTET_LENGTHの場合と同様に、文字数ではなく8ビット単位（オクテット）の数値が返されます。SIZEOFのかわりにVSIZEを使用できます。

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

例1: SIZEOFファンクションを使用した、CHAR列の最大長の計算。

この例では、サンプルの人事データベースを使用します。列LAST_NAMEはCHAR（14）として定義されており、SQLで列に空白が埋め込まれる固定長データ型です。次の文では、SIZEOFのかわりにOCTET_LENGTHを使用した場合と同じ値が返されます。

SQL> SELECT SIZEOF (LAST_NAME), LAST_NAME
cont>       FROM EMPLOYEES
cont>       LIMIT TO 3 ROWS;
               LAST_NAME
          14   Ames
          14   Andriola
          14   Babbin
3 rows selected

例2: SIZEOFファンクションを使用した、VARCHAR列の最大長の計算。

列CANDIDATE_STATUSはVARCHAR（255）として定義されており、SQLで列に空白が埋め込まれない可変長データ型です。SIZEOFでは式の最大サイズ（記憶域幅）が返されるため、すべての行に対して同じ値（255）が返されます。行ごとにデータの実際のサイズを返すOCTET_LENGTHとは対照的です。

SQL> SELECT SIZEOF (CANDIDATE_STATUS) FROM CANDIDATES;

         255
         255
         255
3 rows selected

例3: 非テキスト式に対するSIZEOFファンクションの使用。

この例では、サンプルの人事データベースを使用します。

最初の問合せの列BIRTHDAYは、8バイトのデータ型であるDATE VMSとして定義されています。データは表示用に9文字以上で書式設定されますが、基底データ型の記憶域幅は8バイトです。数値リテラル1は、4バイトのINTEGERを使用して内部的に格納されます。

2つ目の問合せの列BUDGET_ACTUALは、4バイトのデータ型であるINTEGERです。選択した行のBUDGET_ACTUALの値はすべてNULLですが、SIZEOFから列の記憶域幅が返されます。数値リテラル99999999999.1は、8バイトのデータ型であるBIGINT(1)として内部的に格納されます。

SQL> SELECT BIRTHDAY, SIZEOF(BIRTHDAY), 1, SIZEOF(1),
cont> FROM EMPLOYEES LIMIT TO 3 ROWS;
 BIRTHDAY
 28-Mar-1947             8   1                       4
 15-May-1954             8   1                       4
 20-Mar-1954             8   1                       4
3 rows selected
SQL> --
SQL> SELECT BUDGET_ACTUAL, SIZEOF(BUDGET_ACTUAL),
cont> 99999999999.1, SIZEOF(99999999999.1)
cont> FROM DEPARTMENTS LIMIT TO 3 ROWS;
 BUDGET_ACTUAL
          NULL             4   99999999999.1                     8
          NULL             4   99999999999.1                     8
          NULL             4   99999999999.1                     8
3 rows selected

2.6.2.21 SUBSTRINGファンクション

サブストリングでは、文字値式の一部が返されます。サブストリングのデータ型は、CHAR、VARCHAR、LONG VARCHAR、NCHARまたはNCHAR VARYINGである必要があります。

サブストリングを指定するには、値式とFROMキーワードの後に値式の開始位置を指定する必要があります。（文字列の最初の文字は位置1になります。）オプションとして、FROM句の後にFOR句を追加し、開始位置に続く値式の長さを指定できます。

開始位置および文字列長の値には、数値式を使用できます。デフォルトでは、開始位置および文字列長をオクテットで指定することが前提となります。SET DIALECT文またはSET CHARACTER LENGTH文、またはSQLモジュール言語ヘッダーのDIALECT句またはCHARACTER LENGTH句、およびDECLARE MODULE文を使用して、長さの値がオクテットか文字かを指定できます。

指定した長さが文字列長を超えていると、文字列内の有効な文字のみが返され、返されるサブストリングは最後の有効な文字で終了します。

サブストリングのオペランドのいずれかがNULL値の場合、生成される値もNULLになります。

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

次の例では、SELECT文のWHERE句のサブストリングを使用します。

SELECT文の条件の1つは、位置9から開始する4文字がサブストリング機能を使用して抽出される文字列Mathと等しいことです。

SQL> SELECT * FROM DEGREES
cont> WHERE SUBSTRING(DEGREE_FIELD FROM 9 FOR 4) = 'Math'
cont> AND YEAR_GIVEN > 1980;
 EMPLOYEE_ID   COLLEGE_CODE   YEAR_GIVEN   DEGREE   DEGREE_FIELD
 00167         CALT                 1982   MA       Applied Math
 00168         CALT                 1983   PhD      Applied Math
 00169         MIT                  1981   PhD      Applied Math
 00171         QUIN                 1982   MA       Applied Math
 00176         USCA                 1982   MA       Applied Math
 00212         PRDU                 1983   MA       Applied Math
 00220         DREW                 1982   MA       Applied Math
 00227         PRDU                 1981   MA       Applied Math
 00234         CALT                 1981   PhD      Applied Math
 00242         PRDU                 1982   PhD      Applied Math
 00243         HVDU                 1981   MA       Applied Math
 00374         STAN                 1982   MA       Applied Math
 00405         MIT                  1982   PhD      Applied Math
 00415         MIT                  1982   PhD      Applied Math
 00418         CALT                 1982   PhD      Applied Math
15 rows selected

サブストリングを等号（=）条件演算子とともに使用すると、大/小文字が区別されます。

2.6.2.22 SYSDATEファンクション

SYSDATEファンクションでは、現在の日時が返されます。引数は不要です。

SYSDATEはCURRENT_TIMESTAMPのシノニムです。CURRENT_TIMESTAMPの場合と同様に、SYSDATEから返される結果は、次の例に示すようにSET DEFAULT DATE FORMAT文の設定の影響を受けます。

SQL> SET DEFAULT DATE FORMAT 'SQL99'
SQL> SELECT SYSDATE, CURRENT_TIMESTAMP
cont>  FROM RDB$DATABASE;

 1995-08-21 15:21:05.29   1995-08-21 15:21:05.29
1 row selected
SQL> SET DEFAULT DATE FORMAT 'VMS'
SQL> SELECT SYSDATE, CURRENT_TIMESTAMP
cont>  FROM RDB$DATABASE;

 21-AUG-1995 15:21:24.83   21-AUG-1995 15:21:24.83
1 row selected

2.6.2.24 SYSTEM_USERファンクション

SYSTEM_USERファンクションでは、データベース・アタッチ時のプロセスのユーザー名が返されます。

USER句およびUSING句でユーザー名とパスワードを指定してデータベースにアタッチする場合は、指定したユーザー名が返されます。

データ型はCHAR(31)になります。

2.6.2.25 TRANSLATEファンクション

SQLでは、カンマ区切りの引数リストを使用する代替のTRANSLATEファンクションが提供されています。

TRANSLATE ( <sourcestring>, <fromstring>, <tostring> )

TRANSLATEファンクションのこの形式では、2つの変換文字列を使用して特定の文字の変換を定義します。<fromstring>に表示されない<sourcestring>の文字は置換されません。<fromstring>に表示されるソース文字列の文字は、<tostring>の対応する位置にある文字と置換されます。<fromstring>が<tostring>より長くなる場合、一致した文字は結果から削除されます。

引数<sourcestring>、<fromstring>および<tostring>のいずれかがNULLの場合、TRANSLATEファンクションの結果はNULLになります。

結果のデータ型は、長さとキャラクタ・セットが<sourcestring>と同じであるVARCHAR文字列になります。

例

例1: 文字の削除

この例では、名前から一重引用符を削除します。たとえば、O'HaraはOHaraとなります。通常、これはソート中に行われ、一重引用符によって名前が個別にグループ化されることを回避し、かわりにOg（Ogdanなど）とOi（Oilerなど）で始まる名前の間に配列されます。

SQL> select last_name
cont> from EMPLOYEES
cont> order by TRANSLATE (last_name, '"''', '"');

注意

Oracle RDBMSでは空の文字列がNULLとみなされるため、変換文字列に余分な"文字が追加され、結果がNULLになることが回避されます。これはOracle Rdbには不要です。ただし、ORACLE言語を使用する場合は、このようなOracleセマンティクスが可能です。

例2: 文字の書式設定

数値は先行空白とともに表示するのが一般的ですが、アプリケーションの中には先行ゼロを必要とするものもあります。次の例では、郵便番号がINTEGERとして格納され、先行ゼロを含む5桁で表示する必要があることを想定しています。

SQL> select TRANSLATE (CAST(postal_code as CHAR(5)), ' ', '0')
cont> from EMPLOYEES;

例3: 文字のマスク

TRANSLATEを使用すると、出力に表示しない文字をマスクできます。たとえば、ライセンス番号を表示する場合、すべての文字をXにおよびすべての数値を9に変換する必要があります。

SQL> select TRANSLATE ('2KRW229',
cont>       '01234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ',
cont>       '99999999999XXXXXXXXXXXXXXXXXXXXXXXXXX')
cont> from ...;
 9XXX999

例4: ソート用のテキスト変換

姓には、O'SullivanやD'Amicoの一重引用符のように特殊な記号文字が含まれていることがあります。このPERSONNELデータベースの例で考えてみます。

SQL> select last_name
cont>  from employees
cont>  where last_name starting with 'D'
cont>  order by last_name;
 LAST_NAME
 D'Amico
 Dallas
 Danzig
 Dement
 Dement
 Dietrich
 Dietrich
7 rows selected
SQL>

引用符記号が原因で、多くのアプリケーション（電話帳など）で予期する位置より前に名前がソートされることが示されています。

この例では、名前から一重引用符記号を削除し、姓を小文字に変換して、同様の名前内で引用符記号なしで名前をソートします。ただし、元の名前は列に格納されたとおりに表示されます。

SQL> select last_name
cont>  from employees
cont>  where last_name starting with 'D'
cont>  order by translate (last_name,
cont>                      'ABCDEFGHIJKLMNOPQRSTUVWXYZ''',
cont>                      'abcdefghijklmnopqrstuvwxyz');
 LAST_NAME
 Dallas
 D'Amico
 Danzig
 Dement
 Dement
 Dietrich
 Dietrich
7 rows selected
SQL>

TRANSLATEの最初の文字列引数には文字のターゲット・セットが含まれ、名前の文字がこのセットに一致しないと、変更されずに結果に書き込まれます。たとえば、末尾の空白は単に結果にコピーされます。

2つ目の文字列引数には、変換後の文字が含まれます。大文字は、相当する小文字に変換されます。一重引用符が一致すると、それに対する変換後の文字がないため結果から削除されます。

これらのルールを適用すると、D'Amicoがdamicoに変換され、dallasとdanzigの間にソートされます。

2.6.2.26 TRANSLATE USINGファンクション

TRANSLATEファンクションでは、文字値式が1つのキャラクタ・セットから互換性のある別のキャラクタ・セットに変換されます。

char-value-expr内の文字は、変換名で示されるキャラクタ・セットに1文字ずつ変換されます。文字に対して直接変換が存在する場合は、変換キャラクタ・セットの相当する文字で置換されます。文字に対して直接変換が存在しない場合は、TRANSLATEファンクションを使用した例に示すように、変換キャラクタ・セットの空白文字で置換されます。

たとえば、漢字キャラクタ・セットには、漢字、カタカナ、ASCII文字、および2オクテットでエンコードされたASCII文字であるローマ字が含まれています。RDB$KATAKANA変換名を使用して漢字char-value-exprを変換すると、1オクテットのカタカナ・キャラクタ・セットに相当する文字が存在する2オクテットの漢字が変換されます。他の文字は、カタカナの空白文字で置換されます。

表2-25は、各キャラクタ・セットの変換名、および文字の特定のタイプがSQLによってどのキャラクタ・セットに変換されるかを示しています。

**表2-25 変換名および可能な変換**
char-translation
変換名	char-value-expression	結果
RDB$DEC_MCS	MCS	MCS
	ASCII	ASCII¹
RDB$KANJI	漢字	漢字
	ASCII	漢字（ローマ字）
	カタカナ	漢字（カタカナ）
RDB$DEC_KANJI	漢字	漢字
	ASCII	ASCII¹
	カタカナ	半角カタカナ
RDB$HANZI	簡体	簡体
RDB$DEC_HANZI	簡体	簡体
	ASCII	ASCII¹
RDB$KOREAN	韓国語	韓国語
RDB$DEC_KOREAN	韓国語	韓国語
	ASCII	ASCII¹
RDB$HANYU	繁体	繁体
RDB$DEC_SICGCC	繁体	繁体
	ASCII	ASCII¹
RDB$DEC_HANYU	繁体	繁体
	ASCII	ASCII¹
RDB$KATAKANA	カタカナ	カタカナ
	漢字（カタカナ）	カタカナ
	ASCII	ASCII¹
RDB$ISOLATINARABIC	アラビア文字	アラビア文字
	ASCII	ASCII¹
RDB$ISOLATIN1	拡張西ヨーロッパ言語文字 ASCII	ASCII¹ 拡張西ヨーロッパ言語文字
RDB$ISOLATIN9	拡張西ヨーロッパ言語文字 ASCII	ASCII¹ 拡張西ヨーロッパ言語文字
RDB$ISOLATINCYRILLIC	キリル文字	キリル文字
	ASCII	ASCII¹
RDB$ISOLATINGREEK	ギリシャ文字	ギリシャ文字
	ASCII	ASCII¹
RDB$ISOLATINHEBREW	ヘブライ文字	ヘブライ文字
	ASCII	ASCII¹
RDB$DEVANAGARI	デーバナーガリー文字	デーバナーガリー文字
	ASCII	ASCII¹
RDB$SHIFT_JIS	漢字	Shift_JIS
	ASCII	Shift_JIS（ローマ字）
	カタカナ	Shift_JIS（カタカナ）
RDB$HEX	すべての文字	16進の相当文字
RDB$UNICODE	すべての文字	Unicode
RDB$UTF8	すべての文字	UTF8
RDB$WIN_*	同名のRDB$ISOLATIN*と同じ
RDB$DOS_LATIN1	RDN$ISOLATIN1と同じ
RDN$DOS_LATINUS	RDB$ISOLATIN1と同じ
RDB$GB18030	簡体	簡体
	ASCII	ASCII
RDB$AL24UTFFSS	すべての文字	UTF-8 Unicode標準1.1

¹多くのキャラクタ・セットにはASCII文字が含まれています。ソース・キャラクタ・セットのASCII文字は、ターゲット・キャラクタ・セットのASCII文字に変換されます。

ソース文字列の文字がターゲット・キャラクタ・セットと互換性がない場合、その文字には空白文字が代用されます。

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

TRANSLATEファンクションを使用して、DEC_MCS列をENGLISHからKANJIに変換します。

前述の例では、TRANSLATEファンクションを使用して、COLOURS表のENGLISH列のASCII文字を漢字キャラクタ・セット（1文字ごとに2オクテットを使用）のローマ字に変換しています。これは、連結に便利です（第2.6.6項を参照）。

2.6.2.27 TRIMファンクション

TRIMファンクションでは、先行または末尾あるいは両方の空白、数値または文字が文字値式から削除されます。指定の文字列から先行または末尾（あるいは両方）の文字が削除された文字列が返されます。

指定しない場合、BOTHオプションがデフォルトになります。文字列を指定しない場合は、空白文字がデフォルトになります。

削除する文字値式は、CHAR、VARCHAR、NCHARまたはNCHAR VARYINGのデータ型として定義されている必要があります。TRIMファンクションを使用する前に、CASTファンクションを使用して他のデータ型を変換します。

削除文字の長さが正確に1文字でない場合は、ランタイム・エラーが返されます。

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

例1: 次の例は、有効ではありませんがTRIMファンクションを示しています。

SQL> SELECT LAST_NAME,
cont> TRIM (LEADING 'H' FROM LAST_NAME)
cont> FROM EMPLOYEES
cont> WHERE LAST_NAME LIKE 'H%';

 LAST_NAME
 Hall             all
 Harrington       arrington
 Harrison         arrison
 Hastings         astings
 Herbener         erbener
5 rows selected

例2: TRIMファンクションおよびWHERE句の使用

SQL> -- The following INSERT statement helps to show the
SQL> -- TRIM function.
SQL> --
SQL> INSERT INTO EMPLOYEES (LAST_NAME,FIRST_NAME,EMPLOYEE_ID) VALUES
cont> ('  Hillson','Ann','99999');
1 row inserted
SQL> --
SQL> -- If you select columns without specifying the
SQL> -- TRIM function on the WHERE clause, SQL returns only those
SQL> -- last names that start with 'H' and have no leading spaces.
SQL> --
SQL> SELECT LAST_NAME || ', ' || FIRST_NAME
cont> FROM EMPLOYEES
cont> WHERE LAST_NAME LIKE 'H%';

 Hall          , Lawrence
 Harrington    , Margaret
 Harrison      , Lisa
 Hastings      , Norman
 Herbener      , James
5 rows selected
SQL> --
SQL> -- Add the TRIM function to the WHERE clause to get a complete
SQL> -- list of last names beginning with 'H' including those with
SQL> -- leading spaces.
SQL> --
SQL> SELECT LAST_NAME || ', ' || FIRST_NAME
cont> FROM EMPLOYEES
cont> WHERE TRIM (LEADING ' ' FROM LAST_NAME) LIKE 'H%';

 Hastings      , Norman
 Harrington    , Margaret
 Hall          , Lawrence
 Harrison      , Lisa
   Hillson     , Ann
 Herbener      , James
6 rows selected

例3: WHERE句および問合せのSELECT部分でのTRIMファンクションの使用

SQL> -- Add the TRIM function to the SELECT portion of the query
SQL> -- to trim the leading spaces from the display of 'Hillson'.
SQL> -- Note that the LEADING option has been changed to the BOTH
SQL> -- option to trim leading and trailing spaces from the
SQL> -- LAST_NAME column.
SQL> --
SQL> SELECT TRIM (BOTH ' ' FROM LAST_NAME) || ', ' || FIRST_NAME
cont> FROM EMPLOYEES
cont> WHERE TRIM (LEADING ' ' FROM LAST_NAME) LIKE 'H%';

 Hastings, Norman
 Harrington, Margaret
 Hall, Lawrence
 Harrison, Lisa
 Hillson, Ann
 Herbener, James
6 rows selected