游标小记(收集汇总)

fly-in-the-sky

以下内容是自己在平时收集的一些关于游标的內容，现帖出来，希望对大家可能有所帮助。其中如果有值得商榷的地方，还请大家指正。
以前在论坛里面看游标的內容比较零星，不够全面，所以在这里也想起到一个抛砖引玉的作用，看大家还有什么要补充的地方，可以跟帖完善之，共同学习！共同进步！谢谢！



游标
在Oracle9i之前，使用FETCH语句每次只能提取一行数据；从Oracle9i开始，通过使用FETCH…BULK COLLECT INTO语句，每次可以提取多行数据。语法如下：
(1)        FETCH cursor_name INTO variable1,variable2,…；
此方法必须要使用循环语句处理结果集的所有数据。
(2)        FETCH cursor_name BULK COLLECT INTO collect1,collect2,…[LIMIT rows]
          [LIMIT rows]可用来限制每次游标每次提取的行数。


基于游标定义记录变量
使用%ROWTYPE属性不紧可以基于表和视图定义记录变量，也可以基于游标定义记录变量。当基于游标定义记录变量时，记录成员名实际就是SELECT语句的列名和列别名。为了简化显示游标的数据处理，建議开发人员使用记录变量存放游标数据。
For example:
DECLARE
   CURSOR emp_cursor IS SELECT ename,sal FROM emp；
   emp_reocrd emp_cursor%ROWTYPE；
BEGIN
   OPEN emp_cursor；
   LOOP
      FETCH emp_cursor INTO emp_record；
      EXIT WHEN emp_record%NOTFOUND；
      dbms_ouput.put_line(‘雇员名:’||emp_record.ename||’,雇员工资:’||emp_record.sal)；
   END LOOP；
   CLOSE emp_cursor；
END；


参数游标
Defined: CURSOR cursor_name(parameter_name datatype) IS select_statement；
在定义参数游标时,游标参数只能指定数据类型,而不能指定长度.另外,定义参数游标时,一定要在游标子查询的WHERE子句中引用该参数,否则失去了定义参数游标的意义.
For example:
CURSOR emp_cursor(no number) IS SELECT ename FROM emp WHERE deptno=no；

当使用显示游标属性时，必须要在显式游标属性之前带有显式游标名作为前缀(游标名.属性名).(属性主要指:%FOUND,NOTFOUND,%ROWCOUNT,%ISOPEN)

使用游标更新或者删除数据
Syntax:
CUROSR cursor_name(parameter_name datatype)
IS select_statement
FOR UPDATE[OF column_reference] [NOWAIT]；
FOR UPDATE 子句用于在游标结果集数据上加行共享锁，以防止其它用户在相应行上执行DML操作；当SELECT 语句引用到多张表时，使用OF子句可以确定哪些表(或者是哪些表里的哪些字段)要加锁，如果没有OF子句，则会在SELECT语句所引用的全部表上加锁。

NOWAIT子句用于指定不等待锁。使用FOR UPDATE 语句对被作用行加锁，如果其它会话已经在被作用行上加锁，那么在默认情况下当前会话要一直等待对方释放锁。通过在FOR UPDATE子句指定NOWAIT子句，可以避免等待锁。当指定NOWAIT子句拉，如果其它会话已经在被作用行加锁，那么当前会话会显示错误提示信息，并退出PL/SQL块。

在提取了游标数据之后，为了更新或者删除当前游标数据，必须在UPDATE或DELETE语句中引用WHERE CURRENT OF 子句。语法如下:
UPDATE table_name SET column=.. WHERE CURRENT OF cursor_name；
DELETE table_name WHERE CURRENT OF cursor_name；

游标FOR循环
游标FOR循环简化了对游标的处理，当使用游标FOR循环时，Oracle会隐含地打开游标、提取游标数据并关闭游标。为了简化程序代码，建议大家使用游标FOR循环。语法如下：
             FOR record_name IN cursor_name  LOOP
             Statement1；
             Statement2；
             …
             END LOOP；
record_name是Oracle隐含定义的记录变量名(不用在定义部分定义，直接使用即可)。
如果在使用游标FOR循环时不需要使用任何游标属性，那么直接在游标FOR循环中使用子查询。语法如下：
             FOR record_name IN (SELECT column1,column2…FROM…[WHERE …])  LOOP
             Statement1；
             Statement2；
             …
             END LOOP；

游標變量(REF CURSOR 游標或者動態游標)
語法：
首先定義REF CURSOR類型
TYPE ref_type_name IS REF CURSOR [RETURN  return_type]；
然后定義游標變量
       Cursor_variable ref_type_name；
當指定RETURN 子句時，其數据類型必須是記錄類型；另外，不能在包內定義游標變量。
动态游标，在运行的时候才能确定游标使用的查询。分类：
强类型（限制）REF CURSOR，规定返回类型
弱类型（非限制）REF CURSOR，不规定返回类型，可以获取任何结果集。
打開游標：
為了引用該游標變量，在打開游標時需要指定其所對應的SELECT語句。當打開游標變量時，會執行游標變量所對應的SELECT語句，并將語句結果存放到游標結果擊集中。語法如下：
     OPEN cursor_variable FOR select_statement；

强类型举例：
declare
--声明记录类型
type emp_job_rec is record(
  employee_id number,
  employee_name varchar2(50),
  job_title varchar2(30)
)；
--声明REF CURSOR，返回值为该记录类型
type emp_job_refcur_type is ref cursor  return emp_job_rec；
--定义REF CURSOR游标的变量
emp_refcur emp_job_refcur_type；
emp_job emp_job_rec； ──定义记录类型的变量
begin
open emp_refcur for
  select e.employee_id,
    e.first_name || ' ' ||e.last_name "employee_name",
    j.job_title
  from employees e, jobs j
  where e.job_id = j.job_id and rownum < 11 order by 1；
fetch emp_refcur into emp_job；
while emp_refcur%found loop
  dbms_output.put_line(emp_job.employee_name || '''s job is ')；
  dbms_output.put_line(emp_job.job_title)；
  fetch emp_refcur into emp_job；
end loop；
end；

[ 本帖最后由 fly-in-the-sky 于 2007-11-22 14:32 编辑 ]

fly-in-the-sky

用REF CURSOR实现BULK功能
1. To speed up INSERT, UPDATE, and DELETE statements, enclose the SQL statement within a PL/SQL FORALL statement instead of a loop construct.
2. To speed up SELECT statements, include the BULK COLLECT INTO clause in the SELECT statement instead of using INTO.


SQL> create table tab2 as select empno ID, ename NAME, sal SALARY from emp where 1=2；
Table created

-------------------------------------------------------
create or replace procedure REF_BULK is
type empcurtyp is ref cursor；
type idlist is table of emp.empno%type；
type namelist is table of emp.ename%type；
type sallist is table of emp.sal%type；

emp_cv empcurtyp；
ids idlist；
names namelist；
sals sallist；

row_cnt number；
begin
open emp_cv for
select empno, ename, sal from emp；
fetch emp_cv BULK COLLECT
INTO ids, names, sals； --BULK COLLECT INTO instead of INTO
close emp_cv；

for i in ids.first .. ids.last loop
dbms_output.put_line('id=' || ids(i) || ' name=' || names(i) ||
' salary=' || sals(i))；
end loop；

forall i in ids.first .. ids.last --FORALL instead of FOR ...LOOP
insert into tab2 values (ids(i), names(i), sals(i))；
commit；

select count(*) into row_cnt from tab2；
dbms_output.put_line('-----------------------------------')；
dbms_output.put_line('The row number of tab2 is ' || row_cnt)；
end REF_BULK；

------------------------------------------------------------

执行：

SQL> exec ref_bulk；

id=7369 name=SMITH salary=800
id=7499 name=ALLEN salary=1600
id=7521 name=WARD salary=1250
id=7566 name=JONES salary=2975
id=7654 name=MARTIN salary=1250
id=7698 name=BLAKE salary=2850
id=7782 name=CLARK salary=2450
id=7788 name=SCOTT salary=3000
id=7839 name=KING salary=5000
id=7844 name=TURNER salary=1500
id=7876 name=ADAMS salary=1100
id=7900 name=JAMES salary=950
id=7902 name=FORD salary=3000
id=7934 name=MILLER salary=1300
-----------------------------------
The row number of tab2 is 14
PL/SQL procedure successfully completed


使用CURSOR表达式(嵌套游标)
从Oracle9i开始，结果集不仅可以包含普通数据，而且允许包含嵌套游标的数据。使用CURSOR的表达式的语法如下：
      CURSOR(subquery)
通过使用CURSOR表达式，开发人员可以在PL/SQL块中处理更加复杂的基于多张表的关联数据。为了在PL/SQL块中取得嵌套游标的数据，需要使用嵌套循环。
For instance:
declare
  type refcursor is ref cursor;
  cursor dept_cursor(no number) is
    select a.dname,cursor(select ename,sal from emp where deptno=a.deptno)      
    from dept a
    where a.deptno=no;
  empcur refcursor;
  v_dname dept.dname%type;
  v_ename emp.ename%type;
  v_sal   emp.sal%type;
begin
  open dept_cursor(&no);
  loop
     fetch dept_cursor into v_dname,empcur;
     exit when dept_cursor%notfound;
     dbms_output.put_line('部门名：'||v_dname);
     loop
        fetch empcur into v_ename,v_sal;
        exit when empcur%notfound;
        dbms_output.put_line('雇员名：'||v_ename||',工资：'||v_sal);
     end loop;
  end loop;
  close dept_cursor;      
end;

output:
      部门名：ACCOUNTING
雇员名：CLARK,工资：2450
雇员名：KING,工资：5000
雇员名：MILLER,工资：1300

[ 本帖最后由 fly-in-the-sky 于 2007-11-22 14:22 编辑 ]

ilovemk

呵呵，不错，顶一个！

sunnily

整理的不错，UP一下

fly-in-the-sky

希望大家可以補充遺漏的地方!!!

vvcolor

支持lz.
好好学习,努力闪人

yunhyang

原帖由 vvcolor 于 2007-11-22 14:45 发表
支持lz.
好好学习,努力闪人

lispo

up up
收藏了

vvcolor

????
oracle 不会
学习下...

fancychou

谢谢楼主 收藏了