Oracle之PL/SQL学习笔记之游标(五)

in 编程
关注公众号【好便宜】( ID:haopianyi222 ),领红包啦~
阿里云,国内最大的云服务商,注册就送数千元优惠券:https://t.cn/AiQe5A0g
腾讯云,良心云,价格优惠: https://t.cn/AieHwwKl
搬瓦工,CN2 GIA 优质线路,搭梯子、海外建站推荐: https://t.cn/AieHwfX9

Oracle之PL/SQL学习笔记之游标(五)


        在PL/SQL程序中,对于处理多行记录的事务经常使用游标来实现


1. 游标的概念

        为了处理SQL语句,Oracle必须分配一片叫上下文(Context area)的区域来处理所必须的信息,其中包括要处理的行的数目,一个指向语句被分析以后的表示形式和指针以及查询的活动集(active set)。

        游标是一个指向上下文的句柄(handle)或指针。通过游标,PL/SQL可以控制上下文区和处理语句时上下文区会发生些什么事情。

       对于不同的SQL语句,游标的使用情况不同

SQL语句 游标
非查询语句 隐式的
结果是单行的查询语句 隐式的或显示的
结果是多行的查询语句 显示的


1.1 处理显示游标

 显示游标处理需要四个PL/SQL步骤:

            在指定数据类型时,不能使用长度约束。如NUMBER(4)、CHAR(10)等都是错误的。

            定义游标时,不能有into自居。

declare
  v_sal emp.sal%type;
  v_empno emp.empno%type;
  cursor emp_cursor is select sal,empno from emp where deptno=30;
begin
  open emp_cursor;--打开游标
  fetch emp_cursor into v_sal,v_empno;--提取游标
  while emp_cursor%found loop
     dbms_output.put_line('雇员编号:'||v_empno||',salary:'||v_sal);
     fetch emp_cursor into v_sal,v_empno;--提取游标
  end loop;
  close emp_cursor;--关闭游标
end;

   头脑风暴: 

    假如我们的游标查询很多个字段的值,那么fetch emp_cursor into v_1,v_2,....是不是很麻烦呢?想象我们的复合数据类型。


显示游标的属性

 

游标属性 值类型 说明
%FOUND 布尔型 当最近一次读取记录时成功返回,则值为true
%NOTFOUND 布尔型 与%FOUND相反
%ISOPEN 布尔型 当游标已打开时返回true
%ROWCOUNT 数字型 返回已从游标中读取的记录数


1.2 处理隐式游标

             隐式游标和显示游标有所差异,它虽然没有显示游标一样的可操作性,但是在实际的工作当中也经常用到。

1.2.1 隐式游标的特点

           每当允许SELECT或DML语句时,PL/SQL会打开一个隐式的游标。隐式游标不受用户的控制,这一点和显示游标有明显的不同。下面列出隐式游标和显示游标的不同处。

--隐式游标begin
---------------
--业务说明: 跟新指定员工编号员工的工资+11;如果存在该员工更新数据
-- 如果不存在,打印查无此人。
begin
  update emp set sal=sal+11 where empno=7788;
  if sql%notfound then 
    dbms_output.put_line('查无此人');
  end if;
---------------
--隐式游标end

     

   隐式游标的属性

属性名 值类型 说明
%ISOPEN true/false 该属性返回false,由Oracle自己控制
%FOUND true/false

此属性反应DML操作是否影响到了数据,SELECT INTO语句返回数据为true

%NOTFOUND true/false 与%FOUND相反
%ROWCOUNT number 该属性返回的是DML操作影响的行数。


2.  显示游标与循环控制


  案例:把雇员工资低于3000的调整到3000(loop)


-----------
--业务说明: 把雇员工资低于3000的调整到3000
-----------
declare
   v_sal emp.sal%type;--雇员工资,用于判断工资是否大于3000
   v_empno emp.empno%type;--雇员编号,用于更新工资
   cursor emp_cursor is select sal,empno from emp;--定义游标
begin
  --打开游标
  open emp_cursor;
  loop
    --获取数据
    fetch emp_cursor into v_sal,v_empno;
    exit when emp_cursor%notfound;
    if v_sal<3000 then 
      update emp set sal=3000 where empno=v_empno;
      dbms_output.put_line('雇员编号为:'||v_empno||'执行了更新操作');
      commit;
     end if;
  end loop;
  close emp_cursor;--关闭游标
end;


   我们可以对以上案例进行修改,一般情况下使用cursor时是与for循环配置实用的:(for)

declare
   type emp_sal_empbno_record is record(
        sal emp.sal%type,--雇员工资,这儿需要注意,多个字段,需要用,隔开
        empno emp.empno%type--雇员编号
   );
   cursor emp_cursor is select sal,empno from emp;--定义游标
begin
  --for循环自动打开游标,获取每行的数据,关闭游标
  for emp_sal_empbno_record in emp_cursor loop
    if emp_sal_empbno_record.sal<3000 then 
      update emp set sal=3000 where empno=emp_sal_empbno_record.empno;
      dbms_output.put_line('雇员编号为:'||emp_sal_empbno_record.empno||'执行了更新操作');
      commit;
     end if;
  end loop;
end;


    分析:   从上面的代码中可以看到,for循环中,没有open cursor,fetch cursor,close cursor   但是这些都确切的发生了。

    for循环帮我们在其内部处理了这些操作,简化了用户的操作,一般情况下for循环+cursor 堪称完美。(特殊情况除外),

   这儿还有一个需要注意,emp_sal_empbno_record 是记录类型,不是记录类型的变量。


   案例: 工资在0-3000 的加薪5%, 3000-4000 加薪3%,4000-5000 加薪2% 5000-n 加薪1%(while)


declare 
  type emp_record is record(
       sal emp.sal%type,
       empno emp.empno%type
  );--定义一个记录类型
  
  v_emp_record emp_record;--定义记录类型的变量
  v_temp number(4,2);--加薪比例
  cursor emp_cursor is select sal,empno from emp;--定义游标
  
begin
  open emp_cursor;--打开游标
  fetch emp_cursor into v_emp_record;--提取数据
  while emp_cursor%found loop
    if v_emp_record.sal<3000 then
      v_temp:=0.05;--需要注意赋值操作使用:=
    elsif v_emp_record.sal<4000 then
      v_temp:=0.03;
    elsif v_emp_record.sal<5000 then 
      v_temp:=0.02;
    else 
      v_temp:=0.01;
    end if; --记住 if语句结束后一定要有end if
    update emp set sal=sal*(1+v_temp) where empno=v_emp_record.empno;
    commit;   
    --提取数据,必须在while循环内部提取数据,不然就是死循环了
    fetch emp_cursor into v_emp_record;
  end loop;
end;

   

    注意: 两个案例,对应三种不同的循环处理游标,大家做下对比。


3 使用cursor for loop(简化迭代结果集)

      对上面的案例进行改进:

declare 
  v_temp number(4,2);--加薪比例
  cursor emp_cursor is select sal,empno from emp;--定义游标
begin
  --从游标中取数据,自动打开,关闭,判读是否还有数据
  for v_emp in emp_cursor
    loop
      if v_emp.sal<3000 then 
        v_temp:=0.005;
      elsif v_emp.sal<4000 then
        v_temp:=0.03;
      elsif v_emp.sal<5000 then
        v_temp:=0.02;
      else 
        v_temp:=0.01;
      end if;
      update emp set sal=sal*(1+v_temp) where empno=v_emp.empno;
      commit;
    end loop;
end;


4 使用BULK COLLECT 和FOR 语句的游标

      游标中通常使用fetch.... into ... 语句取数据,这种方式是单条数据提取,在数据量很大的情况下执行效率不是很理想。而FETCH ... BULK COLLECT INTO 语句可以批量提取数据,

在数据量大的情况下它的执行效率比单条提取数据高。

declare 
  cursor emp_cursor is select * from emp;
  type emp_tab is table of emp%rowtype;
  v_emp_tab emp_tab;
begin 
  open emp_cursor;--打开游标
  loop
    fetch emp_cursor bulk collect into v_emp_tab limit 5;--使用limit显示一次取得记录数
    for i in 1 .. v_emp_tab.count loop
        dbms_output.put_line('雇员编号:'||v_emp_tab(i).empno||',雇员姓名:'||v_emp_tab(i).ename);
    end loop;
    exit when emp_cursor%notfound;
  end loop;
end;

   fetch emp_cursor bulk collect into v_emp_tab limit 5; 一次取5条记录到集合中,减少了取的次数。


5.  带参数的游标

---------带参数的游标begin-------------
--业务说明: 查询出某部门中雇员名中包含C的员工的姓名
declare 
    c_ename emp.ename%type:='%C%';
    v_ename emp.ename%type;--雇员姓名
    cursor emp_cursor(
           name varchar2,--雇员名,模糊查询
           dtno number--部门编号
           ) is select ename from emp where ename like name and deptno=dtno;
begin
  open emp_cursor(c_ename,10);
  loop 
    fetch emp_cursor into v_ename;
    exit when emp_cursor%NOTFOUND;
    dbms_output.put_line(v_ename||'的名字包含C');
  end loop;
  close emp_cursor;
end;
---------带参数的游标end---------------


  注意: 申明游标变量是,参数的类型,不能使用长度限制:

         错误的写法: varchar2(50),number(7,2)



关注公众号【好便宜】( ID:haopianyi222 ),领红包啦~
阿里云,国内最大的云服务商,注册就送数千元优惠券:https://t.cn/AiQe5A0g
腾讯云,良心云,价格优惠: https://t.cn/AieHwwKl
搬瓦工,CN2 GIA 优质线路,搭梯子、海外建站推荐: https://t.cn/AieHwfX9
扫一扫关注公众号添加购物返利助手,领红包
Comments are closed.

推荐使用阿里云服务器

超多优惠券

服务器最低一折,一年不到100!

朕已阅去看看