《Transact SQL——游标和自定义函数》由会员分享，可在线阅读，更多相关《Transact SQL——游标和自定义函数（23页珍藏版）》请在金锄头文库上搜索。
1、Transact SQL游标和自定义函数1.游标的优点和种类 在数据库开发过程中，要检索的数据只是一条记录时，程序员所编写的事务语句代码往往使用SELECT、INSERT语句。实际上，常常会遇到从某一结果集中逐一地读取一条记录的情况，这时候就要用到游标。 1).游标和游标的优点 在数据库中游标是一个十分重要的概念。游标可以提供一种对从表中检索出的数据进行操作的灵活手段，就本质而言，游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。游标总是与一条T-SQL选择语句相关联。因为游标由结果集和结果集中指向特定记录的游标位置组成。当决定对结果集进行处理时，必须声明一个指向该结果集的
2、游标。游标和FoxBase/Foxpro中的数据库/表中的记录指针完成的工作是相同的，而数据库/表舅相当于SQL Server中的查询，可见游标能够实现按与传统程序读取平面文件类似的方式处理来自基础表的结果集，从而把表中数据以平面文件的形式呈现给程序。 关系数据库管理系统实质是面向集合的，在SQL Server中并没有一种描述表中单一记录的表达形式(使用where子句来限制只有一条记录被选中)。因此，必须借助于游标来进行面向单条记录的数据处理。 由此可见，游标允许应用程序对查询语句返回的行结果集中每一行进行相同或不同的操作，而不是一次对整个结果集进行同一种操作；它还提供了对基于游标位置而对表中
3、数据进行删除或更新的能力。 2).游标种类 SQL Server支持3种类型的游标：Transact-SQL游标、API服务器游标和客户游标。 * API游标 API游标支持在OLE DB，ODBC以及DB_library中使用游标函数，每一次客户端应用程序调用API游标函数，SQL Server的OLEDB提供者、ODBC驱动器或DB_library的动态链接库(DLL)都会将这些客户请求传送给服务器以对API游标进行处理。API游标主要用在服务器上。 * Transact-SQL游标 Transact-SQL游标通过使用DECLARE CURSOR等T-SQL语句定义、操作的，主要用作T-
4、SQL脚本、存储过程和触发器中。T-SQL游标主要用在服务器上，处理由客户端发送给服务器的Transact-SQL语句或是批处理、存储过程、触发器中的数据处理请求，Transact-SQL游标不支持提取数据块或多行数据。 * 客户游标 客户游标主要是当在客户机上缓存结果集时才使用，在客户游标中，有一个Transact-SQL的结果集被用来在客户机上缓存整个结果集。客户游标仅支持静态游标而非动态游标，由于服务器游标并不支持所有的Transact-SQL语句或批处理，所以客户游标常常仅被用在服务器游标的辅助。因为在一般情况下，服务器游标支持决大多数的游标操作。 由于API游标和Transact-S
5、QL游标使用在服务器端，所以被称为服务器游标，也被称为后台游标，而客户端游标被称为前台游标。2.游标的基本操作 1).声明游标 每一个游标有4个组成部分，这4个关键部分必须符合下面的顺序； （1）声明游标 （2）打开游标 （3）从一个游标中查找信息。 （4）关闭游标 通常使用DECLARE来声明一个游标，声明一个游标主要包括以下主要内容： 游标名称 数据来源（表和列） 选取条件 属性（仅读或可修改） 其语法格式如下： DECLARE cursor_nameINSENSITIVESCROLLCURSOR FOR select_statement FORREAD ONLY|UPDATEOF col
6、umn_name,.nSQL92游标声明语句参数-参数|具体含义- Cursor_name 指游标的名字 INSENSITIVE 表明SQL Server会将游标定义所选取出来的数据记录存放在一临时表内，对该游标的读取 操作皆由临时表来应答。 SCROLL 表明所有的提取操作(如FIRST、LAST、PRIOR、NEXT等)都可用。如果不使用该保留字，那 么只能进行NEXT提取操作。 Select_statement 是定义结果集的SELECT语句。应该注意的是在游标中不能使用COMPUTE、COMPUTE BY、 FOR BROWSE、INTO语句 READONLY 表明不允许游标内的数据被
7、更新，尽管在默认状态下游标是允许更新的。而且在UPDATE或 DELETE语句的WHERE CURRENT OF子句中，不允许对该游标进行引用 UPDATEOF column_name,.n 定义在游标中可被修改的列。如果不指出要更新的列，那么所有的列都将被更新- 当游标被成功创建后，游标名成为该游标的惟一标识，如果在以后的存储过程、触发器或Transact-SQL脚本中使用游标，必须指定该游标的名字。 USE pubs GO DECLARE authors_cursor CURSOR -创建游标 FOR SELECT * FROM authors OPEN authors_cursor -打
8、开游标 FETCH NEXT FROM authors_cursor -读取游标定位的数据 GO 上面介绍的是SQL92的游标语法规则，下面介绍SQL Server提供的扩展了的游标声明语法。通过增加另外的保留字，使游标的功能进一步得到了增强，语法规则为： DECLARE cursor_name CURSOR LOCAL|GLOBAL FORWARD_ONLY|SCROLL STATIC|KEYSET|DYNAMIC|FAST_FORWARD READ_ONLY|SCROLL_LOCKS|OPTIMISTIC TYPE_WARNING FOR select_statement FOR UPDA
9、TEOF column_name,.nSQL Server游标声明语句参数-参数|具体含义- LOCAL 定义游标的作用域仅限于其所在的存储过程、触发器或批处理中。当建立游标的存储过程 执行结束后，游标会被自动释放 GLOBAL 定义游标的作用域是整个会话层。会话层指用户的连接时间，它包括从用户登录到SQL Server到脱离数据库的整段时间 FORWARDONLY 指明在从游标中提取数据记录时，只能按照从第一行到最后一行的顺序，此时只能选用 FETCH NEXT操作。除非使用STATIC、KEYSET和DYNAMIC关键字，否则如果未指明是使用 FORWARD_ONLY还是使用SCROLL，
10、那么FORWARD_ONLY将成为默认选项，因为若使用STATIC、 KEYSET和DYNAMIC关键字，则变成了SCROLL游标 STATIC 该选项的含义与INSENSITIVE选项一样，SQL Server会将游标定义所选取出来的数据记录 存放在一临时表内。对该游标的读取操作皆由临时表来应答 KEYSET 指出当游标被打开时，游标中列的顺序是固定的，并且SQL Server会在tempdb内建立一个 表，该表即为KEYSET。KEYSET的键值可惟一识别游标中的某行数据。当游标拥有者或其他 用户对基本表中的非键值数据进行修改时，这种变化能够反映到游标中。 DYNAMIC 指明基础表的变化
11、将反映到游标中。使用这个选项会最大程度上保证数据的一致性。然而与 KEYSET和STATIC类型游标相比较，此类型游标需要大量的游标资源。 FASTFORWARD 指明一个FORWARD_ONLY,READ_ONLY型游标 SCROLLLOCKS 指明锁被放置在游标结果集所使用的数据上。当数据被读入游标中时，就会出现锁。这个选 项确保对一个游标进行的更新和删除操作总能被成功执行。如果FASTFORWORD选项被定义， 则不能选择该选项 OPTIMISTIC 指明在数据被读入游标后，如果游标中某行数据已发生变化，那么对游标数据进行更新或删 除可能会导致失败。 TYPEWARNING 指明若游标类型被修改成与用户定义的类型不同时，将发送一个警告信息给客户端- -标准游标 USE Pubs GO DECLARE authors_curs
