ヘッダーをスキップ
Oracle Warehouse Builderトランスフォーメーション・ガイド
10g リリース2(10.2)
B31257-01
  目次
目次
索引
索引

戻る
戻る
 
次へ
次へ
 

2 変換

マッピングとプロセス・フローを設計する場合、特化された変換を使用して、データを変換します。この章では、Warehouse Builderで提供されるすべての事前定義済変換について説明します。

この章は次の項で構成されます。それぞれの項では、該当するカテゴリ内のすべての事前定義済変換について詳細に説明します。

Administration変換

Administration変換には、ETLプロセスで定期的に実行されるアクションを実行するための、事前作成済の機能が用意されています。Administration変換は主にDBA関連の領域で実行され、パフォーマンスの改善に焦点があてられます。たとえば、多くの場合、表のロード時に無効にした制約は、ロード後に再び有効にする必要があります。

Warehouse BuilderのAdministration変換は、カスタム・ファンクションです。Warehouse Builderに用意されているAdministration変換は次のとおりです。

WB_ABORT

構文

WB_ABORT(p_code, p_message)

p_codeは強制終了コードで、-20000から-29999までの間にする必要があります。p_messageは、指定する強制終了メッセージです。

目的

WB_ABORTでは、Warehouse Builderコンポーネントのアプリケーションを強制終了できます。WB_ABORTはマッピング後プロセスから実行できます。また、マッピング内の変換として実行できます。

この管理ファンクションを使用して、アプリケーションを強制終了します。マッピングでエラーが発生した場合は、マッピング後プロセスでこのファンクションを使用して、配布を強制終了できます。

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

Character変換

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

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

ASCII

構文

ascii::=ASCII(attribute)

目的

ASCIIは、attributeに指定した最初の文字を、データベース・キャラクタ・セットの10進数表現に変換して返します。attributeには、データ型CHAR、VARCHAR2、NCHARまたはNVARCHAR2を指定できます。戻り値のデータ型はNUMBERです。データベース・キャラクタ・セットが7ビットASCIIの場合は、このファンクションはASCII値を返します。データベース・キャラクタ・セットがEBCDICコードの場合は、このファンクションはEBCDIC値を返します。対応するEBCDICキャラクタ・ファンクションはありません。

次の例では、文字QのASCII 10進数表現が返されます。

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

CHR

構文

chr::=CHR(attribute)

目的

CHRは、attributeに指定された数値と同等の2進数が表す文字を、データベース・キャラクタ・セットまたは各国語キャラクタ・セットのどちらかで返します。

USING NCHAR_CSを指定しない場合、このファンクションは、attributeと同等の2進数が表す文字を、データベース・キャラクタ・セットのVARCHAR2値として返します。Expression BuilderでUSING NCHAR_CSを指定すると、このファンクションは、attributeと同等の2進数が表す文字を各国語キャラクタ・セットのNVARCHAR2値として返します。

次の例は、データベース・キャラクタ・セットが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は、attribute1attribute2と連結して返します。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番目に出現する位置を返します。検索により合致したattribute2の先頭文字の、attribute1における文字位置を返します。INSTRBは、文字のかわりにバイトを使用します。INSTRCは、Unicode完全対応の文字を使用します。INSTR2は、UCS2コード・ポイントを使用します。INSTR4は、UCS4コード・ポイントを使用します。

nを負にすると、Oracleはattribute1の末尾から反対方向に検索します。mの値は正にする必要があります。nmのデフォルト値は1です。つまり、Oracleはattribute1の先頭文字から検索し、attribute2の最初の出現位置を返します。戻り値は、nの値に関係なく、attribute1の先頭から数えた位置になり、文字で表現されます。検索が成功しなかった場合、つまり、attribute1n番目以降にattribute2m回出現しない場合、戻り値は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)

目的

長さ関連のファンクションはattributeの長さが返されます。attributeのデータ型は、CHARまたはVARCHAR2にできます。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は、attribute1の長さがnになるまで、attribute2の文字シーケンスを左側に埋め込んで返します。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で指定した文字と同じ文字でattributeの左端にあるものをすべて削除します。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に基づくソートです。言語学的なソート順は、大文字と小文字の変換に対する特殊な言語学的な要件を処理します。これらの要件により、charと異なる長さの値が返されることもあります。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を返します。attributenlsparamのデータ型は、CHARVARCHAR2NCHARNVARCHAR2CLOBNCLOBのいずれかにできます。返される文字列のデータ型は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は、attributeの中のsearch_stringと一致する語をすべてreplacement_stringに置き換えて返します。replacement_stringを省略するかNULLにすると、search_stringと一致する語がすべて削除されます。search_stringをNULLにすると、attributeが返されます。

search_stringreplacement_stringおよびattributeのデータ型は、CHARまたはVARCHAR2にできます。返される文字列のデータ型はVARCHAR2になり、attributeと同じキャラクタ・セットに含まれます。

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

次の例では、「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バイトまで入力できます。patternとsource_stringでデータ型が異なる場合は、Oracle Databaseによって、patternがsource_stringのデータ型に変換されます。patternに指定可能な演算子の一覧は、『Oracle Database SQLリファレンス』の付録C「Oracle正規表現のサポート」を参照してください。

  • positionは正の整数で、source_stringの中で検索が開始される文字を表します。デフォルトは1です。これは、source_stringの1番目の文字で検索が開始されるという意味です。

  • occurrenceは正の整数で、source_stringの中で何回目のパターンの出現が検索されるかを表します。デフォルトは1です。これは、1番目に出現するパターンが検索されるという意味です。

  • return_optionにより、出現に対して返される内容を指定できます。

    • 0を指定した場合、一致した文字列の1番目の文字位置が返されます。これがデフォルトです。

    • 1を指定した場合、一致した文字列の直後の文字位置が返されます。

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

    • i: 大文字と小文字を区別しない照合を指定します。

    • c: 大文字と小文字を区別する照合を指定します。

    • n: match-any-character文字であるピリオド(.)が、改行文字を照合することを許可します。このパラメータを省略すると、ピリオドは改行文字を照合しません。

    • m: ソース文字列を複数行として扱います。ソース文字列内のあらゆる行や場所で、^は開始、$は終了と解釈されます。ソース文字列全体で一度しか出現しない開始と終了ではありません。このパラメータを省略すると、ソース文字列は単一行として扱われます。

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

    • 大文字と小文字を区別するかどうかのデフォルトは、NLS_SORTパラメータの値によって決まります。

    • ピリオド(.)は、改行文字を照合しません。

    • ソース文字列は、単一行として扱われます。

次の例では、文字列を調べて、空白以外の文字が1つ以上出現する文字列を検索します。文字列の1番目の文字から検索を開始して、空白以外の文字が1つ以上出現する文字列を検索し、6番目に一致した文字列の開始位置(デフォルト)を返します。

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

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

次の例では、文字列を調べて、先頭がs、r、pのいずれかで、6文字のアルファベット文字がその後に続く語句(大文字と小文字は区別せず)を検索します。検索は文字列の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 Databaseによって、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は正の整数で、source_stringの中で検索が開始される文字を表します。デフォルトは1です。これは、source_stringの1番目の文字で検索が開始されるという意味です。

  • occurrenceは負でない整数で、次の置換処理の出現を表します。

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

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

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

    • i: 大文字と小文字を区別しない照合を指定します。

    • c: 大文字と小文字を区別する照合を指定します。

    • n: match-any-character文字であるピリオド(.)が、改行文字を照合することを許可します。このパラメータを省略すると、ピリオドは改行文字を照合しません。

    • m: ソース文字列を複数行として扱います。ソース文字列内のあらゆる行や場所で、^は開始、$は終了と解釈されます。ソース文字列全体で一度しか出現しない開始と終了ではありません。このパラメータを省略すると、ソース文字列は単一行として扱われます。

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

    • 大文字と小文字を区別するかどうかのデフォルトは、NLS_SORTパラメータの値によって決まります。

    • ピリオド(.)は、改行文字を照合しません。

    • ソース文字列は、単一行として扱われます。

次の例では、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 aA u s t r a l i aB e l g i u mB r a z i lC 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 Databaseによって、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は正の整数で、source_stringの中で検索が開始される文字を表します。デフォルトは1です。これは、source_stringの1番目の文字で検索が開始されるという意味です。

  • occurrenceは負でない整数で、次の置換処理の出現を表します。

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

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

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

    • i: 大文字と小文字を区別しない照合を指定します。

    • c: 大文字と小文字を区別する照合を指定します。

    • n: match-any-character文字であるピリオド(.)が、改行文字を照合することを許可します。このパラメータを省略すると、ピリオドは改行文字を照合しません。

    • m: ソース文字列を複数行として扱います。ソース文字列内のあらゆる行や場所で、^は開始、$は終了と解釈されます。ソース文字列全体で一度しか出現しない開始と終了ではありません。このパラメータを省略すると、ソース文字列は単一行として扱われます。

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

    • 大文字と小文字を区別するかどうかのデフォルトは、NLS_SORTパラメータの値によって決まります。

    • ピリオド(.)は、改行文字を照合しません。

    • ソース文字列は、単一行として扱われます。

次の例では、文字列を調べて、カンマで囲まれた1番目のサブストリングを検索します。Oracle Databaseは、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は、attribute1の長さがnになるまで、attribute2を必要な回数だけ右側に埋め込んで返します。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)

目的

RTRIMは、setで指定した文字と同じ文字で、attributeの右端にあるものをすべて削除して返します。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

  • 元の名前(手順1を実行する前)で同じ数値の複数の文字が隣り合う場合またはhとwを除けば隣り合う場合は、最初の文字を残してその他すべてを省略します。

  • 0を埋め込んだ最初の4バイトを返します。

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は、attributeの中の、from_stringの各文字と一致するすべての文字をto_stringに指定された文字で置き換えて返します。attributeの文字のうちfrom_stringに含まれない文字は、置き換わりません。引数from_stringには、to_stringより多くの文字を含めることができます。この場合、from_stringの末尾にある余分な文字は、to_stringの文字と対応しません。こうした余分な文字がattributeにある場合、余分な文字は戻り値から削除されます。

to_stringに空文字列を使用し、from_stringと一致するすべての文字を戻り値から削除することはできません。空文字列は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を返します。

Control Center変換

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

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

Control Center変換のファンクションは次のとおりです。

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はオブジェクトが配布される場所を表します。

目的

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

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

次の例では、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など、他のパブリック・ビューへのキーとして使用したり、詳細な情報が必要な場合は他のControl Center変換に使用できます。

次の例では、監査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;

Conversion変換

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

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

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のいずれかにできます。たとえば、ウムラウト(umlaut)コード・ポイントで修飾されたoコード・ポイントは、o-umlautコード・ポイントとして返されます。

次の例では、o-umlautコード・ポイントが返されます。

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のデータベースへの格納に使用されているキャラクタ・セットの名前です。デフォルト値は、データベース・キャラクタ・セットです。

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

次の例では、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ビットASCIIキャラクタ・セット

  • WE8DEC: 西ヨーロッパ8ビット・キャラクタ・セット

  • WE8HP: HP西ヨーロッパLaserjet 8ビット・キャラクタ・セット

  • F7DEC: DECフランス7ビット・キャラクタ・セット

  • WE8EBCDIC500: IBM西ヨーロッパEBCDICコード・ページ500

  • WE8PC850: IBM PCコード・ページ850

  • WE8ISO8859P1: ISO 8859-1西ヨーロッパ8ビット・キャラクタ・セット

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フラッシュバック問合せで使用するタイムスタンプに変換できます。

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(not a number)に変換されます。

文字列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には、数値書式要素から返される次の文字を指定します。

  • 小数点の文字

  • グループ・セパレータ

  • ローカル通貨記号

  • 国際通貨記号

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

'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 Databaseは、基底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の値に変換します。fmtは、attributeの書式を指定する日付書式です。fmtを省略した場合は、attributeをデフォルトの日付書式にする必要があります。fmtをユリウス暦を表す「J」にした場合は、attributeを整数にする必要があります。このファンクションのnlsparamは、Conversion変換のTO_CHARファンクションと同じ目的で動作します。

attribute引数にDATE値を指定してTO_DATEファンクションを使用しないでください。fmtまたはデフォルトの日付書式によっては、返されるDATE値の最初の2桁が、元の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 TastaturProduct Nummer: 3106Nur 39 EURO!Die Tastatur KB 101/CH-DE ist eine Standard PC/AT Tastatur mit 102 Tasten. Tastaturbelegung: 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 Databaseは、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は、Conversion変換の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の値に変換します。オプションのfmtは、charの書式を指定します。fmtを省略すると、charは、TIMESTAMP WITH TIME ZONEデータ型のデフォルトの書式である必要があります。このファンクションのオプションnlsparamは、Conversion変換の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を使用し、UNION演算でNULL列をTIMESTAMP WITH LOCAL TIME ZONEにキャストします。

SELECT order_id, line_item_id, CAST(NULL AS TIMESTAMP WITH LOCAL TIME ZONE)
       order_date   FROM order_items   UNIONSELECT 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-8821-SEP-89 21-NOV-9013-JAN-93 13-MAR-9403-JAN-90 03-MAR-9121-MAY-91 21-JUL-92. . .

UNISTR

構文

unistr::=UNISTR(string)

目的

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

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

SELECT UNISTR('abc\00e5\00f1\00f6') FROM DUAL;UNISTR------abcåñö

Date変換

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

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

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:03ALTER 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は、1か月を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(日付)

構文

round_date::=ROUND(attribute, fmt)

目的

ROUNDは、attributeの日付を、書式モデルfmtで指定される単位に丸めて返します。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(日付)

構文

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:00:00が返されます。

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

Numeric変換

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

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

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に制限はなく、ラジアン表記で-π/2からπ/2の値が返され、これはラジアンで表記された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)

目的

EXPは、attributeで指定される値をnとして、n乗した自然対数の底を返します。自然対数の底の値は、2.71828183...です。

次の例では、4乗した自然対数の底が返されます。

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の対数を返します。底attribute1は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 Databaseによって代替値nが返されます。mがNaNではない場合、Oracleによってmが返されます。このファンクションは、NULLへNaN値をマッピングするのに便利です。このファンクションは、任意の数値データ型、または数値データ型に暗黙的に変換可能な数値以外のデータ型を、引数として取得します。数値優先順位の最も高い引数が判定され、残りの引数がこのデータ型に暗黙的に変換されて、このデータ型が返されます。

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として、n乗したattribute1を返します。底attribute1と指数attribute2は任意の数値にできますが、attribute1が負の場合は、attribute2を整数にする必要があります。

次の例では、3の2乗が返されます。

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

REMAINDER

構文

remainder::=REMAINDER(m,n)

目的

REMAINDERは、mをattribute2で割った剰余を返します。このファンクションは、任意の数値データ型、または数値データ型に暗黙的に変換可能な数値以外のデータ型を、引数として取得します。数値優先順位の最も高い引数が判定され、残りの引数がこのデータ型に暗黙的に変換されて、このデータ型が返されます。

nが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を返します。attribute = 0の場合は0を返し、attribute > 0の場合は1を返します。これは、正の数値のみが期待されるメジャーの検証に使用できます。

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

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

SIN

構文

sin::=SIN(attribute)

目的

SINattribute(角度として表記)の正弦を返します。

次の例では、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)

目的

TANattribute(ラジアンで表記された角度)の正弦を返します。

次の例では、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(NUMBER列用)

構文

 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は、参照結果などで返されるNUMBER列の名前です。key_column_nameは、参照表で照合するキーとして使用するNUMBER列の名前です。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が返されます。この出力は、インライン・ファンクション・コールの結果として、マッピングで処理されます。


注意:

このファンクションは、行ベースのキー参照です。セット・ベースの参照は、『Oracle Warehouse Builderユーザーズ・ガイド』で説明するキー参照演算子を使用した場合にサポートされます。

WB_IS_NUMBER

構文

 WB_IS_NUMBER(attibute, fmt)

目的

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

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

WB_IS_NUMBERは、attributeに有効な数値が含まれる場合、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 Databaseでは必要に応じて、番号が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_DIMENSIONWB_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変換の説明に使用した例は、図2-1に示すシナリオに基づいています。

図2-1 OLAP変換の例

図2-1の説明
「図2-1 OLAP変換の例」の説明

リレーショナル・ディメンションTIME_DIMとリレーショナル・キューブSALES_CUBEは、スキーマWH_TGTに格納されています。WH_TGTスキーマには、ディメンションとキューブがロードされたアナリティック・ワークスペースAW_WHも作成されます。

WB_OLAP_AW_PRECOMPUTE

構文

WBWB_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_* viewsの問合せに使用して、アクティビティのステータスをチェックできます。

目的

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サーバーに配布されている場合を考えます。パラメータgenerate surrogate keys for Dimensionがtrueに設定されているとします。ここで、リレーショナル・ディメンションTIME_DIMからアナリティック・ワークスペースAW_WHのディメンションAW_TIMEにデータを再ロードするには、次の構文を使用します。

WB_OLAP_LOAD_CUBE('WH_TGT', 'AW_WH', 'WH_TGT', 'TIME_DIM', 'AW_TIME')

Other変換

Warehouse Builderには、特定のデータ型に制限されない様々なファンクションを実行できる変換も組み込まれています。この項では、このようなタイプの変換について説明します。Warehouse Builderでは、次のOther変換を使用できます。

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,157SELECT 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 Databaseによってより高い数値の優先度を持つ引数が決定され、暗黙的にもう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_VPHartstein                 MK_MANKaufling                  ST_MANKochhar                   AD_VPKochhar                   AD_VPRaphaely                  PU_MANTaylor                    SA_REPTaylorWhalen                    AD_ASSTWhalen

NVL

構文

nvl::=NVL(attribute1, attribute2)

目的

attribute1がNULLの場合、NVLattribute2を返します。attribute1がNULLでない場合、NVLattribute1を返します。引数attribute1attribute2は、任意のデータ型にできます。2つのデータ型が異なる場合、attribute2は、attribute1のデータ型に変換されてから比較されます。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は任意のデータ型にできます。引数expr2expr3は、LONG以外の任意のデータ型にできます。

expr2expr3でデータ型が異なる場合:

  • expr2が文字データの場合、expr3がNULL定数でないかぎり、Oracle Databaseは、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      10000Baida                           2900       2900Banda                           6200       6882Bates                           7300       8468Bell                            4000       4000Bernstein                       9500      11970Bissot                          3300       3300Bloom                          10000      12100Bull                            4100       4100

ORA_HASH

構文

ora_hash::=ORA_HASH(expr,max_bucket,seed_value)

目的

ORA_HASHは、指定された式のハッシュ値を計算するファンクションです。このファンクションは、データのサブセットの分析やランダム・サンプルの生成などの演算に便利です。このファンクションは、NUMBER値を返します。

expr引数で、Oracle Databaseがハッシュ値を計算するデータを指定します。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 Databaseによってその値は無視され、デフォルトの値が使用されます。Oracleでは、現在のセッションを記述するUSERENVと呼ばれる組込みネームスペースが用意されています。ネームスペースUSERENVの定義済のパラメータの詳細は、『Oracle Database SQLリファレンス』の表7-11を参照してください。

次の文では、データベースにログオンしたユーザーの名前を返します。

CONNECT OE/OESELECT 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は、オペランドの最も特殊なタイプのタイプIDを返します。この値は、代用可能な列の基礎となる型判別列を識別するために主に使用されます。たとえば、SYS_TYPEIDによって返された値を使用して、型判別列に索引を作成できます。SYS_TYPEIDファンクションを使用して、表の型判別列に索引を作成できます。このファンクションは、オブジェクト・タイプのオペランドにのみ使用できます。すべての最終ルートのオブジェクト・タイプ(すなわち、タイプ階層に属していない最終タイプ)は、NULLのタイプIDを持ちます。Oracle Databaseでは、タイプ階層に属するすべてのタイプに一意のNULL以外のタイプIDが割り当てられています。

UID

構文

uid::=UID()

目的

UIDは、変換を含むセッションの実行時にログオンするユーザーなど、セッション・ユーザーを一意に識別する整数を返します。分散型のSQL文の場合、UIDファンクションは、ローカル・データベース上のユーザーを識別します。

監査情報のログをターゲット表に記録し、マッピングを実行しているユーザーを識別する場合、このファンクションを使用します。

次の例では、このセッションにログインしたユーザーのローカル・データベース・ユーザーIDが返されます。

SELECT uid FROM dual;

       UID
----------
        55

USER

構文

user::=USER()

目的

USERは、データ型がVARCHAR2のセッション・ユーザー名(ログオン中のユーザー)を返します。

Oracleは、空白を埋め込んだ比較セマンティクスとこのファンクションの値を比較します。分散型のSQL文の場合、UIDファンクションとUSERファンクションは、ローカル・データベース上のユーザーを識別します。

監査情報のログをターゲット表に記録し、マッピングを実行しているユーザーを識別する場合、このファンクションを使用します。

次の例では、このセッションにログイン中のローカル・データベース・ユーザーが返されます。

SELECT user FROM dual;

USER
------------------------------
OWB9I_RUN

USERENV

構文

userenv::=USERENV(parameter)

目的


注意:

USERENVは、下位互換性のために保持されているレガシー・ファンクションです。現行機能に対しては、SYS_CONTEXTファンクションを、組込みUSERENVネームスペースとともに使用することをお薦めします。

USERENVは、現行セッションについての情報を返します。この情報は、アプリケーション固有の監査証跡表を書き込む場合や、現行のユーザー・セッションで使用されている言語固有の文字を特定する場合に便利です。CHECK制約の条件にUSERENVを使用することはできません。表2-1は、parameter引数の値についての説明です。USERENVをコールすると、必ずVARCHAR2データが返されます。ただし、SESSIONID、ENTRYID、COMMITSCNの各パラメータを指定してコールした場合は、NUMBERが返されます。

表2-1 USERENVファンクションのパラメータ

パラメータ 戻り値

CLIENT_INFO

CLIENT_INFOは、最大64バイトのユーザー・セッション情報を返します。これは、DBMS_APPLICATION_INFOパッケージを使用するアプリケーションによって格納できます。

注意: 一部の商用アプリケーションでは、このコンテキスト値を使用している場合があります。それらのアプリケーションの関連ドキュメントを参照して、このコンテキスト領域の使用に対する制限を確認してください。

ENTRYID

現在の監査エントリ番号です。監査エントリIDのシーケンスは、詳細監査レコードと通常監査レコードで共有されています。この属性は、分散型SQL文で使用することはできません。

ISDBA

ISDBAは、オペレーティング・システムまたはパスワード・ファイルを通じて、データベース管理者権限があると認証された場合、'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

Spatial変換

Spatial変換は、ファンクションとプロシージャを統合したもので、これによって、Oracle Databaseで、空間データを迅速かつ効率的に格納、アクセスおよび分析できます。Warehouse Builderでは、次のSpatial変換を使用できます。

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))

Streams変換

Streams変換のカテゴリには、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' - i.e. 'abcde'
LCR.GET_VALUE('c2', 'new') will have the value for the column 'c2' - i.e. 10

そのようなLCRは、ソース・システムでの表S.T1の変更を取り込むストリーム・キャプチャ・プロセスによって、作成されエンキューされます。

REPLICATE(lcr, true) - ターゲット・システムの表T1に、行('abcde', 10)が挿入されます。


注意:

このアプローチを使用すると、系統情報を取得できません。系統が重要な場合、このファンクションは使用しないでください。ソース表にバインドさたLCRCast演算子とターゲット表にバインドさたtable演算子を使用して、これらの2つの演算子の属性を同じ名前(「名前による一致」)で接続する、より直接的なアプローチを使用します。LCR(論理変更レコード)の詳細は、Oracle Database 10gのドキュメント(情報統合)を参照してください。

XML変換

Warehouse Builderユーザーは、XML変換により、XMLオブジェクトで変換を実行できます。Warehouse Builderユーザーは、この変換により、XML文書とアドバンスト・キューをロードおよび変換できます。

XMLソースのロードを可能にするため、Warehouse Builderでは、この章で詳しく説明されているカスタム・ファンクションを使用して、データベースのXML機能にアクセスします。

XML変換の内容は次のとおりです。

EXISTSNODE

構文

existsnode::=EXISTSNODE(XMLType_instance,XPath_string,namespace_string)

目的

EXISTSNODEは、指定されたパスを使用するXML文書のトラバースが任意のノードとなるかどうかを決定します。このファンクションは、XML文書を含むXMLTypeインスタンスとパスを指定するVARCHAR2のXPath文字列を、引数として受け取ります。オプションのnamespace_stringは、デフォルトのマッピングまたは接頭辞のネームスペース・マッピングを指定するVARCHAR2値になる必要があります。この値は、Oracle Databaseで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)

目的

EXTRACTは、EXISTSNODEファンクションに似ています。このファンクションは、VARCHAR2のXPath文字列に適用し、XMLの断片を含むXMLTypeインスタンスを返します。オプションのnamespace_stringは、デフォルトのマッピングまたは接頭辞のネームスペース・マッピングを指定するVARCHAR2値になる必要があります。この値は、Oracle Databaseで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 Databaseで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     2San Francisco        1New JerseySeattle, 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キーワードにより、収集した値は、副問合せの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ファンクションでは、XMLスタイルシートが存在している必要があります。これは、ノード内の要素をアルファベット順に並べ替える、非常に単純なスタイルシートの例です。

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>