精通Oracle核心技术和项目实战之游标.pptx

第13章 游标 游标是在关系数据库中用来操作查询出来 的数据集。引入游标也是为了更加方便地 访问数据。游标在操作数据库时经常用到 ，它使用相对灵活，容易理解和操作。本 章将主要学习游标的概念、种类、以及如 何创建和使用游标。 13.1 什么是游标 游标从概念上讲基于数据库的表返回结果 集。它指示结果集中的当前位置 ，就像计 算机屏幕上的光标指示当前位置一样，“游 标”由此得名。 13.1.1 游标概念 SQL是面向集合的，其结果一般是多条记 录。而PL/SQL的变量一般是标量，其一组 变量一次只能存放一条记录。所以仅仅使 用变量并不能完全满足SQL语句向应用程序 输出数据的要求。为此，在PL/SQL中引入 了游标的概念。 13.1.1 游标概念 1.基本原理 在PL/SQL块中执行select、insert、update、 delete语句时，Oracle会在内存中为其分配 一个缓冲区。游标是指向该区的一个指针 ，或是一种结构化数据类型。它为应用程 序提供一种对结果集中的每一行数据分别 进行单独处理的方法。 13.1.1 游标概念 可以将游标形象地看成一个变动的光标。它实际上是一个 指针，在一段Oracle存放数据查询结果集或数据操作结果 集的内存中，这个指针可以指向结果集中的任何一条记 录。通过游标就可以得到它所指向的数据，但初始时它指 向首记录。这种模型很像编程语言中的数组。如图所示。 13.1.2 游标种类 Oracle中游标分为静态游标和动态（REF）游标两类。其 中，静态游标就像一个数据快照，打开游标后的结果集是 对数据库的一个备份，数据不随对表执行DML操作后而改 变。静态游标又分为显式游标和隐式游标两类。动态游标 在下面章节中讲解。游标种类如图所示。 13.2 显式游标 显式游标在PL/SQL编程当中有着重要的作 用，通过显式游标用户可以操作返回的数 据，使得一些在编程语言中复杂的功能更 容易实现。 13.2.1 创建显式游标步骤 显示游标的使用顺序可以明确地分成声明 游标、打开游标、读取数据和关闭游标4个 步骤。具体步骤如下。 1.声明游标 声明游标主要是用来给游标命名并且使得 游标关联一个查询。 13.2.1 创建显式游标步骤 声明游标的具体语法如图所示。 【示例13-1】下面我们为customersnew表创 建一个名为cus_cur的简单游标. 13.2.1 创建显式游标步骤 （1）声明列变量 游标的好处之一是可以将访问的对象细化到记 录的列。因此，在声明了游标变量之后，往往 需要声明相应的列变量，以备数据访问之需。 【示例13-2】在示例13-1中所声明的游标变量 含有两列：customer_id、cust_first_name。现 欲分别为两列的访问预备两个变量，那么可以 利用declare命令。 【示例13-3】声明变量时，除了使用特定的数 据类型来声明变量，还可以直接利用列的数据 类型来声明变量类型。 13.2.1 创建显式游标步骤 （2）声明行变量 Oracle不仅可以利用列类型来定义变量，而且 可以直接声明一个行类型的变量，来达到为每 个列统一声明变量声明行类型的语法如图所 示。 【示例13-4】我们可以针对表customersnew的 行声明一个行变量。 13.2.1 创建显式游标步骤 2.打开游标 游标中对数据的任何操作都是建立在游标被打开的 前提下。游标一旦打开，其结果集都是静态的。也 就是说结果集此时不会反映出数据库中对数据进行 的增加、删除、修改操作。打开游标语法如图所 示。 【示例13-5】打开游标cus_cur。 13.2.1 创建显式游标步骤 3.读取数据 游标打开后，就可以取出游标中的数据，并对其进行处理 了。从游标中取出数据的命令是fetch。fetch命令把游标指 向当前记录赋给PL/SQL声明的变量。它只能读出指针当 前的记录，一次取出一行数据。正常情况下，fetch要和循 环语句一起使用，这样指针会不断前进，直到某个条件不 符合条件而退出循环。具体fetch命令的语法如图所示。 13.2.1 创建显式游标步骤 4.关闭游标 游标在使用完后，应该及时关闭，释放它 所占用的内存空间。关闭游标后，结果集 中的数据将不能做任何操作，具体语法如 图所示。 13.2.1 创建显式游标步骤 学了创建显式游标的步骤，总结得到显式 游标操作过程如图所示。 13.2.1 创建显式游标步骤 创建返回单条记录显式游标的语法如图所示。 【示例13-6】下面我们为customersnew表创建一个名为 cus_cur的简单游标，并从游标中提取出单行数据。 【示例13-7】对上面的PL/SQL块进行修改，在PL/SQL块 中使用记录变量。 13.2.2 游标中的loop语句 通常显式游标提取的数据不会只有一条，而是多条记录， 这时如果只用一条fetch语句仅能取到游标中的一行数据。 这样就需要一个遍历结果集的方式，而loop语句就能实现 该功能。语法如图所示，下面示例将演示在游标中如何使 用loop语句。 13.2.2 游标中的loop语句 【示例13-8】下面我们为customersnew表创 建一个名为cus_loop_cur的游标，并从游标 中提取出city是Philadelphia的顾客数据。 【示例13-9】上个示例中，从游标中提取 出city是Philadelphia的顾客数据，除了使用 while方式结束循环，现在还可以用另外一 种形式的循环处理游标。 13.2.3 使用bulk collect和for语句的 游标 游标中通常使用fetch into 语句提取数据，这种方式是单 条数据提取，但是在数据量很大的情况下执行效率不是很 理想。而此时引用fetch bulk collect into语句可以批量提取 数据，在数据量大的情况下它的执行效率比单条提取数据 的高。语法如图所示。 13.2.3 使用bulk collect和for语句的 游标 【示例13-10】修改上一个示例的脚本，实 现使用批量的方式从游标中查询数据。 13.2.4 使用for loop语句 游标中的数据一般是通过循环方式来处理的。在上面的例 子中用常规的循环方法进行处理。PL/SQL还提供了一种 更简便的方法处理游标，这种方法利用for循环，逐行处理 游标中的行。for loop不需要声明变量，它可以直接提出行 对象类型的数据。其语法结构如图所示。 13.2.4 使用for loop语句 【示例13-11】上个示例中，从游标中提取 出city是Philadelphia的顾客数据，采用的是 while设置的loop循环，现在还可以用for循 环处理游标。 13.2.5 为游标传递参数 如果在声明游标时，select语句都没有where子句。但是在 实际的情况中，可能要根据具体需求查询不同的数据。为 了通过游标对数据进行更加灵活的处理，可以在使用显示 游标时，指定参数。这些参数可以用在where子句中。在 打开游标时，指定实际的参数值，这样游标在每次打开时 ，可以根据不同的实际参数值，返回所需的不同数据。一 般情况下，指定参数包括参数的顺序和参数的类型，如图 所示。 13.2.5 为游标传递参数 【示例13-12】在表customersnew中，含有 存储赊销限额的列credit_limit。现欲获得顾 客赊销限额大于某一确定值的顾客信息， 则可以为搜寻时的where条件指定参数。 【示例13-13】对于上述示例，打开游标时 ，采用参数传递的方式。 13.2.6 显式游标的属性 通过游标，可以处理数据。在使用游标时，必须考 虑各种特殊情况。如果select语句没有返回结果，游 标是空的，这时fetch语句将取不到数据。利用游标 的属性可以了解游标当前的状态，防止各种意外情 况的发生。显式游标有4个属性： %isopen：判断游标是否打开, 打开则返回true, 否则 返回false。 %found：判断当前游标最近的一次fetch是否取到数 据。是则返回true, 否则返回false。 %notfound：与%found恰好相反。 %rowcount：判断自游标打开以来，到目前为止， 用fetch命令获取的行数，并非所有总记录数。 13.2.6 显式游标的属性 下面用几个例子来了解一下以上4个属性的 具体使用方法。 1.%isopen属性 【示例13-14】%isopen的使用。 13.2.6 显式游标的属性 2.%found属性 %found一般在多条记录中使用，以下以 %found属性的用法为例大家演示一下。 【示例13-15】%found的使用。 13.2.6 显式游标的属性 3.%rowcount属性 %rowcount属性能使得不必抽取所有记录行 就可以中断游标操作，以下用loop语句来演 示一下它的作用。 【示例13-16】使用%rowcount为emp表提取 10条记录，具体效果如图13-30所示。 【示例13-17】把customersnew表中编号为 983的顾客的city设置为“taiyuan”。 13.3 隐式游标 隐式游标是数据库服务器定义的一种游 标。如果在PL/SQL程序中使用select语句或 者DML语句进行操作，数据库服务器将自 动打开一个隐式游标，用来存放该语句的 执行结果。所以这种游标无需定义，也不 需打开和关闭。隐式游标始终存放最近一 条语句的执行结果。本小节将介绍隐式游 标的特点及属性。 13.3.1 隐式游标的特点 每当执行select或DML语句时，PL/SQL会 自动打开一个隐式游标。隐式游标不受用 户的控制，它有以下几个特点： 隐式游标由PL/SQL自动管理； 隐式游标的默认名称是SQL； select或DML操作产生隐式游标 隐式游标的属性值始终是最新执行的SQL 语句的。 13.3.2 隐式游标的属性 隐式游标种类与显式游标没有变，与显式 游标相比还是有些区别。SQL隐式游标和 cursor for游标是Oracle内置的游标。SQL游 标与当前会话有关。当前会话中的更新（ update）、删除操作都会影响SQL隐式游标 的属性。cursor for游标用于循环。 13.3.2 隐式游标的属性 下面来介绍sql隐式游标的属性，如表所 示。 方法功能 sql%isopen 判断当前游标标是否打开。如果打开，该该属性值为值为 true，否则则 为为false sql%rowcount 对对于DML语语句，该该属性值为值为 受影响的数据行数。对对于select 语语句，如果发发生异常，其值为值为 1 sql%found 对对于DML语语句，该该属性表明表中是否有数据值值受影响。如果 DML语语句没有影响任何数据，该该属性值为值为 false，否则为则为 true 。对对于select语语句，如果发发生异常，其值为值为 true sql%notfound与属性sql%found正好相反 13.3.2 隐式游标的属性 【示例13-18】可以在每一条DML语句之后 输出该操作影响的行数。 13.3.3 使用SQL隐式游标 使用隐式游标不像显式游标声明一个游标名称，它无需定义、 打开、关闭。对于每个隐式游标必须要有一个into子句，因此隐 式游标的select语句必须只选中一行数据或只产生一行数据，创 建语法如图所示。 【示例13-19】使用隐式游标对customersnew表选中一行编号为 “230”数据。 13.3.4 使用cursor for隐式游标 SQL隐式游标只能通过属性信息为PL/SQL 编程提供帮助。用户无法操作SQL隐式游 标。cursor for游标用于循环。利用该游标， 用户可以像使用普通循环语句一样来循环 处理select语句所获得的每一条记录。 cursor for游标使用的一般语法如图所示。 13.3.4 使用cursor for隐式游标 【示例13-20】cursor for游标中的游标变量 ，无需显式声明、打开等操作。为了获得 表customersnew中city为Philadelphia的信息 ，可以利用cursor for游标。 13.4 动态（REF）游标 前面所讲的游标都是与一个SQL语句相关联， 并且在编译该块的时候此语句己经是可知的， 是静态的，而动态游标可以在运行时与不同的 语句关联，是动态的。动态游标被用于处理多 行的査询结果集。在同一个PL/SQL块中，动 态游标不同于特定的查询绑定，而是在打开游 标时才确定所对应的査询。因此，动态游标可 以依次对应多个查询。动态游标可以在游标声 明时不指定其查询定义，而是在游标打开时进 行定义。动态游标又可以分为两类：强类型动 态游标和弱类型动态游标。 13.4.1 强类型动态游标 强类型动态游标是指，在游标使用之前， 虽未指定游标的查询定义，但是游标的类 型已经确定。 13.4.1 强类型动态游标 1.声明动态游标 动态游标变量是ref类型的变量。声明动态游标时，应该 使用ref cursor关键字。强类型动态游标带有return语 句。所以在声明强类型动态游标时，则需要为其指定一 个返回类型，其语法形式如图所示。 【示例13-21】该示例给出了用于游标变量的不同声 明。 13.4.1 强类型动态游标 2.打开游标变量 如果要将一个游标变量与一个特定的select 语句相关联，需要使用open for语句，其语 法结构如图所示。 13.4.1 强类型动态游标 【示例13-22】游标变量的打开示例。 【示例13-23】强类型动态游标常用于根据 不同的