Oracle基本操作十二：存储过程及定时调用存储过程

--程序单元：数据库中命名的PL/SQL块，作为数据对象保存在数据库中
--分类：
  1.过程：执行特定操作，无返回值,相当于PL/SQL块起个名字
  2.函数：进行复杂计算，有返回值,相当于PL/SQL块起个名字
  3.包：逻辑上相关的过程和函数组织在一起
  4.触发器：事件触发，执行相关操作
--结构:2345称为匿名块
  1.header--必须的，了程序的名称、类型和参数
  2.declare--局部变量声明
  3.executable--必须的，sql和控制语句
  4.exception--异常处理
  5.end;--必须
-- 参数模式
  1.in      默认传递，值被传送给子程序，子程序上可以改变参数值
            可以是表达式，变量（有值或无值）
  2.out     必须指定，值被返回调用环境，子程序不能改变参数值
            必须是变量（有值或无值）
  3.in out  必须指定，值被返回调用环境，子程序上可以改变参数值
            必须是变量（有值）
--建的存储过程位于Procedure文件夹中
--举例一.1:新建无参数存储过程：向学生表中插入一条数据
create or replace procedure firstpro  
is --定义变量的地方
   v_sex varchar2:='男';
begin
   insert into tb_student(id,name,age，sex) 
   values(seq_tb_student.nextval,'npl','21',v_sex) ;
   commit;
end firstpro;
注：第一次F8不是执行，是编译并存储在数据库中
    第二次F8是执行
--举例一.2：调用存储过程
begin
   fristpro;
end;


--举例二.1：新建带参数的存储过程
      应用场景:用户从注册页面填写信息，通过jdbc调用存储过程，效率高
create or replace procedure parampro
(
v_name varchar2,
v_age varchar2,
v_sex varchar2
)  
is --定义变量的地方
   v_sex varchar2:='男';
begin
   insert into tb_student(id,name,age，sex) 
   values(seq_tb_student.nextval,v_name,v_age,v_sex) ;
   commit;
end parampro;
注：第一次F8不是执行，是编译并存储在数据库中
    第二次F8是执行
--举例二.2：调用带参数的存储过程
begin
   parampro('npl','25','女');
end;


--存储过程更多情况下是在数据库方做数据整合等复杂的工作
--举例四.1：需求：第一天备份80万条记录，第二份每次备份新增80万条数
   备份在晚上12点进行，
   1.每天备份完之后，max(id)存到tb_maxid表
   2.下次备份时select max(id)
   分析：select * from table where id >max(id)则备份
   步骤：1.提取_student表所有数据
         2.循环插入到tb_student_bak表中
3.完成后记录max(id)到tb_maxid表
create or replace procedure backpro (v_maxid number) is
  cursor c_tb_student is 
  select * from tb_student where id>v_maxid;
  r_tb_student tb_student%rowtype;
  --判断循环次数
  v_index number:=0;
  v_max number;
begin
 --打开游标
  if c_tb_student%isopen then--判断游标是否打开
     null;--只是维护语句的完整性，没有任何效果
  else
     open c_tb_student;
  end if
  --循环插入数据
  loop
    fetch c_tb_student into r_tb_student;
exit when c_tb_student%notfound;
insert tb_student_back(id,name,sex,age)
values(r_tb_student.id,r_tb_student.name,r_tb_student.sex,r_tb_student.age);

v_index number:=v_index+1;
--分段提交
if(v_index=2000) then 
  commit;--问题：放在循环内，效率低，但放到循环外，当回滚段（内存）不足时，会抛异常
         --可以要求管理员加大回滚段---不现实
--可以分段提交，批量提交
v_index :=0;
end if;
  end loop;
  commit;--提交剩余的数据
  
  --实现增量备份
  select max(id) into v_max from tb_student_back;
  update tb_maxid set id =v_max;
  commit;
  
  --关闭游标
  close c_tb_student;
end backpro;


--举例四.2调用存储过程
create or replace procedure invokdbackpro
is 
v_maxid number;
begin
  select id into v_maxid from tb_maxid;
  bachpro(v_maxid);
end invokdbackpro;


--举例四.3：测试
begin
   invokdbackpro;
end;


--举例四.4：定时调度存储过程
--创建调度任务定时器：运行后会在DBMS_job文件夹下生成一个job
declare
  jobno number;
begin
  dbms_job.submit(
    jobno,
what =>'invokdbackpro',--存储过程的名称
Interval => 'TRUNC(sysdate,''mi'')+1/24*60'--定义时间间隔为每分钟
  )
end;