首页 文章

SQL Server中的函数与存储过程

提问于
浏览
705

我一直在学习函数和存储过程很长一段时间,但我不知道为什么以及何时应该使用函数或存储过程 . 他们看起来和我一样,也许是因为我对此有点新手 .

有人可以告诉我为什么吗?

17 回答

  • 19

    用户定义函数是sql server程序员可用的重要工具 . 您可以在SQL语句中内联使用它

    SELECT a, lookupValue(b), c FROM customers
    

    其中 lookupValue 将是UDF . 使用存储过程时无法使用此类功能 . 同时你不能在UDF中做某些事情 . 这里要记住的基本事情是UDF:

    • 无法创建永久性更改

    • 无法更改数据

    存储过程可以做那些事情 .

    对我来说,UDF的内联使用是UDF最重要的用法 .

  • 55

    当您想要计算并返回一个值以便在其他SQL语句中使用时,编写用户定义的函数;在需要时编写存储过程,而不是将可能复杂的SQL语句集合分组 . 毕竟,这是两个完全不同的用例!

  • 3

    存储过程和用户定义函数之间的差异:

    • 存储过程不能在Select语句中使用 .

    • 存储过程支持延迟名称解析 .

    • 存储过程通常用于执行业务逻辑 .

    • 存储过程可以返回任何数据类型 .

    • 存储过程可以接受比用户定义的函数更多的输入参数 . 存储过程最多可包含21,000个输入参数 .

    • 存储过程可以执行动态SQL .

    • 存储过程支持错误处理 .

    • 非确定性函数可用于存储过程 .


    • 用户定义的函数可以在Select语句中使用 .

    • 用户定义的函数不支持延迟名称解析 .

    • 用户定义的函数通常用于计算 .

    • 用户定义的函数应返回一个值 .

    • 用户定义的函数无法返回图像 .

    • 用户定义的函数接受的输入参数数量少于存储过程 . UDF最多可包含1,023个输入参数 .

    • 临时表不能用于用户定义的函数 .

    • 用户定义的函数无法执行动态SQL .

    • 用户定义的函数不支持错误处理 . UDF中不允许 RAISEERROR@@ERROR .

    • 非确定性函数不能在UDF中使用 . 例如, GETDATE() 不能在UDF中使用 .

  • 1
    • 函数可以在select语句中使用,而过程则不能 .

    • 存储过程同时接受输入和输出参数,但函数仅接受输入参数 .

    • 函数不能返回text,ntext,image和timestamps类型的值,如程序所能 .

    • 函数可以在create table中用作用户定义的数据类型,但是过程不能 .

    ***例如:-create table <tablename>(name varchar(10),salary getsal(name))

    这里的getsal是一个用户定义的函数,它返回一个工资类型,当创建表时,没有为工资类型分配存储,并且也没有执行getsal函数,但是当我们从这个表中获取一些值时,获取执行的getsal函数和返回Type作为结果集返回 .

  • 13

    函数和存储过程用于单独的目的 . 虽然它不是最好的类比,但函数可以像任何其他编程语言中使用的函数一样被视为字体,但存储过程更像是单个程序或批处理脚本 .

    功能通常具有输出和可选输入 . 然后,输出可以用作另一个函数(内置的SQL Server,如DATEDIFF,LEN等)的输入,或者作为SQL查询的谓词 - 例如 SELECT a, b, dbo.MyFunction(c) FROM tableSELECT a, b, c FROM table WHERE a = dbo.MyFunc(c) .

    存储过程用于在事务中将SQL查询绑定在一起,并与外部世界进行交互 . ADO.NET等框架不能直接调用函数,但可以直接调用存储过程 .

    函数确实存在隐藏的危险:它们可能被滥用并导致相当令人讨厌的性能问题:请考虑以下查询:

    SELECT * FROM dbo.MyTable WHERE col1 = dbo.MyFunction(col2)
    

    MyFunction声明为:

    CREATE FUNCTION MyFunction (@someValue INTEGER) RETURNS INTEGER
    AS
    BEGIN
       DECLARE @retval INTEGER
    
       SELECT localValue 
          FROM dbo.localToNationalMapTable
          WHERE nationalValue = @someValue
    
       RETURN @retval
    END
    

    这里发生的是为MyTable表中的每一行调用函数MyFunction . 如果MyTable有1000行,那么这是针对数据库的另外1000个临时查询 . 类似地,如果在列规范中指定函数时调用该函数,则将为SELECT返回的每一行调用该函数 .

    所以你需要小心编写函数 . 如果从函数中的表中执行SELECT,则需要问自己,在父存储过程或其他一些SQL构造中使用JOIN是否可以更好地执行SELECT(例如CASE ... WHEN ... ELSE ...结束) .

  • 21

    决定何时使用以下各点可能有所帮助 -

    • 存储过程无法返回表变量,而函数可以这样做 .

    • 您可以使用存储过程来更改服务器环境参数,而使用不能使用的函数 .

    干杯

  • 2

    SP和UDF之间的区别如下:

    +---------------------------------+----------------------------------------+
    | Stored Procedure (SP)           | Function (UDF - User Defined           |
    |                                 | Function)                              |
    +---------------------------------+----------------------------------------+
    | SP can return zero , single or  | Function must return a single value    |
    | multiple values.                | (which may be a scalar or a table).    |
    +---------------------------------+----------------------------------------+
    | We can use transaction in SP.   | We can't use transaction in UDF.       |
    +---------------------------------+----------------------------------------+
    | SP can have input/output        | Only input parameter.                  |
    | parameter.                      |                                        |
    +---------------------------------+----------------------------------------+
    | We can call function from SP.   | We can't call SP from function.        |
    +---------------------------------+----------------------------------------+
    | We can't use SP in SELECT/      | We can use UDF in SELECT/ WHERE/       |
    | WHERE/ HAVING statement.        | HAVING statement.                      |
    +---------------------------------+----------------------------------------+
    | We can use exception handling   | We can't use Try-Catch block in UDF.   |
    | using Try-Catch block in SP.    |                                        |
    +---------------------------------+----------------------------------------+
    
  • 2

    Stored Procedures 用作脚本 . 它们为您运行一系列命令,您可以安排它们运行倍 .

    Functions 用作方法 . 你传递了一些东西,它返回一个结果 . 应该小而快 - 它是否在飞行中 .

  • 6
    • 函数必须返回值,而不是存储过程 .

    • 仅在UDF中接受选择语句,而不需要DML语句 .

    • 存储过程接受任何语句以及DML语句 .

    • UDF仅允许输入而不允许输出 .

    • 存储过程允许输入和输出 .

    • Catch块不能在UDF中使用,但可以在存储过程中使用 .

    • UDF中的函数不允许任何事务,但在存储过程中允许它们 .

    • 只能在UDF中使用表变量,而不能在临时表中使用 .

    • 存储过程允许表变量和临时表 .

    • UDF不允许从函数调用存储过程,而存储过程允许调用函数 .

    • UDF用于join子句,而存储过程不能用于join子句 .

    • 存储过程将始终允许返回零 . 相反,UDF具有必须回到预定点的值 .

  • 595

    Basic Difference

    函数必须返回一个值,但在存储过程中它是可选的(过程可以返回零或n个值) .

    函数只能有输入参数,而程序可以有输入/输出参数 .

    函数需要一个输入参数,它是必需的,但存储过程可能需要o到n个输入参数 .

    可以从Procedure调用函数,但不能从Function调用过程 .

    Advance Difference

    Procedure允许SELECT和DML(INSERT / UPDATE / DELETE)语句,而Function只允许SELECT语句 .

    程序不能在SELECT语句中使用,而Function可以嵌入SELECT语句中 .

    存储过程不能在WHERE / HAVING / SELECT部分的任何地方的SQL语句中使用,而Function可以 .

    返回表的函数可以视为另一个行集 . 这可以在JOIN中与其他表一起使用 .

    内联函数可以作为带参数的视图,可以在JOIN和其他Rowset操作中使用 .

    异常可以通过过程中的try-catch块来处理,而try-catch块不能在Function中使用 .

    我们可以在程序中进行事务管理,而我们不能进入功能 .

    source

  • 7

    在SQL Server中,函数和存储过程是两种不同类型的实体 .

    Function: 在SQL Server数据库中,这些函数用于执行某些操作,操作会立即返回结果 . 功能有两种:

    • System defined

    • User defined

    Stored Procedures: 在SQL Server中,存储过程存储在服务器中,它可以返回零,单个和多个值 . 存储过程有两种类型:

    • System Stored Procedures

    • User Defined Procedures

  • 54

    从返回单个值的函数开始 . 好处是你可以将经常使用的代码放入函数中,并将它们作为结果集中的列返回 .

    然后,您可以将函数用于参数化的城市列表 . dbo.GetCitiesIn(“NY”)返回一个可用作连接的表 .

    这是一种组织代码的方式 . 了解何时可重复使用以及何时浪费时间只能通过反复试验和经验来获得 .

    此外,函数在SQL Server中是个好主意 . 它们更快,可以非常强大 . 内联和直接选择 . 小心不要过度使用 .

  • -2

    这是一个比存储过程更喜欢函数的实际原因 . 如果您的存储过程需要另一个存储过程的结果,则必须使用insert-exec语句 . 这意味着您必须创建临时表并使用 exec 语句将存储过程的结果插入临时表 . 这很麻烦 . 这个问题的一个问题是insert-execs cannot be nested .

    如果您遇到调用其他存储过程的存储过程,则可能会遇到此问题 . 如果嵌套存储过程只返回一个数据集,则可以用表值函数替换它,并且您将不再收到此错误 .

    (这是我们应该将业务逻辑保留在数据库之外的另一个原因)

  • 182
    STORE PROCEDURE                 FUNCTION (USER DEFINED FUNCTION)    
     * Procedure can return 0, single or   | * Function can return only single value   
       multiple values.                    |
                                           |
     * Procedure can have input, output    | * Function  can have only input 
       parameters.                         |   parameters.         
                                           |
     * Procedure cannot be called from     | * Functions can be called from 
       function.                           |   procedure.
                                           |
     * Procedure allows select as well as  | * Function allows only select statement 
       DML statement in it.                |   in it.
                                           |
     * Exception can be handled by         | * Try-catch block cannot be used in a 
       try-catch block in a procedure.     |   function.
                                           |
     * We can go for transaction management| * We can't go for transaction 
       in procedure.                       |   management in function.
                                           |
     * Procedure cannot be utilized in a   | * Function can be embedded in a select 
       select statement                    |   statement.
                                           |
     * Procedure can affect the state      | * Function can not affect the state 
       of database means it can perform    |   of database means it can not    
       CRUD operation on database.         |   perform CRUD operation on 
                                           |   database. 
                                           |
     * Procedure can use temporary tables. | * Function can not use 
       temporary tables                    |   temporary tables. 
                                           |
     * Procedure can alter the server      | * Function can not alter the  
       environment parameters.             |   environment parameters.
                                           |   
     * Procedure can use when we want      | * Function can use when we want
       instead is to group a possibly-     |   to compute and return a value
       complex set of SQL statements.      |   for use in other SQL 
                                               statements.
    
  • 6

    存储过程:

    • 就像是SQL Server中的微型程序 .

    • 可以像select语句一样简单,也可以像添加,删除,更新和/或从数据库中的多个表中读取数据的长脚本一样复杂 .

    • (可以实现循环和游标,它们都允许您使用较小的结果或对数据进行逐行操作 . )

    • 应使用 EXECEXECUTE 语句调用 .

    • 返回表变量,但我们不能使用 OUT 参数 .

    • 支持交易 .

    功能:

    • 不能用于更新,删除或添加记录到数据库 .

    • 只需返回单个值或表值 .

    • 只能用于选择记录 . 但是,它可以在标准SQL中非常容易地调用,例如:

    SELECT dbo.functionname('Parameter1')
    

    要么

    SELECT Name, dbo.Functionname('Parameter1') FROM sysObjects
    
    • 对于简单的可重用选择操作,函数可以简化代码 . 只是要小心在函数中使用 JOIN 子句 . 如果您的函数具有 JOIN 子句并且您从另一个返回多个结果的select语句调用它,那么对于结果集中返回的每一行,该函数调用将 JOIN 这些表一起使用 . 因此,虽然它们可以帮助简化某些逻辑,但如果它们没有得到正确使用,它们也可能成为性能瓶颈 .

    • 使用 OUT 参数返回值 .

    • 不支持交易 .

  • 514

    函数是计算值,不能对SQL Server执行永久性环境更改(即不允许INSERT或UPDATE语句) .

    如果函数返回标量值,则可以在SQL语句中内联使用该函数;如果返回结果集,则可以将其连接在一起 .

    A point worth noting from comments, which summarize the answer. Thanks to @Sean K Anderson:

    函数遵循计算机技术定义,因为它们必须返回一个值并且不能改变数据他们收到参数(参数) . 函数不允许更改任何内容,必须至少有一个参数,并且它们必须返回一个值 . 存储过程不必具有参数,可以更改数据库对象,也不必返回值 .

  • 11

    像游标一样,SQL Server函数可以用作你的最后一个武器!它们确实存在性能问题,因此应尽可能避免使用表值函数 . 谈论性能是指在中型硬件上托管在服务器上的超过1,000,000条记录的表;否则你不必担心功能造成的性能损失 .

    • 从不使用函数将结果集返回到外部代码(如ADO.Net)

    • 尽可能使用views / stored procs组合 . 你可以使用DTA(数据库调优顾问)给你的建议(比如索引视图和统计数据)来恢复未来的成长 - 性能问题 - 有时候!

    有关进一步参考,请参阅:http://databases.aspfaq.com/database/should-i-use-a-view-a-stored-procedure-or-a-user-defined-function.html

相关问题