存储过程当然不会自动运行,是要靠job来运行 以下的过程和job我没测试过,你看是不是你要的东东--存储过程如下 create procedure p_csdn_test as v_num number(6); v_date date; v_status varchar2(10); begin v_status := '测试'; v_date := sysdate; select count(*) into v_num from test1 where past_time < v_date;
if v_num = 0 then return; end if;
update test2 set status = v_status where exists ( select 1 from test1 where user_id = test2.user_id ); commit; exception when others then rollback; end;--job如下 begin sys.dbms_job.submit(job => :job, what => 'p_csdn_test;', next_date => to_date('07-06-2007 14:00:00', 'dd-mm-yyyy hh24:mi:ss'), interval => 'SYSDATE+1/144'); commit; end; /
以下的过程和job我没测试过,你看是不是你要的东东--存储过程如下
create procedure p_csdn_test
as
v_num number(6);
v_date date;
v_status varchar2(10);
begin
v_status := '测试';
v_date := sysdate;
select count(*) into v_num
from test1
where past_time < v_date;
if v_num = 0 then
return;
end if;
update test2
set status = v_status
where exists
(
select 1
from test1
where user_id = test2.user_id
);
commit;
exception
when others then
rollback;
end;--job如下
begin
sys.dbms_job.submit(job => :job,
what => 'p_csdn_test;',
next_date => to_date('07-06-2007 14:00:00', 'dd-mm-yyyy hh24:mi:ss'),
interval => 'SYSDATE+1/144');
commit;
end;
/