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

《精通Oracle核心技术和项目实战之游标.pptx》由会员分享，可在线阅读，更多相关《精通Oracle核心技术和项目实战之游标.pptx（44页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、第13章游标游标是在关系数据库中用来操作查询出来的数据集。引入游标也是为了更加方便地访问数据。游标在操作数据库时经常用到，它使用相对灵活，容易理解和操作。本章将主要学习游标的概念、种类、以及如何创建和使用游标。13.1 什么是游标游标从概念上讲基于数据库的表返回结果集。它指示结果集中的当前位置，就像计算机屏幕上的光标指示当前位置一样，“游标”由此得名。13.1.1 游标概念SQL是面向集合的，其结果一般是多条记录。而PL/SQL的变量一般是标量，其一组变量一次只能存放一条记录。所以仅仅使用变量并不能完全满足SQL语句向应用程序输出数据的要求。为此，在PL/SQL中引入了游标的概念。13.1.1

2、 游标概念1.基本原理在PL/SQL块中执行select、insert、update、delete语句时，Oracle会在内存中为其分配一个缓冲区。游标是指向该区的一个指针，或是一种结构化数据类型。它为应用程序提供一种对结果集中的每一行数据分别进行单独处理的方法。13.1.1 游标概念可以将游标形象地看成一个变动的光标。它实际上是一个指针，在一段Oracle存放数据查询结果集或数据操作结果集的内存中，这个指针可以指向结果集中的任何一条记录。通过游标就可以得到它所指向的数据，但初始时它指向首记录。这种模型很像编程语言中的数组。如图所示。13.1.2 游标种类Oracle中游标分为静态游标和动态（

3、REF）游标两类。其中，静态游标就像一个数据快照，打开游标后的结果集是对数据库的一个备份，数据不随对表执行DML操作后而改变。静态游标又分为显式游标和隐式游标两类。动态游标在下面章节中讲解。游标种类如图所示。13.2 显式游标显式游标在PL/SQL编程当中有着重要的作用，通过显式游标用户可以操作返回的数据，使得一些在编程语言中复杂的功能更容易实现。13.2.1 创建显式游标步骤显示游标的使用顺序可以明确地分成声明游标、打开游标、读取数据和关闭游标4个步骤。具体步骤如下。1.声明游标声明游标主要是用来给游标命名并且使得游标关联一个查询。13.2.1 创建显式游标步骤声明游标的具体语法如图所示。【

4、示例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不仅

5、可以利用列类型来定义变量，而且可以直接声明一个行类型的变量，来达到为每个列统一声明变量声明行类型的语法如图所示。【示例13-4】我们可以针对表customersnew的行声明一个行变量。13.2.1 创建显式游标步骤2.打开游标游标中对数据的任何操作都是建立在游标被打开的前提下。游标一旦打开，其结果集都是静态的。也就是说结果集此时不会反映出数据库中对数据进行的增加、删除、修改操作。打开游标语法如图所示。【示例13-5】打开游标cus_cur。13.2.1 创建显式游标步骤3.读取数据游标打开后，就可以取出游标中的数据，并对其进行处理了。从游标中取出数据的命令是fetch。fetch命令把游标指

6、向当前记录赋给PL/SQL声明的变量。它只能读出指针当前的记录，一次取出一行数据。正常情况下，fetch要和循环语句一起使用，这样指针会不断前进，直到某个条件不符合条件而退出循环。具体fetch命令的语法如图所示。13.2.1 创建显式游标步骤4.关闭游标游标在使用完后，应该及时关闭，释放它所占用的内存空间。关闭游标后，结果集中的数据将不能做任何操作，具体语法如图所示。13.2.1 创建显式游标步骤学了创建显式游标的步骤，总结得到显式游标操作过程如图所示。13.2.1 创建显式游标步骤创建返回单条记录显式游标的语法如图所示。【示例13-6】下面我们为customersnew表创建一个名为cus

7、_cur的简单游标，并从游标中提取出单行数据。【示例13-7】对上面的PL/SQL块进行修改，在PL/SQL块中使用记录变量。游标中的loop语句通常显式游标提取的数据不会只有一条，而是多条记录，这时如果只用一条fetch语句仅能取到游标中的一行数据。这样就需要一个遍历结果集的方式，而loop语句就能实现该功能。语法如图所示，下面示例将演示在游标中如何使用loop语句。游标中的loop语句【示例13-8】下面我们为customersnew表创建一个名为cus_loop_cur的游标，并从游标中提取出city是Philadelphia的顾客数据。【示例13-9】上个示例中，从游标中提取出city

8、是Philadelphia的顾客数据，除了使用while方式结束循环，现在还可以用另外一种形式的循环处理游标。13.2.3使用bulk collect和for语句的游标游标中通常使用fetch into 语句提取数据，这种方式是单条数据提取，但是在数据量很大的情况下执行效率不是很理想。而此时引用fetch bulk collect into语句可以批量提取数据，在数据量大的情况下它的执行效率比单条提取数据的高。语法如图所示。13.2.3使用bulk collect和for语句的游标【示例13-10】修改上一个示例的脚本，实现使用批量的方式从游标中查询数据。使用for loop语句游标中的数据一

9、般是通过循环方式来处理的。在上面的例子中用常规的循环方法进行处理。PL/SQL还提供了一种更简便的方法处理游标，这种方法利用for循环，逐行处理游标中的行。for loop不需要声明变量，它可以直接提出行对象类型的数据。其语法结构如图所示。使用for loop语句【示例13-11】上个示例中，从游标中提取出city是Philadelphia的顾客数据，采用的是while设置的loop循环，现在还可以用for循环处理游标。为游标传递参数如果在声明游标时，select语句都没有where子句。但是在实际的情况中，可能要根据具体需求查询不同的数据。为了通过游标对数据进行更加灵活的处理，可以在使用显示

10、游标时，指定参数。这些参数可以用在where子句中。在打开游标时，指定实际的参数值，这样游标在每次打开时，可以根据不同的实际参数值，返回所需的不同数据。一般情况下，指定参数包括参数的顺序和参数的类型，如图所示。为游标传递参数【示例13-12】在表customersnew中，含有存储赊销限额的列credit_limit。现欲获得顾客赊销限额大于某一确定值的顾客信息，则可以为搜寻时的where条件指定参数。【示例13-13】对于上述示例，打开游标时，采用参数传递的方式。显式游标的属性通过游标，可以处理数据。在使用游标时，必须考虑各种特殊情况。如果select语句没有返回结果，游标是空的，这时fet

11、ch语句将取不到数据。利用游标的属性可以了解游标当前的状态，防止各种意外情况的发生。显式游标有4个属性：%isopen：判断游标是否打开,打开则返回true,否则返回false。%found：判断当前游标最近的一次fetch是否取到数据。是则返回true,否则返回false。%notfound：与%found恰好相反。%rowcount：判断自游标打开以来，到目前为止，用fetch命令获取的行数，并非所有总记录数。显式游标的属性下面用几个例子来了解一下以上4个属性的具体使用方法。1.%isopen属性【示例13-14】%isopen的使用。显式游标的属性2.%found属性%found一般在多

12、条记录中使用，以下以%found属性的用法为例大家演示一下。【示例13-15】%found的使用。显式游标的属性3.%rowcount属性%rowcount属性能使得不必抽取所有记录行就可以中断游标操作，以下用loop语句来演示一下它的作用。【示例13-16】使用%rowcount为emp表提取10条记录，具体效果如图13-30所示。【示例13-17】把customersnew表中编号为983的顾客的city设置为“taiyuan”。13.3 隐式游标隐式游标是数据库服务器定义的一种游标。如果在PL/SQL程序中使用select语句或者DML语句进行操作，数据库服务器将自动打开一个隐式游标，用

13、来存放该语句的执行结果。所以这种游标无需定义，也不需打开和关闭。隐式游标始终存放最近一条语句的执行结果。本小节将介绍隐式游标的特点及属性。13.3.1 隐式游标的特点每当执行select或DML语句时，PL/SQL会自动打开一个隐式游标。隐式游标不受用户的控制，它有以下几个特点：隐式游标由PL/SQL自动管理；隐式游标的默认名称是SQL；select或DML操作产生隐式游标隐式游标的属性值始终是最新执行的SQL语句的。13.3.2隐式游标的属性隐式游标种类与显式游标没有变，与显式游标相比还是有些区别。SQL隐式游标和cursor for游标是Oracle内置的游标。SQL游标与当前会话有关。当

14、前会话中的更新（update）、删除操作都会影响SQL隐式游标的属性。cursor for游标用于循环。13.3.2隐式游标的属性下面来介绍sql隐式游标的属性，如表所示。方法方法功能功能sql%isopen判断当前游判断当前游标是否打开。如果打开，是否打开。如果打开，该属性属性值为true，否，否则为falsesql%rowcount对于于DML语句，句，该属性属性值为受影响的数据行数。受影响的数据行数。对于于select语句，如果句，如果发生异常，其生异常，其值为1sql%found对于于DML语句，句，该属性表明表中是否有数据属性表明表中是否有数据值受影响。如果受影响。如果DML语句没有

15、影响任何数据，句没有影响任何数据，该属性属性值为false，否，否则为true。对于于select语句，如果句，如果发生异常，其生异常，其值为truesql%notfound与属性与属性sql%found正好相反正好相反13.3.2隐式游标的属性【示例13-18】可以在每一条DML语句之后输出该操作影响的行数。13.3.3使用SQL隐式游标使用隐式游标不像显式游标声明一个游标名称，它无需定义、打开、关闭。对于每个隐式游标必须要有一个into子句，因此隐式游标的select语句必须只选中一行数据或只产生一行数据，创建语法如图所示。【示例13-19】使用隐式游标对customersnew表选中一行

16、编号为“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）游标前面所讲的游标

17、都是与一个SQL语句相关联，并且在编译该块的时候此语句己经是可知的，是静态的，而动态游标可以在运行时与不同的语句关联，是动态的。动态游标被用于处理多行的査询结果集。在同一个PL/SQL块中，动态游标不同于特定的查询绑定，而是在打开游标时才确定所对应的査询。因此，动态游标可以依次对应多个查询。动态游标可以在游标声明时不指定其查询定义，而是在游标打开时进行定义。动态游标又可以分为两类：强类型动态游标和弱类型动态游标。13.4.1 强类型动态游标强类型动态游标是指，在游标使用之前，虽未指定游标的查询定义，但是游标的类型已经确定。13.4.1 强类型动态游标1.声明动态游标动态游标变量是ref类型的变

18、量。声明动态游标时，应该使用ref cursor关键字。强类型动态游标带有return语句。所以在声明强类型动态游标时，则需要为其指定一个返回类型，其语法形式如图所示。【示例13-21】该示例给出了用于游标变量的不同声明。13.4.1 强类型动态游标2.打开游标变量如果要将一个游标变量与一个特定的select语句相关联，需要使用open for语句，其语法结构如图所示。13.4.1 强类型动态游标【示例13-22】游标变量的打开示例。【示例13-23】强类型动态游标常用于根据不同的条件来使用不同的游标定义。例如，在表categories中，希望能够获得“software1”这一商品种类的具体信

19、息。如果返回给用户的记录数目为0，那么用户希望能够查看到所有商品种类信息，以便能够确定“software1”这一信息的确不存在。此时，可以利用强类型动态游标进行处理。13.4.2 弱类型动态游标动态游标返回的类型的是特定类型。在PL/SQL语言中，还有一种非受限游标，即弱类型动态游标。它在声明的时候没有returne子句。一个非受限游标变量可以为任何查询打开。使用弱类型动态游标没有return语句，使用弱类型动态游标的语法，如图所示。13.4.2 弱类型动态游标【示例13-24】在表product_information中，如果无法获得category_id为“22”的商品类别名称信息，那么希望继续查询表categories以尝试在该表中获得category_id为“22”的商品类别名称信息。表categories的结构及数据。13.5小结本章介绍了游标的概念定义。游标可以帮助开发人员从大量的数据中提取每一条数据，是一个非常重要的知识点。游标分为静态游标和动态游标。而静态游标分为显示游标和隐式游标，其中显示游标可以接受开发者的操作。而隐式游标不能接受开发者的操作，只能利用游标的属性来获取相关信息。本章重点是显示游标和隐式游标的灵活使用。本章难点是理解动态游标。