oracle 游标 定义,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;