3,491
社区成员
发帖
与我相关
我的任务
分享
BEGIN
SYS.dbms_job.submit(job => :job,
what => 'sp_update_ycz;',
next_date => SYSDATE,
INTERVAL => 'trunc(sysdate) + 20/24');
COMMIT;
END;
begin
sys.dbms_job.submit(job => :job,
what => 'dbms_refresh.refresh(''"U_MOBILE_DB"."MV_BASE_FENXI"'');',
next_date => to_date('02-02-2015 23:00:00', 'dd-mm-yyyy hh24:mi:ss'),
interval => 'trunc(SYSDATE + 1) - 1/24');
commit;
end;
CREATE OR REPLACE PROCEDURE sp_update_ycz
AS
last_date DATE;
BEGIN
SELECT MAX(max_date) INTO last_date FROM record_ycz;
DELETE FROM p_ycz_all WHERE createtime = last_date;
INSERT INTO p_ycz_all
SELECT * FROM ycz@dblinkto_u_dsjycz_db WHERE createtime >= last_date;
COMMIT;
INSERT INTO record_ycz
SELECT SYSDATE,MAX(createtime),COUNT(*),COUNT(DISTINCT mobile),COUNT(user_NAME),COUNT(cardno)
FROM p_ycz_all;
COMMIT;
END
[/quote]
可以啊,固定这样子。
declare
job number;
begin
sys.dbms_job.submit
(job,
'sp_update_ycz;',
to_date('2015-02-02 00:00:00',' yyyy-mm-dd hh24:mi:ss'),
'trunc(sysdate+1)'
);
commit;
end;
CREATE OR REPLACE PROCEDURE sp_update_ycz
AS
last_date DATE;
BEGIN
SELECT MAX(max_date) INTO last_date FROM record_ycz;
DELETE FROM p_ycz_all WHERE createtime = last_date;
INSERT INTO p_ycz_all
SELECT * FROM ycz@dblinkto_u_dsjycz_db WHERE createtime >= last_date;
COMMIT;
INSERT INTO record_ycz
SELECT SYSDATE,MAX(createtime),COUNT(*),COUNT(DISTINCT mobile),COUNT(user_NAME),COUNT(cardno)
FROM p_ycz_all;
COMMIT;
END