增强PL/SQL中查询CLOB或者LONG类型的可读性

来源：互联网发布：白金数据小说叫什么编辑：程序博客网时间：2024/05/17 02:00

一般CLOG或者LONG类型的字段，查询出来是如下所示，影响可读性：

而且我们无法在子查询中查询LONG/CLOB字段：

我们可以从下面的连接中找到方法：

http://www.oracle-developer.net/display.php?id=430

There are several workarounds we can use to solve our sample problem. We will examine each of the following in turn:

TO_LOB;
PL/SQL;
DBMS_XMLGEN;
Dictionary Long Application (oracle-developer.net utility).

个人偏向于第三种，先转换为XML格式的数据，再使用Oracle中处理XML相关的函数进行查询，示例如下：

WITH xml AS (SELECT dbms_xmlgen.getxmltype('SELECT *                                 FROM user_tab_subpartitions a                                  WHERE  a.table_name = ''CSS_CUST_ACCT_ITEM''') AS xml  FROM   dual)SELECT *FROM   (SELECT EXTRACTVALUE(xs.object_value, '/ROW/TABLE_NAME')      AS table_name,               EXTRACTVALUE(xs.object_value, '/ROW/TABLESPACE_NAME') AS tablespace_name,               EXTRACTVALUE(xs.object_value, '/ROW/HIGH_VALUE')      AS high_value        FROM   xml x, TABLE(xmlsequence(extract(x.xml, '/ROWSET/ROW'))) xs) vWHERE  v.high_value = 512;

这样我们不仅增强了CLOB/LONG类型字段的可读性，同时还能对数据进行子查询。

XMLSEQUENCE相关的使用说明如下：

http://www.stanford.edu/dept/itss/docs/oracle/10gR2/server.102/b14200/functions226.htm

XMLSequence divides up an XML document with multiple elements into VARRAY single-element documents

就是说XMLSEQUENCE将XML文档中的各个节点的多个元素分为只存放单个元素的数组

the TABLE keyword instructs Oracle Database to consider the collection a table value that can be used in the FROM clause of the subquery

最终是以表的方式展现出来，并实现能够在from子句中使用子查询。

0 0