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 ---------不存在 <