sql server 游标 写给自己

时间：2022-01-27 04:08:28
Declare @Id varchar(20) --定义两个局部变量 @id @name 全局变量是两个@@name
Declare @Name varchar(20) 
Declare Cur Cursor For --定义一个游标
select id,name from temp1 --查询语句 返回id和name
Open Cur --打开游标
Fetch next From Cur Into @Id,@Name --将游标返回的值赋值给局部变量（提示：右边返回值是一条一条的返回的  next代表的是每次返回下一条）
While @@fetch_status=0 --定义一个while循环
Begin 
Update temp Set [c3]=@Name where [id] like @Id+'%' 
Fetch Next From Cur Into @Id,@Name 
End 
Close Cur 
Deallocate Cur

 

create proc RF_AutoCreate_ProgramName

@mineName varchar(20),           --矿井名称    
@excludeSystem varchar(500) --排除生成系统
as 
declare @starStop varchar(20)  -- 启停表达式
declare @warning varchar(20)   -- 报警表达式
declare @warning_2 varchar(20)   -- 报警表达式
begin
begin transaction    --启动事务
delete from RF_ReportPointInfo_1# where CreateUser ='自动创建';
        --为表达式赋值
        set @starStop = 'BR';
        set @warning = 'BA';
        set @warning_2 = 'BF';

                --定义遍历出来的表的数据
        declare @rfName varchar(300);            --报表类型
        declare @ProgramName varchar(300);        --方案名称
        declare @sysCode varchar(300);            --系统
        declare @chsysCode varchar(300);        --子系统
        declare @Name varchar(300);                --点名
        declare @description varchar(300);        --点描述
        declare @dataType varchar(300);            --点类型

        if(isnull(@excludeSystem,'')<>'')  --如果存在需要排除生成的系统
            begin       
             Declare cur_printinfo Cursor For --创建游标select SystemName from Sys_SystemTable where ID = sysCode
                select SystemName =(select SystemName from Sys_SystemTable where ID =(select SysClassify from Sys_ChildSystemTable where ID = sysCode)),ChSystemName =(select SysName from Sys_ChildSystemTable where ID = sysCode),
                uc.OpcChannelName+'.'+uc.OpcDevName+'.'+p.Name as Name,[description],dataType
                from PT_UsedChildSys as uc join Sys_PointInfo as p on uc.MineName=p.MineName and uc.sheetName=p.sheetName where p.MineName = @mineName and p.OriginalCode<>''
                and not EXISTS(
                SELECT distinct syscode FROM Sys_PointInfo
                 WHERE CHARINDEX(cast(syscode as varchar),LTRIM(@excludeSystem))>0 and p.sysCode=sysCode
                )
                order by sysCode desc
            end 
        else
          begin
             --定义游标
            Declare cur_printinfo Cursor For 
            select SystemName =(select SystemName from Sys_SystemTable where ID =(select SysClassify from Sys_ChildSystemTable where ID = sysCode)),ChSystemName =(select SysName from Sys_ChildSystemTable where ID = sysCode),
            uc.OpcChannelName+'.'+uc.OpcDevName+'.'+p.Name as Name,[description],dataType
            from PT_UsedChildSys as uc join Sys_PointInfo as p on uc.MineName=p.MineName and uc.sheetName=p.sheetName where p.MineName = @mineName   and p.OriginalCode<>'' order by p.sysCode desc    
         end
                 
         Open cur_printinfo
        --移动或提取列值
        Fetch From cur_printinfo into @sysCode,@chsysCode,@Name,@description,@dataType
        --利用循环处理游标中的列值
        While @@Fetch_Status=0
            Begin        
                if(charindex(@starStop,@dataType)>0) --如果点类型是BR  则是布尔型点
                begin
                    --启停数据
                    insert into RF_ReportPointInfo_1#(rfName,ProgramName,sysName,chsysName,ptsBewrite,pts,CreateUser,CreatDate) values('设备启停记录',isnull(@chsysCode,'')+'_设备启停记录',@sysCode,@chsysCode,@description,@Name,'自动创建',GETDATE());
                    insert into RF_ReportPointInfo_1#(rfName,ProgramName,sysName,chsysName,ptsBewrite,pts,CreateUser,CreatDate) values('设备启停统计',isnull(@chsysCode,'')+'_设备启停统计',@sysCode,@chsysCode,@description,@Name,'自动创建',GETDATE());
                end
                else if(charindex(@warning,@dataType)>0)
                begin
                    --报警数据
                    insert into RF_ReportPointInfo_1#(rfName,ProgramName,sysName,chsysName,ptsBewrite,pts,CreateUser,CreatDate) values('报警记录',isnull(@chsysCode,'')+'_报警记录',@sysCode,@chsysCode,@description,@Name,'自动创建',GETDATE());
                    insert into RF_ReportPointInfo_1#(rfName,ProgramName,sysName,chsysName,ptsBewrite,pts,CreateUser,CreatDate) values('报警统计',isnull(@chsysCode,'')+'_报警统计',@sysCode,@chsysCode,@description,@Name,'自动创建',GETDATE());
                end
                else if(charindex(@warning_2,@dataType)>0)
                begin
                    --报警数据
                    insert into RF_ReportPointInfo_1#(rfName,ProgramName,sysName,chsysName,ptsBewrite,pts,CreateUser,CreatDate) values('故障记录',isnull(@chsysCode,'')+'_故障记录',@sysCode,@chsysCode,@description,@Name,'自动创建',GETDATE());
                    insert into RF_ReportPointInfo_1#(rfName,ProgramName,sysName,chsysName,ptsBewrite,pts,CreateUser,CreatDate) values('故障统计',isnull(@chsysCode,'')+'_故障统计',@sysCode,@chsysCode,@description,@Name,'自动创建',GETDATE());
                end
                
                --将游标下移
                Fetch From cur_printinfo into @sysCode,@chsysCode,@Name,@description,@dataType
            End
        --关闭/释放游标
        Close cur_printinfo
        Deallocate cur_printinfo 

    if @@error>0 
    begin
        rollback transaction    --出错了进行回滚事务
        raiserror('出错了',16,1)
        return
    end
    else
    begin
    commit transaction    --正常运行提交事务
    end
end
go

exec RF_AutoCreate_ProgramName '柠条塔煤矿',''

 

标签：

相关文章