SQL之游标实例

--select * from HouseFwdanb
--select * from Ddb
alter trigger insert_housefwdanb_ddb
on housefwdanb
after insert
as
begin
declare @fjbh varchar(50) --房间编号
declare @mc varchar(100) --名称
declare @ddzt varchar(2) --地点状态
declare @sjdd varchar(50) --上级地点
declare @ldid varchar(255) --楼栋id
declare @sffh varchar(2) --是否复核
declare @ddh varchar(50)
--定义游标
declare insert_housefwdanb_ddb_cursor scroll cursor
for
select fjbh,mc,ldid,sffh from inserted order by fjbh
open insert_housefwdanb_ddb_cursor--打开游标
fetch next from insert_housefwdanb_ddb_cursor into @fjbh,@mc,@ldid,@sffh
while @@FETCH_STATUS=0
begin
if @sffh=1--已复核
begin
set @ddzt='1'--地点正常
if LEN(@fjbh)>=6--如果房间编号大于6，自动截取
begin
select @fjbh=RIGHT(@fjbh,6)
select @ddh=@fjbh--将截取的房间编号复制给地点号
end
else--如果房间编号小于6位，补齐
begin
select @fjbh=RIGHT('000000'+CAST(@fjbh as varchar),6)--补齐
select @ddh=@fjbh--将补齐的房间编号复制给地点号
end
insert into Ddb(ddbh,mc,sjdd,ddzt,ddh)values(@fjbh,@mc,'000001',@ddzt,@ddh)
end
fetch next from insert_housefwdanb_ddb_cursor into @fjbh,@mc,@ldid,@sffh
end
close insert_housefwdanb_ddb_cursor --关闭游标
end

转载于:https://www.cnblogs.com/guojie-guojie/p/8416040.html