存储过程查询
select * from user_procedures;
或者
select * from user_objects where object_type='PROCEDURE';-- 一定要大写
类似的查询Function
select * from user_objects where object_type='FUNCTION';
查看所有用户(注意有查询权限)
select * from all_procedures;
或者
select * from all_objects where object_type='PROCEDURE';
可举一反三,如要查看当前用户所有表
select * from user_tables;
或者
select * from user_objects where object_type='TABLE';
查询存储过程的具体信息(可以查询创建语句)
SELECT * FROM USER_SOURCE WHERE TYPE='PROCEDURE'
存储过程的创建
-- 密码过期后冻结用户
create or replace procedure update_staff_password_expire( ENDTIME VARCHAR2 ) is
V_ERROR VARCHAR2(255);
V_POINT VARCHAR2(10);
v_errorcode number;
v_errortext varchar2(200);
v_endtime date;
begintime varchar2(50);
begin
if trim(ENDTIME) is null then
v_endtime := ADD_MONTHS( trunc(sysdate),-6);
else
v_endtime :=ADD_MONTHS( TO_DATE(ENDTIME, 'yyyy-mm-dd'),-6);
end if;
INSERT INTO TD_M_STAFF_PRC_LOG VALUES
('update_staff_password_expire', SYSDATE, 'begin', 'Procedure is begin', '', '');
COMMIT;
begintime := to_char(sysdate,'yy-mm-dd hh24:mi:ss') ;
update sid.td_m_staff T set t.state='12', t.remark='六个月未修改密码,状态置为冻结', t.staff_desc=begintime||'存储过程执行' Where t.staff_id in (SELECT t.staff_id
FROM SID.TD_M_STAFf t
WHERE t.state='10'
and t.password_update_time<v_endtime);
COMMIT;
--记日志
INSERT INTO TD_M_STAFF_PRC_LOG
VALUES
('update_staff_password_expire', SYSDATE, 'finish', 'Procedure is OK', '', '');
COMMIT;
EXCEPTION
WHEN OTHERS THEN
V_ERROR := SQLERRM || ' 错误点:' || V_POINT;
v_errorcode := SQLCODE;
v_errortext := SUBSTR(SQLERRM, 1, 200);
ROLLBACK;
INSERT INTO TD_M_STAFF_PRC_LOG
VALUES
('update_staff_password_expire', SYSDATE, V_ERROR, '', v_errorcode, v_errortext);
COMMIT;
RETURN;
end update_staff_password_expire;
存储过程的调用
创建job
-- 创建job,建立job后默认是执行的
-- next_date设为SYSDATE,会导致存储过程立即执行,这里也可以设为具体的时间,如:to_date('01-07-2017 01:00:00', 'dd-mm-yyyy hh24:mi:ss')
declare password_expire number;
begin
sys.dbms_job.submit(job => password_expire,
what => 'update_staff_password_expire('''');',
next_date => SYSDATE,
interval => 'TRUNC(SYSDATE)+1+1/24');
commit;
end;
/
查询job
查询指定用户的job
select * from sys.user_jobs;
select * from user_jobs;
select * from dba_jobs;
查询所有的job
select * from all_jobs;
select job,broken,what,interval,t.* from sys.user_jobs t;
类似的查询 dbms_scheduler
select * from user_scheduler_jobs;
查询正在运行的job
select * from dba_jobs_running;
job的执行
declare
begin
dbms_job.run(141);
end;
job不执行的原因
测试过程中,添加job之后,定时任务死活执行不了,后来了解到这里是根设置有关
查询 JOB
SQL> show parameter job
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes integer 0
这里的值为0,所以问题就出现在这里
改为10,执行
SQL> alter system set job_queue_processes=10;
System altered.
这种改动会立即生效,但不会保存到SPFILE, 数据库重启后此配置丢失;
sql>alter system set job_queue_processed=10 scope=BOTH;
这种改动即对当前实例起作用,重启后也同样有效(SPFILE,MEMORY)
job的各字段的含义
字段(列) 类型 描述
JOB NUMBER 任务的唯一标示号
LOG_USER VARCHAR2(30) 提交任务的用户
PRIV_USER VARCHAR2(30) 赋予任务权限的用户
SCHEMA_USER VARCHAR2(30) 对任务作语法分析的用户模式
LAST_DATE DATE 最后一次成功运行任务的时间
LAST_SEC VARCHAR2(8) 如HH24:MM:SS格式的last_date日期的小时,分钟和秒
THIS_DATE DATE 正在运行任务的开始时间,如果没有运行任务则为null
THIS_SEC VARCHAR2(8) 如HH24:MM:SS格式的this_date日期的小时,分钟和秒
NEXT_DATE DATE 下一次定时运行任务的时间
NEXT_SEC VARCHAR2(8) 如HH24:MM:SS格式的next_date日期的小时,分钟和秒
TOTAL_TIME NUMBER 该任务运行所需要的总时间,单位为秒
BROKEN VARCHAR2(1) 标志参数,Y标示任务中断,以后不会运行
INTERVAL VARCHAR2(200) 用于计算下一运行时间的表达式
FAILURES NUMBER 任务运行连续没有成功的次数
WHAT VARCHAR2(2000) 执行任务的PL/SQL块
CURRENT_SESSION_LABEL RAW MLSLABEL 该任务的信任Oracle会话符
CLEARANCE_HI RAW MLSLABEL 该任务可信任的Oracle最大间隙
CLEARANCE_LO RAW MLSLABEL 该任务可信任的Oracle最小间隙
NLS_ENV VARCHAR2(2000) 任务运行的NLS会话设置
MISC_ENV RAW(32) 任务运行的其他一些会话参数
ORACLE JOB INTERVAL参数设置
每分钟执行
Interval => TRUNC(sysdate,'mi') + 1/(24*60)
每天定时执行
例如:每天的凌晨2点执行
Interval => TRUNC(sysdate) +1+2/24
每周定时执行
例如:每周一凌晨2点执行
Interval => TRUNC(next_day(sysdate,'星期一'))+2/24
每月定时执行
例如:每月1日凌晨2点执行
Interval =>TRUNC(LAST_DAY(SYSDATE))+1+2/24
每季度定时执行
例如每季度的第一天凌晨2点执行
Interval => TRUNC(ADD_MONTHS(SYSDATE,3),'Q') +2/24
每半年定时执行
例如:每年7月1日和1月1日凌晨2点
Interval => ADD_MONTHS(trunc(sysdate,'yyyy'),6)+2/24
每年定时执行
例如:每年1月1日凌晨2点执行
Interval =>ADD_MONTHS(trunc(sysdate,'yyyy'),6)+2/24
引用
--1.plsql中学习job
--学习job
--建表
create table test_job(para_date date);
commit;
insert into test_job values(sysdate);
commit;
select * from test_job;
--建立存贮过程
create or replace procedure test_jobproce as
begin
insert into test_job values(sysdate);
end test_jobproce;
--建立job
--建立job后默认是执行的
declare test_job_really number;
begin
dbms_job.submit(test_job_really,'test_jobproce;',sysdate,'sysdate+1/1440');
commit;
end;
---停止job 25是建立的job test_job_really
begin
dbms_job.broken(25,true);
commit;
end;
--启动job
begin
dbms_job.run(25);
commit;
end;
--删除job
begin
dbms_job.remove(25);
commit;
end;
--查看执行结果
select * from test_job order by test_job.para_date desc;
--查看job
select * from sys.user_jobs
--使用下面的SQL查询是否JOB还在Running,前提是需要job执行时间不能过短
select * from dba_jobs_running
--除了submit参数外,其余的几个参数有:
--运行job
dbms_job.run(v_job);
--停止一个job,里面参数true也可是false,next_date(某一时刻停止)也可是sysdate(立刻停止)。
dbms_job.broke(v_job,true,next_date);
--删除某个job
dbms_job.remove(v_job);
dbms_job.what(v_job,'sp_fact_charge_code;');
--修改某个job名 修改下一次运行时间
dbms_job.next_date(v_job,sysdate);
Oracle 10g引入dbms_scheduler来替代先前的dbms_job,在功能方面,它比dbms_job提供了更强大的功能和更灵活的机制/管理。详见 百度
查询scheduler
SELECT * FROM all_scheduler_jobs;
本文固定链接:心知博客 » Oracle中存储过程的常用操作
本站内容除特别标注外均为原创,欢迎转载,但请保留出处!