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

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

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

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

二：其次建立一个格式化字符串时间的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, '


  DC 20141010230216 FLOP FGIS   CA-CA1895-D-11OCT141730-D 11OCT141730 
', 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;