oracle中的游标,例外,存储过程,存储函数和触发器

 这一部分主要是讲plsql里面的游标,例外,存储过程存储函数和触发器的使用.以及我们在java程序中如何用jdbc链接oracle数据库,执行里面的plsql块.实际上我们有了一门语言的基础再去学习其他的语言会有那么一种feel的,想要实现的目的相同只是表达方式不同或许方式还是相同的. 

 

在开始之前先了解里面的if判断语句,和循环语句
  --if条件判断 

 declare
 v_age number:=22;
 v_result varchar2(30):= v_age ||':';
 begin
   if   v_age < 18 then
     dbms_output.put_line(v_result||'未成年!');
     elsif v_age >= 18 and  v_age <27 then       --注意这里的elsif 是没有e的,而不是我们java里面else if
        dbms_output.put_line(v_result||'青年了!');
       else
         dbms_output.put_line(v_result||'老年了!');
     end if;
 end;
 
 
 --循环语句,三种
 --1 ,for循环语句
 declare 
 begin
   for  n in 1..10 loop
       dbms_output.put_line(n||'!!!');  -- 1.2.3.....10
       end loop;
   end;

   --in reverse 就是倒序循环,但是仍然是1..10.这个是不需要改的
   declare 
 begin
   for  n in reverse 1..10 loop
       dbms_output.put_line(n||'!!!'); -- 10.9.8.......1
       end loop;
   end;
   --2, while 循环语句
   declare
   v_num number:=0;
   begin
     while v_num <10  loop
       v_num:=v_num+1;--while循环中必须有一个条件让其结束,否则会死循环
        dbms_output.put_line(v_num||'!!');
        end loop;
     end;
     --3,  过程控制循环,类似while
  declare 
  v_num number:=1;
  begin
  loop             -- 开始循环
    v_num:=v_num+1;
     dbms_output.put_line(v_num||'!!!');
     exit when v_num >10;            --满足条件时候退出循环
    end loop;      
  end;
  
  --利用循环打印一个菱形
  declare
  v_size number:=4;
  begin
     for x in -v_size..v_size  loop       --两层for 循环
       for y in -v_size..v_size  loop
       
       if abs(X) + abs(y)<=v_size then  --这里拆分的就是 满足条件,  -4<= x+y <=4  |  并且 -4<=|x-y|<=4
        dbms_output.put('*');
        else 
         dbms_output.put('');
         end if;
        end loop;
         dbms_output.put_line(' ');
      end loop;
    end;
    
    --游标 有不带参数的游标, 带参数的游标 和系统指定游标类型三种
    
    --不带参数
    declare
       cursor mycursor is  select * from zsemp;--声明游标,并定结果集
        rw zsemp%rowtype; --声明了一个变量来接收游标里面的数据,这里是行类型,当然也可以是具体类型
    begin
      open mycursor;
      loop 
      fetch mycursor into rw;--循环赋值给rw
       dbms_output.put_line(rw.ename||rw.sal);
      exit when mycursor%notfound; --当游标是找不到时候就停止循环,停止找
      end loop;
    
      close mycursor;
      end;
      
--携带参数的游标 查询部门是10的部门的员工姓名和职位
declare
cursor dept_c (dno number) is 
select * from zsemp where deptno=dno;


rw zsemp%rowtype;
begin
  open dept_c(10);--打开游标的时候给它里面的参数赋值
  loop
  fetch dept_c into rw;  
  dbms_output.put_line('员工姓名'||rw.ename||'员工的职位'||rw.job);
    exit when dept_c%notfound;
    end loop;
    close dept_c;
  end;
  
  --系统指定的游标类型  输出所有员工工资(系统指定的游标不能够携带参数!)
  declare 
  mycursor sys_refcursor;
  rw zsemp%rowtype;
  begin
    open mycursor for select * from zsemp;--打开游标,并给参数赋值
      loop
      fetch mycursor into  rw; 
    dbms_output.put_line(rw.ename||'工资'||rw.sal);
      exit when mycursor%notfound;
      end loop;
      close mycursor;
    end;
    
    --例外 plsql中的例外就是我们java中的异常, 系统给定的异常如下几个,当然我们也可以自定义异常
   /*no_ data_found;   --指定资源找不到例外
   too_many_rows;--有多行值,但是赋值给了一行例外
   zero_divide; --除零例外
   value_error;值类型转换例外 */
   
   --使用系统指定异常
   declare
   vsal number;
   rw zsemp%rowtype;
   begin
   -- vsal:=10/0;
    --select deptno into vsal from zsemp where empno=1001000;
    --vsal:='韩梅梅';
    select * into rw from zsemp ; 
    exception
      when zero_divide then
        dbms_output.put_line('出现除零例外');
     when no_data_found then
        dbms_output.put_line('资源找不到例外');
       when value_error then
        dbms_output.put_line('类型转换例外');
         when too_many_rows then
        dbms_output.put_line('太多行例外');
        when others then
           dbms_output.put_line('出现其他未知例外');
          
     end;
     
     --使用自定义异常,查询指定编号的员工,找不到则抛出自定义异常
     declare
     no_emp_found exception;
  cursor  emp_c(dno number) is select distinct * from zsemp where empno=dno;
   rw zsemp%rowtype;
     begin
       open emp_c(1001011);
      
       fetch emp_c into rw;
       dbms_output.put_line(rw.ename);
       
      if emp_c%notfound then--我们用游标来判断,如果游标没有找到数据就抛出自定义例外
       raise no_emp_found;
       end if;
       close emp_c;--以上的步骤就将关闭游标和抛出例外整合了
       
       exception 
        when no_emp_found then 
           dbms_output.put_line('没有找到指定的资源');
       end;


--存储过程  实际上是将一段已经编译好的plsql代码片段封装在oracle里面,避免了在程序中去写这些代码
   
   --写一个存储过程,完成给指定员工涨薪,并打印涨薪前和涨薪后的工资(注意在这里不需要写declare,)


   create or replace procedure addSal(epno in number ,newsal in number) 
   is 
   oldSal number;
   begin
     select sal into oldSal from zsemp where empno = epno;
     dbms_output.put_line('涨前'||oldSal);
     update zsemp set sal =sal+newsal where empno=epno;
     dbms_output.put_line('涨后'||(oldSal+newsal));
     commit;
     exception
       when no_data_found then 
         dbms_output.put_line('资源找不到错误');
   end;
   --来调用存储过程,第一种方法调用
   call addSal(7369,50);
select * from zsemp;

--第二种方法调用
declare
begin
 addSal(8888,50);  
 end;
--存储函数   实际上将一段已经编译好的PLSQL代码片断封装在Oracle数据库,与存储过程本质上是一样的,只是这个有返回值
--我们用存储函数来完成上面同样的功能
create or replace function addSalfunc(epno in number ,newsal in number) return number 
is 
oldSal number;


begin
  select sal into oldSal from zsemp where empno=epno;
  return oldSal;
  dbms_output.put_line('涨前'||oldSal);
  update zsemp set sal =sal+newsal where empno=epno;
    dbms_output.put_line('涨后'||(oldSal+newsal));
   commit;
  end;
  
  declare
  vsal number;
  begin
   vsal:= addSalfunc(8888,50);
   dbms_output.put_line(vsal);
    end;
  --查询指定员工的年薪  用存储过程
  create or replace procedure valyear(epno number) is
  totalval number ;
  begin
    select (sal*12+nvl(comm,0)) into totalval from zsemp where empno=epno;
       dbms_output.put_line('年薪'||totalval);
    exception 
      when no_data_found then
        dbms_output.put_line('资源找不到例外');
        when others then 
                   dbms_output.put_line('其他未知 的例外');


    end;
   call valyear(8888);--
   
    --查询指定员工的年薪  用存储函数
    create or replace function valyearfunc(epno number)  return number is
    totalval number;
    begin
      select (sal*12 +nvl(comm,0)) into totalval from zsemp where empno=epno;
      return totalval;
      exception
         when no_data_found then
        dbms_output.put_line('资源找不到例外');
        when others then 
                   dbms_output.put_line('其他未知 的例外');
      end;
      
      declare
      vsal number;
      begin
     vsal:=valyearfunc(8888);
     dbms_output.put_line('总计'||vsal);
        end;
 --调用输出类型为游标类型的存储过程
      create or replace procedure myprocedure (vemp_c out sys_refcursor) 
      is 
      begin
       open vemp_c for  select * from zsemp; --在这里开启,之后就是他自己去关闭,不用手动调用
        end;



      


      
--触发器  我们在执行一行或者某个方法时候触发执行的一段代码逻辑;一般是指,insert update delete 等操时前或者后触发的事件
   --分为行级触发器和语句级触发器 作用是校验数据,监听表的变化  /比如在添加数据成功后提示,添加成功.在指定时间段限制操作等等,或者对传入的数据进行校验
   
   
   --在插入数据后提示插入数据成功(行级触发器)
   create or replace trigger t1 
   after insert on zsemp 
    for each row
  declare
  begin 
    dbms_output.put_line('添加新员工成功!');
   end;
   insert into zsemp values (6666,'付宗强','coder',null,to_date('2017-12-12','yyyy-MM-dd'),10000,500,30);
   select *from zsemp;
   
   --周末时间不予许修改数据库内容(语句级触发器)
   create or replace trigger t2 
   before insert or update on zsemp 
   declare
   currentday varchar2(20);

   begin
     select trim(to_char(sysdate,'day')) into currentday from dual;
     if  currentday in ('sunday','saturday') then 
          raise_application_error('-20002','周末事件不允许办理入职和更新操作');--注意.applcation_error里面的错误范围在-20000 ----30000不包含两头
          end if;
    end;
    insert into zsemp values (7777,'明明','coder',null,to_date('2017-12-12','yyyy-MM-dd'),10000,500,30);
    
    --触发器之校验数据: 校验涨工资涨后不能比涨前少!(:old  :new )
    create or replace trigger t3 
    before update on zsemp for each row
  
 declare
 begin
   if :old.sal>:new.sal then 
     raise_application_error(-20003,'增加工资后比原有工资还少?!?!');
     end if;
  end;
  --测试涨工资
  update zsemp set sal=sal-200 where empno =6666;
  
  --利用触发器在往数据库中添加数据之前自动生成序列的id
  select * from zsemp2;
  alter table zsemp2 add eid number;
  
  create sequence se1; --创建默认的sequence
  create or replace trigger t4 
 before insert on zsemp2 for each row
 
 declare
 begin
    select se1.nextval into :new.eid from dual;-给表添加一列作为id,将序列里面的每个数字添加到id列
   end;
  insert into zsemp2 (empno,sal) values(7789,1350);