Oracle中通过存储过程，Function，触发器实现解析时间类型的字段并插入的对应的数据表中

忘是亡心i 2024-02-17 15:17 8阅读 0赞

摘要：之前在项目中解决了插入字符串类型的数据，今天试着写了一个插入date类型的字段，成功了，现在记录一下，以便以后查看：

一：首先建立一个根据xml节点名称获取对应的xml值的Function.sql:

CREATE OR REPLACE FUNCTION MIP.GetXmlNodeValue (xmlStr CLOB, nodeName VARCHAR2)
       RETURN VARCHAR2
    IS
       --创建xml解析器实例xmlparser.Parser
       xmlPar        xmlparser.Parser := xmlparser.newParser;
       
       --定义DOM文档
       xDoc          xmldom.DOMDocument;
       
       --定义item子节点数目变量
       lenItme       INTEGER;
       
       --定义节点列表，存放item节点们
       itemNodes     xmldom.DOMNodeList;
       
       --定义节点，存放单个item节点
       itemNode      xmldom.DOMNode;
       
       ValueReturn   VARCHAR2 (100);
       
       
       
    BEGIN
        
       --解析xmlStr中xml字符串，并存放到xmlPar中
       xmlparser.parseClob (xmlPar, xmlStr);
    
       --将xmlPar中的数据转存到dom文档中
       xDoc := xmlparser.getDocument (xmlPar);
       
       --释放解析器实例
       xmlparser.freeParser (xmlPar);
    
       --获取所有item节点
       itemNodes := xmldom.getElementsByTagName (xDoc, nodeName);
       
       --获取item节点的个数
       lenItme := xmldom.getLength (itemNodes);
        
      --如果无该标签，则返回EMPTY
       IF lenItme = 0 THEN
       
       ValueReturn := ' ';
      
       ELSE
       
       --获取节点列表中的第1个item节点
       itemNode := xmldom.item (itemNodes, 0);
    
       --获取所有子节点的值
       ValueReturn := xmldom.getNodeValue (xmldom.getFirstChild (itemNode));
       
       END IF;   
       --释放dom
       xmldom.freeDocument(xDoc);
       RETURN ValueReturn;
       
    END GetXmlNodeValue;
    /

二：其次建立一个格式化字符串时间的Funcation.sql:

CREATE OR REPLACE FUNCTION MIP.FormatDateValue (key VARCHAR2, value VARCHAR2)
       RETURN VARCHAR2
    IS
       --定义几个变量，出来解析过来的时间字符串
       --日月年时分(11OCT141024)
       AA       VARCHAR2(32);
    
       DAY      VARCHAR2(100);
    
       MOUNTH   VARCHAR2(100);
    
       YEAR     VARCHAR2(100);
    
       HOUR     VARCHAR2(100);
    
       MINUTE   VARCHAR2(100);
    
       ValueReturn   VARCHAR2 (100);
    
    BEGIN
    
       IF key = ' ' THEN
    
       ValueReturn := ' ';
    
       RETURN ValueReturn;
    
       ELSE
    
       DAY := SUBSTR(key,0,2);
       
       MOUNTH := SUBSTR(key,3,3);
       
       IF INSTR (MOUNTH,'JAN') > 0 THEN
       MOUNTH := 01;
       END IF;
       
       IF INSTR (MOUNTH,'FEB') > 0 THEN
       MOUNTH := 02;
       END IF;
       
       IF INSTR (MOUNTH,'MAR') > 0 THEN
       MOUNTH := 03;
       END IF;
       
       IF INSTR (MOUNTH,'APR') > 0 THEN
       MOUNTH := 04;
       END IF;
       
       IF INSTR (MOUNTH,'MAY') > 0 THEN
       MOUNTH := 05;
       END IF;
       
       IF INSTR (MOUNTH,'JUN') > 0 THEN
       MOUNTH := 06;
       END IF;
       
       IF INSTR (MOUNTH,'JUL') > 0 THEN
       MOUNTH := 07;
       END IF;
       
       IF INSTR (MOUNTH,'AUG') > 0 THEN
       MOUNTH := 08;
       END IF;
       
       IF INSTR (MOUNTH,'SEP') > 0 THEN
       MOUNTH := 09;
       END IF;
       
       IF INSTR (MOUNTH,'OCT') > 0 THEN
       MOUNTH := 10;
       END IF;
       
       IF INSTR (MOUNTH,'NOV') > 0 THEN
       MOUNTH := 11;
       END IF;
       
       IF INSTR (MOUNTH,'DEC') > 0 THEN
       MOUNTH := 12;
       END IF;
       
       YEAR := SUBSTR(key,6,2);
       
       HOUR := SUBSTR(key,8,2);
       
       MINUTE := SUBSTR(key,-2);
       
       AA := 20;
    
       --日月年时分(11OCT141017)
       ValueReturn := AA || YEAR || '-' || MOUNTH || '-' || DAY || ' ' || HOUR || ':' || MINUTE;
       
       --ValueReturn := HOUR || ':' || MINUTE;
    
       RETURN ValueReturn;
    
       END IF;
    
    END FormatDateValue;
    /

三：建立插入数据表的存储过程.sql:

CREATE OR REPLACE PROCEDURE MIP.PRO_TEST (xmlStr IN CLOB)
    IS
       TIME    VARCHAR2(100);
       
       TIME_F  VARCHAR2(100);
    
    BEGIN
    
       --TIME := GetXmlNodeValue (xmlStr, 'TIME');
       
       TIME_F := FORMATDATEVALUE (GetXmlNodeValue (xmlStr, 'TIME'), 'TIME_F');
       
       INSERT INTO TEST (ID,TIME) VALUES (TEST_SEQ.NEXTVAL,to_date(TIME_F,'yyyy-mm-dd hh24:mi:ss'));
       
       COMMIT;
    
    EXCEPTION
    
       WHEN OTHERS
    
       THEN
    
          DBMS_OUTPUT.PUT_LINE (SQLERRM);
    
    END PRO_TEST;
    /

四：建立行级触发器.sql:

DROP TRIGGER MIP.COPY_TEST_TRIGGER;
    
    CREATE OR REPLACE TRIGGER MIP.COPY_TEST_TRIGGER
       AFTER INSERT
       ON MIP.MBINMSGS    FOR EACH ROW
    DECLARE
    -- LOCAL VARIABLES HERE
    BEGIN
       PRO_TEST (:NEW.MBINMSGS_CLOB_MSG);
    END COPY_TEST_TRIGGER;
    /

五：给其中的原始数据表插入一条数据，查看是否解析成功并插入到对应的表中：

Insert into MBINMSGS
       (ID, MBINMSGS_CLOB_MSG, MBINMSGS_DATE_RECEIVED, MBINMSGS_DATE_PROCESSED, MBINMSGS_SUBSYSTEM_NAME, 
        MBINMSGS_SUBSYSTEM_DATE_SENT, SERVICENAME)
     Values
       (1931300, '<?xml version="1.0" encoding="UTF-8"?>
    <ASUPDATA>
    
    <MSG>  <META>    <SNDR>DC</SNDR>    <DTTM>20141010230216</DTTM>    <TYPE>FLOP</TYPE>    <STYP>FGIS</STYP>  </META>  <FLOP>    <FFID>CA-CA1895-D-11OCT141730-D</FFID>    <TIME>11OCT141730</TIME>      </FLOP></MSG>
    </ASUPDATA>', TO_DATE('10/20/2014 11:20:42', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('10/20/2014 17:23:40', 'MM/DD/YYYY HH24:MI:SS'), 'DC2', 
        TO_DATE('10/20/2014 11:28:05', 'MM/DD/YYYY HH24:MI:SS'), 'DC2GIS');
        COMMIT;

六：查看对应的数据表中时间类型的字段是否有值：

![Center][]

[Center]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/8482ca6058464865a48fd35ffdd9cd36.png