三、Oracle 游标、存储过程、存储函数、触发器

    1.游标
        是用来擦做查询结果的结果集，相当于JDBC中的ResultSet;
        语法:
            cursor 游标名[(参数 参数类型)] is 查询结果集
        开发步骤：
            1)声明游标 
            2)打开游标
            3)从游标中获取数据  fetch 游标名 into 变量名
                                游标名%found :找到数据
                                游标名%notfound:没有找到数据
            4)关闭游标
        系统引用游标
            1)声明游标:游标名 sys_refcursor
            2)打开游标:open 游标名 for 结果集
            3)从游标中获取数据
            4)关闭游标
        例:
            --普通游标  输出员工表中所有员工的姓名和工资(不带参数游标)
            
                declare 
                    --1.声明游标
                    cursor vrows is select * from emp;
                    --声明一个变量用来记录所有数据
                    vrow emp%rowtype;
                begin
                    --2.打开游标
                    open vrows;
                    --3.循环取数据，从游标中获取数据
                    loop
                        fetch vrows into vrow;
                        exit when vrows%notfound;
                        dbms_output.put_line('姓名:'||vrow.ename||'    工资:'||vrow.sal);
                    end loop;    
                    --4.关闭游标
                    close vrows;
                
                end;
                
            --系统引用游标    输出员工表中所有员工的姓名和工资
            
                declare
                    vrows sys_refcursor;
                    vrow emp%rowtype;
                begin
                    open vrows for select * from emp;
                    loop
                        fetch vrows into vrow;
                        exit when vrows%noutfound;
                        dbms_output.put_line('姓名:'||vrow.ename||'    工资:'||vrow.sal);
                    end loop
                end;
            --扩展内容:使用for循环遍历游标
            
                declare
                    cursor vrows is select * from emp;
                begin
                    for vrow in vrows loop
                        dbms_output.put_line('姓名:'||vrow.ename||'    工资:'||vrow.sal);
                    end loop;
                end;
            --按照员工工作给所有员工涨工资,总裁涨1000,经理涨800,其他人涨400
                
                declare
                    cursor vrows is select * from emp;
                    vrow emp%rowtype
                begin
                    open vrows;
                    fetch vrows into vrow;
                    exit when vrows%notfound;
                    if vrow.job='president' then 
                        update emp set sal=sal+1000 where empno=vrow.empno;
                    elsif vrow.job='manager' then
                        update emp set sal=sal+800 where empno=vrow.empno;
                    else
                        update emp set sal=sal+400 where empno=vrow.empno;
                    end if;
                    close vrows;
                    commit;
                end;
    2.异常
        语法:
            exception
                when 异常1 then 
                    ...
                when 异常2 then 
                    ...
                when 异常3 then 
                    ...
                when orthers the
                    ...处理其他异常
        异常的常用类型
           zero_divide : 除零异常
           value_error : 类型转换异常
           too_many_rows : 查询出多行记录,但是赋值给了rowtype记录一行数据变量
           no_data_found : 没有找到数据
        例:
            declare
               vi number;
               vrow emp%rowtype;
            begin
               --vi := 8/0;  
               --vi := 'aaa';
               --select * into vrow from emp;
               select * into vrow from emp where empno=1234567;
            exception
              when zero_divide then
                dbms_output.put_line('发生了除零异常');
              when value_error then
                 dbms_output.put_line('发生了类型转换异常');
              when too_many_rows then
                dbms_output.put_line(' 查询出多行记录,但是赋值给了rowtype记录一行数据变量');
              when no_data_found then
                dbms_output.put_line('没有找到数据异常');
              when others then
                 dbms_output.put_line('发生了其它异常' || sqlerrm);     
            end;
        自定义异常:
            异常名 exception;
            raise 异常名;
        例:
            --查询指定编号的员工,如果没有找到,则抛出自定义的异常
            /*
                 游标来判断
                   %found %notfound
                声明一个游标
                声明一个变量,记录数据
                从游标中取记录
                   如果有,则不管它
                   如果没有就抛出自定义的异常
            */
            declare
              --声明游标
              cursor vrows is select * from emp where empno=8888;   
              --声明一个记录型变量
              vrow emp%rowtype;
              --声明一个自定义异常
              no_emp exception;  
            begin
              --1.打开游标
              open vrows;
              --2.取数据
              fetch vrows into vrow;
              --3.判断游标是否有数据
              if vrows%notfound then
                raise no_emp;
              end if;
              close vrows;
            exception
              when no_emp then
                dbms_output.put_line('发生了自定义的异常');
            end;
    3.存储过程
        实际上是封装在服务器上一段PLSQL代码片段，已经编译好的代码，客户端去调用存储过程，执行效率会非常高
        语法:
            create [or replace] procedure 存储过程的名称(参数名 in|out 参数类型,参数名 in|out 参数类型)
            is|as
            --声明部分
            begin 
            --业务逻辑
            end;
        例:
            --给指定员工涨薪,并打印涨薪前和涨薪后的工资
            /*
                参数1:in 员工编号
                参数2:in 涨薪数量
                
                声明一个变量:存储涨薪前的工资
                查询当前工资是多少
                打印涨薪前的工资
                更新工资
                打印涨薪后的工资
            */
            create or replace procedure proc_updatesal(vempno in number,vnum in number)
            is
               --声明变量.记录当前工资
               vsal number;    
            begin
              --查询当前的工资
              select sal into vsal from emp where empno = vempno;
              --输出涨薪前的工资
              dbms_output.put_line('涨薪前:'||vsal);
              --更新工资
              update emp set sal = vsal + vnum where empno = vempno;
              --输出涨薪后的工资
              dbms_output.put_line('涨薪后:'||(vsal+vnum));
              --提交
              commit;
            end;
            
            --调用存储过程方式1:
                call proc_updatesal(7788,10);

            --调用存储过程方式2: 用的最多的方式
                declare

                begin
                  proc_updatesal(7788,-100);
                end;
    4.存储函数
        实际上是一段封装是Oracle服务器中的一段PLSQL代码片断,它是已经编译好了的代码片段
        语法: 
            create [or replace] function 存储函数的名称(参数名 in|out 参数类型,参数名 in|out 参数类型) return 参数类型
            is | as
            
            begin
               
            end;
        存储过程和函数的区别:
            1.它们本质上没有区别
            2.函数存在的意义是给过程调用   存储过程里面调用存储函数
            3.函数可以在sql语句里面直接调用
            4.存储过程能实现的,存储函数也能实现,存储函数能实现的,过程也能实现
             
       默认是 in      
       --查询指定员工的年薪
        /*
            参数 : 员工的编号
            返回 : 年薪          
        */
        create or replace function func_getsal(vempno number) return number
        is
          --声明变量.保存年薪
          vtotalsal number;     
        begin
          select sal*12 + nvl(comm,0) into vtotalsal from emp where empno = vempno;
          return vtotalsal;
        end;

        --调用存储函数
        declare
          vsal number;
        begin
          vsal := func_getsal(7788);
          dbms_output.put_line(vsal);
        end;


        --查询员工的姓名,和他的年薪
        select ename,func_getsal(empno) from emp;
        --查询员工的姓名和部门的名称


        --查询指定员工的年薪--存储过程来实现
        --参数: 员工编号
        --输出: 年薪
        create or replace procedure proc_gettotalsal(vempno in number,vtotalsal out number)
        is
               
        begin
          select sal*12 + nvl(comm,0) into vtotalsal from emp where empno = vempno;
        end;


        declare
          vtotal number;
        begin
          proc_gettotalsal(7788,vtotal);
          dbms_output.put_line('年薪:'||vtotal);
        end;

        select *  from emp where empno = 8888; 
    5.触发器
        触发器: 当用户执行了 insert | update | delete 这些操作之后, 可以触发一系列其它的动作/业务逻辑
        作用 : 
            在动作执行之前或者之后,触发业务处理逻辑
            插入数据,做一些校验
                    
           语法:
               create [or replace] trigger 触发器的名称
               before | after
               insert | update | delete 
               on 表名
               [for each row] --有此句代表是行级触发器,可以影响多行,没有此句代表语句级触发器,只能影响一句
               declare
               
               begin
                 
               end;
           
           触发器的分类:
               语句级触发器:不管影响多少行, 都只会执行一次
               
               行级触发器:影响多少行,就触发多少次
                    :old  代表旧的记录, 更新前的记录
                    :new  代表的是新的记录
        --新员工入职之后,输出一句话: 欢迎加入黑马程序员
        create or replace trigger tri_test1
        after
        insert
        on emp
        declare

        begin
          dbms_output.put_line('欢迎加入黑马程序员');
        end;

        insert into emp(empno,ename) values(9527,'HUAAN');

        --数据校验, 星期六老板不在, 不能办理新员工入职
        --在插入数据之前
        --判断当前日期是否是周六
        --如果是周六,就不能插入
        create or replace trigger tri_test2
        before
        insert 
        on emp
        declare
         --声明变量
         vday varchar2(10);
        begin
          --查询当前
          select trim(to_char(sysdate,'day')) into vday from dual;
          --判断当前日期:
          if vday = 'saturday' then
             dbms_output.put_line('老板不在,不能办理入职');
             --抛出系统异常
             raise_application_error(-20001,'老板不在,不能办理入职');
          end if;
        end;

        insert into emp(empno,ename) values(9528,'HUAAN2');

        --更新所有的工资 输出一句话
        create or replace trigger tri_test3
        after
        update
        on emp 
        for each row
        declare

        begin
          dbms_output.put_line('更新了数据');
        end;

        update emp set sal = sal+10;


        --判断员工涨工资后的工资一定要大于涨工资前的工资
        /*
           200 --> 100
           触发器 : before
              旧的工资 
              新的工资
              如果旧的工资大于新的工资 , 抛出异常,不让它执行成功   
              
              
           触发器中不能提交事务,也不能回滚事务 
        */
        create or replace trigger tri_updatesal
        before
        update
        on emp
        for each row
        declare

        begin
          if :old.sal > :new.sal then
            raise_application_error(-20002,'旧的工资不能大于新的工资');
          end if;
        end;

        update emp set sal = sal + 10;
        select * from emp;

        update emp set sal = sal - 100;


        /*
           模拟mysql中ID的自增属性 auto_increment 
           insert into person(null,'张三');  
           
           触发器:
           
           pid=1  insert  pid=1
           
           序列 : create sequence seq_person_pid;       
        */
        create table person(
            pid number primary key,
            pname varchar2(20)   
        );

        insert into person values(null,'张三'); 

        create sequence seq_person_pid;

        --触发器
        create or replace trigger tri_add_person_pid
        before
        insert
        on person
        for each row
        declare

        begin
          dbms_output.put_line(:new.pname);
          --给新记录 pid 赋值
          select seq_person_pid.nextval into :new.pid from dual;
        end;

        insert into person values(null,'张三'); 


        select * from person;