oracle知识汇总

1、存储过程

创建存储过程

create procedure sp_name()

begin

.........

end

调用存储过程

call sp_name()；

删除存储过程

drop procedure sp_name；

 赋权

grant execute on  存储过程名字  to 用户名；

 查询存过是否执行完毕

select * from v$db_object_cache where locks > 0 and pins > 0 and type='PROCEDURE' and owner = '用户' and name = 'sp_name';

 存过模板

-------------------------------------------------------------------------------------------------

1、隐式游标

declare

begin

for a in

(select * from tmp_trf_id)

loop

dbms_output.put_line('--'||a.id_no);

end loop;

end;

---------------------------------------------------------------------------------------------------

declare

V_SQL VARCHAR2(2000);

begin

for a in

(select * from tmp_trf_id)

loop

V_SQL := 'SELECT SUM(PREPAY_FEE) FROM 表名 WHERE CON_NO = :v1';

EXECUTE IMMEDIATE V_SQL

INTO V_SUM

USING a.CON_NO;

dbms_output.put_line('--'||a.id_no||'--'||V_SUM);

end loop;

end;

------------------------------------------------------------------------------------------------------

2、显式游标

declare

type type_cur is ref cursor;

users type_cur;

cursor c is

select * from tmp_trf_id;

begin

for ic in c loop

dbms_output.put_line('--'||ic.id_no);

end loop;

exception

when others then dbms_output.put_line('err');

end;

 存过抛出异常

declare

    v_row test%rowtype；-- 匹配t_test表中一行所有的数据类型  

    cursor v_cur is  

    select * from test；-- 声明游标  

begin  

    open v_cur；-- 打开游标  

        loop  

            fetch v_cur into v_row；-- 将游标所在行的数据转存到v_row中  

            exit when v_cur%notfound; -- 当游标到最后一行时跳出  

            dbms_output.put_line('id = '||v_row.t_id)；

        end loop；

    close v_cur；-- 关闭游标  

    exception  

        when others then dbms_output.put_line('throw exception: others')；

end

if-elsif-else用法

IF condition1 THEN

statements1

ELSIF condition2 THEN

statements2

ELSE

statements3

END IF;

2、oracle定时器

oracle定时器

declare

jobno number; -- 入参

begin

dbms_job.submit(

jobno,

'test_proc;', -- 自己的存过

sysdate, --存过开始的时间

'sysdate+2/24/60' -- 存过执行的时间

);

end;

declare

begin

dbms_job.broken(25,true); -- 停 25

end;

select job, next_date, next_sec, failures, broken from user_jobs; -- 查出25 编号

oracle定时任务

select * from dba_jobs

dbms_jobs

新建

what值 可语句commit 可存过

间隔

（1）.每分钟执行

Interval => TRUNC(sysdate,'mi') + 1/ (24*60)

每小时执行

Interval => TRUNC(sysdate,'hh') + 1/ (24)

（2）.每天定时执行

例如：每天的凌晨1点执行

Interval => TRUNC(sysdate+ 1)  +1/ (24)

（3）.每周定时执行

例如：每周一凌晨1点执行

Interval => TRUNC(next_day(sysdate,'星期一'))+1/24

（4）.每月定时执行

例如：每月1日凌晨1点执行

Interval =>TRUNC(LAST_DAY(SYSDATE))+1+1/24

（5）.每季度定时执行

例如每季度的第一天凌晨1点执行

Interval => TRUNC(ADD_MONTHS(SYSDATE,3),'Q') + 1/24

（6）.每半年定时执行

例如：每年7月1日和1月1日凌晨1点

Interval => ADD_MONTHS(trunc(sysdate,'yyyy'),6)+1/24

（7）.每年定时执行

例如：每年1月1日凌晨1点执行

Interval =>ADD_MONTHS(trunc(sysdate,'yyyy'),12)+1/24