同一张表,原来有一字段accessdate存放日期的,但类型是varchar2,现在新建一字段accessdate1,类型是date。现在的想法是通过存储过程将accessdate转换成日期插入到accessdate1中去,过程如下:
declare
cursor c1 is select count(id) from taccessinfo;
cursor c2 is select to_date(accessdate,'yyyy-mm-dd') from taccessinfo;
v_num number;
begin
for r1 in c1 loop
for r2 in c2 loop
exit when c1%notfound or c2%notfound;
insert into taccessinfo(accessdate1) values(r2);
v_num:=v_num+1;
if v_num=1000 then
commit;
v_num:=0;
end if;
end loop;
end loop;
commit;
end;这个无法实现此功能,求解?
declare
cursor c1 is select count(id) from taccessinfo;
cursor c2 is select to_date(accessdate,'yyyy-mm-dd') from taccessinfo;
v_num number;
begin
for r1 in c1 loop
for r2 in c2 loop
exit when c1%notfound or c2%notfound;
insert into taccessinfo(accessdate1) values(r2);
v_num:=v_num+1;
if v_num=1000 then
commit;
v_num:=0;
end if;
end loop;
end loop;
commit;
end;这个无法实现此功能,求解?
或者
update yourTableName set accessdate1=to_date(accessdate,'yyyy-mm-dd hh24:mi:ss');
update taccessinfo set accessdate1=to_date(accessdate,'yyyy-mm-dd');