摘要：之前在项目中解决了插入字符串类型的数据，今天试着写了一个插入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 varchar2is --定义几个变量，出来解析过来的时间字符串 --日月年时分(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 rowdeclare-- local variables herebegin 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, 'dc20141010230216flopfgisca-ca1895-d-11oct141730-d11oct141730', 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;
六：查看对应的数据表中时间类型的字段是否有值：