游标

游标

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

游标是一个指向上下文的句柄或指针

显式游标处理

1. 定义游标：定义一个游标名，以及相对应的select语句 (在declare中定义)

   cursor name_cursor (参数名 类型) is select....;

   在带有参数的游标中，指定参数数据类型时，不能使用长度约束 (即name number或name varchar2 )

2. 打开游标：就是执行游标所对应的select语句,将查询结果放入工作区，并且指针指向工作区的首部，标识游标结果集合。

   open name_cursor (参数 => 值);

3. 提取游标数据：就是检索结果集合中的数据行，放入指定的输出变量中

   fetch name_cursor into 变量;

4. 关闭游标

   close name_cursor ;

游标属性

%found 布尔值属性，当最近一次读记录时成功返回，则值为true

%notfound 布尔值属性，与%found相反

%isopen 布尔值属性，当游标打开是返回true

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

无参游标

declare
--定义游标
cursor test_cursor is select employee_id,salary from emp where rownum<=10 order by salary;
--向表中取出前十条数据，再根据salary排序
v_id emp.employee_id%type;
v_sal emp.salary%type;
begin
--打开游标
open test_cursor;

--提取游标数据
fetch test_cursor into v_id,v_sal;

while test_cursor%found loop
  if v_sal <6000 then 
    update emp set salary = 6000 where employee_id=v_id;
  end if;
  fetch test_cursor into v_id,v_sal;
end loop;

--关闭游标
close test_cursor;  

end;

有参游标

declare 
cursor test_cursor(emp_count number default 10) is select employee_id,salary from emp where rownum<=emp_count order by salary;
v_id emp.employee_id%type;
v_sal emp.salary%type;
begin
  open test_cursor(emp_count => 20);
  fetch test_cursor into v_id,v_sal;
  while test_cursor%found loop
    if v_sal<6000 then
       update emp set salary = 6000 where employee_id=v_id; 
    end if;
    fetch test_cursor into v_id,v_sal;
  end loop;
  close test_cursor;
end;

游标的for循环

PL/SQL语言提供了游标的for循环语句，自动执行游标的open,fetch,close以及循环语句的功能。

当进入循环时，游标for循环语句自动打开游标，并提取第一行游标数据，当程序处理完当前所提取的数据而进入下一行时，游标for循环语句自动提取下一行数据供程序处理，当提取完结果集中的所有数据时后结束循环，并自动关闭游标。

for index_variable in 游标名 loop

end loop;

其中index_variable 为游标for循环语句隐含声明的索引变量，该变量为记录变量 ，其机构与游标查询语句返回的集合相同 。在程序中可以通过该索引记录变量元素来读取所提取的游标数据，index_variable 中各元素名称与游标查询语句中个列的名称(别名)相同。如在查询语句中存在计算列，则必须通过别名来访问。

declare
cursor emp_cursor is select last_name as ename,salary as sal from employees;

begin
  for temp in  emp_cursor loop
    --temp为记录类型
    dbms_output.put_line(temp.ename||' --- '||temp.sal);
  end loop;

end;

declare 
cursor emp_cursor(deptno number default 50) is select last_name ename,salary sal from employees where department_id=deptno;
begin
  for temp in emp_cursor(80) loop
    dbms_output.put_line(temp.ename||' --- '||temp.sal);
  end loop;
end;

使用子查询来实现游标功能

declare
v_deptno employees.department_id%type;
begin
  v_deptno := 80;
  for temp in (select last_name ename,salary sal from employees where department_id=v_deptno) loop
    dbms_output.put_line(temp.ename||' --- '||temp.sal);
  end loop;
end;

declare
v_deptno employees.employee_id%type := &input_deptno;
--通过对话框输入值
begin
  for temp in (select last_name ename,salary sal from employees where department_id=v_deptno)loop
    dbms_output.put_line(temp.ename||' --- '||temp.sal);
  end loop;
end;

隐式游标

显式游标主要是用于对查询语句的处理，尤其是在查询结果为多行记录的情况下。对于非查询语句，如修改，删除操作，则由ORACLE系统自动为这些操设置游标并创建其工作区，这些由系统隐含创建的游标称为隐式游标，其名字为SQL，这是由ORACLE系统定义的。

对于隐式游标的操作，如定义，打开，提取及关闭操作，都有ORACLE系统自动完成，无需用户进行处理。用户只能通过隐式游标的相关属性，来完成相应操作。

隐式游标属性
SQL%FOUND 布尔型属性,当最近一次读记录时成功返回，则值为 TRUE
SQL%NOTFOUND 布尔型属性,与%FOUND 相反
SQL %ROWCOUNT 数字型属性, 返回已从游标中读取得记录数
SQL %ISOPEN 布尔型属性, 取值总是 FALSE。SQL 命令执行完毕立即关闭隐式游标。

declare 
v_name emp.last_name%type := 'xxxx';
v_id emp.employee_id%type := &employee_id;
begin
  update emp set last_name=v_name where employee_id = v_id;
  if sql%notfound then
    dbms_output.put_line('无此用户');
  end if;
end;

%NOTFOUND,SQL%NOTFOUND与NO_DATA_FOUND区别

• 当一个显式游标(select语句)的where字句未找到时触发%NOTFOUNF。在提取循环中要用%NOTFOUND、%FOUND来确定循环的退出条件
• 当一个隐式游标(update或delete语句)的where字句未找到时触发SQL%NOTFOUND
• select … into 语句触发NO_DATA_FOUND。存储过程中经常出现

游标的修改和删除操作

游标的修改和删除操作是指在游标定位下，修改或删除表中指定的数据行。这时，要求游标查询语句中必须使用for update选项。

为了对正在处理(查询)的行不被另一个用户改动，ORACLE提供了一个for update字句来对所选择的行进行锁住。防止其他事务处理更新或删除相同的行，直到您的事务处理提交或回退后为止。

语法： select … from … for update (of 指定列) (nowait)

如果另一个会话已对活动集中的行加了锁，那么 SELECT FOR UPDATE 操作一直等待到其它的会话释放这些锁后才继续自己的操作，对于这种情况，当加上 NOWAIT 子句时，如果这些行真的被另一个会话锁定，则 OPEN 立即返回并给出：ORA-0054 ：resource busy and acquire with nowait specified。

where current of name_cursor

如果使用for update声明游标，则可在delete和update语句中使用 where current of 游标名 来表示游标结果集中当前行对应的数据库表中的数据行。

declare
v_deptno emp.department_id%type := &deptno;
cursor emp_cursor is select last_name,salary from emp where department_id=v_deptno for update;
begin
  for emp_record in emp_cursor(v_deptno) loop
    if emp_record.salary<3333 then
      update emp set salary=3333 where current of emp_cursor;
    end if;
  end loop;
end;