SQL SERVER游标理解
DECLARE @TableName VARCHAR(50)
DECLARE @SQL NVARCHAR(1000)
DECLARE @lcErrMsg VARCHAR(200)
DECLARE cr_upd CURSOR FOR SELECT s2.name FROM syscolumns s JOIN sysobjects s2 ON s2.id = s.id
WHERE s.name = ‘pdctno’
AND s2.[type] = ‘U’
AND s2.name NOT IN (‘mktxhglb’,’mkt_jmkc’,’mktbjzyb’,’mktfpglb’,’mktfpglbbak’) –定义一个游标为cr_upd 内容为select……..
OPEN cr_upd –打开游标
FETCH NEXT FROM cr_upd INTO @TableName–第一个没有数据滴，自动下移一行取值，【游标与while大区别在于，游标这里可以into很多列】
WHILE @@FETCH_STATUS = 0 –循环，当取值存在时
BEGIN–判定
PRINT @TableName
SELECT @SQL = ‘update ‘ + @TableName + ‘ set pdctno = UPPER(pdctno) where pdctno COLLATE Chinese_PRC_CS_AS LIKE ”%[abcdefghijklmnopqrstuvwxyz]%” ‘–查询一个修改语句
EXEC sp_executesql @SQL–执行这条修改语句
IF @@ERROR <> 0-
BEGIN
CLOSE cr_upd
DEALLOCATE cr_upd
ROLLBACK
SELECT @lcErrMsg = ‘更新表’+@TableName+’型号失败。’
RETURN –报错回滚
END
FETCH NEXT FROM cr_upd INTO @TableName–继续修改下一行数据
END–结束
CLOSE cr_upd–关闭游标
DEALLOCATE cr_upd–销毁游标