create or replace procedure autoUpdate
as
sid number;
declare cursor myCusor is select id from t_inspector where states=10 and sysdate-to_date(requesttime,'yyyy-mm-dd')>0;
begin
OPEN myCusor;
LOOP
FETCH myCusor INTO sid;
EXIT WHEN myCusor%NOTFOUND;
update t_inspector set period=period+1 where id=sid
END LOOP;
CLOSE myCusor;
END;
----创建job,每个星期的星期天凌晨0点执行检查
Begin
sys.dbms_job.submit(job => 301,
what => 'autoUpdate;',
next_date => to_date(sysdate, 'YYYY-MM-DD'),
interval => 'NEXT_DAY(TRUNC(SYSDATE), ''SUNDAY'') + 0/24');
Commit;
End; begin
sys.dbms_job.remove(:301);
end;
以上PROC在创建后在PLSQL Developer有个小红叉,有问题啊,而且创建job也不对
as
sid number;
declare cursor myCusor is select id from t_inspector where states=10 and sysdate-to_date(requesttime,'yyyy-mm-dd')>0;
begin
OPEN myCusor;
LOOP
FETCH myCusor INTO sid;
EXIT WHEN myCusor%NOTFOUND;
update t_inspector set period=period+1 where id=sid
END LOOP;
CLOSE myCusor;
END;
----创建job,每个星期的星期天凌晨0点执行检查
Begin
sys.dbms_job.submit(job => 301,
what => 'autoUpdate;',
next_date => to_date(sysdate, 'YYYY-MM-DD'),
interval => 'NEXT_DAY(TRUNC(SYSDATE), ''SUNDAY'') + 0/24');
Commit;
End; begin
sys.dbms_job.remove(:301);
end;
以上PROC在创建后在PLSQL Developer有个小红叉,有问题啊,而且创建job也不对
as
sid number;
begin
OPEN myCusor for select id from t_inspector where states=10 and sysdate-to_date(requesttime,'yyyy-mm-dd')>0;------这个游标定义移下来
LOOP
FETCH myCusor INTO sid;
EXIT WHEN myCusor%NOTFOUND;
update t_inspector set period=period+1 where id=sid;---少了分号。
END LOOP;
CLOSE myCusor;
END;
sys.dbms_job.submit(job => 301,
what => 'autoUpdate;',
next_date => to_date(sysdate, 'YYYY-MM-DD'),
interval => NEXT_DAY(TRUNC(SYSDATE), 'SUNDAY') + 0/24');----这里多了个next_day前面的引号,建议字符不使用双引号(sunday那里)
Commit;
End;
sid number;
cursor myCusor is
select id
from t_inspector
where states = 10
and sysdate - to_date(requesttime, 'yyyy-mm-dd') > 0;
begin OPEN myCusor;
LOOP
FETCH myCusor
INTO sid;
EXIT WHEN myCusor%NOTFOUND;
update t_inspector set period = period + 1 where id = sid;
END loop;
CLOSE myCusor; ----创建job,每个星期的星期天凌晨0点执行检查
Begin
sys.dbms_job.submit(job => 301,
what => 'autoUpdate;',
next_date => to_date(sysdate, 'YYYY-MM-DD'),
interval => 'NEXT_DAY(TRUNC(SYSDATE), ''SUNDAY'') + 0/24');
Commit;
dbms_job.remove(301);
commit;
End;end;
sys.dbms_job.submit(job => 301,
what => 'autoUpdate;',
next_date => to_date(sysdate, 'YYYY-MM-DD'),
interval => 'NEXT_DAY(TRUNC(SYSDATE), 'SUNDAY') + 0/24')';----这里应该是了个引号,建议字符不使用双引号(sunday那里)
Commit;
End;
create or replace procedure autoUpdate
as
sid t_inspector.id%type;
cursor myCusor is select id from t_inspector where states=10 and sysdate-to_date(requesttime,'yyyy-mm-dd')>0;
begin
OPEN myCusor;
LOOP
FETCH myCusor INTO sid;
EXIT WHEN myCusor%NOTFOUND;
update t_inspector set period=period+1 where id=sid;
END LOOP;
CLOSE myCusor;
END;
Begin
sys.dbms_job.submit(job => 301,
what => 'autoUpdate;',
next_date => to_date(sysdate, 'YYYY-MM-DD'),
interval => 'TRUNC(NEXT_DAY(SYSDATE,1))+0/24');
Commit;
End;