首页 诗词 字典 板报 句子 名言 友答 励志 学校 网站地图
当前位置: 首页 > 教程频道 > 网站开发 > asp.net >

在sqlServer中怎么用游标遍历表?请进····

2012-02-05 
在sqlServer中如何用游标遍历表??请进如题:if exists(select * from sysobjects where nameA) drop tab

在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;
[解决办法]
刚测试通过了的

SQL code
Declare @Id intDeclare @Name varchar(10)DECLARE TabA CURSOR FOR SELECT aid, aname FROM AAOpen TabAFetch Next From TabA into @Id,@Namewhile(@@FETCH_STATUS=0)    begin    insert into BB values(@Id,@Name)    Fetch Next From TabA into @Id,@Name    endClose 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 FORSELECT top 10 CityName FROM CityOPEN table_curFETCH NEXT FROM table_cur INTO @CityNameWHILE(    @@fetch_status = 0)BEGIN    PRINT    @CityName    FETCH NEXT FROM table_cur INTO @CityNameENDDEALLOCATE table_cur
[解决办法]
加到最后:
SQL code
Declare @Id intDeclare @Name varchar(10)DECLARE TabA CURSOR FOR SELECT aid, aname FROM AAOpen TabAFetch Next From TabA into @Id,@Namewhile(@@FETCH_STATUS=0)    begin    insert into BB values(@Id,@Name)    Fetch Next From TabA into @Id,@Name    endClose TabADEALLOCATE TabA 

热点排行