oracle存储过程以及定时任务

–创建存储过程
CREATE OR REPLACE
procedure t1
is
–开启游标
cursor cur is select * from dual;
–游标一行返回的所有类型
curtype cur%rowtype;
begin
–开启游标
open cur;
–开启循环
loop
fetch cur into curtype;
–退出条件
exit when cur%notfound;
–打印值
dbms_output.put_line(curtype.dummy);
end loop;
–关闭游标
close cur;

end;

–执行存储过程
begin
T1;
end;

–查看存储过程,查看存储过程名称默认大写
select * from user_source where name =‘T1’
–删除存储过程
drop procedure t1;

–创建定时任务
declare
job number;
BEGIN
DBMS_JOB.SUBMIT(
JOB => job, /自动生成JOB_ID/
WHAT => ‘t1;’, /需要执行的存储过程名称或SQL语句/
NEXT_DATE => sysdate, /初次执行时间-立即执行/
INTERVAL => ‘trunc(sysdate+1)+1/(24)’ /每隔1分钟执行一次/
);
commit;
end;

–查询定时任务
select * from user_jobs;
–移除定时任务job——id
call dbms_job.remove(id);