Oracle Database SQL言語リファレンス 11g リリース1(11.1) E05750-03 |
|
疑似列は表の列のように使用できますが、実際に表に格納されているわけではありません。疑似列から値を選択できますが、疑似列に対して値の挿入、更新、削除はできません。疑似列は、引数を指定しないファンクションとも似ています(詳細は第5章「ファンクション」を参照してください)。ただし、引数を指定しないファンクションは、通常、結果セット内の各行に対して同じ値を戻しますが、疑似列では各行に対して異なる値を戻します。
この章では、次の内容を説明します。
階層問合せ疑似列は、階層問合せでのみ有効です。階層問合せ疑似列には、次のものがあります。
問合せの中で階層型の関連を定義するには、CONNECT
BY
句を使用する必要があります。
CONNECT_BY_ISCYCLE
疑似列は、現在の行に自身の祖先でもある子がある場合に1を戻します。それ以外の場合は、0(ゼロ)を戻します。
CONNECT
BY
句のNOCYCLE
パラメータを指定した場合のみ、CONNECT_BY_ISCYCLE
を指定できます。NOCYCLE
によって、Oracleは問合せの結果を戻すことができます。このパラメータを指定しないと、データ内のCONNECT
BY
ループのため、問合せは失敗します。
CONNECT_BY_ISLEAF
疑似列は、現在の行がCONNECT
BY
条件によって定義されるツリーのリーフである場合に1を戻します。それ以外の場合は、0(ゼロ)を戻します。この情報は、特定の行をさらに展開して階層の詳細を表示できるかどうかを示します。
次の例は、hr.employees
表の最初の3レベルです。各行がリーフ行か(IsLeaf
列が1)、子である行を持つか(IsLeaf
列が0)を示しています。
SELECT last_name "Employee", CONNECT_BY_ISLEAF "IsLeaf", LEVEL, SYS_CONNECT_BY_PATH(last_name, '/') "Path" FROM employees WHERE LEVEL <= 3 AND department_id = 80 START WITH employee_id = 100 CONNECT BY PRIOR employee_id = manager_id AND LEVEL <= 4; Employee IsLeaf LEVEL Path ------------------------- ---------- ---------- ------------------------- Abel 1 3 /King/Zlotkey/Abel Ande 1 3 /King/Errazuriz/Ande Banda 1 3 /King/Errazuriz/Banda Bates 1 3 /King/Cambrault/Bates Bernstein 1 3 /King/Russell/Bernstein Bloom 1 3 /King/Cambrault/Bloom Cambrault 0 2 /King/Cambrault Cambrault 1 3 /King/Russell/Cambrault Doran 1 3 /King/Partners/Doran Errazuriz 0 2 /King/Errazuriz Fox 1 3 /King/Cambrault/Fox . . .
階層問合せによって戻される各行について、LEVEL
疑似列は、ルート行に1を戻し、ルートの子には2を戻します(以降同様に続きます)。ルート行は逆ツリー構造の最上位行です。子である行は任意の非ルート行です。親である行は子を持つ任意の行です。リーフ行は子を持たない任意の行です。図3-1に、逆ツリーのノードとそれらのLEVEL
値を示します。
順序は、一意の連続値を生成できるスキーマ・オブジェクトです。これらの値は、主キーや一意のキーによく使用されます。次の疑似列を使用したSQL文で、順序値を参照できます。
CURRVAL
とNEXTVAL
は、順序の名前で修飾する必要があります。
sequence.CURRVAL sequence.NEXTVAL
別のユーザーのスキーマ内での順序の現在の値または次の値を参照するには、その順序に対するSELECT
オブジェクト権限またはSELECT
ANY
SEQUENCE
システム権限のどちらかが必要です。さらに、その順序は、次に示すとおり、順序を含むスキーマで修飾する必要があります。
schema.sequence.CURRVAL schema.sequence.NEXTVAL
リモート・データベース上の順序の値を参照するには、次のように、データベース・リンクの完全な名前または部分的な名前で順序を修飾する必要があります。
schema.sequence.CURRVAL@dblink schema.sequence.NEXTVAL@dblink
順序には、待機またはロックすることなく多数のユーザーが同時にアクセスできます。
次の場所でCURRVAL
とNEXTVAL
を使用できます。
SELECT
文のSELECT構文のリスト
INSERT
文内の副問合せのSELECT構文のリスト
INSERT
文のVALUES
句
UPDATE
文のSET
句
次の構造体では、CURRVAL
およびNEXTVAL
は使用できません。
DELETE
文、SELECT
文またはUPDATE
文内の副問合せ
DISTINCT
演算子を持つSELECT
文
GROUP
BY
句またはORDER
BY
句を持つSELECT
文
UNION
、INTERSECT
またはMINUS
によって別のSELECT
文と結合されているSELECT
文
SELECT
文のWHERE
句
CREATE
TABLE
文またはALTER
TABLE
文の列のDEFAULT
値
CHECK
制約の条件
CURRVAL
またはNEXTVAL
を使用する単一のSQL文では、参照されたLONG
列、更新された表、ロックされた表がすべて同じデータベース上にある必要があります。
順序を作成するときに、初期値と増分値を定義できます。NEXTVAL
の最初の参照によって、順序の初期値が戻されます。その後の参照によって、定義されたNEXTVAL
増分値で順序が増加され、その新しい値が戻されます。CURRVAL
を参照すると、NEXTVAL
への最後の参照で戻された値である、順序の現在の値が常に戻されます。
セッションの順序に対してCURRVAL
を使用する前に、まずNEXTVAL
で順序を初期化してください。順序の詳細は、「CREATE SEQUENCE」を参照してください。
NEXTVAL
への参照が含まれる単一のSQL文の中では、Oracleは、次の各行につき1回順序を増加させます。
SELECT
文の外部問合せブロックによって戻される行。このような問合せブロックは、次の場所に指定できます。
UPDATE
文で更新される行
VALUES
句が含まれるINSERT
文
INSERT
... [ALL
| FIRST
]文(マルチテーブル・インサート)。マルチテーブル・インサートは、単一のSQL文とみなされます。このため、順序のNEXTVAL
への参照では、文のSELECT
部分からの各入力レコードに対して順序が1回のみ増加されます。INSERT
... [ALL
| FIRST
]文のいずれかの部分でNEXTVAL
が複数回指定されている場合は、指定されたレコードが挿入される回数に関係なく、値はすべての挿入ブランチに対して同じになります。
MERGE
文でマージされる行。NEXTVAL
への参照は、merge_insert_clause
またはmerge_update_clause
あるいはその両方に指定できます。NEXTVALUE
値は、更新操作または挿入操作に順序番号が使用されない場合でも、行が更新されるか挿入されるたびに増加されます。NEXTVAL
がこれらの場所のいずれかで複数回指定されている場合、順序は各行に対して1回増加され、その行のNEXTVAL
が検出されるたびにすべて同じ値を戻します。
INSERT
ALL
文の入力行。NEXTVAL
は、各行に対するinsert_into_clause
マップの回数に関係なく、副問合せによって戻される各行に対して1回のみ増加されます。
これらの場所のいずれかが、NEXTVAL
を複数回参照している場合、Oracleは1回のみ順序を増加させ、NEXTVAL
が検出されるたびにすべて同じ値を戻します。
これらの場所のいずれかが、CURRVAL
とNEXTVAL
の両方を参照している場合、OracleはCURRVAL
とNEXTVAL
の両方について順序を増加させ同じ値を戻します。
次の例では、サンプル・スキーマhr
の従業員順序の次の値を検索します。
SELECT employees_seq.nextval FROM DUAL;
次の例では、従業員順序を増加させ、サンプル表hr.employees
に挿入される新しい従業員のためにその値を使用します。
INSERT INTO employees VALUES (employees_seq.nextval, 'John', 'Doe', 'jdoe', '555-1212', TO_DATE(SYSDATE), 'PU_CLERK', 2500, null, null, 30);
次の例では、次の注文番号を使用して新しい注文をマスター注文表に追加します。その後、この番号を使用して関連する注文をディテール注文表に追加します。
INSERT INTO orders (order_id, order_date, customer_id) VALUES (orders_seq.nextval, TO_DATE(SYSDATE), 106); INSERT INTO order_items (order_id, line_item_id, product_id) VALUES (orders_seq.currval, 1, 2359); INSERT INTO order_items (order_id, line_item_id, product_id) VALUES (orders_seq.currval, 2, 3290); INSERT INTO order_items (order_id, line_item_id, product_id) VALUES (orders_seq.currval, 3, 2381);
バージョン問合せ疑似列は、Oracleフラッシュバック問合せの一形態であるOracle Flashback Version Queryでのみ有効です。バージョン問合せ疑似列には、次のものがあります。
VERSIONS_STARTTIME
: 問合せによって戻された行の最初のバージョンのタイムスタンプを戻します。
VERSIONS_STARTSCN
: 問合せによって戻された行の最初のバージョンのSCNを戻します。
VERSIONS_ENDTIME
: 問合せによって戻された行の最後のバージョンのタイムスタンプを戻します。
VERSIONS_ENDSCN
: 問合せによって戻された行の最後のバージョンのSCNを戻します。
VERSIONS_XID
: 各行のそれぞれのバージョンについて、その行バージョンを作成したトランザクションのトランザクションID(RAW
番号)を戻します。
VERSIONS_OPERATION
: 各行のそれぞれのバージョンについて、その行バージョンを発生させた操作を表す単一の文字を戻します。戻される値は、I(挿入操作)、U(更新操作)またはD(削除操作)です。COLUMNS
句を指定せずにXMLTable
構造体を参照する場合、またはTABLE
ファンクションを使用して表の型をネストしたスカラーを参照する場合、データベースは単一列を持つ仮想表を戻します。この疑似列の名前は、COLUMN_VALUE
です。
XMLTable
のコンテキストでは、戻り値のデータ型はXMLType
です。たとえば、次の2つの文は同等で、どちらの出力も戻される列の名前としてCOLUMN_VALUE
を示します。
SELECT * FROM XMLTABLE('<a>123</a>'); COLUMN_VALUE --------------------------------------- <a>123</a> SELECT COLUMN_VALUE FROM (XMLTable('<a>123</a>')); COLUMN_VALUE ---------------------------------------- <a>123</a>
TABLE
ファンクションのコンテキストでは、戻り値はコレクション要素のデータ型になります。次の文では、2つのレベルにネストした表を作成します。このコンテキストでのCOLUMN_VALUE
の使用方法は、「マルチレベル・コレクションの例:」を参照してください。
CREATE TYPE phone AS TABLE OF NUMBER; / CREATE TYPE phone_list AS TABLE OF phone; /
次の文ではCOLUMN_VALUE
を使用してphone
型から選択します。
SELECT t.COLUMN_VALUE from table(phone(1,2,3)) t; COLUMN_VALUE ------------ 1 2 3
ネストした型では、SELECT構文のリストとTABLE
ファンクションの両方でCOLUMN_VALUE
疑似列を使用できます。
SELECT t.COLUMN_VALUE FROM TABLE(phone_list(phone(1,2,3))) p, TABLE(p.COLUMN_VALUE) t; COLUMN_VALUE ------------ 1 2 3
次の例に示すように、キーワードCOLUMN_VALUE
は、列または属性名を持たない内部のネストした表のスカラー値に対してOracle Databaseが生成する名前です。このコンテキストでは、COLUMN_VALUE
は疑似列ではなく、実際の列の名前です。
CREATE TABLE my_customers ( cust_id NUMBER, name VARCHAR2(25), phone_numbers phone_list, credit_limit NUMBER) NESTED TABLE phone_numbers STORE AS outer_ntab (NESTED TABLE COLUMN_VALUE STORE AS inner_ntab);
参照:
|
OBJECT_ID
疑似列は、オブジェクト表またはビューの列のオブジェクト識別子を戻します。Oracleはこの疑似列をオブジェクト表の主キーとして使用します。OBJECT_ID
は、ビューのINSTEAD
OF
トリガーや、オブジェクト表の置換可能行のIDの識別に便利です。
OBJECT_VALUE
疑似列は、オブジェクト表、XMLType
表、オブジェクト・ビューまたはXMLType
ビューの列のシステム生成名を戻します。この疑似列は、オブジェクト表の置換可能行の値の識別や、WITH
OBJECT
IDENTIFIER
句を使用したオブジェクト・ビューの作成に便利です。
参照:
|
ORA_ROWSCN
は、各行について、その行に対する最新の変更のシステム変更番号(SCN)の上限(近似値)を戻します。この疑似列は、行が最後に更新されたおよその時期を判別するのに便利です。Oracleは行が存在するブロックをコミットしたトランザクションによってSCNを追跡するため、この値は正確でない場合があります。行レベル依存の追跡を行う表を作成すると、SCNのより正確な概数を取得できます。行レベル依存の追跡の詳細は、「NOROWDEPENDENCIES | ROWDEPENDENCIES」を参照してください。
この疑似列は、ビューへの問合せでは使用できません。ただし、この疑似列を使用して、ビューの作成時に基礎となる表を参照することは可能です。また、UPDATE
文またはDELETE
文のWHERE
句でこの疑似列を使用することもできます。
ORA_ROWSCN
は、フラッシュバック問合せではサポートされません。かわりに、フラッシュバック問合せ専用に提供されているバージョン問合せ疑似列を使用してください。フラッシュバック問合せの詳細は、「flashback_query_clause」を参照してください。バージョン問合せ疑似列の詳細は、「バージョン問合せ疑似列」を参照してください。
ORA_ROWSCNの制限事項: この疑似列は、外部表ではサポートされません。
次の最初の文では、ORA_ROWSCN
疑似列を使用して、employees
表に対する最後の操作のシステム変更番号を取得します。2番目の文では、この疑似列をSCN_TO_TIMESTAMP
ファンクションとともに使用して、操作のタイムスタンプを判別します。
SELECT ORA_ROWSCN, last_name FROM employees WHERE employee_id = 188; SELECT SCN_TO_TIMESTAMP(ORA_ROWSCN), last_name FROM employees WHERE employee_id = 188;
ROWID
疑似列は、データベース内の各行について、行のアドレスを戻します。Oracle DatabaseのROWID値には、行を検索するために必要な次の情報が含まれています。
ほとんどの場合、ROWID値ではデータベース内の行は一意に識別されます。ただし、同じクラスタに格納されている異なる表の行は、同じROWIDを持つことができます。
ROWID
疑似列の値はROWID
またはUROWID
データ型を持ちます。詳細は、「ROWIDデータ型」および「UROWIDデータ型」を参照してください。
ROWID値には、次の重要な用途があります。
表の主キーとしてROWID
を使用しないでください。たとえば、インポート・ユーティリティとエクスポート・ユーティリティで行を削除してから再挿入する場合、ROWIDが変わる場合があります。行を削除した場合、Oracleは、後から新しく挿入される行にそのROWIDを再度割り当てる可能性があります。
問合せのSELECT
句とWHERE
句でROWID
疑似列を使用できますが、これらの疑似列の値が実際にデータベースに格納されるわけではありません。ROWID
疑似列の値に対して挿入、更新および削除はできません。
次の文は、部門20の従業員のデータを含むすべての行のアドレスを選択します。
SELECT ROWID, last_name FROM employees WHERE department_id = 20;
ROWNUM
疑似列は、問合せによって戻される各行について、表や結合処理された行の集合からOracleが行を選択する順序を示す番号を戻します。つまり、選択される最初の行のROWNUM
は1、2番目の行のROWNUMは2です(以降同様に続きます)。
次の例のように、ROWNUM
を使用して問合せによって戻される行数を制限できます。
SELECT * FROM employees WHERE ROWNUM < 11;
同じ問合せでROWNUM
にORDER
BY
句が続く場合、ORDER
BY
句によって行が再び順序付けられます。結果は、行がアクセスされる方法によって異なります。たとえば、ORDER
BY
句の指定によってOracleが索引を使用してデータにアクセスする場合、索引なしの場合とは異なる順序で行が取り出されることがあります。このため、次の文では、前述の例と同じ行が戻されるとはかぎりません。
SELECT * FROM employees WHERE ROWNUM < 11 ORDER BY last_name;
ORDER
BY
句を副問合せに埋め込んでROWNUM
条件をトップレベル問合せに置いた場合、行の順序付けの後でROWNUM
条件を強制的に適用させることができます。たとえば、次の問合せは、小さい順に10個の従業員番号を持つ従業員を戻します。これは、上位N番のレポートと呼ばれることがあります。
SELECT * FROM (SELECT * FROM employees ORDER BY employee_id) WHERE ROWNUM < 11;
前述の例では、ROWNUM
値はトップレベルのSELECT
文の値です。これらの値は、副問合せ内のemployee_id
によって行が順序付けられた後で生成されます。
比較条件「ROWNUM
値>正の整数」は、常に偽となるため注意してください。たとえば、次の問合せでは行は戻されません。
SELECT * FROM employees WHERE ROWNUM > 1;
最初にフェッチされる行のROWNUM
には1が割り当てられるため、条件は偽と判断されます。2番目にフェッチされる予定だった行は最初の行になるため、このROWNUM
にも1が割り当てられ、条件も偽と判断されます。このように、後続するすべての行が条件を満たさないため、行は戻されません。
また、次の例のように、ROWNUM
を使用して表の各行に一意の値を割り当てることもできます。
UPDATE my_table SET column1 = ROWNUM;
行に一意の番号を割り当てる別の方法については、「ROW_NUMBER」を参照してください。
Oracleは、XMLSchema情報およびSTORAGE句の指定方法に基づいて、XMLType
データをLOBまたはオブジェクト・リレーショナル列に格納します。XMLDATA
疑似列を使用すると、基礎となるLOBまたはオブジェクト・リレーショナル列にアクセスし、追加のSTORAGE句のパラメータ、制約、索引などを指定できます。
次の文は、この疑似列の使用方法を示しています。XMLType
の簡単な表を作成するとします。
CREATE TABLE xml_lob_tab of XMLTYPE;
デフォルト記憶域は、CLOB
列にあります。基礎となるLOB列の記憶特性を変更する場合は、次の文を使用できます。
ALTER TABLE xml_lob_tab MODIFY LOB (XMLDATA) (STORAGE (BUFFER_POOL DEFAULT) CACHE);
「SQL文でのXMLの使用方法」で作成したxwarehouses
表のようなXML Schemaベースの表を作成したとします。その後、次の文に示すように、XMLDATA
列を使用して、基礎となる列のプロパティを設定できます。
ALTER TABLE xwarehouses ADD (UNIQUE(XMLDATA."WarehouseId"));
|
![]() Copyright © 1996, 2008, Oracle Corporation. All Rights Reserved. |
|