11g リリース1（11.1）
E05750-03
 目次 |
 索引 |
| Oracle Database SQL言語リファレンス 11g リリース1（11.1） E05750-03 |
|
このマニュアルの本文では、ほぼすべてのリファレンス項目に例を記載しています。この付録では、単一のSQL文のコンテキストに適さない詳細な例を示します。これらの例は、特定のOracle機能を活用するために使用する一連の手順を示すためのものです。このリファレンス本文の個々のSQL文の構文図およびセマンティクスにかわるものではありません。記載されている参照項目を使用して、必要な権限、制限事項、構文などの補足情報を参照してください。
この付録では、次の内容を説明します。
この項では、拡張索引作成機能の単純で現実的な使用例で必要となる手順の例を示します。
HR.employees表の給与をランク付けし、10〜20にランク付けされる給与を検索するとします。この場合、次のようにDENSE_RANKファンクションを使用できます。
SELECT last_name, salary FROM (SELECT last_name, DENSE_RANK() OVER (ORDER BY salary DESC) rank_val, salary FROM employees) WHERE rank_val BETWEEN 10 AND 20;
このネストした問合せは多少複雑で、employees表のソートのみではなく全体スキャンも必要です。同じ結果が得られるもう1つの方法には、拡張索引作成機能を使用する方法があります。この方法による問合せは単純になります。この問合せには、ROWIDによる索引スキャンおよび表アクセスのみ必要です。そのため、問合せが効率的に実行されます。
最初の手順では、position_im実装タイプ（索引の定義、メンテナンスおよび作成を行うためのメソッド・ヘッダーを含む）を作成します。型本体のほとんどにはPL/SQLが使用されています。その部分はイタリック体で示しています。
ファンクションODCIINDEXCREATE()内にEXECUTE IMMEDIATE文が含まれるため、型は、AUTHID CURRENT_USER句を使用して作成する必要があります。デフォルトでは、このファンクションは定義者権限で実行されます。後続のドメイン索引の作成でこのファンクションがコールされる場合、実行者の権限は定義者権限とは異なります。
|
参照
|
CREATE OR REPLACE TYPE position_im AUTHID CURRENT_USER AS OBJECT ( curnum NUMBER, howmany NUMBER, lower_bound NUMBER, upper_bound NUMBER, /* lower_bound and upper_bound are used for the index-based functional implementation */ STATIC FUNCTION ODCIGETINTERFACES(ifclist OUT SYS.ODCIOBJECTLIST) RETURN NUMBER, STATIC FUNCTION ODCIINDEXCREATE (ia SYS.ODCIINDEXINFO, parms VARCHAR2, env SYS.ODCIEnv) RETURN NUMBER, STATIC FUNCTION ODCIINDEXTRUNCATE (ia SYS.ODCIINDEXINFO, env SYS.ODCIEnv) RETURN NUMBER, STATIC FUNCTION ODCIINDEXDROP(ia SYS.ODCIINDEXINFO, env SYS.ODCIEnv) RETURN NUMBER, STATIC FUNCTION ODCIINDEXINSERT(ia SYS.ODCIINDEXINFO, rid ROWID, newval NUMBER, env SYS.ODCIEnv) RETURN NUMBER, STATIC FUNCTION ODCIINDEXDELETE(ia SYS.ODCIINDEXINFO, rid ROWID, oldval NUMBER, env SYS.ODCIEnv) RETURN NUMBER, STATIC FUNCTION ODCIINDEXUPDATE(ia SYS.ODCIINDEXINFO, rid ROWID, oldval NUMBER, newval NUMBER, env SYS.ODCIEnv) RETURN NUMBER, STATIC FUNCTION ODCIINDEXSTART(SCTX IN OUT position_im, ia SYS.ODCIINDEXINFO, op SYS.ODCIPREDINFO, qi SYS.ODCIQUERYINFO, strt NUMBER, stop NUMBER, lower_pos NUMBER, upper_pos NUMBER, env SYS.ODCIEnv) RETURN NUMBER, MEMBER FUNCTION ODCIINDEXFETCH(SELF IN OUT position_im, nrows NUMBER, rids OUT SYS.ODCIRIDLIST, env SYS.ODCIEnv) RETURN NUMBER, MEMBER FUNCTION ODCIINDEXCLOSE(env SYS.ODCIEnv) RETURN NUMBER ); / CREATE OR REPLACE TYPE BODY position_im IS STATIC FUNCTION ODCIGETINTERFACES(ifclist OUT SYS.ODCIOBJECTLIST) RETURN NUMBER IS BEGIN ifclist := SYS.ODCIOBJECTLIST(SYS.ODCIOBJECT('SYS','ODCIINDEX2')); RETURN ODCICONST.SUCCESS; END ODCIGETINTERFACES; STATIC FUNCTION ODCIINDEXCREATE (ia SYS.ODCIINDEXINFO, parms VARCHAR2, env SYS.ODCIEnv) RETURN NUMBER IS stmt VARCHAR2(2000); BEGIN /* Construct the SQL statement */ stmt := 'Create Table ' || ia.INDEXSCHEMA || '.' || ia.INDEXNAME || '_STORAGE_TAB' || '(col_val, base_rowid, constraint pk PRIMARY KEY ' || '(col_val, base_rowid)) ORGANIZATION INDEX AS SELECT ' || ia.INDEXCOLS(1).COLNAME || ', ROWID FROM ' || ia.INDEXCOLS(1).TABLESCHEMA || '.' || ia.INDEXCOLS(1).TABLENAME; EXECUTE IMMEDIATE stmt; RETURN ODCICONST.SUCCESS; END; STATIC FUNCTION ODCIINDEXDROP(ia SYS.ODCIINDEXINFO, env SYS.ODCIEnv) RETURN NUMBER IS stmt VARCHAR2(2000); BEGIN /* Construct the SQL statement */ stmt := 'DROP TABLE ' || ia.INDEXSCHEMA || '.' || ia.INDEXNAME || '_STORAGE_TAB'; /* Execute the statement */ EXECUTE IMMEDIATE stmt; RETURN ODCICONST.SUCCESS; END; STATIC FUNCTION ODCIINDEXTRUNCATE(ia SYS.ODCIINDEXINFO, env SYS.ODCIEnv) RETURN NUMBER IS stmt VARCHAR2(2000); BEGIN /* Construct the SQL statement */ stmt := 'TRUNCATE TABLE ' || ia.INDEXSCHEMA || '.' || ia.INDEXNAME || '_STORAGE_TAB'; EXECUTE IMMEDIATE stmt; RETURN ODCICONST.SUCCESS; END; STATIC FUNCTION ODCIINDEXINSERT(ia SYS.ODCIINDEXINFO, rid ROWID, newval NUMBER, env SYS.ODCIEnv) RETURN NUMBER IS stmt VARCHAR2(2000); BEGIN /* Construct the SQL statement */ stmt := 'INSERT INTO ' || ia.INDEXSCHEMA || '.' || ia.INDEXNAME || '_STORAGE_TAB VALUES (''' || newval || ''' , ''' || rid || ''' )'; /* Execute the SQL statement */ EXECUTE IMMEDIATE stmt; RETURN ODCICONST.SUCCESS; END; STATIC FUNCTION ODCIINDEXDELETE(ia SYS.ODCIINDEXINFO, rid ROWID, oldval NUMBER, env SYS.ODCIEnv) RETURN NUMBER IS stmt VARCHAR2(2000); BEGIN /* Construct the SQL statement */ stmt := 'DELETE FROM ' || ia.INDEXSCHEMA || '.' || ia.INDEXNAME || '_STORAGE_TAB WHERE col_val = ''' || oldval || ''' AND base_rowid = ''' || rid || ''''; /* Execute the statement */ EXECUTE IMMEDIATE stmt; RETURN ODCICONST.SUCCESS; END; STATIC FUNCTION ODCIINDEXUPDATE(ia SYS.ODCIINDEXINFO, rid ROWID, oldval NUMBER, newval NUMBER, env SYS.ODCIEnv) RETURN NUMBER IS stmt VARCHAR2(2000); BEGIN /* Construct the SQL statement */ stmt := 'UPDATE ' || ia.INDEXSCHEMA || '.' || ia.INDEXNAME || '_STORAGE_TAB SET col_val = ''' || newval || ''' WHERE f2 = '''|| rid ||''''; /* Execute the statement */ EXECUTE IMMEDIATE stmt; RETURN ODCICONST.SUCCESS; END; STATIC FUNCTION ODCIINDEXSTART(SCTX IN OUT position_im, ia SYS.ODCIINDEXINFO, op SYS.ODCIPREDINFO, qi SYS.ODCIQUERYINFO, strt NUMBER, stop NUMBER, lower_pos NUMBER, upper_pos NUMBER, env SYS.ODCIEnv) RETURN NUMBER IS rid VARCHAR2(5072); storage_tab_name VARCHAR2(65); lower_bound_stmt VARCHAR2(2000); upper_bound_stmt VARCHAR2(2000); range_query_stmt VARCHAR2(2000); lower_bound NUMBER; upper_bound NUMBER; cnum INTEGER; nrows INTEGER; BEGIN /* Take care of some error cases. The only predicates in which position operator can appear are op() = 1 OR op() = 0 OR op() between 0 and 1 */ IF (((strt != 1) AND (strt != 0)) OR ((stop != 1) AND (stop != 0)) OR ((strt = 1) AND (stop = 0))) THEN RAISE_APPLICATION_ERROR(-20101, 'incorrect predicate for position_between operator'); END IF; IF (lower_pos > upper_pos) THEN RAISE_APPLICATION_ERROR(-20101, 'Upper Position must be greater than or equal to Lower Position'); END IF; IF (lower_pos <= 0) THEN RAISE_APPLICATION_ERROR(-20101, 'Both Positions must be greater than zero'); END IF; storage_tab_name := ia.INDEXSCHEMA || '.' || ia.INDEXNAME || '_STORAGE_TAB'; upper_bound_stmt := 'Select MIN(col_val) FROM (Select /*+ INDEX_DESC(' || storage_tab_name || ') */ DISTINCT ' || 'col_val FROM ' || storage_tab_name || ' ORDER BY ' || 'col_val DESC) WHERE rownum <= ' || lower_pos; EXECUTE IMMEDIATE upper_bound_stmt INTO upper_bound; IF (lower_pos != upper_pos) THEN lower_bound_stmt := 'Select MIN(col_val) FROM (Select /*+ INDEX_DESC(' || storage_tab_name || ') */ DISTINCT ' || 'col_val FROM ' || storage_tab_name || ' WHERE col_val < ' || upper_bound || ' ORDER BY ' || 'col_val DESC) WHERE rownum <= ' || (upper_pos - lower_pos); EXECUTE IMMEDIATE lower_bound_stmt INTO lower_bound; ELSE lower_bound := upper_bound; END IF; IF (lower_bound IS NULL) THEN lower_bound := upper_bound; END IF; range_query_stmt := 'Select base_rowid FROM ' || storage_tab_name || ' WHERE col_val BETWEEN ' || lower_bound || ' AND ' || upper_bound; cnum := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE(cnum, range_query_stmt, DBMS_SQL.NATIVE); /* set context as the cursor number */ SCTX := position_im(cnum, 0, 0, 0); /* return success */ RETURN ODCICONST.SUCCESS; END; MEMBER FUNCTION ODCIINDEXFETCH(SELF IN OUT position_im, nrows NUMBER, rids OUT SYS.ODCIRIDLIST, env SYS.ODCIEnv) RETURN NUMBER IS cnum INTEGER; rid_tab DBMS_SQL.Varchar2_table; rlist SYS.ODCIRIDLIST := SYS.ODCIRIDLIST(); i INTEGER; d INTEGER; BEGIN cnum := SELF.curnum; IF self.howmany = 0 THEN dbms_sql.define_array(cnum, 1, rid_tab, nrows, 1); d := DBMS_SQL.EXECUTE(cnum); END IF; d := DBMS_SQL.FETCH_ROWS(cnum); IF d = nrows THEN rlist.extend(d); ELSE rlist.extend(d+1); END IF; DBMS_SQL.COLUMN_VALUE(cnum, 1, rid_tab); for i in 1..d loop rlist(i) := rid_tab(i+SELF.howmany); end loop; SELF.howmany := SELF.howmany + d; rids := rlist; RETURN ODCICONST.SUCCESS; END; MEMBER FUNCTION ODCIINDEXCLOSE(env SYS.ODCIEnv) RETURN NUMBER IS cnum INTEGER; BEGIN cnum := SELF.curnum; DBMS_SQL.CLOSE_CURSOR(cnum); RETURN ODCICONST.SUCCESS; END; END; /
次の手順では、索引タイプに関連付けられる演算子に必要なfunction_for_position_betweenファンクション実装を作成します。（PL/SQLブロックはカッコで囲んでいます。）
このファンクションは、索引ベースのファンクション評価で使用するためのものです。そのため、索引コンテキストおよびスキャン・コンテキストをパラメータとして指定します。
|
参照:
|
CREATE OR REPLACE FUNCTION function_for_position_between (col NUMBER, lower_pos NUMBER, upper_pos NUMBER, indexctx IN SYS.ODCIIndexCtx, scanctx IN OUT position_im, scanflg IN NUMBER) RETURN NUMBER AS rid ROWID; storage_tab_name VARCHAR2(65); lower_bound_stmt VARCHAR2(2000); upper_bound_stmt VARCHAR2(2000); col_val_stmt VARCHAR2(2000); lower_bound NUMBER; upper_bound NUMBER; column_value NUMBER; BEGIN IF (indexctx.IndexInfo IS NOT NULL) THEN storage_tab_name := indexctx.IndexInfo.INDEXSCHEMA || '.' || indexctx.IndexInfo.INDEXNAME || '_STORAGE_TAB'; IF (scanctx IS NULL) THEN /* This is the first call. Open a cursor for future calls. First, do some error checking */ IF (lower_pos > upper_pos) THEN RAISE_APPLICATION_ERROR(-20101, 'Upper Position must be greater than or equal to Lower Position'); END IF; IF (lower_pos <= 0) THEN RAISE_APPLICATION_ERROR(-20101, 'Both Positions must be greater than zero'); END IF; /* Obtain the upper and lower value bounds for the range we're interested in. */ upper_bound_stmt := 'Select MIN(col_val) FROM (Select /*+ INDEX_DESC(' || storage_tab_name || ') */ DISTINCT ' || 'col_val FROM ' || storage_tab_name || ' ORDER BY ' || 'col_val DESC) WHERE rownum <= ' || lower_pos; EXECUTE IMMEDIATE upper_bound_stmt INTO upper_bound; IF (lower_pos != upper_pos) THEN lower_bound_stmt := 'Select MIN(col_val) FROM (Select /*+ INDEX_DESC(' || storage_tab_name || ') */ DISTINCT ' || 'col_val FROM ' || storage_tab_name || ' WHERE col_val < ' || upper_bound || ' ORDER BY ' || 'col_val DESC) WHERE rownum <= ' || (upper_pos - lower_pos); EXECUTE IMMEDIATE lower_bound_stmt INTO lower_bound; ELSE lower_bound := upper_bound; END IF; IF (lower_bound IS NULL) THEN lower_bound := upper_bound; END IF; /* Store the lower and upper bounds for future function invocations for the positions. */ scanctx := position_im(0, 0, lower_bound, upper_bound); END IF; /* Fetch the column value corresponding to the rowid, and see if it falls within the determined range. */ col_val_stmt := 'Select col_val FROM ' || storage_tab_name || ' WHERE base_rowid = ''' || indexctx.Rid || ''''; EXECUTE IMMEDIATE col_val_stmt INTO column_value; IF (column_value <= scanctx.upper_bound AND column_value >= scanctx.lower_bound AND scanflg = ODCICONST.RegularCall) THEN RETURN 1; ELSE RETURN 0; END IF; ELSE RAISE_APPLICATION_ERROR(-20101, 'A column that has a domain index of' || 'Position indextype must be the first argument'); END IF; END; /
次の手順は、function_for_position_betweenファンクションを使用するposition_between演算子を作成します。この演算子には、索引列NUMBERを最初の引数として指定し、その後にNUMBERの下限および上限を2番目および3番目の引数として指定します。
CREATE OR REPLACE OPERATOR position_between BINDING (NUMBER, NUMBER, NUMBER) RETURN NUMBER WITH INDEX CONTEXT, SCAN CONTEXT position_im USING function_for_position_between;
索引コンテキストおよびスキャン・コンテキストが、索引ベースのファンクション評価に渡されるように、このCREATE OPERATOR文にはWITH INDEX CONTEXT, SCAN CONTEXT position_im句が含まれています。
次の手順は、position_operatorに必要な索引タイプposition_indextypeを作成します。
CREATE INDEXTYPE position_indextype FOR position_between(NUMBER, NUMBER, NUMBER) USING position_im;
演算子position_betweenは、索引ベースのファンクション実装を使用します。そのため、索引情報がファンクション評価に渡されるように、参照列にドメイン索引を定義する必要があります。そのため、最後の手順では、索引タイプposition_indextypeを使用してドメイン索引salary_indexを作成します。
CREATE INDEX salary_index ON employees(salary) INDEXTYPE IS position_indextype;
これで、演算子position_betweenを使用して、元の問合せを次のように書き換えることができます。
SELECT last_name, salary FROM employees WHERE position_between(salary, 10, 20)=1 ORDER BY salary DESC, last_name; LAST_NAME SALARY ------------------------- ---------- Tucker 10000 King 10000 Baer 10000 Bloom 10000 Fox 9600 Bernstein 9500 Sully 9500 Greene 9500 Hunold 9000 Faviet 9000 McEwen 9000 Hall 9000 Hutton 8800 Taylor 8600 Livingston 8400 Gietz 8300 Chen 8200 Fripp 8200 Weiss 8000 Olsen 8000 Smith 8000 Kaufling 7900
この項では、XMLTypeデータをデータベースで使用する方法を説明します。
サンプル・スキーマoeには表warehousesが含まれ、この表にはXMLType列warehouse_specが含まれます。warehouse_spec情報を持つ別の表を作成するとします。次の例は、暗黙的にCLOB列を1列のみ持つ非常に単純なXMLType表を作成します。
CREATE TABLE xwarehouses OF XMLTYPE;
このような表には、次の文に示すように、XMLType構文を使用してデータを挿入することができます。（この例で挿入されるデータは、サンプル表oe.warehousesのwarehouse_spec列にあるデータに対応します。warehouse_idは1です。）
INSERT INTO xwarehouses VALUES (xmltype('<?xml version="1.0"?> <Warehouse> <WarehouseId>1</WarehouseId> <WarehouseName>Southlake, Texas</WarehouseName> <Building>Owned</Building> <Area>25000</Area> <Docks>2</Docks> <DockType>Rear load</DockType> <WaterAccess>true</WaterAccess> <RailAccess>N</RailAccess> <Parking>Street</Parking> <VClearance>10</VClearance> </Warehouse>'));
この表の問合せには、次の文を使用します。
SELECT e.getClobVal() FROM xwarehouses e;
データは暗黙的にCLOB列に格納されるため、LOB列に対するすべての制限事項が適用されます。これらの制限事項を回避するには、XMLSchemaベースの表を作成します。XMLSchemaは、XML要素を対応するオブジェクト・リレーショナル・データにマップします。次の例は、XMLSchemaをローカルに登録します。XMLSchema（xwarhouses.xsd）には、xwarehouses表と同じ構造が反映されます。（XMLSchemaの宣言ではPL/SQLおよびDBMS_XMLSCHEMAパッケージが使用されています。例では、これらをイタリック体で示しています。）
begin dbms_xmlschema.registerSchema( 'http://www.example.com/xwarehouses.xsd', '<schema xmlns="http://www.w3.org/2001/XMLSchema" targetNamespace="http://www.example.com/xwarehouses.xsd" xmlns:who="http://www.example.com/xwarehouses.xsd" version="1.0"> <simpleType name="RentalType"> <restriction base="string"> <enumeration value="Rented"/> <enumeration value="Owned"/> </restriction> </simpleType> <simpleType name="ParkingType"> <restriction base="string"> <enumeration value="Street"/> <enumeration value="Lot"/> </restriction> </simpleType> <element name = "Warehouse"> <complexType> <sequence> <element name = "WarehouseId" type = "positiveInteger"/> <element name = "WarehouseName" type = "string"/> <element name = "Building" type = "who:RentalType"/> <element name = "Area" type = "positiveInteger"/> <element name = "Docks" type = "positiveInteger"/> <element name = "DockType" type = "string"/> <element name = "WaterAccess" type = "boolean"/> <element name = "RailAccess" type = "boolean"/> <element name = "Parking" type = "who:ParkingType"/> <element name = "VClearance" type = "positiveInteger"/> </sequence> </complexType> </element> </schema>', TRUE, TRUE, FALSE, FALSE); end; /
これで、次の例に示すように、XMLSchemaベースの表を作成できます。
CREATE TABLE xwarehouses OF XMLTYPE XMLSCHEMA "http://www.example.com/xwarehouses.xsd" ELEMENT "Warehouse";
デフォルトでは、この表はオブジェクト・リレーショナル表として格納されます。そのため、次の例に示すように、この表にデータを挿入できます。（この例で挿入されるデータは、サンプル表oe.warehousesのwarehouse_spec列にあるデータに対応します。warehouse_idは1です。）
INSERT INTO xwarehouses VALUES( xmltype.createxml('<?xml version="1.0"?> <who:Warehouse xmlns:who="http://www.example.com/xwarehouses.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.example.com/xwarehouses.xsd http://www.example.com/xwarehouses.xsd"> <WarehouseId>1</WarehouseId> <WarehouseName>Southlake, Texas</WarehouseName> <Building>Owned</Building> <Area>25000</Area> <Docks>2</Docks> <DockType>Rear load</DockType> <WaterAccess>true</WaterAccess> <RailAccess>false</RailAccess> <Parking>Street</Parking> <VClearance>10</VClearance> </who:Warehouse>')); ...
XMLSchemaベースの表には制約を定義できます。その場合は、XML要素Warehouse内の適切な属性を参照するように、XMLDATA疑似列を使用します。
ALTER TABLE xwarehouses ADD (PRIMARY KEY(XMLDATA."WarehouseId"));
xwarehousesのデータはオブジェクト・リレーショナルに格納されるため、可能な場合に基礎となる記憶域を参照できるように、このXMLType表への問合せが書き換えられます。そのため、次の問合せでは、前述の例の主キー制約によって作成された索引を使用します。
SELECT * FROM xwarehouses x WHERE EXISTSNODE(VALUE(x), '/Warehouse[WarehouseId="1"]') = 1, 'xmlns:who="http://www.example.com/xwarehouses.xsd"') = 1; SELECT * FROM xwarehouses x WHERE EXTRACTVALUE(VALUE(x), '/Warehouse/WarehouseId') = 1, 'xmlns:who="http://www.example.com/xwarehouses.xsd"') = 1;
XMLSchemaベースの表に索引を明示的に作成すると、後続の問合せのパフォーマンスが大幅に向上します。XMLType表にオブジェクト・リレーショナル・ビューを作成することも、オブジェクト・リレーショナル表にXMLTypeビューを作成することもできます。
サンプル表oe.warehousesは、XMLType型のwarehouse_spec列を使用して作成されました。記憶域は指定していないため、XMLType列は暗黙的にCLOBとして格納されます。この項の例は、2つのタイプの記憶域を使用して、簡略化したoe.warehouses表を作成します。
最初の例は、CLOBとして格納されたXMLType表を持つ表を作成します。この表ではXMLSchemaが必要ないため、コンテンツ構造は事前に定義しません。
CREATE TABLE xwarehouses ( warehouse_id NUMBER, warehouse_spec XMLTYPE) XMLTYPE warehouse_spec STORE AS CLOB (TABLESPACE example STORAGE (INITIAL 6144 NEXT 6144) CHUNK 4000 NOCACHE LOGGING);
次の例でも前述の例とほぼ同じ表を作成しますが、指定されたXMLSchemaによって構造が決められているオブジェクト・リレーショナルXMLType列に、XMLTypeデータが格納されます。
CREATE TABLE xwarehouses ( warehouse_id NUMBER, warehouse_spec XMLTYPE) XMLTYPE warehouse_spec STORE AS OBJECT RELATIONAL XMLSCHEMA "http://www.example.com/xwarehouses.xsd" ELEMENT "Warehouse";
|
 Copyright © 1996, 2008, Oracle Corporation. All Rights Reserved. |
|