游标

显式游标

显式游标处理

显式游标处理需四个 PL/SQL步骤:
1）定义/声明游标：就是定义一个游标名，以及与其相对应的SELECT 语句。
格式：
CURSOR cursor_name[(parameter[, parameter]…)]
[RETURN datatype]
IS
select_statement;
游标参数只能为输入参数，其格式为：
parameter_name [IN] datatype [{:= | DEFAULT} expression]
在指定数据类型时，不能使用长度约束。如NUMBER(4),CHAR(10) 等都是错误的。
[RETURN datatype]是可选的，表示游标返回数据的数据。如果选择，则应该严格与select_statement中的选择列表在次序和数据类型上匹配。一般是记录数据类型或带“%ROWTYPE”的数据。
2）打开游标：就是执行游标所对应的SELECT 语句，将其查询结果放入工作区，并且指针指向工作区的首部，标识游标结果集合。如果游标查询语句中带有FOR UPDATE选项，OPEN 语句还将锁定数据库表中游标结果集合对应的数据行。
格式：
OPEN cursor_name;
在向游标传递参数时，可以使用与函数参数相同的传值方法，即位置表示法和名称表示法。PL/SQL 程序不能用OPEN 语句重复打开一个游标。
3）提取游标数据：就是检索结果集合中的数据行，放入指定的输出变量中。
格式：
FETCH cursor_name INTO XXXX;
执行FETCH语句时，每次返回一个数据行，然后自动将游标移动指向下一个数据行。当检索到最后一行数据时，如果再次执行FETCH语句，将操作失败，并将游标属性%NOTFOUND置为TRUE。所以每次执行完FETCH语句后，检查游标属性%NOTFOUND就可以判断FETCH语句是否执行成功并返回一个数据行，以便确定是否给对应的变量赋了值。对该记录进行处理；继续处理，直到活动集合中没有记录；
4）关闭游标：当提取和处理完游标结果集合数据后，应及时关闭游标，以释放该游标所占用的系统资源，并使该游标的工作区变成无效，不能再使用FETCH 语句取其中数据。关闭后的游标可以使用OPEN 语句重新打开。
格式：
CLOSE cursor_name;
注：定义的游标不能有INTO 子句。

游标4个属性

Cursor_name%FOUND 布尔型属性，当最近一次提取游标操作FETCH成功则为 TRUE,否则为FALSE；
Cursor_name%NOTFOUND 布尔型属性，与%FOUND相反；
Cursor_name%ISOPEN 布尔型属性，当游标已打开时返回 TRUE；
Cursor_name%ROWCOUNT 数字型属性，返回已从游标中读取的记录数。（sql%rowcount 的作用：记录修改的条数，当你进行删除，更新操作的时候，可以提示已删除\更新的行数，这个参数放在修改语句和commit之间，sql%rowcount只会记录未被提交的最后一条SQL语句的影响行数。这点很重要，如果想统计多个sql的合计影响行数，就必须在每个sql后面，用一个变量保存当前的sql%rowcount。）

关于 NO_DATA_FOUND 和 %NOTFOUND的区别

1. SELECT . . . INTO 语句触发 NO_DATA_FOUND；
2. 当一个显式光标的 where 子句未找到时触发 %NOTFOUND；
3. 当 UPDATE 或 DELETE 语句的 where 子句未找到时触发SQL%NOTFOUND；
4. 在光标的提取(Fetch)循环中要用 %NOTFOUND 或%FOUND 来确定循环的退出条件，不要用 NO_DATA_FOUND。

1. 游标的FOR循环
   PL/SQL语言提供了游标FOR循环语句，自动执行游标的OPEN、FETCH、CLOSE语句和循环语句的功能；当进入循环时，游标FOR循环语句自动打开游标，并提取第一行游标数据，当程序处理完当前所提取的数据而进入下一次循环时，游标FOR循环语句自动提取下一行数据供程序处理，当提取完结果集合中的所有数据行后结束循环，并自动关闭游标。
   格式：
   FOR index_variable IN cursor_name[(value[, value]…)] LOOP
   — 游标数据处理代码
   END LOOP;
   其中：
   index_variable为游标FOR 循环语句隐含声明的索引变量，该变量为记录变量，其结构与游标查询语句返回的结构集合的结构相同。在程序中可以通过引用该索引记录变量元素来读取所提取的游标数据，index_variable中各元素的名称与游标查询语句选择列表中所制定的列名相同。如果在游标查询语句的选择列表中存在计算列，则必须为这些计算列指定别名后才能通过游标FOR 循环语句中的索引变量来访问这些列数据。
   注：不要在程序中对游标进行人工操作；不要在程序中定义用于控制FOR循环的记录。
   declare
   —类型定义
   cursor cc is select empno,ename,job,sal
   from emp where job = ‘MANAGER’;
   —定义一个游标变量
   ccrec cc%rowtype;
   begin
   —for循环
   for ccrec in cc loop
   dbms_output.put_line(ccrec.empno||’-‘||ccrec.ename||’-‘||ccrec.job||’-‘||ccrec.sal);
   end loop;
   end;
   值得注意的是， ccrec cc%rowtype;这个语句不是必须的。他可以在for循环中隐式声明。

fetch游标
—使用的时候 必须要明确的打开和关闭

declare
--类型定义
cursor cc is select empno,ename,job,sal
from emp where job = 'MANAGER';
--定义一个游标变量
ccrec cc%rowtype;
begin
--打开游标
open cc;
--loop循环
loop
--提取一行数据到ccrec中
fetch cc into ccrec;
--判断是否提取到值，没取到值就退出
--取到值cc%notfound 是false
--取不到值cc%notfound 是true
exit when cc%notfound;
dbms_output.put_line(ccrec.empno||'-'||ccrec.ename||'-'||ccrec.job||'-'||ccrec.sal);
end loop;
--关闭游标
close cc;
end;

(3)参数游标

按部门编号的顺序输出部门经理的名字
declare
--部门
cursor c1 is select deptno from dept;
--参数游标c2,定义参数的时候
--只能指定类型,不能指定长度
--参数只能出现在select语句=号的右侧
cursor c2(no number,pjob varchar2) is select emp.* from emp
where deptno = no and job=pjob;
c1rec c1%rowtype;
c2rec c2%rowtype;
--定义变量的时候要指定长度
v_job varchar2(20);
begin
--部门
for c1rec in c1 loop
--参数在游标中使用
for c2rec in c2(c1rec.deptno,'MANAGER') loop
dbms_output.put_line(c1rec.deptno||'-'||c2rec.ename);
end loop;
end loop;
end;

游标变量（动态游标）

 与游标一样，游标变量也是一个指向多行查询结果集合中当前数据行的指针。但与游标不同的是，游标变量是动态的，而游标是静态的。游标只能与指定的查询相连，即固定指向一个查询的内存处理区域，而游标变量则可与不同的查询语句相连，它可以指向不同查询语句的内存处理区域（但不能同时指向多个内存处理区域，在某一时刻只能与一个查询语句相连），只要这些查询语句的返回类型兼容即可。

声明游标变量

游标变量为一个指针，它属于参照类型，所以在声明游标变量类型之前必须先定义游标变量类型。在PL/SQL中，可以在块、子程序和包的声明区域内定义游标变量类型。
语法格式为：
TYPE ref_type_name IS REF CURSOR [ RETURN return_type];
其中：ref_type_name为新定义的游标变量类型名称；
return_type 为游标变量的返回值类型，它必须为记录变量。
在定义游标变量类型时，可以采用强类型定义和弱类型定义两种。强类型定义必须指定游标变量的返回值类型，而弱类型定义则不说明返回值类型。
声明一个游标变量的两个步骤：
步骤一：定义一个REF CURSOU数据类型，如：TYPE ref_cursor_type IS REF CURSOR;
步骤二：声明一个该数据类型的游标变量，如： cv_ref REF_CURSOR_TYPE;
游标变量操作
与游标一样，游标变量操作也包括打开、提取和关闭三个步骤。

打开游标变量

打开游标变量时使用的是OPEN…FOR 语句。格式为：
OPEN {cursor_variable_name | :host_cursor_variable_name}
FOR select_statement;
其中：cursor_variable_name为游标变量，host_cursor_variable_name为PL/SQL主机环境（如OCI: ORACLE Call Interface，Pro*c 程序等）中声明的游标变量。
OPEN…FOR 语句可以在关闭当前的游标变量之前重新打开游标变量，而不会导致CURSOR_ALREAD_OPEN异常错误。新打开游标变量时，前一个查询的内存处理区将被释放。

提取游标变量数据

使用FETCH语句提取游标变量结果集合中的数据。格式为：
FETCH {cursor_variable_name | :host_cursor_variable_name}
INTO {variable [, variable]…| record_variable};
其中：cursor_variable_name和host_cursor_variable_name分别为游标变量和宿主游标变量名称；variable和record_variable分别为普通变量和记录变量名称。

关闭游标变量

CLOSE语句关闭游标变量，格式为：
CLOSE {cursor_variable_name | :host_cursor_variable_name}
其中：cursor_variable_name和host_cursor_variable_name分别为游标变量和宿主游标变量名称，如果应用程序试图关闭一个未打开的游标变量，则将导致INVALID_CURSOR异常错误。

declare

--定义游标类型

typesp_emp_copy_cursor is ref cursor;

--定义一个游标变量,当定义游标时不需要指定相应的select语句

test_cursor sp_emp_copy_cursor;

v_ename emp_copy.ename%type;

v_sal emp_copy.sal%type;

begin

  --把test_cursor和一个select结合,当使用游标时（open时）需要指定select语句

  opentest_cursor for select ename,sal from emp_copy wheredeptno=&no;

  --循环取出(fethch)

  loop

    fetch test_cursor into v_ename,v_sal;

    --判断是否test_cursor是否为空

    exit when test_cursor%notfound;

    dbms_output.put_line('名字：'||v_ename||'工资：'||v_sal);

  end loop;

  --关闭游标

  closetest_cursor;

end;