Oracle学习基础（四）总结

曾经终败给现在 2022-02-24 02:26 267阅读 0赞

**复习：**

--使用Oracle来模拟ID的自增长
    
    --创建序列
    create sequence seq_test1;
    --创建表
    create table test2(
         tid number primary key,
         tname varchar2(10)
    );
    
    --tid自增长插入
    insert into test2 values(seq_test1.nextval,'张山');
    
    select * from test2;
    
    --删除表
    drop table test2;

/*
         PLSQL编程：过程语言，编写一些复杂业务逻辑。
         
         输出星号：
                 abs(x)+abs(y)<=m
                 
          vsal emp.sal%type;--引用型变量
          vrow emp%rowtype;--记录型变量
          
          select sal into vsal from emp where empno=7788;
    
    
    */

--使用abs（）函数输出菱形
    declare
    m number :=5;
    begin
      for y in -m..m loop
        for x in -m..m loop
          if abs(x)+abs(y)<=m then
            dbms_output.put('*');
            else
              dbms_output.put(' ');
            end if;
          end loop;
          
          dbms_output.new_line();--此处必须new_line让缓存输出，不然，循环的输出内容不会显示，会在缓存里的同一行。
        end loop;
    end;

**游标：用来操作查询结果集，相当于是JDBC的ResultSet  
    语法：cursor  游标名\[(参数名  参数类型)\]  is  查询结果集  
      
    开发步骤：  
             1.声明游标  
             2.打开游标   open  游标名  
             3.从游标中取数据：fetch 游标名 into 变量  
                         游标名%found  找到数据  
                         游标名%notfound 没有找到数据  
             4.关闭游标          close 游标名  
             **

**     系统引用游标：  
             1.声明游标：游标名 sys\_refcursor  
             2.打开游标：open 游标名 for 结果集  
             3.从游标中取值  
             4.关闭游标  
     for循环遍历游标：  
             不需要声明额外变量  
             不需要打开和关闭游标  
             **

--输出员工表的所有员工的姓名和工资（不带参数游标）
    /*
       游标：所有员工
       声明一个变量，用来记录每一行的数据  %rowtype
    */
    
    declare
       cursor crow is select * from scott.emp;
       vrow scott.emp%rowtype;
    begin
      open crow;
      loop
        fetch crow into vrow;
      exit when  crow%notfound;
         dbms_output.put_line('姓名：'||vrow.ename||'工资：'||vrow.sal);
         
      end loop;
      close crow;
    end;
    
    --先创建一个此用户下emp表，方便使用。
    create table emp as select * from scott.emp;
    select * from emp;
    
    --带参数游标的使用
    ----查询出指定员工的姓名和薪资
    declare
       cursor crow2(cno number) is select * from emp where empno=cno;
       vrow emp%rowtype;
    begin
       open crow2(7369);
       fetch crow2 into vrow;
       dbms_output.put_line('姓名：'||vrow.ename||'工资：'||vrow.sal);
       close crow2;
    end;
    
    --系统引用游标
    ----输出员工表中所有的员工姓名和工资
    declare
      --声明系统引用游标
      vrows sys_refcursor;
      vrow emp%rowtype;
    begin
      --1.打开游标
      open vrows for select * from emp;
      --2.取数据
        loop
          fetch vrows into vrow;
        exit when vrows%notfound;
          dbms_output.put_line('姓名：'||vrow.ename||'工资：'||vrow.sal);
        end loop;
      --3.关闭游标
      close vrows;
    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;
      loop
        fetch vrows into vrow;
          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;       
      exit when vrows%notfound;
      
      end loop;
      close vrows;
      commit;
    end;

--------------------

**  例外：（意外）程序运行过程发生异常。  
  语法：  
        declare  
          
        begin  
            
        exception  
          when 异常1 then  
               ....  
          when 异常2 then  
               ....  
          when others then  
              ..处理其他异常  
        end;**

**        常见异常：  
             zero\_divide:除零异常  
             value\_error:类型转换异常  
             too\_many\_rows:查询出多行记录，但是赋值给了rowtype记录一行数据变量。  
             no\_data\_found:没有找到数据  
               
             自定义异常：  
                      异常名  exception; --声明异常  
                      raise 异常名    ---抛出异常**

--测试练习各种异常
    declare
       vi number;
       vrow emp%rowtype;
    begin
      --vi:=8/0;--除零异常
      --vi:='xxx'; --类型转换异常
      --select * into vrow from emp; --too_many_rows 异常
      select * into vrow from emp where empno=1111; --没有找到数据的异常
    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('没有找到数据！');
    end;

--练习使用自定义异常
    
    --错误演示：此处抛出的是no_data_found异常，并不会抛出no_emp这个自定义的异常。
    --原因是执行' select * into vrow from emp where empno=8888;'时，已经抛出了异常，所以if抛出自定义异常是不执行的。
    declare
      --1.声明一个变量%rowtype
      vrow emp%rowtype;
      --2.声明一个自定义变量
      no_emp exception;
    begin
      select * into vrow from emp where empno=8888;
      if vrow.sal is null then
        raise no_emp;
      end if;
    
    exception
      when no_emp then
      dbms_output.put_line('输出自定义异常');
      when no_data_found then
       dbms_output.put_line('输出no_data_found异常');
    end;
    
    --正确演示：只能使用游标判断。
    declare
       vrow emp%rowtype;
       no_emp exception;
       cursor crows(ndo number) is select * from emp where empno=ndo;
    begin
       open crows(8888);
       fetch crows into vrow;
       if crows%notfound then
          raise no_emp;
       end if;
       close crows;
    exception
     when no_emp then
     dbms_output.put_line('输出自定义异常');
     when no_data_found then
     dbms_output.put_line('输出no_data_found异常');
    end;

**  存储过程：其实是封装在服务器上的一段PLSQL代码，已经编译好了。  
                 1.客户端调用存储过程，执行效率非常高  
              语法：  
                 create \[or replace\] procedure  存储过程的名称 （参数 in|out 参数类型，参数名 in|out 参数类型）  
                 is | as  
                  --声明部分  
                 begin  
                   --业务逻辑  
                 end;            **

--练习1：给指定员工涨薪，并打印涨薪前和涨薪后的工资。
    /*
       参数： in 员工编号
       参数： in 涨多少
       声明一个变量：记录原工资
       查询当前是多少
       打印原工资
       修改涨薪
       打印出涨薪后的工资
    
    */
    create or replace procedure pro_test1(eno in number,money in number)
    is
      vrow emp%rowtype;
    begin
      select * into vrow from emp where empno=eno;
      dbms_output.put_line('涨薪前：'||vrow.sal); 
      update emp set sal=sal+money where empno=eno;
      select * into vrow from emp where empno=eno;
      dbms_output.put_line('涨薪后：'||vrow.sal); 
      commit;
    end;
    
    --调用procedure的方式
    --1.方式一：
      call pro_test1(7788,10);
    --2.方式二：最常用
    declare
    
    begin
      pro_test1(7788,15);
    end;

--------------------

**存储函数： 封装在Oracle服务器上的一段PLSQL代码，它是编译好的代码  
    
        语法：  
            create \[or repalce\]  function 存储函数的名称（参数名  in|out 参数类型,参数名  in|out  参数类型） return 参数类型  
            is|as  
              
            begin  
                
            end;  
              
         存储过程和存储函数的区别：  
                   1.本质上没有什么区别  
                   2.存储函数存在意义是给过程调用，一般存储过程里面调用存储函数。  
                   3.存储函数可以在sql语句里面直接调用。  
                   4.存储过程能实现，存储函数也能实现，同时存储函数能实现的，存储过程也能实现。  
                   5.存储函数必须返回一个类型的数值。（return）  
                     
         默认参数类型是  in(输入参数)  不是out(输出参数)**

--根据员工id查询员工年薪（基础工资+奖金）
    create or replace function getMoenyById(eno number) return number
    is
     salyear number;
    begin
      select sal*12+nvl(comm,0) into salyear from emp where empno=eno;
      return salyear;
    end;
    
    --调用存储函数:
    declare
      vsal number;
    begin
      vsal:=getMoenyById(7788);
      dbms_output.put_line(vsal);
    end;

Java中调用存储过程的步骤：友情链接：[https://www.cnblogs.com/huhx/p/JavaProcedure.html][https_www.cnblogs.com_huhx_p_JavaProcedure.html]  
**  JAVA调用存储过程：  
       JDBC的开发步骤：  
            1.导入驱动包  
            2.注册驱动  
            3.获取链接  
            4.获取执行SQL的Statement  
            5.封装数据  
            6.执行SQL  
            7.获取结果  
            8.释放资源**

--------------------

** 触发器：当用户执行了  insert| update| delete 这些操作之后，引发一系列的其他的动作/业务逻辑  
            作用：  
                在动作执行之前后者之后，触发一系列的业务逻辑。  
                插入数据时，做一些校验  
            语法：  
                create \[or replace\] trigger 触发器名称  
                before | after  
                insert | update |delete  
                on 表名  
                \[for each row\]  
                declare  
                  
                begin  
                    
                end;  
                
            触发器的分类：  
                语句级触发器： 不管影响多少行，都只会执行一次。  
                  
                行级触发器：  影响多少行，就执行多少次。（for each row）  
                        ：old  代表旧的记录，更新前的记录  
                        ：new  代表新的记录**

--新员工入职后，输出：欢迎加入
    create or replace trigger tri_test1
    after
    insert
    on emp
    declare
    
    begin
      dbms_output.put_line('欢迎加入！');
    end;
    
    insert into emp(ename) values('张三硕士');
    
    --触发器的数据校验功能
    --星期六老板不在, 不能办理新员工入职
    --在插入数据之前
    --判断当前日期是否是周六
    --如果是周六,就不能插入
    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;

--------------------

**触发器的 :old 和:new 两个对象的使用  
   判断员工涨工资后的工资一定要大于涨工资前的工资  
   200 --> 100  
   触发器 : before  
      旧的工资  :old  
      新的工资  :new  
      如果旧的工资大于新的工资 , 抛出异常,不让它执行成功     
        
   触发器中不能提交事务,也不能回滚事务 **

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;

--------------------

** 模拟mysql中ID的自增属性 auto\_increment   
   insert into person(null,'张三');    
     
   触发器:  
     
   pid=1  insert  pid=1  
     
   序列 : create sequence seq\_person\_pid;       **

---让触发器在插入前取得序列的值，并插入新数据中。
    
    create table stu1(
          sid number,
          sname varchar2(10)
    );
    
    create  sequence seq_test2;
    
    create or replace trigger tri_rr
    before
    insert
    on stu1
    for each row
    declare
     
    begin
       dbms_output.put_line(:new.sname);
       select seq_test2.nextval into :new.sid from dual;
    end;
    
    insert into stu1 values(null,'张三ss');
    
    select * from stu1;

[https_www.cnblogs.com_huhx_p_JavaProcedure.html]: https://www.cnblogs.com/huhx/p/JavaProcedure.html