精华内容
下载资源
问答
  • 十几个数据库,只有一个存储过程,我不想一个一个数据库创建,有什么办法执行一遍之后十几个数据库创建好这个存储过程
  • 存储过程(stored Procedure):是一组为了完成特定功能的SQL语句集,经编译后存储在数据库中,用户通过指定存储过程的名字并提供参数(如果有参数的话)来执行它。数据库中的一个重要学习对象。存储过程的优点:1.速度快...

    存储过程(stored Procedure):是一组为了完成特定功能的SQL语句集,经编译后存储在数据库中,用户通过指定存储过程的名字并提供参数(如果有参数的话)来执行它。数据库中的一个重要学习对象。

    存储过程的优点:

    1.速度快:存储过程只在创造时进行编译,以后每次执行存储过程都不需再重新编译,而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。

    2.当对数据库进行复杂操作时(如对多个表进行Update,Insert,Query,Delete时),可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用.

    3.重复使用:可减少数据库开发人员的工作量

    4.安全性高:可设定只有某此用户才具有对指定存储过程的使用权

    5.减少客户机的压力:存储过程主要是在服务器上运行

    6.减少网络流量

    在命令模式下开始创建存储过程:

    //连接数据库: C:\Documents and Settings\Administrator>mysql -uroot -proot

    //调用指定的数据库: mysql> use db_wlgl Database changed

    //开始创建存储过程:

    mysql> delimiter ;

    mysql> create procedure admin_regs (in x varchar(50),in y varchar(50))

    -> begin

    -> select * from tb_admin where admin_user=x and admin_pass=y;

    -> end;

    //php中调用存储过程:

    $sql=$conn->query("call admin_regs('".$admin_user."','".$admin_pass."')");

    展开全文
  • 如果使用游标来批量创建存储过程,可能你会遇到下面的一些问题,假设我们需要多个数据库(当然可以过滤掉部分数据库中创建同样一个存储过程sp_GetId,存储过程的脚本如下Script1所示: -- Script1: -- ...

    如果使用游标来批量创建存储过程,可能你会遇到下面的一些问题,假设我们需要在多个数据库(当然可以过滤掉部分数据库)中创建同样一个存储过程sp_GetId,存储过程的脚本如下Script1所示:

    复制代码
    -- Script1:
    -- 需要被批量创建的存储过程
    USE [master]
    GO
    Create PROCEDURE [dbo].[sp_GetId]
    AS
    BEGIN
        DECLARE @database_id INT
        SET @database_id = 0
        SELECT TOP 1 @database_id = [database_id] FROM sys.[databases]
    
    END
    复制代码

      根据前面提到使用游标方式,我们可能会写出类似下面的代码,错误代码Script2示例:

    复制代码
    -- Script2:
    -- =============================================
    -- Author:      <听风吹雨>
    -- Blog:        <http://gaizai.cnblogs.com/>
    -- Create date: <2014/05/03>
    -- Description: <批量创建存储过程,错误代码示例>
    -- =============================================
    DECLARE @databaseName VARCHAR(100)
    DECLARE @SQL NVARCHAR(MAX)
    DECLARE @itemCur CURSOR
    
    SET @itemCur = CURSOR FOR
        SELECT '['+[name]+']' FROM sys.databases WHERE database_id > 4
    
    OPEN @itemCur
    FETCH NEXT FROM @itemCur INTO @databaseName
    WHILE @@FETCH_STATUS=0
    BEGIN
        --逻辑处理
        PRINT @databaseName
        
        SET @SQL = '
        USE '+@databaseName+'
        GO
        CREATE PROCEDURE [dbo].[sp_GetId]
    AS
    BEGIN
        DECLARE @database_id INT
        SET @database_id = 0
        SELECT TOP 1 @database_id = [database_id] FROM sys.[databases]
    
    END'
        PRINT(@SQL);
        EXEC(@SQL);
        
        FETCH NEXT FROM @itemCur INTO @databaseName
    END 
    
    CLOSE @itemCur
    DEALLOCATE @itemCur
    复制代码

    执行上面的代码你会遇到这样的错误信息:

    wps_clip_image-14323

    (Figure1:错误信息1)

    根据错误信息修改上面的SQL代码,把”GO”改成“;”但还是会出现下图Figure2的错误信息:

    wps_clip_image-8651

    (Figure2:错误信息2)

    既然这样行不通,也许你还会尝试在[dbo].[sp_GetId]前面加上数据库名的变量,但是却出现下图Figure3的错误信息:

    wps_clip_image-17048

    (Figure3:错误信息3)

    四.实现代码(SQL Codes)

      上面的3个错误让我们陷入了困境,也许你想过放弃了,但是经过努力,我通过2种方式实现了在多个数据库中创建同一个存储过程(大家可认为是批量创建存储过程),下面是实现的2种方式概述:

      1. 通过修改过的系统存储过程[dbo].[sp_MSforeachdb_Filter]和拼接SQL;

      2. 通过创建一个模板存储过程,由系统存储过程sp_MSForEachDB循环调用另外一个创建存储过程的存储来创建模板存储过程(这也许听起来很拗口,看后面的实现脚本Script7,你就会理解了)。

     

    (一) 实现方式1:通过修改过的系统存储过程[dbo].[sp_MSforeachdb_Filter]和拼接SQL;

      1) 首先我们需要在master数据库中创建一个存储过程[dbo].[sp_MSforeachdb_Filter],这是通过修改系统存储过程sp_MSforeachdb得来的,做的改进主要是可以过滤数据库,创建的SQL代码如下Script3所示:

    复制代码
    -- Script3:
    -- =============================================
    -- Author:      <听风吹雨>
    -- Blog:        <http://gaizai.cnblogs.com/>
    -- Create date: <2013.05.06>
    -- Description: <扩展sp_MSforeachdb,增加@whereand参数>
    -- =============================================
    USE [master]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    
    create proc [dbo].[sp_MSforeachdb_Filter]
        @command1 nvarchar(2000), @replacechar nchar(1) = N'?', @command2 nvarchar(2000) = null, @command3 nvarchar(2000) = null,
        @whereand nvarchar(2000) = null,@precommand nvarchar(2000) = null, @postcommand nvarchar(2000) = null
    as
        set deadlock_priority low
        
        /* This proc returns one or more rows for each accessible db, with each db defaulting to its own result set */
        /* @precommand and @postcommand may be used to force a single result set via a temp table. */
    
        /* Preprocessor won't replace within quotes so have to use str(). */
        declare @inaccessible nvarchar(12), @invalidlogin nvarchar(12), @dbinaccessible nvarchar(12)
        select @inaccessible = ltrim(str(convert(int, 0x03e0), 11))
        select @invalidlogin = ltrim(str(convert(int, 0x40000000), 11))
        select @dbinaccessible = N'0x80000000'        /* SQLDMODbUserProf_InaccessibleDb; the negative number doesn't work in convert() */
    
        if (@precommand is not null)
            exec(@precommand)
    
        declare @origdb nvarchar(128)
        select @origdb = db_name()
    
        /* If it's a single user db and there's an entry for it in sysprocesses who isn't us, we can't use it. */
       /* Create the select */
        exec(N'declare hCForEachDatabase cursor global for select name from master.dbo.sysdatabases d ' +
                N' where (d.status & ' + @inaccessible + N' = 0)' +
                N' and (DATABASEPROPERTY(d.name, ''issingleuser'') = 0 and (has_dbaccess(d.name) = 1))' + @whereand)
    
        declare @retval int
        select @retval = @@error
        if (@retval = 0)
            exec @retval = sys.sp_MSforeach_worker @command1, @replacechar, @command2, @command3, 1
    
        if (@retval = 0 and @postcommand is not null)
            exec(@postcommand)
    
       declare @tempdb nvarchar(258)
       SELECT @tempdb = REPLACE(@origdb, N']', N']]')
       exec (N'use ' + N'[' + @tempdb + N']')
    
        return @retval
    复制代码

      2) 接着在master数据库中执行下面的SQL在多个数据库中创建同一个存储过程,其实是把需要创建的存储过程通过拼接保存在@SQL变量中,使用[sp_MSforeachdb_Filter]来过滤数据库,并在符合条件的每个数据库中执行@SQL中的语句,SQL代码如下Script4所示:

    复制代码
    -- Script4:
    --批量创建存储过程
    USE [master]
    GO
    DECLARE @SQL NVARCHAR(MAX)
    SELECT @SQL = COALESCE(@SQL,'') + ' USE [?]; EXEC(''
    CREATE PROCEDURE [dbo].[sp_GetId]
    AS
    BEGIN
        DECLARE @database_id INT
        SET @database_id = 0
        SELECT TOP 1 @database_id = [database_id] FROM sys.[databases]
    
    END'')'
    
    PRINT @SQL
    
    --过滤数据库
    EXEC [sp_MSforeachdb_Filter] @command1=@SQL,
    @whereand=" and [name] not in('tempdb','master','model','msdb') "
    复制代码

      3) 执行上面的SQL脚本之后,除了('tempdb','master','model','msdb')4个数据库之外的数据库都会创建了存储过程sp_GetId,为了快速验证,可以使用下面的SQL脚本进行验证:

    复制代码
    -- Script5:
    -- 返回所有数据库sp_GetId存储过程列表
    IF NOT EXISTS (SELECT * FROM [master].sys.objects 
        WHERE object_id = OBJECT_ID(N'[master].[dbo].[SPList]') AND type in (N'U'))
    BEGIN
    CREATE TABLE [master].[dbo].[SPList](
        [db_name] [sysname] NULL,
        [sp_name] [sysname] NULL,
    ) ON [PRIMARY]
    END
    ELSE
        TRUNCATE TABLE [master].[dbo].[SPList]
    
    EXEC [sp_MSforeachdb_Filter] @command1='
        INSERT INTO [master].[dbo].[SPList]([sp_name])
            SELECT [name] FROM [?].sys.[sysobjects] WHERE TYPE = ''P'' AND [name] = ''sp_GetId''
        UPDATE [master].[dbo].[SPList] SET [db_name] = ''?'' WHERE [db_name] IS NULL
    '
    
    SELECT * FROM [master].[dbo].[SPList]
    复制代码

    执行上面的SQL脚本的结果如下图Figure4所示:

    wps_clip_image-6113

    (Figure4:创建了sp_GetId存储过程的数据库列表)

     

    (二) 实现方式2:通过创建一个模板存储过程,由系统存储过程sp_MSForEachDB循环调用另外一个创建存储过程的存储来创建模板存储过程;

      1) 为了能看到方式2的实际效果,我们需要把存在sp_GetId存储过程的数据库中批量删除这个存储过程,通过下面的脚本Script6来实现:

    复制代码
    -- Script6:
    -- 批量删除存储过程
    USE [master]
    GO
    DECLARE @SQL NVARCHAR(MAX)
    SELECT @SQL = COALESCE(@SQL,'') + ' USE [?]; EXEC(''
    IF  EXISTS (SELECT * FROM sys.[objects] 
        WHERE object_id = OBJECT_ID(N''''[dbo].[sp_GetId]'''') 
        AND type in (N''''P'''', N''''PC''''))
    DROP PROCEDURE [dbo].[sp_GetId] '')'
    
    PRINT @SQL
    
    --过滤数据库
    EXEC [sp_MSforeachdb_Filter] @command1=@SQL,
    @whereand=" and [name] not in('tempdb','master','model','msdb') "
    复制代码

      2) 通过Script5确认所有数据库都不存在sp_GetId存储过程;

      3) 接着在master数据库中创建模板存储过程sp_GetId,创建脚本如Script1所示,这里不做重复;

      4) 再接着创建一个存储过程CreateProcedure,这个存储过程的作用就是创建存储过程,在这个存储过程CreateProcedure利用系统表返回sp_GetId存储过程的内容,保存在变量@proc_text中,查询出如下所示:

    复制代码
    -- Script7:
    --创建存储过程的存储过程
    USE [master]
    GO
    -- =============================================
    -- Author:      <听风吹雨>
    -- Blog:        <http://gaizai.cnblogs.com/>
    -- Create date: <2014.05.06>
    -- Description: <创建存储过程的存储过程>
    -- =============================================
    CREATE PROC CreateProcedure
    (
        @dbname SYSNAME,
        @spname SYSNAME
    )
    AS
    BEGIN
        SELECT @dbname = REPLACE(REPLACE(@dbname,'[',''),']','')
    
        IF @dbname <> 'master'
        BEGIN
            DECLARE @proc_text NVARCHAR(MAX)
            SELECT @proc_text = REPLACE([text],'''','''''')
                FROM [sysobjects] o
                INNER JOIN [syscomments] c
                ON c.id = o.id
            WHERE
                o.type = 'P' AND
                o.name = @spname
    
            DECLARE @sql NVARCHAR(MAX)
            SET @sql = 'USE [' + @dbname + ']; EXEC ('' ' + @proc_text + ''');'
    
            EXEC SP_EXECUTESQL @sql
        END
    END
    GO
    复制代码

      5) 准备完上面的步骤,只需要下面的一条SQL语句就能批量创建存储过程sp_GetId:

    复制代码
    -- Script8:
    -- 批量创建存储过程
    USE [master]
    GO
    
    --过滤数据库
    EXEC [sp_MSforeachdb_Filter] @command1='CreateProcedure ''[?]'', ''sp_GetId''',
    @whereand=" and [name] not in('tempdb','master','model','msdb') "
    复制代码

    执行上面的SQL脚本的结果如下图Figure5所示,与Figure4的区别就是在master数据库中多了一个模板存储过程sp_GetId。

    wps_clip_image-14563

    (Figure5:创建了sp_GetId存储过程的数据库列表)

    (三) 总结

      上面已经通过两种方式实现了在多个数据库中创建同一个存储过程,如果存储过程sp_GetId属于比较简单的,使用方式1实现会比较快捷,如果sp_GetId比较复杂了,比如存储过程里面还包含单引号或者代码比较多的情况下,建议使用方式2,虽然方式2的步骤会多一点,但是只要创建好模板存储过程,其它的根本不会因为存储过程sp_GetId而变得复杂;

    (四) 扩展阅读

      在实际运用中,很多时候你需要的并不单单是在多个数据库中创建同一个存储过程,可能还需要修改同一个存储过程,通过上面的阅读你也许猜到修改存储过程,可以先删除,再创建,对的,这是没有问题的,不过也可以直接修改,下面提供SQL代码:

      1) 首先修改下master数据库的模板存储过程sp_GetId,在存储过程里面中加入一个变量@id:

    复制代码
    -- Script10:
    -- 需要被批量创建的存储过程
    USE [master]
    GO
    Create PROCEDURE [dbo].[sp_GetId]
    AS
    BEGIN
        DECLARE @database_id INT
        --修改部分,增加了一个变量
        DECLARE @id INT
        SET @database_id = 0
        SELECT TOP 1 @database_id = [database_id] FROM sys.[databases]
    
    END
    复制代码

      2) 接着创建一个修改存储过程的存储过程AlterProcedure,只需要把变量@proc_text里面的“CREATE PROC”替换成“ALTER PROC”就可以了:

    复制代码
    -- Script10:
    --修改存储过程的存储过程
    USE [master]
    GO
    -- =============================================
    -- Author:      <听风吹雨>
    -- Blog:        <http://gaizai.cnblogs.com/>
    -- Create date: <2014.05.06>
    -- Description: <修改存储过程的存储过程>
    -- =============================================
    CREATE PROC AlterProcedure
    (
        @dbname SYSNAME,
        @spname SYSNAME
    )
    AS
    BEGIN
        SELECT @dbname = REPLACE(REPLACE(@dbname,'[',''),']','')
    
        IF @dbname <> 'master'
        BEGIN
            DECLARE @proc_text NVARCHAR(MAX)
            SELECT @proc_text = REPLACE([text],'''','''''')
                FROM [sysobjects] o
                INNER JOIN [syscomments] c
                ON c.id = o.id
            WHERE
                o.type = 'P' AND
                o.name = @spname
    
            DECLARE @sql NVARCHAR(MAX)
            SET @proc_text = REPLACE(@proc_text,'CREATE PROC','ALTER PROC')
            SET @sql = 'USE [' + @dbname + ']; EXEC ('' ' + @proc_text + ''');'
    
            EXEC SP_EXECUTESQL @sql
        END
    END
    GO
    复制代码

      3) 准备完上面的步骤,再把Script8的脚本中调用存储过程CreateProcedure改成调用存储过程AlterProcedure,通过下面的一条SQL语句批量修改存储过程sp_GetId:

    复制代码
    -- Script11:
    -- 批量修改存储过程
    USE [master]
    GO
    
    --过滤数据库
    EXEC [sp_MSforeachdb_Filter] @command1='AlterProcedure ''[?]'', ''sp_GetId''',
    @whereand=" and [name] not in('tempdb','master','model','msdb') "
    复制代码

      4) 创建完成后,剩下的就是验证下数据库中存储过程sp_GetId的内容了;

    展开全文
  • SQL Server 多个数据库中创建一个存储过程(Create Same Stored Procedure in All Databases) 原文:SQL Server 多个数据库中创建一个存储过程(Create Same Stored Procedure in All ...
    原文:SQL Server 在多个数据库中创建同一个存储过程(Create Same Stored Procedure in All Databases)

    一.本文所涉及的内容(Contents)

    1. 本文所涉及的内容(Contents)
    2. 背景(Contexts)
    3. 遇到的问题(Problems)
    4. 实现代码(SQL Codes)
      1. 方法一:拼接SQL;
      2. 方法二:调用模板存储过程创建存储过程;
      3. 总结
      4. 扩展阅读
    5. 参考文献(References)

    二.背景(Contexts)

      在我的数据库服务器上,同一个实例下面挂载着许多相同结构的数据库,他们为不同公司提供着服务,在许多时候我需要同时创建、修改、删除一些对象,存储过程就是其中一个,但是想要批量创建存储,这有些特殊,下面就教你如何实现在多个数据库中创建同一个存储过程(Create Same Stored Procedure in All Databases)。

    三.遇到的问题(Problems)

      在之前的文章中多次谈到使用游标的方式处理的各种问题:

      如果使用游标来批量创建存储过程,可能你会遇到下面的一些问题,假设我们需要在多个数据库(当然可以过滤掉部分数据库)中创建同样一个存储过程sp_GetId,存储过程的脚本如下Script1所示:

    -- Script1:
    -- 需要被批量创建的存储过程
    USE [master]
    GO
    Create PROCEDURE [dbo].[sp_GetId]
    AS
    BEGIN
        DECLARE @database_id INT
        SET @database_id = 0
        SELECT TOP 1 @database_id = [database_id] FROM sys.[databases]
    
    END

      根据前面提到使用游标方式,我们可能会写出类似下面的代码,错误代码Script2示例:

    -- Script2:
    -- =============================================
    -- Author:      <听风吹雨>
    -- Blog:        <http://gaizai.cnblogs.com/>
    -- Create date: <2014/05/03>
    -- Description: <批量创建存储过程,错误代码示例>
    -- =============================================
    DECLARE @databaseName VARCHAR(100)
    DECLARE @SQL NVARCHAR(MAX)
    DECLARE @itemCur CURSOR
    
    SET @itemCur = CURSOR FOR
        SELECT '['+[name]+']' FROM sys.databases WHERE database_id > 4
    
    OPEN @itemCur
    FETCH NEXT FROM @itemCur INTO @databaseName
    WHILE @@FETCH_STATUS=0
    BEGIN
        --逻辑处理
        PRINT @databaseName
        
        SET @SQL = '
        USE '+@databaseName+'
        GO
        CREATE PROCEDURE [dbo].[sp_GetId]
    AS
    BEGIN
        DECLARE @database_id INT
        SET @database_id = 0
        SELECT TOP 1 @database_id = [database_id] FROM sys.[databases]
    
    END'
        PRINT(@SQL);
        EXEC(@SQL);
        
        FETCH NEXT FROM @itemCur INTO @databaseName
    END 
    
    CLOSE @itemCur
    DEALLOCATE @itemCur

    执行上面的代码你会遇到这样的错误信息:

    wps_clip_image-14323

    (Figure1:错误信息1)

    根据错误信息修改上面的SQL代码,把”GO”改成“;”但还是会出现下图Figure2的错误信息:

    wps_clip_image-8651

    (Figure2:错误信息2)

    既然这样行不通,也许你还会尝试在[dbo].[sp_GetId]前面加上数据库名的变量,但是却出现下图Figure3的错误信息:

    wps_clip_image-17048

    (Figure3:错误信息3)

    四.实现代码(SQL Codes)

      上面的3个错误让我们陷入了困境,也许你想过放弃了,但是经过努力,我通过2种方式实现了在多个数据库中创建同一个存储过程(大家可认为是批量创建存储过程),下面是实现的2种方式概述:

      1. 通过修改过的系统存储过程[dbo].[sp_MSforeachdb_Filter]和拼接SQL;

      2. 通过创建一个模板存储过程,由系统存储过程sp_MSForEachDB循环调用另外一个创建存储过程的存储来创建模板存储过程(这也许听起来很拗口,看后面的实现脚本Script7,你就会理解了)。

     

    (一) 实现方式1:通过修改过的系统存储过程[dbo].[sp_MSforeachdb_Filter]和拼接SQL;

      1) 首先我们需要在master数据库中创建一个存储过程[dbo].[sp_MSforeachdb_Filter],这是通过修改系统存储过程sp_MSforeachdb得来的,做的改进主要是可以过滤数据库,创建的SQL代码如下Script3所示:

    -- Script3:
    -- =============================================
    -- Author:      <听风吹雨>
    -- Blog:        <http://gaizai.cnblogs.com/>
    -- Create date: <2013.05.06>
    -- Description: <扩展sp_MSforeachdb,增加@whereand参数>
    -- =============================================
    USE [master]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    
    create proc [dbo].[sp_MSforeachdb_Filter]
        @command1 nvarchar(2000), @replacechar nchar(1) = N'?', @command2 nvarchar(2000) = null, @command3 nvarchar(2000) = null,
        @whereand nvarchar(2000) = null,@precommand nvarchar(2000) = null, @postcommand nvarchar(2000) = null
    as
        set deadlock_priority low
        
        /* This proc returns one or more rows for each accessible db, with each db defaulting to its own result set */
        /* @precommand and @postcommand may be used to force a single result set via a temp table. */
    
        /* Preprocessor won't replace within quotes so have to use str(). */
        declare @inaccessible nvarchar(12), @invalidlogin nvarchar(12), @dbinaccessible nvarchar(12)
        select @inaccessible = ltrim(str(convert(int, 0x03e0), 11))
        select @invalidlogin = ltrim(str(convert(int, 0x40000000), 11))
        select @dbinaccessible = N'0x80000000'        /* SQLDMODbUserProf_InaccessibleDb; the negative number doesn't work in convert() */
    
        if (@precommand is not null)
            exec(@precommand)
    
        declare @origdb nvarchar(128)
        select @origdb = db_name()
    
        /* If it's a single user db and there's an entry for it in sysprocesses who isn't us, we can't use it. */
       /* Create the select */
        exec(N'declare hCForEachDatabase cursor global for select name from master.dbo.sysdatabases d ' +
                N' where (d.status & ' + @inaccessible + N' = 0)' +
                N' and (DATABASEPROPERTY(d.name, ''issingleuser'') = 0 and (has_dbaccess(d.name) = 1))' + @whereand)
    
        declare @retval int
        select @retval = @@error
        if (@retval = 0)
            exec @retval = sys.sp_MSforeach_worker @command1, @replacechar, @command2, @command3, 1
    
        if (@retval = 0 and @postcommand is not null)
            exec(@postcommand)
    
       declare @tempdb nvarchar(258)
       SELECT @tempdb = REPLACE(@origdb, N']', N']]')
       exec (N'use ' + N'[' + @tempdb + N']')
    
        return @retval

      2) 接着在master数据库中执行下面的SQL在多个数据库中创建同一个存储过程,其实是把需要创建的存储过程通过拼接保存在@SQL变量中,使用[sp_MSforeachdb_Filter]来过滤数据库,并在符合条件的每个数据库中执行@SQL中的语句,SQL代码如下Script4所示:

    -- Script4:
    --批量创建存储过程
    USE [master]
    GO
    DECLARE @SQL NVARCHAR(MAX)
    SELECT @SQL = COALESCE(@SQL,'') + ' USE [?]; EXEC(''
    CREATE PROCEDURE [dbo].[sp_GetId]
    AS
    BEGIN
        DECLARE @database_id INT
        SET @database_id = 0
        SELECT TOP 1 @database_id = [database_id] FROM sys.[databases]
    
    END'')'
    
    PRINT @SQL
    
    --过滤数据库
    EXEC [sp_MSforeachdb_Filter] @command1=@SQL,
    @whereand=" and [name] not in('tempdb','master','model','msdb') "

      3) 执行上面的SQL脚本之后,除了('tempdb','master','model','msdb')4个数据库之外的数据库都会创建了存储过程sp_GetId,为了快速验证,可以使用下面的SQL脚本进行验证:

    -- Script5:
    -- 返回所有数据库sp_GetId存储过程列表
    IF NOT EXISTS (SELECT * FROM [master].sys.objects 
        WHERE object_id = OBJECT_ID(N'[master].[dbo].[SPList]') AND type in (N'U'))
    BEGIN
    CREATE TABLE [master].[dbo].[SPList](
        [db_name] [sysname] NULL,
        [sp_name] [sysname] NULL,
    ) ON [PRIMARY]
    END
    ELSE
        TRUNCATE TABLE [master].[dbo].[SPList]
    
    EXEC [sp_MSforeachdb_Filter] @command1='
        INSERT INTO [master].[dbo].[SPList]([sp_name])
            SELECT [name] FROM [?].sys.[sysobjects] WHERE TYPE = ''P'' AND [name] = ''sp_GetId''
        UPDATE [master].[dbo].[SPList] SET [db_name] = ''?'' WHERE [db_name] IS NULL
    '
    
    SELECT * FROM [master].[dbo].[SPList]

    执行上面的SQL脚本的结果如下图Figure4所示:

    wps_clip_image-6113

    (Figure4:创建了sp_GetId存储过程的数据库列表)

     

    (二) 实现方式2:通过创建一个模板存储过程,由系统存储过程sp_MSForEachDB循环调用另外一个创建存储过程的存储来创建模板存储过程;

      1) 为了能看到方式2的实际效果,我们需要把存在sp_GetId存储过程的数据库中批量删除这个存储过程,通过下面的脚本Script6来实现:

    -- Script6:
    -- 批量删除存储过程
    USE [master]
    GO
    DECLARE @SQL NVARCHAR(MAX)
    SELECT @SQL = COALESCE(@SQL,'') + ' USE [?]; EXEC(''
    IF  EXISTS (SELECT * FROM sys.[objects] 
        WHERE object_id = OBJECT_ID(N''''[dbo].[sp_GetId]'''') 
        AND type in (N''''P'''', N''''PC''''))
    DROP PROCEDURE [dbo].[sp_GetId] '')'
    
    PRINT @SQL
    
    --过滤数据库
    EXEC [sp_MSforeachdb_Filter] @command1=@SQL,
    @whereand=" and [name] not in('tempdb','master','model','msdb') "

      2) 通过Script5确认所有数据库都不存在sp_GetId存储过程;

      3) 接着在master数据库中创建模板存储过程sp_GetId,创建脚本如Script1所示,这里不做重复;

      4) 再接着创建一个存储过程CreateProcedure,这个存储过程的作用就是创建存储过程,在这个存储过程CreateProcedure利用系统表返回sp_GetId存储过程的内容,保存在变量@proc_text中,查询出如下所示:

    -- Script7:
    --创建存储过程的存储过程
    USE [master]
    GO
    -- =============================================
    -- Author:      <听风吹雨>
    -- Blog:        <http://gaizai.cnblogs.com/>
    -- Create date: <2014.05.06>
    -- Description: <创建存储过程的存储过程>
    -- =============================================
    CREATE PROC CreateProcedure
    (
        @dbname SYSNAME,
        @spname SYSNAME
    )
    AS
    BEGIN
        SELECT @dbname = REPLACE(REPLACE(@dbname,'[',''),']','')
    
        IF @dbname <> 'master'
        BEGIN
            DECLARE @proc_text NVARCHAR(MAX)
            SELECT @proc_text = REPLACE([text],'''','''''')
                FROM [sysobjects] o
                INNER JOIN [syscomments] c
                ON c.id = o.id
            WHERE
                o.type = 'P' AND
                o.name = @spname
    
            DECLARE @sql NVARCHAR(MAX)
            SET @sql = 'USE [' + @dbname + ']; EXEC ('' ' + @proc_text + ''');'
    
            EXEC SP_EXECUTESQL @sql
        END
    END
    GO

      5) 准备完上面的步骤,只需要下面的一条SQL语句就能批量创建存储过程sp_GetId:

    -- Script8:
    -- 批量创建存储过程
    USE [master]
    GO
    
    --过滤数据库
    EXEC [sp_MSforeachdb_Filter] @command1='CreateProcedure ''[?]'', ''sp_GetId''',
    @whereand=" and [name] not in('tempdb','master','model','msdb') "

    执行上面的SQL脚本的结果如下图Figure5所示,与Figure4的区别就是在master数据库中多了一个模板存储过程sp_GetId。

    wps_clip_image-14563

    (Figure5:创建了sp_GetId存储过程的数据库列表)

    (三) 总结

      上面已经通过两种方式实现了在多个数据库中创建同一个存储过程,如果存储过程sp_GetId属于比较简单的,使用方式1实现会比较快捷,如果sp_GetId比较复杂了,比如存储过程里面还包含单引号或者代码比较多的情况下,建议使用方式2,虽然方式2的步骤会多一点,但是只要创建好模板存储过程,其它的根本不会因为存储过程sp_GetId而变得复杂;

    (四) 扩展阅读

      在实际运用中,很多时候你需要的并不单单是在多个数据库中创建同一个存储过程,可能还需要修改同一个存储过程,通过上面的阅读你也许猜到修改存储过程,可以先删除,再创建,对的,这是没有问题的,不过也可以直接修改,下面提供SQL代码:

      1) 首先修改下master数据库的模板存储过程sp_GetId,在存储过程里面中加入一个变量@id:

    -- Script10:
    -- 需要被批量创建的存储过程
    USE [master]
    GO
    Create PROCEDURE [dbo].[sp_GetId]
    AS
    BEGIN
        DECLARE @database_id INT
        --修改部分,增加了一个变量
        DECLARE @id INT
        SET @database_id = 0
        SELECT TOP 1 @database_id = [database_id] FROM sys.[databases]
    
    END

      2) 接着创建一个修改存储过程的存储过程AlterProcedure,只需要把变量@proc_text里面的“CREATE PROC”替换成“ALTER PROC”就可以了:

    -- Script10:
    --修改存储过程的存储过程
    USE [master]
    GO
    -- =============================================
    -- Author:      <听风吹雨>
    -- Blog:        <http://gaizai.cnblogs.com/>
    -- Create date: <2014.05.06>
    -- Description: <修改存储过程的存储过程>
    -- =============================================
    CREATE PROC AlterProcedure
    (
        @dbname SYSNAME,
        @spname SYSNAME
    )
    AS
    BEGIN
        SELECT @dbname = REPLACE(REPLACE(@dbname,'[',''),']','')
    
        IF @dbname <> 'master'
        BEGIN
            DECLARE @proc_text NVARCHAR(MAX)
            SELECT @proc_text = REPLACE([text],'''','''''')
                FROM [sysobjects] o
                INNER JOIN [syscomments] c
                ON c.id = o.id
            WHERE
                o.type = 'P' AND
                o.name = @spname
    
            DECLARE @sql NVARCHAR(MAX)
            SET @proc_text = REPLACE(@proc_text,'CREATE PROC','ALTER PROC')
            SET @sql = 'USE [' + @dbname + ']; EXEC ('' ' + @proc_text + ''');'
    
            EXEC SP_EXECUTESQL @sql
        END
    END
    GO

      3) 准备完上面的步骤,再把Script8的脚本中调用存储过程CreateProcedure改成调用存储过程AlterProcedure,通过下面的一条SQL语句批量修改存储过程sp_GetId:

    -- Script11:
    -- 批量修改存储过程
    USE [master]
    GO
    
    --过滤数据库
    EXEC [sp_MSforeachdb_Filter] @command1='AlterProcedure ''[?]'', ''sp_GetId''',
    @whereand=" and [name] not in('tempdb','master','model','msdb') "

      4) 创建完成后,剩下的就是验证下数据库中存储过程sp_GetId的内容了;

    五.参考文献(References)

    Create Same Stored Procedure on All Databases using sp_MSForEachDB T-SQL Example

    posted on 2018-02-01 15:48 NET未来之路 阅读(...) 评论(...) 编辑 收藏

    转载于:https://www.cnblogs.com/lonelyxmas/p/8399403.html

    展开全文
  • 任务13 建立和使用存储过程 【任务... 使用时只要调用这个存储过程或者存储函数,根据指定的用户类别,就可以将不同类别用户的利息计算出来。 再如,编制学生管理系统时,当某个学生某门课程的成绩修改后,根据成...

    任务13 建立和使用存储过程 【任务背景】 银行经常需要计算用户的利息,但不同类别的用户的利率是不一样的。这就可以将计算利率的SQL代码写成一个程序存放起来,用指定的用户类别作参数。这样的程序叫作存储过程或者存储函数。 使用时只要调用这个存储过程或者存储函数,根据指定的用户类别,就可以将不同类别用户的利息计算出来。 再如,在编制学生管理系统时,当某个学生某门课程的成绩修改后,根据成绩CJ是否高于60分更新credit表,将符合条件的学生某门课的学分累加到该生的总学分里。 这是一组重复使用的一段SQL语句。可以将这段SQL语句写成存储过程或存储函数存储在MySQL服务器中,然后再调用,就可以执行多次重复的操作。 【任务要求】 本任务将从认识存储过程着手,学习创建、执行、修改和删除存储过程的方法。包括创建基本的存储过程,创建带有变量的存储过程,创建带有流程控制语句的存储过程。 13.1 认识存储过程 从MySQL 5.1版本开始支持存储过程和存储函数。 在MySQL中,可以定义一段完成特定功能的SQL 语句集,经编译后存储在数据库中,用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它,这样的语句集称为存储过程。 使用存储过程的优点有如下几个方面。 3.2 创建基本的存储过程 13.2.1 关于DELIMITER命令 在MySQL命令行的客户端中,服务器处理语句默认是以分号(;)为结束标志的,如果有一行命令以分号(;)结束,那么按后,MySQL将会执行该命令。 但是在存储过程中,可能要输入较多的语句,且语句中包含有分号。如果还以分号作为结束标志,那么执行完第一个分号语句后,就会认为程序结束,不能再往下执行其他语句。因此,必须将MySQL语句的结束标志修改为其他符号。这时,可以使用MySQL DELIMITER来改变默认结束标志。 DELIMITER语法格式为: DELIMITER $$ 说明 $$是用户定义的结束符,通常这个符号可以是一些特殊的符号,如两个“#”或两个“¥”等。当使用DELIMITER命令时,应该避免使用反斜杠(“\”)字符,因为那是MySQL的转义字符。 输入如下命令,就是告诉MySQL解释器,当碰到“//”时,才执行命令。 mySQL>delimiter // 例如,要查看student表的信息。 mySQL>Select * from student// 要想将命令结束符重新设定为分号,运行下面命令即可。 DELIMITER ; 13.2.2 创建基本存储过程 创建存储过程可以使用CREATE PROCEDURE语句。要在MySQL 5.5中创建存储过程,必须具有CREATE ROUTINE权限。 CREATE PROCEDURE的语法格式如下。 CREATE PROCEDURE sp_name ([proc_parameter[,...]]) [characteristic ...] routine_body 说明 (1)sp_name是存储过程的名称。需要在特定数据库中创建存储过程时,则要在名称前面加上数据库的名称,格式为db_name.sp_name。 (2)proc_parameter是存储过程的参数,在使用参数时要标明参数名和参数的类型,当有多个参数的时候中间用逗号隔开。 MySQL存储过程支持3种类型的参数:输入参数、输出参数和输入/输出参数,关键字分别是IN、OUT和INOUT。存储过程也可以不加参数,但是名称后面的括号是不可省略的。 (3)routine_body是存储过程体。里面包含了在过程调用的时候必须执行的语句,这个部分总是以BEGIN开始,以END结束。当然,当存储过程体中只有一个SQL语句时可以省略BEGIN-END标志。 (4)characteristic:存储过程的某些特征设定(略)。 【任务13.1】 创建存储过程,用指定的学号作为参数删除某一学生的记录。 mysql>DELIMITER $$ CREATE PROCEDURE DELETE_STUDENTS(IN XH CHAR(6)) BEGIN DELETE FROM STUDENT WHERE S_N0=XH; END $$ DELIMITER ; 【任务13.2】 创建存储过程,用指定的课程号参数统计该课程的平均成绩。 mysql>DELIMITER $$ CREAT

    展开全文
  • JDBC调用存储过程步骤:1 通过Connection对象的prepareCall()方法创建一个CallableStatement对象的实例。使用Connection对象的prepareCall()方法时,需要传入一个String类型的字符串,该字符串用于指明如何调用...
  • sql 版本 ...sql servre 帮助文档存储过程的解释 ... 创建存储过程存储过程是已保存的 Transact...可以创建过程供永久使用,或在一个会话(局部临时过程)临时使用,或所有会话(全局临时过程)临时...
  • 1、首先我们通过Administratortest数据库中创建一个简单的表名叫”products“的,里面包括下面这两个字段:2、然后打开Query Browser,并且test中创建存储过程存储过程名称为”GetAll“。3、可以看到系统会...
  • 使用存储过程1存储过程2为什么要使用存储过程3执行存储过程4创建存储过程 1存储过程 使用的大多数SQL语句都是针对一个或多个表的单条语句。并非所有操作都这么简单,经常会有一个完整的操作需要多条语句才能完成。那...
  • 特别是 SQL 代码封装功能,假设没有存储过程外部程序訪问数据库时(比如 PHP),要组织非常多 SQL 语句。特别是业务逻辑复杂的时候,大堆的SQL 和条件夹杂 PHP 代码,让人不寒而栗。如今有了 MySQL 存储过程...
  • 查询分析器中执行如下语句创建一个存储过程。 3.企业管理器中创建存储过程的方法为,展开相应的数据库sample,“存储过程”节点上右击鼠标,弹出的菜单中选择“新建存储过程”命令。 4...
  • JDBC调用存储过程步骤:1 通过Connection对象的prepareCall()方法创建一个CallableStatement对象的实例。使用Connection对象的prepareCall()方法时,需要传入一个String类型的字符串,该字符串用于指明如何调用...
  • 特别是 SQL 代码封装功能,假设没有存储过程外部程序訪问数据库时(比如 PHP),要组织非常多 SQL 语句。特别是业务逻辑复杂的时候,大堆的SQL 和条件夹杂 PHP 代码,让人不寒而栗。如今有了 MySQL 存储过程...
  • MySQL数据库创建存储过程中所遇到的一些问题,仅供参考 下面是我刚开始创建存储过程的语句 CREATE PROCEDURE bootdemooness(usernames OUT VARCHAR) BEGIN SELECT username FROM sb_user WHERE id=1000; END; ...
  • 数据库视频第九章存储过程

    热门讨论 2021-01-29 10:00:02
    经编译后存储在数据库中存储过程可包含程序流、逻辑以及对数据库的查询。他们可以接受参数、输出参数、返回单个或多结果集以及返回值 为什要存储过程 存储过程种类 用户自定义存储过程 系统存储过程 扩展存储...
  • 数据库存储过程创建

    千次阅读 2017-02-20 10:40:37
     首先,存储过程和函数实现上是相差不大的,所谓的存储过,其实是相对一单条sql来讲的一组的sql语句的集合,一个存储过程中可以包含多条sql语句。这里举一个简单的例子,比如说,我们网上商城买东西的时候,...
  • 一个存储过程通常用于完成一段业务逻辑,例如报名,交班费,订单入库等。而一个函数通常专注与某个功能,视为其他程序服务的,需要其他语句调用函数才可以,而存储过程不能被其他调用,是自己执行 通过call执行...
  • 特别是 SQL 代码封装功能,假设没有存储过程外部程序訪问数据库时(比如 PHP),要组织非常多 SQL 语句。特别是业务逻辑复杂的时候,大堆的 SQL 和条件夹杂 PHP 代码,让人不寒而栗。如今有了 MySQL ...
  • 视图的操作存储过程与存储函数触发器的操作(最末尾有测试数据库表和数据代码)一、视图的操作1.1视图可以用来做什么?简单的说视图就是一个查询,它业务只需要最简单的查询语句就可以获取到业务需要的数据。...
  • 1)从 DB2 的目录表 SYSCAT.ROUTINES 选取: SYSCAT.ROUTINES 系统目录表,有一个名为 TEXT 的字段,其数据类型定义为 CLOB,长度为 2M(2097152 个字节),用于存放存储过程创建语句。为得到全部存储过程的...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 4,902
精华内容 1,960
关键字:

在数据库中创建一个存储过程