--任务队列管理器

begin
sys.dbms_job.submit(job => :job,
what => 'sm_p_insertMessage;',
next_date => to_date('07-09-2010 02:30:00', 'dd-mm-yyyy hh24:mi:ss'),
interval => 'TRUNC(SYSDATE + 1) + (2*60+30)/(24*60)');
commit;
end;

--

oracle 创建表时判断表是否存在语句

declare cnt number;

begin
---查询要创建的表是否存在
select count(*)into cnt from user_tables where table_name='ENTRY_MODIFYSTATUS';
---如果存在则删除该表
if cnt>0 then
dbms_output.put_line('表存在不创建');
else
dbms_output.put_line('表不存在');
execute immediate 'create table ENTRY_MODIFYSTATUS (
ENTRY_ID VARCHAR2(18) not null,
APPLY_TIME DATE not null,
STATUS NUMBER(2),
constraint PK_ENTRY_MODIFYSTATUS primary key (ENTRY_ID, APPLY_TIME)
)';
end if;

cnt:=0;
end;

==============================================

CREATE OR REPLACE PROCEDURE AP_T_RPT_PRO_LOGINCOUNT
AS
login_count NVARCHAR2(100);
week_login_count NVARCHAR2(100);
--date_time DATE;
now_time DATE;
flag CHAR(1);
BEGIN
SELECT TRUNC(SYSDATE) INTO now_time FROM dual;
--date_time := to_date('2010-6-7', 'YYYY-MM-DD');
--WHILE(date_time < now_time-6)
--date_time := date_time + 7;
--SELECT d.ANALYSIS_FLAG INTO flag FROM ap_t_rpt_login_dict d
FOR A IN (SELECT start_time FROM ap_t_rpt_login_dict WHERE start_time < now_time-6)
LOOP
BEGIN
SELECT d.ANALYSIS_FLAG INTO flag FROM ap_t_rpt_login_dict d WHERE d.start_time = A.START_TIME;
IF(flag = 'N')
THEN
FOR C IN (SELECT u1.unit_id FROM sm_t_unit u1)
LOOP
SELECT COUNT(DISTINCT t.staff_id) INTO login_count FROM SM_t_login T
WHERE t.unit_id = C.UNIT_ID
AND t.login_time > add_months(A.START_TIME + 7,-2)
AND t.login_time < A.START_TIME + 7;

SELECT COUNT(DISTINCT t.staff_id) INTO week_login_count FROM SM_t_login T
WHERE t.unit_id = C.UNIT_ID
AND t.login_time > A.START_TIME
AND t.login_time < A.START_TIME + 7;

INSERT INTO ap_t_rpt_login (rpt_login_id, unit_id, start_time, user_count, week_login_user)
VALUES(ap_s_rpt_login.nextval, C.UNIT_ID, A.START_TIME, login_count, week_login_count);

END LOOP;
UPDATE ap_t_rpt_login_dict SET ANALYSIS_FLAG = 'Y' WHERE start_time = A.START_TIME;

END IF;
END;
END LOOP;

END;