--使用明細表
--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;