Oracle中存储过程的常用操作

数据库 小智 37℃ 0评论

存储过程查询

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中存储过程的常用操作
本站内容除特别标注外均为原创,欢迎转载,但请保留出处!

喜欢 (0)
发表我的评论
取消评论
表情

请填写您的昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址