时间戳

首页 > 解决方案 > 游标用于更新另一个表，使用 SQL Server、T-SQL？

sql-server - 游标用于更新另一个表，使用 SQL Server、T-SQL？

问题描述

我有一张桌子，我需要对其进行初始更新，然后每晚更新一次。我有一个文件名，我需要从中获取值才能更新两列。我来自 Oracle PL/SQL 背景，在 SQL Server 和 T-SQL 方面经验很少。我很擅长使用 Oracle 游标来遍历表，并且理解它们在 SQL Server 中做事是有争议的。

我的最终目标是为用户提供有关每种文件类型和水权类型已处理多少的信息。

文件名如下所示：

UPDATE_TO_FILE_STATEMENT_OF_CLAIM_41L_44799_00_2780.pdf
PRELIMINARY_DECREE_STATEMENT_OF_CLAIM_41G_40643_00_1134.pdf
FILE_STOCKWATER_PERMIT_40N_30116114_10437.pdf
UPDATE_TO_FILE_GROUND_WATER_CERTIFICATE_40E_30025354_10399.pdf

从这个文件名中，我需要提取文件类型和 Water Right 类型。有 4 种文件类型和 20 种水权类型。我可以使用Case，但如果他们添加文件类型或 Water Right 类型，我必须更改代码。解析名称也不是一件容易的事。我已经创建了一个表格，其中包含我将使用的代码、文件名的外观、描述以及它是文件类型还是水权类型。

FILE_CD FILE_NAME                       DESCR                       FILE_TYPE
----------------------------------------------------------------------------
62GW    62-73_GROUND_WATER_RECORD       62-73 GROUND WATER RECORD       WT
CDWR    CONSERVATION_DISTRICT_RECORD    CONSERVATION DISTRICT RECORD    WT
UPDT    UPDATE_TO_FILE_                 UPDATE TO FILE                  FT

我的计划是遍历这个表并为初始运行运行更新。我还将遍历此表，并在触发器中为每晚添加的每条记录进行一次更新。

这是我的代码：

BEGIN TRANSACTION

DECLARE @F_type NVARCHAR(2)
DECLARE @F_name NVARCHAR(30)
DECLARE @F_CD NVARCHAR(4)

-- local means the cursor name is private to this code
-- fast_forward enables some speed optimizations
DECLARE c_TUpdt CURSOR LOCAL FAST_FORWARD FOR
   SELECT 
       FILE_CD, FILE_NAME, FILE_TYPE
   FROM 
       PRDECMSTATS.dbo.FileType 
   WHERE 
       FILE_CD NOT IN ('OTHT', 'OTHF')

OPEN c_TUpdt

FETCH NEXT FROM c_TUpdt INTO @F_CD, @F_name, @F_type

WHILE @@fetch_status = 0
BEGIN
    -- CHECK FILE TYPE
    IF @F_type = 'FT' THEN
       UPDATE PRDECMSTATS.dbo.FileDetails 
       SET File_Type = @F_CD
       WHERE File_Type IS NULL
         AND FileNAME LIKE @F_name || '%'
    END IF

    IF @F_type = 'WT' THEN
       UPDATE PRDECMSTATS.dbo.FileDetails 
       SET WR_Type = @F_CD
       WHERE WR_Type IS NULL
         AND FileNAME LIKE '%' || @F_name || '%'
    END IF

    FETCH NEXT FROM c_TUpdt INTO @F_CD, @F_name, @F_type
END

CLOSE c_TUpdt
DEALLOCATE c_TUpdt

/* need to then traverse for other types, these will need to be corrected by hand at some point */
UPDATE PRDECMSTATS.dbo.FileDetails 
SET File_Type = 'OTHF'
WHERE File_Type IS NULL

UPDATE PRDECMSTATS.dbo.FileDetails 
SET WR_Type = 'OTHT'
WHERE WR_Type IS NULL

COMMIT TRANSACTION

鉴于文件名中的数据，我希望这会更新 2 列。

这行得通吗？有一个更好的方法吗？

标签： sql-servercursorsmss

解决方案

光标可以工作，但几乎从来都不是最有效的答案。我相信这是您光标的完全替代品

UPDATE fd
SET File_Type = ft.FILE_CD
FROM PRDECMSTATS.dbo.FileDetails fd
INNER JOIN PRDECMSTATS.dbo.FileType ft ON ft.FILE_CD NOT IN ('OTHT', 'OTHF')
    AND ft.FILE_TYPE='FT'
    AND fd.File_Type IS NULL
    AND fd.FileNAME LIKE ft.FILE_NAME + '%'

UPDATE fd
SET WR_Type = ft.FILE_CD
FROM PRDECMSTATS.dbo.FileDetails fd
INNER JOIN PRDECMSTATS.dbo.FileType ft ON ft.FILE_CD NOT IN ('OTHT', 'OTHF')
    AND ft.FILE_TYPE='WT'
    AND fd.WR_Type IS NULL
    AND fd.FileNAME LIKE  '%' + ft.FILE_NAME + '%'

它可以被浓缩成一个单一的陈述，但我认为它会使它变得不那么清晰。顺便说一句，“+”运算符在 SQL Server 中连接。此外，第一个 UPDATE 在字符串前面没有通配符，如第二个；不确定这是否是故意的。

超出有问题的光标，这可以替换所有内容

UPDATE fd
SET File_Type = ISNULL(ft.FILE_CD,'OTHF')
FROM PRDECMSTATS.dbo.FileDetails fd
LEFT JOIN PRDECMSTATS.dbo.FileType ft ON ft.FILE_CD NOT IN ('OTHT', 'OTHF')
    AND ft.FILE_TYPE='FT'
    AND fd.FileNAME LIKE ft.FILE_NAME + '%'
WHERE fd.File_Type IS NULL

UPDATE fd
SET WR_Type = ISNULL(ft.FILE_CD,'OTHT')
FROM PRDECMSTATS.dbo.FileDetails fd
LEFT JOIN PRDECMSTATS.dbo.FileType ft ON ft.FILE_CD NOT IN ('OTHT', 'OTHF')
    AND ft.FILE_TYPE='WT'
    AND fd.FileNAME LIKE  '%' + ft.FILE_NAME + '%'
WHERE fd.WR_Type IS NULL

推荐阅读