ヘッダーをスキップ
Oracle Warehouse Builderユーザーズ・ガイド
10gリリース2(10.2)
B31258-02
  目次
目次
索引
索引

戻る
戻る
 
次へ
次へ
 

27 変換

マッピングおよびプロセス・フローを設計する際、専用の変換を使用してデータを変換できます。この章では、Warehouse Builderが提供するすべての事前定義済の変換について説明します。

この章の内容は次のとおりです。それぞれのトピックには、そのカテゴリ内のすべての事前定義済変換の詳細が含まれます。

管理変換

管理変換により事前構築ファンクションが提供され、ETLプロセスで定期的に実行されるアクションを実行します。これらの変換は主に、DBA関連領域、またはパフォーマンスの向上にフォーカスがおかれています。たとえば、表をロードする際に制約を無効化して、ロードが完了した後に再度有効化するのが一般的です。

Warehouse Builderの管理変換は、カスタム・ファンクションです。Warehouse Builderが提供する管理変換は、次のとおりです。

WB_ABORT

構文

WB_ABORT(p_code, p_message)

p_codeは停止コードで、-20000から-29999の範囲です。また、p_messageは、ユーザーが指定する停止メッセージです。

目的

WB_ABORTを使用すると、Warehouse Builderコンポーネントからアプリケーションを停止できます。マッピング後プロセスから、またはマッピング内の変換として、これを実行できます。

この管理ファンクションを使用して、アプリケーションを停止します。マッピングにエラーがある場合、マッピング後プロセスでこのファンクションを使用して配布を停止できます。

WB_COMPILE_PLSQL

構文

WB_COMPILE_PLSQL(p_name, p_type)

p_nameはコンパイルされるオブジェクトの名前です。p_typeは、コンパイルされるオブジェクトのタイプです。有効なタイプは次のとおりです。

'PACKAGE'
'PACKAGE BODY'
'PROCEDURE'
'FUNCTION'
'TRIGGER'

目的

このプログラム・ユニットは、データベースに格納されているオブジェクトをコンパイルします。

次の例では、add_employee_procというプロシージャをコンパイルすると仮定します。

EXECUTE WB_COMPILE_PLSQL('ADD_EMPLOYEE_PROC', 'PROCEDURE');

WB_DISABLE_ALL_CONSTRAINTS

構文

WB_DISABLE_ALL_CONSTRAINTS(p_name)

p_nameは制約が無効化される表の名前です。

目的

このプログラム・ユニットは、プログラム・コールで指定した、表によって所有されるすべての制約を無効化します。

データ・セットをより迅速にロードするため、表の制約を無効化できます。これでデータは検証されずにロードされます。これは主として比較的クリーンなデータ・セットについて行われます。

次の例では、表OE.CUSTOMERSの制約の無効化を示しています。

SELECT constraint_name
,      DECODE(constraint_type,'C','Check','P','Primary') Type
,      status
FROM user_constraints
WHERE table_name = 'CUSTOMERS';
CONSTRAINT_NAME                TYPE    STATUS
------------------------------ ------- --------
CUST_FNAME_NN                  Check   ENABLED
CUST_LNAME_NN                  Check   ENABLED
CUSTOMER_CREDIT_LIMIT_MAX      Check   ENABLED
CUSTOMER_ID_MIN                Check   ENABLED
CUSTOMERS_PK                   Primary ENABLED

すべての制約を無効化するには、SQL*PlusまたはWarehouse Builderで次を実行します。

EXECUTE WB_DISABLE_ALL_CONSTRAINTS('CUSTOMERS');

CONSTRAINT_NAME                TYPE    STATUS
------------------------------ ------- --------
CUST_FNAME_NN                  Check   DISABLED
CUST_LNAME_NN                  Check   DISABLED
CUSTOMER_CREDIT_LIMIT_MAX      Check   DISABLED
CUSTOMER_ID_MIN                Check   DISABLED
CUSTOMERS_PK                   Primary DISABLED

注意:

この文では、カスケード・オプションを使用して、キーの無効化によって依存性を解除できます。

WB_DISABLE_ALL_TRIGGERS

構文

WB_DISABLE_ALL_TRIGGERS(p_name)

p_nameはトリガーが無効化される表の名前です。

目的

このプログラム・ユニットは、プログラム・コールで指定した表によって所有されるすべてのトリガーを無効化します。表の所有者は、現行ユーザー(変数USER)である必要があります。このアクションはトリガーを停止し、パフォーマンスを向上します。

次の例では、表OE.OC_ORDERSのすべてのトリガーの無効化を示しています。

SELECT trigger_name
,      status
FROM user_triggers
WHERE table_name = 'OC_ORDERS';

TRIGGER_NAME                   STATUS
------------------------------ --------
ORDERS_TRG                     ENABLED
ORDERS_ITEMS_TRG               ENABLED

OC_ORDERSのすべてのトリガーを無効化するには、SQL*PlusまたはWarehouse Builderで次を実行します。

EXECUTE WB_DISABLE_ALL_TRIGGERS ('OC_ORDERS');
TRIGGER_NAME                   STATUS
------------------------------ --------
ORDERS_TRG                     DISABLED
ORDERS_ITEMS_TRG               DISABLED

WB_DISABLE_CONSTRAINT

構文

WB_DISABLE_CONSTRAINT(p_constraintname, p_tablename)

p_constraintnameは、無効化される制約名です。p_tablenameは、指定した制約が定義される表の名前です。

目的

このプログラム・ユニットは、プログラム・コールで指定した表によって所有される指定された制約を無効化します。ユーザーは現行ユーザー(変数USER)です。

データ・セットをより迅速にロードするため、表の制約を無効化できます。これで、データは検証されずにロードされます。これによりオーバーヘッドが削減されます。これは主として比較的クリーンなデータ・セットについて行われます。

次の例では、表OE.CUSTOMERSの指定された制約の無効化を示しています。

SELECT constraint_name
, DECODE(constraint_type
, 'C', 'Check'
, 'P', 'Primary'
) Type
, status
FROM user_constraints
WHERE table_name = 'CUSTOMERS';

CONSTRAINT_NAME                TYPE    STATUS
------------------------------ ------- --------
CUST_FNAME_NN                  Check   ENABLED
CUST_LNAME_NN                  Check   ENABLED
CUSTOMER_CREDIT_LIMIT_MAX      Check   ENABLED
CUSTOMER_ID_MIN                Check   ENABLED
CUSTOMERS_PK                   Primary ENABLED

指定された制約を無効化するには、SQL*PlusまたはWarehouse Builderで次を実行します。

EXECUTE WB_DISABLE_CONSTRAINT('CUSTOMERS_PK','CUSTOMERS');

CONSTRAINT_NAME                TYPE    STATUS
------------------------------ ------- --------
CUST_FNAME_NN                  Check   ENABLED
CUST_LNAME_NN                  Check   ENABLED
CUSTOMER_CREDIT_LIMIT_MAX      Check   ENABLED
CUSTOMER_ID_MIN                Check   ENABLED
CUSTOMERS_PK                   Primary DISABLED

注意:

この文では、カスケード・オプションを使用して、キーの無効化によって依存性を解除できます。

WB_DISABLE_TRIGGER

構文

WB_DISABLE_TRIGGER(p_name)

p_nameは無効化されるトリガー名です。

目的

このプログラム・ユニットは、指定されたトリガーを無効化します。トリガーの所有者は現行ユーザー(変数USER)である必要があります。

次の例では、表OE.OC_ORDERSのトリガーの無効化を示しています。

SELECT trigger_name, status
FROM user_triggers
WHERE table_name = 'OC_ORDERS';

TRIGGER_NAME                   STATUS
------------------------------ --------
ORDERS_TRG                     ENABLED
ORDERS_ITEMS_TRG               ENABLED

指定された制約を無効化するには、SQL*PlusまたはWarehouse Builderで次を実行します。

ECECUTE WB_DISABLE_TRIGGER ('ORDERS_TRG');

TRIGGER_NAME                   STATUS
------------------------------ --------
ORDERS_TRG                     DISABLED
ORDERS_ITEMS_TRG               ENABLED

WB_ENABLE_ALL_CONSTRAINTS

構文

WB_ENABLE_ALL_CONSTRAINTS(p_name)

p_nameは、すべての制約を有効化する必要がある表の名前です。

目的

このプログラム・ユニットは、プログラム・コールで指定した表によって所有されるすべての制約を有効化します。

データ・セットをより迅速にロードするため、表の制約を無効化できます。データをロードした後、このプログラム・ユニットを使用してこれらの制約を再び有効化する必要があります。

次の例では、表OE.CUSTOMERSの制約の有効化を示しています。

SELECT constraint_name
, DECODE(constraint_type
, 'C', 'Check'
, 'P', 'Primary)
Type
, status
FROM user_constraints
WHERE table_name = 'CUSTOMERS';

CONSTRAINT_NAME                TYPE    STATUS
------------------------------ ------- --------
CUST_FNAME_NN                  Check   DISABLED
CUST_LNAME_NN                  Check   DISABLED
CUSTOMER_CREDIT_LIMIT_MAX      Check   DISABLED
CUSTOMER_ID_MIN                Check   DISABLED
CUSTOMERS_PK                   Primary DISABLED

すべての制約を有効化するには、SQL*PlusまたはWarehouse Builderで次を実行します。

EXECUTE WB_ENABLE_ALL_CONSTRAINTS('CUSTOMERS');

CONSTRAINT_NAME                TYPE    STATUS
------------------------------ ------- --------
CUST_FNAME_NN                  Check   ENABLED
CUST_LNAME_NN                  Check   ENABLED
CUSTOMER_CREDIT_LIMIT_MAX      Check   ENABLED
CUSTOMER_ID_MIN                Check   ENABLED
CUSTOMERS_PK                   Primary ENABLED

WB_ENABLE_ALL_TRIGGERS

構文

WB_ENABLE_ALL_TRIGGERS(p_name)

p_nameはトリガーが有効化される表の名前です。

目的

このプログラム・ユニットは、プログラム・コールで指定した表によって所有されるすべてのトリガーを有効化します。表の所有者は、現行ユーザー(変数USER)である必要があります。

次の例では、表OE.OC_ORDERSのすべてのトリガーの有効化を示しています。

SELECT trigger_name
,      status
FROM user_triggers
WHERE table_name = 'OC_ORDERS';

TRIGGER_NAME                   STATUS
------------------------------ --------
ORDERS_TRG                     DISABLED
ORDERS_ITEMS_TRG               DISABLED

OE.OC_ORDERSに定義されたすべてのトリガーを有効化するには、SQL*PlusまたはWarehouse Builderで次を実行します。

EXECUTE WB_ENABLE_ALL_TRIGGERS ('OC_ORDERS');

TRIGGER_NAME                   STATUS
------------------------------ --------
ORDERS_TRG                     ENABLED
ORDERS_ITEMS_TRG               ENABLED

WB_ENABLE_CONSTRAINT

構文

WB_ENABLE_CONSTRAINT(p_constraintname, p_tablename)

p_constraintnameは無効化される制約名です。また、p_tablenameは指定した制約が定義される表の名前です。

目的

このプログラム・ユニットは、プログラム・コールで指定した表によって所有される指定された制約を有効化します。ユーザーは現行ユーザー(変数USER)です。データ・セットをより迅速にロードするため、表の制約を無効化できます。ロードが完了した後、これらの制約を再び有効化する必要があります。このプログラム・ユニットは制約を個別に有効化する方法を示します。

次の例は、表OE.CUSTOMERSの指定された制約の有効化を示しています。

SELECT constraint_name
,      DECODE(constraint_type
       , 'C', 'Check'
       , 'P', 'Primary'
       ) Type
,      status
FROM user_constraints
WHERE table_name = 'CUSTOMERS';

CONSTRAINT_NAME                TYPE    STATUS
------------------------------ ------- --------
CUST_FNAME_NN                  Check   DISABLED
CUST_LNAME_NN                  Check   DISABLED
CUSTOMER_CREDIT_LIMIT_MAX      Check   DISABLED
CUSTOMER_ID_MIN                Check   DISABLED
CUSTOMERS_PK                   Primary DISABLED

指定された制約を有効化するには、SQL*PlusまたはWarehouse Builderで次を実行します。

EXECUTE WB_ENABLE_CONSTRAINT('CUSTOMERS_PK', 'CUSTOMERS');

CONSTRAINT_NAME                TYPE    STATUS
------------------------------ ------- --------
CUST_FNAME_NN                  Check   DISABLED
CUST_LNAME_NN                  Check   DISABLED
CUSTOMER_CREDIT_LIMIT_MAX      Check   DISABLED
CUSTOMER_ID_MIN                Check   DISABLED
CUSTOMERS_PK                   Primary ENABLED

WB_ENABLE_TRIGGER

構文

WB_ENABLE_TRIGGER(p_name)

p_nameは、有効化するトリガー名です。

目的

このプログラム・ユニットは、指定したトリガーを有効化します。トリガーの所有者は、現行ユーザー(変数USER)である必要があります。

次の例では、表OE.OC_ORDERSでのトリガーの有効化を示します。

SELECT trigger_name
,      status
FROM user_triggers
WHERE table_name = 'OC_ORDERS';

TRIGGER_NAME                   STATUS
------------------------------ --------
ORDERS_TRG                     DISABLED
ORDERS_ITEMS_TRG               ENABLED

指定された制約を有効化するには、SQL*PlusまたはWarehouse Builderで次を実行します。

EXECUTE WB_ENABLE_TRIGGER ('ORDERS_TRG');

TRIGGER_NAME                   STATUS
------------------------------ --------
ORDERS_TRG                     ENABLED
ORDERS_ITEMS_TRG               ENABLED

WB_TRUNCATE_TABLE

構文

WB_TRUNCATE_TABLE(p_name)

p_nameは、切り捨てる表名です。

目的

このプログラム・ユニットは、コマンド・コールで指定した表を切り捨てます。トリガーの所有者は、現行ユーザー(変数USER)である必要があります。このコマンドは、すべての参照制約を無効化および再有効化し、表の切捨てコマンドを有効化します。このコマンドをマッピング前プロセスで使用してステージング表を明示的に切り捨て、このステージング表のすべてのデータが新しくロードされたデータになるようにします。

次の例では、表OE.OC_ORDERSの切捨てを示します。

SELECT COUNT(*) FROM oc_orders;

  COUNT(*)
----------
       105

指定された制約を有効化するには、SQL*PlusまたはWarehouse Builderで次を実行します。

EXECUTE WB_TRUNCATE_TABLE ('OC_ORDERS');

  COUNT(*)
----------
         0

文字変換

文字変換によりWarehouse Builderユーザーは、文字オブジェクトで変換を実行できます。Warehouse Builderに用意されているカスタム・ファンクションには、接頭辞WB_が付いています。

Warehouse Builderで使用可能な文字変換は、次のとおりです。

ASCII

構文

ascii::=ASCII(attribute)

目的

ASCIIは、データベース・キャラクタ・セットのattributeの最初の文字の10進表記を戻します。attributeは、データ型CHAR、VARCHAR2、NCHARまたはNVARCHAR2です。戻される値はデータ型NUMBERです。データベース・キャラクタ・セットが7-bit ASCIIの場合、このファンクションはASCII値を戻します。データベース・キャラクタ・セットがEBCDICコードの場合、このファンクションはEBCDIC値を戻します。対応するEBCDICキャラクタ・ファンクションはありません。

次の例では、文字Qに相当するASCIIの10進数を戻しています。

SELECT ASCII('Q') FROM DUAL;
ASCII('Q')
----------
81

CHR

構文

chr::=CHR(attribute)

目的

CHRは、データベース・キャラクタ・セットまたは各国語キャラクタ・セットのいずれかでattributeに指定された数値に相当する文字をバイナリで戻します。

USING NCHAR_CSが指定されていない場合、このファンクションは、データベース・キャラクタ・セットでVARCHAR2の値としてattributeに相当する文字をバイナリで戻します。式ビルダーにUSING NCHAR_CSが指定されている場合、このファンクションは、各国語キャラクタ・セットでNVARCHAR2の値としてattributeに相当する文字をバイナリで戻します。

次の例では、データベース・キャラクタ・セットがWE8ISO8859P1として定義されているASCIIベースのマシン上で実行されています。

SELECT CHR(67)││CHR(65)││CHR(84) "Dog"
   FROM DUAL;

Dog
---
CAT

キャラクタ・セットがWE8EBCDIC1047のEBCDICベースのマシン上で同じ結果を得るには、前述の例を次のように変更します。

SELECT CHR(195)││CHR(193)││CHR(227) "Dog"
   FROM DUAL;

Dog
---
CAT

次の例では、UTF8キャラクタ・セットを使用しています。

SELECT CHR (50052 USING NCHAR_CS)
   FROM DUAL;
CH
--
Ä

CONCAT

構文

concat::=CONCAT(attribute1, attribute2)

目的

CONCATは、attribute2と連結されたattribute1を戻します。attribute1attribute2はともに、CHARまたはVARCHAR2のデータ型のいずれかです。戻される文字列は、attribute1と同じキャラクタ・セットに含まれるVARCHAR2のデータ型です。このファンクションは、連結演算子(││)と等価です。

この例では、ネスティングを使用して3つの文字列を連結しています。

SELECT CONCAT(CONCAT(last_name, '''s job category is '), job_id) "Job"
   FROM employees
   WHERE employee_id = 152;

Job
------------------------------------------------------
Hall's job category is SA_REP

INITCAP

構文

initcap::=INITCAP(attribute)

目的

INITCAPは、各単語の最初の文字を大文字にし、残りの文字はすべて小文字にしてattributeの内容を戻します。単語は空白または英数字以外の文字で区切られています。attributeは、CHARまたはVARCHAR2のデータ型のいずれかです。戻り値はattributeと同じデータ型になります。

次の例では、文字列の各単語の最初の文字が大文字になります。

SELECT INITCAP('the soap') "Capitals" FROM DUAL;

Capitals
---------
The Soap

INSTR、INSTR2、INSTR4、INSTRB、INSTRC

構文

instr::=INSTR(attribute1, attribute2, n, m)
instr2::=INSTR2(attribute1, attribute2, n, m)
instr4::=INSTR4(attribute1, attribute2, n, m)
instrb::=INSTRB(attribute1, attribute2, n, m)
instrc::=INSTRC(attribute1, attribute2, n, m)

目的

INSTRは、attribute1のn番目の文字からattribute2m番目に出現する位置を検索します。これは出現した最初の文字であるattribute1における文字の位置を戻します。INSTRBは文字のかわりにバイトを使用します。INSTRCはUnicode完全対応の文字を使用します。INSTR2はUCS2コード・ポイントを使用します。INSTR4はUCS4コード・ポイントを使用します。

nが負の場合、attribute1の末尾から逆方向に検索、カウントが行われます。mは正数である必要があります。nmのデフォルト値は両方とも1で、これはOracleがattribute1の最初の文字から始めてattribute2が出現する位置を検索することを意味します。戻り値は、nの値にかかわらずattribute1の開始位置から相対的で、文字数で表現されます。検索に失敗した場合(attribute2m回、attribute1n番目の位置から出現しない場合)、戻り値は0になります。

次の例では、文字列CORPORATE FLOORの3番目の文字から文字列ORを検索し、CORPORATE FLOORでORが2回目に出現する位置を戻します。

SELECT INSTR('CORPORATE FLOOR','OR', 3, 2) "Instring"
   FROM DUAL;

Instring
----------
14

次の例では、末尾から3番目の文字から検索を開始します。

SELECT INSTR('CORPORATE FLOOR','OR', -3, 2) "Reversed Instring"
   FROM DUAL;

Reversed Instring
-----------------
2

この例では、ダブルバイト・データベース・キャラクタ・セットを想定しています。

SELECT INSTRB('CORPORATE FLOOR','OR',5,2) "Instring in bytes"
   FROM DUAL;

Instring in bytes
-----------------
27

LENGTH、LENGTH2、LENGTH4、LENGTHB、LENGTHC

構文

length::=LENGTH(attribute)
length2::=LENGTH2(attribute)
length4::=LENGTH4(attribute)
lengthb::=LENGTHB(attribute)
lengthC::=LENGTHC(attribute)

目的

LENGTHファンクションは、CHARまたはVARCHAR2のデータ型のいずれかであるattributeの長さを戻します。LENGTHは、入力キャラクタ・セットで定義された文字を使用して長さを計算します。LENGTHBは文字のかわりにバイトを使用します。LENGTHCはUnicode完全対応の文字を使用します。LENGTH2はUCS2コード・ポイントを使用します。LENGTH4はUCS4コード・ポイントを使用します。戻り値のデータ型はNUMBERです。attributeのデータ型がCHARの場合、長さはすべての後続の空白を含みます。attributeにNULL値が含まれる場合、このファンクションはNULLを戻します。

次の例では、LENGTHファンクションでシングルバイトおよびマルチバイトのデータベース・キャラクタ・セットを使用します。

SELECT LENGTH('CANDIDE') "Length in characters"
   FROM DUAL;

Length in characters
--------------------
7

この例では、ダブルバイト・データベース・キャラクタ・セットを想定しています。

SELECT LENGTHB ('CANDIDE') "Length in bytes"
   FROM DUAL;

Length in bytes
---------------
14

LOWER

構文

lower::=LOWER(attribute)

目的

LOWERは、すべての文字を小文字にしてattributeを戻します。attributeはCHARまたはVARCHAR2のデータ型のいずれかです。戻される値は、attributeと同じデータ型です。

次の例では、文字列が小文字で戻されます。

SELECT LOWER('MR. SCOTT MCMILLAN') "Lowercase"
   FROM DUAL;

Lowercase
--------------------
mr. scott mcmillan

LPAD

構文

lpad::=LPAD(attribute1, n, attribute2)

目的

LPADは、attribute2の文字列でn文字を左詰めで埋めてattribute1を戻します。Attribute2のデフォルトは1つの空白文字です。attribute1nよりも長い場合、このファンクションはnに一致するattribute1の一部分を戻します。

attribute1attribute2はともに、CHARとVARCHAR2のいずれかのデータ型です。戻される文字列は、VARCHAR2のデータ型で、attribute1と同じキャラクタ・セットです。引数nは、戻り値の長さの合計で、画面に表示されます。ほとんどのキャラクタ・セットでは、戻り値に含まれる文字数に一致しますが、マルチバイト・キャラクタ・セットの中には画面に表示される長さが文字列に含まれる文字数と異なる場合があります。

次の例では、文字列を*.文字で左詰めで埋めています。

SELECT LPAD('Page 1',15,'*.') "LPAD example"
   FROM DUAL;

LPAD example
---------------
*.*.*.*.*Page 1

LTRIM

構文

ltrim::=LTRIM(attribute, set)

目的

LTRIMは、attributeの左から文字を削除します。setの左端のすべての文字が削除されます。setのデフォルトは1つの空白文字です。attributeが文字リテラルの場合、一重引用符で囲む必要があります。Warehouse Builderはattributeのスキャンを先頭の文字から開始し、setにない文字が出現するまでsetにある文字をすべて削除します。次に結果を戻します。

attributesetはともにCHARとVARCHAR2のいずれかのデータ型です。戻される文字列は、VARCHAR2のデータ型になり、attributeと同じキャラクタ・セットに含まれます。

次の例では、左端にあるすべてのxとyが文字列から削除されます。

SELECT LTRIM('xyxXxyLAST WORD','xy') "LTRIM example"
   FROM DUAL;

LTRIM example
------------
XxyLAST WORD

NLSSORT

構文

nlssort::=NLSSORT(attribute, nlsparam)

目的

NLSSORTは、attributeのソートに使用されたバイト文字列を戻します。パラメータattributeはVARCHAR2のデータ型です。このファンクションを使用して、文字列のバイナリ値ではなく言語ソート順に基づいて比較します。

nlsparamの値は、'NLS_SORT = sort'という形式で、言語ソート順またはBINARYに基づくソートです。nlsparamを省略すると、このファンクションは使用中のセッションのデフォルトのソート順を使用します。

次の例では、2つの値が含まれる表を作成し、戻される値をNLSSORTファンクションでソートする方法を示しています。

CREATE TABLE test (name VARCHAR2(15));
INSERT INTO TEST VALUES ('Gaardiner');
INSERT INTO TEST VALUES ('Gaberd');

SELECT * FROM test ORDER BY name;

NAME
------
Gaardiner
Gaberd

SELECT * 
   FROM test
   ORDER BY NLSSORT(name, 'NLSSORT = XDanish');

Name
------
Gaberd
Gaardiner

NLS_INITCAP

構文

nls_initcap::=NLS_INITCAP(attribute, nlsparam)

目的

NLS_INITCAPは、各単語の最初の文字を大文字にし、残りの文字はすべて小文字にしてattributeを戻します。単語は空白または英数字以外の文字で区切られます。nlsparamの値は、書式'NLS_SORT = sort'で、言語ソート順またはBINARYに基づくソートです。言語ソート順は、大/小文字の変換に対する特殊な言語要件を処理します。これらの要件により、attributeとは異なる長さの値が戻されることもあります。nlsparamを省略すると、このファンクションは使用中のセッションのデフォルトのソート順を使用します。

次の例は、言語ソート順により、このファンクションから異なる値が戻される様子を示しています。

SELECT NLS_INITCAP('ijsland') "InitCap"
   FROM dual;

InitCap
---------
Ijsland

SELECT NLS_INITCAP('ijsland','NLS_SORT=XDutch) "InitCap"
   FROM dual;

InitCap
---------
IJsland

NLS_LOWER

構文

nls_lower::=NLS_LOWER(attribute, nlsparam)

目的

NLS_LOWERは、すべての文字を小文字にしてattributeを戻します。attributenlsparamのデータ型は、CHAR、VARCHAR2、NCHAR、NVARCHAR2、CLOBまたはNCLOBのいずれかです。戻される文字列は、VARCHAR2データ型で、attributeと同じキャラクタ・セットです。nlsparamの値は、書式'NLS_SORT = sort'で、言語ソート順またはBINARYに基づくソートです。

次の例では、言語ソート順XGermanを使用して、文字列'citta''が戻されます。

SELECT NLS_LOWER('CITTA''','NLS_SORT=XGerman) "Lowercase"
   FROM DUAL;

Lowercase
------------
citta'

NLS_UPPER

構文

nls_upper::=NLS_UPPER(attribute, nlsparam)

目的

NLS_UPPERは、すべての文字を大文字にしてattributeを戻します。attributeおよびnlsparamはともに、CHARVARCHAR2NCHARNVARCHAR2CLOBまたはNCLOBのデータ型のいずれかです。戻される文字列は、VARCHAR2のデータ型で、attributeと同じキャラクタ・セットです。nlsparamの値は、書式'NLS_SORT = sort'で、言語ソート順またはBINARYです。

次の例では、すべての文字が大文字に変換された文字列が戻されます。

SELECT NLS_UPPER('große') "Uppercase"
   FROM DUAL;

Uppercase
------------
GROßE

SELECT NLS_UPPER('große', 'NLS_SORT=XGerman) "Uppercase"
   FROM DUAL;

Uppercase
------------
GROSSE

REPLACE

構文

replace::=REPLACE(attribute, 'search_string', 'replacement_string')

目的

REPLACEは、出現するすべてのsearch_stringreplacement_stringに置換してattributeを戻します。replacement_stringが省略されたりNULLである場合は、search_stringのすべての出現が削除されます。search_stringがNULLである場合は、attributeが戻されます。

search_stringreplacement_stringおよびattributeは、CHARまたはVARCHAR2のデータ型のいずれかです。戻される文字列は、VARCHAR2データ型で、attributeと同じキャラクタ・セットです。

このファンクションは、TRANSLATEファンクションによって提供される機能のスーパーセットです。TRANSLATEでは、1つの文字の1対1の置換です。REPLACEにより文字列を別の文字列に置換したり、文字列を削除したりできます。

次の例では、「J」が「BL」に置換されます。

SELECT REPLACE('JACK and JUE','J','BL') "Changes"
   FROM DUAL;

Changes
--------------
BLACK and BLUE

REGEXP_INSTR

構文

regexp_instr:=REGEXP_INSTR(source_string, pattern, position, occurance,
                           return_option, match_parameter)

目的

REGEXP_INSTRは、INSTRファンクションの機能を拡張するものであり、正規表現パターンの文字列を検索できます。このファンクションは、入力キャラクタ・セットで定義されている文字を使用して文字列を評価します。return_option引数の値に応じて、一致する部分文字列の開始位置または終了位置を示す整数を戻します。一致が見つからない場合、ファンクションは0を戻します。このファンクションはPOSIX正規表現規格およびUnicode正規表現ガイドラインに準拠します。

  • source_stringは、検索値となる文字式です。通常は文字列であり、CHAR、VARCHAR2、NCHAR、NVARCHAR2、CLOBまたはNCLOBのいずれかのデータ型となります。

  • patternは正規表現です。通常はテキスト・リテラルであり、CHAR、VARCHAR2、NCHARまたはNVARCHAR2のいずれかのデータ型となります。最大512バイトを含めることができます。パターンのデータ型がsource_ stringのデータ型と異なる場合、Oracleデータベースはパターンをsource_ stringのデータ型に変換します。パターンで指定できる演算子のリストは、付録C「Oracle正規表現のサポート」を参照してください。

  • positionは、Oracleが検索を開始するsource_stringの文字を示す正の整数です。デフォルトは1です。これは、Oracleがsource_stringの1文字目から検索を開始することを意味します。

  • occurrenceは、source_stringにおけるパターンの何番目の出現をOracleが検索する必要があるかを示す正の整数です。デフォルトは1です。これは、Oracleがパターンの最初の出現を検索することを意味します。

  • return_optionでは、出現箇所に関してOracleが戻す情報を指定できます。

    • 0を指定した場合、Oracleは出現の最初の文字の位置を戻します。これはデフォルトです。

    • 1を指定した場合、Oracleは出現の後の文字の位置を戻します。

  • match_parameterは、ファンクションのデフォルト照合動作を変更できるテキスト・リテラルです。match_parameterに次の値の1つ以上を指定できます。

    • 「i」は大/小文字を区別しない照合を指定します。

    • 「c」は大/小文字を区別する照合を指定します。

    • 「n」では、ピリオド(.)、つまり任意の文字に一致する文字が改行文字と一致します。このパラメータを省略した場合、ピリオドは改行文字と一致しません。

    • 「m」はソース文字列を複数行とみなします。Oracleは^および$をソース文字列全体の最初または最後としてだけでなく、ソース文字列における任意の行の、それぞれ最初および最後として解釈します。このパラメータを省略した場合、Oracleはソース文字列を1行とみなします。

    複数の矛盾する値を指定した場合、Oracleは最後の値を使用します。たとえば、「ic」と指定した場合、Oracleは大/小文字を区別する照合を使用します。前述の文字以外の文字を指定した場合、Oracleはエラーを戻します。match_parameterを省略すると、次のようになります。

    • デフォルトの大/小文字の区別は、NLS_SORTパラメータの値で決定されます。

    • ピリオド(.)は改行文字と一致しません。

    • ソース文字列は1行とみなされます。

次の例では、1つ以上の空白以外の文字の出現を検索する文字列について検討しています。Oracleは文字列内の最初の文字から検索を開始し、1つ以上の空白以外の文字が6回目に出現した箇所の開始位置(デフォルト)を戻します。

SELECT 
     REGEXP_INSTR('500 Oracle Parkway, Redwood Shores, CA', '[^ ]+', 1, 6) FROM DUAL;

REGEXP_INSTR
------------
37

次の例では、大/小文字に関係なく「s」、「r」または「p」で始まり、英文字6文字が続く語の出現を検索する文字列について検討しています。Oracleは文字列内の3文字目から検索を開始し、大/小文字に関係なく「s」、「r」または「p」で始まる7文字からなる語の2番目の出現の後にくる文字の文字列内の位置を戻します。

SELECT
     REGEXP_INSTR('500 Oracle Parkway, Redwood Shores, CA',
     '[s│r│p][[:alpha:]]{ 6 }', 3, 2, 1, 'i')   FROM DUAL;

REGEXP_INSTR
------------
28

REGEXP_REPLACE

構文

regexp_replace:=REGEXP_REPLACE(source_string, pattern, replace_string,
                               position, occurance, match_parameter)

目的

REGEXP_REPLACEは、正規表現パターンの文字列を検索させることによってREPLACEファンクションの機能を拡張します。デフォルト設定によって、このファンクションは、正規表現パターンが出現するたびにreplace_stringで置き換えられたsource_stringを戻します。戻された文字列は、source_stringと同じキャラクタ・セット内にあります。最初の引数がLOBではない場合VARCHAR2を戻し、最初の引数がLOBである場合はCLOBを戻します。

このファンクションは、POSIX正規表現標準およびUnicode正規表現ガイドラインに準拠します。

  • source_stringは、検索値として機能する文字式です。一般的には文字の列であり、データ型は、CHAR、VARCHAR2、NCHAR、NVARCHAR2、CLOB、NCLOBのいずれかになります。

  • patternは正規表現です。通常はテキスト・リテラルであり、データ型は、CHAR、VARCHAR2、NCHAR、NVARCHAR2のいずれかになります。ここには、 512バイトまで入力できます。patternとsource_stringでデータ型が異なる場合は、Oracleデータベースによって、patternがsource_stringのデータ型に変換されます。

  • replace_stringのデータ型は、CHAR、VARCHAR2、NCHAR、NVARCHAR2、CLOB、NCLOBのいずれかになります。replace_stringがCLOBまたはNCLOBの場合は、replace_stringが32Kに切り捨てられます。replace_stringには、¥nの形式(nは1から9の数字)で、部分正規表現に対する後方参照を500個まで入力できます。replace_stringでnが円記号の場合は、前にエスケープ文字を入れる必要があります(¥¥)。

  • positionは、Oracleが検索を開始するsource_stringの文字を示す正の整数です。デフォルトは1です。これは、Oracleがsource_stringの1文字目から検索を開始することを意味します。

  • occurrenceは正の整数で、次の置換処理の回数を表します。

    • 0を指定した場合、一致したものすべてが置換されます。

    • 正の整数nを指定した場合、n番目に一致したものが置換されます。

  • match_parameterはテキスト・リテラルで、ファンクションが持つデフォルトの照合動作を変更できます。この引数は照合処理にのみ影響し、replace_stringには影響しません。match_parameterで使用する次の値のうち、1つ以上を指定できます。

    • 「i」は大/小文字を区別しない照合を指定します。

    • 「c」は大/小文字を区別する照合を指定します。

    • 「n」では、ピリオド(.)、つまり任意の文字に一致する文字が改行文字と一致します。このパラメータを省略した場合、ピリオドは改行文字と一致しません。

    • 「m」はソース文字列を複数行とみなします。Oracleは^および$をソース文字列全体の最初または最後としてだけでなく、ソース文字列における任意の行の、それぞれ最初および最後として解釈します。このパラメータを省略した場合、Oracleはソース文字列を1行とみなします。

    矛盾する複数の値を指定した場合は、最後の値が使用されます。たとえば「ic」と指定した場合は、大文字と小文字を区別する照合が使用されます。ここに示した文字以外の文字を指定すると、エラーが戻されます。match_parameterを省略すると、次のように処理されます。

    • デフォルトの大/小文字の区別は、NLS_SORTパラメータの値で決定されます。

    • ピリオド(.)は改行文字と一致しません。

    • ソース文字列は1行とみなされます。

次の例では、phone_numberを調べて、パターンxxx.xxx.xxxxを検索します。このパターンの書式が(xxx) xxx-xxxxに変更されます。

SELECT
     REGEXP_REPLACE(phone_number,
       '([[:digit:]]{ 3 })\.([[:digit:]]{ 3 })\.([[:digit:]]{ 4 })',
       '(\1) \2-\3')
FROM employees;

REGEXP_REPLACE
--------------------------------------------------------------------------------
(515) 123-4567
(515) 123-4568
(515) 123-4569
(590) 423-4567
. . .

次の例では、country_nameを調べます。文字列の中で、NULL以外の各文字の後に空白が挿入されます。

SELECT
     REGEXP_REPLACE(country_name, '(.)', '\1 ') "REGEXP_REPLACE"
FROM countries;

REGEXP_REPLACE
--------------------------------------------------------------------------------
A r g e n t i n a
A u s t r a l i a
B e l g i u m
B r a z i l
C a n a d a
. . .

次の例では、文字列を調べて、2つ以上の空白を検索します。出現した2つ以上の空白はそれぞれ、1つの空白に置換されます。

SELECT
     REGEXP_REPLACE('500 Oracle Parkway, Redwood Shores, CA','( ){ 2, }', ' ')
FROM DUAL;

REGEXP_REPLACE
--------------------------------------
500 Oracle Parkway, Redwood Shores, CA

REGEXP_SUBSTR

構文

regexp_substr:=REGEXP_SUBSTR(source_string, pattern, position,
                             occurance, match_parameter)

目的

REGEXP_SUBSTRは、正規表現パターンの文字列を検索させることによって、SUBSTRファンクションの機能を拡張します。REGEXP_INSTRと似ていますが、サブストリングの位置を戻すかわりに、サブストリングそのものを戻します。このファンクションは、ソース文字列内の一致文字列の位置ではなく内容が必要な場合に便利です。source_stringと同じキャラクタ・セット内にあるVARCHAR2またはCLOBデータとして文字列を戻します。

このファンクションは、POSIX正規表現標準およびUnicode正規表現ガイドラインに準拠します。

  • source_stringは、検索値として機能する文字式です。一般的には文字の列であり、データ型は、CHAR、VARCHAR2、NCHAR、NVARCHAR2、CLOB、NCLOBのいずれかになります。

  • patternは正規表現です。通常はテキスト・リテラルであり、データ型は、CHAR、VARCHAR2、NCHAR、NVARCHAR2のいずれかになります。ここには、 512バイトまで入力できます。patternとsource_stringでデータ型が異なる場合は、Oracleデータベースによって、patternがsource_stringのデータ型に変換されます。

  • replace_stringのデータ型は、CHAR、VARCHAR2、NCHAR、NVARCHAR2、CLOB、NCLOBのいずれかになります。replace_stringがCLOBまたはNCLOBの場合は、replace_stringが32Kに切り捨てられます。replace_stringには、¥nの形式(nは1から9の数字)で、部分正規表現に対する後方参照を500個まで入力できます。replace_stringでnが円記号の場合は、前にエスケープ文字を入れる必要があります(¥¥)。

  • positionは、Oracleが検索を開始するsource_stringの文字を示す正の整数です。デフォルトは1です。これは、Oracleがsource_stringの1文字目から検索を開始することを意味します。

  • occurrenceは正の整数で、次の置換処理の回数を表します。

    • 0を指定した場合、一致したものすべてが置換されます。

    • 正の整数nを指定した場合、n番目に一致したものが置換されます。

  • match_parameterはテキスト・リテラルで、ファンクションが持つデフォルトの照合動作を変更できます。この引数は照合処理にのみ影響し、replace_stringには影響しません。match_parameterで使用する次の値のうち、1つ以上を指定できます。

    • 「i」は大/小文字を区別しない照合を指定します。

    • 「c」は大/小文字を区別する照合を指定します。

    • 「n」では、ピリオド(.)、つまり任意の文字に一致する文字が改行文字と一致します。このパラメータを省略した場合、ピリオドは改行文字と一致しません。

    • 「m」はソース文字列を複数行とみなします。Oracleは^および$をソース文字列全体の最初または最後としてだけでなく、ソース文字列における任意の行の、それぞれ最初および最後として解釈します。このパラメータを省略した場合、Oracleはソース文字列を1行とみなします。

    矛盾する複数の値を指定した場合は、最後の値が使用されます。たとえば「ic」と指定した場合は、大/小文字を区別する照合が使用されます。ここに示した文字以外の文字を指定すると、エラーが戻されます。match_parameterを省略すると、次のように処理されます。

    • デフォルトの大/小文字の区別は、NLS_SORTパラメータの値で決定されます。

    • ピリオド(.)は改行文字と一致しません。

    • ソース文字列は1行とみなされます。

次の例では、文字列を調べて、カンマで囲まれた1番目のサブストリングを検索します。Oracleデータベースは、1つ以上のカンマ以外の文字列がカンマの後に続き、さらにその後にカンマが続く文字列を検索します。先頭と末尾のカンマを含めたサブストリングが戻されます。

SELECT
     REGEXP_SUBSTR('500 Oracle Parkway, Redwood Shores, CA',',[^,]+,')FROM DUAL;

REGEXPR_SUBSTR
-----------------
, Redwood Shores,

次の例では、文字列を調べて、http://の後に1文字以上の英数字とピリオド(.)(オプション)が続くサブストリングを検索します。http://とスラッシュ(/)または文字列の最後との間で、このサブストリングが最低3回、最高4回出現する文字列が検索されます。

SELECT
     REGEXP_SUBSTR('http://www.oracle.com/products',
                   'http://([[:alnum:]]+\.?){ 3,4 } /?')FROM DUAL;

REGEXP_SUBSTR
----------------------
http://www.oracle.com/

RPAD

構文

rpad::=RPAD(attribute1, n, attribute2)

目的

RPADは、attribute2の文字列でn文字を右詰めで埋めてattribute1を戻します。Attribute2のデフォルト値は空白1個です。attribute1nより長い場合、このファンクションは、nに一致するattribute1の一部を戻します。

attribute1attribute2のデータ型は、CHARまたはVARCHAR2です。戻される文字列のデータ型はVARCHAR2になり、attribute1と同じキャラクタ・セットに含まれます。

引数nは、画面に表示される戻り値の長さです。ほとんどのキャラクタ・セットでは、戻り値の文字数でもあります。ただし、一部のマルチバイト・キャラクタ・セットでは、表示される文字列の長さが、文字列の文字数と異なる場合があります。

次の例では、長さが12文字になるまで、文字「ab」が名前の右に埋め込まれます。

SELECT RPAD('MORRISON',12,'ab') "RPAD example"
   FROM DUAL;

RPAD example
-----------------
MORRISONabab

RTRIM

構文

rtrim::=RTRIM(attribute, set)

目的

RTRIMattributeを戻します。setに出現する右端の文字はすべて削除されます。setのデフォルトは1つの空白文字です。attributeが文字リテラルの場合、一重引用符で囲む必要があります。RTRIMの機能はLTRIMと同じです。attributesetはともに、CHARとVARCHAR2のデータ型のいずれかです。戻される文字列は、VARCHAR2データ型で、attributeと同じキャラクタ・セットです。

次の例では、文字列の右側から文字「xy」が削除されます。

SELECT RTRIM('BROWNINGyxXxy','xy') "RTRIM e.g."
   FROM DUAL;

RTRIM e.g
-------------
BROWNINGyxX

SOUNDEX

構文

soundex::=SOUNDEX(attribute)

目的

SOUNDEXは、attributeの音声表現を含む文字列を戻します。このファンクションでは、異なるスペルで英語の発音が似ている語句を比較できます。

この音声表現は、Donald E. KnuthのThe Art of Computer Programming, Volume 3: Sorting and Searchingで次のように定義されています。

  • 文字列の最初の文字を保持しておき、残りの文字からa、e、h、i、o、u、w、yの文字をすべて取り除きます。

  • さらに残った(先頭以外の)文字のすべてに次のように数字を割り当てます。

    • b、f、p、v = 1

    • c、g、j、k、q、s、x、z = 2

    • d、t = 3

    • l = 4

    • m、n = 5

    • r = 6

  • 2つ以上の同じ数字を含む文字が元の名前内(ステップ1実行前の名前)で隣接していたり、hやwを挟んだ形で隣接している場合、数字を1つ残してすべて削除してください。

  • 先頭の4バイトに0を埋め込んで戻します。

attributeのデータ型は、CHARまたはVARCHAR2です。戻り値のデータ型は、attributeと同じです。

次の例では、姓の音声表現が「Smyth」になる従業員が戻されます。

SELECT last_name, first_name
   FROM hr.employees
   WHERE SOUNDEX(last_name) = SOUNDEX('SMYTHE');


LAST_NAME  FIRST_NAME
---------- ----------
Smith      Lindsey

SUBSTR、SUBSTR2、SUBSTR4、SUBSTRB、SUBSTRC

構文

substr::=SUBSTR(attribute, position, substring_length)
substr2::=SUBSTR2(attribute, position, substring_length)
substr4::=SUBSTR4(attribute, position, substring_length)
substrb::=SUBSTRB(attribute, position, substring_length)
substrc::=SUBSTRC(attribute, position, substring_length)

目的

部分文字列の各ファンクションは、attributeの指定されたpositionから始まる、substring_lengthに指定された長さの文字を戻します。SUBSTRは、入力キャラクタ・セットで定義された文字を使用して長さを計算します。SUBSTRBは文字のかわりにバイトを使用します。SUBSTRCはUnicodeの完了文字を使用します。SUBSTR2はUCS2コード・ポイントを使用します。SUBSTR4はUCS4コード・ポイントを使用します。

  • positionを0にすると、1として扱われます。

  • positionを正にした場合、Warehouse Builderは、attributeの先頭から数えて最初の文字を特定します。

  • positionを負にした場合、Warehouse Builderは、attributeの末尾から反対方向に数えます。

  • substring_lengthを省略すると、Warehouse Builderは、attributeの末尾までの文字をすべて戻します。substring_lengthを1より小さくすると、NULLが戻されます。

attributeのデータ型は、CHARまたはVARCHAR2にできます。戻り値のデータ型は、attributeと同じです。浮動小数点数を引数としてSUBSTRに渡すと、整数に自動的に変換されます。

次の例では、「ABCDEFG」の指定された部分文字列が戻されます。

SELECT SUBSTR('ABCDEFG',3,4) "Substring"
   FROM DUAL;

Substring
---------
CDEF

SELECT SUBSTR('ABCDEFG',-5,4) "Substring"
   FROM DUAL;

Substring
---------
CDEF

ダブルバイト・データベース・キャラクタ・セットの場合は、次のようになります。

SELECT SUBSTRB('ABCDEFG',5,4.2) "Substring with bytes"
   FROM DUAL;

Substring with bytes
--------------------
CD

TRANSLATE

構文

translate::=TRANSLATE(attribute, from_string, to_string)

目的

TRANSLATEは、from_stringで指定された各文字の出現箇所をすべてto_stringで指定された文字に置き換えたattributeを戻します。from_stringに含まれないattribute内の文字は置換されません。引数from_stringにはto_stringよりも多くの文字を含めることができます。この場合、from_stringの末尾にあるto_stringの文字と対応しない余分な文字があることになります。attributeに含まれるこれらの余分な文字は戻り値から削除されます。

to_stringに空文字列を使用し、from_stringと一致するすべての文字を戻り値から削除することはできません。Warehouse Builderでは空文字列はNULLとして解釈され、このファンクションは、NULL引数が含まれる場合、NULLを戻します。

次の文では、ライセンス番号が変換されます。すべての文字「ABC...Z」は「X」に、すべての数字「012... 9」は「9」に変換されます。

SELECT TRANSLATE('2KRW229','0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',
'9999999999XXXXXXXXXXXXXXXXXXXXXXXXXX') "License"
FROM DUAL;

License
--------
9XXX999

次の文では、文字が削除されて数字が残った状態でライセンス番号が戻されます。

SELECT TRANSLATE('2KRW229','0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', '0123456789')  "Translate example"
FROM DUAL;

Translate example
-----------------
2229

TRIM

構文

trim::=TRIM(attribute)

目的

TRIMでは、先頭の空白または末尾の空白、あるいはその両方を文字列から削除できます。このファンクションは、データ型がVARCHAR2の値を戻します。値の最大長は、attributeの長さです。

次の例では、先頭の空白と末尾の空白が文字列から削除されます。

SELECT TRIM ('   Warehouse    ') "TRIM Example"
   FROM DUAL;

TRIM example
------------
Warehouse

UPPER

構文

upper::=UPPER(attribute)

目的

UPPERは、すべての文字を大文字にしてattributeを戻します。attributeのデータ型は、CHARまたはVARCHAR2にできます。戻り値のデータ型は、attributeと同じです。

次の例では、大文字の文字列が戻されます。

SELECT UPPER('Large') "Uppercase"
   FROM DUAL;

Upper
-----
LARGE

WB_LOOKUP_CHAR (number)

構文

WB.LOOKUP_CHAR (table_name
, column_name
, key_column_name
, key_value
)

table_nameは参照を実行する表の名前で、column_nameは、参照結果などで戻されるVARCHAR2列の名前です。たとえば、key_column_nameの参照結果は、参照表で照合するキーとして使用されるNUMBER列の名前です。key_valueは、照合を実行するkey_column_nameにマッピングされるキー列の値です。

目的

照合キーとしてNUMBER列を使用し、数値でキー参照を実行して、データベース表からVARCHAR2値を 戻します。

参照表LKP1として、次の表を使用するとします。

KEY_COLUMN    TYPE    COLOR
10            Car     Red
20            Bike    Green

次のようなコールで、このパッケージを使用します。

WB.LOOKUP_CHAR ('LKP1'
, 'TYPE'
, 'KEYCOLUMN'
, 20
)

この場合は、この変換の出力として値「Bike」が戻されます。この出力は、インライン・ファンクション・コールの結果として、マッピングで処理されます。


注意:

このファンクションは、行ベースのキー参照です。セット・ベースの参照は、参照演算子を使用した場合にサポートされます。

WB_LOOKUP_CHAR (varchar2)

構文

WB.LOOKUP_CHAR (table_name
, column_name
, key_column_name
, key_value
)

table_nameは参照を実行する表の名前で、column_nameは参照結果などで戻されるVARCHAR2列の名前です。key_column_nameは、参照表で照合するキーとして使用されるVARCHAR2列の名前です。key_valueは、照合を実行するkey_column_nameにマッピングされる値などのキー列の値です。

目的

照合キーとしてVARCHAR2列を使用し、VARCHAR2の文字でキー参照を実行して、データベース表からVARCHAR2値を戻します。

参照表LKP1として、次の表を使用するとします。

KEYCOLUMN  TYPE  COLOR
ACV        Car   Red
ACP        Bike  Green

次のようなコールで、このパッケージを使用します。

WB.LOOKUP_CHAR ('LKP1'
, 'TYPE'
, 'KEYCOLUMN'
, 'ACP'
)

この場合は、この変換の出力として値「Bike」が戻されます。この出力は、インライン・ファンクション・コールの結果として、マッピングで処理されます。


注意:

このファンクションは、行ベースのキー参照です。セット・ベースの参照は、参照演算子を使用した場合にサポートされます。

WB_IS_SPACE

構文

WB_IS_SPACE(attibute)

目的

文字列値に空白のみが含まれているかどうかをチェックします。このファンクションは、ブール値を戻します。メインフレーム・ソースでは、固定長書式にファイルを合せるため、一部のフィールドに多くの空白が含まれます。このファンクションでは、この空白をチェックできます。

WB_IS_SPACEは、attributeに空白のみが含まれる場合、TRUEを戻します。

コントロール・センター変換

コントロール・センター変換をプロセス・フローまたはカスタム変換で使用すると、実行時にコントロール・センターに関する情報にアクセスできます。たとえば、推移に関する式にコントロール・センター変換を使用すると、実行時にプロセス・フローを使用してフローを制御しやすくなります。また、カスタム・ファンクションの中でコントロール・センター変換を使用することもできます。そしてそのカスタム・ファンクションをプロセス・フローの設計に使用できます。

すべてのコントロール・センター変換には、コントロール・センター・リポジトリに格納された監査データへのハンドルとなる監査IDが必要です。監査IDは、パブリック・ビューALL_RT_AUDIT_EXECUTIONSへのキーです。この変換を使用して、実行時にその監査ID固有のデータを取得できます。プロセス・フローのコンテキストで実行すると、プロセス・フロー式で擬似変数audit_idを使用して、実行時に監査IDを取得できます。この変数は、現在実行中のジョブの監査IDとして評価されます。たとえば、マップ入力パラメータの場合はマップの実行を表し、推移の場合は推移元のジョブを表します。

コントロール・センター変換は、次のとおりです。

WB_RT_GET_ELAPSED_TIME

構文

WB_RT_GET_ELAPSED_TIME(audit_id)

目的

このファンクションは、指定されたaudit_idで与えられたジョブの実行の経過時間を秒単位で戻します。指定された監査IDが存在しない場合は、NULLを戻します。たとえば、前のアクティビティの所要時間に応じて選択する場合、このファンクションを推移に使用できます。

次の例では、audit_idで表されるアクティビティが開始されてから経過した時間を戻します。

declare
   audit_id NUMBER := 1812;
   l_time NUMBER;
begin
   l_time:= WB_RT_GET_ELAPSED_TIME(audit_id);
end;

WB_RT_GET_JOB_METRICS

構文

WB_RT_GET_JOB_METRICS(audit_id, no_selected, no_deleted, no_updated, no_inserted, no_discarded,
no_merged, no_corrected)

ジョブの実行時において、no_selectedは選択された行数、no_deletedは削除された行数、no_updatedは更新された行数、no_insertedは挿入された行数、no_discardedは破棄された行数、no_mergedはマージされた行数、no_correctedは修正された行数を表します。

目的

このプロシージャは、指定されたaudit_idで表されるジョブ実行のメトリックを戻します。このメトリックには、選択、削除、更新、挿入、破棄、マージおよび修正された行の数があります。

次の例では、audit_idで表される監査IDのジョブ・メトリックを取得します。

declare
   audit_id NUMBER := 16547;
   l_nselected NUMBER;
   l_ndeleted NUMBER;
   l_nupdated NUMBER;
   l_ninserted NUMBER;
   l_ndiscarded NUMBER;
   l_nmerged NUMBER;
   l_ncorrected NUMBER;
begin
   WB_RT_GET_JOB_METRICS(audit_id, l_nselected, l_ndeleted, l_nupdated,
                         l_ninserted, l_ndiscarded, l_nmerged, l_ncorrected);
   dbms_output.put_line('sel=' ││ l_nselected ││ ', del=' l_ndeleted ││
                        ', upd=' ││ l_nupdated);
   dbms_output.put_line('ins='││ l_ninserted ││ ' , dis=' ││ l_ndiscarded );
   dbms_output.put_line('mer=' ││ l_nmerged ││ ', cor=' ││l_ncorrected);
 end;

WB_RT_GET_LAST_EXECUTION_TIME

構文

WB_RT_GET_LAST_EXECUTION_TIME(objectName, objectType, objectLocationName)

objectNameはオブジェクト名、objectTypeはオブジェクトのタイプ(MAPPING、DATA_AUDITOR、PROCESS_FLOW、SCHEDULABLEなど)、objectLocationNameはオブジェクトが配布される場所を表します。

目的

この変換により、時間ベースのデータにアクセスできます。一般的には、これはプロセス・フローで使用して、時間に関連する設計の側面をモデル化します。たとえば、前回の実行から2日以上経過した場合に別のマップを実行できるように、パスを設計できます。

また、この変換を使用して、同時に実行される複数のプロセス・フローで、時間の同期化を決定することもできます。たとえば、別のプロセス・フローが完了しているかどうかに従って、プロセス・フローのパスを選択できます。

次の例では、TIMES_MAPのマッピングが前回実行された日時を取得し、if条件によって、その日時から現在までの経過時間が1日以内かどうかを判定します。この日時に基づいて、別のアクションを実行できます。

declare
    last_exec_time DATE;
begin
    last_exec_time:=WB_RT_GET_LAST_EXECUTION_TIME('TIMES_MAP','MAPPING','WH_LOCATION');
    if last_exec_time < sysdate - 1 then
--       last-execution was more than one day ago
--       provide details of action here
          NULL;
    Else
--       provide details of action here
          NULL;
    end if;
end;

WB_RT_GET_MAP_RUN_AUDIT

構文

WB_RT_GET_MAP_RUN_AUDIT(audit_id)

目的

このファンクションは、マップ・アクティビティを表すジョブ実行用のマップ実行IDを戻します。audit_idがマップのジョブ実行を表すものでない場合は、NULLを戻します。たとえば、戻されたIDをキーとして使用し、ALL_RT_MAP_RUN_<name>ビューにアクセスして詳細情報を取得できます。

次の例では、監査IDが67265であるジョブ実行用のマップ実行IDを取得します。このマップ実行IDを使用して、ALL_RT_MAP_RUN_EXECUTIONSパブリック・ビューからソースの名前を取得します。

declare
  audit_id NUMBER := 67265;
  l_sources VARCHAR2(256);
  l_run_id NUMBER;
begin
  l_run_id := WB_RT_GET_MAP_RUN_AUDIT_ID(audit_id);
  SELECT source_name INTO l_sources FROM all_rt_map_run_sources
         WHERE map_run_id = l_run_id;
end;

WB_RT_GET_NUMBER_OF_ERRORS

構文

WB_RT_GET_NUMBER_OF_ERRORS(audit_id)

目的

このファンクションは、指定されたaudit_idで与えられたジョブ実行で記録されたエラーの数を戻します。特定のaudit_idが見つからない場合は、NULLを戻します。

次の例では、監査IDが8769のジョブ実行で生成されたエラーの数を取得します。このエラー数に応じて、異なるアクションを実行できます。

declare
   audit_id NUMBER := 8769;
   l_errors NUMBER;begin   l_errors := WB_RT_GET_NUMBER_OF_ERRORS(audit_id);
   if l_errors < 5 then
      .....
   else
      .....
   end if;
end;

WB_RT_GET_NUMBER_OF_WARNINGS

構文

WB_RT_GET_NUMBER_OF_WARNINGS(audit_id)

目的

このファンクションは、audit_idで表されるジョブ実行で記録された警告の数を戻します。audit_idが存在しない場合は、NULLを戻します。

次の例では、監査IDが54632のジョブ実行で生成された警告の数を戻します。この警告の数に応じて、異なるアクションを実行できます。

declare   audit_is NUMBER := 54632;
   l_warnings NUMBER;begin   l_ warnings:= WB_RT_GET_NUMBER_OF_WARNINGS (audit_id);
   if l_warnings < 5 then
      .....
   else
      .....
   end if;
end;

WB_RT_GET_PARENT_AUDIT_ID

構文

WB_RT_GET_PARENT_AUDIT_ID(audit_id)

目的

このファンクションは、audit_idで表されるジョブ実行を保有するプロセスの監査IDを戻します。audit_idが存在しない場合は、NULLを戻します。戻された監査IDは、ALL_RT_AUDIT_EXECUTIONSなど、他のパブリック・ビューへのキーとして使用したり、詳細な情報が必要な場合は他のコントロール・センター変換に使用できます。

次の例では、監査IDが76859のジョブ実行用の親監査IDを取得します。この監査IDを使用して、親アクティビティの経過時間を判断できます。親アクティビティの経過時間に応じて、異なるアクションを実行できます。

declare
   audit_id NUMBER := 76859;
   l_elapsed_time NUMBER;
   l_parent_id NUMBER;
begin
   l_parent_id := WB_RT_GET_PARENT_AUDIT_ID(audit_id);
   l_elapsed_time := WB_RT_GET_ELAPSED_TIME(l_parent_id);
   if l_elpased_time < 100 then
      .....
   else
      .....
   end if;
end;

WB_RT_GET_RETURN_CODE

構文

WB_RT_GET_RETURN_CODE(audit_id)

目的

このファンクションは、audit_idで表されるジョブ実行で記録されたリターン・コードを戻します。audit_idが存在しない場合は、NULLを戻します。ジョブの実行が正常に終了すると、リターン・コードは0以上になります。リターン・コードが0未満の場合は、ジョブの実行が失敗したことを意味します。

次の例では、監査IDがaudit_idで表されるジョブ実行のリターン・コードを取得します。

declare   audit_id NUMBER:=69;
   l_code NUMBER;begin   l_code:= WB_RT_GET_RETURN_CODE(audit_id);end;

WB_RT_GET_START_TIME

構文

WB_RT_GET_START_TIME(audit_id)

目的

このファンクションは、audit_idで表されるジョブ実行の開始時間を戻します。audit_idが存在しない場合は、NULLを戻します。たとえば、前のアクティビティの開始時間に応じて選択する場合、これを推移に使用できます。

次の例では、監査IDが354のジョブ実行の開始時間を判断します。

declare   audit_id NUMBER:=354;
   l_date TIMESTAMP WITH TIMEZONE;begin   l_date := WB_RT_GET_START_TIME(audit_id);end;

変換の変換

Warehouse Builderユーザーは、変換の変換により、値の条件付き変換が可能なファンクションを実行できます。これらのファンクションでは、SQL内でif-then構成が実行されます。

Warehouse Builderでは、次の変換の変換を使用できます。

ASCIISTR

構文

asciistr::=ASCII(attribute)

目的

ASCIISTRは、データ型VARCHAR2の文字列を引数として取得し、ASCIIバージョンの文字列を戻します。ASCII以外の文字は、形式¥xxxxに変換されます。xxxxはUTF-16コード・ユニットを表します。

次の例では、テキスト文字列「ABÄDE」と等価のASCII文字列が戻されます。

SELECT ASCIISTR('ABÄDE') FROM DUAL;

ASCIISTR('
----------
AB\00C4CDE

COMPOSE

構文

compose::=COMPOSE(attribute)

目的

COMPOSEは、入力と同じキャラクタ・セットで、完全に正規化された形式のUnicode文字列を戻します。パラメータattributeは、CHAR、VARCHAR2、NCHAR、NVARCHAR2、CLOBまたはNCLOBのいずれかのデータ型にできます。たとえば、ウムラウト・コード・ポイントで修飾されたoのコード・ポイントは、oウムラウト・コード・ポイントとして戻されます。

次の例では、oウムラウト・コード・ポイントが戻されます。

SELECT COMPOSE('o' ││ UNISTR('\038')) FROM DUAL;

CO
---
ö 

CONVERT

構文

convert::=CONVERT(attribute, dest_char_set, source_char_set)

目的

CONVERTは、演算子attributeに指定されている文字列をあるキャラクタ・セットから別のキャラクタ・セットに変換します。戻り値のデータ型はVARCHAR2です。

  • 引数attributeは、変換する値です。データ型はCHARまたはVARCHAR2のいずれかです。

  • 引数dest_char_setは、変換後のattributeに使用するキャラクタ・セットの名前です。

  • 引数source_char_setは、attributeのデータベースへの格納に使用されているキャラクタ・セットの名前です。デフォルト値は、データベース・キャラクタ・セットです。

変換後および変換前のキャラクタ・セット引数は、キャラクタ・セット名を含むリテラルまたは列にすることができます。文字変換が完全に対応するには、変換前キャラクタ・セットで定義されている文字表現がすべて変換後キャラクタ・セットに含まれる必要があります。変換後キャラクタ・セットにない文字は、置換文字に置換されます。置換文字はキャラクタ・セットの一部として定義できます。

次の例では、Latin-1文字列をASCIIに変換するキャラクタ・セット変換の具体例が示されています。変換結果は、同じ文字列をWE8ISO8859P1データベースからUS7ASCIIデータベースにインポートした場合と同じになります。

SELECT CONVERT('Ä Ê Í Õ Ø A B C D E ', 'US7ASCII', 'WE8ISO8859P1')
   FROM DUAL;

CONVERT('ÄÊÍÕØABCDE'
---------------------
A E I ? ? A B C D E ?

一般的なキャラクタ・セットは次のとおりです。

  • US7ASCII: US 7-bit ASCIIキャラクタ・セット

  • WE8DEC: West European 8-bitキャラクタ・セット

  • WE8HP: HP West European Laserjet 8-bitキャラクタ・セット

  • F7DEC: DEC French 7-bitキャラクタ・セット

  • WE8EBCDIC500: IBM West European EBCDIC Code Page 500

  • WE8PC850: IBM PC Code Page 850

  • WE8ISO8859P1: ISO 8859-1 West European 8-bitキャラクタ・セット

HEXTORAW

構文

hextoraw::=HEXTORAW(attribute)

目的

HEXTORAWは、CHAR、VARCHAR2、NCHARまたはNVARCHAR2のキャラクタ・セット内の16進数字を含むattributeをraw値に変換します。このファンクションは、CLOBデータを直接サポートしません。ただし、CLOBは暗黙的なデータ変換により、引数として渡すことができます。

次の例では、1つのRAW列を含む単純な表を作成し、RAWに変換された16進値の値を挿入します。

CREATE TABLE test (raw_col RAW(10));

INSERT INTO test VALUES (HEXTORAW('7D'));

NUMTODSINTERVAL

構文

numtodsinterval::=NUMTODSINTERVAL(n,interval_unit)

目的

NUMTODSINTERVALは、nをINTERVAL DAY TO SECONDリテラルに変換します。引数nは、任意のNUMBER値または暗黙的にNUMBER値に変換する式です。引数interval_unitは、CHAR、VARCHAR2、NCHARまたはNVARCHAR2のいずれかのデータ型です。interval_unit値は、nの単位を指定し、次の文字列値の1つになります。

  • 'DAY'

  • 'HOUR'

  • 'MINUTE'

  • 'SECOND'

パラメータinterval_unitでは、大/小文字は区別されません。カッコ内での先頭値と後続値は無視されます。デフォルトでは、戻り精度は 9です。

次の例では、各従業員を対象に、入社日の前100日以内に同じマネージャに雇用された従業員の数を計算します。

SELECT manager_id, last_name, hire_date,
       COUNT(*) OVER (PARTITION BY manager_id ORDER BY hire_date
       RANGE NUMTODSINTERVAL(100, 'day') PRECEDING) AS t_count
   FROM employees;

MANAGER_ID LAST_NAME                 HIRE_DATE    T_COUNT
---------- ---------                 ---------    -------
      100  Kochhar                   21-SEP-89          1
      100  De Haan                   13-JAN-93          1
      100  Raphaely                  07-DEC-94          1
      100  Kaufling                  01-MAY-95          1
      100  Hartstein                 17-FEB-96          1
. . .
      149  Grant                     24-MAY-99          1
      149  Johnson                   04-JUN-00          1
      210  Goyal                     17-AUG-97          1
      205  Gietz                     07-JUN-94          1
           King                      17-JUN-87          1

NUMTOYMINTERVAL

構文

numtoyminterval::=NUMTOYMINTERVAL(n,interval_unit)

目的

NUMTOYMINTERVALは、nをINTERVAL YEAR TO MONTHリテラルに変換します。引数nは、任意のNUMBER値または暗黙的にNUMBER値に変換できる式です。引数interval_unitは、CHAR、VARCHAR2、NCHARまたはNVARCHAR2のいずれかのデータ型です。interval_unit値は、nの単位を指定し、次の文字列値の1つになります。

  • 'DAY'

  • 'HOUR'

  • 'MINUTE'

  • 'SECOND'

パラメータinterval_unitでは、大/小文字は区別されません。カッコ内での先頭値と後続値は無視されます。デフォルトでは、戻り精度は 9です。

次の例では、各従業員を対象に、入社日の前1年以内に雇用された従業員の合計給与を計算します。

SELECT last_name, hire_date, salary, SUM(salary)
       OVER (ORDER BY hire_date
       RANGE NUMTOYMINTERVAL(1,'year') PRECEDING) AS t_sal
   FROM employees;

LAST_NAME                 HIRE_DATE     SALARY      T_SAL
---------                 ---------     ------      -----
King                      17-JUN-87      24000      24000
Whalen                    17-SEP-87       4400      28400
Kochhar                   21-SEP-89      17000      17000
. . .
Markle                    08-MAR-00       2200     112400
Ande                      24-MAR-00       6400     106500
Banda                     21-APR-00       6200     109400
Kumar                     21-APR-00       6100     109400

RAWTOHEX

構文

rawtohex::=RAWTOHEX(raw)

目的

RAWTOHEXは、rawをその16進数表現を含む文字値に変換します。引数のデータ型は、RAWである必要があります。PL/SQLブロック内からコールされた場合、このファンクションのBLOB引数を指定できます。

次の仮説の例では、RAW列の値と等価の16進数を戻します。

SELECT RAWTOHEX(raw_column) "Graphics"
   FROM grpahics;

Graphics
--------
7D

RAWTONHEX

構文

rawtonhex::=RAWTONHEX(raw)

目的

RAWTONHEXは、rawをその16進数表現を含むNVARCHAR2の文字値に変換します。

次の仮説の例では、RAW列の値と等価の16進数を戻します。

SELECT RAWTONHEX(raw_column),
     DUMP ( RAWTONHEX (raw_column) ) "DUMP" 
   FROM graphics;

RAWTONHEX(RA)           DUMP 
----------------------- ------------------------------ 
7D                      Typ=1 Len=4: 0,55,0,68 

SCN_TO_TIMESTAMP

構文

scn_to_timestamp::=SCN_TO_TIMESTAMP(number)

目的

SCN_TO_TIMESTAMPは、システム変更番号(SCN)への評価を行う番号を引数として受け取り、SCNに関連付けられたおよそのタイムスタンプを戻します。戻り値は、TIMESTAMPデータ型です。このファンクションは、SCNに関連付けられたタイムスタンプを確認する際に便利です。たとえば、これをORA_ROWSCN疑似列とともに使用して、タイムスタンプを最新の行変更に関連付けることができます。

次の例では、行の最終更新のシステム変更番号を判別するためにORA_ROWSCN疑似列を使用し、そのSCNをタイムスタンプに変換するためにSCN_TO_TIMESTAMPを使用します。

SELECT SCN_TO_TIMESTAMP(ORA_ROWSCN) FROM employees
       WHERE employee_id=188;

このような問合せを使用して、システム変更番号を、Oracle Flashback Queryで使用するタイムスタンプに変換できます。

SELECT salary FROM employees WHERE employee_id = 188;

    SALARY
----------
      3800

UPDATE employees SET salary = salary*10 WHERE employee_id = 188;
COMMIT;

SELECT salary FROM employees WHERE employee_id = 188;

    SALARY
----------
      3800

SELECT SCN_TO_TIMESTAMP(ORA_ROWSCN) FROM employees
       WHERE employee_id=188;

SCN_TO_TIMESTAMP(ORA_ROWSCN)
----------------------------------------------------------
28-AUG-03 01.58.01.000000000 PM

FLASHBACK TABLE employees TO TIMESTAMP   TO_TIMESTAMP('28-AUG-03 01.00.00.000000000 PM');

SELECT salary FROM employees WHERE employee_id = 188;

      SALARY
----------
      3800

TIMESTAMP_TO_SCN

構文

timestamp_to_scn::=TIMESTAMP_TO_SCN(timestamp)

目的

TIMESTAMP_TO_SCNは、引数としてタイムスタンプ値を受け取り、そのタイムスタンプに関連付けられたおよそのシステム変更番号(SCN)を戻します。戻り値は、データ型NUMBERです。このファンクションは、特定のタイムスタンプに関連付けられたSCNを確認する際に便利です。

次の例では、oe.orders表に行を挿入し、TIMESTAMP_TO_SCNを使用して挿入処理のシステム変更番号を判別します(実際に戻されるSCNは、システムごとに異なります)。

INSERT INTO orders (order_id, order_date, customer_id, order_total)
   VALUES (5000, SYSTIMESTAMP, 188, 2345);
COMMIT;

SELECT TIMESTAMP_TO_SCN(order_date) FROM orders
   WHERE order_id = 5000;

TIMESTAMP_TO_SCN(ORDER_DATE)
----------------------------
                      574100

TO_BINARY_DOUBLE

構文

to_binary_double::=TO_BINARY_DOUBLE(expr, fmt, nlsparam)

目的

TO_BINARY_DOUBLEは、倍精度浮動小数点数を戻します。パラメータexprは文字列でも、NUMBER、BINARY_FLOAT、BINARY_DOUBLEいずれかの型の数値でもかまいません。exprがBINARY_DOUBLEの場合、このファンクションはexprを戻します。

引数fmtおよびnlsparamはオプションで、exprが文字列の場合にのみ有効です。これらの引数は、TO_CHAR (数値)ファンクションと同じ目的で動作します。大/小文字を区別しない文字列「INF」は、正の無限大に変換されます。大/小文字を区別しない文字列「-INF」は、負の無限大に変換されます。大/小文字を区別しない文字列「NaN」は、NaN(数値ではない)に変換されます。

文字列exprには、浮動小数点の数値書式の要素(F、f、D、またはd)は使用できません。また、文字列またはNUMBERからBINARY_DOUBLEへの変換は不正確になる場合があります。NUMBER型およびキャラクタ・タイプでは数値を表すために10進数による精度が使用されますが、BINARY_DOUBLEでは2進数による精度が使用されるためです。BINARY_FLOATからBINARY_DOUBLEへの変換は正確です。

次の例は、数値のデータ型がそれぞれ異なる3つの列で構成される表に基づいています。

CREATE TABLE float_point_demo
  (dec_num NUMBER(10,2), bin_double BINARY_DOUBLE, bin_float BINARY_FLOAT);

INSERT INTO float_point_demo VALUES (1234.56,1234.56,1234.56);

SELECT * FROM float_point_demo;

   DEC_NUM BIN_DOUBLE  BIN_FLOAT
---------- ---------- ----------
   1234.56 1.235E+003 1.235E+003

次の例では、データ型NUMBERの値をデータ型BINARY_DOUBLEの値に変換します。

SELECT dec_num, TO_BINARY_DOUBLE(dec_num)
  FROM float_point_demo;

   DEC_NUM TO_BINARY_DOUBLE(DEC_NUM)
---------- -------------------------
   1234.56                1.235E+003

次の例では、dec_num列とbin_double列から抽出したダンプ情報を比較します。

SELECT DUMP(dec_num) "Decimal",
     DUMP(bin_double) "Double"
   FROM float_point_demo;

Decimal                     Double
--------------------------- ---------------------------------------------
Typ=2 Len=4: 194,13,35,57   Typ=101 Len=8: 192,147,74,61,112,163,215,10

TO_BINARY_FLOAT

構文

to_binary_float::=TO_BINARY_FLOAT(expr, fmt, nlsparam)

目的

TO_BINARY_FLOATは、単精度浮動小数点数を戻します。パラメータexprは文字列でも、NUMBER、BINARY_FLOAT、BINARY_DOUBLEいずれかのデータ型の数値でもかまいません。exprがBINARY_FLOATの場合、このファンクションはexprを戻します。

引数fmtおよびnlsparamはオプションで、exprが文字列の場合にのみ有効です。これらの引数は、TO_CHAR (数値)ファンクションと同じ目的で動作します。大/小文字を区別しない文字列「INF」は、正の無限大に変換されます。大/小文字を区別しない文字列「-INF」は、負の無限大に変換されます。大/小文字を区別しない文字列「NaN」は、NaN(数値ではない)に変換されます。

文字列exprには、浮動小数点の数値書式の要素(F、f、D、またはd)は使用できません。また、文字列またはNUMBERからBINARY_FLOATへの変換は不正確になる場合があります。NUMBER型およびキャラクタ・タイプでは数値を表すために10進数による精度が使用されますが、BINARY_FLOATでは2進数による精度が使用されるためです。BINARY_DOUBLEの値のビット数がBINARY_FLOATでサポートされる精度より大きい場合は、BINARY_DOUBLEからBINARY_FLOATへの変換は不正確になります。

次の例では、TO_BINARY_DOUBLE用に作成した表float_point_demoを使用して、データ型NUMBERの値をデータ型BINARY_FLOATの値に変換します。

SELECT dec_num, TO_BINARY_FLOAT(dec_num)
  FROM float_point_demo;

   DEC_NUM TO_BINARY_FLOAT(DEC_NUM)
---------- ------------------------
   1234.56               1.235E+003

TO_CHAR

構文

to_char_date::=TO_CHAR(attribute, fmt, nlsparam)

目的

TO_CHARは、DATEまたはNUMBERデータ型のattributeを、書式fmtで指定した書式で、VARCHAR2データ型の値に変換します。fmtを省略すると、日付はデフォルトの日付書式のVARCHAR2値に変換され、数値はちょうど有効桁数を保持するために十分な長さのVARCHAR2値に変換されます。

attributeが日付の場合、nlsparamには、月日の名前と短縮形を戻す言語を指定します。この引数は、'NLS_DATE_LANGUAGE = language'という形式にできます。nlsparamを省略すると、このファンクションはセッションでデフォルトの日付言語を使用します。

attributeが数値の場合、nlsparamには、数値書式要素から戻される次の文字を指定します。

  • 10進文字

  • グループ・セパレータ

  • 各国通貨記号

  • 国際通貨記号

この引数は、次の形式にできます。

'NLS_NUMERIC_CHARACTERS = ''dg''
 NLS_CURRENCY = ''text''
 NLS_ISO_CURRENCY = territory '

文字dは小数点文字、gは、グループ・セパレータを表します。これらは、異なるシングルバイト文字にする必要があります。引用した文字列内では、2つの一重引用符でパラメータ値を囲む必要があります。通貨記号には、10個の文字を使用できます。

nlsparamまたはパラメータの1つを省略した場合、このファンクションはセッションのデフォルト・パラメータ値を使用します。

次の例では、データベースのシステム日付で様々な変換が行われます。

SELECT TO_CHAR(sysdate) no_fmt FROM DUAL;

NO_FMT
---------
26-MAR-02

SELECT TO_CHAR(sysdate, 'dd-mm-yyyy') fmted FROM DUAL;

FMTED
----------
26-03-2002

次の例では、通貨記号の左側に空白が埋め込まれて出力されます。

SELECT TO_CHAR(-10000,'L99G999D99MI') "Amount" FROM DUAL;

Amount
--------------
$10,000.00-
SELECT TO_CHAR(-10000,'L99G999D99MI'
     'NLS_NUMERIC_CHARACTERS = '',.''
     NLS_CURRENCY = ''AusDollars'' ') "Amount"
   FROM DUAL;

Amount
-------------------
AusDollars10.000,00-

TO_CLOB

構文

to_clob::=TO_CLOB(attribute)

目的

TO_CLOBは、LOB列または他の文字列のNCLOB値をCLOB値に変換します。charは、CHAR、VARCHAR2、NCHAR、NVARCHAR2、CLOBまたはNCLOBのデータ型のいずれかです。Oracleデータベースは、基底LOBデータを各国語キャラクタ・セットからデータベース・キャラクタ・セットに変換することによってこのファンクションを実行します。

次の文は、サンプルのpm.print_media表のNCLOBデータをCLOBに変換してCLOB列に挿入し、その列の既存データをその値に置き換えます。

UPDATE PRINT_MEDIA SET AD_FINALTEXT = TO_CLOB (AD_FLTEXTN);

TO_DATE

構文

to_date::=TO_DATE(attribute, fmt, nlsparam)

目的

TO_DATEは、CHARまたはVARCHAR2のいずれかのデータ型のattributeをDATEデータ型の値に変換します。fmtattributeの形式を指定する日付書式です。fmtを省略すると、attributeはデフォルトの日付書式にする必要があります。fmtがユリウス暦の「J」の場合、attributeは整数である必要があります。nlsparamには同じ目的で日付変換を行うTO_CHARファンクションがあります。

TO_DATEファンクションのattribute引数にDATE値を使用しないでください。戻されるDATE値の最初の2桁は、fmtまたはデフォルトの日付書式によって、元のattributeと異なる場合があります。

次の例では、文字列を日付に変換します。

SELECT TO_DATE('January 15, 1989, 11:00 A.M.','Month dd, YYYY, HH:MI A.M.',
     'NLS_DATE_LANGUAGE = American')
   FROM DUAL;

TO_DATE 
---------
15-JAN-89

TO_DSINTERVAL

構文

to_dsinterval::=TO_DSINTERVAL(char, nlsparam)

目的

TO_DSINTERVALは、CHAR、VARCHAR2、NCHARまたはNVARCHAR2のいずれかのデータ型の文字列をINTERVAL DAY TO SECOND値に変換します。引数charは、変換する文字列を表します。このファンクションに指定できる有効なnlsparamは、NLS_NUMERIC_CHARACTERSのみです。nlsparamは書式NLS_NUMERIC_CHARACTERS = "dg"で、dは小数点文字を表し、gはグループ・セパレータを表します。

次の例では、1990年1月1日の時点で勤続100日以上の従業員を、employees表から選択します。

SELECT employee_id, last_name
   FROM employees
   WHERE hire_date + TO_DSINTERVAL('100 10:00:00') <= DATE '1990-01-01';

EMPLOYEE_ID LAST_NAME
----------- ---------------
        100 King
        101 Kochhar
        200 Whalen

TO_MULTI_BYTE

構文

to_multi_byte::=TO_MULTI_BYTE(attribute)

目的

TO_MULTI_BYTEは、attributeのすべてのシングルバイト文字を対応するマルチバイト文字に変換して戻します。attributeはCHARまたはVARCHAR2のいずれかのデータ型です。戻される値はattributeと同じデータ型です。attributeに含まれるシングルバイト文字で、対応するマルチバイト文字のないものは、シングルバイト文字のまま出力されます。

このファンクションはデータベースのキャラクタ・セットにシングルバイト文字とマルチバイト文字の両方が含まれている場合にのみ便利です。

次の例では、シングルバイトの「A」がマルチバイトに変換されます。

'A' in UTF8:
SELECT dump(TO_MULTI_BYTE('A')) FROM DUAL;

DUMP(TO_MULTI_BYTE('A'))
------------------------
Typ=1 Len=3: 239,188,161

TO_NCHAR

構文

to_nchar::=TO_NCHAR(c, fmt, nlsparam)

目的

TO_NCHARは、データベース・キャラクタ・セットの文字列CLOB値またはNCLOB値を各国語キャラクタ・セットに変換します。このファンクションは、各国語キャラクタ・セットのUSING句を使用するTRANSLATE ... USINGファンクションと等価です。

次の例では、pm.print_media表のNCLOBデータを各国語キャラクタ・セットに変換します。

SELECT TO_NCHAR(ad_fltextn) FROM print_media
   WHERE product_id = 3106;

TO_NCHAR(AD_FLTEXTN)
------------------------------------------------------------------------
TIGER2  Tastaturen...weltweit fuehrend in Computer-Ergonomie.
TIGER2 3106 Tastatur
Product Nummer: 3106
Nur 39 EURO!
Die Tastatur KB 101/CH-DE ist eine Standard PC/AT Tastatur mit 102 Tasten. Tasta
turbelegung: Schweizerdeutsch.
. NEU: Kommt mit ergonomischer Schaumstoffunterlage.
. Extraflache und ergonimisch-geknickte Versionen verfugbar auf Anfrage.
. Lieferbar in Elfenbein, Rot oder Schwarz.

TO_NCLOB

構文

to_nclob::=TO_NCLOB(char)

目的

TO_NCLOBは、LOB列または他の文字列のCLOB値をNCLOB値に変換します。charはCHAR、VARCHAR2、NCHAR、NVARCHAR2、CLOBまたはNCLOBのデータ型のいずれかです。Oracleデータベースでは、charのキャラクタ・セットをデータベース・キャラクタ・セットから各国語キャラクタ・セットに変換することによってこのファンクションを実行します。

次の例では、最初にTO_NCLOBファンクションでデータを変換することによって、pm.print_media表のNCLOB列に文字データを挿入します。

INSERT INTO print_media (product_id, ad_id, ad_fltextn)
   VALUES (3502, 31001, TO_NCLOB('Placeholder for new product description'));

TO_NUMBER

構文

to_number::=TO_NUMBER(attribute, fmt, nlsparam)

目的

TO_NUMBERは、CHARまたはVARCHAR2のデータ型で、オプションの書式fmtで指定された書式の数字を含むattributeを、NUMBERデータ型の値に変換します。

次の例では、文字列データを数値に変換します。

UPDATE employees 
   SET salary = salary + TO_NUMBER('100.00', '9G999D99')
   WHERE last_name = 'Perkins';

このファンクションのnlsparamの文字列は、数値変換のTO_CHARファンクションと同じ目的で動作します。

SELECT TO_NUMBER('-AusDollars100','L9G999D99',
' NLS_NUMERIC_CHARACTERS = '',.''
NLS_CURRENCY = ''AusDollars''
') "Amount"
FROM DUAL;

Amount
----------
-100

TO_SINGLE_BYTE

構文

to_single_byte::=TO_SINGLE_BYTE(attribute)

目的

TO_SINGLE_BYTEは、attributeに含まれるすべてのマルチバイト文字を対応するシングルバイト文字に変換して戻します。attributeはCHARまたはVARCHAR2のいずれかのデータ型です。戻される値は、attributeと同じデータ型です。attributeに含まれるマルチバイト文字で、対応するシングルバイト文字がないものは、マルチバイト文字として出力で表示されます。

このファンクションはデータベースのキャラクタ・セットにシングルバイト文字とマルチバイト文字の両方が含まれている場合にのみ便利です。

次の例では、UTF8のマルチバイト文字「A」を、シングルバイトのASCII文字「A」に変換します。

SELECT TO_SINGLE_BYTE( CHR(15711393)) FROM DUAL;

T
-
A

TO_TIMESTAMP

構文

to_timestamp::=TO_TIMESTAMP(char, fnt, nlsparam)

目的

TO_TIMESTAMPは、CHAR、VARCHAR2、NCHARまたはNVARCHAR2のいずれかのデータ型のcharを、TIMESTAMPデータ型に変換します。オプションfmtは、charの形式を指定します。fmtを省略すると、charはNLS_TIMESTAMP_FORMAT初期化パラメータによって決定された、TIMESTAMPデータ型のデフォルト形式である必要があります。オプションのnlsparam引数は、日付変換に対してはTO_CHARファンクションの場合と同じ処理をします。このファンクションは、CLOBデータを直接サポートしません。ただし、CLOBデータは、暗黙的なデータ変換により、引数として渡すことができます。

次の例では、文字列をタイムスタンプに変換します。この文字列はデフォルトのTIMESTAMP書式ではないため、書式マスクを指定する必要があります。

SELECT TO_TIMESTAMP ('10-Sep-02 14:10:10.123000', 'DD-Mon-RR HH24:MI:SS.FF')
      FROM DUAL;

TO_TIMESTAMP('10-SEP-0214:10:10.123000','DD-MON-RRHH24:MI:SS.FF')
---------------------------------------------------------------------------
10-SEP-02 02.10.10.123000000 PM

TO_TIMESTAMP_TZ

構文

to_timestamp_tz::=TO_TIMESTAMP_TZ(char, fmt, nlsparam)

目的

TO_TIMESTAMP_TZは、CHAR、VARCHAR2、NCHARまたはNVARCHAR2のいずれかのデータ型のcharをTIMESTAMP WITH TIME ZONEデータ型の値に変換します。オプションfmtcharの形式を指定します。fmtを省略すると、charはTIMESTAMP WITH TIME ZONEデータ型のデフォルトの形式である必要があります。オプションのnlsparamは日付変換のTO_CHARファンクションと同じ目的で動作します。


注意:

このファンクションは、文字列をTIMESTAMP WITH LOCAL TIME ZONEに変換しません。

次の例では、文字列をTIMESTAMP WITH TIME ZONEの値に変換します。

SELECT TO_TIMESTAMP_TZ('1999-12-01 11:00:00 -8:00','YYYY-MM-DD HH:MI:SS TZH:TZM')
     FROM DUAL;

TO_TIMESTAMP_TZ('1999-12-0111:00:00-08:00','YYYY-MM-DDHH:MI:SSTZH:TZM')
--------------------------------------------------------------------
01-DEC-99 11.00.00.000000000 AM -08:00

次の例では、サンプル表oe.order_itemsおよびoe.ordersを使用して、TIMESTAMP WITH LOCAL TIME ZONEとしてNULL列をUNION操作にキャストします。

SELECT order_id, line_item_id, CAST(NULL AS TIMESTAMP WITH LOCAL TIME ZONE) 
       order_date
    FROM order_items
    UNION
SELECT order_id, to_number(null), order_date
   FROM orders;

  ORDER_ID LINE_ITEM_ID ORDER_DATE
---------- ------------ -----------------------------------
      2354            1
      2354            2
      2354            3
      2354            4
      2354            5
      2354            6
      2354            7
      2354            8
      2354            9
      2354           10
      2354           11
      2354           12
      2354           13
      2354           14-JUL-00 05.18.23.234567 PM
      2355            1
      2355            2
...

TO_YMINTERVAL

構文

to_yminterval::=TO_YMINTERVAL(char)

目的

TO_YMINTERVALは、CHAR、VARCHAR2、NCHARまたはNVARCHAR2のいずれかのデータ型のcharで表される文字列を、INTERVAL YEAR TO MONTHタイプに変換します。

次の例では、各従業員を対象に、サンプルのhr.employees表で、入社日から1年2か月後の日付を計算します。

SELECT hire_date, hire_date + TO_YMINTERVAL('01-02') "14 months"
   FROM employees;

HIRE_DATE 14 months
--------- ---------
17-JUN-87 17-AUG-88
21-SEP-89 21-NOV-90
13-JAN-93 13-MAR-94
03-JAN-90 03-MAR-91
21-MAY-91 21-JUL-92
. . .

UNISTR

構文

unistr::=UNISTR(string)

目的

UNISTRは、stringで表されるテキスト文字列を引数として受け取り、各国語キャラクタ・セットにその文字列を戻します。データベースの各国語のキャラクタ・セットは、AL16UTF16またはUTF8です。UNISTRは、Unicode文字列のリテラルへのサポートを提供しており、ユーザーは文字列の文字のUnicodeエンコーディング値を指定します。これは、NCHAR列へのデータの挿入に便利です。Unicodeエンコーディング値は、形式「¥xxxx」です。「xxxx」はUCS-2エンコーディング形式文字の16進値です。文字列そのものに円記号を含めるには、文字列の先頭に別の円記号を付けます(¥¥)。移植性とデータの保護のため、OracleではUNISTR文字列の引数にASCII文字とUnicodeエンコーディング値のみを指定することをお薦めします。

次の例では、ASCII文字とUnicodeエンコーディング値の両方をUNISTRファンクションに渡し、受け取ったファンクションが、各国語キャラクタ・セットで文字列を戻します。

SELECT UNISTR('abc\00e5\00f1\00f6') FROM DUAL;

UNISTR
------
abcåñö

日付変換

Warehouse Builderユーザーは、日付変換により、日付属性で変換を実行できます。ここでは、この変換について順番に説明します。Warehouse Builderで提供されるカスタム・ファンクションは、すべてWB_<function name>の形式になっています。

Warehouse Builderが提供する日付変換は次のとおりです。

ADD_MONTHS

構文

add_months::=ADD_MONTHS(attribute, n)

目的

ADD_MONTHSは、attributeの日付に月数nを加えた値を戻します。引数nは任意の整数にできます。これは通常、attributeまたは定数から加算されます。

attributeの日付が月の最終日の場合、または加算結果の月の日数がattributeの日付より少ない場合、戻される結果は加算後の月の最終日になります。それ以外の場合は、attributeと同じ日になります。

次の例では、サンプル表employeesで、hire_dateの1か月後を戻します。

SELECT TO_CHAR(ADD_MONTHS(hire_date,1), 'DD-MON-YYYY') "Next month"
   FROM employees
   WHERE last_name = 'Baer';

Next Month
-----------
07-JUL-1994

CURRENT_DATE

構文

current_date::=CURRENT_DATE()

目的

CURRENT_DATEはセッション・タイムゾーンの現在の日付をDATEデータ型のグレゴリオ暦の値で戻します。

次の例では、CURRENT_DATEがセッション・タイムゾーンから受ける影響を示します。

ALTER SESSION SET TIME_ZONE = '-5:0';
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
SELECT SESSIONTIMEZONE, CURRENT_DATE FROM DUAL;

SESSIONTIMEZONE CURRENT_DATE
--------------- --------------------
-05:00          29-MAY-2000 13:14:03

ALTER SESSION SET TIME_ZONE = '-8:0';
SELECT SESSIONTIMEZONE, CURRENT_DATE FROM DUAL;

SESSIONTIMEZONE CURRENT_DATE
--------------- --------------------
-08:00          29-MAY-2000 10:14:33

DBTIMEZONE

構文

dbtimezone::+DBTIMEZONE()

目的

DBTIMEZONEは、データベースのタイムゾーンの値を戻します。戻り型は、タイムゾーン・オフセット(形式「[+│-]TZH:TZM」のキャラクタ・タイプ)またはタイムゾーンのリージョン名であり、最新のCREATE DATABASEまたはALTER DATABASE文内に指定されたデータベースのタイムゾーン値に依存します。

次の例では、データベース・タイムゾーンがUTCタイムゾーンに設定されているとします。

SELECT DBTIMEZONE FROM DUAL;

DBTIME
------
+00:00

FROM_TZ

構文

from_tz::=FROM_TZ(timestamp_value, time_zone_value)

目的

FROM_TZは、timestamp_valueで示すタイムスタンプ値とtime_zone_valueで示すタイムゾーンをTIMESTAMP WITH TIME ZONE値に変換します。time_zone_valueは、形式「TZH:TZM」の文字列、またはオプションのTZD形式を使用してTZRに文字列を戻す文字式です。

次の例では、タイムスタンプ値をTIMESTAMP WITH TIME ZONEに戻します。

SELECT FROM_TZ(TIMESTAMP '2000-03-28 08:00:00', '3:00') 
   FROM DUAL;

FROM_TZ(TIMESTAMP'2000-03-2808:00:00','3:00')
---------------------------------------------------------------
28-MAR-00 08.00.00 AM +03:00

LAST_DAY

構文

last_day::=LAST_DAY(attribute)

目的

LAST_DAYは、attributeの日付が含まれる月の最終日の日付を戻します。

次の文では、現在の月の残りの日数を計算します。

SELECT SYSDATE, LAST_DAY(SYSDATE) "Last", LAST_DAY(SYSDATE) - SYSDATE "Days Left"
   FROM DUAL;

SYSDATE   Last Days Left
--------- --------- ----------
23-OCT-97 31-OCT-97 8

MONTHS_BETWEEN

構文

months_between::=MONTHS_BETWEEN(attribute1, attribute2)

目的

MONTHS_BETWEENは、attribute1の日付とattribute2の日付の間の月数を戻します。attribute1attribute2よりも後の場合、結果は正の数になり、その逆の場合、結果は負の数になります。

attribute1attribute2が月の同じ日付または両方が異なる月の最終日の場合、結果は常に整数になります。それ以外の場合、Oracleは月31日をベースとして結果の小数部分を計算し、attribute1attribute2の差を求めます。

次の例では、2つの日付間の月数を計算します。

SELECT MONTHS_BETWEEN(TO_DATE('02-02-1995','MM-DD-YYYY'),
     TO_DATE('01-01-1995','MM-DD-YYYY') ) "Months"
   FROM DUAL;

Months
----------
1.03225806

NEW_TIME

構文

new_time::=NEW_TIME(attribute, zone1, zone2)

目的

NEW_TIMEは、タイムゾーンzone1の日時がattributeの値である場合に、タイムゾーンzone2の日時に変換して戻します。このファンクションを使用する前に、NLS_DATE_FORMATパラメータを設定して、24時間表示にする必要があります。

引数zone1zone2は次の任意のテキスト文字列です。

  • AST、ADT: 大西洋標準時または夏時間

  • BST、BDT: ベーリング標準時または夏時間

  • CST、CDT: 中部標準時または夏時間

  • CST、EDT: 東部標準時または夏時間

  • GMT: グリニッジ標準時

  • HST、HDT: アラスカ・ハワイ標準時または夏時間

  • MST、MDT: 山岳部標準時または夏時間

  • NST: ニューファンドランド標準時

  • PST、PDT: 太平洋標準時または夏時間

  • YST、YDT: ユーコン標準時または夏時間

次の例では、太平洋標準時を大西洋標準時に変換して戻します。

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
SELECT NEW_TIME (TO_DATE ('11-10-99 01:23:45', 'MM-DD-YY HH24:MI:SS'),
      'AST', 'PST') "New Date and Time" 
   FROM DUAL;

New Date and Time
--------------------
09-NOV-1999 21:23:45

NEXT_DAY

構文

next_day::=NEXT_DAY(attribute1, attribute2)

目的

NEXT_DAYは、attribute1の日付より後の日付で、attribute2の文字列で指定された曜日に該当する最初の日付を戻します。引数attribute2には、そのセッションで使用している言語の曜日を指定します。完全な名前と短縮形のいずれも可能です。必要最小文字数は短縮形の文字数です。有効な短縮形に続くあらゆる文字が無視されます。戻り値は引数attribute1と同じ時、分、秒の構成になります。

次の例では、2001年2月2日以降の最初の火曜日にあたる日付を戻します。

SELECT NEXT_DAY('02-FEB-2001','TUESDAY') "NEXT DAY"
   FROM DUAL;

NEXT DAY
-----------
06-FEB-2001

ROUND (date)

構文

round_date::=ROUND(attribute, fmt)

目的

ROUNDは、書式モデルfmtで指定された単位に丸められたattributeの日付を戻します。fmtを省略した場合は、日付は最も近い日付に丸められます。

次の例では、日付を次の年の最初の日付に丸めます。

SELECT ROUND (TO_DATE ('27-OCT-00'),'YEAR') "New Year" 
   FROM DUAL;

New Year
---------
01-JAN-01

SESSIONTIMEZONE

構文

sessiontimezone::=SESSIONTIMEZONE()

目的

SESSIONTIMEZONEは、現行セッションのタイムゾーンを戻します。戻り型は、タイムゾーン・オフセット(形式が「[+│]TZH:TZM」のキャラクタ・タイプ)またはタイムゾーンのリージョン名であり、最新のALTER SESSION文内に指定されたセッション・タイムゾーン値に依存します。デフォルトのクライアント・セッション・タイムゾーンは、ORA_SDTZ環境変数を使用して設定できます。

次の例では、現行セッションのタイムゾーンを戻します。

SELECT SESSIONTIMEZONE FROM DUAL;
SESSION
--------
08:00

SYSDATE

構文

sysdate::=SYSDATE

目的

SYSDATEは現在の日時を戻します。戻り値のデータ型はDATEです。このファンクションには引数は必要ありません。分散SQL文の場合、このファンクションはローカル・データベースの日時を戻します。このファンクションはCHECK制約の条件の中では使用できません。

次の例では、現在の日時を戻します。

SELECT TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS')"NOW" FROM DUAL;

NOW
-------------------
04-13-2001 09:45:51

SYSTIMESTAMP

構文

systimestamp::=SYSTIMESTAMP()

目的

SYSTIMESTAMPは、小数点以下の秒とタイムゾーンを含む、データベースが常駐しているシステムのシステム日付を戻します。戻り型はTIMESTAMP WITH TIME ZONEです。

次の例では、システムのタイムスタンプを戻します。

SELECT SYSTIMESTAMP FROM DUAL;

SYSTIMESTAMP
------------------------------------------------------------------
28-MAR-00 12.38.55.538741 PM -08:00

次の例では、小数点以下の秒を明示的に指定する方法を示します。

SELECT TO_CHAR(SYSTIMESTAMP, 'SSSSS.FF') FROM DUAL;

TO_CHAR(SYSTIME
---------------
55615.449255

SYS_EXTRACT_UTC

構文

sys_extract_utc::=SYS_EXTRACT_UTC(datetime_with_timezone)

目的

SYS_EXTRACT_UTCは、(協定世界時-元グリニッジ標準時)をタイムゾーン・オフセットまたはタイムゾーンのリージョン名を持つ日時値からUTC抽出します。

次の例では、指定した日時からUTCを抽出します。

SELECT SYS_EXTRACT_UTC(TIMESTAMP '2000-03-28 11:30:00.00 -08:00')
   FROM DUAL;

SYS_EXTRACT_UTC(TIMESTAMP'2000-03-2811:30:00.00-08:00')
-----------------------------------------------------------------
28-MAR-00 07.30.00 PM

TRUNC (date)

構文

trunc_date::=TRUNC(attribute, fmt)

目的

TRUNCは、attributeの日付の時刻部分を、書式モデルfmtで指定された単位に切り捨てて戻します。fmtを省略した場合、日付は最も近い日に切り捨てられます。

次の例では、日付を切り捨てます。

SELECT TRUNC(TO_DATE('27-OCT-92','DD-MON-YY'), 'YEAR') "New Year" 
   FROM DUAL;

New Year
---------
01-JAN-92

WB_CAL_MONTH_NAME

構文

WB_CAL_MONTH_NAME(attribute)

目的

このファンクション・コールでは、attributeで指定した日付の月名が、省略されない名前で戻されます。

次の例では、sysdateおよび指定した日付文字列に対する値を戻します。

SELECT WB_CAL_MONTH_NAME(sysdate)
   FROM DUAL;

WB_CAL_MONTH_NAME(SYSDATE)
----------------------------
March

SELECT WB_CAL_MONTH_NAME('26-MAR-2002')
   FROM DUAL;

WB_CAL_MONTH_NAME('26-MAR-2002')
----------------------------------
March

WB_CAL_MONTH_OF_YEAR

構文

WB_CAL_MONTH_OF_YEAR(attribute)

目的

WB_CAL_MONTH_OF_YEARは、attributeで指定される日付の月(1から12)を戻します。

次の例では、sysdateおよび指定した日付文字列に対する値を戻します。

SELECT WB_CAL_MONTH_OF_YEAR(sysdate) month
   FROM DUAL;

     MONTH
----------
         3

SELECT WB_CAL_MONTH_OF_YEAR('26-MAR-2002') month
FROM DUAL;

     MONTH
----------
         3

WB_CAL_MONTH_SHORT_NAME

構文

WB_CAL_MONTH_SHORT_NAME(attribute)

目的

WB_CAL_MONTH_SHORT_NAMEは、attributeで指定される日付の月の短縮名(「Jan」など)を戻します。

次の例では、sysdateおよび指定した日付文字列に対する値を戻します。

SELECT WB_CAL_MONTH_SHORT_NAME (sysdate) month
FROM DUAL;

MONTH
---------
Mar

SELECT WB_CAL_MONTH_SHORT_NAME ('26-MAR-2002') month
FROM DUAL;

MONTH
---------
Mar

WB_CAL_QTR

構文

WB_CAL_QTR(attribute)

目的

WB_CAL_QTRは、attributeで指定される日付のグレゴリオ暦の四半期(1月から3月の場合は1)を戻します。

次の例では、sysdateおよび指定した日付文字列に対する値を戻します。

SELECT WB_CAL_QTR (sysdate) quarter
FROM DUAL;

   QUARTER
----------
         1

SELECT WB_CAL_QTR ('26-MAR-2002') quarter
FROM DUAL;

   QUARTER
----------
         1

WB_CAL_WEEK_OF_YEAR

構文

WB_CAL_WEEK_OF_YEAR(attribute)

目的

WB_CAL_WEEK_OF_YEARは、attributeで指定される日付の週(1から53)を戻します。

次の例では、sysdateおよび指定した日付文字列に対する値を戻します。

SELECT WB_CAL_WEEK_OF_YEAR (sysdate) w_of_y
FROM DUAL;

    W_OF_Y
----------
        13

SELECT WB_CAL_WEEK_OF_YEAR ('26-MAR-2002') w_of_y
FROM DUAL;

    W_OF_Y
----------
        13

WB_CAL_YEAR

構文

WB_CAL_YEAR(attribute)

目的

WB_CAL_YEARは、attributeで指定された日付の年の数値を戻します。

次の例では、sysdateおよび指定した日付文字列に対する値を戻します。

SELECT WB_CAL_YEAR (sysdate) year
FROM DUAL;

      YEAR
----------
      2002

SELECT WB_CAL_YEAR ('26-MAR-2002') w_of_y
FROM DUAL;

      YEAR
----------
      2002

WB_CAL_YEAR_NAME

構文

WH_CAL_YEAR_NAME(attribute)

目的

WB_CAL_YEAR_NAMEは、attributeで指定される日付の年のスペル・アウトした名前を戻します。

次の例では、sysdateおよび指定した日付文字列に対する値を戻します。

select WB_CAL_YEAR_NAME (sysdate) name
from dual;

NAME
----------------------------------------------
Two Thousand Two

select WB_CAL_YEAR_NAME ('26-MAR-2001') name
from dual;

NAME
----------------------------------------------
Two Thousand One

WB_DATE_FROM_JULIAN

構文

WB_DATE_FROM_JULIAN(attribute)

目的

WB_DATE_FROM_JULIANは、ユリウス暦の日付attributeを通常の日付に変換します。

次の例では、指定したユリウス暦の日付に対する値を戻します。

select to_char(WB_DATE_FROM_JULIAN(3217345),'dd-mon-yyyy') JDate 
from dual;

JDATE
-----------
08-sep-4096

WB_DAY_NAME

構文

WB_DAY_NAME(attribute)

目的

WB_DAY_NAMEは、attributeで指定される日付の完全な曜日名を戻します。

次の例では、sysdateおよび指定した日付文字列に対する値を戻します。

select WB_DAY_NAME (sysdate) name
from dual;

NAME
--------------------------------------------
Thursday

select WB_DAY_NAME ('26-MAR-2002') name
from dual;

NAME
--------------------------------------------
Tuesday

WB_DAY_OF_MONTH

構文

WB_DAY_OF_MONTH(attribute)

目的

WB_DAY_OF_MONTHは、attributeで指定される日付の、月内での日付番号を戻します。

次の例では、sysdateおよび指定した日付文字列に対する値を戻します。

select WB_DAY_OF_MONTH (sysdate) num
from dual;

       NUM
----------
        28

select WB_DAY_OF_MONTH ('26-MAR-2002') num
from dual

       NUM
----------
        26

WB_DAY_OF_WEEK

構文

WB_DAY_OF_WEEK(attribute)

目的

WB_DAY_OF_WEEKは、attributeで指定される日付の、週内での日付番号をデータベース・カレンダに基づいて戻します。

次の例では、sysdateおよび指定した日付文字列に対する値を戻します。

select WB_DAY_OF_WEEK (sysdate) num
from dual;

       NUM
----------
         5

select WB_DAY_OF_WEEK ('26-MAR-2002') num
from dual;


       NUM
----------
         3

WB_DAY_OF_YEAR

構文

WB_DAY_OF_YEAR(attribute)

目的

WB_DAY_OF_YEARは、attributeで指定される日付の、年内での日付番号を戻します。

次の例では、sysdateおよび指定した日付文字列に対する値を戻します。

select WB_DAY_OF_YEAR (sysdate) num
from dual;

       NUM
----------
        87

select WB_DAY_OF_YEAR ('26-MAR-2002') num
from dual;

       NUM
----------
        85

WB_DAY_SHORT_NAME

構文

WB_DAY_SHORT_NAME(attribute)

目的

WB_DAY_SHORT_NAMEは、attributeで指定される日付の、曜日の3文字の略称または名前を戻します。

次の例では、sysdateおよび指定した日付文字列に対する値を戻します。

select WB_DAY_SHORT_NAME  (sysdate) abbr
from dual;

ABBR
-------------------------------------
Thu

select WB_DAY_SHORT_NAME  ('26-MAR-2002') abbr
from dual;

NUM
-------------------------------------
Tue

WB_DECADE

構文

WB_DECADE(attribute)

目的

WB_DECADEは、attributeで指定される日付の、世紀内での年を10年単位で戻します。

次の例では、sysdateおよび指定した日付文字列に対する値を戻します。

select WB_DECADE  (sysdate) dcd
from dual;

       DCD
----------
         2

select WB_DECADE  ('26-MAR-2002') DCD
from dual;

       DCD
----------
         2

WB_HOUR12

構文

WB_HOUR12(attribute)

目的

WB_HOUR12は、attributeに対応する日付の時間(12時間設定)を戻します。

次の例では、sysdateおよび指定した日付文字列に対する値を戻します。

select WB_HOUR12 (sysdate) h12
from dual;

       H12
----------
         9

select WB_HOUR12 ('26-MAR-2002') h12
from dual;

       H12
----------
        12

注意:

2番目の例のように、タイムスタンプを含まない日付の場合、Oracleでは深夜12:00のタイムスタンプが使用されるため、この場合は12が戻されます。

WB_HOUR12MI_SS

構文

WB_HOUR12MI_SS(attribute)

目的

WB_HOUR12MI_SSは、HH12:MI:SSという書式でattributeのタイムスタンプを戻します。

次の例では、sysdateおよび指定した日付文字列に対する値を戻します。

select WB_HOUR12MI_SS (sysdate) h12miss
from dual;

H12MISS
-------------------------------------
09:08:52

select WB_HOUR12MI_SS ('26-MAR-2002') h12miss
from dual;

H12MISS
-------------------------------------
12:00:00

注意:

2番目の例のように、タイムスタンプを含まない日付の場合、Oracleでは深夜12:00のタイムスタンプが使用されるため、この場合は12が戻されます。

WB_HOUR24

構文

WB_HOUR24(attribute)

目的

WB_HOUR24は、attributeに対応する日付の時間(24時間設定)を戻します。

次の例では、sysdateおよび指定した日付文字列に対する値を戻します。

select WB_HOUR24 (sysdate) h24
from dual;

       H24
----------
         9

select WB_HOUR24 ('26-MAR-2002') h24
from dual;

       H24
----------
         0

注意:

2番目の例のように、タイムスタンプを含まない日付の場合、Oracleでは00:00:00というタイムスタンプが使用されるため、この場合はこのタイムスタンプが戻されます。

WB_HOUR24MI_SS

構文

WB_HOUR24MI_SS(attribute)

目的

WB_HOUR24MI_SSは、HH24:MI:SSという書式でattributeのタイムスタンプを戻します。

次の例では、sysdateおよび指定した日付文字列に対する値を戻します。

select WB_HOUR24MI_SS (sysdate) h24miss
from dual;

H24MISS
------------------------------------
09:11:42

select WB_HOUR24MI_SS ('26-MAR-2002') h24miss
from dual;

H24MISS
------------------------------------
00:00:00

注意:

2番目の例のように、タイムスタンプを含まない日付の場合、Oracleでは00:00:00というタイムスタンプが使用されるため、この場合はこのタイムスタンプが戻されます。

WB_IS_DATE

構文

WB_IS_DATE(attribute, fmt)

目的

attributeに有効な日付が含まれているかどうかをチェックします。このファンクションで戻されるブール値は、attributeに有効な日付が含まれている場合、trueに設定されます。fmtは、オプションの日付書式です。fmtを省略した場合は、データベース・セッションの日付書式が使用されます。

このファンクションは、データを検証してから表にロードする場合に使用できます。この方法により、値が表にロードされエラーの原因になる前に、値を変換できます。

WB_IS_DATEは、attributeに有効な日付が含まれる場合、PL/SQLのTRUEを戻します。

WB_JULIAN_FROM_DATE

構文

WB_JULIAN_FROM_DATE(attribute)

目的

WB_JULIAN_FROM_DATEは、attributeに対応する日付のユリウス暦の日付を戻します。

次の例では、sysdateおよび指定した日付文字列に対する値を戻します。

select WB_JULIAN_FROM_DATE (sysdate) jdate
from dual;

     JDATE
----------
   2452362

select WB_JULIAN_FROM_DATE ('26-MAR-2002') jdate
from dual;

     JDATE
----------
   2452360

WB_MI_SS

構文

WB_MI_SS(attribute)

目的

WB_MI_SSは、attributeに対応する日付の時間の分と秒を戻します。

次の例では、sysdateおよび指定した日付文字列に対する値を戻します。

select WB_MI_SS (sysdate) mi_ss
from dual;

MI_SS
-------------------------------------------
33:23

select WB_MI_SS ('26-MAR-2002') mi_ss
from dual;

MI_SS
-------------------------------------------
00:00

注意:

2番目の例のように、タイムスタンプを含まない日付の場合、Oracleでは00:00:00というタイムスタンプが使用されるため、この場合はこのタイムスタンプが戻されます。

WB_WEEK_OF_MONTH

構文

WB_WEEK_OF_MONTH(attribute)

目的

WB_WEEK_OF_MONTHは、attributeに対応する日付の、カレンダ月での週番号を戻します。

次の例では、sysdateおよび指定した日付文字列に対する値を戻します。

select WB_WEEK_OF_MONTH (sysdate) w_of_m
from dual;

    W_OF_M
----------
         4

select WB_WEEK_OF_MONTH ('26-MAR-2002') w_of_m
from dual;

    W_OF_M
----------
         4

番号変換

Warehouse Builderユーザーは、数値変換により、数値で変換を実行できます。Warehouse Builderで提供されるカスタム・ファンクションには、WB_が接頭辞として付きます。

Warehouse Builderで提供される数値変換は、次のとおりです。

ABS

構文

abs::=ABS(attribute)

目的

ABSは、attributeの絶対値を戻します。

次の例では、-15の絶対値を戻します。

SELECT ABS(-15) "Absolute" FROM DUAL;
Absolute
----------
15

ACOS

構文

acos::= ACOS(attribute)

目的

ACOSは、attributeの逆余弦を戻します。引数attributeの範囲は-1から1です。このファンクションによってラジアンで表記された0からπの範囲の値が戻されます。

次の例では、.3の逆余弦を戻します。

SELECT ACOS(.3) "Arc_Cosine" FROM DUAL;

Arc_Cosine
----------
1.26610367

ASIN

構文

asin::=ASIN(attribute)

目的

ASINは、attributeの逆正弦を戻します。引数の範囲attributeは-1から1です。このファンクションによって、ラジアン表記で-π/2からπ/2の値が戻されます。

次の例では、.3の逆余弦を戻します。

SELECT ACOS(.3) "Arc_Sine" FROM DUAL;

Arc_Sine
----------
.304692654

ATAN

構文

atan::=ATAN(attribute)

目的

ATANは、attributeの逆正接を戻します。引数attributeに制限はなく、ラジアン表記で-π/2からπ/2の値が戻されます。

次の例では、.3の逆正接を戻します。

SELECT ATAN(.3) "Arc_Tangent" FROM DUAL;

Arc_Tangent
----------
.291456794

ATAN2

構文

atan2::=ATAN2(attribute1, attribute2)

目的

ATAN2は、attribute1attribute2の逆正接を戻します。引数attribute1に制限はなく、ラジアン表記で-πからπの値が戻され、これはラジアン表記されたattribute1とattribute2の符号に依存します。ATAN2(attribute1,attribute2)ATAN2(attribute1/attribute2)と同じです。

次の例では、.3と.2の逆正接を戻します。

SELECT ATAN2(.3,.2) "Arc_Tangent2" FROM DUAL;

Arc_Tangent2
------------
.982793723

BITAND

構文

bitand::=BITAND(expr1,expr2)

目的

BITANDは、ともに負ではない整数になるexpr1expr2のビット上でAND演算を行い、整数を戻します。このファンクションは、次の例に示すように、通常はDECODEファンクションとともに使用します。両方の引数とも、任意の数値データ型または暗黙的にNUMBERに変換される数値以外のデータ型です。このファンクションはNUMBERを戻します。

次の例では、サンプル表oe.ordersの各order_statusを、個々のビットごとに示します(この例では、合計が7までにしかならないオプションを指定し、order_statusが7より大きい行が除外されるようにしています)。

SELECT order_id, customer_id,
  DECODE(BITAND(order_status, 1), 1, 'Warehouse', 'PostOffice')
      Location,
  DECODE(BITAND(order_status, 2), 2, 'Ground', 'Air') Method,
  DECODE(BITAND(order_status, 4), 4, 'Insured', 'Certified') Receipt
  FROM orders
  WHERE order_status < 8;

  ORDER_ID CUSTOMER_ID LOCATION   MET RECEIPT
---------- ----------- ---------- --- ---------
      2458         101 Postoffice Air Certified
      2397         102 Warehouse  Air Certified
      2454         103 Warehouse  Air Certified
      2354         104 Postoffice Air Certified
      2358         105 Postoffice G   Certified
      2381         106 Warehouse  G   Certified
      2440         107 Warehouse  G   Certified
      2357         108 Warehouse  Air Insured
      2394         109 Warehouse  Air Insured
      2435         144 Postoffice G   Insured
      2455         145 Warehouse  G   Insured
. . .

CEIL

構文

ceil::=CEIL(attribute)

目的

CEILは、attribute以上の最小整数値を戻します。

次の例では、15.7以上の最小整数値を戻します。

 SELECT CEIL(15.7) "Ceiling" FROM DUAL;
 Ceiling
----------
16

COS

構文

cos::=COS(attribute)

目的

COSは、attribute(度数で表記された角度)の余弦を戻します。

次の例では、180度の余弦を戻します。

SELECT COS(180 * 3.14159265359/180) "Cosine" FROM DUAL;

Cosine
------
     -1

COSH

構文

cosh::=COSH(attribute)

目的

COSHは、attributeの双曲正弦を戻します。

次の例では、0の双曲余弦を戻します。

SELECT COSH(0) "Hyperbolic Cosine" FROM DUAL;

Hyperbolic Cosine
-----------------
                1

EXP

構文

exp::=EXP(attribute)

目的

EXPattributeで指定される値をnとして、自然対数の底eのn乗を戻します。ここで、e = 2.71828183...です。

次の例では、4乗した自然対数の底eを戻しています。

SELECT EXP(4) "e to the 4th power" FROM DUAL;

e to the 4th power
------------------
54.59815

FLOOR

構文

floor::=FLOOR(attribute)

目的

FLOORは、attributeの数値以下の最大整数値を戻します。

次の例では、15.7以下の最大整数値を戻します。

SELECT FLOOR(15.7) "Floor" FROM DUAL;

Floor
----------
15

LN

構文

ln::=LN(attribute)

目的

LNは、attributeの自然対数を戻します。ここでattributeは0より大きい値です。

次の例では、95の自然対数を戻します。

SELECT LN(95) "Natural Logarithm" FROM DUAL;

Natural Logarithm
-----------------
4.55387689

LOG

構文

log::=LOG(attribute1, attribute2)

目的

LOGは、attribute1を底とするattribute2の対数を戻します。底attributeは0と1を除く任意の正の数で、attribute2は任意の正の数です。

次の例では、100の対数を戻します。

SELECT LOG(10,100) "Log base 10 of 100" FROM DUAL;

Log base 10 of 100
------------------
              2

MOD*

構文

mod::=MOD(attribute1, attribute2)

目的

MODは、attribute1attribute2で除算した剰余を戻します。attribute2が0の場合、attribute1を戻します。

次の例では、11を4で除算した剰余を戻します。

 SELECT MOD(11,4) "Modulus" FROM DUAL;
 Modulus
----------
3

NANVL

構文

nanvl::=NANVL(m,n)

目的

NANVLファンクションは、浮動小数点数のタイプがBINARY_FLOATまたはBINARY_DOUBLEの場合にのみ便利です。入力値mがNaNである(数値ではない)場合、Oracleデータベースによって代替値nが戻されます。mがNaNではない場合は、Oracleによってmが戻されます。このファンクションは、NULLへNaN値をマップする場合に便利です。このファンクションは、任意の数値データ型、または暗黙的に数値データ型に変換できる数値以外のデータ型を、引数として取得します。Oracleによって数値の優先度の最も高い引数が判定され、残りの引数がそのデータ型に暗黙的に変換されて、そのデータ型が戻されます。

TO_BINARY_DOUBLE用に作成した表float_point_demoを使用して、2番目のエントリをこの表に挿入します。

INSERT INTO float_point_demo
  VALUES (0,'NaN','NaN');

SELECT * FROM float_point_demo;

   DEC_NUM BIN_DOUBLE  BIN_FLOAT
---------- ---------- ----------
   1234.56 1.235E+003 1.235E+003
         0        Nan        Nan

次の例では、数値ではないbin_floatを戻します。それ以外の場合は、0を戻します。

SELECT bin_float, NANVL(bin_float,0)
  FROM float_point_demo;

 BIN_FLOAT NANVL(BIN_FLOAT,0)
---------- ------------------
1.235E+003         1.235E+003
       Nan                  0

POWER

構文

power::=POWER(attribute1, attribute2)

目的

POWERは、attribute2で指定される値をnとして、attribute1のn乗を戻します。底attribute1と指数attribute2は任意の数値にできますが、attribute1が負の場合は、attribute2を整数にする必要があります。

次の例では、3の2乗を戻します。

SELECT POWER(3,2) "Raised" FROM DUAL;
Raised
----------
9

REMAINDER

構文

remainder::=REMAINDER(m,n)

目的

REMAINDERは、mをnで除算した剰余を戻します。このファンクションは、任意の数値データ型、または暗黙的に数値データ型に変換できる数値以外のデータ型を、引数として取得します。Oracleによって数値の優先度の最も高い引数が決定され、残りの引数がそのデータ型に暗黙的に変換されて、そのデータ型が戻されます。

nnが0またはmが無限大の場合、引数の型がNUMBERであればエラーが戻され、BINARY_FLOATまたはBINARY_DOUBLEであればNaNが戻されます。nが0以外の場合、剰余はm - (n*N)になります。Nはm/nに最も近い整数です。mが浮動小数点数で剰余が0の場合、剰余の符号はmの符号になります。剰余が0の場合、NUMBER値には符号が付きません。

MODファンクションはREMAINDERと類似していますが、MODが式でFLOORを使用するのに対し、REMAINDERはROUNDを使用します。

次の例では、TO_BINARY_DOUBLE用に作成した表float_point_demoを使用して、2つの浮動小数点数を除算し、その除算の剰余を戻します。

SELECT bin_float, bin_double, REMAINDER(bin_float, bin_double)
  FROM float_point_demo;

 BIN_FLOAT BIN_DOUBLE REMAINDER(BIN_FLOAT,BIN_DOUBLE)
---------- ---------- -------------------------------
1.235E+003 1.235E+003                      5.859E-005

ROUND(数値)

構文

round_number::=ROUND(attribute1, attribute2)

目的

ROUNDは、attribute1を、小数点以下第attribute2位に丸めて戻します。attribute2を省略すると、attribute1は第0位に丸められます。また、attribute2を負にすると、小数点の左側の数値が丸められます。attribute2は整数にする必要があります。

次の例では、小数点第1位に数値を丸めます。

SELECT ROUND(15.193,1) "Round" FROM DUAL;

Round
----------
15.2

次の例では、小数点の左1桁目の数値を丸めます。

SELECT ROUND(15.193,-1) "Round" FROM DUAL;
Round
----------
20

SIGN

構文

sign::=SIGN(attribute)

目的

SIGNは、attribute< 0の場合、-1を戻します。SIGNattribute= 0の場合は0を戻し、attribute> 0の場合は1を戻します。これは、正の数値のみが期待されるメジャーの検証に使用できます。

次の例では、ファンクションの引数-15が0より小さいことを示します。

SELECT SIGN(-15) "Sign" FROM DUAL;
 Sign
----------
-1

SIN

構文

sin::=SIN(attribute)

目的

SINは、attribute(角度として表記)の正弦を戻します。

次の例では、30度の正弦を戻します。

SELECT SIN(30 * 3.14159265359/180) "Sine of 30 degrees" FROM DUAL;

Sine of 30 degrees
------------------
                .5

SINH

構文

sinh::=SINH(attribute)

目的

SINHは、attributeの双曲正弦を戻します。

次の例では、1の双曲正弦を戻します。

SELECT SINH(1) "Hyperbolic Sine of 1" FROM DUAL;

Hyperbolic Sine of 1
--------------------
          1.17520119

SQRT

構文

sqrt::=SQRT(attribute)

目的

SQRTは、attributeの平方根を戻します。attributeの値を負にすることはできません。SQRTは、実数の結果を戻します。

次の例では、26の平方根を戻します。

SELECT SQRT(26) "Square root" FROM DUAL;

Square root
-----------
5.09901951

TAN

構文

tan::=TAN(attrin=bute)

目的

TANは、attribute(角度はラジアン表記)の正接を戻します。

次の例では、135度の正接を戻します。

SELECT TAN(135 * 3.14159265359/180) "Tangent of 135 degrees" FROM DUAL;

Tangent of 135 degrees
----------------------
                    -1

TANH

構文

tanh::=TANH(attribute)

目的

TANHは、attributeの双曲正接を戻します。

次の例では、5の双曲正接を戻します。

SELECT TANH(5) "Hyperbolic tangent of 5" FROM DUAL;

Hyperbolic tangent of 5
-----------------------
             .462117157

TRUNC(数値)

構文

trunc_number::=TRUNC(attribute, m)

目的

TRUNCは、attributeを、小数点以下第m位に切り捨てて戻します。mを省略すると、attributeは第0位に切り捨てられます。mを負にすると、小数点の左側の第m位が切り捨てられます(0になります)。

次の例では、数値を切り捨てます。

SELECT TRUNC(15.79,1) "Truncate" 
FROM DUAL;
Truncate
----------
15.7
 SELECT TRUNC(15.79,-1) "Truncate" 
FROM DUAL;
 Truncate
----------
10

WB_LOOKUP_NUM(数値列用)

構文

 WB_LOOKUP_NUM (table_name
, column_name
, key_column_name
, key_value
)

table_nameは、参照する表の名前です。column_nameは、参照結果などで戻されるNUMBER列の名前です。key_column_nameは、参照表で照合するキーとして使用するNUMBER列の名前です。key_valueは、照合を実行するkey_column_nameにマップされる値などのキー列の値です。

目的

NUMBER列を照合キーとして使用し、キー参照を実行して、使用してデータベース表からNUMBER値を戻します。

参照表LKP1として、次の表を使用するとします。

KEYCOLUMN  TYPE_NO  TYPE
10         100123   Car
20         100124   Bike

次のようなコールで、このパッケージを使用します。

WB_LOOKUP_CHAR('LKP1'
, 'TYPE_NO'
, 'KEYCOLUMN'
, 20
)

この変換の出力として値100124が戻されます。この出力は、インライン・ファンクション・コールの結果としてマッピングで処理されます。


注意:

このファンクションは、行ベースのキー参照です。セット・ベースの参照は、参照演算子を使用した場合にサポートされます。

WB_LOOKUP_NUM(varchar2列用)

構文:

WB_LOOKUP_CHAR(table_name
, column_name
, key_column_name
, key_value
)

table_nameは、参照する表の名前です。column_nameは、参照結果などで戻されるVARCHAR2列の名前です。key_column_nameは、参照表で照合するキーとして使用するVARCHAR2列の名前です。key_valueは、照合を実行するkey_column_nameにマップされる値などのキー列の値です。

目的

VARCHAR2列を照合キーとして使用し、キー参照を実行して、データベース表からNUMBER値を戻します。

次の表を参照表LKP1として使用します。

KEYCOLUMN  TYPE_NO  TYPE
ACV        100123   Car
ACP        100124   Bike

次のようなコールで、このパッケージを使用します。

WB_LOOKUP_CHAR ('LKP1'
, 'TYPE'
, 'KEYCOLUMN'
, 'ACP'
)

この変換の出力として値100124が戻されます。この出力は、インライン・ファンクション・コールの結果としてマッピングで処理されます。


注意:

このファンクションは、行ベースのキー参照です。「キー参照演算子」に記載されている参照演算子を使用する場合は、セット・ベースの参照がサポートされます。

WB_IS_NUMBER

構文

 WB_IS_NUMBER(attibute, fmt)

目的

attributeに有効な数値が含まれているかどうかをチェックします。このファンクションで戻されるブール値は、attributeに有効な数値が含まれている場合、TRUEに設定されます。fmtは、オプションの数値書式です。fmtを省略すると、セッションの数値書式が使用されます。

このファンクションは、データを表にロードする前に検証する際に使用できます。これにより、値が表にロードされてエラーの原因になる前に値を変換できます。

attributeに有効な数値が含まれている場合、WB_IS_NUMBERはPL/SQLのTRUEを戻します。

WIDTH_BUCKET

構文

width_bucket::=WIDTH_BUCKET(expr,min_value,max_value,num_buckets)

目的

指定の式では、WIDTH_BUCKETは、この式の値が評価された後に入るバケットの番号を戻します。WIDTH_BUCKETでは、等幅ヒストグラムを構成します。ヒストグラムの範囲は、同一サイズのインターバルに分割されています。理想的には、各バケットは実数直線のクローズ/オープン・インターバルです。たとえば、バケットは10.00から19.999...のスコアに割り当てられ、インターバルに10が含まれ20が除外されることを示します。これは(10, 20)として示されることもあります。

引数exprは、ヒストグラムを作成するための式を表します。この式は、数値または日時値、あるいは数値または日時値に暗黙的に変換できる値として評価される必要があります。exprがNULLと評価された場合、式はNULLを戻します。min_valuemax_valueは、exprの許容範囲のエンド・ポイントになる式です。どちらの式も、数値または日時値として評価される必要があり、NULLと評価できません。num_bucketsは、バケット数を示す定数になる式です。この式は、正の整数として評価される必要があります。

Oracleデータベースでは、必要に応じて、番号が0のアンダーフロー・バケットおよび番号がnum_buckets+1のオーバーフロー・バケットが作成されます。これらのバケットは、min_valueより小さい値とmax_valueより大きい値を処理します。これらは、エンド・ポイントの妥当性をチェックする際に役立ちます。

次の例では、サンプル表oe.customerscredit_limit列で、スイスの顧客を対象に、バケットが10個あるヒストグラムを作成し、顧客ごとにバケット番号("Credit Group")を戻します。与信限度額が最大値を超える顧客は、オーバーフロー・バケットの11に割り当てられます。

SELECT customer_id, cust_last_name, credit_limit, 
   WIDTH_BUCKET(credit_limit, 100, 5000, 10) "Credit Group"
   FROM customers WHERE nls_territory = 'SWITZERLAND'
   ORDER BY "Credit Group";

CUSTOMER_ID CUST_LAST_NAME       CREDIT_LIMIT Credit Group
----------- -------------------- ------------ ------------
        825 Dreyfuss                      500            1
        826 Barkin                        500            1
        853 Palin                         400            1
        827 Siegel                        500            1
        843 Oates                         700            2
        844 Julius                        700            2
        835 Eastwood                     1200            3
        840 Elliott                      1400            3
        842 Stern                        1400            3
        841 Boyer                        1400            3
        837 Stanton                      1200            3
        836 Berenger                     1200            3
        848 Olmos                        1800            4
        849 Kaurusmdki                   1800            4
        828 Minnelli                     2300            5
        829 Hunter                       2300            5
        852 Tanner                       2300            5
        851 Brown                        2300            5
        850 Finney                       2300            5
        830 Dutt                         3500            7
        831 Bel Geddes                   3500            7
        832 Spacek                       3500            7
        838 Nicholson                    3500            7
        839 Johnson                      3500            7
        833 Moranis                      3500            7
        834 Idle                         3500            7
        845 Fawcett                      5000           11
        846 Brando                       5000           11
        847 Streep                       5000           11

OLAP変換

Warehouse Builderユーザーは、OLAP変換により、リレーショナル・ディメンションおよびリレーショナル・キューブに格納されたデータを、アナリティック・ワークスペースにロードできます。

Warehouse Builderで提供されるOLAP変換は、次のとおりです。

WB_OLAP_LOAD_CUBEWB_OLAP_LOAD_DIMENSIONおよびWB_OLAP_LOAD_DIMENSION_GENUKの各変換は、Warehouse Builderでキューブのクローニングに使用します。これらのOLAP変換は、データベースのバージョンがOracle Database 9iまたはOracle Database 10gリリース1の場合にのみ使用します。Oracle 10gリリース2からは、ディメンションおよびキューブをアナリティック・ワークスペースに直接配布できます。

WB_OLAP_AW_PRECOMPUTEは、Oracle Warehouse Builder 10gリリース2でのみ動作します。

これらのOLAP変換の説明に使用した例は、図27-1に示すシナリオに基づいています。

図27-1 OLAP変換の例

図27-1の説明が続きます
「図27-1 OLAP変換の例」の説明

リレーショナル・ディメンションTIME_DIMとリレーショナル・キューブSALES_CUBEは、スキーマWH_TGTに格納されています。スキーマWH_TGTには、ディメンションとキューブがロードされたアナリティック・ワークスペースAW_WHも作成されています。

WB_OLAP_AW_PRECOMPUTE

構文

wb_olap_aw_precompute::=WB_OLAP_AW_PRECOMPUTE(p_aw_name, p_cube_name,
        p_measure_name, p_allow_parallel_ solve, p_max_job_queues_allocated)

p_aw_nameは、キューブが配布されるアナリティック・ワークスペースの名前です。p_cube_nameは、解決するキューブの名前です。p_measure_nameは、解決する特定のメジャーの任意の名前です(メジャーが指定されない場合、すべてのメジャーが解決されます)。p_allow_parallel_solveは、パーティション化を基に解決のパラレル化を行うかどうかを示すブール値です(パフォーマンス関連のパラメータ)。p_max_job_queues_allocatedは、パラレル実行するDBMSジョブの個数です(デフォルト値は0)。これを5と定義し、20のパーティションがある場合、5つのDBMSジョブのプールが、データ・ロードの実行に使用されます。パラレルと非パラレルの解決は、微妙に異なります。非パラレル解決では、解決が同期的に実行されるため、APIコールが完了すると解決も完了します。パラレル解決は非同期的に実行され、APIコールは、起動されたジョブのジョブID付きで戻されます。ジョブは、その処理を制御する最大ジョブ・キュー・パラメータを使用して、パラレル解決を制御します。ユーザーは、そのジョブIDをall_scheduler_*ビューの問合せに使用して、アクティビティのステータスをチェックできます。

目的

WB_OLAP_AW_PRECOMPUTEは、圧縮されていないキューブの解決に使用します(圧縮されたキューブは自動解決されます)。ロードおよび解決のステップは、別々に実行できます。デフォルトでは、キューブ・マップでデータをロードしてから、キューブを解決(事前計算)します。このマップを使用してデータをロードしてから、別の時点で解決を実行できます(解決と構築を同時に行うのは、最も時間がかかる操作であるため)。

次の例では、リレーショナル・キューブMARTおよびSALES_CUBEからキューブSALESにデータをロードし、逐次に動作する単純な解決を実行します。この例では、パラレル解決のパラメータおよびジョブ・キューの最大数のパラメータがあります。パラレル解決が実行されると、ASYNCHRONOUS解決ジョブが起動し、リターン・ファンクションを通じてマスター・ジョブIDが戻されます。

declare
  rslt varchar2(4000);
begin
…
  rslt :=wb_olap_aw_precompute('MART','SALES_CUBE','SALES');
…
end;
/

WB_OLAP_LOAD_CUBE

構文

wb_olap_load_cube::=WB_OLAP_LOAD_CUBE(olap_aw_owner, olap_aw_name,
        olap_cube_owner, olap_cube_name, olap_tgt_cube_name)

olap_aw_ownerは、アナリティック・ワークスペースを保有するデータベース・スキーマの名前です。olap_aw_nameは、キューブ・データを格納するアナリティック・ワークスペースの名前です。olap_cube_ownerは、関連するリレーショナル・キューブを保有するデータベース・スキーマの名前です。olap_cube_nameは、リレーショナル・キューブの名前です。olap_tgt_cube_nameは、アナリティック・ワークスペースに入っているキューブの名前です。

目的

WB_OLAP_LOAD_CUBEは、リレーショナル・キューブからアナリティック・ワークスペースにデータをロードします。これにより、キューブ・データを詳細に分析できます。これは、クローニング元のリレーショナル・キューブからアナリティック・ワークスペース・キューブにデータをロードするためのものです。これは、キューブをロードするためのDBMS_AWMパッケージにあるいくつかのプロシージャのラッパーです。

次の例では、リレーショナル・キューブSALES_CUBEからアナリティック・ワークスペースAW_WHのキューブAW_SALESにデータをロードします。

WB_OLAP_LOAD_CUBE('WH_TGT', 'AW_WH', 'WH_TGT', 'SALES_CUBE', 'AW_SALES')

WB_OLAP_LOAD_DIMENSION

構文

wb_olap_load_dimension::=WB_OLAP_LOAD_DIMENSION(olap_aw_owner, olap_aw_name,
        olap_dimension_owner, olap_dimension_name, olap_tgt_dimension_name)

olap_aw_ownerは、アナリティック・ワークスペースを保有するデータベース・スキーマの名前です。olap_aw_nameは、ディメンション・データを格納するアナリティック・ワークスペースの名前です。olap_dimension_ownerは、関連するリレーショナル・ディメンションを保有するデータベース・スキーマの名前です。olap_dimension_nameは、リレーショナル・ディメンションの名前です。olap_tgt_dimension_nameは、アナリティック・ワークスペースに入っているディメンションの名前です。

目的

WB_OLAP_LOAD_DIMENSIONは、リレーショナル・ディメンションからアナリティック・ワークスペースにデータをロードします。これにより、ディメンション・データを詳細に分析できます。これは、クローニング元のリレーショナル・ディメンションからアナリティック・ワークスペース・ディメンションにデータをロードするためのものです。これは、ディメンションをロードするためのDBMS_AWMパッケージにあるいくつかのプロシージャのラッパーです。

次の例では、リレーショナル・ディメンションTIME_DIMからアナリティック・ワークスペースAW_WHのディメンションAW_TIMEにデータをロードします。

WB_OLAP_LOAD_DIMENSION('WH_TGT', 'AW_WH', 'WH_TGT', 'TIME_DIM', 'AW_TIME')

WB_OLAP_LOAD_DIMENSION_GENUK

構文

wb_olap_load_dimension_genuk::=WB_OLAP_LOAD_DIMENSION_GENUK(olap_aw_owner, 
        olap_aw_name, olap_dimension_owner, olap_dimension_name,
        olap_tgt_dimension_name)

olap_aw_ownerは、アナリティック・ワークスペースを保有するデータベース・スキーマの名前です。olap_aw_nameは、ディメンション・データを格納するアナリティック・ワークスペースの名前です。olap_dimension_ownerは、関連するリレーショナル・ディメンションを保有するデータベース・スキーマの名前です。olap_dimension_nameは、リレーショナル・ディメンションの名前です。olap_tgt_dimension_nameは、アナリティック・ワークスペースに入っているディメンションの名前です。

目的

WB_OLAP_LOAD_DIMENSION_GENUKは、リレーショナル・ディメンションからアナリティック・ワークスペースにデータをロードします。これにより、すべてのレベルで一意のディメンション識別子が生成されます。これは、クローニング元のリレーショナル・ディメンションからアナリティック・ワークスペース・ディメンションにデータをロードするためのものです。これは、ディメンションをロードするためのDBMS_AWMパッケージにあるいくつかのプロシージャのラッパーです。

キューブがクローニングされた場合、「ディメンションのサロゲート・キーを生成」オプションで「はい」を選択すると、ディメンションを再ロードするときに、WB_OLAP_LOAD_DIMENSION_GENUKプロシージャを使用する必要があります。このプロシージャでは、アナリティック・ワークスペースのすべてのレベルでサロゲート識別子が生成されます。これは、アナリティック・ワークスペースでは、すべてのレベルの識別子が、すべてのレベルのディメンションで一意である必要があるためです。

キューブのクローニングによって、ディメンションTIME_DIMがOLAP Serverに配布されている場合を考えます。パラメータ「ディメンションのサロゲート・キーを生成」がTRUEに設定されているとします。ここで、リレーショナル・ディメンションTIME_DIMからアナリティック・ワークスペースAW_WHのディメンションAW_TIMEにデータを再ロードするには、次の構文を使用します。

WB_OLAP_LOAD_CUBE('WH_TGT', 'AW_WH', 'WH_TGT', 'TIME_DIM', 'AW_TIME')

その他の変換

Warehouse Builderには、特定のデータ型に制限されない様々なファンクションを実行できる変換も組み込まれています。この項では、このようなタイプの変換について説明します。Warehouse Builderで提供されるその他の変換は、次のとおりです。

DEPTH

構文

depth::=DEPTH(correlation_integer)

目的

DEPTHは、補助ファンクションであり、UNDER_PATH条件およびEQUALS_PATH条件でのみ使用されます。DEPTHは、同じ相関変数を持つUNDER_PATH条件で指定されたパスのレベル番号を戻します。correlation_integerは、任意のNUMBER整数です。この文が複数の1次条件を含む場合、この補助ファンクションと1次条件を関連付けるために使用します。1より小さい値は、1として処理されます。

EQUALS_PATHおよびUNDER_PATH条件では、2つの補助ファンクションDEPTHおよびPATHを使用できます。次の例では、両方の補助ファンクションの使用方法を示します。この例では、XMLSchema warehouses.xsdが存在することを想定しています。

SELECT PATH(1), DEPTH(2)
   FROM RESOURCE_VIEW
   WHERE UNDER_PATH(res, '/sys/schemas/OE', 1)=1
     AND UNDER_PATH(res, '/sys/schemas/OE', 2)=1;

PATH(1)                          DEPTH(2)
-------------------------------- --------
/www.oracle.com                         1

/www.oracle.com/xwarehouses.xsd         2

DUMP

構文

dump::=DUMP(expr,return_fmt,start_position,length)

目的

DUMPは、データ型コード、バイト長およびexprの内部表現を含むVARCHAR2値を戻します。結果は、常にデータベース・キャラクタ・セットに戻されます。引数return_fmtを使用して戻り値の書式を指定し、値は次のいずれかにできます。

  • 8を指定すると、結果が8進数で戻されます。

  • 10を指定すると、結果が10進数で戻されます。

  • 16を指定すると、結果が16進数で戻されます。

  • 17を指定すると、結果が文字で戻されます。

デフォルトでは、戻り値にはキャラクタ・セット情報が含まれません。exprのキャラクタ・セット名を取得するには、前述の書式の値に1000を加えます。たとえば、return_fmtに1008を指定すると、結果が8進数で戻され、exprのキャラクタ・セット名を取得できます。

引数start_positionlengthの組合せにより、内部表現の戻される部分が決定されます。デフォルトでは、内部表現全体を10進数で戻します。exprがNULLの場合、このファンクションはNULLを戻します。


注意:

このファンクションは、CLOBデータを直接サポートしません。ただし、CLOBデータは、暗黙的なデータ変換により、引数として渡すことができます。

次の例では、文字列式と列からダンプ情報を抽出する方法を示します。

SELECT DUMP('abc', 1016)
   FROM DUAL;

DUMP('ABC',1016)                          
------------------------------------------ 
Typ=96 Len=3 CharacterSet=WE8DEC: 61,62,63 

SELECT DUMP(last_name, 8, 3, 2) "OCTAL"
   FROM employees
   WHERE last_name = 'Hunold';

OCTAL
-------------------------------------------------------------------
Typ=1 Len=6: 156,157

SELECT DUMP(last_name, 10, 3, 2) "ASCII"
   FROM employees
   WHERE last_name = 'Hunold';

ASCII
--------------------------------------------------------------------
Typ=1 Len=6: 110,111

EMPTY_BLOB, EMPTY_CLOB

構文

empty_blob::=EMPTY_BLOB()
empty_clob::=EMPTY_CLOB()

目的

EMPTY_BLOBおよびEMPTY_CLOBは、LOB変数を初期化するために使用する空のLOBロケータを戻します。また、INSERT文またはUPDATE文では、LOBの列や属性をEMPTYに初期化するために使用する空のLOBロケータを戻します。EMPTYとは、LOBは初期化されているが、データは入力されていないことを意味します。オブジェクト・タイプの一部であるLOB属性を初期化してから、LOBにアクセスしデータを入力する必要があります。


注意:

このファンクションから戻されたロケータは、DBMS_LOBパッケージまたはOCIへのパラメータとして使用できません。

次の例では、サンプル表pm.print_mediaのad_photo列をEMPTYに初期化します。

UPDATE print_media SET ad_photo = EMPTY_BLOB();

NLS_CHARSET_DECL_LEN

構文

nls_charset_decl_len::=NLS_CHARSET_DECL_LEN(byte_count,charset_id)

目的

NLS_CHARSET_DECL_LENは、NCHAR列の宣言幅(文字数)を戻します。引数byte_countは列の幅で、引数charset_idは列のキャラクタ・セットIDです。

次の例では、マルチバイト・キャラクタ・セットを使用するとき、200バイトの列にある文字数を戻します。

SELECT NLS_CHARSET_DECL_LEN(200, nls_charset_id('ja16eucfixed')) FROM DUAL;

NLS_CHARSET_DECL_LEN(200,NLS_CHARSET_ID('JA16EUCFIXED'))
----------------------------------------------------------
                                         100

NLS_CHARSET_ID

構文

nls_charset_id::= NLS_CHARSET_ID(text)

目的

NLS_CHARSET_IDは、キャラクタ・セット名textに対応するキャラクタ・セットID番号を戻します。引数textは、ランタイムのVARCHAR2値です。text'CHAR_CS'はサーバーのデータベース・キャラクタ・セットID番号を戻します。text'NCHAR_CS'はサーバーの各国語キャラクタ・セットID番号を戻します。

キャラクタ・セット名が無効な場合は、NULLが戻されます。

次の例では、キャラクタ・セットのキャラクタ・セットID番号を戻します。

SELECT NLS_CHARSET_ID('ja16euc') FROM DUAL;

NLS_CHARSET_ID('JA16EUC')
--------------------------
                       830

NLS_CHARSET_NAME

構文

nls_charset_name::= NLS_CHARSET_NAME(number)

目的

NLS_CHARSET_NAMEは、ID numberに対応するキャラクタ・セットの名前を戻します。キャラクタ・セット名は、データベース・キャラクタ・セットの中のVARCHAR2値として戻されます。numberが有効なキャラクタ・セットIDとして認識されない場合、このファンクションはNULLを戻します。

次の例では、キャラクタ・セットID番号2に対応するキャラクタ・セットを戻します。

SELECT NLS_CHARSET_NAME(2) FROM DUAL;

NLS_CH
--------
WE8DEC

NULLIF

構文

nullif::=NULLIF(expr1,expr2)

目的

NULLIFは、expr1expr2を比較します。2つが等しい場合、NULLが戻されます。2つが等しくない場合、expr1が戻されます。expr1にはリテラルNULLを指定できません。2つの引数が数値データ型の場合、Oracleデータベースによってより高い数値の優先度を持つ引数が決定され、暗黙的にもう1つの引数がそのデータ型に変換されて、そのデータ型が戻されます。引数が数値ではない場合は同じデータ型である必要があり、そうでない場合はOracleによってエラーが戻されます。NULLIFファンクションは、論理的には次のCASE式と同じです。

CASE WHEN expr1 = expr 2 THEN NULL ELSE expr1 END

次の例では、サンプル・スキーマhrから、入社時から職務が変わった従業員を選択します。job_history表のjob_idemployees表の現在のjob_idが異なる場合、職務が変わったことを示します。

SELECT e.last_name, NULLIF(e.job_id, j.job_id) "Old Job ID"
   FROM employees e, job_history j
   WHERE e.employee_id = j.employee_id
   ORDER BY last_name;

LAST_NAME                 Old Job ID
------------------------- ----------
De Haan                   AD_VP
Hartstein                 MK_MAN
Kaufling                  ST_MAN
Kochhar                   AD_VP
Kochhar                   AD_VP
Raphaely                  PU_MAN
Taylor                    SA_REP
Taylor
Whalen                    AD_ASST
Whalen

NVL

構文

nvl::=NVL(attribute1, attribute2)

目的

attribute1がNULLの場合、NVLattribute2を戻します。attribute1がNULLでない場合、NVLattribute1を戻します。引数attribute1およびattribute2は、任意のデータ型にできます。2つのデータ型が異なる場合、attribute2attribute1のデータ型に変換されてから比較されます。Warehouse Builderには、すべての入力値をサポートできるように、NVLのバリアントが3つ用意されています。

attribute1が文字データでないかぎり、戻り値のデータ型は、常にattribute1と同じデータ型になります。文字データの場合、戻り値のデータ型は、attribute1のキャラクタ・セットのVARCHAR2になります。

次の例では、従業員名と歩合給のリストを戻しますが、従業員が歩合給を受け取っていない場合、歩合給は「Not Applicable」になります。

SELECT last_name, NVL(TO_CHAR(commission_pct), 'Not Applicable') "COMMISSION" 
FROM employees
WHERE last_name LIKE 'B%';

LAST_NAME                 COMMISSION
------------------------- ----------------------------------------
Baer                      Not Applicable
Baida                     Not Applicable
Banda                     .11
Bates                     .16
Bell                      Not Applicable
Bernstein                 .26
Bissot                    Not Applicable
Bloom                     .21
Bull                      Not Applicable

NVL2

構文

nvl2::=NVL2(expr1,expr2,expr3)

目的

NVL2は、指定された式がNULLかNULLではないかに基づいて、問合せから戻された値を判別します。expr1がNULLではない場合、NVL2expr2を戻します。expr1がNULLの場合、NVL2expr3を戻します。引数expr1は任意のデータ型にできます。引数expr2およびexpr3は、LONG以外の任意のデータ型にできます。

expr2expr3のデータ型が異なる場合:

  • expr2が文字データの場合、expr3がNULL定数でないかぎり、Oracleデータベースは、expr3expr2のデータ型に変換してから比較します。その場合、データ型の変換は不要です。expr2のキャラクタ・セットでVARCHAR2が戻されます。

  • expr2が数値の場合、数値の優先度の最も高い引数が判定され、もう一方の引数がこのデータ型に暗黙的に変換されて、そのデータ型が戻されます。

次の例では、指定した従業員の収入が固定給+歩合給であるか、固定給のみであるかを示します。歩合給の有無は、employeescommission_pct列がNULLかどうかで判断します。

SELECT last_name, salary, NVL2(commission_pct,
   salary + (salary * commission_pct), salary) income
   FROM employees WHERE last_name like 'B%'
   ORDER BY last_name;

LAST_NAME                     SALARY     INCOME
------------------------- ---------- ----------
Baer                           10000      10000
Baida                           2900       2900
Banda                           6200       6882
Bates                           7300       8468
Bell                            4000       4000
Bernstein                       9500      11970
Bissot                          3300       3300
Bloom                          10000      12100
Bull                            4100       4100

ORA_HASH

構文

ora_hash::=ORA_HASH(expr,max_bucket,seed_value)

目的

ORA_HASHは、指定された式のハッシュ値を計算するファンクションです。このファンクションは、データのサブセットの分析またはランダム・サンプルを生成する演算に役立ちます。このファンクションは、NUMBER値を戻します。

引数exprで、Oracleデータベースがハッシュ値を計算するデータを指定します。exprで表されるデータの型や長さに制限はありません。通常は、列名に解決されるものを指定します。引数max_bucketはオプションで、ハッシュ・ファンクションによって戻される最大バケット値を指定します。0から4294967295の任意の値を指定できます。デフォルト値は4294967295です。オプションの引数seed_valueにより、同じデータのセットに対し、多数の異なる結果を作成できます。このハッシュ・ファンクションは、exprseed_valueの組合せに適用されます。0から4294967295の任意の値を指定できます。デフォルト値は0です。

次の例では、sh.sales表の顧客IDと製品IDの組合せごとにハッシュ値を作成し、このハッシュ値を最大100のバケットに分割して、最初のバケット(バケット0)のamount_soldの合計値を戻します。3つ目の引数(5)で、ハッシュ・ファンクションのシード値を指定します。このシード値を変更することにより、同じ問合せに対して別のハッシュ結果を取得できます。

SELECT SUM(amount_sold) FROM sales
   WHERE ORA_HASH(CONCAT(cust_id, prod_id), 99, 5) = 0;

SUM(AMOUNT_SOLD)
----------------
            7315

次の例では、10のバケット(0から9)を指定してからバケット1のデータを戻すことにより、sh.sales表のデータのサブセットを取得します。予想されるサブセットは、全行の約10%です(sh.sales表は960行あります)。

SELECT * FROM sales
   WHERE ORA_HASH(cust_id, 9) = 1;

   PROD_ID    CUST_ID TIME_ID   C   PROMO_ID QUANTITY_SOLD AMOUNT_SOLD
---------- ---------- --------- - ---------- ------------- -----------
      2510       6950 01-FEB-98 S       9999             2          78
      9845       9700 04-FEB-98 C       9999            17         561
      3445      33530 07-FEB-98 T       9999             2         170
. . .
       740      22200 13-NOV-00 S       9999             4         156
      9425       4750 29-NOV-00 I       9999            11         979
      1675      46750 29-NOV-00 S       9999            19        1121

PATH

構文

path::=PATH(correlation_integer)

目的

PATHは補助ファンクションであり、UNDER_PATH条件およびEQUALS_PATH条件でのみ使用されます。PATHは、親条件で指定されたリソースへの相対パスを戻します。correlation_integerは任意のNUMBER整数で、補助ファンクションと1次条件を関連付けるために使用します。1より小さい値は、1として処理されます。

「DEPTH」の例を参照してください。この例では、EQUALS_PATHとUNDER_PATH両方の補助ファンクションを使用しています。

SYS_CONTEXT

構文

sys_context::=SYS_CONTEXT(namespace,parameter,length)

目的

SYS_CONTEXTは、コンテキストのnamespaceに関連付けられたパラメータ値を戻します。このファンクションは、SQL文とPL/SQL文の両方に使用できます。namespaceparameterについては、ネームスペースまたは属性を指定する文字列になる文字列または式のいずれかを指定できます。コンテキストのnamespaceがすでに作成されていることと、関連するparameterとその値もDBMS_SESSION.set_contextプロシージャを使用して設定されていることが必要です。namespaceは、有効なSQL識別子である必要があります。parameter名は任意の文字列です。文字列は大文字と小文字を区別しませんが、長さは30バイトを超えることはできません。戻り値のデータ型はVARCHAR2です。戻り値のデフォルトの最大サイズは256バイトです。オプションのlengthパラメータを指定して、このデフォルトに上書きできます。この値は、NUMBERまたは暗黙的にNUMBERに変換される値である必要があります。有効な値の範囲は、1から4000バイトです。無効な値を指定すると、Oracleデータベースによってその値は無視され、デフォルトの値が使用されます。Oracleでは、現在のセッションを記述するUSERENVと呼ばれる組込みネームスペースが用意されています。ネームスペースUSERENVの事前定義済のパラメータの詳細は、Oracle Database SQLリファレンスの表7-11を参照してください。

次の文では、データベースにログオンしたユーザーの名前を戻します。

CONNECT OE/OE
SELECT SYS_CONTEXT ('USERENV', 'SESSION_USER') 
   FROM DUAL;

SYS_CONTEXT ('USERENV', 'SESSION_USER')
------------------------------------------------------
OE

次の仮説の例では、hr_appsが作成されたときに、PL/SQLパッケージで、コンテキストhr_appsに関連付けられた属性group_noの値として設定されたグループ番号を戻します。

SELECT SYS_CONTEXT ('hr_apps', 'group_no') "User Group" 
   FROM DUAL;

SYS_GUID

構文

sys_guid::=SYS_GUID()

目的

SYS_GUIDは、16バイトで構成されるグローバルに一意な識別子(RAW値)を生成して戻します。多くのプラットフォームで生成される識別子は、ホスト識別子、ファンクションを起動するプロセスやスレッドのプロセス識別子またはスレッド識別子、およびそのプロセスやスレッドの非反復値(バイトのシーケンス)から構成されます。

次の例では、サンプル表hr.locationsに列を追加し、一意の識別子を各行に挿入して、グローバルに一意な識別子の16バイトRAW値を32文字の16進数で戻します。

ALTER TABLE locations ADD (uid_col RAW(32));

UPDATE locations SET uid_col = SYS_GUID();

SELECT location_id, uid_col FROM locations;

LOCATION_ID UID_COL
----------- ----------------------------------------
       1000 7CD5B7769DF75CEFE034080020825436
       1100 7CD5B7769DF85CEFE034080020825436
       1200 7CD5B7769DF95CEFE034080020825436
       1300 7CD5B7769DFA5CEFE034080020825436
. . .

SYS_TYPEID

構文

sys_typeid::=SYS_TYPEID(object_type_value)

目的

SYS_TYPEIDは、オペランドの最も特殊なタイプであるtypeidを戻します。この値は、代用可能な列の基礎となるtype-discriminant列を識別するために主に使用されます。たとえば、SYS_TYPEIDによって戻された値を使用して、type-discriminant列に索引を作成できます。SYS_TYPEIDファンクションは、表のtype-discriminant列の索引の作成に使用できます。このファンクションは、オブジェクト・タイプのオペランドにのみ使用できます。すべての最終ルートのオブジェクト・タイプ(すなわち、タイプ階層に属していない最終タイプ)は、NULLのtypeidを持ちます。Oracleデータベースでは、タイプ階層に属するすべてのタイプに一意のNULL以外のtypeidが割り当てられています。

UID

構文

uid::=UID()

目的

UIDは変換を含むセッションの実行時にログインするユーザーなど、セッション・ユーザーを一意に識別する整数を戻します。分散SQL文の場合、UIDファンクションによって、ローカル・データベースのユーザーを識別します。

監査情報のログをターゲット表に記録し、マッピングを実行しているユーザーを識別する場合、このファンクションを使用します。

次の例では、このセッションにログインしたユーザーのローカル・データベース・ユーザーIDが戻されます。

SELECT uid FROM dual;

       UID
----------
        55

USER

構文

user::=USER()

目的

USERは(ログインしている)セッション・ユーザーの名前をVARCHAR2データ型で戻します。

Oracleはこのファンクションの値を空白埋め比較方法で比べます。分散SQL文の場合、UIDUSERファンクションによってローカル・データベースのユーザーを識別します。

監査情報のログをターゲット表に記録し、マッピングを実行しているユーザーを識別する場合、このファンクションを使用します。

次の例では、このセッションにログイン中のローカル・データベース・ユーザーが戻されます。

SELECT user FROM dual;

USER
------------------------------
OWB9I_RUN

USERENV

構文

userenv::=USERENV(parameter)

目的


注意:

USERENVは、下位互換性のために保持されるレガシー・ファンクションです。Oracleでは、現在の機能のため組込みのUSERENVネームスペースを持つSYS_CONTEXTファンクションの使用をお薦めします。

USERENVは、現行セッションの情報を返します。この情報は、アプリケーション固有の監査証跡表を書き込む場合や、現行のユーザー・セッションで使用されている言語固有の文字を特定する場合に便利です。CHECK制約の条件にUSERENVは使用できません。表27-1はparameter引数の値についての説明です。USERENVをコールすると、必ずVARCHAR2データが返されます。ただし、SESSIONID、ENTRYID、COMMITSCNの各パラメータを指定してコールした場合は、NUMBERが返されます。

表27-1 USERENVファンクションのパラメータ

パラメータ 戻り値

CLIENT_INFO

CLIENT_INFOは、最大64バイトのユーザー・セッション情報を戻します。これは、DBMS_APPLICATION_INFOパッケージを使用するアプリケーションによって格納できます。

注意: 一部の商用アプリケーションでは、このコンテキスト値を使用している場合があります。それらのアプリケーションの関連ドキュメントを参照して、このコンテキスト領域の使用に対する制限を確認してください。

ENTRYID

現在の監査エントリ番号です。監査エントリIDのシーケンスは、詳細監査レコードと通常監査レコードで共有されています。この属性は、分散SQL文では使用できません。

ISDBA

ISDBAは、オペレーティング・システムまたはパスワード・ファイルを通じて、ユーザーにDBA権限があると認証された場合、TRUEを戻します。

LANG

LANGは、言語名のISO省略形を戻します。これは、既存のLANGUAGEパラメータより短い書式です。

LANGUAGE

LANGUAGEは、現行セッションのデータベース・キャラクタ・セットで使用されている言語と地域を、language_territory.charactersetという書式で戻します。

SESSIONID

SESSIONIDは、監査セッション識別子を戻します。このパラメータは、分散SQL文では指定できません。

TERMINAL

TERMINALは、現行セッションの端末に対するオペレーティング・システム識別子を戻します。分散SQL文では、このパラメータにより、ローカル・セッションの識別子が戻されます。分散環境では、このパラメータはリモートSELECT文でのみサポートされ、INSERT、UPDATE、DELETEの各リモート演算ではサポートされません。


次の例では、現行セッションのLANGUAGEパラメータを戻します。

SELECT USERENV('LANGUAGE') "Language" FROM DUAL;

Language
-----------------------------------
AMERICAN_AMERICA.WE8ISO8859P1

VSIZE

構文

vsize::=VSIZE(expr)

目的

VSIZEは、exprの内部表現のバイト数を戻します。exprがNULLの場合、このファンクションはNULLを戻します。このファンクションは、CLOBデータを直接サポートしません。ただし、CLOBデータは、暗黙的なデータ変換により、引数として渡すことができます。

次の例では、部門10に属している従業員のlast_name列のバイト数を戻します。

SELECT last_name, VSIZE (last_name) "BYTES"      
  FROM employees
  WHERE department_id = 10;
 
LAST_NAME            BYTES
--------------- ----------
Whalen                   6

空間変換

空間変換は、ファンクションとプロシージャを統合したもので、これによって、Oracleデータベースで空間データを迅速かつ効率的に格納、アクセスおよび分析できます。Warehouse Builderで使用できる空間変換は、次のとおりです。

SDO_AGGR_CENTROID

構文

sdo_aggr_centroid::= SDO_AGGR_CENTROID(AggregateGeometry SDOAGGRTYPE)

目的

SDO_AGGR_CENTROIDは、指定されたジオメトリ・オブジェクトの重心(重力の中心)であるジオメトリ・オブジェクトを戻します。このファンクションの動作は、ジオメトリ・オブジェクトのすべてがポリゴンまたはポイントであるか、ポリゴンとポイントの組合せであるかによって異なります。

  • ジオメトリ・オブジェクトがすべてポリゴンの場合、すべてのオブジェクトの重心が戻されます。

  • ジオメトリ・オブジェクトがすべてポイントの場合、すべてのオブジェクトの重心が戻されます。

  • ジオメトリ・オブジェクトがポリゴンとポイントの組合せの場合(具体的には、最低1つのポリゴンと最低1つのポイントが含まれる場合)、いずれのポイントも無視され、すべてのポリゴンの重心が戻されます。

結果は、ジオメトリ・オブジェクトの各ポリゴンの領域によって加重計算されます。ジオメトリ・オブジェクトがポリゴンとポイントの組合せの場合、ポイントは重心の計算に使用されません。ジオメトリ・オブジェクトがすべてポイントの場合、ポイントの重量は等しくなります。

次の例では、COLA_MARKETS表のジオメトリ・オブジェクトの重心が戻されます。

SELECT SDO_AGGR_CENTROID(SDOAGGRTYPE(shape, 0.005))
  FROM cola_markets;

SDO_AGGR_CENTROID(SDOAGGRTYPE(SHAPE,0.005))(SDO_GTYPE, SDO_SRID, SDO_POINT
--------------------------------------------------------------------------------
SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(5.21295938, 5.00744233, NULL), NULL, NULL)

SDO_AGGR_CONVEXHULL

構文

sod_aggr_convexhull::= SDO_AGGR_CONVEXHULL(AggregateGeometry SDOAGGRTYPE)

目的

SDO_AGGR_CONVEXHULLは、指定されたジオメトリ・オブジェクトの凸型となるジオメトリ・オブジェクトを戻します。

次の例では、COLA_MARKETS表のジオメトリ・オブジェクトの凸型が戻されます。

SELECT SDO_AGGR_CONVEXHULL(SDOAGGRTYPE(shape, 0.005))
  FROM cola_markets;

SDO_AGGR_CONVEXHULL(SDOAGGRTYPE(SHAPE,0.005))(SDO_GTYPE, SDO_SRID,
SDO_POI
-------------------------------------------------------------------
-------------
SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1),
SDO_ORDINATE_ARRAY(8, 1, 10, 7, 10, 11, 8, 11, 6, 11, 1, 7, 1, 1, 8, 1))

SDO_AGGR_MBR

構文

sod_aggr_mbr::= SDO_AGGR_MBR(geom SDO_GEOMETRY)

目的

SDO_AGGR_MBRは、指定されたジオメトリの最小外接矩形(MBR)、つまりジオメトリを最小で包囲する単一の長方形を戻します。

次の例では、COLA_MARKETS表のジオメトリ・オブジェクトの最小外接矩形が戻されます。

SELECT SDO_AGGR_MBR(shape) FROM cola_markets;

SDO_AGGR_MBR(C.SHAPE)(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SD
-------------------------------------------------------------------------------
SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 3), SDO_ORDINATE_ARRAY(1, 1, 10, 11))

SDO_AGGR_UNION

構文

SDO_AGGR_UNION(

     AggregateGeometry SDOAGGRTYPE

     ) RETURN SDO_GEOMETRY;

目的

SDO_AGGR_UNIONは、指定されたジオメトリ・オブジェクトの位相結合(OR演算)であるジオメトリ・オブジェクトを戻します。

次の例では、COLA_MARKETS表の最初の3つのジオメトリ・オブジェクト(cola_d以外のすべて)の結合が戻されます。

SELECT SDO_AGGR_UNION(
  SDOAGGRTYPE(c.shape, 0.005))
  FROM cola_markets c
  WHERE c.name < 'cola_d';

SDO_AGGR_UNION(SDOAGGRTYPE(C.SHAPE,0.005))(SDO_GTYPE, SDO_SRID, SDO_POINT(
-------------------------------------------------------------------------------
SDO_GEOMETRY(2007, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 2, 11, 1003, 1), SDO
_ORDINATE_ARRAY(8, 11, 6, 9, 8, 7, 10, 9, 8, 11, 1, 7, 1, 1, 5, 1, 8, 1, 8, 6, 5,
7, 1, 7))

ストリーム変換

ストリーム変換のカテゴリには、REPLICATEという変換が1つあります。次の項では、この変換について説明します。

REPLICATE

構文

REPLICATE(lcr, conflict_resolution)

lcrは、Logical Change Record(論理変更レコード)の略で、DML変更をカプセル化します。そのデータ型はSYS.LCR$_ROW_RECORDです。conflict_resolutionは、ブール変数です。この値がTRUEの場合、表に定義された競合解消方法のいずれかを使用して、LCRの実行に起因する競合を解消します。競合解消の詳細は、Oracle Streamsレプリケーション管理者ガイドを参照してください。

目的

REPLICATEを使用して、ソース・システムの表で発生したDML変更(INSERT、UPDATEまたはDELETE)を、ターゲット・システムの同じ表にレプリケートします。ターゲット・システムの表は、次の点でソース・システムの表と同一である必要があります。

  • ターゲット表を含むスキーマの名前は、ソース表を含むスキーマの名前と同じである必要があります。

  • ターゲット表の名前は、ソース表の名前と同じである必要があります。

  • ターゲット表の構造は、ソース表の構造と同じである必要があります。構造には、数、名前、表の列のデータ型が含まれます。

ソース・システムのスキーマSには表T1(c1 varchar2(10), c2 number primary key)があり、ターゲット・システムにも同一の表があるとします。ソース・システムの表T1で、次の挿入操作をするとします。

insert into T1 values ('abcde', 10)

ソース・システムの表T1で前述の行挿入を行った後の変更を表すLCRの詳細は、次のとおりです。

LCR.GET_OBJECT_OWNER will be 'S'
LCR.GET_OBJECT_NAME will be 'T1'
LCR.GET_COMMAND_TYPE will be 'INSERT'
LCR.GET_VALUE('c1', 'new') will have the value for the column 'c1', that is, 'abcde'
LCR.GET_VALUE('c2', 'new') will have the value for the column 'c2', that is, 10

このようなLCRは、ソース・システムでの表S.T1の変更を取り込むストリーム・キャプチャ・プロセスによって作成され、エンキューされます。

REPLICATE(lcr, true) - ターゲット・システムの表T1に、行('abcde', 10)が挿入されます。


注意:

このアプローチを使用すると、系統情報を取得できません。系統が重要な場合、このファンクションは使用しないでください。ソース表にバインドされたLCRCast演算子とターゲット表にバインドされた表演算子を使用して、これらの2つの演算子の属性を同じ名前(名前による一致)で接続する、より直接的なアプローチを使用します。LCR(論理変更レコード)の詳細は、Oracle Database 10gのドキュメント(情報統合)を参照してください。

XML変換

Warehouse Builderユーザーは、XML変換により、XMLオブジェクトで変換を実行できます。Warehouse Builderユーザーは、この変換により、XML文書とOracle AQをロードおよび変換できます。

XMLソースのロードを可能にするため、Warehouse Builderでは、この章で詳しく説明されているカスタム・ファンクションを使用して、データベースのXML機能にアクセスします。

XML変換の内容は次のとおりです。

EXISTSNODE

構文

existsnode::=EXISTSNODE(XMLType_instance,XPath_string,namespace_string)

目的

EXISTSNODEは、指定されたパスを使用するXML文書のトラバースが任意のノードとなるかどうかを決定します。このファンクションは、XML文書を含むXMLTypeインスタンスとパスを指定するVARCHAR2のXPath文字列を、引数として受け取ります。オプションのnamespace_stringは、デフォルトのマッピングまたは接頭辞のネームスペース・マッピングを指定するVARCHAR2値になる必要があります。この値は、OracleデータベースでXPath式を評価するときに使用されます。このファンクションはNUMBER値を戻します。文書にXPathトラバースを適用した後にノードが残っていない場合は、0を戻します。ノードが残っている場合は、1を戻します。

次の例では、サンプル表oe.warehouseswarehouse_spec列のXMLパスに、/Warehouse/Dockノードが存在するかテストします。

SELECT warehouse_id, warehouse_name
   FROM warehouses
   WHERE EXISTSNODE(warehouse_spec, '/Warehouse/Docks') = 1;

WAREHOUSE_ID WAREHOUSE_NAME
------------ -----------------------------------
           1 Southlake, Texas
           2 San Francisco
           4 Seattle, Washington

EXTRACT

構文

extract::=EXTRACT(XMLType_instance,XPath_string,namespace_string)

目的

EXTRACTEXISTSNODEファンクションに類似しています。このファンクションは、VARCHAR2のXPath文字列に適用し、XMLの断片を含むXMLTypeインスタンスを戻します。オプションのnamespace_stringは、デフォルトのマッピングまたは接頭辞のネームスペース・マッピングを指定するVARCHAR2値になる必要があります。この値は、OracleデータベースがXPath式を評価するときに使用します。

次の例では、サンプル表oe.warehouseswarehouse_spec列のXMLパスにある/Warehouse/Dockノードの値を抽出します。

SELECT warehouse_name, EXTRACT(warehouse_spec, '/Warehouse/Docks')
   "Number of Docks"
   FROM warehouses
   WHERE warehouse_spec IS NOT NULL;

WAREHOUSE_NAME       Number of Docks
-------------------- --------------------
Southlake, Texas          <Docks>2</Docks>
San Francisco             <Docks>1</Docks>
New Jersey                <Docks/>
Seattle, Washington       <Docks>3</Docks>

EXTRACTVALUE

構文

extractvalue::=EXTRACTVALUE(XMLType_instance,XPath_string,namespace_string)

目的

EXTRACTVALUEファンクションは、引数としてXMLTypeインスタンスとXPath式を受け取り、結果ノードのスカラー値を戻します。結果は、シングル・ノードと、テキスト・ノード、属性または要素のいずれかになります。結果が要素である場合、要素は子としてシングル・テキスト・ノードを持つ必要があり、ファンクションから戻される値になります。指定されたXPathが複数の子ノードを持つノードを指す場合、またはその指し示されたノードが非テキスト・ノードの子ノードを持つ場合は、Oracleによってエラーが戻されます。オプションのnamespace_stringは、デフォルトのマッピングや接頭辞のネームスペース・マッピングを指定するVARCHAR2値になる必要があります。この値は、OracleがXPath式を評価するときに使用します。XMLスキーマをベースとした文書では、Oracleで戻り値の型を判断できる場合、適切な型のスカラー値を戻します。それ以外の場合の結果はVARCHAR2型になります。XMLスキーマをベースとしない文書の場合、戻り型は常にVARCHAR2になります。

次の例では、入力としてEXTRACTの例と同じ引数を受け取ります。XMLの断片を戻すかわりに、EXTRACTファンクションと同様、XMLの断片のスカラー値を戻します。

SELECT warehouse_name, 
   EXTRACTVALUE(e.warehouse_spec, '/Warehouse/Docks')
   "Docks"
   FROM warehouses e 
   WHERE warehouse_spec IS NOT NULL;

WAREHOUSE_NAME       Docks
-------------------- ------------
Southlake, Texas     2
San Francisco        1
New Jersey
Seattle, Washington  3

SYS_XMLAGG

構文

sys_xmlagg::=SYS_XMLAGG(expr,fmt)

目的

SYS_XMLAGGは、XML文書またはexprによって表現される断片すべてを集計し、単一のXML文書を作成します。また、デフォルト名ROWSETを持つ新規の囲み要素を追加します。XML文書を別の方法でフォーマットする場合は、XMLFormatオブジェクトのインスタンスであるfmtを指定します。

次の例では、SYS_XMLGENファンクションを使用して、サンプル表employeesの各行に対し、XML文書を生成します。ここでは姓がRで始まる従業員を取得し、すべての行を単一のXML文書に集計して、デフォルトの要素ROWSETで囲みます。

SELECT SYS_XMLAGG(SYS_XMLGEN(last_name))
   FROM employees
   WHERE last_name LIKE 'R%';

SYS_XMLAGG(SYS_XMLGEN(LAST_NAME))
--------------------------------------------------------------------
<ROWSET>
  <LAST_NAME>Raphaely</LAST_NAME>
  <LAST_NAME>Rogers</LAST_NAME>
  <LAST_NAME>Rajs</LAST_NAME>
  <LAST_NAME>Russell</LAST_NAME>
</ROWSET>

SYS_XMLGEN

構文

sys_xmlgen::=SYS_XMLGEN(expr,fmt)

目的

SYS_XMLGENは、データベース内の特定の行と列を評価する式を受け取り、XML文書を含むタイプXMLTypeのインスタンスを戻します。exprは、スカラー値、ユーザー定義済タイプまたはXMLTypeインスタンスです。exprがスカラー値の場合、ファンクションはスカラー値を含むXML要素を戻します。exprがタイプの場合、このファンクションはユーザー定義済タイプの属性をXML要素にマップします。exprがXMLTypeインスタンスの場合、デフォルトのタグ名がROWのXML要素で文書を囲みます。デフォルトでは、XML文書の要素はexprの要素と一致します。たとえば、exprが列名になる場合、囲むXML要素はその列と同じ名前になります。XML文書を別の方法でフォーマットする場合は、XMLFormatオブジェクトのインスタンスであるfmtを指定します。

次の例では、サンプル表oe.employeesから従業員の電子メールIDを検索します。ここでは、employee_idの値が205のレコードを取得し、EMAIL要素付きのXML文書を含むXMLTypeのインスタンスを生成します。

SELECT SYS_XMLGEN(email)
   FROM employees
   WHERE employee_id = 205;

SYS_XMLGEN(EMAIL)
-------------------------------------------------------------------
<EMAIL>SHIGGINS</EMAIL>

WB_XML_LOAD

構文:

WB_XML_LOAD(control_file)

目的

このプログラム・ユニットは、XML文書からデータを抽出し、そのデータをデータベース・ターゲットにロードします。control_file(XML文書)には、XML文書のソース、ターゲットおよびランタイム制御を指定します。変換を定義した後、Warehouse Builderでのマッピングでは、マッピング前トリガーまたはマッピング後トリガーとしてこの変換がコールされます。

次の例では、ファイルproducts.xmlに保存されているXML文書からデータを抽出し、そのデータをターゲット表booksにロードするWarehouse Builder変換の実装時に使用されるスクリプトを示します。

begin
wb_xml_load('<OWBXMLRuntime>' 
││
'<XMLSource>'
││
' <file>\ora817\GCCAPPS\products.xml</file>'
││
'</XMLSource>'
││
'<targets>'
││
' <target XSLFile="\ora817\XMLstyle\GCC.xsl">books</target>'
││
'</targets>'
││
'</OWBXMLRuntime>'
);
end;

制御ファイルの詳細は、Oracle Warehouse Builderユーザーズ・ガイドを参照してください。

WB_XML_LOAD_F

構文

WB_XML_LOAD_F(control_file)

目的

WB_XML_LOAD_Fは、XML文書からデータを抽出し、そのデータをデータベース・ターゲットにロードします。このファンクションは、ロード中に読み込まれたXML文書の数を戻します。control_file(XML文書)には、XML文書のソース、ターゲットおよびランタイム制御を指定します。変換を定義した後、Warehouse Builderでのマッピングでは、マッピング前トリガーまたはマッピング後トリガーとしてこの変換がコールされます。

次の例では、ファイルproducts.xmlに保存されているXML文書からデータを抽出し、そのデータをターゲット表booksにロードするWarehouse Builder変換の実装時に使用されるスクリプトを示します。

begin
wb_xml_load_f('<OWBXMLRuntime>' 
││
'<XMLSource>'
││
' <file>\ora817\GCCAPPS\products.xml</file>'
││
'</XMLSource>'
││
'<targets>'
││
' <target XSLFile="\ora817\XMLstyle\GCC.xsl">books</target>'
││
'</targets>'
││
'</OWBXMLRuntime>'
);
end;

処理されるタイプおよびcontrol_fileの詳細は、Oracle Warehouse Builderユーザーズ・ガイドを参照してください。

XMLCONCAT

構文

xmlconcat::=XMLCONCAT(XMLType_instance)

目的

XMLCONCATは、入力として一連のXMLTypeインスタンスを受け取り、各行の一連の要素を連結してそれを戻します。XMLCONCATは、逆XMLSEQUENCEです。Null式は、結果から削除されます。すべての式の値がNULLの場合は、NULLが戻されます。

次の例では、従業員のサブセットの姓と名のXML要素を作成し、それらの要素を連結して戻します。

SELECT XMLCONCAT(XMLELEMENT("First", e.first_name),
   XMLELEMENT("Last", e.last_name)) AS "Result"
   FROM employees e
   WHERE e.employee_id > 202;

Result
----------------------------------------------------------------
<First>Susan</First>
<Last>Mavris</Last>

<First>Hermann</First>
<Last>Baer</Last>

<First>Shelley</First>
<Last>Higgins</Last>

<First>William</First>
<Last>Gietz</Last>

XMLSEQUENCE

構文

xmlsequence:=xmlsequence(XMLType_instance XMLType)

目的

XMLSEQUENCEは、入力としてXMLTypeインスタンスを受け取り、XMLTypeのトップ・レベルのノードの可変長配列を戻します。このファンクションをTABLE句で使用して、コレクション値を複数の行にネスト解除できます。さらに、SQL問合せで処理することもできます。

次の例では、複数の要素を持つXML文書を、XMLSequenceによって可変長配列の単一要素の文書に分割します。TABLEキーワードにより、Oracleデータベースでは、コレクション値は副問合せのFROM句で使用可能な表の値とみなされます。

SELECT EXTRACT(warehouse_spec, '/Warehouse') as "Warehouse"
   FROM warehouses
   WHERE warehouse_name = 'San Francisco';

Warehouse
------------------------------------------------------------
<Warehouse?
  <Building>Rented</Building>
  <Area>50000</Area>
  <Docks>1</Docks>
  <DockType>Side load</DockType>
  <WaterAccess>Y</WaterAccess>
  <RailAccess>N</RailAccess>
  <Parking>Lot</Parking>
  <VClearance>12 ft</VClearance>
</Warehouse>


SELECT VALUE(p)
   FROM warehouses w,
   TABLE(XMLSEQUENCE(EXTRACT(warehouse_spec,'/Warehouse/*'))) p
   WHERE w.warehouse_name = 'San Francisco';

VALUE(p)
------------------------------------------------------------

<Building>Rented</Building>
<Area>50000</Area>
<Docks>1</Docks>
<DockType>Side load</DockType>
<WaterAccess>Y</WaterAccess>
<RailAccess>N</RailAccess>
<Parking>Lot</Parking>
<VClearance>12 ft</VClearance>

XMLTRANSFORM

構文

xmltransform::=XMLTRANSFORM(XMLType_instance,XMLType_instance)

目的

XMLTRANSFORMは、引数としてXMLTypeインスタンスとXMLTypeインスタンス形式であるXSLスタイル・シートを受け取ります。ファンクションは、スタイルシートをインスタンスに適用し、XMLTypeを戻します。このファンクションは、データベースからデータを取得する際に、スタイルシートに従ってデータを編成する場合に便利です。

XMLTRANSFORMファンクションでは、XSLスタイルシートが存在している必要があります。これは、ノード内の要素をアルファベット順に並べ替える、非常に単純なスタイルシートの例です。

CREATE TABLE xsl_tab (col1 XMLTYPE);

INSERT INTO xsl_tab VALUES (
   XMLTYPE.createxml(
   '<?xml version="1.0"?> 
    <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
 >
      <xsl:output encoding="utf-8"/>
      <!-- alphabetizes an xml tree -->  
      <xsl:template match="*">  
        <xsl:copy>
          <xsl:apply-templates select="*│text()">
            <xsl:sort select="name(.)" data-type="text" order="ascending"/>
          </xsl:apply-templates> 
        </xsl:copy> 
      </xsl:template>
      <xsl:template match="text()"> 
        <xsl:value-of select="normalize-space(.)"/>
      </xsl:template>
    </xsl:stylesheet>  '));

次の例では、xsl_tab XSLスタイルシートを使用して、サンプル表oe.warehousesの1つのwarehouse_specの要素をアルファベット順に並べ替えます。

SELECT XMLTRANSFORM(w.warehouse_spec, x.col1).GetClobVal()
   FROM warehouses w, xsl_tab x
   WHERE w.warehouse_name = 'San Francisco';

XMLTRANSFORM(W.WAREHOUSE_SPEC,X.COL1).GETCLOBVAL()
--------------------------------------------------------------------------------
<Warehouse>
  <Area>50000</Area>
  <Building>Rented</Building>
  <DockType>Side load</DockType>
  <Docks>1</Docks>
  <Parking>Lot</Parking>
  <RailAccess>N</RailAccess>
  <VClearance>12 ft</VClearance>
  <WaterAccess>Y</WaterAccess>
</Warehouse>