摘要:之前在项目中解决了插入字符串类型的数据,今天试着写了一个插入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 VARCHAR232); DAY VARCHAR2100); MOUNTH VARCHAR2100); YEAR VARCHAR2100); HOUR VARCHAR2100); MINUTE VARCHAR2100); ValueReturn VARCHAR2 100); BEGIN IF key = ' ' THEN ValueReturn := ' '; RETURN ValueReturn; ELSE DAY := SUBSTRkey,0,2); MOUNTH := SUBSTRkey,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 := SUBSTRkey,6,2); HOUR := SUBSTRkey,8,2); MINUTE := SUBSTRkey,-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 VARCHAR2100); TIME_F VARCHAR2100); BEGIN --TIME := GetXmlNodeValue xmlStr, 'TIME'); TIME_F := FORMATDATEVALUE GetXmlNodeValue xmlStr, 'TIME'), 'TIME_F'); INSERT INTO TEST ID,TIME) VALUES TEST_SEQ.NEXTVAL,to_dateTIME_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;
六:查看对应的数据表中时间类型的字段是否有值: