简单的oracle存储过程

前段时间学习oracle简单的存储过程，以备项目中偶尔需要用到，这里做一个简单的总结；

--第一个存储过程


create or replace procedure firstpro is

begin
select *
from sys_org_element;
end firstpro;

//执行存储过程
begin
firstpro
end;




--带参数的存储过程
create or replace procedure secondpro
(v_bukrs varchar)
is
begin
select *
from hisense_hr_t001
where bukrs=v_bukrs;
end secondpro;

//执行存储过程
begin
secondpro('2000');
end;


declare
jobno number;
begin
sys.dbms_job.submit(jobno,
what => 'seleid;',
interval => 'trunc(sysdate,''mi'')+1/(24*60)'
); 
commit;
end;




--备份sys_org_element


create table sys_org_element_bak
(id  varchar(32),
fd_name varchar(32));


select *
from sys_org_element_bak;
--drop table sys_org_element_bak;


create table saverowid(
id number
);


select *
from saverowid;


update saverowid set id=0;
commit;


insert into saverowid(id) values(0);
commit;





//带循环的存储过程
create or replace procedure bakorg(v_id number) is
  cursor x_cursor is
    select fd_id,fd_name,rn
from(
select person.*,rownum rn from sys_org_element person
    where rownum <=v_id+1000 ) a
    where a.rn>v_id;
  bak_id sys_org_element.fd_id%type;
  bak_fd_name sys_org_element.fd_id%type;
  bak_rn number;
  i       number := 0;
begin
open x_cursor;
  loop
    i := i + 1;
    fetch x_cursor
      into bak_id,bak_fd_name,bak_rn;
      exit when x_cursor%notfound;
    insert into sys_org_element_bak
      (ID,
       fd_name)
    values
      (bak_id,bak_fd_name);
    if (i mod 100 = 0) then
    dbms_output.put_line('提交了');
      commit;
    else
      null;
      end if;
    end loop;
    close x_cursor;
    update saverowid set id=v_id+1000;
    commit;
  end bakorg;
  
create or replace procedure seleid is
v_id number;
begin
select id
into v_id
from saverowid;
bakorg(v_id);
end seleid;


--定时任务 每分钟


declare
jobno number;
begin
sys.dbms_job.submit(jobno,
what => 'seleid;',
interval => 'trunc(sysdate,''mi'')+1/(24*60)'
); 
commit;
end;