代码生成: 利用SQL生成SQL代码

曾经终败给现在 2022-08-09 06:59 582阅读 0赞

**作者** [ **Haidong Ji**][_Haidong Ji]  
  
\---  
作为一位DBA或者程序开发者，随着越来越富于经验，无疑会收集一些使工作和生活更为有趣的技巧和窍门。在SQL编程方面，我就有一个窍门。

你可能认为SQL仅仅是查询和编辑数据用的。然而，如果你很好的理解了SQL服务器的数据库图表和各种数据库系统表，通过字符串操作技术，利用SQL就可以产生出其他的SQL语句，存储过程，程序命令，如BCP，和另外的有用的代码。

在这篇文章里，我将向你显示一些简单的实例帮助你入门。并且提供一些更好的代码生成技术的资源链接。

**利用SQL生成SQL**

作为 DBA，偶尔会有人问起两个或者更多的数据库中的数据是否是一样的。对于一般的想法，,你可能想获得全部的数据表和它们行数的统计。你可能会写一些SQL语句，

如： "SELECT COUNT(\*) FROM ORDERS"， "SELECT COUNT(\*) FROM CUSTOMERS"， 等等，一直到你处理完全部的表。这个方法可行，但是会消耗大量的时间，并且有可能有错误，尤其是在数据库当中有很多数据表的时候。但是，如果你比较了解数据库的系统表，就可以使用SQL来产生这些语句，而不是用键盘敲出来，看看下面的语句：

Use Northwind
    SELECT 'SELECT ' + CHAR(39) + NAME + CHAR(39) + ' AS TABLENAME, ' +
    'COUNT(*) FROM [' + NAME + ']'
    FROM SYSOBJECTS WHERE XTYPE = 'U' AND NAME NOT IN ('DTPROPERTIES')
    ORDER BY NAME

全部的SQL服务器数据库对象都存储在 sysobjects 表中。还有某些实元数据，如syscolumns, syscomments, 等等。 sysobjects 可以提供全部的用户数据表名称。 如果你只是想得到对象的类型，这就足够了。CHAR(39) 表示一个单引号，这样你可以知道这个表的行数量。在Query Analyzer中运行这个语句，记着输出结果的文本，这样你就得到了一个漂亮的能够得到一个数据库全部表的行数量的SQL语句了。

这种SQL代码的生成方式，发挥想象，是没有局限的。例如，稍稍改变上面的语句，就可以将它变成对全部存储过程授权给用户TESTUSER的语句。如下：

SELECT 'GRANT EXECUTE ON [' + NAME + '] TO TESTUSER'
    FROM SYSOBJECTS WHERE XTYPE = 'P'

**利用SQL产生和执行BCP 命令**

BCP是一个非常好的工具，用来处理数据库的输入输出。它是轻量级的，迅速的和有效率的。它可以在不同的服务器不同的数据库之间灵活的输入输出数据。

下面是备份数据库 Northwind全部表的例子。需要先有一个C:\\DataDump 的文件夹。也可以改变驱动器和目录。当运行这个SQL批处理的时候，全部数据都备份的选定目录的文本文件当中。文件名称有表名构成。请参考BCP信息相关书籍。

set nocount on
    
    /* Initialize variables here */
    declare @TableNames table (TableTempID
    smallint IDENTITY(1,1) PRIMARY KEY CLUSTERED, TableName varchar(50))
    declare @TableName varchar(50)
    declare @BackupFileName char(50)
    declare @BackupFolderFile varchar(150)
    declare @Counter smallint
    declare @MaxTableCount smallint
    declare @BackupFolder varchar(100)
    declare @BCPOutCommand varchar(500)
    
    /* Set BackupFolder name here */
    set @BackupFolder = 'c:\DataDump\'
    
    /* Get the list of tables that we want to backup */
    insert into @TableNames (TableName) select name from
    Northwind.dbo.sysobjects where xtype = 'U'
    and name not in ('dtproperties')
    
    select @MaxTableCount = max(TableTempID) from @TableNames
    set @Counter = 1
    
    /* Loop through all each table individually, generate bcp
    commands and run bcp commands to export data */
    while @Counter <= @MaxTableCount
    
    Begin
    
    /* Create backup file name */
    select @TableName = ltrim(rtrim(TableName)) from
    @TableNames where TableTempID = @Counter
    
    select @BackupFileName = ltrim(rtrim(@TableName)) + '.txt'
    
    /* Combine backup folder name and file name */
    select @BackupFolderFile = @BackupFolder + @BackupFileName
    
    /* Create BCP command */
    select @BCPOutCommand = 'bcp ' + '"Northwind.dbo.' + @TableName +
    '" out "' + ltrim(rtrim(@BackupFolderFile)) +
    '" -c -q -S' + @@Servername + ' -T'
    
    print @BCPOutCommand
    exec master..xp_cmdshell @BCPOutCommand
    set @Counter = @Counter + 1
    end稍微对上面的代码进行修改，可以得到输入数据的BCP命令。再在 Query Analyzer运行，确定输出文本结果。拷贝并保存为DOS批处理文件。这样你可以完成用BCP输入的工作了。对于这个任务，注意@TableNames 不是必须的，这样下面的脚本就更简单了。另外，如果想测试，建议你建立一个新的数据库， 和Northwind结构一样就可以了。 set nocount on
    
    /* Initialize variables here */
    declare @TableNames table (TableTempID
    smallint IDENTITY(1,1) PRIMARY KEY CLUSTERED, TableName varchar(50))
    declare @TableName varchar(50)
    declare @BackupFileName char(50)
    declare @BackupFolderFile varchar(150)
    declare @Counter smallint
    declare @MaxTableCount smallint
    declare @BackupFolder varchar(100)
    declare @BCPInCommand varchar(500)
    
    /* Set BackupFolder name here */
    set @BackupFolder = 'c:\DataDump\'
    
    /* Get the list of tables that we want to backup */
    insert into @TableNames (TableName) select name from
    Northwind.dbo.sysobjects where xtype = 'U'
    and name not in ('dtproperties')
    
    select @MaxTableCount = max(TableTempID) from @TableNames
    set @Counter = 1
    
    /* Loop through all each table individually, generate bcp
    commands and run bcp commands to export data */
    while @Counter <= @MaxTableCount
    
    Begin
    
    /* Create backup file name */
    select @TableName = ltrim(rtrim(TableName)) from
    @TableNames where TableTempID = @Counter
    
    select @BackupFileName = ltrim(rtrim(@TableName)) + '.txt'
    
    /* Combine backup folder name and file name */
    select @BackupFolderFile = @BackupFolder + @BackupFileName
    
    /* Create BCP command */
    select @BCPInCommand = 'bcp ' + '"Northwind.dbo.' + @TableName +
    '" in "' + ltrim(rtrim(@BackupFolderFile)) + '" -c -q -S '
    + @@Servername + ' -T -E'
    
    print @BCPInCommand
    set @Counter = @Counter + 1
    end

**结论和资源**

对于SQL代码规范编程，希望这个题目对你有所帮助。可以把它当作你开始的一个模板。

\--------------------

【更多资源】

[译：从Sql Server到MySQL移植数据][Sql Server_MySQL]

[使用VBScript自动化SQL任务][VBScript_SQL]

[Oracle与SQL Server数据互用性（3）][Oracle_SQL Server_3]

[Oracle驱动的比较–（2）][Oracle_2]

[Oracle与SQL Server数据互易性（1）][Oracle_SQL Server_1]

[【书籍】人工智能][Link 1]

[利用文件锁机制实现服务器程序的自启动][Link 2]

[_Haidong Ji]: http://www.sqlservercentral.com/Authors/Articles/Haidong_Ji/14430/
[Sql Server_MySQL]: http://9801.me/?p=285
[VBScript_SQL]: http://9801.me/?p=272
[Oracle_SQL Server_3]: http://9801.me/?p=268
[Oracle_2]: http://9801.me/?p=263
[Oracle_SQL Server_1]: http://9801.me/?p=258
[Link 1]: http://9801.me/?p=889
[Link 2]: http://9801.me/?p=113