PL/SQL程序单元

PL/SQL程序单元

PL/SQL程序单元：

• /**
  第一个存储过程，向tab_stu插入一条数据
  **/
  
  create or replace procedure firstPro 
  --is相当于前面的declare在is后面写申明变量
  is
   v_stu_age number:=23;
  begin
    insert into tab_stu(stu_id,stu_name,stu_age,class_id)
    values(5,'德玛',v_stu_age,3);
    commit;
  end firstPro;

  
  

• /**
  带参数的存储过程
  **/
  create or replace procedure paramPro
  (
  --默认是输入参数
  v_stu_id varchar2,
  v_stu_name varchar2,
  v_stu_age number,
  v_class_id number
  ) 
  is
  begin
    insert into  tab_stu(stu_id,stu_name,stu_age,class_id)
    values(v_stu_id,v_stu_name,v_stu_age,v_class_id);
    commit;
  end paramPro;

  
  

• /**
  存储过程更多情况下是在数据库方做数据整合等复杂的工作
  需求：
  每天在规定时间需要备份tab_stu表的数据
  思路：
  1.备份上一次没有备份的数据。
  2.这个时候就需要有一个id来存储上次备份到那
  3.备份完后，需要修改id的值。
  4.还需要有一个备份表
  **/
  
  create or replace procedure backPro(
  --上次备份最后的id
  max_id number
  ) 
  is
  --用游标来存储，需要备份的数据
  cursor c_tab_stu is select * from tab_stu where stu_id>max_id;
  --定义rowtype
  r_tab_stu tab_stu%rowtype;
  
  --存储备份后的最大id的值
  v_max_id number;
  --索引值
  v_index number:=0;
  begin
    if c_tab_stu%isopen then
      null;
    else 
      open c_tab_stu;
    end if;
    --循环备份
    loop
      fetch c_tab_stu into r_tab_stu;
      exit when c_tab_stu%notfound;
      
      insert into tab_stu_back(stu_id,stu_name,stu_age,class_id) 
      values(r_tab_stu.stu_id,r_tab_stu.stu_name,r_tab_stu.stu_age,r_tab_stu.class_id);
      /**
      下面这样写因为：
      1.如果数据量很大的话，一次性提交或插入一条提交一次。都是不好的。
       1.1.一次性提交，因为Oracle是将回滚段存到内存中的，如果数据量很大内存会出现问题。
       1.2.如果插入一条提交一次，上面的问题没有了。但是性能非常的差
      2.这里就让它每插入2000条就提交一次。
      3.这里记得归零。
      **/
      if v_index>2000 then 
        commit;
        v_index:=0;
        else 
          v_index:=v_index+1;
      end if;
      
    end loop;
    
    commit;
    
    close c_tab_stu;
    --修改tab_max_id的max_id的值为备份表中的最大id的值
    
    select max(stu_id) into v_max_id from tab_stu_back;
    update tab_max_id set max_id=v_max_id;
    commit;
  end backPro;

  
  

• --通过一个存储过程调用另一存储过程
  create or replace procedure invockParam
  is
  v_max_id number;
  
  begin
   select max_id into v_max_id from tab_max_id;
   --调用backpro存储过程
   backpro(v_max_id);        
  end invockParam;

  这里只要执行上面的存储过程就能备份了。

• --创建调度任务定时器
  
  declare
    jobno number;--这个可以随便定义
  begin
    
    sys.dbms_job.submit(jobno,
                        what => 'invockParam;', --invockParam为存储过程的名称
                        Interval =>'TRUNC(sysdate,''mi'')+1/(24*60)' --定义时间间隔每分钟
                        );
    commit;
  end;

  上面的定时也可以通过PL/SQL  developer创建，还是左边的选择DBMS_Jobs右键新建，在弹出窗口中，查看sql。输入上面的内容就行。通过小齿轮运行。