| Oracle XML Developer's Kitプログラマーズ・ガイド 10gリリース3（10.1.3） B28611-01 |
|
 戻る |
 次へ |
この章の内容は次のとおりです。
XSUのSQL Utility（XSU）PL/SQL APIは、データベースからのXML文書の生成およびデータベースへのXML文書の格納方法がJava APIに似ています。DBMS_XMLQueryおよびDBMS_XMLSaveは、Javaクラス（OracleXMLQueryおよびOracleXMLSave）のファンクションが反映された2つのパッケージです。どちらのパッケージにも、パッケージに対応付けられたコンテキスト・ハンドルがあります。コンストラクタに類似したファンクションの1つをコールしてコンテキストを作成し、ハンドルを取得してそのハンドルをすべての副問合せコールに使用します。
XSUによるXMLTypeのサポート
Oracle9iリリース2（9.2）から、XSUはXMLTypeをサポートしています。XSUでのXMLTypeの使用は、オブジェクトまたは表内にXMLType列が含まれる場合などに有効です。
|
関連資料: XSUでのXMLTypeの使用例については、『Oracle XML DB開発者ガイド』の特にXMLの生成に関する章を参照してください。 |
XMLを生成すると、XML文書を含むCLOBが生成されます。DBMS_XMLQueryおよびXSU生成エンジンを使用するための手順は次のとおりです。
DBMS_XMLQuery.getCtxファンクションをコールし、CLOBまたはVARCHAR2のいずれかで問合せを指定して、コンテキスト・ハンドルを作成します。
DBMS_XMLQuery.bindファンクションを使用して、可能性のある値を問合せにバインドします。バインドは、名前を位置にバインドすることで行われます。たとえば、問合せはselect * from employees where employee_id = :EMPNO_VARになります。ここではsetBindValueファンクションを使用して、EMPNO_VARの値をバインドしています。
ROWタグ名、ROWSETタグ名、フェッチする行の数などの引数をオプションで設定します。
getXML()ファンクションを使用してCLOBとしてXMLをフェッチします。getXML()をコールすると、DTDまたはスキーマの有無にかかわらずXMLを生成できます。
コンテキストをクローズします。
次に、DBMS_XMLQuery PL/SQLパッケージを使用する例を示します。
この例では、employees表から行を選択し、XML文書をCLOBとして取得します。最初に問合せを渡してコンテキスト・ハンドルを取得し、次にgetXMLClobルーチンをコールしてCLOB値を取得します。文書のエンコーディングは、データベース・キャラクタ・セットのエンコーディングと同じになります。
declare
queryCtx DBMS_XMLquery.ctxType;
result CLOB;
begin
-- set up the query context...!
queryCtx := DBMS_XMLQuery.newContext('select * from employees');
-- get the result..!
result := DBMS_XMLQuery.getXML(queryCtx);
-- Now you can use the result to put it in tables/send as messages..
printClobOut(result);
DBMS_XMLQuery.closeContext(queryCtx); -- you must close the query handle..
end;
printClobOut()は、CLOBを出力バッファへ出力する単純なファンクションです。このPL/SQLコードをSQL*Plusで実行する場合、CLOBの結果は画面に出力されます。結果を表示するには、serveroutputをオンに設定します。表示バッファを増やして全出力を表示する必要がある場合があります。
set serveroutput on size 200000 set long 20000
次にコードを示します。
CCREATE OR REPLACE PROCEDURE printClobOut(result IN OUT NOCOPY CLOB) is
xmlstr varchar2(32767);
line varchar2(2000);
begin
xmlstr := dbms_lob.SUBSTR(result,32767);
loop
exit when xmlstr is null;
line := substr(xmlstr,1,instr(xmlstr,chr(10))-1);
dbms_output.put_line('| '||line);
xmlstr := substr(xmlstr,instr(xmlstr,chr(10))+1);
end loop;
end;
XSUのPL/SQL APIを使用して、ROWタグ名およびROWSETタグ名を変更することもできます。これらはデフォルトの名前であり、結果の各行の先頭と末尾、および出力文書全体の先頭と末尾にそれぞれ置かれます。これは、次の例に示すように、setRowTagNameおよびsetRowSetTagNameプロシージャによって実行されます。
--Setting the ROW tag names
declare
queryCtx DBMS_XMLQuery.ctxType;
result CLOB;
begin
-- set the query context.
queryCtx := DBMS_XMLQuery.newContext('select * from employees');
DBMS_XMLQuery.setRowTag(queryCtx,'EMP'); -- sets the row tag name
DBMS_XMLQuery.setRowSetTag(queryCtx,'EMPSET'); -- sets rowset tag name
result := DBMS_XMLQuery.getXML(queryCtx); -- get the result
printClobOut(result); -- print the result..!
DBMS_XMLQuery.closeContext(queryCtx); -- close the query handle;
end;
結果のXML文書には、EMPSET文書要素が含まれます。各行は、EMPタグによって区切られています。
次のファンクションを使用して、問合せの生成からの結果ページを区切ることができます。
たとえば、employees表の最初の3行をスキップし、残りの行を一度に10行ずつ出力するには、最初の10行のバッチでskipRowsを3に設定し、残りのバッチで0に設定できます。
XML SQL UtilityのJava APIの場合のように、フェッチ間の状態を維持するには、keepObjectOpen()ファンクションをコールします。デフォルトの動作では、フェッチ後に状態をクローズします。複数のフェッチの場合は、フェッチする行がなくなったときを確認する必要があります。これを行うには、setRaiseNoRowsException()を設定します。この設定により、CLOBへの行の書込みがなかった場合に例外を発生させます。この例外は、終了条件として捕捉および使用できます。
-- Pagination of results
--Setting the ROW tag names
declare
queryCtx DBMS_XMLQuery.ctxType;
result CLOB;
begin
-- set the query context.
queryCtx := DBMS_XMLQuery.newContext('select * from employees');
DBMS_XMLQuery.setRowTag(queryCtx,'EMP'); -- sets the row tag name
DBMS_XMLQuery.setRowSetTag(queryCtx,'EMPSET'); -- sets rowset tag name
result := DBMS_XMLQuery.getXML(queryCtx); -- get the result
printClobOut(result); -- print the result..!
DBMS_XMLQuery.closeContext(queryCtx); -- close the query handle;
end;
XSUのPL/SQL APIは、次の方法で、生成したXML文書にスタイルシートを設定する機能を提供します。
結果のXMLにスタイルシート・ヘッダーを設定します。これを行うには、setStylesheetHeader()プロシージャを使用して、結果にスタイルシート・ヘッダーを設定します。このプロシージャは、XML処理命令を追加して、スタイルシートを含めます。
生成の前に、結果のXML文書にスタイルシートを適用します。このメソッドを使用すると、パフォーマンスが大幅に向上します。このメソッドを使用しない場合は、XML文書をCLOBとして生成し、再度パーサーに送信し、スタイルシートを適用する必要があります。XSUはDOM文書を生成し、XMLパーサーをコールし、スタイルシートを適用してから結果を生成します。結果のXML文書にスタイルシートを適用するには、setXSLT()プロシージャを使用します。このプロシージャは、スタイルシートを使用して結果を生成します。
XSUのPL/SQL APIは、SQL文に値をバインドする機能を提供します。SQL文には、名前付きバインド変数を指定できます。バインド変数であることを宣言するには、このバインド変数の前にコロン（:）を付ける必要があります。バインド変数を使用するには、次の手順を実行します。
バインド変数を含む問合せで問合せコンテキストを初期化します。たとえば、次の文は、バインド変数:EMPLOYEE_IDおよび:FIRST_NAMEを含むWHERE句を使用して、employees表から行を選択する問合せを登録します。従業員番号の値と従業員のファースト・ネームの値を後でバインドします。
queryCtx = DBMS_XMLQuery.getCtx('select * from employees where employee_id =
:EMPLOYEE_ID and first_name = :FIRST_NAME');
バインド値のリストを設定します。clearBindValues()は、すべてのバインド変数の設定を消去します。setBindValue()は、単一のバインド変数を文字列値で設定します。たとえば、次に示すようにempnoおよびename値を設定します。
DBMS_XMLQuery.clearBindValues(queryCtx); DBMS_XMLQuery.setBindValue(queryCtx,'EMPLOYEE_ID',20); DBMS_XMLQuery.setBindValue(queryCtx,'FIRST_NAME','John');
結果をフェッチします。文にバインド値が適用され、条件employee_id = 20およびfirst_name = 'John'に対応する結果が取得されます。
DBMS_XMLQuery.getXMLClob(queryCtx);
必要に応じて、値を再バインドします。たとえば、FIRST_NAMEのみをscottに変更し、問合せを再実行します。
DBMS_XMLQuery.setBindValue(queryCtx,'FIRST_NAME','Scott');
FIRST_NAMEの再バインディングには、JohnではなくScottが使用されます。
次に、SQL文でバインド変数を使用する例を示します。
declare
queryCtx DBMS_XMLquery.ctxType;
result CLOB;
begin
queryCtx := DBMS_XMLQuery.newContext('select * from employees where employee_id
= :EMPLOYEE_ID and first_name = :FIRST_NAME');
--No longer needed:
--DBMS_XMLQuery.clearBindValues(queryCtx);
DBMS_XMLQuery.setBindValue(queryCtx,'EMPLOYEE_ID',100);
DBMS_XMLQuery.setBindValue(queryCtx,'FIRST_NAME','Steven');
result := DBMS_XMLQuery.getXML(queryCtx);
--printClobOut(result);
DBMS_XMLQuery.setBindValue(queryCtx,'FIRST_NAME','Neena');
result := DBMS_XMLQuery.getXML(queryCtx);
--printClobOut(result);
end;
create or replace procedure insProc(xmlDoc IN CLOB, tableName IN VARCHAR2) is
insCtx DBMS_XMLSave.ctxType;
rows number;
begin
insCtx := DBMS_XMLSave.newContext(tableName); -- get the context handle
rows := DBMS_XMLSave.insertXML(insCtx,xmlDoc); -- this inserts the document
DBMS_XMLSave.closeContext(insCtx); -- this closes the handle
end;
DBMS_XMLSaveおよびXML SQL Utility格納エンジンを使用するには、次の手順を実行します。
DBMS_XMLSave.getCtxファンクションをコールし、DML操作に使用する表名を指定して、コンテキスト・ハンドルを作成します。
挿入の場合。setUpdateColNamesファンクションを使用して挿入する列のリストを設定できます。デフォルトでは、すべての列に値が挿入されます。
更新の場合。キー列のリストを指定する必要があります。オプションで、更新するキー列のリストを指定することもできます。この場合は、キー列名と一致するXML文書内のタグがUPDATE文のWHERE句に使用され、更新列のリストと一致するタグがUPDATE文のSET句に使用されます。
削除の場合。デフォルトで、指定した文書の各ROW要素にあるすべてのタグ値と一致するWHERE句が作成されます。この動作は、キー列のリストを設定することでオーバーライドできます。この場合は、タグ名がリスト内の列と一致するタグ値のみが、削除する行の識別に使用されます（DELETE文のWHERE句に使用して有効）。
挿入、更新および削除するには、insertXML、updateXMLまたはdeleteXMLファンクションにそれぞれXML文書を指定します。
最後の操作を必要な回数分繰り返すことができます。
コンテキストをクローズします。
Javaの例OracleXMLSaveクラスと同じ例を使用してください。
表またはビューに文書を挿入するには、表名またはビュー名、およびXML文書を指定します。XSUはXML文書（文字列が指定されている場合）を解析し、INSERT文を作成してこの文にすべての値をバインドします。デフォルトでは、XSUは表またはビューのすべての列に値を挿入します。存在しない要素はNULL値として処理されます。
次のコードは、employees表から生成した文書を比較的簡単に表に格納できる方法を示します。
この例では、次を受け入れるプロシージャinsProcを作成します。
CLOBとしてのXML文書
文書を挿入する表の名前
XML文書を表に挿入します。
create or replace procedure insProc(xmlDoc IN CLOB, tableName IN VARCHAR2) is
insCtx DBMS_XMLSave.ctxType;
rows number;
begin
insCtx := DBMS_XMLSave.newContext(tableName); -- get the context handle
rows := DBMS_XMLSave.insertXML(insCtx,xmlDoc); -- this inserts the document
DBMS_XMLSave.closeContext(insCtx); -- this closes the handle
end;
これでXML文書と表名を使用してこのプロシージャをコールできます。たとえば次のようなコールになります。
execute insProc(xmlDocument, 'hr.employees');
このコールでは、次の形式のINSERT文が生成されます。
INSERT INTO hr.employees (employee_id, last_name, job_id, manager_id, hire_date,
salary, department_id VALUES(?,?,?,?,?,?,?);
列名と一致している入力XML文書内の要素タグが照合され、その値がバインドされます。前述の抜粋コードで、次のXML文書を送信するとします。
<?xml version='1.0'?>
<ROWSET>
<ROW num="1">
<EMPLOYEE_ID>7369</EMPLOYEE_ID>
<LAST_NAME>Smith</LAST_NAME>
<JOB_ID>CLERK</JOB_ID>
<MANAGER_ID>7902</MANAGER_ID>
<HIRE_DATE>12/17/1980 0:0:0</HIRE_DATE>
<SALARY>800</SALARY>_
<DEPARTMENT_ID>20</DEPARTMENT_ID>
</ROW>
<!-- additional rows ... -->
</ROWSET>
指定した列に対し、7369、Smith、CLERK、7902、12/17/1980、800および20の値を含むemployees表に新しい行が生成されます。行要素内に存在しない要素は、NULL値として扱われます。
すべての列に値を挿入する必要がない場合もあります。取得する値が完全なセットではなく、残りの列のためにトリガーまたはデフォルト値を使用する必要がある場合などが当てはまります。次のコードに、この実行方法を示します。
従業員番号、名前、仕事の値のみを取得して、給与、管理者、部署番号、入社日の各フィールドに値が自動的に挿入されるとします。挿入を実行する列名のリストを作成して、そのリストをDBMS_XMLSaveプロシージャに渡します。setUpdateColumnName()プロシージャを繰り返しコールし、コールするたびに更新する列名を渡すことで、これらの値を設定できます。列名の設定は、clearUpdateColumnNames()を使用して消去できます。
create or replace procedure testInsert( xmlDoc IN clob) is
insCtx DBMS_XMLSave.ctxType;
doc clob;
rows number;
begin
insCtx := DBMS_XMLSave.newContext('hr.employees'); -- get the save context..!
DBMS_XMLSave.clearUpdateColumnList(insCtx); -- clear the update settings
-- set the columns to be updated as a list of values..
DBMS_XMLSave.setUpdateColumn(insCtx,'EMPLOYEE_ID');
DBMS_XMLSave.setUpdateColumn(insCtx,'LAST_NAME');
DBMS_XMLSave.setUpdatecolumn(insCtx,'JOB_ID');
-- Now insert the doc. This will only insert into EMPLOYEE_ID, LAST_NAME, and
-- JOB_ID columns
rows := DBMS_XMLSave.insertXML(insCtx, xmlDoc);
DBMS_XMLSave.closeContext(insCtx);
end;
/
文書としてCLOBに渡すプロシージャをコールする場合、次の形式のINSERT文が生成されます。
INSERT INTO hr.employees (employee_id, last_name, job_id) VALUES (?, ?, ?);
前述の例では、挿入した文書に他の列の値（HIRE_DATEなど）が含まれていても、それらは無視されることに注意してください。
入力に存在するROW要素ごとに挿入が実行されます。これらの挿入はデフォルトでバッチされます。
XML文書から表への値の挿入方法を理解したところで、特定の値のみを更新する方法を見ていきます。XML文書で従業員の給与とその従業員が所属する部署も更新する場合、次のようになります。
<ROWSET>
<ROW num="1">
<EMPLOYEE_ID>7369</EMPLOYEE_ID>
<SALARY>1800</SALARY>
<DEPARTMENT_ID>30</DEPARTMENT_ID>
</ROW>
<ROW>
<EMPLOYEE_ID>2290</EMPLOYEE_ID>
<SAARY>2000</SALARY>
<HIRE_DATE>12/31/1992</HIRE_DATE>
<!-- additional rows ... -->
</ROWSET>
更新処理をコールして値を更新できます。更新の場合は、キー列名のリストを使用してXSUを指定する必要があります。これらの列は、UPDATE文のWHERE句で構成されます。以前に示したemployees表では、従業員番号（employee_ID）列がキーであり、更新にはこのキーが使用されます。
次のPL/SQLプロシージャについて考えてみます。
create or replace procedure testUpdate ( xmlDoc IN clob) is
updCtx DBMS_XMLSave.ctxType;
rows number;
begin
updCtx := DBMS_XMLSave.newContext('hr.employees'); -- get the context
DBMS_XMLSave.clearUpdateColumnList(updCtx); -- clear the update settings..
DBMS_XMLSave.setKeyColumn(updCtx,'EMPLOYEE_ID'); -- set EMPLOYEE_ID as key column
rows := DBMS_XMLSave.updateXML(updCtx,xmlDoc); -- update the table.
DBMS_XMLSave.closeContext(updCtx); -- close the context..!
end;
/
この例では、前述の文書を含むCLOB値でプロシージャが実行される場合、2つのUPDATE文が生成されます。最初のROW要素では、UPDATE文を生成して、次のようにフィールドを更新します。
UPDATE hr.employees SET salary = 1800 AND department_id = 30 WHERE employee_id = 7369;
2番目のROW要素の場合は次のようになります。
UPDATE hr.employees SET salary = 2000 AND hire_date = 12/31/1992 WHERE employee_id = 2290;
更新する列のリストを指定できます。すべてのROW要素に対して同じUPDATE文を使用できるため、列リストを指定すると処理速度が向上します。また、文書内にある他のタグを無視できます。更新する列のリストを指定すると、更新する列に対応する要素が存在しない場合は、その要素がNULLとして処理されることに注意してください。
更新するすべての要素がXML文書のすべてのROW要素と同じであるとわかれば、setUpdateColumnNames()プロシージャを使用して、更新する列名を設定できます。
create or replace procedure testUpdate(xmlDoc IN CLOB) is
updCtx DBMS_XMLSave.ctxType;
rows number;
begin
updCtx := DBMS_XMLSave.newContext('hr.employees');
DBMS_XMLSave.setKeyColumn(updCtx,'EMPLOYEE_ID'); -- set EMPLOYEE_ID as key column
-- set list of columnst to update.
DBMS_XMLSave.setUpdateColumn(updCtx,'SALARY');
DBMS_XMLSave.setUpdateColumn(updCtx,'JOB_ID');
rows := DBMS_XMLSave.updateXML(updCtx,xmlDoc); -- update the XML document..!
DBMS_XMLSave.closeContext(updCtx); -- close the handle
end;
削除の場合は、キー列のリストを設定できます。これらの列は、DELETE文のWHERE句の一部として挿入されます。キー列名が指定されていない場合、新しいDELETE文がXML文書のROW要素ごとに作成されます。DELETE文のWHERE句の列のリストは、ROW要素の列と一致します。
次に示すdeleteの例について考えてみます。
create or replace procedure testDelete(xmlDoc IN clob) is
delCtx DBMS_XMLSave.ctxType;
rows number;
begin
delCtx := DBMS_XMLSave.newContext('hr.employees');
DBMS_XMLSave.setKeyColumn(delCtx,'EMPLOYEE_ID');
rows := DBMS_XMLSave.deleteXML(delCtx,xmlDoc);
DBMS_XMLSave.closeContext(delCtx);
end;
更新の例と同じXML文書を使用する場合、次の2つのDELETE文で終了します。
DELETE FROM hr.employees WHERE employee_id=7369 AND salary=1800 AND department_id=30; DELETE FROM hr.employees WHERE employee_id=2200 AND salary=2000 AND hire_date=12/31/1992;
XML文書内の各ROW要素にあるタグ名に基づいてDELETE文が形成されました。
かわりに削除処理の条件としてキー値のみを使用する場合は、setKeyColumnファンクションを使用して設定できます。
create or replace package testDML AS
saveCtx DBMS_XMLSave.ctxType := null; -- a single static variable
procedure insertXML(xmlDoc in clob);
procedure updateXML(xmlDoc in clob);
procedure deleteXML(xmlDoc in clob);
end;
create or replace package body testDML AS
rows number;
procedure insertXML(xmlDoc in clob) is
begin
rows := DBMS_XMLSave.insertXML(saveCtx,xmlDoc);
end;
procedure updateXML(xmlDoc in clob) is
begin
rows := DBMS_XMLSave.updateXML(saveCtx,xmlDoc);
end;
procedure deleteXML(xmlDoc in clob) is
begin
rows := DBMS_XMLSave.deleteXML(saveCtx,xmlDoc);
end;
begin
saveCtx := DBMS_XMLSave.newContext('hr.employees'); -- create the context once
DBMS_XMLSave.setKeyColumn(saveCtx, 'EMPLOYEE_ID'); -- set the key column name.
end;
次に、単一のDELETE文を示します。
DELETE FROM hr.employees WHERE employee_id=?
これは、文書内のすべてのROW要素に対して生成および使用されます。
前述の3つの例（挿入、更新および削除）では、同じコンテキスト・ハンドルを使用して複数の操作を実行できます。saveコンテキストを作成したときに指定した同一の表に対してすべての挿入が行われる場合は、同じコンテキストを使用して複数の挿入を実行できます。このコンテキストを使用して、更新、削除および挿入を組み合せることもできます。
たとえば、次の例では、同じコンテキストと設定を使用して、ユーザーの入力に基づいて値を挿入、削除または更新する方法を示します。
この例では、すべてのファンクション・コールで同じコンテキストが使用されるように、PL/SQLパッケージの静的変数を使用してコンテキストを格納します。
create or replace package testDML AS
saveCtx DBMS_XMLSave.ctxType := null; -- a single static variable
procedure insert(xmlDoc in clob);
procedure update(xmlDoc in clob);
procedure delete(xmlDoc in clob);
end;
/
create or replace package body testDML AS
procedure insert(xmlDoc in clob) is
row number;
begin
row := DBMS_XMLSave.insertXML(saveCtx, xmlDoc);
end;
procedure update(xmlDoc in clob) is
begin
row := DBMS_XMLSave.updateXML(saveCtx, xmlDoc);
end;
procedure delete(xmlDoc in clob) is
begin
row := DBMS_XMLSave.deleteXML(saveCtx, xmlDoc);
end;
begin
saveCtx := DBMS_XMLSave.newContext('hr.employees'); -- create the context
once..!
DBMS_XMLSave.setKeyColumn(saveCtx, 'EMPLOYEE_ID'); -- set the key column name.
end;
end;
/
前述のパッケージでは、パッケージ全体（セッション）のためのコンテキストを一度作成し、挿入、更新および削除の実行に同じコンテキストを再利用します。
|
注意: キー列EMPNOが、行を識別する方法として更新と削除の両方に使用されます。
|
このパッケージのユーザーは、3つのルーチンのどれをコールしてもemployees表を更新できます。
testDML.insert(xmlclob); testDML.delete(xmlclob); testDML.update(xmlclob);
これらのコールはすべて、同じコンテキストを使用します。各コールが同じコンテキストを使用することで、これらの操作が頻繁に実行される場合は特に操作のパフォーマンスが向上します。
次に、XSUのPL/SQL例外の処理例を示します。
declare
queryCtx DBMS_XMLQuery.ctxType;
result clob;
errorNum NUMBER;
errorMsg VARCHAR2(200);
begin
queryCtx := DBMS_XMLQuery.newContext('select * from employees where df = dfdf');
-- set the raise exception to true..
DBMS_XMLQuery.setRaiseException(queryCtx, true);
DBMS_XMLQuery.setRaiseNoRowsException(queryCtx, true);
-- set propagate original exception to true to get the original exception..!
DBMS_XMLQuery.propagateOriginalException(queryCtx,true);
result := DBMS_XMLQuery.getXML(queryCtx);
exception
when others then
-- get the original exception
DBMS_XMLQuery.getExceptionContent(queryCtx,errorNum, errorMsg);
dbms_output.put_line(' Exception caught ' || TO_CHAR(errorNum)
|| errorMsg );
end;
/
