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