CREATE OR REPLACE PROCEDURE adddata (a VARCHAR2, b VARCHAR2)
AS
BEGIN
insert into b select * from a where rownom <=10;
delete from a where rownum <= 10;
commit;
END;
/
我的意思是每天的早上8:00到下午的19:00之间这个“判断”应该怎样写,我的代码是这样写的,
if((TO_date('9:00:00','HH24:MI:SS')<=to_date(sysdate,'HH24:MI:SS')) or (to_date(sysdate,'HH24:MI:SS')<=TO_date('19:00:00','HH24:MI:SS')))。但是报错了,估计是to_date或者to_char错了,但是不知道具体该怎么写。
创建存储过程:
CREATE OR REPLACE PROCEDURE adddata
AS
BEGIN
insert into b select * from a where rownom <=10;
delete from a where rownum <= 10;
commit;
END;
/
执行:
exec adddata;
每天8:00定时执行的话,创建job:
VARIABLE jobno number;
begin
DBMS_JOB.SUBMIT(:jobno,
'adddata;',
SYSDATE, 'trunc(SYSDATE) +1+ 8/24');
commit;
end;
/