谢谢大家,搞定了
my writed procedures is:
create procedure updatesm
as
cursor t_sor is
select * from nation.smsend;
begin
for v_sor in t_sor loop
if to_date(trunc(v_sor.creatime))+90<to_date(trunc(sysdate)) then
update nation.smsend set valid='0' where nid=v_sor.nid;
end if;
end loop;
--dbms_output.put_line(to_date(trunc(sysdate))+1);
commit;
end;
/
create procedure deletesm
as
cursor t_sor is
select * from nation.smsend;
begin
for v_sor in t_sor loop
if to_date(trunc(v_sor.creatime))+180<to_date(trunc(sysdate)) then
delete from nation.smsend where nid=v_sor.nid;
end if;
end loop;
commit;
end;
/
结贴去了
Create Cursor Temp_List is
select id,time From a;
Begin
Do Loop
For eatch Temp_List Into
Uid,
Utime;
Exit When Temp_List%NotFound;
if trunc(Utime)=trunc(sysdate - 30) then
Delete a Where a.id = Uid;
Commit;
end if;
End Loop;
End;
/
create procedure pro
as
cursor t_sor is
select * from table_name;
begin
for v_sor in t_sor loop
if trunc(v_sor.dtime)=trunc(sysdate) then
update table_name set nflag=0 where id=v_sor.id;
end if;
end loop;
commit;
end;
/
declare
jobno number;
begin
dbms_job.submit(jobno,'pro;',sysdate,'trunc(sysdate)+1');
commit;
end;
/
我看到上面相关例子,但不理解v_sor是什么意思啊??有没人知道?