`

ORACLE中的游标汇总

 
阅读更多
游标（Cursor）：用来查询数据库，获取记录集合（结果集）的指针，可以让开发者一次访问一行结果集，在每条结果集上作操作。
    游标可分为：
    <!--[if !supportLists]-->l         <!--[endif]-->静态游标：分为显式（explicit）游标和隐式（implicit）游标。
<!--[if !supportLists]-->l         <!--[endif]-->REF游标：是一种引用类型，类似于指针。----也称为动态游标

 

1、静态游标 


1.1显式游标 


定义格式：   
CURSOR 游标名 ( 参数 )  [返回值类型]  IS    Select 语句 ; 例子
open 游标;
loop  fetch  游标  into 游标变量;
exit   when 游标%notfound;
end loop;
close 游标;
cursor emp_cur ( p_deptid in number) is
set serveroutput on
declare
select * from employees where department_id = p_deptid;

 

l_emp employees%rowtype;
begin
 dbms_output.put_line('Getting employees from department 30');
open emp_cur(30);
 loop
  fetch emp_cur into l_emp;
  exit when emp_cur%notfound;
  dbms_output.put_line('Employee id '|| l_emp.employee_id || ' is');
  dbms_output.put_line(l_emp.first_name || ' ' || l_emp.last_name);
emp_cur;
 end loop;
 close

 

 dbms_output.put_line('Getting employees from department 90');
open emp_cur(90);
 loop
 fetch emp_cur into l_emp;
  exit when emp_cur%notfound;
  dbms_output.put_line('Employee id '|| l_emp.employee_id || ' is');
  dbms_output.put_line(l_emp.first_name || ' ' || l_emp.last_name);
 end loop;
 close emp_cur;
end;
/

 
1.2隐式游标 


不用明确建立游标变量，分两种：
1．在PL/SQL中使用DML语言，使用ORACLE提供的名为“SQL”的隐示游标。
举例：
declare
begin
 update departments    set  department_name=department_name where 1=2; dbms_output.put_line('update '|| sql%rowcount ||' records');
end;
2．CURSOR FOR LOOP，用于for  in loop 语句
for 游标变量 in 游标 loop   end loop;用于静态游标中，不能用于动态游标,且不需要显示的打开、关闭、取数据、测试数据的存在、定义数据的变量等等。
举例：
游标FOR循环，简化游标操作my_dept_rec 不需要声明
declare
begin
 for my_dept_rec in ( select department_name, department_id from epartments)
 loop
  dbms_output.put_line(my_dept_rec.department_id || ' : ' || my_dept_rec.department_name);
 end loop;
end;
/

 

1.3静态游标常用属性：
显式游标属性： 
%FOUND：变量最后从游标中获取记录的时候，在结果集中找到了记录。
%NOTFOUND：变量最后从游标中获取记录的时候，在结果集中没有找到记录。
%ROWCOUNT：当前时刻已经从游标中获取的记录数量。
%ISOPEN：是否打开。
 
隐式游标属性:  
 

  
SQL%FOUND
SQL%NOTFOUND 

在执行任何DML语句前SQL%FOUND和SQL%NOTFOUND的值都是NULL,在执行DML语句后，SQL%FOUND的属性值将是： 


. TRUE :INSERT 


. TRUE :DELETE和UPDATE，至少有一行被DELETE或UPDATE. 


. TRUE :SELECT INTO至少返回一行 

当SQL%FOUND为TRUE时,SQL%NOTFOUND为FALSE.
 

SQL%ROWCOUNT 


   在执行任何DML语句之前，SQL%ROWCOUNT的值都是NULL,对于SELECT            INTO语句，如果执行成功，SQL%ROWCOUNT的值为1,如果没有成功，SQL%ROWCOUNT的值为0，同时产生一个异常NO_DATA_FOUND.


SQL%ISOPEN 

 SQL%ISOPEN是一个布尔值，如果游标打开，则为TRUE, 如果游标关闭，则为FALSE.对于隐式游标而言SQL%ISOPEN总是FALSE，这是因为隐式游标在DML语句执行时打开，结束时就立即关闭。
Declare
 
 Cursor emps is
 Select * from employees where rownum<6 order by 1;
 
 emp employees%rowtype;
 Row number :=1;
Begin
 Open emps; 
 Fetch emps into emp; 
 Loop
  If emps%found then
   Dbms_output.put_line('Looping over record '||row|| ' of ' || emps%rowcount);
   Fetch emps into emp;
   Row := row + 1;
  Else if emps%notfound then
   Exit; 
  End if;
 End loop;
 
 If emps%isopen then
  Close emps;  
 End if;
End;
/

 

 

显式和隐式游标的区别： 


尽量使用隐式游标，避免编写附加的游标控制代码（声明，打开，获取，关闭），也不需要声明变量来保存从游标中获取的数据。

 


2、REF CURSOR游标
动态游标，在运行的时候才能确定游标使用的查询。可以分为：
<!--[if !supportLists]-->l         <!--[endif]-->强类型（限制）（Strong REF CURSOR），规定返回类型
<!--[if !supportLists]-->l         <!--[endif]-->弱类型（非限制）（Weak REF CURSOR），不规定返回类型，可以获取任何结果集。
定义格式：
TYPE ref_cursor_name IS REF CURSOR [RETURN return_type]
   cursor   ref_cursor_name  
 

例如：

open cursor for  select ......from (多个open for 同时用时 ，不需要有关闭游标的语句)

Declare
  
Type refcur_t is ref cursor;
 Type emp_refcur_t is ref cursor return employee%rowtype;
Begin
 Null;
End;
/

 

强类型举例： 


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_tpe;
 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;
/

 
指定了retrun 类型，CURSOR变量的类型必须和return 类型一致。
例子：
CREATE OR REPLACE PACKAGE emp_data AS
TYPE empcurtyp IS REF CURSOR RETURN emp%rowtype;
--定义Strong REF CURSOR
PROCEDURE open_emp_cv(emp_cv IN OUT empcurtyp, choice INT);
--根据不同的choice选择不同的CURSOR
PROCEDURE retrieve_data(choice INT);
--通过调用procedure open_emp_cv，返回指定的结果集。
END emp_data;

 

 

CREATE OR REPLACE PACKAGE BODY emp_data AS



 

PROCEDURE open_emp_cv(emp_cv IN OUT empcurtyp, choice INT) IS
--emp_cv作为传入/传出的CURSOR PARAMETER
BEGIN


IF choice = 1 THEN


OPEN emp_cv FOR  SELECT * FROM emp WHERE empno < 7800;
ELSIF choice = 2 THEN


OPEN emp_cv FOR SELECT * FROM emp WHERE SAL < 1000;
ELSIF choice = 3 THEN


OPEN emp_cv  FOR SELECT * FROM emp WHERE ename like 'J%';
END IF;


END;



 

PROCEDURE retrieve_data(choice INT) IS


return_cv empcurtyp;
--定义传入open_emp_cv的CURSOR变量
return_row emp%ROWTYPE;
invalid_choice EXCEPTION;
BEGIN


--调用 procedure OPEN_EMP_CV
open_emp_cv(return_cv, choice);
dt> 
 
IF choice = 1 THEN


DBMS_OUTPUT.PUT_LINE('EMPLOYEES with empno less t han7800');
ELSIF choice = 2 THEN


DBMS_OUTPUT.PUT_LINE('EMPLOYEES with salary less than 1000');
ELSIF choice = 3 THEN


DBMS_OUTPUT.PUT_LINE('EMPLOYEES with name starts with ''J''');
ELSE


RAISE invalid_choice;
END IF;



 

LOOP 


FETCH return_cv   INTO return_row;
EXIT WHEN return_cv%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(return_row.empno || '--' || return_row.ename || '--' ||
return_row.sal);


END LOOP;



 

EXCEPTION


WHEN invalid_choice THEN
DBMS_OUTPUT.PUT_LINE('The CHOICE should be in one of (1,2,3)!');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Errors in procedure RETRIEVE_DATA!');
END;

 

END emp_data;



 

 

执行： 


SQL> EXEC emp_data.retrieve_data(1);


SQL> EXEC emp_data.retrieve_data(2);


SQL> EXEC emp_data.retrieve_data(3);


SQL> EXEC emp_data.retrieve_data(34);



 

使用Weak REF CURSOR例子 


create or replace procedure open_cv(choice IN INT, return_cv OUT SYS_REFCURSOR) is


--参数return_cv为weak REF CURSOR,利用SYS_CURSOR来定义
 
begin


if choice = 1 then


open return_cv for 'select * from emp';
elsif choice = 2 then


open return_cv for 'select * from dept';
end if;


end open_cv;



 

 

CREATE or replace procedure retrieve_data(choice IN INT) is


emp_rec emp%rowtype;


dept_rec dept%rowtype;


return_cv SYS_REFCURSOR;


invalid_choice exception;

 

BEGIN


if choice=1 then


dbms_output.put_line('employee information');
open_cv(1,return_cv); --调用procedure open_cv;
loop
fetch return_cv into emp_rec;
exit when return_cv%notfound;
dbms_output.put_line(emp_rec.empno||'-'||emp_rec.ename||'-'||emp_rec.sal);
end loop;
elsif choice=2 then


dbm_output.put_line('department information');
open_cv(2,return_cv);

 

loop
fetch return_cv into dept_rec;
exit when return_cv%notfound;
dbms_output.put_line(dept_rec.deptno||'-'||dept_rec.dname||'-'||dept_rec.loc);
end loop;
else


raise invalid_choice;
end if;



 

exception


when invalid_choice then
dbms_output.put_line('The CHOICE should be one of 1 and 2!');
when others then


dbms_output.put_line('Errors in procedure retrieve_data');
END retrieve_data;


 

 

执行： 


SQL> exec retrieve_data(1);


SQL> exec retrieve_data(2);

 


 

用REF CURSOR实现BULK功能 


1. 可以加速INSERT, UPDATE, DELETE语句的执行，也就是用FORALL语句来替代循环语句。 


2. 加速SELECT，用BULK COLLECT INTO 来替代INTO。

 

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


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;
--将字段成批放入变量中，此时变量是一个集合
close emp_cv;

 

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

 

FORALL  i  IN  ids.first .. ids.last
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;


 

 


 


 

3、cursor 和 ref cursor的区别 


从技术底层看，两者是相同的。普通plsql cursor在定义时是“静态”的。而
Ref cursors可以动态打开。
例如下面例子：
Declare
typerc is ref cursor;
cursor c is select * from dual;

 

l_cursor rc;
begin
if ( to_char(sysdate,'dd') = 30 ) then
       open l_cursor for 'select * from emp';
elsif ( to_char(sysdate,'dd') = 29 ) then
       open l_cursor for select * from dept;
else
       open l_cursor for select * from dual;
end if;
open c;
end;
/ 
分享到：
| PLSQL开发笔记和小结
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    oracle-游标使用汇总.doc

    Oracle数据库中的游标是处理查询结果集的一种重要机制，尤其在需要逐行处理多行数据时。游标允许我们按照需要遍历查询结果，并对每一行进行操作。本篇文章将详细解析Oracle数据库中游标的使用方法。 首先，游标分为...

    游标分类汇总行转列oracleplsql

    在Oracle PL/SQL中，游标是一种非常重要的机制，用于处理从数据库中检索出来的结果集。通过使用游标，可以逐行地读取数据并进行相应的处理，这在需要对查询结果执行复杂逻辑时特别有用。 #### 2. 创建游标 在PL/SQL...

    oracle存储过程使用游标对多表操作例子

    本文将深入探讨如何在Oracle存储过程中使用游标进行多表操作，具体通过一个示例来展示这一过程。 ### 标题与描述分析 标题：“oracle存储过程使用游标对多表操作例子”直接指出了文章的主题是关于在Oracle环境下，...

    Oracle 遍历游标的四种方式汇总（for、fetch、while、BULK COLLECT）

    在Oracle数据库中，遍历游标是处理查询结果集的一种常见方法，特别是在编写存储过程或PL/SQL块时。游标允许我们逐行处理查询结果，而不会一次性加载所有数据，这对于大型数据集来说非常高效。以下是Oracle遍历游标的...

    oracle笔记(存储过程函数触发器游标流程控制等)

    这份"oracle笔记(存储过程函数触发器游标流程控制等)"涵盖了Oracle数据库管理中的关键知识点，包括但不限于以下几个方面： 1. **存储过程与函数**：存储过程是预编译的SQL语句集合，用于执行特定任务。它们可以提高...

    Oracle Exception汇总(自定义Oracle异常)

    以上就是Oracle数据库中一些常见的异常及对应的处理方法。在编写PL/SQL代码时，充分理解并合理处理这些异常是确保程序稳定运行的关键。通过预定义异常处理和良好的编程实践，可以提高代码的健壮性和可靠性。

    SQLServer与Oracle语法差异汇总.docx

    在IT行业中，数据库管理系统是核心组件之一，Oracle和SQL Server是两种广泛应用的关系型数据库系统。它们在语法上有诸多差异，这些差异主要体现在存储过程、自定义函数、游标、变量、赋值、语句结束符以及大小写敏感...

    ORACLE 10 知识汇总

    Oracle 10 是一款强大的关系型数据库管理系统，其在企业级应用中有着广泛的应用。本文将对Oracle 10中的核心知识点进行详细阐述。 一、Oracle 基本概念 Oracle 是一个对象关系型数据库管理系统（ORDBMS），它具备...

    oracle常用SQL语句(汇总版).zip

    以上内容只是Oracle SQL语句的冰山一角，实际应用中还有更复杂的查询技巧、事务管理、游标、递归查询等。在"oracle常用SQL语句(汇总版)"文档中，读者可以找到更多关于这些主题的详细信息，对于学习和掌握Oracle SQL...

    Oracle经典SQL语句

    ORACLE经典语句汇总 -- 字符串左填充和右填充，默认填充空格 -- 产生1~99行数据，少于一位则补0 -- 刪除相同行 -- 随机数 -- 产生业务流水号 -- 查询某张表中有哪些字段 -- 自循环表中 由叶子节点查父节点 -- 查子...

    Oracle语句优化规则汇总.pdf

    这篇文档“Oracle语句优化规则汇总.pdf”很可能提供了关于如何提高Oracle数据库查询效率、减少资源消耗的关键信息。以下是一些可能涵盖在文档中的关键知识点： 1. **索引优化**： - 使用合适的索引类型，如B树索引...

    oracle 函数大全 参考函数 手册 速查 chm格式

    10. 游标和查询相关函数：如CURSOR（定义游标）、FETCH（从游标获取数据）以及嵌套查询中的聚合函数。 11. 连接函数：如CONNECT_BY_ROOT（找到层次查询的根节点）、START_WITH（指定层次查询的起始节点）等，处理...

    oracle_hint教程汇总

    Oracle Hint是Oracle数据库系统中的一种特性，它允许数据库管理员或开发人员通过在SQL语句中添加特定的提示来指导查询优化器如何执行查询。Hint机制为优化查询性能提供了额外的控制，尤其是在面对复杂查询和性能瓶颈...

    Oracle期末考试试题(卷）与答案解析.doc

    在 Oracle 中，GROUP BY 子句用于对结果集进行分组和汇总，它可以与HAVING 子句结合使用，以对分组后的结果进行过滤。HAVING 子句用于对分组后的结果进行过滤，而不是对原始数据进行过滤。 在 Oracle 中，SYS 用户...

    Oracle常用语句-总结文档汇总

    "Oracle常用语句-总结文档汇总"提供了丰富的资源，涵盖了SQL语句的基础到进阶应用，包括列行转换、SQL性能优化等多个关键知识点。 首先，列行转换是数据处理中常见的需求，Oracle提供了多种方法来实现这一操作。...

    大数据Oracle理论+SQL+面试问题汇总+BI理论

    《大数据Oracle理论+SQL+面试问题汇总+BI理论》是一门综合性的课程，旨在帮助大数据行业、数据库开发人员以及BI开发人员深入理解Oracle技术，并掌握关键的SQL技能和面试技巧。课程内容涵盖广泛，旨在提升专业技能，...

    超详细Oracle教程

    20. 游标、函数：讲解了在PLSQL中如何使用游标进行复杂数据操作，以及创建自定义函数的方法。 21. 存储过程：介绍了存储过程的创建、调用和管理，存储过程是一组为了完成特定功能的SQL语句集。 22. 触发器：讲解了...

    XML Oracle Servlet 复习汇总(S2)

    隐式游标是Oracle在执行SQL语句时自动使用的，无需程序员显式声明。创建用户和授权涉及数据库安全管理和权限控制。 【Servlet知识点】 Servlet是Java Web开发中用于扩展服务器功能的Java类。EL（Expression ...

    oracle常用函数汇总.pdf

    本文主要汇总了Oracle中的常用SQL语句，包括数据控制语句（DML）和数据定义语句（DDL）中的关键函数和操作。 一、数据控制语句（DML） 1. INSERT语句： - 插入单行数据：`INSERT INTO 表名(字段名1, 字段名2, ......

Magicbox
Global site tag (gtag.js) - Google Analytics