自己寫的存儲(chǔ)過程與游標(biāo)結(jié)合使用的實(shí)例，與大家分享，也供自己查閱，僅供參考：
--
使用游標(biāo)循環(huán)處理，刪除重復(fù)的記錄
declare
@UserID
int
declare
@UserName
varchar
(
32
)
declare
@RealName
varchar
(
32
)
declare
@UnitFlag
int
declare
@Email2
varchar
(
64
)
declare
@Mobile
varchar
(
64
)
declare
@Start
int
declare
@End
int
declare
@Type
varchar
(
16
)
declare
@IsSubscribe
bit
declare
curEmailTotalLib
cursor
for
(
select
UserID,UserName,RealName,UnitFlag,Email,Mobile,IsSubscribe
from
Task_IntermediateData)
open
curEmailTotalLib
--
打開游標(biāo)
fetch
next
from
curEmailTotalLib
into
@UserID
,
@UserName
,
@RealName
,
@UnitFlag
,
@Email2
,
@Mobile
,
@IsSubscribe
while
@@fetch_status
=
0
--
獲取成功
begin
--
在郵件系統(tǒng)總庫中不存在此用戶ID，不存在此郵箱，并且用戶訂閱過
if
not
exists
(
select
*
from
Task_EmailTotalLib
where
UserID
=
@UserID
)
and
not
exists
(
select
*
from
Task_EmailTotalLib
where
Email
=
@Email2
)
and
@IsSubscribe
=
1
begin
set
@Start
=
charindex
(
'
@
'
,
@Email2
,
0
)
set
@End
=
charindex
(
'
.
'
,
@Email2
,
@Start
)
if
@Start
!=
0
and
@End
!=
0
begin
--
不是垃圾郵件
if
@Email2
is
not
null
and
ltrim
(
rtrim
(
@Email2
))
<>
''
begin
if
not
exists
(
select
*
from
Task_JunkEmail
where
Email
=
@Email2
)
begin
begin
try
set
@Type
=
substring
(
@Email2
,
@Start
+
1
,
@End
-
@Start
-
1
)
if
@Type
!=
'
qq
'
and
@Type
!=
'
126
'
and
@Type
!=
'
163
'
and
@Type
!=
'
sina
'
and
@Type
!=
'
sohu
'
and
@Type
!=
'
gmail
'
and
@Type
!=
'
hotmail
'
and
@Type
!=
'
yahoo
'
and
@Type
!=
'
139
'
and
@Type
!=
'
263
'
and
@Type
!=
'
yeah
'
and
@Type
!=
'
cnki
'
begin
set
@Type
=
'
extra
'
end
insert
into
Task_EmailTotalLib(UserID, UserName, RealName, Email, Mobile,
Priority, MailType, LibType, FpIsSend, CpIsSend, UpIsSend, VpIsSend, WpIsSend,
XpIsSend, YpIsSend, ZpIsSend, SendCount, SucCount, FailCount, CreditRate, IsJunkEmail,
IsSubscribe, IsUsed, Memo)
values
(
@UserID
,
@UserName
,
@RealName
,
@Email2
,
@Mobile
,
2
,
@Type
,
@UnitFlag
,
0
,
0
,
0
,
0
,
0
,
0
,
0
,
0
,
0
,
0
,
0
,
10
,
0
,
@IsSubscribe
,
0
,
''
)
end
try
begin
catch
print
'
@Email2:
'
+
@Email2
+
'
charindex(
''
@
''
, @Email2, 0)
'
+
charindex
(
'
@
'
,
@Email2
,
0
)
+
'
@Start
'
+
@Start
+
'
@End
'
+
@End
+
'
@End - @Start - 1:
'
+
@End
-
@Start
-
1
end
catch
end
end
end
end
fetch
next
from
curEmailTotalLib
into
@UserID
,
@UserName
,
@RealName
,
@UnitFlag
,
@Email2
,
@Mobile
,
@IsSubscribe
end
close
curEmailTotalLib
--
關(guān)閉游標(biāo)
deallocate
curEmailTotalLib
--
釋放游標(biāo)
觸發(fā)器實(shí)例：插入數(shù)據(jù)時(shí)，觸發(fā)器獲取這條數(shù)據(jù)ID，自動(dòng)修改，比程序處理更方便。
Create trigger tg_url_update? on [dbo].UrlTotal for insert as
declare @getid int;?
declare @url varchar(128);
set @getid=(select id from inserted);
set @url='Test.aspx?id='+cast(@getid as varchar(50))?
update UrlTotal set
url=@url
where
id=@getid
?????? 謝謝閱讀~~
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號(hào)聯(lián)系： 360901061
您的支持是博主寫作最大的動(dòng)力，如果您喜歡我的文章，感覺我的文章對(duì)您有幫助，請(qǐng)用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點(diǎn)擊下面給點(diǎn)支持吧，站長非常感激您！手機(jī)微信長按不能支付解決辦法：請(qǐng)將微信支付二維碼保存到相冊(cè)，切換到微信，然后點(diǎn)擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對(duì)您有幫助就好】元
