oracle job 实例,Oracle job实例

--建存储过程

create or replace procedure add_table_view_to_dataObject

IS

--查询数据库中所有的系统

CURSOR sysCur IS select sys_id from FBS_SYSTEM;

--查询数据库中所有的用户表名

CURSOR tabCur IS select TABLE_NAME from user_tables;

--查询数据库中所有的用户视图名

CURSOR viewCur IS select VIEW_NAME from user_views;

BEGIN

--遍历所有的系统

FOR sysId in sysCur LOOP

--遍历所有的用户表

FOR tabName in tabCur LOOP

insert into fbs_dataObject(id,sys_id,object_name,type,sql,note,created_time)

select sys_guid(),sysId.Sys_Id,tabName.Table_Name,'1','select * from '|| tabName.Table_Name,'Oracle job自动插入',sysdate

from dual

where not exists (select 1 from fbs_dataObject where sys_id=sysId.Sys_Id and type='1' and object_name = tabName.Table_Name);

END LOOP;

--遍历所有的用户视图

FOR viewName in viewCur LOOP

insert into fbs_dataObject(id,sys_id,object_name,type,sql,note,created_time)

select sys_guid(),sysId.Sys_Id,viewName.View_Name,'2','select * from '|| viewName.View_Name,'Oracle job自动插入',sysdate

from dual

where not exists (select 1 from fbs_dataObject where sys_id=sysId.Sys_Id and type='2' and object_name = viewName.View_Name);

END LOOP;

END LOOP;

END;

-- 测试脚本

begin

-- Call the procedure

add_table_view_to_dataobject;

end;

--建job并提交(提交后默认立即运行)

declare

jobId number;

begin

dbms_job.submit(jobId, 'add_table_view_to_dataObject;', sysdate, 'sysdate + 10/86400', true);--每10秒执行一次

end;

--查看系统中的用户job

select * from user_jobs;

--暂停job(立即停止)

begin

dbms_job.broken(24,true,sysdate);

end;

--重启job；

begin

dbms_job.run(24);

end;

--修改job的执行频率

dbms_job.interval(24,'sysdate + 10/86400');

--删除job；

begin

dbms_job.remove(24);

end;

参考：http://www.cnblogs.com/hoojo/p/oracle_procedure_job_interval.html