在sqlServer中怎么用游标遍历表？请进····
日期：2014-05-18 浏览次数：20465 次
在sqlServer中如何用游标遍历表？？请进····
如题：
if exists(select * from sysobjects where name='A')
drop table A
create table A
(
Aid int identity(1,1) primary key,
Aname varchar(50)
)
go
insert into a(aname) values('a')
insert into a(aname) values('b')
insert into a(aname) values('c')
insert into a(aname) values('d')
if exists(select * from sysobjects where name='B')
drop table B
create table B
(
Bid int identity(1,1) primary key,
Bname varchar(50)
)
go
if exists(select * from sysobjects where name='p')
drop proc p
CREATE PROCEDURE p
AS
BEGIN
如何在此用游标将 A 表中所有数据添加到 B 表中？？？？ END
GO
------解决方案--------------------
没装SQLSERVER，只会Oracle下的,自己稍微改一下把
Field1 VARCHAR(1);
CURSOR cursor1 IS
SELECT Field1 FROM TableA
BEGIN
IF NOT cursor1 %ISOPEN THEN
OPEN cursor1 ;
END IF;
LOOP
FETCH cursor1
INTO Field1;
EXIT WHEN cursor1%NOTFOUND;
Insert into B(field) values(Field1);
End Loop;
IF record%ISOPEN THEN
CLOSE record;
END IF;
------解决方案--------------------
刚测试通过了的
如题：
if exists(select * from sysobjects where name='A')
drop table A
create table A
(
Aid int identity(1,1) primary key,
Aname varchar(50)
)
go
insert into a(aname) values('a')
insert into a(aname) values('b')
insert into a(aname) values('c')
insert into a(aname) values('d')
if exists(select * from sysobjects where name='B')
drop table B
create table B
(
Bid int identity(1,1) primary key,
Bname varchar(50)
)
go
if exists(select * from sysobjects where name='p')
drop proc p
CREATE PROCEDURE p
AS
BEGIN
如何在此用游标将 A 表中所有数据添加到 B 表中？？？？ END
GO
------解决方案--------------------
没装SQLSERVER，只会Oracle下的,自己稍微改一下把
Field1 VARCHAR(1);
CURSOR cursor1 IS
SELECT Field1 FROM TableA
BEGIN
IF NOT cursor1 %ISOPEN THEN
OPEN cursor1 ;
END IF;
LOOP
FETCH cursor1
INTO Field1;
EXIT WHEN cursor1%NOTFOUND;
Insert into B(field) values(Field1);
End Loop;
IF record%ISOPEN THEN
CLOSE record;
END IF;
------解决方案--------------------
刚测试通过了的
- SQL code
Declare @Id int
Declare @Name varchar(10)
DECLARE TabA CURSOR FOR
SELECT aid, aname
FROM AA
Open TabA
Fetch Next From TabA into @Id,@Name
while(@@FETCH_STATUS=0)
begin
insert into BB values(@Id,@Name)
Fetch Next From TabA into @Id,@Name
end
Close TabA
------解决方案--------------------
- SQL code
Delcare @name varchar(50)
Declare MyCursor Cursor for
select Aname from A
Open MyCursor
FETCH NEXT FROM Aname into @name
WHILE @@FETCH_STATUS = 0
BEGIN
insert into B(BName) values(@name)
FETCH NEXT FROM MyCursor into @name
END
CLOSE MyCursor
DEALLOCATE MyCursor
------解决方案--------------------
- SQL code
DECLARE @CityName NVARCHAR(100)
DECLARE table_cur CURSOR FOR
SELECT top 10 CityName FROM City
OPEN table_cur
FETCH NEXT FROM table_cur INTO @CityName
WHILE( @@fetch_status = 0)
BEGIN
PRINT @CityName
FETCH NEXT FROM table_cur INTO @CityName
END
DEALLOCATE table_cur
------解决方案--------------------
加到最后：
- SQL code
Declare @Id int
Declare @Name varchar(10)
DECLARE TabA CURSOR FOR
SELECT aid, aname
FROM AA
Open TabA
Fetch Next From TabA into @Id,@Name
while(@@FETCH_STATUS=0)
begin
insert into BB values(@Id,@Name)
Fetch Next From TabA into @Id,@Name
end
Close TabA
DEALLOCATE TabA
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
