游标的使用

先举例说下游标的基本用法：

DECLARE   @name   varchar(40)  
   
  DECLARE   test_cursor   CURSOR   --test_cursor   为游标名称  
   
  FOR   SELECT   au_fname   FROM   authors   --你要逐条记录滚动的表authors    
   
  OPEN   test_cursor     --打开游标  
   
  FETCH   NEXT   FROM   test_cursor   INTO   @name   --将表   authors   中每条记录的   au_fname值逐一赋值给@name  
  WHILE   (@@fetch_status   <>   -1)  
  BEGIN  
        --写你想要处理的SQL语句  
  FETCH   NEXT   FROM   <cursor_name,   sysname,   test_cursor>   INTO   @name  
  END  
   
  CLOSE   test_cursor   --关闭游标  
  DEALLOCATE   test_cursor   --释放游标 

再结合存储过程来举例说下游标使用
 题目:

users1

　　name    company    company_address   url1
　　Joe     ABC        Work Lane        abc.com;xyz.com
　　Jill    XYZ        Job Street       abc.com;xyz.com
写存储过程来创建新表
users2
　　name company company_address url1

　　Joe ABC Work Lane abc.com
    Joe ABC Work Lane xyz.com
　　Jill XYZ  Job Street abc.com
    Jill XYZ  Job Street xyz.com
要是分不够还可以再加。还有别的小问题要提问

------------------------------------------------------------------------------------------------------------------

--應該寫function,然後根據;拆分紀錄

GO
--FUNCTION
Create    FUNCTION SplitList
(@separator char(1), @List varchar(8000))
RETURNS @ReturnTable TABLE(ListItem varchar(1000) COLLATE Database_Default)
AS
BEGIN
DECLARE @Index int
DECLARE @NewText varchar(8000)
IF @List = null
RETURN
SET @Index = CHARINDEX(@separator, @List)
WHILE NOT(@Index = 0)
BEGIN
SET @NewText = RTRIM(LTRIM(LEFT(@List, @Index - 1)))
SET @List = RIGHT(@List, LEN(@List) - @Index)
INSERT INTO @ReturnTable(ListItem) VALUES(@NewText)
SET @Index = CHARINDEX(@separator, @List)
    END
 
INSERT INTO @ReturnTable(ListItem) VALUES(RTRIM(LTRIM(@List)))
        
     RETURN
END

GO
--測試數據
create table users1(name  varchar(10) ,  company  varchar(20),  company_address  varchar(20), url1 varchar(100) )
insert into users1 select    'Joe'   ,'ABC'  ,'Work Lane'  ,'abc.com;xyz.com'
insert into users1 select   'Jill'   ,'XYZ'  ,'Job Street' , 'abc.com;xyz.com'

create table users2(name  varchar(10) ,  company  varchar(20),  company_address  varchar(20), url1 varchar(100) )
GO
--存儲過程

Create procedure dbo.usp_test
AS

declare @name  varchar(10) ,  @company  varchar(20),  @company_address  varchar(20), @url1 varchar(100)
declare c1 cursor for
select * from users1
open c1
fetch next from c1 into @name,@company,@company_address,@url1
while @@fetch_status=0
begin
  insert into users2
   select @name,@company,@company_address,ListItem from dbo.SplitList(';',@url1) T
  fetch next from c1 into @name,@company,@company_address,@url1
end
close c1
deallocate c1

GO

exec dbo.usp_test
select * from users2
/*
name    company      company_address           url1
Joe ABC  Work Lane            abc.com
Joe ABC  Work Lane            xyz.com
Jill XYZ  Job Street         abc.com
Jill XYZ  Job Street          xyz.com

*/

drop table users1,users2
drop proc usp_test
drop function splitlist