Oracle中通过存储过程，Function，触发器实现解析时间类型的字段

摘要：之前在项目中解决了插入字符串类型的数据，今天试着写了一个插入date类型的字段，成功了，现在记录一下，以便以后查看： 一：首先建立一个根据xml节点名称获取对应的xml的Function.sql: 二：其次建立一个式化字符串时间的Funcation.sql: CREATE OR REP

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

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

二：其次建立一个&＃26684;式化字符串时间的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 = &＃39; &＃39; THEN

   ValueReturn := &＃39; &＃39;;

   RETURN ValueReturn;

   ELSE

   DAY := SUBSTR(key,0,2);
   
   MOUNTH := SUBSTR(key,3,3);
   
   IF INSTR (MOUNTH,&＃39;JAN&＃39;) > 0 THEN
   MOUNTH := 01;
   END IF;
   
   IF INSTR (MOUNTH,&＃39;FEB&＃39;) > 0 THEN
   MOUNTH := 02;
   END IF;
   
   IF INSTR (MOUNTH,&＃39;MAR&＃39;) > 0 THEN
   MOUNTH := 03;
   END IF;
   
   IF INSTR (MOUNTH,&＃39;APR&＃39;) > 0 THEN
   MOUNTH := 04;
   END IF;
   
   IF INSTR (MOUNTH,&＃39;MAY&＃39;) > 0 THEN
   MOUNTH := 05;
   END IF;
   
   IF INSTR (MOUNTH,&＃39;JUN&＃39;) > 0 THEN
   MOUNTH := 06;
   END IF;
   
   IF INSTR (MOUNTH,&＃39;JUL&＃39;) > 0 THEN
   MOUNTH := 07;
   END IF;
   
   IF INSTR (MOUNTH,&＃39;AUG&＃39;) > 0 THEN
   MOUNTH := 08;
   END IF;
   
   IF INSTR (MOUNTH,&＃39;SEP&＃39;) > 0 THEN
   MOUNTH := 09;
   END IF;
   
   IF INSTR (MOUNTH,&＃39;OCT&＃39;) > 0 THEN
   MOUNTH := 10;
   END IF;
   
   IF INSTR (MOUNTH,&＃39;NOV&＃39;) > 0 THEN
   MOUNTH := 11;
   END IF;
   
   IF INSTR (MOUNTH,&＃39;DEC&＃39;) > 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 || &＃39;-&＃39; || MOUNTH || &＃39;-&＃39; || DAY || &＃39; &＃39; || HOUR || &＃39;:&＃39; || MINUTE;
   
   --ValueReturn := HOUR || &＃39;:&＃39; || 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, &＃39;TIME&＃39;);
   
   TIME_F := FORMATDATEVALUE (GetXmlNodeValue (xmlStr, &＃39;TIME&＃39;), &＃39;TIME_F&＃39;);
   
   INSERT INTO TEST (ID,TIME) VALUES (TEST_SEQ.NEXTVAL,to_date(TIME_F,&＃39;yyyy-mm-dd hh24:mi:ss&＃39;));
   
   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, &＃39;


      DC    20141010230216    FLOP    FGIS        CA-CA1895-D-11OCT141730-D    11OCT141730      
&＃39;, TO_DATE(&＃39;10/20/2014 11:20:42&＃39;, &＃39;MM/DD/YYYY HH24:MI:SS&＃39;), TO_DATE(&＃39;10/20/2014 17:23:40&＃39;, &＃39;MM/DD/YYYY HH24:MI:SS&＃39;), &＃39;DC2&＃39;, 
    TO_DATE(&＃39;10/20/2014 11:28:05&＃39;, &＃39;MM/DD/YYYY HH24:MI:SS&＃39;), &＃39;DC2GIS&＃39;);
    COMMIT;