GaragebandとiMovieで



検索

  • Google
フォト

Twitter

オンラインショッピング


2023年2月
日 月 火 水 木 金 土
      1 2 3 4
5 6 7 8 9 10 11
12 13 14 15 16 17 18
19 20 21 22 23 24 25
26 27 28        

気まぐれ写真館

  • spider lily - 白と赤の彼岸花（曼珠沙華）

カテゴリー

Oracle

Apple

その他

  • ブログ de ノベルティ
    ノベルティグッズをただ、ただ、記録していくブログ。Mac De Oracleでは出来ないネタなので、別ブログを立てた次第。

Googleの広告



RSS

ブログ:ココログ

当サイトについて

プライバシーポリシー
プライバシーポリシー

「個人情報の保護に関する法律」に基づき、「個人情報保護の基本方針（プライバシーポリシー）」を
定め個人情報の適切な管理・保護に努めることを表明します。

個人情報保護の基本方針（プライバシーポリシー）

1.　個人情報の利用目的をできるだけ特定し、特定された利用目的の達成に必要な範囲を超えて利用
　　することはありません。
2.　個人情報を偽りその他不正な手段による取得しません。取得したときは、本人に速やかに利用目的
　　を通知又は公表します。
3.　個人情報を利用目的の範囲内で正確かつ最新の内容に保つように努めます。
4.　個人情報の漏洩や滅失を防ぐために、必要かつ適切な安全管理措置を講じます。
5.　あらかじめ本人の同意を得ないで第三者に個人情報を提供することはありません。
　　ただし、法令に基づく場合、人の生命、身体又は財産の保護に必要な場合、公衆衛生・児童の健全育成に
　　特に必要な場合、国等に協力する場合は除きます。

« 日本の借金時計 - java applet版 久々にバージョンアップしました | トップページ | Oracle de XMLDB #23 - Relational dataをXMLへ #6 »

2008年2月19日 (火) / Author : Hiroshi Sekiguchi.

Oracle de XMLDB #22 - Relational dataをXMLへ #5

リレーショナルデータを取り出してからXMLへ変換する方法へ行く前にもう一つあるのを忘れてました。
SYS_XMLAGG()とXMLELEMENT()とXMLFOREST()関数を利用する方法。
Oracle9i R1から提供されていた関数だったが、古くから提供されていたので完璧に忘れてました。m(_ _)m
（マニュアルを調べた上でOracle9i R1から提供されていた関数と書いたが・・・もし、Oracle8i 8.1.7あたりからあったよ！というツッコミがあればご遠慮なく。）

バックナンバー：
Oracle de XMLDB #18 - Relational dataをXMLへ #1

Oracle de XMLDB #19 - Relational dataをXMLへ #2
Oracle de XMLDB #20 - Relational dataをXMLへ #3
Oracle de XMLDB #21 - Relational dataをXMLへ #4

2008/2/19 9:0:0
文字参照のミスを修正しました。


尚、他のXQueryの結果に関してはいままでもこちらでは制御できない改行が挿入された結果だったり、逆に取り除かれた結果が返されたりしていたが、前述の関数を利用した場合は全体に整形用改行が入るのではなく、SYS_XMLAGG()関数でマージされたXMLELEMENT()の結果からは改行は取り除かれているが、XML宣言やルート要素タグの後には何故か改行が付加される。（要素間の改行やインデントなどは目視する際に見やすくするためなので、あっても無くていいのだが、なんとも気持ちの悪い付け方をしてくれる。。。）

また、いままで実行してきた各SQL文は、それぞれ２回実行してからalter system flush shared_poolを１度実行した後に実行してある。（実行環境は以前と同じ）

SQL*Plus: Release 10.1.0.3.0 - Production on 火 2月 19 00:00:09 2008

Copyright (c) 1982, 2004, Oracle.  All rights reserved.

> conn scott/tiger@lampeye
接続されました。
SCOTT> set long 4000
SCOTT> set pagesize 1000
SCOTT> select
  2    sys_xmlagg(
  3      xmlelement("ROW",
  4        xmlforest( 
  5          empno,
  6          ename,
  7          job,
  8          mgr,
  9          hiredate,
 10          sal,
 11          comm,
 12          deptno
 13    ))) xmldoc
 14  from
 15    emp
 16  order by
 17    empno;

XMLDOC
--------------------------------------------------------------------------------
<?xml version="1.0"?>
<ROWSET>
<ROW><EMPNO>9999</EMPNO><ENAME>ほげ</ENAME><JOB>ENGINEER</JOB><MGR>7566</MGR><HI
REDATE>2008-01-01</HIREDATE><SAL>2500</SAL><DEPTNO>20</DEPTNO></ROW><ROW><EMPNO>
7369</EMPNO><ENAME>SMITH</ENAME><JOB>CLERK</JOB><MGR>7902</MGR><HIREDATE>1980-12
-17</HIREDATE><SAL>800</SAL><DEPTNO>20</DEPTNO></ROW><ROW><EMPNO>7499</EMPNO><EN
AME>ALLEN</ENAME><JOB>SALESMAN</JOB><MGR>7698</MGR><HIREDATE>1981-02-20</HIREDAT
E><SAL>1600</SAL><COMM>300</COMM><DEPTNO>30</DEPTNO></ROW><ROW><EMPNO>7521</EMPN
O><ENAME>WARD</ENAME><JOB>SALESMAN</JOB><MGR>7698</MGR><HIREDATE>1981-02-22</HIR
EDATE><SAL>1250</SAL><COMM>500</COMM><DEPTNO>30</DEPTNO></ROW><ROW><EMPNO>7566</
EMPNO><ENAME>JONES</ENAME><JOB>MANAGER</JOB><MGR>7839</MGR><HIREDATE>1981-04-02<
/HIREDATE><SAL>2975</SAL><DEPTNO>20</DEPTNO></ROW><ROW><EMPNO>7654</EMPNO><ENAME
>MARTIN</ENAME><JOB>SALESMAN</JOB><MGR>7698</MGR><HIREDATE>1981-09-28</HIREDATE>
<SAL>1250</SAL><COMM>1400</COMM><DEPTNO>30</DEPTNO></ROW><ROW><EMPNO>7698</EMPNO
><ENAME>BLAKE</ENAME><JOB>MANAGER</JOB><MGR>7839</MGR><HIREDATE>1981-05-01</HIRE
DATE><SAL>2850</SAL><DEPTNO>30</DEPTNO></ROW><ROW><EMPNO>7782</EMPNO><ENAME>CLAR
K</ENAME><JOB>MANAGER</JOB><MGR>7839</MGR><HIREDATE>1981-06-09</HIREDATE><SAL>24
50</SAL><DEPTNO>10</DEPTNO></ROW><ROW><EMPNO>7839</EMPNO><ENAME>KING</ENAME><JOB
>PRESIDENT</JOB><HIREDATE>1981-11-17</HIREDATE><SAL>5000</SAL><DEPTNO>10</DEPTNO
></ROW><ROW><EMPNO>7844</EMPNO><ENAME>TURNER</ENAME><JOB>SALESMAN</JOB><MGR>7698
</MGR><HIREDATE>1981-09-08</HIREDATE><SAL>1500</SAL><COMM>0</COMM><DEPTNO>30</DE
PTNO></ROW><ROW><EMPNO>7900</EMPNO><ENAME>JAMES</ENAME><JOB>CLERK</JOB><MGR>7698
</MGR><HIREDATE>1981-12-03</HIREDATE><SAL>950</SAL><DEPTNO>30</DEPTNO></ROW><ROW
><EMPNO>7902</EMPNO><ENAME>FORD</ENAME><JOB>ANALYST</JOB><MGR>7566</MGR><HIREDAT
E>1981-12-03</HIREDATE><SAL>3000</SAL><DEPTNO>20</DEPTNO></ROW><ROW><EMPNO>7934<
/EMPNO><ENAME>MILLER</ENAME><JOB>CLERK</JOB><MGR>7782</MGR><HIREDATE>1982-01-23<
/HIREDATE><SAL>1300</SAL><DEPTNO>10</DEPTNO></ROW></ROWSET>


経過: 00:00:00.41
SCOTT>


ちなみに、もうお気づきだと思うが、上記のようにorder by句を付けたとしてもエラーにはならないが指定した列でソートされることはないのでご注意を！
ソートした結果が欲しい場合には副問い合せを利用するとよいだろう。


SCOTT> select
  2    sys_xmlagg(
  3      xmlelement("ROW",
  4        xmlforest(
  5          empno,
  6          ename,
  7          job,
  8          mgr,
  9          hiredate,
 10          sal,
 11          comm,
 12          deptno
 13    ))) xmldoc
 14  from
 15    (select * from emp order by empno) alias_of_emp
 16  /

XMLDOC
--------------------------------------------------------------------------------
<?xml version="1.0"?>
<ROWSET>
<ROW><EMPNO>7369</EMPNO><ENAME>SMITH</ENAME><JOB>CLERK</JOB><MGR>7902</MGR><HIRE
DATE>1980-12-17</HIREDATE><SAL>800</SAL><DEPTNO>20</DEPTNO></ROW><ROW><EMPNO>749
9</EMPNO><ENAME>ALLEN</ENAME><JOB>SALESMAN</JOB><MGR>7698</MGR><HIREDATE>1981-02
-20</HIREDATE><SAL>1600</SAL><COMM>300</COMM><DEPTNO>30</DEPTNO></ROW><ROW><EMPN
O>7521</EMPNO><ENAME>WARD</ENAME><JOB>SALESMAN</JOB><MGR>7698</MGR><HIREDATE>198
1-02-22</HIREDATE><SAL>1250</SAL><COMM>500</COMM><DEPTNO>30</DEPTNO></ROW><ROW><
EMPNO>7566</EMPNO><ENAME>JONES</ENAME><JOB>MANAGER</JOB><MGR>7839</MGR><HIREDATE
>1981-04-02</HIREDATE><SAL>2975</SAL><DEPTNO>20</DEPTNO></ROW><ROW><EMPNO>7654</
EMPNO><ENAME>MARTIN</ENAME><JOB>SALESMAN</JOB><MGR>7698</MGR><HIREDATE>1981-09-2
8</HIREDATE><SAL>1250</SAL><COMM>1400</COMM><DEPTNO>30</DEPTNO></ROW><ROW><EMPNO
>7698</EMPNO><ENAME>BLAKE</ENAME><JOB>MANAGER</JOB><MGR>7839</MGR><HIREDATE>1981
-05-01</HIREDATE><SAL>2850</SAL><DEPTNO>30</DEPTNO></ROW><ROW><EMPNO>7782</EMPNO
><ENAME>CLARK</ENAME><JOB>MANAGER</JOB><MGR>7839</MGR><HIREDATE>1981-06-09</HIRE
DATE><SAL>2450</SAL><DEPTNO>10</DEPTNO></ROW><ROW><EMPNO>7839</EMPNO><ENAME>KING
</ENAME><JOB>PRESIDENT</JOB><HIREDATE>1981-11-17</HIREDATE><SAL>5000</SAL><DEPTN
O>10</DEPTNO></ROW><ROW><EMPNO>7844</EMPNO><ENAME>TURNER</ENAME><JOB>SALESMAN</J
OB><MGR>7698</MGR><HIREDATE>1981-09-08</HIREDATE><SAL>1500</SAL><COMM>0</COMM><D
EPTNO>30</DEPTNO></ROW><ROW><EMPNO>7900</EMPNO><ENAME>JAMES</ENAME><JOB>CLERK</J
OB><MGR>7698</MGR><HIREDATE>1981-12-03</HIREDATE><SAL>950</SAL><DEPTNO>30</DEPTN
O></ROW><ROW><EMPNO>7902</EMPNO><ENAME>FORD</ENAME><JOB>ANALYST</JOB><MGR>7566</
MGR><HIREDATE>1981-12-03</HIREDATE><SAL>3000</SAL><DEPTNO>20</DEPTNO></ROW><ROW>
<EMPNO>7934</EMPNO><ENAME>MILLER</ENAME><JOB>CLERK</JOB><MGR>7782</MGR><HIREDATE
>1982-01-23</HIREDATE><SAL>1300</SAL><DEPTNO>10</DEPTNO></ROW><ROW><EMPNO>9999</
EMPNO><ENAME>ほげ</ENAME><JOB>ENGINEER</JOB><MGR>7566</MGR><HIREDATE>2008-01-01<
/HIREDATE><SAL>2500</SAL><DEPTNO>20</DEPTNO></ROW></ROWSET>


経過: 00:00:00.41


さらに前回までに紹介した、DBMS_XMLGEN.GETXMLやDBMS_XMLGEN.GETXMLTYPE()では関数に渡すクエリーにorder by句を付加すればソートできる。

SCOTT> select
  2    dbms_xmlgen.getxml(sqlQuery=>'select * from emp order by empno') xmldoc
  3  from dual
  4  /

XMLDOC
--------------------------------------------------------------------------------
<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <EMPNO>7369</EMPNO>
  <ENAME>SMITH</ENAME>
  <JOB>CLERK</JOB>
  <MGR>7902</MGR>
  <HIREDATE>80-12-17</HIREDATE>
  <SAL>800</SAL>
  <DEPTNO>20</DEPTNO>
 </ROW>
 <ROW>
  <EMPNO>7499</EMPNO>
  <ENAME>ALLEN</ENAME>
  <JOB>SALESMAN</JOB>
  <MGR>7698</MGR>
  <HIREDATE>81-02-20</HIREDATE>
  <SAL>1600</SAL>
  <COMM>300</COMM>
  <DEPTNO>30</DEPTNO>
 </ROW>
 <ROW>
  <EMPNO>7521</EMPNO>
  <ENAME>WARD</ENAME>
  <JOB>SALESMAN</JOB>
  <MGR>7698</MGR>
  <HIREDATE>81-02-22</HIREDATE>
  <SAL>1250</SAL>
  <COMM>500</COMM>
  <DEPTNO>30</DEPTNO>
 </ROW>
 <ROW>
  <EMPNO>7566</EMPNO>
  <ENAME>JONES</ENAME>
  <JOB>MANAGER</JOB>
  <MGR>7839</MGR>
  <HIREDATE>81-04-02</HIREDATE>
  <SAL>2975</SAL>
  <DEPTNO>20</DEPTNO>
 </ROW>
 <ROW>
  <EMPNO>7654</EMPNO>
  <ENAME>MARTIN</ENAME>
  <JOB>SALESMAN</JOB>
  <MGR>7698</MGR>
  <HIREDATE>81-09-28</HIREDATE>
  <SAL>1250</SAL>
  <COMM>1400</COMM>
  <DEPTNO>30</DEPTNO>
 </ROW>
 <ROW>
  <EMPNO>7698</EMPNO>
  <ENAME>BLAKE</ENAME>
  <JOB>MANAGER</JOB>
  <MGR>7839</MGR>
  <HIREDATE>81-05-01</HIREDATE>
  <SAL>2850</SAL>
  <DEPTNO>30</DEPTNO>
 </ROW>
 <ROW>
  <EMPNO>7782</EMPNO>
  <ENAME>CLARK</ENAME>
  <JOB>MANAGER</JOB>
  <MGR>7839</MGR>
  <HIREDATE>81-06-09</HIREDATE>
  <SAL>2450</SAL>
  <DEPTNO>10</DEPTNO>
 </ROW>
 <ROW>
  <EMPNO>7839</EMPNO>
  <ENAME>KING</ENAME>
  <JOB>PRESIDENT</JOB>
  <HIREDATE>81-11-17</HIREDATE>
  <SAL>5000</SAL>
  <DEPTNO>10</DEPTNO>
 </ROW>
 <ROW>
  <EMPNO>7844</EMPNO>
  <ENAME>TURNER</ENAME>
  <JOB>SALESMAN</JOB>
  <MGR>7698</MGR>
  <HIREDATE>81-09-08</HIREDATE>
  <SAL>1500</SAL>
  <COMM>0</COMM>
  <DEPTNO>30</DEPTNO>
 </ROW>
 <ROW>
  <EMPNO>7900</EMPNO>
  <ENAME>JAMES</ENAME>
  <JOB>CLERK</JOB>
  <MGR>7698</MGR>
  <HIREDATE>81-12-03</HIREDATE>
  <SAL>950</SAL>
  <DEPTNO>30</DEPTNO>
 </ROW>
 <ROW>
  <EMPNO>7902</EMPNO>
  <ENAME>FORD</ENAME>
  <JOB>ANALYST</JOB>
  <MGR>7566</MGR>
  <HIREDATE>81-12-03</HIREDATE>
  <SAL>3000</SAL>
  <DEPTNO>20</DEPTNO>
 </ROW>
 <ROW>
  <EMPNO>7934</EMPNO>
  <ENAME>MILLER</ENAME>
  <JOB>CLERK</JOB>
  <MGR>7782</MGR>
  <HIREDATE>82-01-23</HIREDATE>
  <SAL>1300</SAL>
  <DEPTNO>10</DEPTNO>
 </ROW>
 <ROW>
  <EMPNO>9999</EMPNO>
  <ENAME>ほげ</ENAME>
  <JOB>ENGINEER</JOB>
  <MGR>7566</MGR>
  <HIREDATE>08-01-01</HIREDATE>
  <SAL>2500</SAL>
  <DEPTNO>20</DEPTNO>
 </ROW>
</ROWSET>



経過: 00:00:00.65
SCOTT>


これは書く必要は無かったかもしれないが念のため。
XMLQuery()関数や、XMLTable()関数ではXQueryのFLWOR式でorder by句を使えばソートできる。

SCOTT> select
  2    xmlroot(xmldoc.column_value, version '1.0')
  3  from
  4    xmltable(
  5      '<ROWSET>{
  6         for $d in ora:view("EMP")
  7         order by $d
  8         return $d
  9      }</ROWSET>'
 10    ) xmldoc
 11  /

XMLROOT(XMLDOC.COLUMN_VALUE,VERSION'1.0')
--------------------------------------------------------------------------------
<?xml version="1.0"?>
<ROWSET>
  <ROW>
    <EMPNO>7369</EMPNO>
    <ENAME>SMITH</ENAME>
    <JOB>CLERK</JOB>
    <MGR>7902</MGR>
    <HIREDATE>1980-12-17</HIREDATE>
    <SAL>800</SAL>
    <DEPTNO>20</DEPTNO>
  </ROW>
  <ROW>
    <EMPNO>7499</EMPNO>
    <ENAME>ALLEN</ENAME>
    <JOB>SALESMAN</JOB>
    <MGR>7698</MGR>
    <HIREDATE>1981-02-20</HIREDATE>
    <SAL>1600</SAL>
    <COMM>300</COMM>
    <DEPTNO>30</DEPTNO>
  </ROW>
  <ROW>
    <EMPNO>7521</EMPNO>
    <ENAME>WARD</ENAME>
    <JOB>SALESMAN</JOB>
    <MGR>7698</MGR>
    <HIREDATE>1981-02-22</HIREDATE>
    <SAL>1250</SAL>
    <COMM>500</COMM>
    <DEPTNO>30</DEPTNO>
  </ROW>
  <ROW>
    <EMPNO>7566</EMPNO>
    <ENAME>JONES</ENAME>
    <JOB>MANAGER</JOB>
    <MGR>7839</MGR>
    <HIREDATE>1981-04-02</HIREDATE>
    <SAL>2975</SAL>
    <DEPTNO>20</DEPTNO>
  </ROW>
  <ROW>
    <EMPNO>7654</EMPNO>
    <ENAME>MARTIN</ENAME>
    <JOB>SALESMAN</JOB>
    <MGR>7698</MGR>
    <HIREDATE>1981-09-28</HIREDATE>
    <SAL>1250</SAL>
    <COMM>1400</COMM>
    <DEPTNO>30</DEPTNO>
  </ROW>
  <ROW>
    <EMPNO>7698</EMPNO>
    <ENAME>BLAKE</ENAME>
    <JOB>MANAGER</JOB>
    <MGR>7839</MGR>
    <HIREDATE>1981-05-01</HIREDATE>
    <SAL>2850</SAL>
    <DEPTNO>30</DEPTNO>
  </ROW>
  <ROW>
    <EMPNO>7782</EMPNO>
    <ENAME>CLARK</ENAME>
    <JOB>MANAGER</JOB>
    <MGR>7839</MGR>
    <HIREDATE>1981-06-09</HIREDATE>
    <SAL>2450</SAL>
    <DEPTNO>10</DEPTNO>
  </ROW>
  <ROW>
    <EMPNO>7839</EMPNO>
    <ENAME>KING</ENAME>
    <JOB>PRESIDENT</JOB>
    <HIREDATE>1981-11-17</HIREDATE>
    <SAL>5000</SAL>
    <DEPTNO>10</DEPTNO>
  </ROW>
  <ROW>
    <EMPNO>7844</EMPNO>
    <ENAME>TURNER</ENAME>
    <JOB>SALESMAN</JOB>
    <MGR>7698</MGR>
    <HIREDATE>1981-09-08</HIREDATE>
    <SAL>1500</SAL>
    <COMM>0</COMM>
    <DEPTNO>30</DEPTNO>
  </ROW>
  <ROW>
    <EMPNO>7900</EMPNO>
    <ENAME>JAMES</ENAME>
    <JOB>CLERK</JOB>
    <MGR>7698</MGR>
    <HIREDATE>1981-12-03</HIREDATE>
    <SAL>950</SAL>
    <DEPTNO>30</DEPTNO>
  </ROW>
  <ROW>
    <EMPNO>7902</EMPNO>
    <ENAME>FORD</ENAME>
    <JOB>ANALYST</JOB>
    <MGR>7566</MGR>
    <HIREDATE>1981-12-03</HIREDATE>
    <SAL>3000</SAL>
    <DEPTNO>20</DEPTNO>
  </ROW>
  <ROW>
    <EMPNO>7934</EMPNO>
    <ENAME>MILLER</ENAME>
    <JOB>CLERK</JOB>
    <MGR>7782</MGR>
    <HIREDATE>1982-01-23</HIREDATE>
    <SAL>1300</SAL>
    <DEPTNO>10</DEPTNO>
  </ROW>
  <ROW>
    <EMPNO>9999</EMPNO>
    <ENAME>ほげ</ENAME>
    <JOB>ENGINEER</JOB>
    <MGR>7566</MGR>
    <HIREDATE>2008-01-01</HIREDATE>
    <SAL>2500</SAL>
    <DEPTNO>20</DEPTNO>
  </ROW>
</ROWSET>


経過: 00:00:00.30
SCOTT>

次回へつづく。

投稿日時 2008年2月19日 (火) 07時00分 Oracle, Mac, XML, Linux, Oracle Database 11g | |

トラックバック


この記事へのトラックバック一覧です： Oracle de XMLDB #22 - Relational dataをXMLへ #5:

コメント

コメントを書く