一个用于在 Oracle 中生成级联删除 SQL 脚本的小型实用工具

• 下载解决方案 - 24.18 KB

引言

本文分享一个用于生成 Oracle 数据库中级联删除 SQL 脚本的小工具，并介绍其工作原理。

背景

最近，我为客户进行数据修复，需要删除数十个表中的数据。这些表都有引用表，而这些引用表又有引用表，以此类推。要删除一个表，我首先必须删除它的引用表，要删除它的引用表，我首先必须删除引用表的引用表，依此类推。在一种情况下，有六层深度，并且需要首先删除 220 个表，顺序必须严格。我创建了这个实用工具来生成级联删除脚本，以避免我进行繁琐且容易出错的工作。我将其发布在 CodeProject 上，以便其他人可以使用，因为我从该网站获得了很大的帮助。该实用工具目前支持 Oracle 数据库。但它可以扩展以支持 SQLServer。提供了源代码，以便其他人可以扩展它。

Using the Code

该实用工具的代码结构由三个部分组成。底部部分是获取数据库字典信息，例如主键和引用表。中间部分是实现生成脚本的核心逻辑。顶部部分是 UI，一个 Windows 应用程序界面，允许用户生成脚本。该实用工具使用 VB.NET 和 Visual Studio 2010 实现。

让我们从底部部分开始介绍。在底部部分，引入了一个接口，以便可以扩展它以支持不同的数据库。

Public Interface IDictionaryInfo

    ' Get Primary Key of a table
    Function GetPKColName(ByVal strTableName As String) As String

    ' Get foreign key reference table and the foreign key column in that table
    '   A list of KeyValuePair rather than a dictionary is used as
    '   return type since one reference table may have 1+ reference column.
    Function GetFKReferences(ByVal strTableName As String) _
    	As List(Of KeyValuePair(Of String, String))

End Interface

这是支持 Oracle 数据库的接口实现。

' the implementation of the IDictionaryInfo for oracle
Public Class OracleDictionaryInfo
    Implements IDictionaryInfo

    ' get the primary key for a table
    Public Function GetPKName(ByVal strTableName As String) _
    	As String Implements IDictionaryInfo.GetPKColName
        strTableName = strTableName.ToUpper		'Oracle tables are saved
        				' in uppercase in Oracle dictionary.

        'SQL to retrieve oracle dictionary to get primary key
        Dim strSQl As String = String.Format_
        	("SELECT cols.column_name FROM user_constraints cons, _
        	user_cons_columns cols WHERE cols.table_name = '{0}' _
        	AND cons.constraint_type = 'P' AND cons.constraint_name = _
        	cols.constraint_name", strTableName)

        Dim dt = GetDataTable(strSQl)

        If dt.Rows.Count > 0 Then
            Return dt.Rows(0)(0)
        Else
            Return String.Empty
        End If
    End Function


    ' get the foreign key tables and its reference columns
    Public Function GetFKReferences(ByVal strTableName As String) _
    As System.Collections.Generic.List(Of _
    System.Collections.Generic.KeyValuePair(Of String, String)) _
    Implements IDictionaryInfo.GetFKReferences
        strTableName = strTableName.ToUpper

        Dim strSQl As String = String.Format_
        ("select C.TABLE_NAME, C.COLUMN_NAME from user_cons_columns _
        c where c.CONSTRAINT_NAME IN ( Select constraint_name from_
        (user_constraints) where constraint_type='R'  and _
        r_constraint_name in (select constraint_name from user_constraints _
        where constraint_type in ('P','U') and table_name='{0}'))", _
        strTableName)

        Dim dt = GetDataTable(strSQl)

        Dim tnq = From dr In dt.Rows Select New KeyValuePair_
        (Of String, String)(CType(dr(0), String), CType(dr(1), String))
        Return tnq.ToList()
    End Function

End Class

中间部分收集主键和引用的数据库字典信息。它调用一个内部递归函数以级联方式生成删除脚本。

' Core class to generate script for the table
Public Class GenScriptCore

    Private iDic As IDictionaryInfo

    Private Sub New()   ' no instance is allowed
    End Sub

    ' Main function to generate script.  Prepare database type, _
    connection string and table name to call the function
    Public Shared Function GenScript(ByVal enumDB As DB, _
    ByVal strConnection As String, ByVal strTableName As String) As String
        Dim oc As New GenScriptCore

        Select Case [enumDB]
            Case DB.Oracle
                oc.iDic = New OracleDictionaryInfo(strConnection)
            Case DB.SQlServer
                Throw New Exception("SQL server is not supported yet. _
                But you can implement it by implementing the interface of _
                IDictionaryInfo for SQL Server")
        End Select

        Dim strPKCol = oc.iDic.GetPKColName(strTableName)

        If String.IsNullOrEmpty(strPKCol) Then
            Throw New Exception(String.Format("table '{0}' _
            does not exist or has no primary key / unique key", strTableName))
        End If

        Return oc.gs(strTableName, strPKCol) ' call the main method
    End Function

	' Core function to generate script though recursive calling
    Private Function gs(ByVal strTableName As String, _
    ByVal strFilterColumn As String, _
    Optional ByVal strInFilter As String = Nothing) As String

        Dim sb As New System.Text.StringBuilder
        Dim strPKCol = iDic.GetPKColName(strTableName)

        If String.IsNullOrEmpty(strPKCol) Then
            Throw New Exception(String.Format("table '{0}' _
            has no primary key / unique key", strTableName))
        End If

        ' define where clause for the reference tables
        Dim strNextLevelWhereClause As String
        If Not String.IsNullOrEmpty(strInFilter) Then
            strNextLevelWhereClause = String.Format("{0} _
            in ({1})", strFilterColumn, strInFilter)
        Else
            strNextLevelWhereClause = String.Format("{0} = _
            p_{0}", strFilterColumn)
        End If

        Dim strNextLevelFilter = String.Format("Select {0} from _
        {1} where {2}", strPKCol, strTableName, strNextLevelWhereClause)

        ' find the foreign key reference tables and delete them first
        Dim lstFKTableFKCol = iDic.GetFKReferences(strTableName)
        For Each kv In lstFKTableFKCol
            sb.AppendLine(gs(kv.Key, kv.Value, strNextLevelFilter))
        Next

        '  delete itself
        Dim strDeleteWhereClause As String
        If Not String.IsNullOrEmpty(strInFilter) Then
            strDeleteWhereClause = String.Format_
            ("{0} in ({1})", strFilterColumn, strInFilter)
        Else
            strDeleteWhereClause = String.Format_
            ("{0} = p_{0}", strFilterColumn)
        End If

        sb.AppendFormat("delete {0} where {1};", _
        strTableName, strDeleteWhereClause)
        sb.AppendLine()

        Return sb.ToString
    End Function

End Class

UI 收集用户输入，包括数据库类型、连接字符串和表名。它验证输入并调用中间层以生成脚本，该脚本在窗口内显示，并同时复制到剪贴板。如果应用程序抛出异常，UI 将以红色显示错误消息。

关注点

这个小工具对我帮助很大。我希望这个小工具也能帮助其他人。

历史

• 初始版本：2011 年 2 月 17 日

作者 Ronald Cao 是 TP Systems Co. 的高级 .NET 开发人员。