游标

一、游标的定义

1. 游标概念

① 从表中检索出结果集，从中每次指向一条记录进行交互的机制。

② 关系数据库中的操作是在完整的行集合上执行的。由SELECT 语句返回的行集合包括满足该语句的WHERE 子句所列条件的所有行。由该语句返回完整的行集合叫做结果集。

③ 游标是通过游标库来实现的。游标库是常常作为数据库系统或数据访问API 的一部分而得以实现的软件。

④ 用来管理从数据源返回的数据的属性（结果集）。这些属性包括并发管理、在结果集中的位置、返回的行数，以及是否能够在结果集中向前和/或向后移动（可滚动性）。

⑤ 游标跟踪结果集中的位置，并允许对结果集逐行执行多个操作，在这个过程中可能返回至原始表，也可能不返回至原始表。

换句话说，游标从概念上讲基于数据库的表返回结果集。

由于它指示结果集中的当前位置，就像计算机屏幕上的光标指示当前位置一样，“游标”由此得名。

%NotFound : 最后一条记录是否提取出True or False。

作用

①指定结果集中特定行的位置。

②基于当前的结果集位置检索一行或连续的几行。

③在结果集的当前位置修改行中的数据。

④可以以编程的方式访问数据库。

2. 主要事项

因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该改写；如果使用了游标，就要尽量避免在游标循环中再进行表连接的操作。

3. 游标类型

静态游标：结果集已经确实（静态定义）的游标。分为隐式和显示游标。

(1)隐式游标：所有DML语句为隐式游标，通过隐式游标属性可以获取SQL语句信息。 一次只能从数据库中提取一行数据，对于这种形式的查询和DML操作，系统都会使用一个隐式游标。

    * 插入操作：INSERT。              
    * 更新操作：UPDATE。               
    * 删除操作：DELETE。               
    * 单行查询操作：SELECT ... INTO ...。

(2)隐式游标的属性 返回值类型意义

     SQL%ROWCOUNT    整型  代表DML语句成功执行的数据行数  
　　 SQL%FOUND   布尔型 值为TRUE代表插入、删除、更新或单行查询操作成功  
    SQL%NOTFOUND    布尔型 与SQL%FOUND属性返回值相反  
    SQL%ISOPEN  布尔型 DML执行过程中为真，结束后为假  

(3)显示游标：用户显示声明的游标，即指定结果集。当查询返回结果超过一行时，就需要一个显式游标。
           显式游标对应一个返回结果为多行多列的SELECT语句。
    　　　　REF游标：动态关联结果集的临时对象。

5. 游标属性

    %Found ：Fetch语句（获取记录）执行情况True or False。

    %NotFound : 最后一条记录是否提取出True or False。

    %ISOpen ： 游标是否打开True or False。

    %RowCount ：游标当前提取的行数 。

二、游标的使用方式

 1 1.单条处理 
 2 open 游标; 
 3 LOOP   
 4 FETCH 游标 INTO 变量; 
 5 EXIT WHEN  条件; 
 6 END LOOP; 
 7 CLOSE 游标; 
 8   
 9 2.批量处理 
10 open 游标; 
11 FETCH 游标 BULK COLLECT INTO 集合变量; 
12 CLOSE 游标; 
13   
14 3.隐式游标 
15 for x in (sql语句) loop 
16 ...--逻辑处理 
17 end loop;

1、cursor游标使用

 1 /*简单cursor游标  
 2  *students表里面有name字段，你可以换做其他表测试   */  
 3 declare 
 4  --定义游标并且赋值(is 不能和cursor分开使用)  
 5  cursor stus_cur is select * from students;  
 6  --定义rowtype  
 7  cur_stu students%rowtype;  
 8  /*开始执行*/  
 9  begin 
10  --开启游标  
11  open stus_cur;  
12   --loop循环  
13   loop  
14   --循环条件  
15   exit when stus_cur%notfound;  
16   --游标值赋值到rowtype  
17   fetch stus_cur into cur_stu;  
18   --输出  
19   dbms_output.put_line(cur_stu.name);  
20   --结束循环  
21   end loop;  
22  --关闭游标  
23  close stus_cur;  
24  /*结束执行*/  
25  end;

2、ref cursor 游标使用

（1）介绍

　　像游标cursor一样，游标变量ref cursor指向指定查询结果集当前行。游标变量显得更加灵活因为其声明并不绑定指定查询。其主要运用于PLSQL函数或存储过程以及其他编程语言java等程序之间作为参数传递。不像游标的一点，游标变量没有参数。游标变量具有以下属性： (%FOUND, %NOTFOUND, %ISOPEN, and %ROWCOUNT)

（2）用法介绍：

1、声明格式：

1   DECLARE TYPE DeptCurTyp IS REF CURSOR RETURN  departments%ROWTYPE;

2、游标变量又分为强类型strong(with a return type)和弱类型(with no return type)：

DECLARE
   TYPE empcurtyp IS REF CURSOR RETURN employees%ROWTYPE; -- 强类型
   TYPE genericcurtyp IS REF CURSOR; -- 弱类型
   cursor1 empcurtyp;
   cursor2 genericcurtyp;
   my_cursor SYS_REFCURSOR; -- 使用预定义游标变量sys_refcursor
   TYPE deptcurtyp IS REF CURSOR RETURN departments%ROWTYPE;
   dept_cv deptcurtyp; -- 声明游标变量或是返回record类型：
   DECLARE
   TYPE EmpRecTyp IS RECORD (
   employee_id NUMBER,
   last_name VARCHAR2(25),
   salary NUMBER(8,2));
   TYPE EmpCurTyp IS REF CURSOR RETURN EmpRecTyp;
   emp_cv EmpCurTyp; -- declare cursor variable

3、使用游标熟悉检查游标变量是否打开

DECLARE
TYPE empcurtyp IS REF CURSOR RETURN employees%ROWTYPE;
emp_cv empcurtyp;
BEGIN
IF NOT emp_cv%ISOPEN THEN -- open cursor variable
OPEN emp_cv FOR SELECT * FROM employees;
END IF;
CLOSE emp_cv;
END;

4、使用游标变量作为参数传递

 1 DECLARE
 2 TYPE empcurtyp IS REF CURSOR RETURN employees%ROWTYPE;
 3 emp empcurtyp;
 4 -- after result set is built, process all the rows inside a single procedure
 5 -- rather than calling a procedure for each row
 6 PROCEDURE process_emp_cv (emp_cv IN empcurtyp) IS
 7 person employees%ROWTYPE;
 8 BEGIN
 9 DBMS_OUTPUT.PUT_LINE('-----');
10 DBMS_OUTPUT.PUT_LINE('Here are the names from the result set:');
11 LOOP
12 FETCH emp_cv INTO person;
13 EXIT WHEN emp_cv%NOTFOUND;
14 DBMS_OUTPUT.PUT_LINE('Name = ' || person.first_name ||
15 ' ' || person.last_name);
16 END LOOP;
17 END;
18 BEGIN
19 -- First find 10 arbitrary employees.
20 OPEN emp FOR SELECT * FROM employees WHERE ROWNUM < 11;
21 process_emp_cv(emp);
22 CLOSE emp;
23 -- find employees matching a condition.
24 OPEN emp FOR SELECT * FROM employees WHERE last_name LIKE 'R%';
25 process_emp_cv(emp);
26 CLOSE emp;
27 END;
28 /

5、在包package中声明游标变量：

 1 CREATE PACKAGE emp_data AS
 2 TYPE empcurtyp IS REF CURSOR RETURN employees%ROWTYPE;
 3 PROCEDURE open_emp_cv (emp_cv IN OUT empcurtyp);
 4 END emp_data;
 5 /
 6 CREATE PACKAGE BODY emp_data AS
 7 PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp) IS
 8 BEGIN
 9 OPEN emp_cv FOR SELECT * FROM employees;
10 END open_emp_cv;
11 END emp_data;
12 /

6、提取游标变量到集合类型collection：

 1 DECLARE
 2 TYPE empcurtyp IS REF CURSOR;
 3 TYPE namelist IS TABLE OF employees.last_name%TYPE;
 4 TYPE sallist IS TABLE OF employees.salary%TYPE;
 5 emp_cv empcurtyp;
 6 names namelist;
 7 sals sallist;
 8 BEGIN
 9 OPEN emp_cv FOR SELECT last_name, salary FROM employees
10 WHERE job_id = 'SA_REP';
11 FETCH emp_cv BULK COLLECT INTO names, sals;
12 CLOSE emp_cv;
13 -- loop through the names and sals collections
14 FOR i IN names.FIRST .. names.LAST
15 LOOP
16 DBMS_OUTPUT.PUT_LINE('Name = ' || names(i) || ', salary = ' || sals(i));
17 END LOOP;
18 END;
19 /

（3）游标变量的使用限制：

1、不能再包说明中声明游标变量；

2、不能用“=”运算符比较游标变量相等性、不等性及是否为空；

3、不能存储于表列中；

4、不能将游标变量存在于关联数组、嵌套表或数组；

5、游标和游标变量之前是不可互操作的！

1、强类型游标：

 1 CREATE OR REPLACE PACKAGE strongly_typed IS
 2 
 3 TYPE return_cur IS REF CURSOR RETURN all_tables%ROWTYPE;
 4 PROCEDURE child(p_return_rec OUT return_cur);
 5 PROCEDURE parent(p_NumRecs PLS_INTEGER);
 6 
 7 END strongly_typed;
 8 /
 9 CREATE OR REPLACE PACKAGE BODY strongly_typed IS
10 PROCEDURE child(p_return_rec OUT return_cur) IS
11 
12 BEGIN
13   OPEN p_return_rec FOR 
14   SELECT * FROM all_tables; 
15 END child;
16 --==================================================
17 PROCEDURE parent (p_NumRecs PLS_INTEGER) IS 
18  p_retcur return_cur;
19  at_rec   all_tables%ROWTYPE;
20 BEGIN
21   child(p_retcur);
22 
23   FOR i IN 1 .. p_NumRecs
24   LOOP
25     FETCH p_retcur
26     INTO at_rec;
27 
28     dbms_output.put_line(at_rec.table_name || 
29     ' - ' || at_rec.tablespace_name || 
30     ' - ' || TO_CHAR(at_rec.initial_extent) || 
31     ' - ' || TO_CHAR(at_rec.next_extent));
32   END LOOP;
33 END parent;
34 END strongly_typed;
35 /
38 set serveroutput on
39 
40 exec strongly_typed.parent(1);
41 exec strongly_typed.parent(8);

2、弱类型游标：

 1 CREATE OR REPLACE PROCEDURE child (
 2  p_NumRecs IN PLS_INTEGER,
 3  p_return_cur OUT SYS_REFCURSOR)
 4 IS
 5 
 6 BEGIN
 7   OPEN p_return_cur FOR
 8   'SELECT * FROM all_tables WHERE rownum <= ' || p_NumRecs ;
 9 END child;
10 /
11 
12 CREATE OR REPLACE PROCEDURE parent (pNumRecs VARCHAR2) IS
13  p_retcur  SYS_REFCURSOR;
14  at_rec    all_tables%ROWTYPE;
15 BEGIN
16   child(pNumRecs, p_retcur);
17 
18   FOR i IN 1 .. pNumRecs
19   LOOP
20     FETCH p_retcur
21     INTO at_rec;
22 
23     dbms_output.put_line(at_rec.table_name ||
24     ' - ' || at_rec.tablespace_name ||
25     ' - ' || TO_CHAR(at_rec.initial_extent) ||
26     ' - ' || TO_CHAR(at_rec.next_extent));
27   END LOOP;
28 END parent;
29 /
30 
31 set serveroutput on
32 
33 exec parent(1);
34 exec parent(17);

3、预定义游标变量：

CREATE TABLE employees (
empid   NUMBER(5),
empname VARCHAR2(30));

INSERT INTO employees (empid, empname) VALUES (1, 'Dan Morgan');
INSERT INTO employees (empid, empname) VALUES (2, 'Hans Forbrich');
INSERT INTO employees (empid, empname) VALUES (3, 'Caleb Small');
COMMIT;
CREATE OR REPLACE PROCEDURE pass_ref_cur(p_cursor SYS_REFCURSOR) IS

TYPE array_t IS TABLE OF VARCHAR2(4000)
INDEX BY BINARY_INTEGER;

rec_array array_t;

BEGIN
  FETCH p_cursor BULK COLLECT INTO rec_array;

  FOR i IN rec_array.FIRST .. rec_array.LAST
  LOOP
    dbms_output.put_line(rec_array(i));
  END LOOP;
END pass_ref_cur;
/
set serveroutput on

DECLARE
 rec_array SYS_REFCURSOR;
BEGIN
  OPEN rec_array FOR
  'SELECT empname FROM employees';

  pass_ref_cur(rec_array);
  CLOSE rec_array;
END;

三、游标循环方式

1、补充一种循环条件

 1 declare 
 2  stu_cur sys_refcursor;  
 3  stuone students%rowtype;  
 4  begin 
 5   open stu_cur for select * from students;  
 6   fetch stu_cur into stuone;  
 7   --特别注意循环条件的改变  
 8   --这个条件是发现了在循环  
 9   --与上一个notfound不同的  
10   while stu_cur%found loop  
11   dbms_output.put_line(stuone.name||' '||stuone.hobby);  
12   fetch stu_cur into stuone;  
13       end loop;  
14  end;

2、普通的fetch into

 1 /*普通方式*/  
 2 declare 
 3 cursor myemp_cur is select * from myemp;  
 4 v_myemp myemp%rowtype;  
 5 begin 
 6  open myemp_cur;  
 7  fetch myemp_cur into v_myemp;  
 8  while myemp_cur%found loop  
 9  dbms_output.put_line(v_myemp.ename);  
10  fetch myemp_cur into v_myemp;  
11  end loop;  
12 end;

3、高效的bulk collect

 1 /*高效bulk collect for*/  
 2 declare 
 3 cursor myemp_cur  
 4 is select * from myemp;  
 5 type myemp_tab is table of myemp%rowtype;  
 6 myemp_rd myemp_tab;  
 7 begin 
 8  open myemp_cur;  
 9  loop  
10  fetch myemp_cur bulk collect into myemp_rd limit 20;  
11  for i in 1..myemp_rd.count loop  
12   dbms_output.put_line('姓名：'||myemp_rd(i).ename);  
13  end loop;  
14  exit when myemp_cur%notfound;  
15  end loop;  
16 end;

游标

一、 游标的定义