Oracle-游标的种类、语法、属性及使用,NO_DATA_FOUND和%NOTFOUND的区别

原创 2016年05月31日 23:17:01

一、游标的分类

Oracle数据库提供了俩种游标类型,分别为静态游标和动态游标,而静态游标又分为隐士游标和显式游标,动态游标分为弱类型和强类型俩种。


二、静态游标

1、显式游标
1).声明游标语法:

cursor 游标名 [(游标输入参数1[,游标输入参数2]…)]
[return 返回类型] is 查询语句

游标名:指定义的游标名称,一般采用cursor_名称这种命名格式。
游标输入参数:为游标指定输入参数,注意指定参数类型时,不能约束长度,比如NUMBER(10)是错误的。
返回类型:表示游标提取的一行数据的类型
查询语句:游标所使用的查询语句
游标参数和返回类型用[]圈起来表示是可以省略的条件,声明游标时可以不加这俩个参数

2).打开游标语法:
open 游标名 [所有定义的游标输入参数]

3).提取游标语法:
fetch 游标名 into 接收变量

4).关闭游标语法:
close 游标名

使用示例:

--检索EMP表中的所有JOB为MANAGER的雇员信息
DECLARE
  /*声明游标、(游标输入参数变量为VAR_JOB)可选项*/
  CURSOR CUR_EMP(VAR_JOB IN VARCHAR2:='SALESMAN') IS
    /*游标所使用的查询语句*/
    SELECT EMPNO, ENAME, SAL FROM EMP WHERE JOB = VAR_JOB;
  /*声明一个RECORD类型的记录变量*/
  TYPE RECORD_EMP IS RECORD(
    VAR_EMPNO EMP.EMPNO%TYPE,
    VAR_ENAME EMP.ENAME%TYPE,
    VAR_SAL   EMP.SAL%TYPE);
  EMP_ROW RECORD_EMP;
BEGIN
  /*打开游标,指定输入参数值为MANAGER*/
  OPEN CUR_EMP('MANAGER');
  /*将游标指向结果集第一行数据并存入RECORD记录变量*/
  FETCH CUR_EMP
    INTO EMP_ROW;
  /*如果游标有数据就循环*/
  WHILE CUR_EMP%FOUND LOOP
    DBMS_OUTPUT.PUT_LINE('雇员编号:' || EMP_ROW.VAR_EMPNO || ' 雇员姓名:' ||
                         EMP_ROW.VAR_ENAME || ' 雇员薪水:' || EMP_ROW.VAR_SAL);
    /*将游标指向结果集下一条数据*/
    FETCH CUR_EMP
      INTO EMP_ROW;
  END LOOP;
  /*关闭游标*/
  CLOSE CUR_EMP;
END;

--另一种方式,使用%ROWTYPE类型
DECLARE
  CURSOR CUR_EMP IS
    /*使用FETCH+%ROWTYPE查询这里的查询字段必须和表中字段顺序及数量一致*/
    SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO FROM EMP;
  /*定义一个%ROWTYPE接收变量*/
  VAR_EMP_TYPE EMP%ROWTYPE;
BEGIN
  OPEN CUR_EMP;
  FETCH CUR_EMP
    INTO VAR_EMP_TYPE;
  WHILE CUR_EMP%FOUND LOOP
    DBMS_OUTPUT.PUT_LINE('雇员编号:' || VAR_EMP_TYPE.EMPNO || ' 雇员姓名:' ||
                         VAR_EMP_TYPE.ENAME || ' 雇员薪水:' ||
                         VAR_EMP_TYPE.SAL);
    FETCH CUR_EMP
      INTO VAR_EMP_TYPE;
  END LOOP;
  CLOSE CUR_EMP;
END;

显示游标具有以下属性:

这里写图片描述

2、隐式游标

PL/SQL为所有SQL数据操纵语句(包括返回一行的SELECT)隐士声明游标,称为隐式游标的原因是用户不能直接命名和控制此类游标。当用户在PL/SQL中使用数据操纵语言(DML)时,Oracle预先定义一个名为SQL的隐士游标,通过检查隐式游标的属性可以获取与最近执行的SQL语句相关的信息。

隐式游标属性表如下:

这里写图片描述

使用示例:

--将EMP表中SALESMAN的工资加100,使用隐士游标sql的%rowcount属性输出增加工资的员工数量

DECLARE

BEGIN
  UPDATE EMP SET SAL = SAL + 100 WHERE JOB = 'SALESMAN';
  IF SQL%NOTFOUND THEN
    DBMS_OUTPUT.PUT_LINE('没有符合条件的雇员');
  ELSE
    DBMS_OUTPUT.PUT_LINE('上调了:' || SQL%ROWCOUNT || '个雇员的工资');
  END IF;
END;

三、动态游标

静态游标在使用时,使用的查询语句已经确定,如果需要在运行的时候动态的绝对执行哪种查询,可以使用REF游标(动态游标)和游标变量。

1).声明游标的语法
声明REF游标需要2个步骤:声明REF的游标类型和声明使用此类型的游标变量。

声明游标类型的语法:
type 游标类型名称 is ref cursor
[return 游标返回值类型]

游标类型名称:定义的一个游标变量的类型,一般采用ref_type_类型名 这种格式。
游标返回值类型:可选项,定义游标变量的返回值类型,注意必须为记录变量。

REF游标分为强类型和弱类型俩种,在定义游标变量类型时,如果指定了游标变量的返回值类型,那么就是强类型,如果没指定,就是弱类型。并且一旦指定了返回值类型,在打开游标时,绑定的查询结果的返回集一定是return中定义的类型.

声明游标变量的语法:
游标变量名称 游标类型名称

2).打开游标的语法:
open 游标变量名 for 查询语句

3).提取游标,同显式游标。
4).关闭游标同显式游标。

使用示例:

--弱类型
--将薪水低于3000的雇员薪水增加500,增加后最高不超过3000
DECLARE
  TYPE REF_CURSOR_TYPE IS REF CURSOR; --声明一个弱类型的动态游标类型
  REF_CURSOR REF_CURSOR_TYPE; --定义一个游标为声明的弱类型游标
  V_SAL      EMP.SAL%TYPE;--声明一个变量用来接收雇员薪水
  V_EMPNO    EMP.EMPNO%TYPE;--声明一个变量用来接收雇员编号
BEGIN
  OPEN REF_CURSOR FOR
    SELECT SAL, EMPNO FROM EMP;--打开游标并指定使用的SQL语句
  LOOP
    FETCH REF_CURSOR
      INTO V_SAL, V_EMPNO;--将游标指向一行数据并给变量赋值
    EXIT WHEN REF_CURSOR%NOTFOUND;--当游标无数据时退出
    IF V_SAL < 3000 THEN--薪水低于3000进入IF,这个IF最后会执行更新SQL
      IF V_SAL >= 2500 THEN--薪水低于3000又大于2500进入这个IF
        V_SAL := 3000;
      ELSE
        V_SAL := V_SAL + 500;
      END IF;--结束一个IF,一个IF对应一个END IF;
      UPDATE EMP SET SAL = V_SAL WHERE EMPNO = V_EMPNO;--更新雇员薪水
    END IF;--结束最外层IF
  END LOOP;--结束LOOP循环
  CLOSE REF_CURSOR;--关闭游标
END;

--强类型
--查询所有雇员姓名
DECLARE
  TYPE EMP_REF_CURSOR IS REF CURSOR RETURN EMP%ROWTYPE;--声明一个强类型(指定返回类型)的动态游标
  REF_CURSOR   EMP_REF_CURSOR;--定义一个声明的强类型游标
  V_EMP_RECORD EMP%ROWTYPE;--定义一个接收变量
BEGIN
  OPEN REF_CURSOR FOR
    SELECT * FROM EMP;
  LOOP
    FETCH REF_CURSOR
      INTO V_EMP_RECORD;
    EXIT WHEN REF_CURSOR%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(V_EMP_RECORD.ENAME);
  END LOOP;
  CLOSE REF_CURSOR;
END;

四、通过for语句循环游标

使用for语句循环游标可以简化显式游标的处理代码。for语句循环游标隐式的打开游标,自动从活动集获取行、然后在处理完所有行时关闭游标。循环游标自动创建%ROWTYPE类型的变量并将此变量用做记录索引。

语法:
for 记录变量 in 游标变量
loop
//操作记录变量
end loop;

记录变量是PL/SQL声明的记录变量,此变量的属性声明为%ROWTYPE类型,作用域在for循环之内,for循环之外不能访问此变量。

for语句循环游标特性:
1、在从游标中提取了所有记录之后自动终止。
2、提取和处理游标中的每一条记录。
3、如果在提取之后%NOTFOUND属性返回TRUE,则终止循环。如果未返回行,则不进入循环。

使用示例:

--查询所有雇员姓名
DECLARE
  CURSOR EMP_CURSOR IS SELECT * FROM EMP;
BEGIN
  /*使用for语句循环游标无需手动打开/关闭游标*/
  FOR V_EMP_RECORD IN EMP_CURSOR LOOP
    DBMS_OUTPUT.PUT_LINE(V_EMP_RECORD.ENAME);
  END LOOP;
END;

五、NO_DATA_FOUND和%NOTFOUND的区别

1、SELECT…INTO语句返回0条和多条记录时触发NO_DATA_FOUND。
2、当UPDATE或DELETE语句的WHERE子句未找到时触发%NOTFOUND。
3、在提取循环中要用%NOTFOUND或%FOUND来确定循环的退出条件,不要使用NO_DATA_FOUND。

版权声明:本文为博主原创文章,转载请注明出处。

游标的属性

 (1) %ISOPEN  打开属性 布尔型 打开为TRUE               判断'光标'是否打开如未打开则打开'光标'         IF NOT(CORSOR_NAME%...
  • caixingyun
  • caixingyun
  • 2013年12月15日 21:31
  • 1531

游标使用2-常用属性及引用游标

隐式游标 四个常用的属性 Sql%FOUND SQL%NOTFOUND SQL%ISOPEN SQL%ROWCOUNTDeclare Dept_no_number(4) :=50; B...
  • aoerqileng
  • aoerqileng
  • 2016年06月19日 09:59
  • 913

NO_DATA_FOUND 和 %NOTFOUND的区别

SELECT … INTO 语句触发 NO_DATA_FOUND; 当一个显式游标的WHERE子句未找到时触发%NOTFOUND; 当UPDATE或DELETE 语句的WHERE 子句未找到时,触...
  • HelloCqk1
  • HelloCqk1
  • 2016年07月17日 00:25
  • 980

oracle游标的使用方法和属性

一.游标:是一种 容器,存储SQL语句影响行数。 二. 游标类型: 隐式游标,显示游标,REF游标。其中,隐式游标和显示游标属于静态游标(运行前将游标与SQL语句关联),REF游标属于动态游标(运行时...
  • yi3040
  • yi3040
  • 2015年01月12日 16:45
  • 480

存储过程游标的详解

1,什么是游标?  ①从表中检索出结果集,从中每次指向一条记录进行交互的机制。      ②关系数据库中的操作是在完整的行集合上执行的。   由SELECT 语句返回的行集合包括满足该语句的W...
  • gogogo_zzz
  • gogogo_zzz
  • 2016年09月21日 15:29
  • 859

数据库之【游标使用详解篇】

***************************** 数据库之游标使用详解篇 ***************************** 游标(Cursor):用来查询数据库,...
  • next_sun
  • next_sun
  • 2012年04月10日 23:30
  • 2155

选择合适的游标类型

背景:定义游标的时候, 如果不指定STATIC 关键字的时候, 则默认定义的游标是动态(DYNAMIC) 的动态(DYNAMIC) 游标会反映在滚动游标时对结果集内的各行所做的所有数据更改。行的数据值...
  • zjcxc
  • zjcxc
  • 2007年05月12日 17:27
  • 7932

PL/SQL之游标属性详解

游标属性%isopen,%found,%notfound,%rowcount,以及简单循环,while循环使用
  • yhl_jxy
  • yhl_jxy
  • 2016年02月28日 12:12
  • 1200

Oracle数据库疑问:函数出现no_data_found不报异常

今天做一个功能的时候,发现Oracle数据库的一个令人很奇怪的点: 在sql里面调用的函数,如果函数体出现no_data_found的报错居然不会显式报错!而是终止执行,不报错,并返回null值。真是...
  • samt007
  • samt007
  • 2016年10月25日 15:52
  • 679

简述游标的使用

游标的使用可以简单归类为5个步骤: 1.定义游标 2.打开游标 3.使用游标 4.关闭游标 5.释放游标 如下是游标简单实用的示例: --1.定义游标 DECLARE test_...
  • Wikey_Zhang
  • Wikey_Zhang
  • 2017年07月10日 12:10
  • 465
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Oracle-游标的种类、语法、属性及使用,NO_DATA_FOUND和%NOTFOUND的区别
举报原因:
原因补充:

(最多只允许输入30个字)