17,377
社区成员
发帖
与我相关
我的任务
分享
create or replace procedure pro_onlineuser
AS
carinfo_id number;
BEGIN
carinfo_id := 3;
end pro_onlineuser;
-- Created on 2012/2/10 by ADMINISTRATOR
DECLARE
-- Local variables here
jobID binary_integer;
beginDate DATE;
BEGIN
beginDate := to_date('10-02-2012 23:30:30', 'DD-MM-YYYY HH24:MI:SS');
dbms_job.submit(jobID, 'pro_onlineuser;', beginDate, 'SYSDATE + 1' );
END;
CREATE OR REPLACE procedure BarDataCompare as
begin
----中班已到未发---------------------------------------------------------
INSERT INTO TB_BarScanData
(OrderID,Scantype,SiteId,CenterId,EnterSite,ScanTime,EnterTime)
select OrderID,'02',SiteId,CenterId,EnterSite,
to_date(to_char(sysdate,'yyyy-mm-dd') || '22:00:00','YYYY-MM-DD HH24:MI:SS'),
EnterTime
from TB_BarScanData t
where ScanTime between
to_date(to_char(sysdate,'yyyy-mm-dd') || '13:0:00','YYYY-MM-DD HH24:MI:SS')
and
to_date(to_char(sysdate,'yyyy-mm-dd') || '23:30:00','YYYY-MM-DD HH24:MI:SS')
and ScanType= '03' and CenterID = '76900' and EnterSite ='76900'
and not exists(select OrderID from TB_BarScanData
where ScanType= '02' and CenterID = '76900' and EnterSite ='76900')
----中班未到已发--------------------------------------------------------
INSERT INTO TB_BarScanData
(OrderID,Scantype,SiteId,CenterId,EnterSite,ScanTime,EnterTime)
select OrderID,'03',SiteId,CenterId,EnterSite,
to_date(to_char(sysdate,'yyyy-mm-dd') || '22:00:00','YYYY-MM-DD HH24:MI:SS'),
EnterTime
from TB_BarScanData t
where ScanTime between
to_date(to_char(sysdate,'yyyy-mm-dd') || '13:0:00','YYYY-MM-DD HH24:MI:SS')
and
to_date(to_char(sysdate,'yyyy-mm-dd') || '23:30:00','YYYY-MM-DD HH24:MI:SS')
and ScanType= '02' and CenterID = '76900' and EnterSite ='76900'
and not exists(select OrderID from TB_BarScanData
where ScanType= '03' and CenterID = '76900' and EnterSite ='76900')
end;
VARIABLE jobno number;
MYDATE DATE;
begin
DBMS_JOB.SUBMIT(:jobno,'pro_onlineuser;',SYSDATE,'TRUNC(SYSDATE + 1) + (23*60+30)/(24*60)');
commit;
end;
SYS.DBMS_JOB.RUN(:jobno);
select job,next_date,next_sec,failures,broken from user_jobs;
create or replace procedure pro_onlineuser
AS
carinfo_id number;
BEGIN
end pro_onlineuser;
VARIABLE jobno number;
MYDATE DATE;
begin
select TO_DATE(to_char(sysdate,'YYYY/MM/DD')||'23:30:30','YYYY/MM/DD hh24:mi:ss')INTO MYDATE from dual;
DBMS_JOB.SUBMIT(:jobno,'pro_onlineuser;',MYDATE ,'sysdate+1/24/60');
commit;
end;
VARIABLE jobno number;
MYDATE VARCHAR2(18);
begin
select to_char(sysdate,'YYYY/MM/DD')||'23:30:30' INTO MYDATE from dual;
DBMS_JOB.SUBMIT(:jobno,'pro_onlineuser;',SYSDATE,MYDATE);
commit;
end;
-- 在SQL*Plus中执行:
variable pro_get_table_increment_jb number;
begin
dbms_job.submit(:pro_get_table_increment_jb,'pro_get_table_increment;',sysdate,'TRUNC(SYSDATE+1)+(23.5*60*60+30)/(24*60*60)');
end;
/