一個簡單的遊標刪除SQL SERVER表，遊標sqlserver表

一個簡單的遊標刪除SQL SERVER表，遊標sqlserver表

use databaseName
declare @tblname char(100)
declare @sql char(5000)
declare table_cursor cursor for select name  from sysobjects where  name like 'tb_card[_]%'  and name<>'Tb_card_regist' and name<>'Tb_card_item' and name<>'Tb_card_discrule' and name<>'tb_card_packagesetdetail' and name<>'tb_card_packagedetail' and name<>'tb_card_exchangeitem'
and  name<>'tb_card_package'
open table_cursor

fetch next from table_cursor into @tblname
WHILE @@FETCH_STATUS = 0
BEGIN
 set @sql='delete from '+@tblname
 exec(@sql)
 print 'delete ' +@tblname + 'successful'
 fetch next from table_cursor into @tblname
END
close table_cursor
deallocate table_cursor

SQL中，定義一個遊標，刪除學生表中第一行資料，怎寫

declare @sex int
declare @grade int
declare my_youbiao cursor
for select sex ,grade from StudentTable
open my_youbiao
fetch next from my_youbiao into @sex, @grade
while @@fetch_status=0
begin
if @sex='男'
begin
update StudentTable set grade=@grade-'2' where current of my_youbiao
end
else
begin
update StudentTable set grade=@grade-'1' where current of my_youbiao
end
fetch next from my_youbiao into @sex, @grade
end
close my_youbiao
deallocate my_youbiao
 
怎在SQL Server裡面編寫一個預存程序，來實現刪除一個表中的重複記錄？

/*******
假設你要處理的表名是: pludetail
可以用以下過程來實現,速度不在下面過程的考慮之中
*********/
create procedure distinct_deal
as
begin

begin transaction

select distinct * into #tempdel from pludetail --提取無重複的記錄到暫存資料表中

truncate table pludetail --清掉原表

insert pludetail
select * from #tempdel --把暫存資料表中無重複的資料插回原表
drop table #tempdel

if @@error<>0
begin
raiserror('資料處理失敗!',16,-1)
goto error_deal
end

commit transaction
return
error_deal:
rollback transaction
return

end

/**

要實現以上過程在指定時間內執行
可以用資料庫的管理中的作業作實現,很簡單,這裡不詳述了
希望這個方法對你有用

**/