注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

有你做翅膀,孤单都是坚强

生命中总要经历曲折,或许是我们不愿意,突然到最后走不通,想重新来过,记录每件小事

 
 
 

日志

 
 
关于我
K24

曾经的生涩,曾经的懵懂,曾经的自恋,都随风去吧。。。

网易考拉推荐
 
 

cursor用法总结  

2015-04-21 17:32:02|  分类: @sql |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

cursor分为三种,一是直接声明为cursor变量,二是首先声明类型再声明变量,三是声明为sys_refcursor。

(1)直接声明

declare

 cursor emp_cur  is select *  from emp;

 emp_record emp%rowtype;

begin

 open emp_cur;

 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;

end;

/

(2)ref cursor:分为强类型(有return子句的)和弱类型,强类型在使用时,其返回类型必须和return中的类型一致,否则报错,而弱类型可以随意打开任何类型。

例如:

强类型

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;

 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; 错误的,类型不一致。

 --close emp_cur;

end;

/

弱类型:

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;

/

(3)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 (job VARCHAR2, max_wage NUMBER) IS

    SELECT * FROM employees  WHERE job_id = job  AND salary > max_wage;

BEGIN

  FOR person  IN  c1('CLERK', 3000)

  LOOP

     DBMS_OUTPUT.PUT_LINE('Name = ' || person.last_name || ', salary = ' ||

                         person.salary || ', Job Id = ' || person.job_id );

  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;

/

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 PACKAGE BODY emp_data  AS

-- emp_cv EmpCurTyp; -- not allowed

PROCEDURE open_emp_cv  IS

  emp_cv EmpCurTyp; -- this is legal

  BEGIN

    OPEN emp_cv  FOR SELECT *  FROM  employees;

  END  open_emp_cv;

END  emp_data;

/

5、嵌套cursor

打开父cursor时,子cursor隐含打开;当

语法格式:cursor(subquery)

A   nested  cursor  is  implicitly  opened when  the  containing  row  is  fetched from  the  parent  cursor.

The  nested  cursor  is  closed  only  when:

The  nested  cursor  is  explicitly  closed by  the  user

The  parent  cursor  is  reexecuted

The  parent  cursor  is  closed

The  parent  cursor  is  canceled

示例;
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 )emps

 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;


 
Oracle中,commit引起Redo Log的物理I/O
为提高效率,尽可能的批量提交

-- 逐行提交
DECLARE
BEGIN
  FOR cur IN (SELECT * FROM user_objects) LOOP
    INSERT INTO t_loop VALUES cur;
    COMMIT;
  END LOOP;
END;

-- 模拟批量提交
DECLARE
  v_count NUMBER;
BEGIN
  FOR cur IN (SELECT * FROM user_objects) LOOP
    INSERT INTO t_loop VALUES cur;
    v_count := v_count + 1;
    IF v_count >= 100 THEN
      COMMIT;
    END IF;
  END LOOP;
  COMMIT;
END;

-- 真正的批量提交,用到嵌套表(数组)的特性
DECLARE
   CURSOR cur IS
     SELECT * FROM t_ref;
   TYPE rec IS TABLE OF t_ref%ROWTYPE;
   recs rec;
 BEGIN
   OPEN cur;
   WHILE (TRUE) LOOP
     FETCH cur BULK COLLECT
       INTO recs LIMIT 100;
     FORALL i IN 1 .. recs.COUNT
       INSERT INTO t VALUES recs (i);
     COMMIT;

     EXIT WHEN cur%NOTFOUND;
   END LOOP;
   CLOSE cur;
 END;


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

引用: http://blog.itpub.net/7607759/viewspace-710233
  评论这张
 
阅读(35)| 评论(0)
推荐

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017