Remove function

最新推荐文章于 2021-06-17 21:04:57 发布

转载最新推荐文章于 2021-06-17 21:04:57 发布 · 143 阅读

本文介绍了一种使用SQL语句创建临时表、插入数据及通过游标遍历记录进行数据处理的方法。其中包括表结构的设计、数据的初始化以及利用游标进行逐条记录的读取与处理等关键步骤。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..#table') and type='U')
DROP TABLE #table

CREATE TABLE #table
    (
      [RECNUM] [decimal](28, 0) IDENTITY(1, 1)     NOT NULL ,
      ModuleCode NVARCHAR(30) COLLATE SQL_Latin1_General_CP1_CI_AS       NULL ,
      FunctionCode NVARCHAR(30) COLLATE SQL_Latin1_General_CP1_CI_AS       NULL ,
      FuncDescription NVARCHAR(30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      Process_Code NVARCHAR(30) COLLATE SQL_Latin1_General_CP1_CI_AS      NULL,
      Menu_Type NVARCHAR(30) COLLATE SQL_Latin1_General_CP1_CI_AS   NULL
    )

INSERT INTO #table(ModuleCode, FunctionCode , FuncDescription,Process_Code,Menu_Type)
        SELECT 'PRSF', 'PRSFRT' , 'Matrial Reservation Transfer','M6_ENT','ENTRY'
        UNION ALL
        SELECT 'PRSF', 'PRSFRC' ,'Routing Process Completion','M6_ENT','ENTRY'
        UNION ALL
        SELECT 'PRPE', 'PRPEMJ' ,'Mass ECN','M4_ENT','ENTRY'
        UNION ALL
        SELECT 'PRPM', 'PRPMMJ' ,'Mass ECN','M4_ENT','ENTRY'
        UNION ALL
        SELECT 'SAMF', 'SAMFRP' ,'Routing Process','MAST01','MAST'
        UNION ALL
        SELECT 'SAMF', 'SAMFRP' ,'Bom Routing Process Settings','MAST01','MAST'
        UNION ALL
        SELECT 'SAMF', 'SAMFCL' ,'Collection Master','MAST01','MAST'
        UNION ALL
        SELECT 'SAMF', 'SAMFSY' ,'Style Master','MAST01','MAST'
        UNION ALL
        SELECT 'SAMF','SAMFCO' ,'Color Master','MAST01','MAST'
        UNION ALL
        SELECT 'PRPM', 'PRMJU' , 'Job BOM Update','M4_ENT','ENTRY'


Declare @RECNUM int
Declare @ModuleCode Nvarchar(30)
Declare @FunctionCode Nvarchar(30)
Declare @Process_Code Nvarchar(30)
Declare @Menu_Type Nvarchar(30)

Declare Cur Cursor For Select [RECNUM] From #table
Open Cur
Fetch next From Cur Into @RECNUM
While @@fetch_status=0
BEGIN
     SELECT @ModuleCode=ModuleCode,@FunctionCode=FunctionCode,@Process_Code=Process_Code,@Menu_Type=Menu_Type
         FROM #table WHERE RECNUM=@RECNUM

     --[USER_GROUP], [PROCESS_CODE], [MENU_TYPE], [MENU_CODE]
     DELETE ADMNUD WHERE USER_GROUP='SYSADM' AND PROCESS_CODE=@Process_Code AND MENU_TYPE=@Menu_Type AND FUNCTION_CODE=@FunctionCode

     --([USER_GROUP], [MODULE_CODE], [FUNCTION_NO])
     DELETE ADAUTD WHERE USER_GROUP='SYSADM' AND MODULE_CODE=@ModuleCode AND FUNCTION_CODE=@FunctionCode

     --[MODULE_CODE], [FUNCTION_NO]
     DELETE ADFUNC WHERE MODULE_CODE=@ModuleCode AND FUNCTION_CODE=@FunctionCode


     Fetch next From Cur Into @RECNUM
End
Close Cur
Deallocate Cur