存储过程

pl/sql程序单元

　　1、是数据库中命名得PL/sql块，作为数据库对象保存在数据库里。

　   2、主要有四类：

　　　　-过程：执行特定操作，无返回值

　　　　-函数：进行复杂计算，有返回值

　　　　-包：逻辑上相关的过程和函数组织在一起

　　　　-触发器：事件触发，执行响应操作

程序单元结构

　　header——必须

　　　　-子程序名称、类型和参数

　　declarative可选

　　　　-局部变量声明

　　executable——必须

　　　　-sql语句

　　　　-pl/sql控制语句

　　exception handling--可选

　　　　-异常处理

　　end；--必须

 

　　快速建立程序窗口

create or replace procedure qq is
begin
  
end qq;

 

------无参数的存储过程

/********
第一个存储过程，向tb_student表插入一条数据
**************/
create or replace procedure qq 
is
  v_sex varchar2(18) :='男';
begin
       insert into tb_student(id,name,sex,age,email,address,iphone4s,clazz_id)
       values(seq_tb_student.nextval,'LMY',v_sex,23,'LMY@qq.com','g2','123',1);
       commit;
end qq;

在左边的procedure文件夹下的qq里右键点击（测试）

begin
  -- Call the procedure
  qq;
end;

或者直接执行

begin
  qq;
end;

执行就把上面的语句执行了，就在数据库添加了一条数据了。

 

-----------有参的存储过程

/********
当参数的存储过程，完成向tb_student添加一条数据
用户从注册页面填写信息，通过jdbc调用存储过程，插入数据，效率高
**************/
create or replace procedure aa
(
  v_name varchar2,
  v_sex varchar2,
  v_age number,
  v_email varchar2,
  v_address varchar2,
  v_iphone4s varchar2,
  v_clazz_id number
) 

is
begin
   insert into tb_student(id,name,sex,age,email,address,iphone4s,clazz_id)
   values(seq_tb_student.nextval,v_name,v_sex,v_age,v_email,v_address,v_iphone4s,v_clazz_id);
   commit;
end aa;

方法也是和上面一样，一种测试，一种直接执行

直接执行如下

begin
  --.....是参数
  aa(.........);
end;

/********
存储过程更多情况下是在数据库方做数据整合等复杂的工作
现在在开发银行系统，数据相当重要tb_student
需求：每天需要备份重要表的数据。
1、第一天插入了80万条记录
2、第二天插入了80万条记录
备份在每天晚上的12点之后
思路：
1、每天备份完数据之后，max(id)存到一张表
create table tb_maxid(id number)
2、下次备份的时候select max(id) 
3、select * from table where id>max（id）

步骤：
1、提取tb_student表所有数据
2、循环插入到tb_student_back表当中
3、完成之后记录max(id)到tb_maxid表

注意问题：
1、如果在循环中commit,效率低下
2、假设数据量很大（千万），oracle回滚段不够（有默认值）
  2.1 加大回滚段（如果不够的话）
  2.2 分段提交
**************/
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:=0;
  
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 into tb_student(id,name,sex,age,email,address,iphone4s,clazz_id)
    values(seq_tb_student.nextval,r_tb_student.name,r_tb_student.sex,r_tb_student.age,
　　　　　　r_tb_student.email,r_tb_student.address,r_tb_student.iphone4s,r_tb_student.clazz_id);
    
    --分段提交
    v_index :=v_index + 1;
    if(v_index = 2000) then
       commit;
       v_index :=0;
    end if;
  end loop;
  commit;--循环后再次提交数据
  --找到最大id
  select max(id) into v_max from tb_student_back;
  update tb_maxid set id = v_max;
  commit;
  
  
  --关闭游标
  close c_tb_student;
end backpro;

/***********
调用backpro的存储过程
************/
create or replace procedure invokebackpro 
is
  v_maxid number;
begin
  select id into v_maxid from tb_maxid;
  backpro(v_maxid);
end invokebackpro;

begin
  invokebackpro;
end;

最后就差定时了

dbms_jobs文件夹下右键。。。