游标的个人使用理解

---------------静态显式游标  
declare
  cursor A_cursor is
    select * from emp; ---直接声明游标
begin
  for emp_rec in A_cursor loop
    dbms_output.put_line(emp_rec.ename || emp_rec.empno || emp_rec.sal ||
                         emp_rec.comm || emp_rec.deptno);
  end loop;
end;
与
declare
  cursor A_cursor is
    select * from emp; ---直接声明游标
  emp_rec emp%rowtype;
begin
  open A_cursor;
  loop
    fetch A_cursor
      into emp_rec;
    exit when A_cursor%notfound;
    dbms_output.put_line(emp_rec.ename || emp_rec.empno || emp_rec.sal ||
                         emp_rec.comm || emp_rec.deptno);
  end loop;
  close A_cursor;
end;
   与
    declare
    begin
      for emp_rec in (select * from emp) ---隐式 游标
       loop
        dbms_output.put_line(emp_rec.ename || emp_rec.empno || emp_rec.sal ||
                             emp_rec.comm || emp_rec.deptno);
      end loop;
    end;
 -----↑上面这三个游标的结果出来是一样的。
 ------------------------------------动态强类型游标  适应性强，而且可以打开多次，而且条件与记录数是不相同的情况下。
declare
  type emp_cur_type is ref cursor return emp%rowtype;
  emp_cur    emp_cur_type;
  emp_record emp%rowtype;
begin
  open emp_cur for
    select * from emp where deptno = 30;
  loop
    fetch emp_cur
      into emp_record;
    exit when emp_cur%notfound;
    dbms_output.put_line('name is:' || emp_record.ename || ' and sal is:' ||
                         emp_record.sal);
    dbms_output.put_line('-------------------------------------');
    dbms_output.put_line(emp_cur%rowcount);
  end loop;
  close emp_cur;
  --open emp_cur for select * from dept;错误的，类型不一致。
  --close emp_cur;
  open emp_cur for
    select * from emp where deptno = 20;
  loop
    fetch emp_cur
      into emp_record;
    exit when emp_cur%notfound;
    dbms_output.put_line('name is:' || emp_record.ename || ' and sal is:' ||
                         emp_record.sal);
    dbms_output.put_line('********************');
    dbms_output.put_line(emp_cur%rowcount);
  end loop;
  close emp_cur;
end;
------------------------------------------------------动态弱类型游标  一个emp_cur可以对应 emp_record用完也可以对应dept_record
declare
  type emp_cur_type is ref cursor;
  emp_cur     emp_cur_type;
  emp_record  emp%rowtype;
  dept_record dept%rowtype;
begin
  open emp_cur for
    select * from emp;
  loop
    fetch emp_cur
      into emp_record;
    exit when emp_cur%notfound;
    dbms_output.put_line('name is:' || emp_record.ename || ' and sal is:' ||
                         emp_record.sal);
  end loop;
  close emp_cur;

  open emp_cur for
    select * from dept; --可再次打开，不同类型的
  loop
    fetch emp_cur
      into dept_record;
    exit when emp_cur%notfound;
    dbms_output.put_line('dname is:' || dept_record.dname);
  end loop;
  close emp_cur;
end;
/

----------------强、弱游标在同一过程中
declare
  type cur1 is ref cursor;
  type cur2 is ref cursor return emp%rowtype;
  r_cur1      cur1;
  r_cur2      cur2;
  emp_record  emp%rowtype;
  dept_record dept%rowtype;
begin
  dbms_output.put_line('Cur2_强*******************************************emp');
  open r_cur2 for
    select * from emp where deptno = 30;
  loop
    fetch r_cur2
      into emp_record;
    exit when r_cur2%notfound;
    dbms_output.put_line('name is:' || emp_record.ename || ' and sal is:' ||
                         emp_record.sal);
    dbms_output.put_line('-------------------------------------');
    dbms_output.put_line(r_cur2%rowcount);
  end loop;
  close r_cur2;
  dbms_output.put_line('Cur1_弱**************************************************emp');
  open r_cur1 for
    select * from emp where deptno = 30;
  loop
    fetch r_cur1
      into emp_record;
    exit when r_cur1%notfound;
    dbms_output.put_line('name is:' || emp_record.ename || ' and sal is:' ||
                         emp_record.sal);
    dbms_output.put_line('-------------------------------------');
    dbms_output.put_line(r_cur1%rowcount);
  end loop;
  close r_cur1;
  dbms_output.put_line('Cur1_弱**************************************************dept');
  open r_cur1 for
    select * from dept;
  loop
    fetch r_cur1
      into dept_record;
    exit when r_cur1%notfound;
    dbms_output.put_line('name is:' || dept_record.deptno ||
                         ' and sal is:' || dept_record.dname);
    dbms_output.put_line('-------------------------------------');
    dbms_output.put_line(r_cur1%rowcount);
  end loop;
  close r_cur1;
end;
 -------只使用表里面某几例的游标
declare
  type emp_cur_type is ref cursor;
  emp_cur emp_cur_type;
  type emp_record1 is record(
    ename emp.ename%type,
    sal   emp.sal%type);  ----使用type record 必须在下面声明赋值  
        ----TYPE SalList IS TABLE OF emp.sal%TYPE; 别一种使用方式 or tyep sallist is table of emp%rowtype
  emp_record emp_record1; ---这里就是声明与赋值
begin
  open emp_cur for
    select ename, sal from emp where deptno = 30;
  loop
    fetch emp_cur
      into emp_record;
    exit when emp_cur%notfound;
    dbms_output.put_line('name is:' || emp_record.ename || ' and sal is:' ||
                         emp_record.sal);
    dbms_output.put_line('-------------------------------------');
    dbms_output.put_line(emp_cur%rowcount);
  end loop;
  close emp_cur;
end;
 ---------------使用弱类型游标，对应两个不现表，不字段类型
declare
  type emp_cur_type is ref cursor;
  emp_cur emp_cur_type;
  type emp_record1 is record(
    ename varchar2(10),
    job   varchar2(10));
  emp_record emp_record1;
begin
  open emp_cur for
    select ename, job from emp where deptno = 30;
  loop
    fetch emp_cur
      into emp_record;
    exit when emp_cur%notfound;
    dbms_output.put_line('name is:' || emp_record.ename || ' and sal is:' ||
                         emp_record.job);
    dbms_output.put_line('-------------------------------------');
    dbms_output.put_line(emp_cur%rowcount);
  end loop;
  close emp_cur;
  open emp_cur for
    select ename, to_char(sal) sal from emp where deptno = 30;
  loop
    fetch emp_cur
      into emp_record;
    exit when emp_cur%notfound;
    dbms_output.put_line('name is:' || emp_record.ename || ' and sal is:' ||
                         emp_record.job);
    dbms_output.put_line('-------------------------------------');
    dbms_output.put_line(emp_cur%rowcount);
  end loop;
  close emp_cur;
end;
----------------------sys_refcursor   可多次打开，直接声明此类型的变量，不用先定义类型再声明变量。
declare
  emp_cur     sys_refcursor;
  emp_record  emp%rowtype;
  dept_record dept%rowtype;
begin
  open emp_cur for
    select * from emp;
  loop
    fetch emp_cur
      into emp_record;
    exit when emp_cur%notfound;
    dbms_output.put_line('name is:' || emp_record.ename || ' and sal is:' ||
                         emp_record.sal);
  end loop;
  close emp_cur;
  open emp_cur for
    select * from dept; --可再次打开，不同类型的
  loop
    fetch emp_cur
      into dept_record;
    exit when emp_cur%notfound;
    dbms_output.put_line('dname is:' || dept_record.dname);
  end loop;
  close emp_cur;
end;
------------------其他总结：

1、游标可以用for循环，但只限于cursor cur_var is……这种类型，用在其他的里面都是错误的；for本身就包含了打开、关闭游标，此时再显示打开关闭都是错误的。
declare
  cursor emp_cur is
    select * from emp;
begin
  --open emp_cur;    --;是错误的，因为for本身就包含了打开、关闭
  for emp_record in emp_cur loop
    dbms_output.put_line('name is:' || emp_record.ename || ' and sal is:' ||
                         emp_record.sal);
  end loop;
  --close emp_cur;   ---是错误的，for本身包含了关闭。
end;
*******************************************************************************
--是不是表示：ref cursor变量不支持for打开并循环？
declare
  type emp_cur_type is ref cursor return emp%rowtype;
  emp_cur emp_cur_type;
begin
  open emp_cur for
    select * from emp; --怎么都是错，for已经打开了。
  for emp_record in emp_cur --不管前面有没有打开语句，for都不承认这种类型
   loop
    dbms_output.put_line('name is:' || emp_record.ename || ' and sal is:' ||
                         emp_record.sal);
  end loop;
end;
2、游标可以带参数

DECLARE
  CURSOR c1(ename VARCHAR2, sal NUMBER) IS
    SELECT * FROM emp WHERE sal > 800;
BEGIN
  FOR person IN c1(1213, 11111) ----c1里面带了两参数，不知道做啥用的
   LOOP
    DBMS_OUTPUT.PUT_LINE('ename = ' || person.ename || ', sal = ' ||
                         person.sal || '------' || c1%rowcount);
  END LOOP;
END;

3、bulk collect批量赋值

declare
 type emp_cur_type is ref cursor;
 emp_cur emp_cur_type;
 type name_list is table of emp.ename%type;
 type sal_list is table of emp.sal%type;
 names name_list;
 sals sal_list;
begin
 open emp_cur for select ename,sal from emp;
 fetch emp_cur bulk collect into names,sals;
 close emp_cur;
 for i in names.first .. names.last
 loop
 dbms_output.put_line('name is:'||names(i)||' and sal is:'||sals(i));
 end  loop;
end;

oracle学习之bulk collect用法
通过bulk collect减少loop处理的开销，使用Bulk Collect提高Oracle查询效率

Oracle8i中首次引入了Bulk Collect特性，该特性可以让我们在PL/SQL中能使用批查询，批查询在某些情况下能显著提高查询效率。

采用bulk collect可以将查询结果一次性地加载到collections中。

而不是通过cursor一条一条地处理。

可以在select into,fetch into,returning into语句使用bulk collect。

注意在使用bulk collect时，所有的into变量都必须是collections.

 

举几个简单的例子：

View Code

 

--在select into语句中使用bulk collect

DECLARE
TYPE SalList IS TABLE OF emp.sal%TYPE;
sals SalList;
BEGIN
-- Limit the number of rows to 100.
SELECT sal BULK COLLECT INTO sals FROM emp
WHERE ROWNUM <= 100;
 for i in sals.first .. sals.last
 loop
 dbms_output.put_line('name is:'||sals(i)||' and sal is:'||sals(i));
 end  loop;
-- Retrieve 10% (approximately) of the rows in the table.
 dbms_output.put_line('-------------------------');
SELECT sal BULK COLLECT INTO sals FROM emp  where deptno=10;
 for i in sals.first .. sals.last
 loop
 dbms_output.put_line('name is:'||sals(i)||' and sal is:'||sals(i));
 end  loop;
END;
/

 

--在fetch into中使用bulk collect

DECLARE
TYPE DeptRecTab IS TABLE OF dept%ROWTYPE;
dept_recs DeptRecTab;
CURSOR c1 IS
SELECT deptno, dname, loc FROM dept WHERE deptno > 10;
BEGIN
OPEN c1;
FETCH c1 BULK COLLECT INTO dept_recs;
END;
/

--在returning into中使用bulk collect

CREATE TABLE emp2 AS SELECT * FROM employees;
DECLARE
TYPE NumList IS TABLE OF employees.employee_id%TYPE;
enums NumList;
TYPE NameList IS TABLE OF employees.last_name%TYPE;
names NameList;
BEGIN
DELETE FROM emp2 WHERE department_id = 30
RETURNING employee_id, last_name BULK COLLECT INTO enums, names;
dbms_output.put_line('Deleted ' || SQL%ROWCOUNT || ' rows:');
FOR i IN enums.FIRST .. enums.LAST
LOOP
dbms_output.put_line('Employee #' || enums(i) || ': ' || names(i));
END LOOP;
END;
/
DROP TABLE emp2;

 

 

通过上面的测试和分析，我们可以看到Bulk
Collect批查询在某种程度上可以提高查询效率，它首先将所需数据读入内存，然后再统计分析，这样就可以提高查询效率。但是，如果Oracle数据库的内存较小，Shared
Pool Size不足以保存Bulk Collect批查询结果，那么该方法需要将Bulk Collect的集合结果保存在磁盘上，在这种情况下，Bulk
Collect方法的效率反而不如其他两种方法，有兴趣的读者可以进一步测试。

另外，除了Bulk Collect批查询外，我们还可以使用FORALL语句来实现批插入、删除和更新，这在大批量数据操作时可以显著提高执行效率。

----------以下未验证
4、cursor变量的位置
CREATE PACKAGE emp_data AS
 TYPE EmpCurTyp IS REF CURSOR RETURN employees%ROWTYPE;
-- emp_cv EmpCurTyp; -- not allowed
 PROCEDURE open_emp_cv;
END emp_data;
/
CREATE PACKAGEBODY emp_data  AS
-- emp_cv EmpCurTyp; -- not allowed
PROCEDURE open_emp_cv IS
 emp_cv EmpCurTyp;-- this is legal
 BEGIN
   OPEN emp_cvFOR SELECT *FROM employees;
 END open_emp_cv;
END emp_data;
/

5、嵌套cursor
打开父cursor时，子cursor隐含打开；当
语法格式:cursor(subquery)
A  nestedcursorisimplicitly openedwhenthecontainingrowisfetchedfromtheparentcursor.
Thenestedcursorisclosedonlywhen:
Thenestedcursorisexplicitly closedbytheuser
Theparentcursorisreexecuted
Theparentcursorisclosed
Theparentcursoriscanceled
示例；
declare
 type emp_cur_type is ref cursor;
 type dept_cur_type is ref cursor;
 v_ename emp.ename%type;
 v_dname dept.dname%type;
 emp_cur emp_cur_type;
 dept_cur dept_cur_type;
begin
 open dept_cur for
 select d.dname,
 cursor(select e.ename from emp e where e.deptno=d.deptno )emp s from dept d;
 loop
 fetch dept_cur into v_dname,emp_cur;
 exit when dept_cur%notfound;
 dbms_output.put_line('dname is : '||v_dname);
 loop
  fetch emp_cur into v_ename;
  exit when emp_cur%notfound;
  dbms_output.put_line('--ename is : '||v_ename);
 end loop;
 end loop;
 close dept_cur;
end;