htmlをsqlでつくる

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

#参考文献

>めっちゃ便利。。。

[ダイナミックSQLを生成し実行結果を得る単一SQL ステートメントの作り方(Oracle)](https://qiita.com/tlokweng/items/ead23e3ebd194f0fe851)

#クエリ

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


```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

>おぉぉぉ

```sql
[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](https://qiita-image-store.s3.amazonaws.com/0/286037/c8dd661c-da5c-48cb-9845-d6992f2fc930.png)

```html
"<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とかもいれてそれぽくした


```html: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](https://docs.oracle.com/en/database/oracle/oracle-database/18/adxdb/generation-of-XML-data-from-relational-data.html#GUID-3BEB9A88-8B44-4AA3-89F4-8CBF6F8FA686)

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

#追記

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

>function fmt_xml2

```sql
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;

```

>おまけ

```sql
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;
/
```