• Oracle数据库游标,序列,存储过程,存储函数,触发器


    游标的概念: 
        游标是SQL的一个内存工作区,由系统或用户以变量的形式定义。游标的作用就是用于临时存储从数据库中提取的数据块。在某些情况下,需要把数据从存放在磁盘的表中调到计算机内存中进行处理,最后将处理结果显示出来或最终写回数据库。这样数据处理的速度才会提高,否则频繁的磁盘数据交换会降低效率。 
    游标有两种类型:显式游标和隐式游标。在前述程序中用到的SELECT...INTO...查询语句,一次只能从数据库中提取一行数据,对于这种形式的查询和DML操作,系统都会使用一个隐式游标。但是如果要提取多行数据,就要由程序员定义一个显式游标,并通过与游标有关的语句进行处理。显式游标对应一个返回结果为多行多列的SELECT语句。 
    游标一旦打开,数据就从数据库中传送到游标变量中,然后应用程序再从游标变量中分解出需要的数据,并进行处理。 
    隐式游标 
    如前所述,DML操作和单行SELECT语句会使用隐式游标,它们是: 
    * 插入操作:INSERT。 
    * 更新操作:UPDATE。 
    * 删除操作:DELETE。 
    * 单行查询操作:SELECT ... INTO ...。 
    当系统使用一个隐式游标时,可以通过隐式游标的属性来了解操作的状态和结果,进而控制程序的流程。隐式游标可以使用名字SQL来访问,但要注意,通过SQL游标名总是只能访问前一个DML操作或单行SELECT操作的游标属性。所以通常在刚刚执行完操作之后,立即使用SQL游标名来访

    动手敲o~~~

    /*
    游标:是用来操作查询结果集,相当于是JDBC中的ResultSet
        语法:cursor 游标名[(参数名 参数类型)] is 查询结果集
        开发步骤:
           1.声明游标
           2.打开游标
           3.从游标中取数据 fetch 游标名 into 变量
                游标名%found :找到数据
                游标名%notfound :没找到数据
           4.关闭游标  close 游标名     
           
        系统引用游标
               1. 声明游标 : 游标名 sys_refcursor
               2. 打开游标: open 游标名 for 结果集
               3. 从游标中取数据
               4. 关闭游标
                    
         for循环遍历游标:
               不需要声明额外变量
               不需要打开游标
               不需要关闭游标          
    */
    --输出员工表中所有的员工姓名和工资(不带参数游标)
    /*
    游标:所有员工
    声明一个变量,用来记录一行数据  %rowtype
    */
    declare
        --游标
        cursor vrows is select * from emp;
        --声明变量,记录一行数据
        vrow emp%rowtype;
    begin
      --1.打开游标
      open vrows;
      --2.从游标提取数据
      --循环取数据
      loop
      fetch vrows into vrow ;
      exit when vrows%notfound;
      dbms_output.put_line('姓名:'||vrow.ename ||' 工资: ' || vrow.sal);
      end loop;
      --3.关闭游标
      close vrows;
    end;  
    
    --输出指定部门下的员工姓名和工资   带参数
    /*
    游标:指定部门所有的员工
    声明一个变量记录一行数据
    */
    declare 
      cursor vrows(dno number) is  select * from emp where deptno = dno;
      --声明变量
      vrow emp%rowtype;
    begin
      --1.打开游标,指定10号部门
      open vrows(10);
      --2.循环遍历,取数据
      loop
         fetch vrows into vrow;
         exit when vrows%notfound;
         dbms_output.put_line('姓名:'||vrow.ename ||' 工资: ' || vrow.sal);
      end loop;
      close vrows;    
    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;
       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 || '工作:'|| vrow.job);
      end loop;
    end;
    
    --按照员工工作给所有员工涨工资,总裁涨1000,经理涨800,其他人涨400
    /*
      游标所有员工
      声明一个记录一行数据
    */
    declare
      cursor vrows is select * from emp;
      vrow emp%rowtype;
    begin
      open vrows;
      --循环
      loop
         --取数据
         fetch vrows into vrow;
         --退出条件
         exit when vrows%notfound ;
         --根据不同的职位,涨工资 总裁涨1000,经理涨800,其他人涨400
         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;   
      end loop;        
      --关闭游标
      close vrows;
      --提交事务
      commit;
    end;
    
    
    /*
      例外:(意外)程序运行的过程发生异常,相当于是JAVA中的异常
       
       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 := '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;
    
    --查询指定编号的员工,如果没有找到,则抛出自定义的异常
    /*
         --错误的演示
         
         1.声明一个变量 %rowtype
         2.查询员工信息,保存起来
         3.判断员工信息是否为空
         4. 如果是 则抛出异常
    */
    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 others then
         dbms_output.put_line('输出了其它异常'||sqlerrm);  
    end;
    
    --查询指定编号的员工,如果没有找到,则抛出自定义的异常
    /*
         游标来判断
           %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;
    
    /*
        
        存储过程: 实际上是封装在服务器上一段PLSQL代码片断,已经编译好了的代码
                  1.客户端取调用存储过程,执行效率就会非常高效
             语法:
                  create [or replace] procedure 存储过程的名称(参数名 in|out 参数类型,参数名 in|out 参数类型)
                  is | as
                   --声明部分
                  begin
                   --业务逻辑 
                  end; 
    */
    --给指定员工涨薪,并打印涨薪前和涨薪后的工资
    /*
        参数 : in 员工编号
        参数 : 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; 
    
    select * from emp;
    --方式1
    call proc_updatesal(7788,10);
    
    declare
    
    begin
      proc_updatesal(7788,-10);
    end;
    
    /*
      存储函数: 实际上是一段封装是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 proce_getsal(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
      proce_getsal(7788,vtotal);
       dbms_output.put_line('年薪:'||vtotal);
    end;
    
    
    /*
        JAVA调用存储过程
           JDBC的开发步骤:
              1.导入驱动包
              2.注册驱动
              3.获取连接
              4.获取执行SQL的statement
              5.封装参数
              6.执行SQL
              7.获取结果
              8.释放资源   
    */
    
    /*
       封装一个存储过程 : 输出所有表中的记录
       
       输出类型 : 游标  
    */
    create or replace procedure proc_getemps(vrows out sys_refcursor)
    is
    
    begin
      --1.打开游标, 给游标赋值
      open vrows for select * from emp;
    end;
    
    /*
       触发器: 当用户执行了 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,'dashao');
    
    --数据校验, 星期六老板不在, 不能办理新员工入职
    --在插入数据之前
    --判断当前日期是否是周六
    --如果是周六,就不能插入
    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,'dashao2');
    
    --更新所有的工资 输出一句话
    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;
  • 相关阅读:
    css基础
    什么是css
    js写guess网页
    JavaScript_day02
    html基础知识
    JavaScript_day01
    由一个瀑布流导出对滚动条滚动距离,可视区尺寸,元素尺寸的内容的知识点
    一个页面从输入URL到页面加载显示完成,这个过程中发生了什么?
    http常用状态吗以及分别代表什么意思?
    cookies,sessionStorage和localStorage的相同点和不同点?
  • 原文地址:https://www.cnblogs.com/Young111/p/10589989.html
Copyright © 2020-2023  润新知