一道存储过程面试题
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
到此这篇存储过程 面试题（存储过程面试题）的文章就 介绍到这了,更多相关内容请继续浏览下面的相关 推荐文章，希望大家都能在 编程的领域有一番成就！
版权声明：
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
如若内容造成侵权、违法违规、事实不符，请将相关资料发送至xkadmin@xkablog.com进行投诉反馈，一经查实，立即处理！
转载请注明出处，原文链接：https://www.xkablog.com/jszy-zmms/44952.html