八、PL/SQL游标的使用

1、概念：

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

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

2、对于不同的sql游标的使用情况不同：

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

3、游标属性：

%FOUND       布尔型属性，当最近一次读记录时成功返回,则值为TRUE；

%NOTFOUND   布尔型属性，与%FOUND相反；

%ISOPEN       布尔型属性，当游标已打开时返回TRUE；

%ROWCOUNT   数字型属性，返回已从游标中读取的记录数

4、游标的for循环语句：

PL/SQL语言提供了游标FOR循环语句，自动执行游标的OPEN、FETCH、CLOSE语句和循环语句的功能；

1)    当进入循环时，游标FOR循环语句自动打开游标，并提取第一行游标数据

2)    当程序处理完当前所提取的数据而进入下一次循环时，游标FOR循环语句自动提取下一行数据供程序处理

3)    当提取完结果集合中的所有数据行后结束循环，并自动关闭游标。

格式：

FOR index_variable IN cursor_name [value[, value]…] LOOP

--游标数据处理代码

END LOOP;

详解：

A、  index_variable为游标FOR 循环语句隐含声明的索引变量，该变量为记录变量，其结构与游标查询语句返回的结构集合的结构相同，在程序中可以通过引用该索引记录变量元素来读取所提取的游标数据，index_variable中各元素的名称与游标查询语句选择列表中所制定的列名相同。

B、  如果在游标查询语句的选择列表中存在计算列，则必须为这些计算列指定别名后才能通过游标FOR 循环语句中的索引变量来访问这些列数据

C、  不要在程序中对游标进行人工操作；不要在程序中定义用于控制FOR 循环的记

5、实例一到三为显示游标，实例四为隐式游标

|实例一|

使用游标：打印出 2班的所有的学生的工资和学号:salary: xxx

方法1：使用变量实现

declare

  v_sal student.sal %type;

  v_xuehao student.xuehao %type;

   --定义游标

   cursor student_cursor is select sal,xuehao from student where classid =2;

begin

  --打开游标

   open  student_cursor;

   --提取游标

   fetch student_cursor into v_sal, v_xuehao;

   while student_cursor %found loop

     dbms_output.put_line('xuehao:'||v_xuehao||'sal:'||v_sal);

   fetch student_cursor into v_sal,v_xuehao;

     end loop;

    --关闭游标  

   Close student_cursor;

end;

方法2：使用记录类型实现

declare

   type student_record is record

(

    v_sal student.sal%type,

    v_xuehao student.xuehao%type

   );

    v_student_record student_record;

   cursor student_cursor is select sal,xuehao from student where classid=2;

begin

    --打开游标

     open student_cursor;

   --提取游标

   fetch student_cursor into v_student_record;

while student_cursor%found loop

   dbms_output.put_line('xuehao:'||v_student_record.v_xuehao ||'sal:'||v_student_record.v_sal);

   fetch student_cursor into v_student_record;

   end loop;

    --关闭游标

close student_cursor;

end;

方法3：使用游标的for循环实现

declare

       cursor student_cursor is select sal,xuehao from student where classid=2;

begin

-- 特点： 自动开关游标 自动提取数据

       for c in student_cursor loop  

      dbms_output.put_line('xuehao:'||c.xuehao||' sal:'||c.sal);

      end loop;

end;

 

|实例二|

调整学生的工资:

工资范围           调整基数

 0 - 5000         5%

 5000 - 10000   3%

 10000 - 15000  2%

 15000 -           1%

方法1：使用游标的if elsif实现

declare 

  cursor student_sal_cursor is select xuehao,sal from student;

         v_temp number(4,2);

         v_xuehao student.xuehao%type;

       v_sal student.sal%type;

begin

   open student_sal_cursor;

         fetch student_sal_cursor into v_xuehao,v_sal;

   while student_sal_cursor%found loop

   if v_sal<5000 then v_temp:=0.05;

         elsif v_sal<10000 then v_temp:=0.03;

         elsif v_sal<15000 then v_temp:=0.02;

        else v_temp:=0.01; 

    end if;

    update student set sal=sal*(1+v_temp) where xuehao =v_xuehao;

fetch student_sal_cursor into v_xuehao,v_sal;

end loop;

close student_sal_cursor;

end;

备注：commit;提交后才能更新成功

方法2：使用sql

1)使用decode（）函数

update student set salary = salary * (1 + (decode(trunc(salary/5000), 0, 0.05,

                                                                        1, 0.03,

                                                                        2, 0.02,

                                                                        0.01)))

 

2)使用case when

update student

set salary=salary*(1+(case sal/5000 when 0 then 0.05 when 1 then 0.03 when 2 then 0.02 else 0.01 end))

方法3：使用游标的for循环来解决

declare 

       cursor student_sal_cursor is select xuehao,sal from student;  

       v_temp number(4,2);

begin

       for c in student_sal_cursor loop

      if c.sal<5000 then v_temp:=0.05;

      elsif c.sal<10000 then v_temp:=0.03;

      elsif c.sal<15000 then v_temp:=0.02;

      else v_temp:=0.01;

      end if;

      update student set sal=sal*(1+v_temp) where xuehao=c.xuehao;

end loop;

end;

commit;提交操作后该存储过程即生效。

|实例三|

带参数的游标

把工资限制在至少5000以上才能进行修改

declare

       cursor student_sal_cursor(v_classid number, v_sal number) is select xuehao,sal+1000 sal  from student where classid = v_classid and sal> v_sal;

     --定义基数变量

     temp number;

begin

       --处理游标的循环操作

       for c in student_sal_cursor(v_classid =>1,v_sal =>5000) loop 

      --判断员工的工资, 执行 update 操作

          if c.sal <= 5000 then

             temp :=0.05;

          elsif c.sal <= 10000 then

             temp :=0.03;  

          elsif c.sal <= 15000 then

             temp :=0.02;

          else

             temp := 0.01;

          end if;

         

          dbms_output.put_line( c.xuehao|| '的薪水: '||c.sal|| '增加率'||temp);

       --   update student set sal = sal * (1 + temp) where classid= c.classid;

    end loop;

end;

 

|实例四|

隐式游标

begin

         update student set sal = sal + 10 where xuehao= 19;

         if sql%notfound then

            dbms_output.put_line('查无此人!');

         end if;

end;

commit;提交成功后才会更新成功