表:
CREATE TABLE SMS_1
(
SUBSCRIBEID VARCHAR2(32 BYTE) NOT NULL,
SUMMARY VARCHAR2(80 BYTE),
CONTENT CLOB NOT NULL,
AUTHORIZEFLAG NUMBER(1) DEFAULT (0) NOT NULL,
SENDPOINT DATE,
QUEUEFLAG NUMBER(1) DEFAULT (0) NOT NULL
)SMS_1表中SENDPOINT字段值为时间类型,当SENDPOINT字段值为何值时,就何时把QUEUEFLAG改为'1';
如:当SENDPOINT为'2009-3-22',那就到'2009-3-22'这天再触发事件,将QUEUEFLAG改为'1',
请问该如何写?请高人指点..
回复必给分..
CREATE TABLE SMS_1
(
SUBSCRIBEID VARCHAR2(32 BYTE) NOT NULL,
SUMMARY VARCHAR2(80 BYTE),
CONTENT CLOB NOT NULL,
AUTHORIZEFLAG NUMBER(1) DEFAULT (0) NOT NULL,
SENDPOINT DATE,
QUEUEFLAG NUMBER(1) DEFAULT (0) NOT NULL
)SMS_1表中SENDPOINT字段值为时间类型,当SENDPOINT字段值为何值时,就何时把QUEUEFLAG改为'1';
如:当SENDPOINT为'2009-3-22',那就到'2009-3-22'这天再触发事件,将QUEUEFLAG改为'1',
请问该如何写?请高人指点..
回复必给分..
VARIABLE JOBNO NUMBER;
begin
sys.dbms_job.submit(job=>:JOBNO,
what=>'update sms_1 set queueflag=1 where sendpoint = trunc(sysdate);',
next_date=>to_date('27-02-2009 00:00:00', 'dd-mm-yyyy hh24:mi:ss'),
interval=>'trunc(sysdate+1)');
commit;
end;
/
做了一个每分钟更新的例子:
create or replace procedure proc_sms_1 as
begin
update sms_1
set queueflag = 1
where sendpoint = trunc(sysdate, 'mi')
and queueflag <> 1 ;
commit;
end;
begin
sys.dbms_job.submit(
job => :job,
what => 'proc_sms_1;',
next_date => trunc(sysdate, 'mi') + 1/(24*60),
interval => 'trunc(sysdate, ''mi'') + 1/(24*60)' //需要每天更新的话,把这里的时间修改一下就好了
);
end; SQL> select * from sms_1 where queueflag = 1;SENDPOINT QUEUEFLAG
------------------- ----------
2009-02-26 18:30:00 1SQL> select sysdate from dual;SYSDATE
-------------------
2009-02-26 18:31:00SQL> select * from sms_1 where queueflag = 1;SENDPOINT QUEUEFLAG
------------------- ----------
2009-02-26 18:30:00 1
2009-02-26 18:31:00 1