用游标
以下为SQL2000使用游标的模板。-- =============================================
-- Declare and using a KEYSET cursor
-- =============================================
DECLARE <cursor_name, sysname, test_cursor> CURSOR
KEYSET
FOR <select_statement, , SELECT au_fname FROM pubs.dbo.authors>DECLARE @name varchar(40)OPEN <cursor_name, sysname, test_cursor>FETCH NEXT FROM <cursor_name, sysname, test_cursor> INTO @name
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
-- PRINT 'add user defined code here'
-- eg.
PRINT 'updating record for ' + @name
UPDATE pubs.dbo.authors
SET phone = replace(phone, ' ', '-')
WHERE CURRENT OF <cursor_name, sysname, test_cursor>
END
FETCH NEXT FROM <cursor_name, sysname, test_cursor> INTO @name
ENDCLOSE <cursor_name, sysname, test_cursor>
DEALLOCATE <cursor_name, sysname, test_cursor>
GO
以下为SQL2000使用游标的模板。-- =============================================
-- Declare and using a KEYSET cursor
-- =============================================
DECLARE <cursor_name, sysname, test_cursor> CURSOR
KEYSET
FOR <select_statement, , SELECT au_fname FROM pubs.dbo.authors>DECLARE @name varchar(40)OPEN <cursor_name, sysname, test_cursor>FETCH NEXT FROM <cursor_name, sysname, test_cursor> INTO @name
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
-- PRINT 'add user defined code here'
-- eg.
PRINT 'updating record for ' + @name
UPDATE pubs.dbo.authors
SET phone = replace(phone, ' ', '-')
WHERE CURRENT OF <cursor_name, sysname, test_cursor>
END
FETCH NEXT FROM <cursor_name, sysname, test_cursor> INTO @name
ENDCLOSE <cursor_name, sysname, test_cursor>
DEALLOCATE <cursor_name, sysname, test_cursor>
GO
解决方案 »
- SQL中如何“让一个数据库向另一个数据库定时批量的传输数据”?
- 这个存储过程怎么写啊
- 基于sql server的c/s信息管理系统,如何解决异地之间的数据上报功能.[下级定期将数据报到上级数据库中]
- SQL 排序的问题......
- 用like 查询 nchar 列时的问题(明天下午结贴急)
- 请问SQL中登陆的用户,密码等信息存在MASTER数据库哪个表中?
- 一个很简单的问题:Java程序和MS SQL Server 2000相连应该用什么驱动程序?
- 【求救】SQL整个数据库替换TEXT的方法
- 如何在这个(Select * from A union Select * from B)数据集中查找数据?
- sql 查询问题
- 将Sqlserver导出到Excel表中,如何覆盖原Excel表中的数据
- 求合同管理系统的需求分析。
open cursor_app_select
FETCH NEXT FROM cursor_app_select INTO @appname
while @@fetch_status=0
begin
declare cusor_app_statflowlog cursor for select appstatflowlog_tolcon,appstatflowlog_tolrate,appstatflowlog_starttime,appstatflowlog_stoptime,appstatflowlog_addiinfo from tb_appstatflowlog where (appstatflowlog_starttime>=@starttime and appstatflowlog_stoptime<=@stoptime and appstatflowlog_appname=@appname) or (appstatflowlog_stoptime>@starttime and appstatflowlog_stoptime<@stoptime and appstatflowlog_appname=@appname) or (appstatflowlog_starttime>@starttime and appstatflowlog_starttime<@stoptime and appstatflowlog_appname=@appname) or (appstatflowlog_stoptime>@stoptime and appstatflowlog_starttime<@starttime and appstatflowlog_appname=@appname)
open cusor_app_statflowlog
fetch next from cursor_app_statflowlog into @tolcontemp,@tolratetemp,@starttimetemp,@stoptimetemp,@additemp
while @@fetch_status=0
begin
fetch next from cursor_app_statflowlog into @tolcontemp,@tolratetemp,@starttimetemp,@stoptimetemp,@additemp
--处理数据 end fetch next from cursor_app_select into @appname
end
close cursor_app_select
deallocate cursor_app_select我需要同时打开2、3个表,可以向我上面写的那样吗?如果可以,@@fetch_status 能自动区分是哪个游标吗?