主页 / dba / 问题 / 317143
Accepted
Subin Benny
Asked: 2022-09-21 05:29:46 +0800 CST

如何使用临时表或 while 循环代替游标

  • 772

我有一个 sp 可以从孙子表中删除数据。

但我需要更改光标并使用临时表或 while 循环而不是光标。我试过但没有工作，有人可以帮忙。

Sp在下面添加

CREATE Procedure spDeleteRows
/* 
Recursive row delete procedure. 

It deletes all rows in the table specified that conform to the criteria selected, 
while also deleting any child/grandchild records and so on.  This is designed to do the 
same sort of thing as Access's cascade delete function. It first reads the sysforeignkeys 
table to find any child tables, then deletes the soon-to-be orphan records from them using 
recursive calls to this procedure. Once all child records are gone, the rows are deleted 
from the selected table.   It is designed at this time to be run at the command line. It could 
also be used in code, but the printed output will not be available.
*/
    (
    @cTableName varchar(50), / name of the table where rows are to be deleted /
    @cCriteria nvarchar(1000), / criteria used to delete the rows required /
    @iRowsAffected int OUTPUT / number of records affected by the delete /
    )
As
set nocount on
declare     @cTab varchar(255), / name of the child table /
    @cCol varchar(255), / name of the linking field on the child table /
    @cRefTab varchar(255), / name of the parent table /
    @cRefCol varchar(255), / name of the linking field in the parent table /
    @cFKName varchar(255), / name of the foreign key /
    @cSQL nvarchar(1000), / query string passed to the sp_ExecuteSQL procedure /
    @cChildCriteria nvarchar(1000), /* criteria to be used to delete 
                                           records from the child table */
    @iChildRows int / number of rows deleted from the child table /

/ declare the cursor containing the foreign key constraint information /
DECLARE cFKey CURSOR LOCAL FOR 
SELECT SO1.name AS Tab, 
       SC1.name AS Col, 
       SO2.name AS RefTab, 
       SC2.name AS RefCol, 
       FO.name AS FKName
FROM dbo.sysforeignkeys FK  
INNER JOIN dbo.syscolumns SC1 ON FK.fkeyid = SC1.id 
                              AND FK.fkey = SC1.colid 
INNER JOIN dbo.syscolumns SC2 ON FK.rkeyid = SC2.id 
                              AND FK.rkey = SC2.colid 
INNER JOIN dbo.sysobjects SO1 ON FK.fkeyid = SO1.id 
INNER JOIN dbo.sysobjects SO2 ON FK.rkeyid = SO2.id 
INNER JOIN dbo.sysobjects FO ON FK.constid = FO.id
WHERE SO2.Name = @cTableName

OPEN cFKey
FETCH NEXT FROM cFKey INTO @cTab, @cCol, @cRefTab, @cRefCol, @cFKName
WHILE @@FETCH_STATUS = 0
     BEGIN
    /* build the criteria to delete rows from the child table. As it uses the 
           criteria passed to this procedure, it gets progressively larger with 
           recursive calls */
    SET @cChildCriteria = @cCol + ' in (SELECT [' + @cRefCol + '] FROM [' + 
                              @cRefTab +'] WHERE ' + @cCriteria + ')'
    print 'Deleting records from table ' + @cTab
    / call this procedure to delete the child rows /
    EXEC spDeleteRows @cTab, @cChildCriteria, @iChildRows OUTPUT
    FETCH NEXT FROM cFKey INTO @cTab, @cCol, @cRefTab, @cRefCol, @cFKName
     END
Close cFKey
DeAllocate cFKey
/ finally delete the rows from this table and display the rows affected  /
SET @cSQL = 'DELETE FROM [' + @cTableName + '] WHERE ' + @cCriteria
print @cSQL
EXEC sp_ExecuteSQL @cSQL
print 'Deleted ' + CONVERT(varchar, @@ROWCOUNT) + ' records from table ' + @cTableName
sql-server stored-procedures

1 个回答

  1. Best Answer

    我必须说，我通常建议您使用级联外键，或者手动编写代码。

    尽管如此，您不需要游标或WHILE循环。您可以使用递归 CTE 构建查询，它获取每个外键关系并构造它的动态 CTE，然后按顺序从每个外键中删除。

    请注意，我的过程没有考虑自引用外键（为此您需要动态递归 CTE），也没有处理多个级联路径。我把它留给读者作为练习。

    您需要连接列的 TVF，因为您不能在递归 CTE 中进行聚合。

    CREATE OR ALTER FUNCTION dbo.GetJoinCols (@parent_object_id int, @child_object_id int, @fk_object_id int)
RETURNS TABLE
AS RETURN
SELECT
  JoinCols = 
    STRING_AGG(
      CAST(
        'c.' + QUOTENAME(cChild.name) + ' = p.' + QUOTENAME(cParent.name)
        AS nvarchar(max)
      ),
      ' AND '
    )
FROM sys.foreign_key_columns fkc
JOIN sys.columns cParent ON cParent.object_id = @parent_object_id AND cParent.column_id = fkc.referenced_column_id
JOIN sys.columns cChild ON cChild.object_id = @child_object_id AND cChild.column_id = fkc.parent_column_id
WHERE fkc.constraint_object_id = @fk_object_id;

    最后的程序是

    CREATE OR ALTER PROC DeleteWithFK
    @tableName sysname, -- name of the table where rows are to be deleted
    @Criteria nvarchar(1000), -- criteria used to delete the rows required
    @RowsAffected int = NULL OUTPUT, -- number of records affected by the delete /
    @schemaName sysname = 'dbo'
As
SET NOCOUNT ON;

DECLARE @sql nvarchar(max);

WITH cte AS (
    SELECT
      Level = 0,
      t.object_id,
      TableToDelete = QUOTENAME('cte' + t.name),  --must be the CTE name from below
      TablesAsCte = CONCAT(
        QUOTENAME('cte' + t.name),
        ' AS (
  SELECT *
  FROM ',
        QUOTENAME(s.name),
        '.',
        QUOTENAME(t.name),
        '
  WHERE ',
        CAST(@Criteria AS nvarchar(max)),
        '
)'
      )
    FROM sys.tables t
    JOIN sys.schemas s ON s.schema_id = t.schema_id
    WHERE s.name = @schemaName
      AND t.name = @tableName

    UNION ALL
  
    SELECT
      cte.Level + 1,
      tChild.object_id,
      QUOTENAME('cte' + tChild.name),  --must be the CTE name from below
      CONCAT(
        cte.TablesAsCte,
        ',
',
        QUOTENAME('cte' + tChild.name),
        ' AS (
  SELECT c.*
  FROM ',
        QUOTENAME(sChild.name),
        '.',
        QUOTENAME(tChild.name),
        ' c
  WHERE EXISTS (SELECT 1
      FROM ',
        cte.TableToDelete,
        ' p WHERE ',
        cols.JoinCols,
        '
    )
)'
      )
    FROM cte
    JOIN sys.foreign_keys fk ON fk.referenced_object_id = cte.object_id
    JOIN sys.tables tChild ON tChild.object_id = fk.parent_object_id
    JOIN sys.schemas sChild ON sChild.schema_id = tChild.schema_id
    CROSS APPLY dbo.GetJoinCols(cte.object_id, tChild.object_id, fk.object_id) cols
)

SELECT
  @sql = STRING_AGG(
    CONCAT(
      'WITH ',
      cte.TablesAsCte,
      '
DELETE ',
      cte.TableToDelete
    ),
    ';

'
  ) WITHIN GROUP (ORDER BY cte.Level DESC)
FROM cte;

SET @sql += ';

SET @RowsAffected = @@ROWCOUNT;
';

PRINT @sql;  -- your friend

EXEC sp_executesql @sql,
    N'@RowsAffected int OUTPUT',
    @RowsAffected = @RowsAffected OUTPUT;

    db<>小提琴

    例如，生成的 SQL 可能如下所示

    WITH [cteAGENTS] AS (
  SELECT *
  FROM [dbo].[AGENTS]
  WHERE SomeColum = 'Somevalue'
),
[cteCUSTOMER] AS (
  SELECT c.*
  FROM [dbo].[CUSTOMER] c
  WHERE EXISTS (SELECT 1
      FROM [cteAGENTS] p WHERE c.[AGENT_CODE] = p.[CODE]
    )
),
[cteORDERS] AS (
  SELECT c.*
  FROM [dbo].[ORDERS] c
  WHERE EXISTS (SELECT 1
      FROM [cteCUSTOMER] p WHERE c.[CUST_CODE] = p.[CODE]
    )
)
DELETE [cteORDERS];

WITH [cteAGENTS] AS (
  SELECT *
  FROM [dbo].[AGENTS]
  WHERE 1=1
),
[cteCUSTOMER] AS (
  SELECT c.*
  FROM [dbo].[CUSTOMER] c
  WHERE EXISTS (SELECT 1
      FROM [cteAGENTS] p WHERE c.[AGENT_CODE] = p.[CODE]
    )
)
DELETE [cteCUSTOMER];

WITH [cteAGENTS] AS (
  SELECT *
  FROM [dbo].[AGENTS]
  WHERE 1=1
)
DELETE [cteAGENTS]

SET @RowsAffected = @@ROWCOUNT;

    如果STRING_AGG您的 SQL Server 版本不支持，您需要使用FOR XML

    CREATE OR ALTER FUNCTION dbo.GetJoinCols (@parent_object_id int, @child_object_id int, @fk_object_id int)
RETURNS TABLE
AS RETURN
SELECT
  JoinCols = STUFF((
    SELECT
      ' AND c.' + QUOTENAME(cChild.name) + ' = p.' + QUOTENAME(cParent.name)
    FROM sys.foreign_key_columns fkc
    JOIN sys.columns cParent ON cParent.object_id = @parent_object_id AND cParent.column_id = fkc.referenced_column_id
    JOIN sys.columns cChild ON cChild.object_id = @child_object_id AND cChild.column_id = fkc.parent_column_id
    WHERE fkc.constraint_object_id = @fk_object_id
    FOR XML PATH(''), TYPE
  ).value('text()[1]', 'nvarchar(max)'),
  1,
  LEN(' AND '),
  ''
);

    和

    WITH cte AS (
.............
-- same as above
)

SELECT @sql = (
    SELECT
      CONCAT(
        'WITH ',
        cte.TablesAsCte,
        '
DELETE ',
        cte.TableToDelete,
    ';

'
      )
    FROM cte
    ORDER BY cte.Level DESC
    FOR XML PATH(''), TYPE
).value('text()[1]', 'nvarchar(max)');
    • 1

相关问题