SQL语句块使用总结

1：概论

在实际开发中，有可能需要写一些SQL语句块脚本，按照实际需要修改数据，或者做其他操作。下面我只做，我对于之前开发遇到的一些需求，并做的一些SQL脚本实现，只做参考，可当例子回忆。

2：例子演示:

2.2例子：

需求：为特定表assetborrowOut添加字段，但由于开发人员没有权限，操作测试人员和实施人员（正式上线的环境）中的库，所以，只能通过给脚本的形式，给他们执行，达到一致的效果。

分析：比较简单，语句块中，为表添加字段，跟正常的添加、修改字段没什么区别

代码演示：

--AssetBorrowOut表增加erCode字段。
begin tran
alter table AssetBorrowOut
add erCode NVARCHAR(20)
--添加字段说明
EXECUTE sp_addextendedproperty N'MS_Description', N'调出单号', N'user', N'dbo', N'table', N'AssetBorrowOut', N'column', N'erCode'
commit tran
GO

 

2.2例子1：

需求：现在有一个表assetborrowOut（已经有数据），然后在表中新增一个字段（类似单号，内容格式为前缀DB+日期+序号：DB201808250001）。

分析：将所有数据，查找出来，针对结果集的每行特定字段，循环赋予值；并且前缀固定，序号为4位数，从0001-9999，超出了，就日期加1。

代码演示：

--切换库（sql server语法，其他类型数据库，不清楚）
use [CFAsset-xh]

begin
    --这里用来记录，游标中字段，游标中只记录三个字段
    --记录：行数据，当前表主键ID
	declare @borrowOutId int
    --记录：行数据，当前编号
	declare @erCode varchar(20)
    --记录：行数据，时间
	declare @borrowerCheckTime smalldatetime
    
    --记录总数，超过9999，归0，日期加1
	declare @total int
	set @total = 0
	--日期
	declare @date varchar(50)
	--前缀日期
	declare @prefix varchar(50)
	--尾数
	declare @suffix varchar(50)
	--游标定义，类似一个集合，用于后面循环集合，并做相关业务操作
	declare cur_erCode cursor FOR
	select borrowOutId,erCode,borrowerCheckTime from dbo.assetborrowout where erCode is null or erCode = ''
	--打开游标，遍历游标必须先打开，遍历结束，需要关闭，并释放
	open cur_erCode
	fetch next from cur_erCode into @borrowOutId,@erCode,@borrowerCheckTime
	while(@@fetch_status=0)--返回被 FETCH 语句执行的最后游标的状态，而不是任何当前被连接打开的游标的状态。
	begin
		--加1
		set @total = @total + 1
		set @date = CONVERT(varchar(20),GETDATE(),112)
		--尾缀设置
		--长度为1
		if DataLength(cast(@total as varchar(20)))=1
			begin
				set @prefix = @date
				set @suffix = '000' + cast(@total as varchar(20))
			end
		
		--长度为2
		if DataLength(cast(@total as varchar(20)))=2
			begin
				set @prefix = @date
				set @suffix = '00' + cast(@total as varchar(20))
			end
		
		--长度为3
		if DataLength(cast(@total as varchar(20)))=3
			begin
				set @prefix = @date
				set @suffix = '0' + cast(@total as varchar(20))
			end
			
		--长度为4
		if DataLength(cast(@total as varchar(20)))=4
			begin
				set @prefix = @date
				set @suffix = cast(@total as varchar(20))
			end
			
		--长度为5
		if DataLength(cast(@total as varchar(20)))=5
			begin
				--超过五位数，日期加一天，重置计数器
				set @date = CONVERT(varchar(20),dateadd(day,1,@date),112)
				set @total = 1
				set @prefix = @date
				set @suffix = '000' + cast(@total as varchar(20))
			end
		
		--若是调入人已经确认，那么状态修改为2，增加单号
		if @borrowerCheckTime is not null
			begin
				update dbo.assetborrowout set erCode = 'DB' + @prefix + @suffix, status = 2
				where borrowOutId = @borrowOutId
			end
		
		--若是调入人未确认，那么状态修改为0，增加单号
		if @borrowerCheckTime is null
			begin
				update dbo.assetborrowout set erCode = 'DB' + @prefix + @suffix, status = 1
				where borrowOutId = @borrowOutId
			end

		fetch next from cur_erCode into @borrowOutId,@erCode,@borrowerCheckTime  --转到下一个游标，没有会死循环
	end
	--关闭游标
	CLOSE cur_erCode
	--释放游标
	DEALLOCATE cur_erCode
end