sql游标使用实例

‘壹’ sql游标怎么用

例子
table1结构如下
id
int
name
varchar(50)
declare
@id
int
declare
@name
varchar(50)
declare
cursor1
cursor
for
--定义游标cursor1
select
*
from
table1
--使用游标的对象(跟据需要填入select文)
open
cursor1
--打开游标
fetch
next
from
cursor1
into
@id,@name
--将游标向下移1行，获取的数据放入之前定义的变量@id,@name中
while
@@fetch_status=0
--判断是否成功获取数据
begin
update
table1
set
name=name+'1'
where
id=@id
--进行相应处理(跟据需要填入SQL文)
fetch
next
from
cursor1
into
@id,@name
--将游标向下移1行
end
close
cursor1
--关闭游标
deallocate
cursor1

‘贰’ sql游标实例！

这是模板。
-- =============================================
-- Create procere with CURSOR OUTPUT Parameters
-- =============================================
-- drop the store procere if it already exists
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'<procere_name, sysname, proc_test>'
AND type = 'P')
DROP PROCEDURE <procere_name, sysname, proc_test>
GO

-- create the store procere
CREATE PROCEDURE <procere_name, sysname, proc_test>
<@proc_cursor_name, , @proc_test_cursor> CURSOR VARYING OUTPUT
AS
SET <@proc_cursor_name, , @proc_test_cursor> = CURSOR FOR
<select_statement, , select 1>
OPEN <@proc_cursor_name, , @proc_test_cursor>
GO

-- =============================================
-- example to execute the store procere
-- =============================================
DECLARE <@variable_cursor_name, , @test_cursor_variable> CURSOR

EXEC <procere_name, sysname, proc_test> <@proc_cursor_name, , @proc_test_cursor> = <@variable_cursor_name, , @test_cursor_variable> OUTPUT

WHILE (@@FETCH_STATUS = 0)
BEGIN
FETCH NEXT FROM <@variable_cursor_name, , @test_cursor_variable>
PRINT 'put user defined code here'
END

CLOSE <@variable_cursor_name, , @test_cursor_variable>

DEALLOCATE <@variable_cursor_name, , @test_cursor_variable>
GO

‘叁’ sql 中游标的作用及使用方法

游标可以从数据库中查询出一个结果集，在你关闭它之前，你可以反复使用这个结果集，读取这个结果集中的任意行任意字段的内容，一般在存储过程或前台程序中常见。

‘肆’ SQL中游标的应用

begin
open c1;
fetch c1 into vtest;
while c1%found loop
update im_test1 set test3=vtest; //这里不加where条件是更新所有行？？？
fetch c1 into vtest;
end loop;
end;
close c1;

‘伍’ 求一个SQL Server游标应用实例

--申明一个游标
DECLARE MyCursor CURSOR
FOR SELECT TOP 5 FBookName,FBookCoding FROM TBookInfo

--打开一个游标
OPEN MyCursor

--循环一个游标
DECLARE @BookName nvarchar(2000),@BookCoding nvarchar(2000)
FETCH NEXT FROM MyCursor INTO @BookName,@BookCoding
WHILE @@FETCH_STATUS =0
BEGIN
print 'name'+@BookName
FETCH NEXT FROM MyCursor INTO @BookName,@BookCoding
END

--关闭游标
CLOSE MyCursor
--释放资源
DEALLOCATE MyCursor

游标属于行级操作 消耗很大
SQL查询是基于数据集的
所以一般查询能有 能用数据集 就用数据集 别用游标
数据量大 是性能杀手

‘陆’ sql如何使用游标查询指定行记录

--用游标
DECLARE @COLUMN1 VARCHAR(10)
DECLARE @LineNum INT
SET @LineNum = 1
DECLARE CUR_TEST CURSOR FOR
SELECT 语句

OPEN CUR_TEST

FETCH NEXT FROM CUR_TEST INTO @COLUMN1,...

WHILE @@FETCH_STATUS = 0
BEGIN
IF (@LineNum >= N and @LineNum <= M)
BEGIN
SELECT @COLUMN1,...
END
SET @LineNum = @LineNum + 1
END

CLOSE CUR_TEST
DEALLOCATE CUR_TEST

--用双TOP直接取出,从前M条倒序取出前(M-N)条,等同于取出了N到M之间的
SELECT TOP (M-N) * FROM (SELECT TOP M * FROM 表名 WHERE 语句 ORDER BY COLUMNname DESC)

‘柒’ SQL游标如何使用

A. 在简单的游标中使用 FETCH
下例为 authors 表中姓以字母 B 开头的行声明了一个简单的游标，并使用 FETCH NEXT 逐个提取这些行。FETCH 语句以单行结果集形式返回由 DECLARE CURSOR 指定的列的值。

USE pubs
GO
DECLARE authors_cursor CURSOR FOR
SELECT au_lname FROM authors
WHERE au_lname LIKE "B%"
ORDER BY au_lname

OPEN authors_cursor

-- Perform the first fetch.
FETCH NEXT FROM authors_cursor

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM authors_cursor
END

CLOSE authors_cursor
DEALLOCATE authors_cursor
GO

au_lname
----------------------------------------
Bennet
au_lname
----------------------------------------
Blotchet-Halls
au_lname
----------------------------------------

B. 使用 FETCH 将值存入变量
下例与上例相似，但 FETCH 语句的输出存储于局部变量而不是直接返回给客户端。PRINT 语句将变量组合成单一字符串并将其返回到客户端。

USE pubs
GO

-- Declare the variables to store the values returned by FETCH.
DECLARE @au_lname varchar(40), @au_fname varchar(20)

DECLARE authors_cursor CURSOR FOR
SELECT au_lname, au_fname FROM authors
WHERE au_lname LIKE "B%"
ORDER BY au_lname, au_fname

OPEN authors_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 authors_cursor
INTO @au_lname, @au_fname

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN

-- Concatenate and display the current values in the variables.
PRINT "Author: " + @au_fname + " " + @au_lname

-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM authors_cursor
INTO @au_lname, @au_fname
END

CLOSE authors_cursor
DEALLOCATE authors_cursor
GO

Author: Abraham Bennet
Author: Reginald Blotchet-Halls

C. 声明 SCROLL 游标并使用其它 FETCH 选项
下例创建一个 SCROLL 游标，使其通过 LAST、PRIOR、RELATIVE 和 ABSOLUTE 选项支持所有滚动能力。

USE pubs
GO

-- Execute the SELECT statement alone to show the
-- full result set that is used by the cursor.
SELECT au_lname, au_fname FROM authors
ORDER BY au_lname, au_fname

-- Declare the cursor.
DECLARE authors_cursor SCROLL CURSOR FOR
SELECT au_lname, au_fname FROM authors
ORDER BY au_lname, au_fname

OPEN authors_cursor

-- Fetch the last row in the cursor.
FETCH LAST FROM authors_cursor

-- Fetch the row immediately prior to the current row in the cursor.
FETCH PRIOR FROM authors_cursor

-- Fetch the second row in the cursor.
FETCH ABSOLUTE 2 FROM authors_cursor

-- Fetch the row that is three rows after the current row.
FETCH RELATIVE 3 FROM authors_cursor

-- Fetch the row that is two rows prior to the current row.
FETCH RELATIVE -2 FROM authors_cursor

CLOSE authors_cursor
DEALLOCATE authors_cursor
GO

au_lname au_fname
---------------------------------------- --------------------
Bennet Abraham
Blotchet-Halls Reginald
Carson Cheryl
DeFrance Michel
del Castillo Innes
Dull Ann
Green Marjorie
Greene Morningstar
Gringlesby Burt
Hunter Sheryl
Karsen Livia
Locksley Charlene
MacFeather Stearns
McBadden Heather
O'Leary Michael
Panteley Sylvia
Ringer Albert
Ringer Anne
Smith Meander
Straight Dean
Stringer Dirk
White Johnson
Yokomoto Akiko

au_lname au_fname
---------------------------------------- --------------------
Yokomoto Akiko
au_lname au_fname
---------------------------------------- --------------------
White Johnson
au_lname au_fname
---------------------------------------- --------------------
Blotchet-Halls Reginald
au_lname au_fname
---------------------------------------- --------------------
del Castillo Innes
au_lname au_fname
---------------------------------------- --------------------
Carson Cheryl

‘捌’ sql语句编写存储过程，使用游标循环打印学生表中的数据，求大神

写一个例子给楼主看下就知道了：

在sqlserver2000中新建一个存储过程：
CREATEPROCEDUREPK_Test
AS
//声明1个变量
declare@namenvarchar(20)

//声明一个游标mycursor，select语句中参数的个数必须要和从游标取出的变量名相同


//打开游标
openmycursor

//从游标里取出数据赋值到我们刚才声明的2个变量中

fetchnextfrommycursorinto@name

//判断游标的状态
//0fetch语句成功
//-1fetch语句失败或此行不在结果集中
//-2被提取的行不存在
while(@@fetch_status=0)
begin

//显示出我们每次用游标取出的值
print'游标成功取出一条数据'
print@name

//用游标去取下一条记录
fetchnextfrommycursorinto@name

end

//关闭游标
closemycursor

//撤销游标
deallocatemycursor

GO

‘玖’ sql游标的写法

给你一个游标的写法。
此demo的目地是将游标行的name更新到另一张表的字段中条件是2张表的ID相同

Declare@Idvarchar(20)
Declare@Namevarchar(20)
DeclareCurCursorFor
selectid,namefromtemp1
OpenCur
FetchnextFromCurInto@Id,@Name
While@@fetch_status=0
Begin
UpdatetempSet[c3]=@Namewhere[id]=@Id
FetchNextFromCurInto@Id,@Name
End
CloseCur
DeallocateCur

‘拾’ sql server 中游标的作用麻烦简单举例说明。

游标说简单点都是设置一个数据表的行指针，然后使用循环等操作数据

以下是一个示例

createprocereUpdateValue--存储过程里面放置游标
as
begin

declareUpdateCursorcursor--声明一个游标，查询满足条件的数据
forselect主键,SD_VALfromEQ_SD_D

openUpdateCursor--打开

declare@idint,@SD_VALnvarchar(20)--声明一个变量，用于读取游标中的值
fetchnextfromUpdateCursorinto@id,@SD_VAL

while@@fetch_status=0--循环读取
begin
updateEQ_SD_Dsetname=@SD_VALwhereid=@id
fetchnextfromUpdateCursorinto@id,@SD_VAL
end

closeUpdateCursor--关闭

deallocateUpdateCursor--删除

end

这里是一个教学

http://www.cnblogs.com/moss_tan_jun/archive/2011/11/26/2263988.html