我寫了一個存儲過程(其實是在以前前輩指教的觸發器基礎上改的),用來執行將入庫表storein中的數據更新至庫存表store中,同時入庫表中的入庫標記更新為1(表示已經入庫).可是每執行一次這個過程,只能更新入庫表storein中的一行數據,我想讓它一次更新所有入庫表中的mark為0的數據到庫存表中,請教該如何改進? create procedure sp_store as begin declare @mater_no varchar(12), @price real, @quantity real, @mark int declare cur_storein scroll cursor for select mater_no,price,quantity,mark from storein where mark=0 open cur_storein fetch cur_storein into @mater_no,@price,@quantity,@mark while (@@fetch_status=0) begin if (select count(*) from store where mater_no=@mater_no)=0 insert store (mater_no,price,quantity) values (@mater_no,@price,@quantity) else update store set price=((price*quantity @price*@quantity)/(quantity @quantity)),quantity=quantity @quantity from store where mater_no = @mater_no update storein set mark=1 where mater_no=@mater_no fetch next from cur_storein into @mater_no,@price,@quantity end close cur_storein deallocate cur_storein return end

hi, 你的 stored procedure 看起來應該是沒有辦法進入 while 迴圈之中的, 因為你的第一個 fetch 並未使用 fetch next , 會使得 @@fetch_status 不為 0 , 而導致無法進入 while 迴圈中, 而且也漏了 from, 請修改一下再試試看:    

create procedure sp_store as
begin
  declare  @mater_no varchar(12),
           @price       real,
           @quantity     real,
          @mark int
  declare cur_storein scroll cursor 
       for select mater_no,price,quantity,mark from storein where mark=0
  open cur_storein
  fetch next from cur_storein into @mater_no,@price,@quantity,@mark
  while (@@fetch_status=0)
  begin
      if (select count(*) from store where mater_no=@mater_no)=0
        insert store (mater_no,price,quantity) values (@mater_no,@price,@quantity)
      else
        update store set price=((price*quantity @price*@quantity)/(quantity @quantity)),quantity=quantity @quantity
          from store where mater_no = @mater_no
    update storein set mark=1 where  mater_no=@mater_no
    fetch next from cur_storein into @mater_no,@price,@quantity
  end
  close cur_storein
  deallocate cur_storein
  return
end

謝謝timhuang兄,現在問題解決了.不過有點奇怪,得先把@mark都刪除,不知道是否是因為后邊的update沒有使用到這個字段或者是后一個fetch沒有@mark字段?

FETCH 資料放入變數中, 必須和該 Cursor 的 select 欄位(字段, column)數目相同, 如你的 cursor 是    declare cur_storein scroll cursor  for select mater_no,price,quantity,mark from storein where mark=0 則一共有 4個欄位, 所以使用 fetch next from cur_storein 時, 後面的 into 也必須同時有 4個變數來接, 因此, 第二次的 fetch 中, 你的變數少了一個, 將不能正常的執行! fetch next from cur_storein into @mater_no,@price,@quantity 這行會發生問題的, 少了一個變數哦~