Oracle中存储过程的常用操作

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后默认是执行的
-- 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
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;

declare
begin
dbms_job.run(141);
end;

测试过程中，添加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                     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)           任务运行的其他一些会话参数

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);

查询scheduler
SELECT * FROM all_scheduler_jobs;