-- 生成编码规则调到的 编码更新 T-SQL
CREATE FUNCTION dbo.f_ChangeCodeRule(
@CodeRule_Old varchar(50), -- 以逗号分隔的旧的编码规则, 每层编码的长度. 比如 1, 2, 3, 表示有三层编码,第一层长度为 1, 第二层长度为 2, 第三层长度为 3
@CodeRule_New varchar(50), -- 以逗号分隔的旧的编码规则, 如果某个层次的编码长度为 0, 表示删除该层编码
@CharFill char(1), -- 扩充编码时,填充的字符
@Position int, -- 为 0, 从编码的最前面开始压缩或者填充, 为 -1 或者大于旧编码的长度, 从最后一位开始处理, 为其他值, 从指定的位置后开始处理
@FieldName sysname -- 编码字段名
)RETURNS nvarchar(4000)
AS
BEGIN
IF ISNULL(@CharFill, '') = ''
SET @CharFill = '0'
-- 将编码规则拆分为表
-- a. 拆分旧的编码规则
DECLARE @tb_Code_Old TABLE(
ID int IDENTITY,
CodeLen int,
CodeLens int,
Code nvarchar(200))
DECLARE
@CodeLen int,
@CodeLens varchar(20)
SELECT
@CodeRule_Old = @CodeRule_Old + ',',
@CodeLens = 1
WHILE @CodeRule_Old > ''
BEGIN
SELECT
@CodeLen = LEFT(@CodeRule_Old, CHARINDEX(',', @CodeRule_Old) - 1),
@CodeRule_Old = STUFF(@CodeRule_Old, 1, CHARINDEX(',', @CodeRule_Old), '')
INSERT @tb_Code_Old(
CodeLen, CodeLens, Code)
VALUES(
@CodeLen, @CodeLens,
-- 取当前层次编码的 T-SQL 语句
N'SUBSTRING(' + @FieldName + N', ' + RTRIM(@CodeLens) + N', ' + RTRIM(@CodeLen) + N')')
SET @CodeLens = @CodeLens + CONVERT(int, @CodeLen)
END
-- b. 拆分新的编码规则
DECLARE @tb_Code_New TABLE(
ID int IDENTITY,
CodeLen int)
SET @CodeRule_New = @CodeRule_New + ','
WHILE @CodeRule_New > ''
BEGIN
INSERT @tb_Code_New(
CodeLen)
VALUES(
LEFT(@CodeRule_New, CHARINDEX(',', @CodeRule_New) - 1))
SET @CodeRule_New = STUFF(@CodeRule_New, 1, CHARINDEX(',', @CodeRule_New), '')
END
-- 生成编号规则修改处理语句
DECLARE
@sql nvarchar(4000)
SET @sql = N''
SELECT
@sql = @sql
+ CASE
WHEN N.CodeLen = 0 THEN '' -- 新编码长度为 0, 表示去掉这段编码
ELSE N'
+ CASE
-- 不包含当前层次编码的记录不需要处理
WHEN LEN(' + @FieldName + N') < ' + CAST(O.CodeLens as varchar) + N'
THEN ''''
ELSE '
+ CASE
WHEN N.CodeLen = O.CodeLen THEN O.Code --新旧编码长度相同时不需要处理
WHEN N.CodeLen > O.CodeLen
THEN CASE -- 扩充编码长度的处理, 根据 @Position 和旧编码长度决定编码的填充位置
WHEN @Position = -1 OR @Position >= O.CodeLen
THEN O.Code + N' + ' + QUOTENAME(REPLICATE(@CharFill,N.CodeLen - O.CodeLen), N'''')
ELSE N'STUFF(' + O.Code + N', ' + CAST(@Position + 1 as varchar)
+ N', 0, ' + QUOTENAME(REPLICATE(@CharFill, N.CodeLen - O.CodeLen), N'''')
+ N')'
END
ELSE CASE -- 收缩编码长度的处理, 根据 @Position 和新编码长度决定编码的截取位置
WHEN @Position = -1 OR @Position > N.CodeLen
THEN N'LEFT(' + O.Code + N',' + CAST(N.CodeLen as varchar) + N')'
ELSE N'STUFF(' + O.Code + N', ' + CAST(@Position + 1 as varchar)
+ N', ' + CAST(O.CodeLen - N.CodeLen as varchar)
+ N', '''')'
END
END
+ N'
END'
END
FROM @tb_Code_Old O, @tb_Code_New N
WHERE O.ID = N.ID
RETURN(
STUFF(@sql, CHARINDEX(N'+', @sql), 1, N''))
END
GO
-- 调整编码规则的存储过程(需要与前面的函数配合使用)
CREATE PROC dbo.p_ChangeCodeRule
@TableName sysname, -- 调整编码规则的表名
@FieldName sysname, -- 编码字段名
@CodeRule_Old varchar(50), -- 以逗号分隔的旧的编码规则,每层编码的长度,比如 1, 2, 3, 表示有三层编码,第一层长度为 1, 第二层长度为 2, 第三层长度为 3
@CodeRule_New varchar(50), -- 以逗号分隔的旧的编码规则,如果某个层次的编码长度为 0,表示删除该层编码
@CharFill char(1) = '0', -- 扩充编码时,填充的字符
@Position int = 0 -- 为 0, 从编码的最前面开始压缩或者填充,为 -1 或者大于旧编码的长度, 从最后一位开始处理,为其他值,从指定的位置后开始处理
AS
-- 参数检查
IF ISNULL(OBJECTPROPERTY(OBJECT_ID(@TableName), N'IsUserTable'), 0) = 0
BEGIN
RAISERROR(N'"%s" 不存在,或者不是用户表',16, 1, @TableName)
RETURN
END
IF NOT EXISTS(
SELECT * FROM dbo.syscolumns
WHERE ID = OBJECT_ID(@TableName)
AND name = @FieldName)
BEGIN
RAISERROR(N'列名 "%s" 在用户表 "%s" 中不存在',16, 1, @FieldName, @TableName)
RETURN
END
IF ISNULL(@CodeRule_Old, '') = ''
OR ISNULL(@CodeRule_New, '') = ''
BEGIN
RAISERROR(N'必须编码规则字符串', 16, 1)
RETURN
END
IF PATINDEX(N'%[^0-9^,]%', @CodeRule_Old) > 0
BEGIN
RAISERROR(N'编码规则字符串 "%s" 中只能包含数字和逗号(,)', 16, 1, @CodeRule_Old)
RETURN
END
IF PATINDEX(N'%[^0-9^,]%', @CodeRule_New) > 0
BEGIN
RAISERROR(N'编码规则字符串 "%s" 中只能包含数字和逗号(,)', 16, 1, @CodeRule_New)
RETURN
END
-- 调用函数 dbo.f_ChangeCodeRule 得到编码处理的 T-SQL 语句
DECLARE
@s nvarchar(4000)
SET @s = dbo.f_ChangeCodeRule(@CodeRule_Old, @CodeRule_New, @CharFill, @Position, @FieldName)
-- 更新编码规则
EXEC(N'
BEGIN TRAN
-- 将处理后的编码与处理前的编码保存到临时表
SELECT
No_Old = ' + @FieldName + N',
No_New = ' + @s + N'
INTO #
FROM ' + @TableName + N' WITH(XLOCK, TABLOCK)
-- 检查更新后的编码是否存在重复
IF EXISTS(
SELECT
No_New
FROM #
GROUP BY No_New
HAVING COUNT(*) > 1)
BEGIN
-- 如果重复, 则显示会产生生理的编码
SELECT * FROM # A
WHERE EXISTS(
SELECT * FROM #
WHERE No_New = A.No_New
AND No_Old <> A.No_Old)
ORDER BY No_New, No_Old
ROLLBACK TRAN
END
ELSE
BEGIN
-- 如果编码处理后不重复, 则更新到编码表中
UPDATE A SET
' + @FieldName + N' = B.No_New
FROM ' + @TableName + N' A, # B
WHERE A.' + @FieldName + N' = B.No_Old
COMMIT TRAN
END
')
GO
-- 调用示例
-- a. 测试数据表
CREATE TABLE dbo.tb(
No varchar(10))
INSERT dbo.tb SELECT '1'
UNION ALL SELECT '3'
UNION ALL SELECT '302'
UNION ALL SELECT '305'
UNION ALL SELECT '305001'
UNION ALL SELECT '305005'
UNION ALL SELECT '305101'
UNION ALL SELECT '6'
UNION ALL SELECT '601'
-- b.1 调整编码规则 - 会导致重复
EXEC dbo.p_ChangeCodeRule
@TableName = N'dbo.tb',
@FieldName = N'No',
@CodeRule_Old = '1,2,3',
@CodeRule_New = N'3,2,2',
@CharFill = '0',
@Position = 0
-- b.2 调整编码规则 - 不会导致重复
EXEC dbo.p_ChangeCodeRule
@TableName = N'dbo.tb',
@FieldName = N'No',
@CodeRule_Old = '1,2,3',
@CodeRule_New = N'3,2,2',
@CharFill = '0',
@Position = 1
-- c. 显示结果
SELECT * FROM dbo.tb
DROP TABLE dbo.tb
GO
DROP PROC dbo.p_ChangeCodeRule
DROP FUNCTION dbo.f_ChangeCodeRule
深入浅出SQL Server 2000开发、管理与应用实例 源代码
需积分: 10 63 浏览量
2014-12-17
21:39:50
上传
评论 1
收藏 173KB ZIP 举报
深入浅出SQL Server 2000开发、管理与应用实例 源代码 （133个子文件） 
01. 调整编码规则.sql 6KB 02. 移动与复制结点.sql 6KB 7.2.1 使用TOP N实现分页.sql 5KB 6.4.2 同期及上期数据对比.sql 5KB 7.3.1 随机显示的分页处理.sql 4KB 02.结存表.sql 4KB 10.1.2 使用系统存储过程定义作业的常用模板.sql 4KB 01. 删除某个结点.sql 4KB 02. 按日编号.sql 4KB 01.直接查询.sql 3KB 13.2.5 字段相关对象查询.sql 3KB 6.4.3 销售排行榜.sql 3KB 06. 差异备份和日志备份还原中的常见故障.sql 3KB 7.2.3 使用临时表.sql 3KB 7.2.2 使用字符串.sql 3KB 13.2.3 搜索指定字符在哪个表的哪个字段中.sql 3KB a. 阻塞进程信息.sql 3KB 01. 结点复制.sql 3KB 13.3.5 在两个SQL Server数据库之间复制对象.sql 3KB 02. 重排通用存储过程.sql 3KB 5.4.4 缺勤天数统计.sql 3KB 01. 列车车次查询.sql 3KB 03. 分拆求平均.sql 3KB 8.3.2 公交车乘车线路查询.sql 3KB 7.3.2 分类数据的分页处理.sql 3KB 01. 将所有的char、varchar改为nchar、nvarchar.sql 3KB 11.4.3 数据库还原中的孤立用户问题.sql 3KB 6.1.6 随机记录查询.sql 3KB 02.普通列转换为标识列.sql 3KB 6.3.4 动态列中的字符溢出处理.sql 2KB 6.1.3 UNION与UNION ALL.sql 2KB 13.2.2 获取存储过程参数定义.sql 2KB 11.3.7 同步备用服务器.sql 2KB 5.4.3 生成已用编号和缺号分布字符串.sql 2KB 01. 日期加减处理.sql 2KB 02.循环数据校验.sql 2KB 01. 工作时间计算.sql 2KB 3.7.5 ntext字段的REPLACE处理.sql 2KB 8.3.1 产品配件清单.sql 2KB 6.3.5 特殊的交叉报表.sql 2KB 01. 查找父结点.sql 2KB 11.4.2 使用媒体集备份时的常见问题.sql 2KB 02. 逐级汇总存储过程.sql 2KB b 锁信息.sql 2KB 01. 维护索引.sql 2KB 01. 多个调度.sql 2KB 13.2.1 搜索指定的对象所处的数据库.sql 2KB 01. 执行耗时的存储过程.sql 2KB 6.1.2 联接.sql 2KB 02. 存储过程.sql 2KB 02. 不正确的统计信息影响查询方案选择.sql 2KB 01. 统计提高查询性能.sql 2KB 6.2.4 分级汇总结果的排序处理.sql 2KB 13.2.6 生成数据库字典.sql 2KB 01. 编码累计函数.sql 2KB 6.1.1 空（NULL）值对查询的影响.sql 2KB 6.1.5 上限与下限区间统计.sql 2KB 01. 部分数据文件被破坏时的数据恢复处理.sql 2KB 03. 编写汉字首字母查询处理用户定义函数.sql 2KB 5.4.1 已用编号分布查询.sql 1KB a 隐性事务.sql 1KB 01.标识列转换为普通列.sql 1KB 01. 部分数据库还原.sql 1KB 13.2.4 非UNICODE字段改为UNICODE字段的可行性查询.sql 1KB 13.3.4 把列添加指定位置.sql 1KB 02.a 深度排序(递归).sql 1KB 11.3.5 将数据还原到指定时间点.sql 1KB 11.3.6 将数据还原到指定标记点.sql 1KB 6.3.3 动态列.sql 1KB 04. 排名应用.sql 1KB 02. 字符串交集.sql 1KB 01. 数字编号.sql 1KB 01.多个汇总列.sql 1KB 6.2.3 分级汇总结果显示格式处理.sql 1KB 02.b 深度排序(单编号).sql 1KB 01. 简单应用示例.sql 1KB 01. 重排示例.sql 1KB 01. SET DATEFORMAT.sql 1KB 5.4.2 缺号分布查询.sql 1KB 01. 查询指定日期过生日的人员.sql 1KB 3.3.1 游标合并法.sql 1KB 02. 数据文件完全被破坏，仅有日志文件时的数据恢复处理.sql 1KB 01. 定时启用（或者停用）数据库.sql 1KB 02. 进行全角与半角字符的转换.sql 1KB 01. 字符串并集.sql 1KB a. 死锁跟踪示例.sql 1KB a. 触发器中的事务.sql 1KB 02.多个列转换为行记录.sql 1KB 01. 数据项完全相同.sql 1KB 6.2.1 实现分级汇总处理 - 01.UNION ALL.sql 1KB a. 事务保存点.sql 1KB 01. 结点删除.sql 1KB 11.1.4 数据文件或文件组备份与还原.sql 1KB 03. 大小字母混合编号.sql 1KB 01. 使用临时表实现编号重排.sql 1KB 01. 记录数统计.sql 1KB 01. 广度排序.sql 1KB 3.2.3 辅助表法.sql 1KB 02. 分拆求和.sql 1KB 02. 得到每个字母的开始和结束的汉字.sql 987B共 133 条
- 1
- 2
资源评论
