游标

游标简介

逐行处理查询结果，以编程的方式访问数据
游标的类型：

隐式游标

在PL/SQL中使用DML语句时自动创建隐式游标
隐式游标自动声明、打开和关闭，其名为 SQL
通过检查隐式游标的属性可以获得最近执行的DML 语句的信息
隐式游标的属性有：
%FOUND – SQL 语句影响了一行或多行时为 TRUE
%NOTFOUND – SQL 语句没有影响任何行时为TRUE
%ROWCOUNT – SQL
1. 用在select后，在执行一次fetch后指向下一条数据。返回第几条数据的下标
2. update和delete直接返回删除了多少行
%ISOPEN - 游标是否打开，始终为FALSE

SQL> SET SERVEROUTPUT ON
SQL> BEGIN
	UPDATE toys SET toyprice=270
	WHERE toyid= 'P005';
	IF SQL%FOUND THEN --只有在 DML 语句影响一行或多行时，FOUND才返回 True
		DBMS_OUTPUT.PUT_LINE(‘表已更新');
	END IF;
     END;
	/

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  	v_TOYID TOYS.ID%type := '&TOYID';
  	v_TOYNAME TOYS.NAME%Type := '&TOYNAME';
     BEGIN
  	UPDATE TOYS SET NAME = v_TOYNAME
  	WHERE toyid=v_TOYID;
  	IF SQL%NOTFOUND THEN --**如果 DML 语句不影响任何行，NOTFOUND则返回 True** 

    		DBMS_OUTPUT.PUT_LINE('编号未找到。');
 	ELSE
		DBMS_OUTPUT.PUT_LINE(‘表已更新');
	END IF;
     END;

SQL> SET SERVEROUTPUT ON 
SQL> BEGIN
	UPDATE vendor_master
	SET venname= 'Rob Mathew'
	WHERE vencode='V004';
	DBMS_OUTPUT.PUT_LINE (SQL%ROWCOUNT);--返回 DML 语句影响的行数
     END;

利用隐式游标解决NO_DATA_FOUND异常

搜索不存在的员工id
没有用到游标之前用异常抛出

--根据员工编号查员工姓名
declare
emp_name emp.first_name%type;
emp_no emp.employee_id%type :=&员工id;
begin
  select concat(e.last_name,e.first_name) into emp_name from emp e where e.employee_id = emp_no;
  dbms_output.put_line(emp_name);
exception
  when no_data_found then
    dbms_output.put_line('员工不存在');
end;

利用隐式游标不用抛出

--隐式游标在for循环中应用  
begin
  for emp_row in (select * from emp where emp.employee_id = 1)
    loop
      dbms_output.put_line('员工id'||emp_row.employee_id||'工资:'||emp_row.salary);
    end loop;
end;

例子

--游标
--隐式游标统一名称SQL（隐式游标永远是关闭状态）  
--found数据发生改变则为true 否则为false ，
--ISOPEN识别游标的状态（打开和关闭）
--ROWCOUNT返回修改了多少条记录的数量
--  不能打印true和false
begin
  delete from student s where s.id between 1 and 100;
  if SQL%found then
     dbms_output.put_line('修改了多少行:'||SQL%ROWCOUNT);
  else
  dbms_output.put_line('没有对应记录');
  end if;
  IF SQL%ISOPEN THEN
    dbms_output.put_line('当前游标状态打开');
  ELSE 
    dbms_output.put_line('当前游标状态关闭');
  END IF;
  commit;
end;

显式游标

不带参数的显示游标

遍历游标 fetch 游标名称 变量；
在PL/SQL中使用DML语句时自动创建隐式游标
隐式游标自动声明、打开和关闭，其名为 SQL
通过检查隐式游标的属性可以获得最近执行的DML 语句的信息
隐式游标的属性有：
%FOUND – SQL 语句影响了一行或多行时为 TRUE
%NOTFOUND – SQL 语句没有影响任何行时为TRUE
%ROWCOUNT – SQL
1. 用在select后，在执行一次fetch后指向下一条数据。返回第几条数据的下标
2. update和delete直接返回删除了多少行
%ISOPEN - 游标是否打开，始终为FALSE


例子

--不带参数的显示游标
declare
--声明显示游标并且初始化  关键字cursor(定义为游标),is..（初始化）;
declare
cursor emp_cur is select * from emp;
emp_row emp%rowtype;
begin
  --打开游标
  open emp_cur;
  loop
  --逐行读取游标赋值给变量(与迭代器差不多)
  fetch emp_cur into emp_row;
  --当游标没有记录时退出
  exit when emp_cur%notfound;
  dbms_output.put_line('员工id'||emp_row.employee_id||'工资:'||emp_row.salary);
  end loop;
  --关闭游标
  close emp_cur;
end;

带参数的显示游标

例子

--带参数的显示游标(可以多个参数，注意参数名称和表中字段名称不要重复)
declare
cursor emp_cur(dept_no emp.department_id%type,sal emp.salary%type) is select * from emp e where e.department_id = dept_no and e.salary>sal ;
emp_row emp%rowtype;
begin
  open emp_cur(&部门id,&工资);
  loop
    fetch emp_cur into emp_row;
    exit when emp_cur%notfound;
    dbms_output.put_line('员工id'||emp_row.employee_id||'工资:'||emp_row.salary);
  end loop;     
  close emp_cur;
end;

使用显式游标更新行

在显示游标初始化时在后边加上for update
在更新和删除行时在后边加上条件 where current of 游标名称

例子
注意遍历游标时，不能进行事务的提交。

--游标更新数据 用 for update方法
declare
  cursor emp_cur(dept_no emp.department_id%type, sal emp.salary%type) is
    select *
      from emp e
     where e.department_id = dept_no
       and e.salary < sal
       for update;
  emp_row emp%rowtype;
begin
  open emp_cur(&部门id, &工资);
  loop
    fetch emp_cur into emp_row;
    exit when emp_cur%notfound;
    dbms_output.put_line('员工id' || emp_row.employee_id || '工资:' ||emp_row.salary);
    update emp e set e.salary = e.salary * 1.2 where current of emp_cur;
  end loop;
  commit;--commit 遍历游标时不能进行提交
  close emp_cur;
end;

游标的FOR循环

循环游标用于简化游标处理代码
当用户需要从游标中提取所有记录时使用
循环游标的语法如下：

FOR <record_index> IN <cursor_name>
LOOP

END LOOP;

游标的FOR循环举例：
隐式游标：

begin
  for emp_row in (select * from emp where emp.employee_id = 1)
    loop
      dbms_output.put_line('员工id'||emp_row.employee_id||'工资:'||emp_row.salary);
    end loop;
end;

显示游标：

REF 游标和游标变量

使用ref游标变量执行动态 SQL

注意：ref动态游标只能是弱类型（没有返回值）

--ref 动态游标 不能是强类型只能是弱类型
declare
      type emp_type is ref cursor; 
      emp_cur emp_type;
      emp_row emp%rowtype;
      
begin
      --动态执行的SQL语句,:n代表第n个占位符。使用using传值
      open emp_cur for 'select * from emp e where e.department_id =:1 and e.salary>:2' using &部门编号,&工资;
      loop
        fetch emp_cur into emp_row;
        exit when emp_cur%notfound;
        dbms_output.put_line('员工id'||emp_row.employee_id||'工资:'||emp_row.salary);
      end loop;
      close emp_cur;
end;

总结

游标作用：用于处理查询结果集中的数据
游标类型有：隐式游标、显式游标和 REF 游标
隐式游标由 PL/SQL 自动定义、打开和关闭
显式游标用于处理返回多行的查询
显式游标可以删除和更新活动集中的行
要处理结果集中所有记录时，可使用循环游标
在声明 REF 游标时，不需要将 SELECT 语句与 其关联