Oracle -如何将此提取的XML值转换为日期？

zlhcx6iw 于 2022-11-28 发布在 Oracle

关注(0)|答案(3)|浏览(178)

我发现这个sql有助于避免high_value长类型列。

--VER HIGH VALUE
col partition_name for a30
col high_value for a120
col PARTITION for a20
WITH xml AS
(
 SELECT dbms_xmlgen.getxmltype('SELECT table_name,partition_name,partition_position,high_value 
                                  FROM dba_tab_partitions 
                                 WHERE TABLE_OWNER = UPPER(''MY_SCHEMA'') ----MY SCHEMA HERE') AS x
    FROM dual
)
SELECT extractValue(rws.object_value, '/ROW/TABLE_NAME') table_name,
       extractValue(rws.object_value, '/ROW/PARTITION_NAME') partition,
       extractValue(rws.object_value, '/ROW/HIGH_VALUE') high_value
  FROM xml x, table(xmlsequence(extract(x.x, '/ROWSET/ROW'))) rws;

示例输出：

SAMPLE_TABLE                     P82                  TO_DATE(' 2021-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SAMPLE_TABLE                     P83                  TO_DATE(' 2021-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SAMPLE_TABLE                     P84                  TO_DATE(' 2021-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SAMPLE_TABLE                     P85                  TO_DATE(' 2021-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SAMPLE_TABLE                     P86                  TO_DATE(' 2022-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SAMPLE_TABLE                     P87                  TO_DATE(' 2022-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SAMPLE_TABLE                     P88                  TO_DATE(' 2022-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SAMPLE_TABLE                     P89                  TO_DATE(' 2022-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SAMPLE_TABLE                     P90                  TO_DATE(' 2022-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SAMPLE_TABLE                     P91                  TO_DATE(' 2022-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SAMPLE_TABLE                     P92                  TO_DATE(' 2022-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SAMPLE_TABLE                     P93                  TO_DATE(' 2022-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SAMPLE_TABLE                     P94                  TO_DATE(' 2022-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SAMPLE_TABLE                     P95                  TO_DATE(' 2022-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SAMPLE_TABLE                     P96                  TO_DATE(' 2022-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SAMPLE_TABLE                     P97                  TO_DATE(' 2022-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SAMPLE_TABLE                     P98                  MAXVALUE

如何将high_value列直接转换为日期
IE：而不是获取：

`TO_DATE(' 2022-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')`

我想得到：

2022-12-01

或者，如果更改日期掩码，则会得到：

alter session set nls_date_format = 'dd/mm/yyyy hh24:mi:ss';

01/12/2022 00:00:00

oracle

来源：https://stackoverflow.com/questions/74554329/oracle-how-to-convert-this-extracted-xml-value-to-a-date

3条答案

按热度按时间

knsnq2tg1#

在示例输出中，您似乎需要从位置11提取HIGH_VALUE列的子字符串，取10个字符并将其转换为日期。还应处理包含tekst 'MAXVALUE'的最后一行。下面是转换日期的SQL，它使用前一行的日期代替MAXVALUE tekst结果...

Select 
    TABLE_NAME, 
    PARTITION_NAME, 
    PARTITION_POSITION,
    CASE WHEN HIGH_VALUE != 'MAXVALUE' THEN To_Date(SubStr(HIGH_VALUE, 11, 10), 'yyyy-mm-dd') 
    ELSE LAST_VALUE(To_Date(SubStr(HIGH_VALUE, 11, 10), 'yyyy-mm-dd')) OVER(ORDER BY PARTITION_NAME ROWS BETWEEN 1 PRECEDING And 1 PRECEDING)
  END "HIGH_VALUE"
From 
    dba_tab_partitions
WHERE 
    TABLE_OWNER = UPPER(''''MY_SCHEMA'''')'
/*  
    R e s u l t :
TABLE_NAME   PARTITION_NAME PARTITION_POSITION HIGH_VALUE
------------ -------------- ------------------ ----------
SAMPLE_TABLE P82            Null               01-SEP-21  
SAMPLE_TABLE P83            Null               01-OCT-21  
SAMPLE_TABLE P84            Null               01-NOV-21  
SAMPLE_TABLE P85            Null               01-DEC-21  
SAMPLE_TABLE P86            Null               01-JAN-22  
SAMPLE_TABLE P87            Null               01-FEB-22  
SAMPLE_TABLE P88            Null               01-MAR-22  
SAMPLE_TABLE P89            Null               01-APR-22  
SAMPLE_TABLE P90            Null               01-MAY-22  
SAMPLE_TABLE P91            Null               01-JUN-22  
SAMPLE_TABLE P92            Null               01-JUL-22  
SAMPLE_TABLE P93            Null               01-AUG-22  
SAMPLE_TABLE P94            Null               01-SEP-22  
SAMPLE_TABLE P95            Null               01-OCT-22  
SAMPLE_TABLE P96            Null               01-NOV-22  
SAMPLE_TABLE P97            Null               01-DEC-22  
SAMPLE_TABLE P98            Null               01-DEC-22
*/

此致

赞(0）回复(0）举报 2022-11-28

oyxsuwqo2#

您可以将HIGH_VALUE表达式作为动态SQL执行，并在不进行任何解析的情况下获取日期。
第一次
| 表格名称|分区名称|高值|
| - -|- -|- -|
| T形|项目管理信息|2022年01月01日00：00：00|
| T形|系统_P4420| 2022年1月10日上午10时00分|
| T形|系统_P4421| 2022年1月13日上午10时00分|
| T形|系统_P4422| 2022年01月22日00：00：00|
| T形|系统_P4423| 2022年1月28日上午10时00分|
| T形|系统_P4424| 2022年01月07日00：00：00|
| T形|系统_P4425| 2022年1月31日上午10时00分|
| T形|系统_P4426| 2022年1月19日上午10时00分|
fiddle

赞(0）回复(0）举报 2022-11-28

cdmah0mi3#

您可以在PL/SQL代码块中使用动态SQL语句，以便能够在从LONG转换为VARCHAR 2后返回所需的日期值，例如

DECLARE
  v_dt  DATE;
  v_chr VARCHAR2(4000);
BEGIN
  DBMS_OUTPUT.PUT_LINE('table_name       partition_name       high_value');
  FOR c IN ( 
            SELECT table_name,partition_name,partition_position,high_value 
              FROM user_tab_partitions 
           )
  LOOP             
    v_chr := SUBSTR(c.high_value, 1, 4000);
    EXECUTE IMMEDIATE 'SELECT '||v_chr||' FROM dual' INTO v_dt;
    DBMS_OUTPUT.PUT(c.table_name||'    ');
    DBMS_OUTPUT.PUT(c.partition_name||'    ');
    DBMS_OUTPUT.PUT_LINE(v_dt);
  END LOOP;
END;
/

在这种情况下，我可以直接使用当前的日期转换表达式，而不需要任何额外的转换或提取的一些子串操作。
顺便说一句，假设你需要为当前模式处理它，最好使用user_tab_partitions字典视图。

赞(0）回复(0）举报 2022-11-28

我来回答

Oracle -如何将此提取的XML值转换为日期？

3条答案

相关问题

热门标签

最新问答