SQL存储过程 循环VS游标 ●请教●
特价图书:bookname,publishername,isbn,codeprice,publishdate,authors字段
books里面:bookid,bookname,isbn,authors,publishername,codeprice,discount,publishdate,gettimes字段
目的是把特价图书表里面的isbn和价格相同的图书导入到books表里面
要求:
books表里面有相同的isbn 和价格 则更新books的一条记录
books表里面有不相同的isbn 和价格 或者没有则插入新数据
特价图书 几万条数据
books 200万条数据
现在有现有的存储过程 是用游标写的.但是很慢
我用循环直接写个 但是比游标还要慢~
请各位高手 帮忙设计一下 高分送出
下面是游标代码
ALTER procedure [dbo].[导入图书数据_存储过程]
as
begin ---------------------存储过程开始
declare mycursor cursor for
select bookname,publishername,isbn,codeprice,publishdate,authors from 特价图书
open mycursor
declare @bookid int,@bookname nvarchar(200),@publishername nvarchar(50),@isbn nvarchar(50), @isbn9 nvarchar(50),
@codeprice money,@publishdate datetime,@authors nvarchar(200),@isbncount int,
@bookid1 int,@countfrombookid int
fetch next from mycursor into @bookname,@publishername,@isbn,@codeprice,@publishdate,@authors
select @bookid=max(bookid) from books
insert WangMingtao(bookid) values(1)
while @@fetch_status = 0
begin ------------------------while循环开始
declare @charstr varchar(11) ----检验ISBN的合法性开始
set @charstr='1234567890X'
declare @i int
declare @isbnstr nvarchar(20)
select @i=1
while @i <=len(@isbn)
begin
set @isbnstr=right(left(@isbn,@i),1)
if charindex(@isbnstr, @charstr)=0
begin
set @isbn=replace(@isbn,@isbnstr,'#')
end
set @i=@i+1
end
set @isbn=replace(@isbn,'#','')
set @isbn9=@isbn
if len(@isbn)=13
begin
set @isbn9=substring(@isbn,4,9)
end
if len(@isbn)=10
begin
set @isbn9=left(@isbn,9)
end
----检验ISBN的合法性结束
print('11')
set @bookid=@bookid+1 ----------------------bookid加一
select @isbncount=count(isbn) from books where left(isbn,9)=@isbn9 and codeprice=@codeprice
print('1')
if @isbncount <> 0
begin ---------如果在books中存在该isbn
update books set status=1,isbn=@isbn where left(isbn,9)=@isbn9 and codeprice=@codeprice
select @bookid1=bookid from books where left(isbn,9)=@isbn9 and codeprice=@codeprice
insert WangMingtao(bookid) values(@bookid1)
select @countfrombookid=count(bookid) from frombook where bookid=@bookid1 and [from]=11
if @countfrombookid <> 0
begin
update frombook set discount=80 where bookid=@bookid1 and [from]=11
end
else
begin
insert into frombook ([from],bookid,stocknum,seat,isupdated,discount) values
(11,@bookid1,5,'0',0,80)
end
end ---------如果在books中存在该isbn
else
begin ---------不存在
insert books(bookid,bookname,isbn,authors,publishername,codeprice,discount,publishdate,gettimes)
values
(@bookid,@bookname,@isbn,@authors,@publishername,@codeprice,80,@publishdate,getdate())
insert WangMingtao(bookid) values(@bookid)
insert frombook([from],bookid,stocknum,seat,isupdated,discount) values
(11,@bookid,5,'0',0,80)
end ---------不存在
fetch next from mycursor into @bookname,@publishername,@isbn,@codeprice,@publishdate,@authors
end------------------------while循环结束
close mycursor
deallocate mycursor
end -----------------存储过程结束
set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgo-----创建存储过程ALTER procedure [dbo].[导入新书]asbegin ---------------------存储过程开始declare @bookid int,@bookname nvarchar(200),@publishername nvarchar(50),@isbn varchar(50), @isbn9 nvarchar(50), @codeprice money,@publishdate datetime,@authors nvarchar(200),@isbncount int,@booksisbn int,@bookid1 int,@countfrombookid intselect @bookid=max(bookid) from booksdeclare @i
set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgo------自定义函数,检验ISBN的合法性ALTER function [dbo].[ChkIsbn](@Aisbn nvarchar(50)) returns nvarchar(50)asbegindeclare @charstr varchar(11),@isbn9 nvarchar(50)set @charstr='1234567890X'declare @i intdeclare @isbnstr nvarchar(20)select @i=1while @i<=len(@Aisbn)beginset @isbnstr=right(left(@Aisbn,@i),1) if charindex(@isbnstr, @charstr)=0 begin set @Aisbn=replace(@Aisbn,@isbnstr,'#') endset @i=@i+1endset @Aisbn=replace(@Aisbn,'#','')set @isbn9=@Aisbnif len(@Aisbn)=13set @isbn9=substring(@Aisbn,4,9)if len(@Aisbn)=10 set @isbn9=left(@Aisbn,9) return @isbn9end
create function checkISBN(@isbn nvarchar库
[解决办法]
建立了数据函数[dbo].[ChkIsbn]后，不妨这样来处理：
（请检查一下books, 世纪书缘库存这两张表是否有对isbn,codeprice的索引，如果没有的话，建立索引后速度上会提高很多）
--1)books表里面有相同的isbn 和价格 则更新books的一条记录
update t1
set status=1,isbn=t2.isbn
from books t1, 世纪书缘库存 t2
where left(t1.isbn,9)=dbo.ChkIsbn(t2.isbn)
and t1.codeprice=t2.codeprice
--2)books表里面有不相同的isbn 和价格 或者没有则插入新数据
declare @intMaxBookid int
select @intMaxBookid=max(bookid) from books
declare @tblSpecialBooks table (bookid int identity(1,1),isbn nvarchar(50))
--假定isbn为“世纪书缘库存”的主键，如果是主键为其他字段，则改为其他字段关联
insert into @tblSpecialBooks(isbn)
select isbn
from 世纪书缘库存
where dbo.ChkIsbn(t2.isbn) not in (select distinct left(t1.isbn,9) from books)
insert books(bookid,bookname,isbn,authors,publishername,codeprice,discount,publishdate,gettimes,status)
select @intMaxBookid+t2.bookid,bookname,isbn,authors,publishername,codeprice,discount,publishdate,gettimes,status
from 世纪书缘库存 t1, @tblSpecialBooks t2
where t1.isbn=t2.isbn
[解决办法]
