LoginSignup
4
0

More than 5 years have passed since last update.

@ukijumotahaneniarukenia

htmlをsqlでつくる

Last updated at Posted at 2019-03-17

まえがき

xml関数を少し学んだので、tableでもつくろうかとおもいました。なお、htmlは雰囲気。

参考文献

めっちゃ便利。。。

ダイナミックSQLを生成し実行結果を得る単一SQL ステートメントの作り方(Oracle)

クエリ

再帰でないwith句の部分でhtmlを作って、実行部で呼び出しています。lateralチックにcallします。なるべく、実行部でのインターフェースは崩したくないので、withで切り出し、誰でもメンテできるように工夫しました。ちなみにbuild_sqlの部分に;(セミコロン)入れるとエラーはいてくれるので、入れなくて大丈夫です。

WITH src AS(
        SELECT
            'with sub as('
            || ' select '
            || ''''
            || '<div class="s-tbl">'
            || '<table>'
            || '<tbody>'
            || '<tr>'
            || '<th>'
            || 'table_name'
            || '</th>'
            || '<th>'
            || 'column_name'
            || '</th>'
            || '</tr>'
            || obj_info
            || '</tbody>'
            || '</table>'
            || '</div>'
            || ''''
            || ' as doc from dual'
            || ') select fmt_xml2(doc) as xmldoc from sub' AS build_sql
          FROM
            (
            SELECT
                LISTAGG(obj_info, '') WITHIN GROUP(
                    ORDER BY
                        table_name
                        , column_id
                ) AS obj_info
            FROM
                (
                    SELECT
                        s1.table_name
                        , s2.column_name
                        , s2.column_id
                        , '<tr>'
                          ||'<td>'
                          || s1.table_name
                          || '</td>'
                          || '<td>'
                          || s2.column_name
                          || '</td>'
                          || '</tr>'
                          AS obj_info
                    FROM
                        user_tables s1
                        , user_tab_columns s2
                    WHERE
                        s1.table_name = s2.table_name
                )
            )
)
SELECT
    xmldoc
FROM
    (SELECT build_sql FROM src)
    ,XMLTABLE(
            '/ROWSET/ROW'
            PASSING dbms_xmlgen.getxmltype (
            build_sql
            )
        COLUMNS xmldoc CLOB
        );

実行してみた

sqlplus

おぉぉぉ

[oracle@f285aba0589a ~]$ sqlplus aine/ORACLE_PWD@pdb1

SQL*Plus: Release 18.0.0.0.0 - Production on Sun Mar 17 11:20:09 2019
Version 18.3.0.0.0

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

Last Successful login time: Sun Mar 17 2019 11:19:43 +09:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

AINE@pdb1> col xmldoc for a170
AINE@pdb1> SELECT xmldoc FROM ( SELECT 'with sub as(' || ' select ' || '''' || '<div class="s-tbl">' || '<table>' || '<tbody>' || '<tr>' || '<th>' || 'table_name' || '</th>' || '<th>' || 'column_name' || '</th>' || '</tr>' || obj_info || '</tbody>' || '</table>' || '</div>' || '''' || ' as doc from dual' || ') select fmt_xml2(doc) as xmldoc from sub' AS build_sql FROM ( SELECT LISTAGG(obj_info, '') WITHIN GROUP( ORDER BY table_name , column_id ) AS obj_info FROM ( SELECT s1.table_name , s2.column_name , s2.column_id , '<tr><td>' || s1.table_name || '</td>' || '<td>' || s2.column_name || '</td></tr>' AS obj_info FROM user_tables s1 , user_tab_columns s2 WHERE s1.table_name = s2.table_name ) ) ) ,XMLTABLE( '/ROWSET/ROW' PASSING dbms_xmlgen.getxmltype ( build_sql ) COLUMNS xmldoc CLOB );

XMLDOC
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
<div class="s-tbl">
  <table>
    <tbody>
      <tr>
        <th>table_name</th>
        <th>column_name</th>
      </tr>
      <tr>
        <td>PLCH_DEPT</td>
        <td>DEPTID</td>
      </tr>
      <tr>
        <td>PLCH_DEPT</td>
        <td>DEPTNAME</td>
      </tr>
      <tr>
        <td>PLCH_EMP</td>
        <td>EMPID</td>
      </tr>
      <tr>
        <td>PLCH_EMP</td>
        <td>EMPNAME</td>
      </tr>
      <tr>
        <td>PLCH_EMP</td>
        <td>DEPTID</td>
      </tr>
      <tr>
        <td>TEST___AVL___</td>
        <td>STF</td>
      </tr>
      <tr>
        <td>TEST___MST___</td>
        <td>STF</td>
      </tr>
      <tr>
        <td>TEST___RCP___</td>
        <td>RCP_ID</td>
      </tr>
      <tr>
        <td>TEST___RCP___</td>
        <td>NM</td>
      </tr>
      <tr>
        <td>TEST___STF___</td>
        <td>RCP_ID</td>
      </tr>
      <tr>
        <td>TEST___STF___</td>
        <td>STF</td>
      </tr>
      <tr>
        <td>TEST___TMP___</td>
        <td>RCP_ID</td>
      </tr>
      <tr>
        <td>TEST___TMP___</td>
        <td>STF_XML</td>
      </tr>
      <tr>
        <td>TEST___XML___</td>
        <td>RCP_ID</td>
      </tr>
      <tr>
        <td>TEST___XML___</td>
        <td>STF_XML</td>
      </tr>
    </tbody>
  </table>
</div>


1 row selected.

Elapsed: 00:00:00.02

sqldeveloper

ダブクォ入らないで。入らない方法あるかな。コピッてエディタ貼り付けると入ってしまう。

image.png

"<div class="s-tbl">
  <table>
    <tbody>
      <tr>
        <th>table_name</th>
        <th>column_name</th>
      </tr>
      <tr>
        <td>PLCH_DEPT</td>
        <td>DEPTID</td>
      </tr>
      <tr>
        <td>PLCH_DEPT</td>
        <td>DEPTNAME</td>
      </tr>
      <tr>
        <td>PLCH_EMP</td>
        <td>EMPID</td>
      </tr>
      <tr>
        <td>PLCH_EMP</td>
        <td>EMPNAME</td>
      </tr>
      <tr>
        <td>PLCH_EMP</td>
        <td>DEPTID</td>
      </tr>
      <tr>
        <td>TEST___AVL___</td>
        <td>STF</td>
      </tr>
      <tr>
        <td>TEST___MST___</td>
        <td>STF</td>
      </tr>
      <tr>
        <td>TEST___RCP___</td>
        <td>RCP_ID</td>
      </tr>
      <tr>
        <td>TEST___RCP___</td>
        <td>NM</td>
      </tr>
      <tr>
        <td>TEST___STF___</td>
        <td>RCP_ID</td>
      </tr>
      <tr>
        <td>TEST___STF___</td>
        <td>STF</td>
      </tr>
      <tr>
        <td>TEST___TMP___</td>
        <td>RCP_ID</td>
      </tr>
      <tr>
        <td>TEST___TMP___</td>
        <td>STF_XML</td>
      </tr>
      <tr>
        <td>TEST___XML___</td>
        <td>RCP_ID</td>
      </tr>
      <tr>
        <td>TEST___XML___</td>
        <td>STF_XML</td>
      </tr>
    </tbody>
  </table>
</div>
"

cssとかもいれてそれぽくした

test.html
<style type="text/css">
.s-tbl tbody tr:nth-child(1) {
    background: #81BEF7;
}
.s-tbl tr:nth-child(even) {
    background: #E0ECF8;
}
.s-tbl tr:nth-child(odd) {
    background: #A9D0F5;
}
.s-tbl tr:hover {
    background: #F5A9F2;
    cursor:pointer;
}
.s-tbl {/*table全体のラッパー。スクロールを表示する*/
  max-width: 800px;
  max-height:300px;
  width:100%;
  overflow: scroll;
}
table {
  position: relative;
  border: 1px solid #000000;
  border-collapse: collapse;
}

td, th {
  white-space: nowrap;
  border: 1px solid #000000;
  padding: 20px;
  text-align: center;
}

/*行ヘッダの固定*/
th {
  background-color: #81BEF7;
  position: sticky;
  top: -1px;
  z-index: 2;
}
</style>
<div class="s-tbl">
  <table>
    <tbody>
      <tr>
        <th>table_name</th>
        <th>column_name</th>
      </tr>
      <tr>
        <td>PLCH_DEPT</td>
        <td>DEPTID</td>
      </tr>
      <tr>
        <td>PLCH_DEPT</td>
        <td>DEPTNAME</td>
      </tr>
      <tr>
        <td>PLCH_EMP</td>
        <td>EMPID</td>
      </tr>
      <tr>
        <td>PLCH_EMP</td>
        <td>EMPNAME</td>
      </tr>
      <tr>
        <td>PLCH_EMP</td>
        <td>DEPTID</td>
      </tr>
      <tr>
        <td>TEST___AVL___</td>
        <td>STF</td>
      </tr>
      <tr>
        <td>TEST___MST___</td>
        <td>STF</td>
      </tr>
      <tr>
        <td>TEST___RCP___</td>
        <td>RCP_ID</td>
      </tr>
      <tr>
        <td>TEST___RCP___</td>
        <td>NM</td>
      </tr>
      <tr>
        <td>TEST___STF___</td>
        <td>RCP_ID</td>
      </tr>
      <tr>
        <td>TEST___STF___</td>
        <td>STF</td>
      </tr>
      <tr>
        <td>TEST___TMP___</td>
        <td>RCP_ID</td>
      </tr>
      <tr>
        <td>TEST___TMP___</td>
        <td>STF_XML</td>
      </tr>
      <tr>
        <td>TEST___XML___</td>
        <td>RCP_ID</td>
      </tr>
      <tr>
        <td>TEST___XML___</td>
        <td>STF_XML</td>
      </tr>
    </tbody>
  </table>
</div>

あとがき

xml関数というあまりなじみがない関数ですが、いい感じに使えることもあるようですね。データさえあれば、モック画面とかさくっと作れて、いいかもですね。画面レイアウトこんな感じみたいにイメージできるから。CSSのスタイル名は組み込んどいて後でマージするほうが効率よさそうかも。楽しみが広がりますね。

なお、xml関数のreferは以下を参考にしました。

Generation of XML Data from Relational Data

以上ありがとうございました。

追記

大事なやつ忘れてました。

function fmt_xml2

CREATE OR REPLACE FUNCTION fmt_xml2 (p_str VARCHAR2)
RETURN CLOB
IS
    fmtxml CLOB;
BEGIN
    fmtxml := to_clob(' ');
    dbms_xmldom.writetoclob(dbms_xmldom.makenode(dbms_xmldom.newdomdocument(XMLTYPE.createxml(p_str))),fmtxml);
RETURN fmtxml;
END;
/

--テスト
select FMT_XML2('<div class="s-tbl"><table><tbody><tr><th>table_name</th></tr><tr><th>column_name</th></tr><tr><td>PLCH_DEPT</td><td>DEPTID</td></tr><tr><td>PLCH_DEPT</td><td>DEPTNAME</td></tr><tr><td>PLCH_EMP</td><td>EMPID</td></tr><tr><td>PLCH_EMP</td><td>EMPNAME</td></tr><tr><td>PLCH_EMP</td><td>DEPTID</td></tr><tr><td>TEST___AVL___</td><td>STF</td></tr><tr><td>TEST___MST___</td><td>STF</td></tr><tr><td>TEST___RCP___</td><td>RCP_ID</td></tr><tr><td>TEST___RCP___</td><td>NM</td></tr><tr><td>TEST___STF___</td><td>RCP_ID</td></tr><tr><td>TEST___STF___</td><td>STF</td></tr><tr><td>TEST___TMP___</td><td>RCP_ID</td></tr><tr><td>TEST___TMP___</td><td>STF_XML</td></tr><tr><td>TEST___XML___</td><td>RCP_ID</td></tr><tr><td>TEST___XML___</td><td>STF_XML</td></tr></tbody></table></div>') as xmldoc from dual;

おまけ

CREATE OR REPLACE FUNCTION fmt_xml(p_xml XMLTYPE)
RETURN CLOB
AS fmtxml CLOB;
BEGIN
    fmtxml := to_clob(' ');
    dbms_xmldom.writetoclob(dbms_xmldom.makenode(dbms_xmldom.newdomdocument(p_xml)),fmtxml);
    RETURN fmtxml;
END;
/
4
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
4
0