oracle dbms_job 自動調用

 --使用明細表
--no            商品編號
--id             人員ID
--limit_year   使用年限
--grant_date 發放日付
--stale         是否失效   

--建立一個Oracle内部自動調用的存儲過程，隔一分鐘檢査Details表，
--如果[發放日付]+[使用年限] > [當前日付]， 將[是否失效]設置為1。

drop table details

create table details
(
no int ,
id varchar2(4),
 primary key(no,id),
limit_year int  not null,
grant_date date,
stale char(1)
)

select * from details

-- insert value
insert into details values(1,'1001',2,'20081202',null)
insert into details values(2,'1002',1,'20081202',null)

update details
where
--select sysdate,sysdate - interval  '1'  year from dual
select limit_year from details

 

drop procedure proc_checkdate

-- 創建存儲過程

create or replace procedure proc_checkdate

as
--r_row details%rowtype;
v_stale char(1);
v_date date;
--v_id int;
--v_no char(4);
--v_no int;
--v_id char(4);

cursor c1  is
select * from details ;

begin
    --open c1;  
    --fetch c1 into r_row;
    for i in c1 loop
    --v_id := i.id;
    --v_no := i.no;
    --select 1  into v_stale from details t where t.no = 1 ;
    dbms_output.put_line(i.limit_year);
    dbms_output.put_line(i.grant_date);
    dbms_output.put_line('=======================');
    select add_months(i.grant_date, i.limit_year*12) into v_date from details t  where t.no =i.no and t.id = i.id;
   
   
    dbms_output.put_line('v_date=' || to_char(v_date));
   
    dbms_output.put_line('no=' || i.no);
   
    dbms_output.put_line('id=' || i.id);
   
    dbms_output.put_line('year=' || i.limit_year);
   
    dbms_output.put_line('--------------------');
   
    dbms_output.put_line('current_date=' || current_date );
   
    dbms_output.put_line('v_date=' || v_date );
   
    --select 1 into v_stale from dual where to_date(current_date, 'yyyy/mm/dd') > to_date(v_date,'yyyy/mm/dd');
   
    --select 1 into v_stale  from details t  where current_date > v_date and t.no = i.no and t.id = i.id;
   
    --select 1 into v_stale  from dual  where  current_date > v_date ;
   
    if current_date > v_date then
       DBMS_OUTPUT.put_line('!!!!!!!!!!!!!!!!!!!!!!!!!!!');
       --dbms_output.put_line('v_stale=' || v_stale);
       --v_stale := 1;
      
       update details t set t.STALE = '1' where t.NO = i.no and t.id = i.id;
    end if;
   
    DBMS_OUTPUT.put_line('v_stale=' || v_stale);
   
    --update details t set t.STALE = v_stale where v_stale = '1' and t.NO = i.no and t.id = i.id;
   
    --select 1 into v_stale  from details t  where current_date > (select add_months(t.grant_date, t.limit_year*12) from details t where t.no =i.no and t.id = i.id ) and t.no = i.no and t.id = i.id;
    --update details t set stale = v_stale where t.no = i.no and t.id = i.id ;
   
    end loop;
    commit;
    --close mycur;
end;

--執行存儲過程
begin
proc_checkdate;
end;

--以下Command Window中運行
--調用DBMS_JOB利用時鐘自動定時和間隔時間執行存儲過程

--===============================
--定義Job

variable myjob number;
begin
dbms_job.submit(:myjob,'proc_checkdate;',sysdate,'sysdate+1/1440');
commit;
end;

--================================
--運行Job

begin
dbms_job.run(:myjob);
end;

--==================================
--移除Job

begin
dbms_job.remove(:myjob);
end;