(MS SQL)HOW TO USE FETCH
来源:互联网 发布:linux命令行登录mysql 编辑:程序博客网 时间:2024/05/22 11:58
FETCH (Transact-SQL)
FETCH [ [ NEXT | PRIOR | FIRST | LAST | ABSOLUTE { n | @nvar } | RELATIVE { n | @nvar } ] FROM ] { { [ GLOBAL ] cursor_name } | @cursor_variable_name } [ INTO @variable_name [ ,...n ] ]
If the SCROLL option is not specified in an ISO style DECLARE CURSOR statement, NEXT is the only FETCH option supported. If SCROLL is specified in an ISO style DECLARE CURSOR, all FETCH options are supported.
When the Transact-SQL DECLARE cursor extensions are used, these rules apply:
If either FORWARD_ONLY or FAST_FORWARD is specified, NEXT is the only FETCH option supported.
If DYNAMIC, FORWARD_ONLY or FAST_FORWARD are not specified, and one of KEYSET, STATIC, or SCROLL are specified, all FETCH options are supported.
DYNAMIC SCROLL cursors support all the FETCH options except ABSOLUTE.
The @@FETCH_STATUS function reports the status of the last FETCH statement. The same information is recorded in the fetch_status column in the cursor returned by sp_describe_cursor. This status information should be used to determine the validity of the data returned by a FETCH statement prior to attempting any operation against that data. For more information, see @@FETCH_STATUS (Transact-SQL).
FETCH permissions default to any valid user.
A.Using FETCH in a simple cursor
The following example declares a simple cursor for the rows in the Person.Person table with a last name that starts with B, and uses FETCH NEXT to step through the rows. The FETCH statements return the value for the column specified in DECLARE CURSOR as a single-row result set.
USE AdventureWorks2012;GODECLARE contact_cursor CURSOR FORSELECT LastName FROM Person.PersonWHERE LastName LIKE 'B%'ORDER BY LastName;OPEN contact_cursor;-- Perform the first fetch.FETCH NEXT FROM contact_cursor;-- Check @@FETCH_STATUS to see if there are any more rows to fetch.WHILE @@FETCH_STATUS = 0BEGIN -- This is executed as long as the previous fetch succeeds. FETCH NEXT FROM contact_cursor;ENDCLOSE contact_cursor;DEALLOCATE contact_cursor;GO
B.Using FETCH to store values in variables
The following example is similar to example A, except the output of the FETCH statements is stored in local variables instead of being returned directly to the client. The PRINT statement combines the variables into a single string and returns them to the client.
USE AdventureWorks2012;GO-- Declare the variables to store the values returned by FETCH.DECLARE @LastName varchar(50), @FirstName varchar(50);DECLARE contact_cursor CURSOR FORSELECT LastName, FirstName FROM Person.PersonWHERE LastName LIKE 'B%'ORDER BY LastName, FirstName;OPEN contact_cursor;-- Perform the first fetch and store the values in variables.-- Note: The variables are in the same order as the columns-- in the SELECT statement. FETCH NEXT FROM contact_cursorINTO @LastName, @FirstName;-- Check @@FETCH_STATUS to see if there are any more rows to fetch.WHILE @@FETCH_STATUS = 0BEGIN -- Concatenate and display the current values in the variables. PRINT 'Contact Name: ' + @FirstName + ' ' + @LastName -- This is executed as long as the previous fetch succeeds. FETCH NEXT FROM contact_cursor INTO @LastName, @FirstName;ENDCLOSE contact_cursor;DEALLOCATE contact_cursor;GO
C.Declaring a SCROLL cursor and using the other FETCH options
The following example creates a SCROLL cursor to allow full scrolling capabilities through the LAST, PRIOR, RELATIVE, and ABSOLUTE options.
USE AdventureWorks2012;GO-- Execute the SELECT statement alone to show the -- full result set that is used by the cursor.SELECT LastName, FirstName FROM Person.PersonORDER BY LastName, FirstName;-- Declare the cursor.DECLARE contact_cursor SCROLL CURSOR FORSELECT LastName, FirstName FROM Person.PersonORDER BY LastName, FirstName;OPEN contact_cursor;-- Fetch the last row in the cursor.FETCH LAST FROM contact_cursor;-- Fetch the row immediately prior to the current row in the cursor.FETCH PRIOR FROM contact_cursor;-- Fetch the second row in the cursor.FETCH ABSOLUTE 2 FROM contact_cursor;-- Fetch the row that is three rows after the current row.FETCH RELATIVE 3 FROM contact_cursor;-- Fetch the row that is two rows prior to the current row.FETCH RELATIVE -2 FROM contact_cursor;CLOSE contact_cursor;DEALLOCATE contact_cursor;GO
- (MS SQL)HOW TO USE FETCH
- How To Use SQL XML ?
- how to use the code in ms ssrs
- How to use INF in MS Driver Package
- How to use the Sql Tuning Advisor
- How to fetch the SQL scipts with worst performance
- How to use MySql with Linq to SQL
- How To Use TKPROF To Trace And Analyze Sql Explan
- How To Use Dynamic Sql in Sql Server ?
- How To Use DataGird
- how to use typedef
- how to use gz
- How to use chkconfig
- How to use ,,,,
- how to use dialog
- How to use UIDs
- How to use dmalloc
- How to use abld
- iOS企业账号发布流程
- pearson, kendall 和spearman三种相关分析方法的区别
- java小基础
- CSS基础知识梳理-font字体
- Linux 共享库:LD_LIBRARY_PATH 与ld.so.conf
- (MS SQL)HOW TO USE FETCH
- 银光实时压缩和解压控件Xceed Real-Time Zip for Silverlight控件下载及详细介绍使用方法
- 编译dll文件时,error:程序数据库管理器不匹配;请检查安装
- 零拷贝的定义
- CSS基础知识梳理-anchor链接
- Hibernate的generator属性之意义
- 【VBA研究】用VBA取得EXCEL有效行列数方法比较
- mysql不支持远程连接的解决办法(#报错:1130-host ... is not allowed to connect to this MySql server 开放mysql远程连接 不使用lo
- 冒泡排序的实现