java存储过程面试题_一道存储过程面试题

一道存储过程面试题

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

===========================================================================================================

drop table users1

go

create table users1 (name varchar(10),company varchar(10),company_address varchar(20),url1 varchar(20))

insert into users1

select 'Joe','ABC','Work Lane','abc.com;xyz.com'

union all select 'Jill','XYZ','Job Street','abc.com;xyz.com'

drop proc up_test

go

create proc up_test

as

if exists(select 1 from sysobjects where type='U' and name='users2')

drop table users2

select * into users2

from (

select name,company,company_address,left(url1,charindex(';',url1)-1) as url1

from users1

union all

select name,company,company_address,right(url1,len(url1)-charindex(';',url1))

from users1)t

order by name,company,company_address

GO

exec up_test

select * from users2

/*

name       company    company_address      url1

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

Jill       XYZ        Job Street           abc.com

Jill       XYZ        Job Street           xyz.com

Joe        ABC        Work Lane            xyz.com

Joe        ABC        Work Lane            abc.com

(所影响的行数为 4 行)

*/

============================================================================================================

--應該寫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