发布新日志

  • SQL自定义函数

    2009-12-12 16:31:40

  • sql2005创建存储过程(需要注意的几点)

    2009-12-12 16:09:43

    创建存储过程。存储过程是已保存的 Transact-SQL 语句集合,或对 Microsoft .NET Framework 公共语言运行时 (CLR) 方法的引用,可接收并返回用户提供的参数。可以创建过程供永久使用,或在一个会话(局部临时过程)中临时使用,或在所有会话(全局临时过程)中临时使用。

    启动 SQL Server 的一个实例时,也可以创建并自动运行存储过程。

     Transact-SQL 语法约定

    语法
     
    CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ]
        [ { @parameter [ type_schema_name. ] data_type }
            [ VARYING ] [ = default ] [ [ OUT [ PUT ]
        ] [ ,...n ]
    [ WITH <procedure_option> [ ,...n ]
    [ FOR REPLICATION ]
    AS { <sql_statement> [;][ ...n ] | <method_specifier> }
    [;]
    <procedure_option> ::=
        [ ENCRYPTION ]
        [ RECOMPILE ]
        [ EXECUTE_AS_Clause ]

    <sql_statement> ::=
    { [ BEGIN ] statements [ END ] }

    <method_specifier> ::=
    EXTERNAL NAME assembly_name.class_name.method_name
     

    备注
    Transact-SQL 存储过程的最大大小为 128 MB。

    只能在当前数据库中创建用户定义存储过程。临时过程对此是个例外,因为它们总是在 tempdb 中创建。如果未指定架构名称,则使用创建过程的用户的默认架构。有关架构的详细信息,请参阅用户架构分离。

    在单个批处理中,CREATE PROCEDURE 语句不能与其他 Transact-SQL 语句组合使用。

    默认情况下,参数可为空值。如果传递 NULL 参数值并且在 CREATE 或 ALTER TABLE 语句中使用该参数,而该语句中被引用列又不允许使用空值,则数据库引擎 会产生一个错误。若要阻止向不允许使用空值的列传递 NULL,请为过程添加编程逻辑,或使用 CREATE TABLE 或 ALTER TABLE 的 DEFAULT 关键字,以便对该列使用默认值。

    存储过程中的任何 CREATE TABLE 或 ALTER TABLE 语句都将自动创建临时表。建议对于临时表中的每列,显式指定 NULL 或 NOT NULL。如果在 CREATE TABLE 或 ALTER TABLE 语句中未进行指定,则 ANSI_DFLT_ON 和 ANSI_DFLT_OFF 选项将控制数据库引擎 为列指派 NULL 或 NOT NULL 属性的方式。如果某个连接执行的存储过程对这些选项的设置与创建该过程的连接的设置不同,则为第二个连接创建的表列可能会有不同的为空性,并且显示出不同的行为。如果为每个列显式声明了 NULL 或 NOT NULL,那么将对所有执行该存储过程的连接使用相同的为空性创建临时表。

    使用 SET 选项
    在创建或修改 Transact-SQL 存储过程时,数据库引擎 将保存 SET QUOTED_IDENTIFIER 和 SET ANSI_NULLS 的设置。执行存储过程时,将使用这些原始设置。因此,所有客户端会话的 SET QUOTED_IDENTIFIER 和 SET ANSI_NULLS 设置在执行存储过程时都将被忽略。在创建或更改存储过程时不保存其他 SET 选项(例如 SET ARITHABORT、SET ANSI_WARNINGS 或 SET ANSI_PADDINGS)。如果存储过程的逻辑取决于特定的设置,则应在过程开头添加一条 SET 语句,以确保设置正确。从存储过程中执行 SET 语句时,该设置只在存储过程完成之前有效。之后,设置将还原为调用存储过程时的值。这样一来,单个客户端就可以设置所需的选项,而不会影响存储过程的逻辑。

    注意: 
    在传递存储过程或用户定义函数中的参数时,或在声明和设置批语句中的变量时,ANSI_WARNINGS 的优先级较低。例如,如果一个变量被定义为 char(3),但后来将该参数设置为一个大于三个字符的值,则数据将被截断为定义的大小,并且 INSERT 或 UPDATE 语句将执行成功。
     


    使用 CLR 存储过程的参数
    CLR 存储过程的参数可以是标量 SQL Server 系统数据类型的任何一种。

    为了使数据库引擎 在 .NET Framework 中被重载时引用正确的方法,<method_specifier> 中指示的方法必须具有下列特征:

    声明为静态方法。


    接收的参数个数与过程的参数个数相同。


    不能是类的构造函数或析构函数。


    使用的参数类型与 SQL Server 过程的相应参数的数据类型兼容。有关将 SQL Server 数据类型与 .NET Framework 数据类型匹配的信息,请参阅 SQL Server Data Types and Their .NET Framework Equivalents。


    返回 void,或者返回类型为 SQLInt32、SQLInt16、System.Int32 或 System.Int16 的值。


    如果对于任何特定的参数声明都指定了 OUTPUT,则按照引用返回它的参数,而不是按照值返回。


    获得有关存储过程的信息
    若要显示 Transact-SQL 存储过程的定义,请使用该过程所在的数据库中的 sys.sql_modules 目录视图。

    例如:

     复制代码
    USE AdventureWorks;
    GO
    SELECT definition
    FROM sys.sql_modules
    JOIN sys.objects ON sys.sql_modules.object_id = sys.objects.object_id AND TYPE = ''P'';
     
    注意: 
    不能使用 sys.sql_modules 目录视图查看使用 ENCRYPTION 选项创建的存储过程的文本。
     


    若要获取有关某过程引用的对象的报表,请查询 sys.sql_dependencies 目录视图或使用 sp_depends。sp_depends 不返回有关 CLR 存储过程引用的对象的信息。若要显示有关 CLR 存储过程的信息,请使用该过程所在的数据库中的 sys.assembly_modules 目录视图。

    若要显示有关存储过程中定义的参数的信息,请使用该过程所在的数据库中的 sys.parameters 目录视图。

    延迟名称解析
    可以创建引用尚不存在的表的存储过程。在创建时,只进行语法检查。直到第一次执行该存储过程时才对其进行编译。只有在编译过程中才解析存储过程中引用的所有对象。因此,如果语法正确的存储过程引用了不存在的表,则仍可以成功创建;但如果引用的表不存在,则存储过程将在运行时失败。有关详细信息,请参阅延迟名称解析和编译。

    执行存储过程
    当执行用户定义的存储过程时,无论是在批中还是在模块(例如用户定义的存储过程或函数)内,极力建议使用架构名称来限定存储过程名。

    如果存储过程编写为可以接受参数值,则可以提供参数值。该值必须是常量或变量。不能指定函数名作为参数值。变量可以是用户定义变量或系统变量,例如 @@SPID。

    有关详细信息,请参阅执行存储过程(数据库引擎)。

    第一次执行某个过程时,将编译该过程以确定检索数据的最优访问计划。如果已经生成的计划仍保留在数据库引擎 计划缓存中,则存储过程随后执行的操作可能重新使用该计划。有关详细信息,请参阅执行计划的缓存和重新使用。

    使用 cursor 数据类型的参数
    Transact-SQL 存储过程只能将 cursor 数据类型用于 OUTPUT 参数。如果为某个参数指定了 cursor 数据类型,则还需要 VARYING 和 OUTPUT 参数。如果为某个参数指定了 VARYING 关键字,则数据类型必须是 cursor,并且必须指定 OUTPUT 关键字。有关详细信息,请参阅在 OUTPUT 参数中使用 cursor 数据类型。

    临时存储过程
    数据库引擎 支持两种临时过程:局部临时过程和全局临时过程。局部临时过程只对创建该过程的连接可见。全局临时过程则可由所有连接使用。局部临时过程在当前会话结束时将被自动删除。全局临时过程在使用该过程的最后一个会话结束时被删除。有关详细信息,请参阅创建存储过程(数据库引擎)。

    自动执行存储过程
    SQL Server 启动时可以自动执行一个或多个存储过程。这些存储过程必须由系统管理员在 master 数据库中创建,并以 sysadmin 固定服务器角色作为后台进程执行。这些过程不能有任何输入或输出参数。有关详细信息,请参阅自动执行存储过程。

    存储过程嵌套
    存储过程可以被嵌套。这表示一个存储过程可以调用另一个存储过程。在被调用过程开始运行时,嵌套级将增加,在被调用过程运行结束后,嵌套级将减少。存储过程最多可以嵌套 32 级。有关详细信息,请参阅嵌套存储过程。

    若要估计编译后的存储过程大小,请使用下列性能监视器计数器。

    性能监视器对象名  性能监视器计数器名称 
    SQLServer: Plan Cache Object
     Cache Hit Ratio
     
     
     Cache Pages
     
     
     Cache Object Counts*
     

    * 各种类别的缓存对象均可以使用这些计数器,包括即席 sql、准备好的 sql、过程、触发器等。

    有关详细信息,请参阅 SQL Server Plan Cache 对象。

    <sql_statement> 限制
    可以在存储过程中指定除了 SET SHOWPLAN_TEXT 和 SET SHOWPLAN_ALL 以外的任何 SET 语句。这些语句在批处理中必须唯一。选择的 SET 选项在存储过程执行过程中有效,之后恢复为原来的设置。

    如果用户不是存储过程所有者,则在使用存储过程时,必须使用对象架构名称对存储过程内所有数据定义语言 (DDL) 语句(例如 CREATE、ALTER 或 DROP 语句、DBCC 语句、EXECUTE 和动态 SQL 语句)中使用的对象名称进行限定。有关详细信息,请参阅设计存储过程(数据库引擎)。

    参数
    schema_name
    过程所属架构的名称。

    procedure_name
    新存储过程的名称。过程名称必须遵循有关标识符的规则,并且在架构中必须唯一。

    极力建议不在过程名称中使用前缀 sp_。此前缀由 SQL Server 使用,以指定系统存储过程。有关详细信息,请参阅创建存储过程(数据库引擎)。

    可在 procedure_name 前面使用一个数字符号 (#) (#procedure_name) 来创建局部临时过程,使用两个数字符号 (##procedure_name) 来创建全局临时过程。对于 CLR 存储过程,不能指定临时名称。

    存储过程或全局临时存储过程的完整名称(包括 ##)不能超过 128 个字符。局部临时存储过程的完整名称(包括 #)不能超过 116 个字符。

    ; number
    用于对同名过程进行分组的可选整数。使用一个 DROP PROCEDURE 语句可将这些分组过程一起删除。例如,称为 orders 的应用程序可能使用名为 orderproc;1、orderproc;2 等的过程。DROP PROCEDURE orderproc 语句将删除整个组。如果名称中包含分隔标识符,则数字不应包含在标识符中;只应在 procedure_name 前后使用适当的分隔符。

    带编号的存储过程有以下限制:

    不能使用 xml 或 CLR 用户定义类型作为数据类型。


    不能对带编号的存储过程创建计划指南。


    注意: 
    后续版本的 Microsoft SQL Server 将删除该功能。请避免在新的开发工作中使用该功能,并应着手修改当前还在使用该功能的应用程序。
     


    @ parameter
    过程中的参数。在 CREATE PROCEDURE 语句中可以声明一个或多个参数。除非定义了参数的默认值或者将参数设置为等于另一个参数,否则用户必须在调用过程时为每个声明的参数提供值。存储过程最多可以有 2,100 个参数。

    通过使用 at 符号 (@) 作为第一个字符来指定参数名称。参数名称必须符合有关标识符的规则。每个过程的参数仅用于该过程本身;其他过程中可以使用相同的参数名称。默认情况下,参数只能代替常量表达式,而不能用于代替表名、列名或其他数据库对象的名称。有关详细信息,请参阅 EXECUTE (Transact-SQL)。

    如果指定了 FOR REPLICATION,则无法声明参数。

    [ type_schema_name. ] data_type
    参数以及所属架构的数据类型。除 table 之外的其他所有数据类型均可以用作 Transact-SQL 存储过程的参数。但是,cursor 数据类型只能用于 OUTPUT 参数。如果指定了 cursor 数据类型,则还必须指定 VARYING 和 OUTPUT 关键字。可以为 cursor 数据类型指定多个输出参数。

    对于 CLR 存储过程,不能指定 char、varchar、text、ntext、image、cursor 和 table 作为参数。有关 CLR 类型与 SQL Server 系统数据类型之间关系的详细信息,请参阅 SQL Server Data Types and Their .NET Framework Equivalents。有关 SQL Server 系统数据类型及其语法的详细信息,请参阅数据类型 (Transact-SQL)。

    如果参数的数据类型为 CLR 用户定义类型,则必须对此类型有 EXECUTE 权限。

    如果未指定 type_schema_name,则 SQL Server 2005 Database Engine 将按以下顺序引用 type_name:

    SQL Server 系统数据类型。


    当前数据库中当前用户的默认架构。


    当前数据库中的 dbo 架构。


    对于带编号的存储过程,数据类型不能为 xml 或 CLR 用户定义类型。

    VARYING
    指定作为输出参数支持的结果集。该参数由存储过程动态构造,其内容可能发生改变。仅适用于 cursor 参数。

    default
    参数的默认值。如果定义了 default 值,则无需指定此参数的值即可执行过程。默认值必须是常量或 NULL。如果过程使用带 LIKE 关键字的参数,则可包含下列通配符:%、_、[] 和 [^]。

    注意: 
    只有 CLR 过程的默认值记录在 sys.parameters.default 列中。对于 Transact-SQL 过程参数,该列将为 NULL。
     


    OUTPUT
    指示参数是输出参数。此选项的值可以返回给调用 EXECUTE 的语句。使用 OUTPUT 参数将值返回给过程的调用方。除非是 CLR 过程,否则 text、ntext 和 image 参数不能用作 OUTPUT 参数。使用 OUTPUT 关键字的输出参数可以为游标占位符,CLR 过程除外。

    RECOMPILE
    指示数据库引擎 不缓存该过程的计划,该过程在运行时编译。如果指定了 FOR REPLICATION,则不能使用此选项。对于 CLR 存储过程,不能指定 RECOMPILE。

    若要指示数据库引擎 放弃存储过程内单个查询的计划,请使用 RECOMPILE 查询提示。有关详细信息,请参阅查询提示 (Transact-SQL)。如果非典型值或临时值仅用于属于存储过程的查询子集,则使用 RECOMPILE 查询提示。

    ENCRYPTION
    指示 SQL Server 将 CREATE PROCEDURE 语句的原始文本转换为模糊格式。模糊代码的输出在 SQL Server 2005 的任何目录视图中都不能直接显示。对系统表或数据库文件没有访问权限的用户不能检索模糊文本。但是,可通过 DAC 端口访问系统表的特权用户或直接访问数据库文件的特权用户可使用此文本。此外,能够向服务器进程附加调试器的用户可在运行时从内存中检索已解密的过程。

    该选项对于 CLR 存储过程无效。

    注意: 
    使用此选项创建的过程不能在 SQL Server 复制过程中发布。
     


    EXECUTE AS
    指定在其中执行存储过程的安全上下文。

    有关详细信息,请参阅 EXECUTE AS 子句 (Transact-SQL)。

    FOR REPLICATION
    指定不能在订阅服务器上执行为复制创建的存储过程。使用 FOR REPLICATION 选项创建的存储过程可用作存储过程筛选器,且只能在复制过程中执行。如果指定了 FOR REPLICATION,则无法声明参数。对于 CLR 存储过程,不能指定 FOR REPLICATION。对于使用 FOR REPLICATION 创建的过程,忽略 RECOMPILE 选项。

    FOR REPLICATION 过程将在 sys.objects 和 sys.procedures 中包含 RF 对象类型。

    <sql_statement>
    要包含在过程中的一个或多个 Transact-SQL 语句。有关某些适用的限制的信息,请参阅“备注”部分。

    EXTERNAL NAME , assembly_name.class_name.method_name
    指定 .NET Framework 程序集的方法,以便 CLR 存储过程引用。class_name 必须为有效的 SQL Server 标识符,并且该类必须存在于程序集中。如果类包含一个使用句点 (.) 分隔命名空间各部分的限定命名空间的名称,则必须使用方括号 ([ ]) 或引号 (" ") 将类名称分隔开。指定的方法必须为该类的静态方法。

    注意: 
    默认情况下,SQL Server 不能执行 CLR 代码。可以创建、修改和删除引用公共语言运行时模块的数据库对象;不过,只有在启用了 clr enabled 选项之后,才能在 SQL Server 中执行这些引用。若要启用该选项,请使用 sp_configure。
     


    权限
    需要在数据库中有 CREATE PROCEDURE 权限,对在其中创建过程的架构有 ALTER 权限。

    对于 CLR 存储过程,需要对 <method_specifier> 中引用的程序集的所有权,或拥有对该程序集的 REFERENCES 权限。

    示例
    A. 使用简单过程
    以下存储过程将从视图中返回所有雇员(提供姓和名)、职务以及部门名称。此存储过程不使用任何参数。
    USE AdventureWorks;
    GO
    IF OBJECT_ID ( ''HumanResources.usp_GetAllEmployees'', ''P'' ) IS NOT NULL
        DROP PROCEDURE HumanResources.usp_GetAllEmployees;
    GO
    CREATE PROCEDURE HumanResources.usp_GetAllEmployees
    AS
        SELECT LastName, FirstName, JobTitle, Department
        FROM HumanResources.vEmployeeDepartment;
    GO

     

    usp_GetEmployees 存储过程可通过以下方式执行:

    EXECUTE HumanResources.usp_GetAllEmployees;
    GO
    -- Or
    EXEC HumanResources.usp_GetAllEmployees;
    GO
    -- Or, if this procedure is the first statement within a batch:
    HumanResources.usp_GetAllEmployees;

     

    B. 使用带有参数的简单过程
    下面的存储过程只从视图中返回指定的雇员(提供名和姓)及其职务和部门名称。此存储过程接受与传递的参数精确匹配的值

    USE AdventureWorks;
    GO
    IF OBJECT_ID ( ''HumanResources.usp_GetEmployees'', ''P'' ) IS NOT NULL
        DROP PROCEDURE HumanResources.usp_GetEmployees;
    GO
    CREATE PROCEDURE HumanResources.usp_GetEmployees
        @lastname varchar(40),
        @firstname varchar(20)
    AS
        SELECT LastName, FirstName, JobTitle, Department
        FROM HumanResources.vEmployeeDepartment
        WHERE FirstName = @firstname AND LastName = @lastname;
    GO

     

    usp_GetEmployees 存储过程可通过以下方式执行:

     复制代码
    EXECUTE HumanResources.usp_GetEmployees ''Ackerman'', ''Pilar'';
    -- Or
    EXEC HumanResources.usp_GetEmployees @lastname = ''Ackerman'', @firstname = ''Pilar'';
    GO
    -- Or
    EXECUTE HumanResources.usp_GetEmployees @firstname = ''Pilar'', @lastname = ''Ackerman'';
    GO
    -- Or, if this procedure is the first statement within a batch:
    HumanResources.usp_GetEmployees ''Ackerman'', ''Pilar'';

     

    C. 使用带有通配符参数的简单过程
    以下存储过程只从视图中返回指定的一些雇员(提供名和姓)及其职务和部门名称。此存储过程模式与所传递的参数相匹配;或者,如果未提供参数,则使用预设的默认值(以字母 D 打头的姓)。
     
    USE AdventureWorks;
    GO
    IF OBJECT_ID ( ''HumanResources.usp_GetEmployees2'', ''P'' ) IS NOT NULL
        DROP PROCEDURE HumanResources.usp_GetEmployees2;
    GO
    CREATE PROCEDURE HumanResources.usp_GetEmployees2
        @lastname varchar(40) = ''D%'',
        @firstname varchar(20) = ''%''
    AS
        SELECT LastName, FirstName, JobTitle, Department
        FROM HumanResources.vEmployeeDepartment
        WHERE FirstName LIKE @firstname
            AND LastName LIKE @lastname;
    GO

     

    usp_GetEmployees2 存储过程可以按多种组合执行。下面只显示了几个组合:


    EXECUTE HumanResources.usp_GetEmployees2;
    -- Or
    EXECUTE HumanResources.usp_GetEmployees2 ''Wi%'';
    -- Or
    EXECUTE HumanResources.usp_GetEmployees2 @firstname = ''%'';
    -- Or
    EXECUTE HumanResources.usp_GetEmployees2 ''[CK]ars[OE]n'';
    -- Or
    EXECUTE HumanResources.usp_GetEmployees2 ''Hesse'', ''Stefen'';
    -- Or
    EXECUTE HumanResources.usp_GetEmployees2 ''H%'', ''S%'';

     

    D. 使用 OUTPUT 参数
    以下示例将创建 usp_GetList 存储过程。此过程将返回价格不超过指定数值的产品的列表。此示例显示如何使用多个 SELECT 语句和多个 OUTPUT 参数。OUTPUT 参数允许外部过程、批处理或多条 Transact-SQL 语句在过程执行期间访问设置的某个值。


    USE AdventureWorks;
    GO
    IF OBJECT_ID ( ''Production.usp_GetList'', ''P'' ) IS NOT NULL
        DROP PROCEDURE Production.usp_GetList;
    GO
    CREATE PROCEDURE Production.usp_GetList @product varchar(40)
        , @maxprice money
        , @compareprice money OUTPUT
        , @listprice money OUT
    AS
        SELECT p.name AS Product, p.ListPrice AS ''List Price''
        FROM Production.Product p
        JOIN Production.ProductSubcategory s
          ON p.ProductSubcategoryID = s.ProductSubcategoryID
        WHERE s.name LIKE @product AND p.ListPrice < @maxprice;
    -- Populate the output variable @listprice.
    SET @listprice = (SELECT MAX(p.ListPrice)
            FROM Production.Product p
            JOIN  Production.ProductSubcategory s
              ON p.ProductSubcategoryID = s.ProductSubcategoryID
            WHERE s.name LIKE @product AND p.ListPrice < @maxprice);
    -- Populate the output variable @compareprice.
    SET @compareprice = @maxprice;
    GO

     

    执行 usp_GetList,返回价格低于 $700 的 Adventure Works 产品(自行车)的列表。OUTPUT 参数 @cost 和 @compareprices 用于流控制语言,以便在“消息”窗口中返回消息。

    注意: 
    OUTPUT 变量必须在创建过程时或使用变量时定义。参数名和变量名不一定要匹配;但是,除非使用 @listprice = variable 的形式,否则数据类型和参数位置必须匹配。
     
    DECLARE @compareprice money, @cost money
    EXECUTE Production.usp_GetList ''%Bikes%'', 700,
        @compareprice OUT,
        @cost OUTPUT
    IF @cost <= @compareprice
    BEGIN
        PRINT ''These products can be purchased for less than
        $''+RTRIM(CAST(@compareprice AS varchar(20)))+''.''
    END
    ELSE
        PRINT ''The prices for all products in this category exceed
        $''+ RTRIM(CAST(@compareprice AS varchar(20)))+''.''

     

    下面是部分结果集:
    Product                                            List Price
    -------------------------------------------------- ------------------
    Road-750 Black, 58                      &nbs

    p;          539.99
    Mountain-500 Silver, 40                            564.99
    Mountain-500 Silver, 42                            564.99
    ...
    Road-750 Black, 48                                 539.99
    Road-750 Black, 52                                 539.99

    (14 row(s) affected)

    These items can be purchased for less than $700.00.
     

    E. 使用 WITH RECOMPILE 选项
    如果为过程提供的参数不是典型的参数,并且新的执行计划不应被缓存或存储在内存中,则 WITH RECOMPILE 子句会很有用。
     
    USE AdventureWorks;
    GO
    IF OBJECT_ID ( ''dbo.usp_product_by_vendor'', ''P'' ) IS NOT NULL
        DROP PROCEDURE dbo.usp_product_by_vendor;
    GO
    CREATE PROCEDURE dbo.usp_product_by_vendor @name varchar(30) = ''%''
    WITH RECOMPILE
    AS
        SELECT v.Name AS ''Vendor name'', p.Name AS ''Product name''
        FROM Purchasing.Vendor v
        JOIN Purchasing.ProductVendor pv
          ON v.VendorID = pv.VendorID
        JOIN Production.Product p
          ON pv.ProductID = p.ProductID
        WHERE v.Name LIKE @name;
    GO

     

    F. 使用 WITH ENCRYPTION 选项
    以下示例将创建 HumanResources.usp_encrypt_this 存储过程。


    USE AdventureWorks;
    GO
    IF OBJECT_ID ( ''HumanResources.usp_encrypt_this'', ''P'' ) IS NOT NULL
        DROP PROCEDURE HumanResources.usp_encrypt_this;
    GO
    CREATE PROCEDURE HumanResources.usp_encrypt_this
    WITH ENCRYPTION
    AS
        SELECT EmployeeID, Title, NationalIDNumber, VacationHours, SickLeaveHours
        FROM HumanResources.Employee;
    GO

     

    如以下示例所示,WITH ENCRYPTION 选项可阻止返回存储过程的定义。

    运行 sp_helptext:


    EXEC sp_helptext ''HumanResources.usp_encrypt_this'';
     

    下面是结果集:


    The text for object ''HumanResources.usp_encrypt_this'' is encrypted.
     

    直接查询 sys.sql_modules 目录视图:

     USE AdventureWorks;
    GO
    SELECT definition FROM sys.sql_modules
    WHERE object_id = OBJECT_ID(''HumanResources.usp_encrypt_this'');
     

    下面是结果集:


    definition
    ----------------------
    NULL

    (1 row(s) affected)
     

    G. 使用延迟名称解析
    以下示例将创建 usp_proc1 过程。该过程使用延迟名称解析。尽管引用的表在编译时不存在,但仍能创建存储过程。但是,执行过程时表必须存在。


    USE AdventureWorks;
    GO
    IF OBJECT_ID ( ''dbo.usp_proc1'', ''P'' ) IS NOT NULL
        DROP PROCEDURE dbo.usp_proc1;
    GO
    CREATE PROCEDURE dbo.usp_proc1
    AS
        SELECT column1, column2 FROM table_does_not_exist
    GO

     

    若要验证是否已创建了存储过程,请运行以下查询:

     
    USE AdventureWorks;
    GO
    SELECT definition
    FROM sys.sql_modules
    WHERE object_id = OBJECT_ID(''dbo.usp_proc1'');

     

    下面是结果集:
    definition
    -----------------------------------------------------------------------
    CREATE PROCEDURE usp_proc1
    AS
        SELECT column1, column2 FROM table_does_not_exist

    (1 row(s) affected)

     

    H. 使用 EXECUTE AS 子句
    以下示例显示使用 EXECUTE AS 子句指定执行存储过程的安全上下文。在此示例中,选项 CALLER 指定此过程可在调用它的用户上下文中执行。

     USE AdventureWorks;
    GO
    IF OBJECT_ID ( ''Purchasing.usp_vendor_info_all'', ''P'' ) IS NOT NULL
        DROP PROCEDURE Purchasing.usp_vendor_info_all;
    GO
    CREATE PROCEDURE Purchasing.usp_vendor_info_all
    WITH EXECUTE AS CALLER
    AS
        SELECT v.Name AS Vendor, p.Name AS ''Product name'',
          v.CreditRating AS ''Credit Rating'',
          v.ActiveFlag AS Availability
        FROM Purchasing.Vendor v
        INNER JOIN Purchasing.ProductVendor pv
          ON v.VendorID = pv.VendorID
        INNER JOIN Production.Product p
          ON pv.ProductID = p.ProductID
        ORDER BY v.Name ASC;
    GO

     

    I. 创建 CLR 存储过程
    以下示例将创建 GetPhotoFromDB 存储过程,此过程引用 HandlingLOBUsingCLR 程序集中的 LargeObjectBinary 类的 GetPhotoFromDB 方法。创建存储过程前,需要在本地数据库中注册 HandlingLOBUsingCLR 程序集。

     
    CREATE ASSEMBLY HandlingLOBUsingCLR
    FROM ''\\MachineName\HandlingLOBUsingCLR\bin\Debug\HandlingLOBUsingCLR.dll'''';
    GO
    CREATE PROCEDURE dbo.GetPhotoFromDB
    (
        @ProductPhotoID int,
        @CurrentDirectory nvarchar(1024),
        @FileName nvarchar(1024)
    )
    AS EXTERNAL NAME HandlingLOBUsingCLR.LargeObjectBinary.GetPhotoFromDB;
    GO
     

    J. 使用 OUTPUT 游标参数
    OUTPUT 游标参数用来将存储过程的局部游标传递回执行调用的批处理、存储过程或触发器。

    首先,创建以下过程:在 titles 表上声明并打开一个游标:

    USE AdventureWorks;
    GO
    IF OBJECT_ID ( ''dbo.currency_cursor'', ''P'' ) IS NOT NULL
        DROP PROCEDURE dbo.currency_cursor;
    GO
    CREATE PROCEDURE dbo.currency_cursor
        @currency_cursor CURSOR VARYING OUTPUT
    AS
        SET @currency_cursor = CURSOR
        FORWARD_ONLY STATIC FOR
          SELECT CurrencyCode, Name
          FROM Sales.Currency;
        OPEN @currency_cursor;
    GO

     

    接下来,运行以下批处理:声明一个局部游标变量,执行上述过程以将游标赋值给局部变量,然后从该游标提取行。

     
    USE AdventureWorks;
    GO
    DECLARE @MyCursor CURSOR;
    EXEC dbo.currency_cursor @currency_cursor = @MyCursor OUTPUT;
    WHILE (@@FETCH_STATUS = 0)
    BEGIN;
         FETCH NEXT FROM @MyCursor;
    END;
    CLOSE @MyCursor;
    DEALLOCATE @MyCursor;
    GO

  • 索引

    2009-12-12 15:51:57

    索引用来快速地寻找那些具有特定值的记录,所有MySQL索引都以B-树的形式保存。如果没有索引,执行查询时MySQL必须从第一个记录开始扫描整个表的所有记录,直至找到符合要求的记录。表里面的记录数量越多,这个操作的代价就越高。如果作为搜索条件的列上已经创建了索引,MySQL无需扫描任何记录即可迅速得到目标记录所在的位置。如果表有1000个记录,通过索引查找记录至少要比顺序扫描记录快100倍。

    索引是对数据库表中一个或多个列(例如,employee表的姓氏(lname)列)的值进行排序的结构。如果想按特定职员的姓来查找他或她,则与在表中搜索所有的行相比,索引有助于更快地获取信息。

    索引提供指针以指向存储在表中指定列的数据值,然后根据指定的排序次序排列这些指针。数据库使用索引的方式与使用书的目录很相似:通过搜索索引找到特定的值,然后跟随指针到达包含该值的行。

    在数据库关系图中,可以为选定的表创建、编辑或删除索引/键属性页中的每个索引类型。当保存附加在此索引上的表或包含此表的数据库关系图时,索引同时被保存。有关详细信息,请参见创建索引。

    通常情况下,只有当经常查询索引列中的数据时,才需要在表上创建索引。索引将占用磁盘空间,并且降低添加、删除和更新行的速度。不过在多数情况下,索引所带来的数据检索速度的优势大大超过它的不足之处。然而,如果应用程序非常频繁地更新数据,或磁盘空间有限,那么最好限制索引的数量。

    索引类型

    根据数据库的功能,可在数据库设计器中创建三种类型的索引——唯一索引、主键索引和聚集索引。

    提示:尽管唯一索引有助于找到信息,但为了获得最佳性能,建议使用主键约束或唯一约束。

    唯一索引:唯一索引不允许两行具有相同的索引值。

    主键索引:数据库表通常有一列或列组合,其值用来唯一标识表中的每一行。该列称为表的主键。

    在数据库关系图中为表定义一个主键将自动创建主键索引,主键索引是唯一索引的特殊类型。主键索引要求主键中的每个值是唯一的。

    聚集索引:聚集索引中,表中各行的物理顺序与键值的逻辑(索引)顺序相同。表只能包含一个聚集索引。

    如果不是聚集索引,表中各行的物理顺序与键值的逻辑顺序不匹配。聚集索引比非聚集索引有更快的数据访问速度

    在Microsoft?SQLServer?数据库中可以创建聚集索引。在聚集索引中,表中各行的物理顺序与索引键值的逻辑(索引)顺序相同。表只能包含一个聚集索引。聚集索引通常可加快UPDATE和DELETE操作的速度,因为这两个操作需要读取大量的数据。创建或修改聚集索引可能要花很长时间,因为执行这两个操作时要在磁盘上对表的行进行重组。

    可考虑将聚集索引用于:

    1:包含数量有限的唯一值的列,如state列只包含50个唯一的州代码。

    2:使用下列运算符返回一个范围值的查询:BETWEEN、>、>=、<和<=。

    3:返回大结果集的查询。(摘自Microsoft?SQLServer?帮助)

    聚集索引对于那些经常要搜索范围值的列特别有效。使用聚集索引找到包含第一个值的行后,便可以确保包含后续索引值的行在物理相邻。例如,如果应用程序执行的一个查询经常检索某一日期范围内的记录,则使用聚集索引可以迅速找到包含开始日期的行,然后检索表中所有相邻的行,直到到达结束日期。这样有助于提高此类查询的性能。同样,如果对从表中检索的数据进行排序时经常要用到某一列,则可以将该表在该列上聚集(物理排序),避免每次查询该列时都进行排序,从而节省成本。

    当索引值唯一时,使用聚集索引查找特定的行也很有效率。例如,使用唯一雇员ID列emp_id查找特定雇员的最快速的方法,是在emp_id列上创建聚集索引或PRIMARYKEY约束。

    在创建聚集索引之前,应先了解您的数据是如何被访问的。可考虑将聚集索引用于:

    1包含大量非重复值的列。

    2使用下列运算符返回一个范围值的查询:BETWEEN、>、>=、<和<=。

    3被连续访问的列。

    4返回大型结果集的查询。

    5经常被使用联接或GROUPBY子句的查询访问的列;一般来说,这些是外键列。对ORDERBY或GROUPBY子句中指定的列进行索引,可以使SQLServer不必对数据进行排序,因为这些行已经排序。这样可以提高查询性能。

    6OLTP类型的应用程序,这些程序要求进行非常快速的单行查找(一般通过主键)。应在主键上创建聚集索引。

    聚集索引不适用于:

    1频繁更改的列这将导致整行移动(因为SQLServer必须按物理顺序保留行中的数据值)。这一点要特别注意,因为在大数据量事务处理系统中数据是易失的。

    2宽键来自聚集索引的键值由所有非聚集索引作为查找键使用,因此存储在每个非聚集索引的叶条目内。

    说明:如果该表上尚未创建聚集索引,且在创建PRIMARYKEY约束时未指定非聚集索引,PRIMARYKEY约束会自动创建聚集索引。

    注意事项:定义聚集索引键时使用的列越少越好,这一点很重要。如果定义了一个大型的聚集索引键,则同一个表上定义的任何非聚集索引都将增大许多,因为非聚集索引条目包含聚集键。当把SQL脚本保存到可用空间不足的磁盘上时,索引优化向导不返回错误。

    索引的缺点

    到目前为止,我们讨论的都是索引的优点。事实上,索引也是有缺点的。

    首先,索引要占用磁盘空间。通常情况下,这个问题不是很突出。但是,如果你创建每一种可能列组合的索引,索引文件体积的增长速度将远远超过数据文件。如果你有一个很大的表,索引文件的大小可能达到操作系统允许的最大文件限制。

    第二,对于需要写入数据的操作,比如DELETE、UPDATE以及INSERT操作,索引会降低它们的速度。这是因为MySQL不仅要把改动数据写入数据文件,而且它还要把这些改动写入索引文件。

    在大型数据库中,索引是提高速度的一个关键因素。不管表的结构是多么简单,一次500000行的表扫描操作无论如何不会快。如果你的网站上也有这种大规模的表,那么你确实应该花些时间去分析可以采用哪些索引,并考虑是否可以改写查询以优化应用。要了解更多信息,请参见MySQLmanual。另外注意,本文假定你所使用的MySQL是3.23版,部分查询不能在3.22版MySQL上执行。

    转自于:什么时候才要建索引,什么时候不要建索呢?

    以下情况不要建索引:

    • 1、如果每次都需要取到所有表记录,无论如何都必须进行全表扫描了,那么是否加索引也没有意义了。
    • 2、对非唯一的字段,例如“性别”这种大量重复值的字段,增加索引也没有什么意义。
    • 3、对于记录比较少的表,增加索引不会带来速度的优化反而浪费了存储空间,因为索引是需要存储空间的,而且有个致命缺点是对于update/insert/delete的每次执行,字段的索引都必须重新计算更新

    以下情况要建立索引


        SELECT c.companyID, c.companyName FROM Companies c, User u WHERE c.companyID = u.fk_companyID AND c.numEmployees >= 0 AND c.companyName LIKE '%i%' AND u.groupID IN (SELECT g.groupID FROM Groups g WHERE g.groupLabel = 'Executive')

        这条语句涉及3个表的联接,并且包括了许多搜索条件比如大小比较,Like匹配等。在没有索引的情况下Mysql需要执行的扫描行数是77721876行。而我们通过在companyID和groupLabel两个字段上加上索引之后,扫描的行数只需要134行。在Mysql中可以通过Explain Select来查看扫描次数。可以看出来在这种联表和复杂搜索条件的情况下,索引带来的性能提升远比它所占据的磁盘空间要重要得多。

Open Toolbar