SQL高级使用，提取字段、获取JSON、解析XML

SQL高级使用，提取字段、获取JSON、解析XML

提取表中所有字段，返回clob类型数据

-- 提前表中所有的字段
-- SEQ,CHT_ID,DATESEQ,SOURCE,STATUS,I_HIS_CUST_TYPE...
SELECT WM_CONCAT(COLUMN_NAME) COL_NM
  FROM (SELECT *
          FROM USER_TAB_COLUMNS
         WHERE TABLE_NAME = UPPER('表名')
         ORDER BY COLUMN_ID);

提取表中所有字段，并将字段拼接成JSON串

-- 将表中的所有字段拼接成JSON串
-- {"SEQ":"SEQ","CHT_ID":"CHT_ID..."}
SELECT '{' || WM_CONCAT('"' || COLUMN_NAME || '":"'|| COLUMN_NAME || '"') || '}' COL_NM
  FROM (SELECT *
          FROM USER_TAB_COLUMNS
         WHERE TABLE_NAME = UPPER('表名')
         ORDER BY COLUMN_ID);

将表中的所有字段拼接成指定类型的JSON串

-- 将表中的所有字段拼接成指定类型的JSON串
-- {"SEQ":"'||V_AMP.SEQ||'","CHT_ID":"'||V_AMP.CHT_ID||...'"}
SELECT '{' || WM_CONCAT('"' || COLUMN_NAME || '":"''||V_AMP.' || COLUMN_NAME || '||''"') || '}' COL_NM
  FROM (SELECT *
          FROM USER_TAB_COLUMNS
         WHERE TABLE_NAME = UPPER('表名')
         ORDER BY COLUMN_ID);

检查查看JSON串，看SQL是否出错

-- 检查查看JSON串
SELECT TO_CLOB('{"ampTraceId":"V_AMP.I_AMP_TRACE_ID", "traceId":"V_AMP.I_TRACE_ID",
              "orgTraceNo":"V_AMP.I_ORG_TRACE_NO", "orgCode":"V_AMP.I_ORG_CODE",
              "subOrgCode":"V_AMP.I_SUB_ORG_CODE", "refundReason":"V_AMP.I_REFUND_REASON",
              "refundAmount":"V_AMP.I_REFUND_AMOUNT", "refundType":"V_AMP.I_REFUND_TYPE",
              "outRefundNo":"V_AMP.I_OUT_REFUND_NO", "orgRefundSn":"V_AMP.I_ORG_REFUND_SN"}') json FROM dual   T ; 

SQL解析XML数据

<o> <!-- 一级父节点 -->
   <code>200</code> <!-- 二级子节点 -->
   <message>success</message> <!-- 二级子节点 -->
   <data> <!-- 二级子节点节点 -->
       <digest>200</digest> <!-- 三级子节点 -->
       <downloadUrl>2022</downloadUrl> <!-- 三级子节点 -->
       <digestType>7</digestType> <!-- 三级子节点 -->
       <billType>21</billType> <!-- 三级子节点 -->
   </data>
 </o>

SQL语句解析XML数据，获取XML中的指定值

SELECT A.*
  FROM DUAL,
       XMLTABLE('$A/o' PASSING XMLTYPE(
       'XML格式数据') AS A COLUMNS ---指定列名称
                 CODE VARCHAR2(20) PATH '/xml父节点/xml节点',
                 MSG VARCHAR2(500) PATH '/xml父节点/xml节点',
                 DIGEST VARCHAR2(40) PATH '/xml父节点/xml层级/xml节点',
                 DOWNLOADURL VARCHAR2(40) PATH '/xml父节点/xml层级/xml节点',
                 DIGESTTYPE VARCHAR2(40) PATH '/xml父节点/xml层级/xml节点',
                 BILLTYPE VARCHAR2(40) PATH '/xml父节点/xml层级/xml节点') A;

完整SQL数据示例

-- 提前表中所有的字段
-- SEQ,CHT_ID,DATESEQ,SOURCE,STATUS,I_HIS_CUST_TYPE...
SELECT WM_CONCAT(COLUMN_NAME) COL_NM
  FROM (SELECT *
          FROM USER_TAB_COLUMNS
         WHERE TABLE_NAME = UPPER('表名')
         ORDER BY COLUMN_ID);


-- 将表中的所有字段拼接成JSON串
-- {"SEQ":"SEQ","CHT_ID":"CHT_ID..."}
SELECT '{' || WM_CONCAT('"' || COLUMN_NAME || '":"'|| COLUMN_NAME || '"') || '}' COL_NM
  FROM (SELECT *
          FROM USER_TAB_COLUMNS
         WHERE TABLE_NAME = UPPER('表名')
         ORDER BY COLUMN_ID);


-- 将表中的所有字段拼接成指定类型的JSON串
-- {"SEQ":"'||V_AMP.SEQ||'","CHT_ID":"'||V_AMP.CHT_ID||...'"}
SELECT '{' || WM_CONCAT('"' || COLUMN_NAME || '":"''||V_AMP.' || COLUMN_NAME || '||''"') || '}' COL_NM
  FROM (SELECT *
          FROM USER_TAB_COLUMNS
         WHERE TABLE_NAME = UPPER('表名')
         ORDER BY COLUMN_ID);


-- 检查查看JSON串
SELECT TO_CLOB('{"ampTraceId":"V_AMP.I_AMP_TRACE_ID", "traceId":"V_AMP.I_TRACE_ID",
              "orgTraceNo":"V_AMP.I_ORG_TRACE_NO", "orgCode":"V_AMP.I_ORG_CODE",
              "subOrgCode":"V_AMP.I_SUB_ORG_CODE", "refundReason":"V_AMP.I_REFUND_REASON",
              "refundAmount":"V_AMP.I_REFUND_AMOUNT", "refundType":"V_AMP.I_REFUND_TYPE",
              "outRefundNo":"V_AMP.I_OUT_REFUND_NO", "orgRefundSn":"V_AMP.I_ORG_REFUND_SN"}') json FROM dual   T ; 


-- SQL语句解析XML数据
SELECT A.*
  FROM DUAL,
       XMLTABLE('$A/o' PASSING XMLTYPE(
       '<o>
          <code>200</code>
          <message>success</message>
          <data>
              <digest>200</digest>
              <downloadUrl>2022</downloadUrl>
              <digestType>7</digestType>
              <billType>21</billType>
          </data>
        </o>') AS A COLUMNS ---指定列名称
                 CODE VARCHAR2(20) PATH '/o/code',
                 MSG VARCHAR2(500) PATH '/o/message',
                 DIGEST VARCHAR2(40) PATH '/o/data/digest',
                 DOWNLOADURL VARCHAR2(40) PATH '/o/data/downloadUrl',
                 DIGESTTYPE VARCHAR2(40) PATH '/o/data/digestType',
                 BILLTYPE VARCHAR2(40) PATH '/o/data/billType') A;