Oracle游标

2019-05-13

游标(cursor)能够根据查询条件从数据表中提取一组记录,将其作为一个临时表置于数据缓冲区中,利用指针逐行对记录数据进行操作。

隐式游标

在执行SQL语句时,Oracle会自动创建隐式游标,该游标是内存中处理该语句的数据缓冲区,存储了执行SQL语句的结果。通过隐式游标属性可获知SQL语句的执行状态信息。

 %found:布尔型属性,如果sql语句至少影响到一行数据,值为true,否则为false。
 %notfound:布尔型属性,与%found相反。
 %rowcount:数字型属性,返回受sql影响的行数。
 %isopen:布尔型属性,当游标已经打开时返回true,游标关闭时则为false。

显式游标

用户可以显式定义游标。使用显式游标处理数据要4个步骤:定义游标、打开游标、提取游标数据和关闭游标。

1.定义游标

游标由游标名称和游标对应的select结果集组成。定义游标应该放在pl/sql程序块的声明部分。

语法格式:cursor 游标名称(参数) is 查询语句

2.打开游标

打开游标时,游标会将符合条件的记录送入数据缓冲区,并将指针指向第一条记录。

语法格式:open 游标名称(参数);

3.提取游标数据

将游标中的当前行数据赋给指定的变量或记录变量。

语法格式:fetch 游标名称 into 变量名;

4.关闭游标

游标一旦使用完毕,就应将其关闭,释放与游标相关联的资源。

语法格式:close 游标名称;

示例:编写游标,统计雇员表中指定部门的工资在2000以下、2000-5000、以及5000以上的职工人数

declare
     cursor emp_cursor( var_deptno in number) --定义游标
     is select * from scott.emp where deptno= var_deptno ;
     var_emp scott.emp%rowtype;
     var_deptno scott.emp.deptno%type;
     num1 int default 0;
     num2 int default 0;
     num3 int default 0;   
begin
var_deptno:=&var_deptno;
open emp_cursor(var_deptno); --打开游标
fetch emp_cursor into var_emp; --提取游标数据
     while emp_cursor%found loop  --判断是否有数据
         if(var_emp.sal<2000) then
            num1:=num1+1;
         elsif(var_emp.sal<=5000) then
            num2:=num2+1;
         else
            num3:=num3+1;
         end if;
         fetch emp_cursor into var_emp;  --提取游标数据
     end loop;
     close emp_cursor;  --使用完游标后必须显式关闭
     dbms_output.put_line('小于2000人数:' || num1);
     dbms_output.put_line('2000-5000人数:' || num2);
     dbms_output.put_line('大于5000人数:' || num3);
end;

游标FOR循环

PL/SQL提供了游标for循环语句,是遍历显式游标的一种快捷方式。

特点:
 当for循环开始时,游标会自动打开(不需要使用open方法)。
 每循环一次系统自动读取游标当前行的数据(不需要使用fetch)。
 当退出for循环时,游标被自动关闭(不需要使用close)。

for 变量 in 游标名称 loop 
          语句块 ; 
end loop ;
declare
    cursor emp_cursor( var_deptno in number) --定义游标
    is select * from emp where deptno= var_deptno ;
    var_deptno scott.emp.deptno%type;
    num1 int default 0;
    num2 int default 0;
    num3 int default 0;   
begin
    var_deptno:=&var_deptno;
    for var_emp in emp_cursor(var_deptno) loop
         if(var_emp.sal<2000) then
            num1:=num1+1;
         elsif(var_emp.sal<=5000) then
            num2:=num2+1;
         else
            num3:=num3+1;
         end if;
     end loop;
     dbms_output.put_line('小于2000人数:' || num1);
     dbms_output.put_line('2000-5000人数:' || num2);
     dbms_output.put_line('大于5000人数:' || num3);
end;

使用游标更新记录

cursor 游标名称 is 查询语句 for update;

示例:更新雇员工资 规则:1000以内增加300;2000以内增加200;其它增加100。

declare
     cursor emp_cursor is select * from scott.emp for update;
begin
    for var_emp in emp_cursor loop
        if var_emp.sal <= 1000 then
             update scott.emp set sal = var_emp.sal+300 
            where current of emp_cursor;//游标的当前行
        elsif var_emp.sal<=2000 then
             update scott.emp set sal = var_emp.sal+200 
            where current of emp_cursor;
        else
             update scott.emp set sal = var_emp.sal+100 
            where current of emp_cursor;
        end if; 
    end loop;
    commit;
end;

推荐阅读更多精彩内容

  • ORA-00001: 违反唯一约束条件 (.) 错误说明:当在唯一索引所对应的列上键入重复值时,会触发此异常。 O...
    我想起个好名字阅读 1,269评论 0 6
  • 一、Python简介和环境搭建以及pip的安装 4课时实验课主要内容 【Python简介】: Python 是一个...
    _小老虎_阅读 1,625评论 0 1
  • 前言 厚积而薄发。 在 PL/SQL 程序中,对于处理多行记录的事务经常使用游标来实现。 游标的概念 --为了处理...
    olaH阅读 228评论 0 4
  • 游标概念 由select语句返回的结果集包括满足该语句的where子句中条件的所有行。但是有时候应用程序并不总能将...
    不知名的蛋挞阅读 569评论 0 6
  • Andy是我的学长,大我三届。我们刚在一起时,他很开心,时常牵着我的手,一起上自习时稍微一点动静,他都会有意识地紧...
    Ellasaid阅读 49评论 0 2