向各位高手问个问题:
表:
CREATE TABLE SMS_MSGBATCHSENDLOGMAIN
(
SUBSCRIBEID VARCHAR2(32 BYTE) NOT NULL,
BATCHSENDID VARCHAR2(32 BYTE) NOT NULL,
MSGDATE DATE,
SUMMARY VARCHAR2(80 BYTE),
CONTENT CLOB NOT NULL,
AUTHORIZEFLAG NUMBER(1) DEFAULT (0) NOT NULL,
SENDPOINT DATE,
AUTHORIZEDATE DATE,
QUEUEFLAG NUMBER(1) DEFAULT (0) NOT NULL
)
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;这是个定时调用存储过程的,我要当时间为SENDPOINT字段里的时间时,调用上面的存储过程,将QUEUEFLAG值改为1,
请问我上面的代码该怎么改了?回复必给分,谢谢..
表:
CREATE TABLE SMS_MSGBATCHSENDLOGMAIN
(
SUBSCRIBEID VARCHAR2(32 BYTE) NOT NULL,
BATCHSENDID VARCHAR2(32 BYTE) NOT NULL,
MSGDATE DATE,
SUMMARY VARCHAR2(80 BYTE),
CONTENT CLOB NOT NULL,
AUTHORIZEFLAG NUMBER(1) DEFAULT (0) NOT NULL,
SENDPOINT DATE,
AUTHORIZEDATE DATE,
QUEUEFLAG NUMBER(1) DEFAULT (0) NOT NULL
)
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;这是个定时调用存储过程的,我要当时间为SENDPOINT字段里的时间时,调用上面的存储过程,将QUEUEFLAG值改为1,
请问我上面的代码该怎么改了?回复必给分,谢谢..
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)'
);
2.是哪部分时间相同?是年月日时分秒全相同时调用还是只要分钟相同?因为看你写的存储过程,对上面这几个问题处理的模糊。
表为:
CREATE TABLE SMS_1
(
BATCHSENDID VARCHAR2(32 BYTE) NOT NULL, //主键
CONTENT CLOB NOT NULL, //内容
SENDPOINT DATE, //比较的时间,一般为当前时间之后的时间;
QUEUEFLAG NUMBER(1) DEFAULT (0) NOT NULL
) 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;
系统时间为SMS_1表里的sendpoint字段,时间只比较年月日,没有时分移的,谢谢..
begin
update SMS_1
set QUEUEFLAG = 1
where trunc(SENDPOINT, 'mi') = trunc(sysdate, 'mi')
and queueflag <> 1 ;
commit;
end;