来自 计算机编程 2020-05-06 04:03 的文章
当前位置: 澳门威尼斯人平台 > 计算机编程 > 正文

MSSQL 游标使用 心得

核心提示:本文阐述在Oracle8i Release 2和Oracle9i中增强的游标共享设施

游标为您提供了在逐行的基础上而不是一次处理整个结果集为基础的操作表中数据的方法。 1.如何使用游标 1)定义游标语句 Declare 游标名 Cursor For 2)创建游标语句 Open 游标名 3)提取游标列值、移动记录指针 Fetch 列名列表 From 游标名 [Into 变量列表] 4)使用@@Fetch_Status利用While循环处理游标中的行 5)删除游标并释放语句 Close 游标名/Deallocate 游标名 6)游标应用实例 --定义游标 Declare cur_Depart Cursor For Select cDeptID,cDeptName From Department into @DeptID,@DeptName --创建游标 Open cur_Depart --移动或提取列值 Fetch From cur_Depart into @DeptID,@DeptName --利用循环处理游标中的列值 While @@Fetch_Status=0 Begin Print @DeptID,@DeptName Fetch From cur_Depart into @DeptID,@DeptName End --关闭/释放游标 Close cur_Depart Deallocate cur_Depart 2.语句的详细及注意 1)定义游标语句 Declare 游标名 [Insensitive] [Scroll] Cursor For Select 语句 [FOR {Read Only | Update [ OF 列名列表]}] Insensitive DBMS创建查询结果集数据的临时副本。游标是Read Only,也就是说不能修改其内容或底层表的内容; Scroll 指定游标支持通过使用任意Fetch 选项选取它的任意行作为当前行。如果此项省略,则游标将只支持向下移动单行; Select语句 定义游标结果集的标准 SELECT 语句。在游标声明的 Select语句内不允许使用关键字 COMPUTE、COMPUTE BY、FOR BROWSE 和 INTO; Read Only 防止使用游标的用户通过更新数据或删除行改变游标的内容; Update 创建可更新游标且列出值能被更新的游标列。如果子句中列入了任意列,则只有被列入的列才能被更新。如果Declare Cursor语句中只指定的UPDATE,则游标将允许更新它的任何或所有列。 Declare cur_Depart Cursor For Select * From Department For Update OF cDeptID,cDeptName 2)提取游标列值、移动记录指针语句 Fetch [Next | Prior | First | Last | {Absolute 行号} | {Relative 行号}] From 游标名 [Into 变量列表……] 每次执行Fetch语句时,DBMS移到游标中的下一行并把游标中的列值获取到Into中列出的变量中。因此Fetch语句的Into子句中列出的变量必须与游标定义中Select 语句中的列表的类型与个数相对应; 仅当定义游标时使用Scroll参数时,才能使用Fetch语句的行定位参数;如果Fetch语句中不包括参数Next | Prior | First | Last,DBMS将执行默认的Fetch Next; Next 向下、向后移动一行; Prior 向上、向前移动一行; First 移动至结果集的第一行; Last 移动至结果集的最后一行; Absolute n 移动到结果集中的第n行。如果n是正值,DBMS从结果集的首部向后或向下移动至第n行;如果n是负数,则DBMS从结果集的底部向前或向上移动n行; Fetch Absolute 2 From cur_Depart Into @DeptID,@DeptName Relative n 从指针的当前位置移动n行。如果n是正值,DBMS将行指针向后或向下移动至第n行;如果n是负数,则DBMS将行指针向前或向上移动n行; Fetch Relative 2 From cur_Depart Into @DeptID,@DeptName 3)基于游标的定位DELETE/UPDATE语句 如果游标是可更新的,就可以用游标从游标数据的源表中DELETE/UPDATE行,即DELETE/UPDATE基于游标指针的当前位置的操作; 举例: --删除当前行的记录 Declare cur_Depart Cursor For Select cDeptID,cDeptName From Department into @DeptID,@DeptName Open cur_Depart Fetch From cur_Depart into @DeptID,@DeptName Delete From Department Where CURRENT OF cur_Depart --更新当前行的内容 Declare cur_Depart Cursor For Select cDeptID,cDeptName From Department into @DeptID,@DeptName Open cur_Depart Fetch From cur_Depart into @DeptID,@DeptName Update Department Set cDeptID='2007' + @DeptID Where CURRENT OF cur_Depart 3.游标使用技巧及注意 1)利用Order By改变游标中行的顺序。此处应该注意的是,只有在查询的中Select 子句中出现的列才能作为Order by子句列,这一点与普通的Select语句不同; 2)当语句中使用了Order By子句后,将不能用游标来执行定位DELETE/UPDATE语句;如何解决这个问题,首先在原表上创建索引,在创建游标时指定使用此索引来实现;例如: Declare cur_Depart Cursor For Select cDeptID,cDeptName From Department With INDEX(idx_ID) For Update Of cDeptID,cDeptName 通过在From子句中增加With Index来实现利用索引对表的排序; 3)在游标中可以包含计算好的值作为列; 4)利用@@Cursor_Rows确定游标中的行数; ALTER FUNCTION SEL_KEYAR(@YEARNUM INT,@f_k_lessonid VARCHAR(15)) RETURNS VARCHAR(8000) AS BEGIN DECLARE @NIAN VARCHAR(8000),@NUMS INT,@NIANS VARCHAR(8000) SET @NUMS=1 DECLARE GETYEAR CURSOR FOR SELECT f_year FROM t_kejianol WHERE f_k_lessonid=@f_k_lessonid GROUP BY f_year ORDER BY f_year DESC OPEN GETYEAR FETCH NEXT FROM GETYEAR INTO @NIAN WHILE @@FETCH_STATUS=0 BEGIN IF @YEARNUM=@NUMS SET @NIANS=ISNULL(@NIANS+',','')+RTRIM(@NIAN) SELECT @NUMS=@NUMS+1 FETCH NEXT FROM GETYEAR INTO @NIAN --FETCH ABSOLUTE 3 FROM GETYEAR INTO @NIAN END CLOSE GETYEAR DEALLOCATE GETYEAR --PRINT @NIANS RETURN @NIANS END ALTER FUNCTION SEL_KEYAR(@YEARNUM INT,@f_k_lessonid VARCHAR(15)) RETURNS VARCHAR(8000) AS BEGIN DECLARE @NIAN VARCHAR(8000),@NIANS VARCHAR(8000) DECLARE GETYEAR CURSOR FOR SELECT f_year FROM t_kejianol WHERE f_k_lessonid=@f_k_lessonid GROUP BY f_year ORDER BY f_year ASC OPEN GETYEAR FETCH ABSOLUTE @YEARNUM FROM GETYEAR INTO @NIAN CLOSE GETYEAR DEALLOCATE GETYEAR RETURN @NIANS END

今天看到一篇文章写的自己整理记录下,据说比用游标快。

游标是数据库领域较为复杂的一个概念,因为游标包含了shared cursor和session cursor。两者有其不同的概念,也有不同的表现形式。
共享游标的概念易于与SQL语句中定义的游标相混淆。本文主要描述解析过程中的父游标,子游标以及共享游标,即shared cursor,同时给出了
游标(session cursor)的生命周期以及游标的解析过程的描述。   

Boost SQL Performance with cursor_sharing 关键词:cursor_sharing 概述 本文阐述在Oracle8i Release 2和Oracle9i中增强的游标共享设施。这些增强功能被一个新的参数cursor_sharing控制。 cursor_sharing的目的就是提高没有使用绑定变量(bind vvariable)的应用程序服务器的性能。 需要 cursor_sharing 本段解释为什么应用程序不使用绑定变量(bind variables)会带来性能问题。 应用程序反复执行相似的SQL语句 使用Oracle数据库管理他们的数据的应用程序必须使用SQL语句访问/修改数据库。这些SQL语句可以是由一个应用程序使用OCI, OCCI, JDBC, PL/SQL等直接产生的,也是可以是使用其他工具和库间接产生的。 根据不用的应用类型,通常一个应用程序都为最终用户提供了一个固定的功能集合,例如,一个人力资源应用程序可能会提供一些像增加一个新雇员,修改一个雇员的个人信息等功能。最终这些功能使用SQL访问和/或修改数据。因为应用程序重复地执行这些功能,一个应用和Oracle数据库的交互是由相似的SQL语句的反复执行构成的。 SQL调用的步骤 为执行一个SQL语句,客户端可以使用使用不同的接口。例如,通过OCI接口,客户端创建一个语句句柄(statement handle),然后perpare这个语句,绑定,定义和执行这个语句句柄,或者,SQL语句也可以通过一个PL/SQL过程被执行。 按照客户端接口,Oracle数据库一直都使用固定的步骤: 1. 打开一个游标 - 用户游标是一个和SQL语句相关的全部用户状态的句柄,像执行内存,共享游标引用,用户游标的当前状态等等。

DECLARE @字段1 数据类型;DECLARE @字段2 数据类型;DECLARE @TMP_WHILE_ID INT;SELECT TMP_WHILE_ID=IDENTITY(INT,1,1),TMP_WHILE_FLAG=0,[字段1],[字段2],...INTO #TMP_WHILE FROM [表名] WHERE [条件]....;SELECT @TMP_WHILE_ID=MIN(TMP_WHILE_ID) FROM #TMP_WHILE WHERE TMP_WHILE_FLAG=0;WHILE @TMP_WHILE_ID IS NOT NULLBEGIN --获取当前处理行的信息 SELECT @字段1=字段1,@字段2=字段2,... FROM #TMP_WHILE WHERE TMP_WHILE_ID=@TMP_WHILE_ID;  --<这里自己的处理过程>  --标识当前行已处理完毕 UPDATE #TMP_WHILE SET TMP_WHILE_FLAG=1 WHERE TMP_WHILE_ID=@TMP_WHILE_ID; --选择下一行号 SELECT @TMP_WHILE_ID=MIN(TMP_WHILE_ID) FROM #TMP_WHILE WHERE TMP_WHILE_FLAG=0 AND TMP_WHILE_ID>@TMP_WHILE_ID;ENDDROP TABLE #TMP_WHILE;

            有关游标的定义,声明,与使用请参考:PL/SQL 游标
            有关硬解析与软解析请参考:Oracle硬解析与软解析

  1. 解析一个SQL语句到打开的用户游标中 -使SQL语句和用户游标关联;它也建立了一个共享游标,对应于SQL语句的解析格式。在一些情况下,共享游标也可以作为解析的一部分被校对和优化。解析,校对和优化SQL语句的过程通常是非常耗费CPU时间,内存和连接资源的。
  2. 如有需要,绑定变量 - 给Oracle提供SQL语句中绑定变量的数据类型,大小和值等必要的信息。 4. 校对优化共享游标,如果还没有做这项工作的话。 5. 执行用户游标 - 这一步真正完成语句执行的工作,根据语句的复杂程度消耗CPU和会话内存(session memory)。 注意,解析,校对和优化组成了执行一个SQL语句的消耗,并且能够限制数据库的容量和可测量性。 共享游标 一个典型的重复执行相似语句的应用,在Oracle数据库许多针对SQL处理目的的优化重复执行。最重要的优化是共享游标,试图通过在相同的语句的不同执行之间共享编译结果来消除编译的耗费。如下图: User Session 1 Private execution state User Session 2 Private execution state Shared Cursor

 

一、相关定义         shared cursor
                也即是共享游标,是SQL语句在游标解析阶段生成获得的,是位于library cache中的sql或匿名的pl/sql等。其元数据被在视图V$sqlarea
                与v$sql中具体化。如果library cache中的父游标与子游标能够被共享,此时则为共享游标。父游标能够共享即为共享的父游标,子游
                标能够共享极为共享的子游标。
                
        session cursor
                即通过系统为用户分配缓冲区用于存放SQL语句的执行结果。用户可以通过这个中间缓冲区逐条取出游标中的记录并对其处理,直到所
                有的游标记录被逐一处理完毕。session cursor指的跟这个session相对应的server process的PGA里(准确的说是UGA)的一块内存区域
                (或者说内存结构)即其主要特性表现在记录的逐条定位,逐条处理。session cursor的元数据通过v$open_cursor视图来具体化。每一
                个打开或解析的SQL都将位于该视图。

为了能够使用共享游标,Oracle分割语句执行状态到共享游标中,并且在实例中预处理。共享游标是编译的结果并包含了执行计划;它在缓存在共享池中。每个执行该语句的会话有一个预执行状态的私有拷贝,如用户游标,运行时变量值等。 在解析阶段,Oracle首先搜索一个已经存在的可以被用户会话共享的共享游标。Oracle把搜索分为两步:基于SQL文本的检索,找到相同SQL文本创建的游标,根据其他标准选择适当的游标,如优化模式,访问的基本对象等。如果一个可以共享的游标被找到,并不需要编译,这个处理成为软解析。否则,编译SQL语句创建新的共享游标,这个处理成为硬解析。 当被应用程序使用的大多数语句能够共享同样的游标集合时,大多数解析变成为软解析,进而提高数据库服务器的能力/吞吐量,响应时间和可测量性 )。 为什么游标不是共享的? 假设其他的因素是相同的,如可配置的实例/会话/事务等级参数,理论上,如果在同样的行/对象上执行同样的操作,使用同样的计划,语句S1和S2的游标可以被共享。但是要计算和找出这些游标是非常困难的,这样做也可能抵消共享游标带来的好处。因此,Oracle游标共享标准规定在所有的情况下默认都不共享游标,除非它们被设计得很高效。从8i Release 2开始,如果S1和S2都是文本的并且不少的其他条件都相同,它们可以共享同一个游标。 当应用程序在语句中使用文字标量替代绑定变量时就会导致一个游标共享的问题。如应用程序最终产生的语句只是在文字标量上有一些不同,甚至语句都是相同的。如,一个应用程序没有使用绑定变量,可以假设在不同的时间或不同的会话中有下面两个语句: INSERT INTO T VALUES(1, ‘foo’, 4) INSERT INTO T VALUES(2, ‘bar’, 7) 因为这两个语句文本上并不相同,它们最终产生不同的游标。 有几种情况下应用程序可能不会使用绑定变量: l 用文字标量很容易就写出一个SQL语句,特别是使用了一些工具 l 老的Oracle关系数据库不支持绑定变量,已有的应用程序要求作一些代码升级的工作。 l 其他所有的数据库供应商都不支持绑定变量,即使有语法也不相同;因此,使用特定的Oracle语法/特性会使应用程序失去与其他数据的兼容性。 l 如果一个语句使用绑定变量,那么它就一直使用相同的执行计划。如果不同的绑定变量会有不同的优化计划就可能产生问题,如,考虑下面的语句: SELECT * FROM T1, T2 WHERE (T1.N = 100) AND (T1.N1=T2.N2) SELECT * FROM T1, T2 WHERE (T1.N = 500) AND (T1.N1=T2.N2) 根据值在字段N中的分布,两个语句可能有不同的优化计划。因此使用绑定变量: SELECT * FROM T1, T2 WHERE (T1.N = :X) AND (T1.N1=T2.N2) 将会由于一些绑定变量的值导致低效的优化。这时可以强制使用文字标量代替绑定变量。 概念 在开始解决方案之前,这里先澄清一些基本概念。 相似语句(Similar statements) 如果任何两个语句只是文字标量不相同,可以认为它们是相似的。 这纯粹是一个语义学上的标准。 例如:以下的语句是相似的 INSERT INTO T VALUES(1, ‘foo’, 4) INSERT INTO T VALUES(2, ‘bar’, 7) INSERT INTO T VALUES(5, ‘alpha’, 11) INSERT INTO T VALUES(10, ‘kappa’, 17) 最优共享语句 相似语句可能有也可能没有同样的执行计划。例如,下面两个语句就有相同的执行计划: INSERT INTO T VALUES(1, ‘foo’, 4) INSERT INTO T VALUES(2, ‘bar’, 7) 在本文中,这些语句叫做最优共享语句。 因此: 最优共享语句是具有相同最优计划的相似语句。 同样也意味着最优共享语句可以共享相同的游标,而不会对执行成本有任何的影响。 非最优化共享语句 另一面,如下面两个语句: SELECT * FROM T1, T2 WHERE (T1.N = 100) AND (T1.N1 = T2.N2) SELECT * FROM T1, T2 WHERE (T1.N = 500) AND (T1.N1 = T2.N2) 根据和的行数,值在字段N中的分布,在N, N1或N2上索引的可用性等情况,可能有不同的最优计划。例如,第一个语句可能使用一个在T1上的索引,而第二个语句可能是在T1上做全表扫描。或者第一个语句可能是作一个哈希连接而第二个语句可能是做一个嵌套循环连接。这些语句响应地可以当作是非最优化共享语句,因此: 非最优化共享语句是可能具有不同最优计划的相似语句。 同样也意味着如果非最优化共享语句共享同样的游标,那么在执行效率上可能会存在损失。 最优共享与非最优共享语句 最优共享和非最优共享语句的区别并不纯粹是在语义上的。它依赖于下面的因素: l 文字标量在语句中的位置 l 可用的访问路径 l 如果一个文字标量出现在一个包含字段的谓词中,则取决于数据分布和它的可用性 l 优化器的算法使用 非共享语句 因为使用同样的游标会产生不正确的结果,会出现相似语句不能共享同一个游标的情况。这些相似语句意味着不同的事情,或者在执行期间做了完全不同的事。下面的语句描述了这一点: SELECT * FROM T ORDER BY 1,4 SELECT * FROM T ORDER BY 2,3 在这个例子中,文字标量1,2,3和4指的是选择表项中的项目。这些语句叫做非共享语句。因此有: 非共享语句是不能共享同样的执行计划的相似语句。 这里最重要的一点就是:如果两个非共享语句共享同样的游标,它们其中一个就会得到错误的结果。 解决方案 这一节描述通过cursor_sharing参数所提供的解决方案 概述 新参数cursor_sharing只要有可能就允许相似语句共享游标。根据参数的值,相似语句可以被强制共享相同的游标,或者共享相同游标而不危及底层执行计划的安全。 不管设置cursor_sharing为SIMILAR还是FORCE,Oracle都首先搜索完全相同的语句文本的游标。如果没有发现,Oracle搜索相似语句文本的游标。 用法 参数:cursor_sharing 从Oracle 8i Release 2开始,一个新的动态参数cursor_sharing被引入。在8i中,参数可以有两个不同的值FORCE和EXACT。从9i开始,一个新的值SIMILAR被加入。 默认值是EXACT。它只允许完全相同文本的语句共享一个游标。这是早期版本的行为。 SIMILAR参数值使相似语句共享同样的游标,而不危机执行计划的安全。例如:只有最优共享语句共享游标。 将参数值设为FORCE会强迫Oracle对相似语句共享游标,但存在非最优执行计划的风险,如,最优共享和非最优共享语句会共享同一个游标。只有在非最优执行计划的风险被共享游标的性能提高超过的时候,该参数才可以被设置为FORCE,例如:如果太多的非最优共享语句的硬解析导致了严重的性能问题。 SQL语句 一个新的标记CURSOR_SHARING_EXACT在被SQL语句中被用于在语句级别控制游标共享。这个标记类似于初始化参数cursor_sharing被设置为EXACT,并屏蔽了已经设定的初始化参数的值,也就是:它导致语句共享采用精确匹配构建的游标。 ALTER SYSTEM 和 ALTER SESSION 命令允许新参数cursor_sharing的设置和改变。语法如下面的形式: ALTER SYSTEM SET cursor_sharing = {FORCE | SIMILAR | EXACT} ALTER SYSTEM SET cursor_sharing = {FORCE | SIMILAR | EXACT} 动态视图 下面的四个动态视图显示了绑定变量的信息: l GV$SQL_BIND_METADATA l V$SQL_BIND_METADATA l GV$SQL_BIND_DATA l V$SQL_BIND_DATA 这些视图也包括了内部绑定变量的信息。内部绑定变量可以根据视图[G]V$SQL_BIND_DATA中的字段SHARED_FLAG2与用户绑定变量区分,内部绑定变量的标记值为256。 只参看内部绑定变量的行,用户可以考虑下面的语句: SELECT * FROM V$SQL_BIND_DATA WHERE BITAND (SHARED_FLAG2, 256) =256 主要利益与折衷 考虑一个没有使用绑定变量的应用,该应用重复地使用相似语句,大多数的执行都将导致硬解析。 一个不使用绑定变量的典型应用可能会有各种类型的语句:最优共享,非最优共享安和非共享。对于最优共享语句,共享游标明显是有好处;非共享语句不能共享同样的游标。 对于非最优共享语句没有一个简单的答案:共享游标与获取最优计划的比较是硬解析的系统耗费与强制使用相同执行计划后的性能退化之间的折衷。因此,根据系统负载,应用特征,资源限制等,正确的答案是不同的。这也是Oracle 提供为cursor_sharing提供两个不同的值SIMILAR和FORCE,并把决定权留给用户的原因。SIMILAR是更保守的选择,它仅仅使最优可共享语句共享游标。采用FORCE,最优共享和非最优共享语句都被强制共享游标,结果便不可预测,因为游标可能被共享但执行计划的性能也降低了。因此,因为硬解析造成性能有非常大的影响并且非最优共享语句占非常大的百分比的情况下,使用FORCE是有意义的。另外一个考虑的方式是:在采用FORCE 之前先尝试SIMILAR。

二、游标的生命周期(session cursor)         session cursor需要从UGA中分配内存,因此有其生命周期。其生命周期主要包括:
                打开游标(根据游标声明的名称在UGA中分配内存区域)
                解析游标(将SQL语句与游标关联,并将其执行计划加载到Library Cache)
                定义输出变量(仅当游标返回数据时)
                绑定输入变量(如果与游标关联的SQL语句使用了绑定变量)
                执行游标(即执行SQL语句)
                获取游标(即获取SQL语句记录结果,根据需要对记录作相应操作。游标将逐条取出查询的记录,直到取完所有记录)
                关闭游标(释放UGA中该游标占有的相关资源,但Library Cache中的游标的执行计划按LRU原则清除,为其游标共享提供可能性)

本文由澳门威尼斯人平台发布于计算机编程,转载请注明出处:MSSQL 游标使用 心得

关键词: