存储过程和用户定义的函数之间的区别[英] Difference between stored procedures and user defined functions

本文是小编为大家收集整理的关于存储过程和用户定义的函数之间的区别的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

任何人都可以解释存储过程和用户​​定义的功能之间的确切差异,在哪个上下文中有用?

推荐答案

这是我一直牢记的:)

  • 过程可以返回零或n值,而函数可以返回一个强制性的值.
  • 过程可以具有输入/输出参数,而功能只能具有输入参数.
  • 过程允许选择以及其中的DML语句,而功能仅允许在其中选择语句.
  • 函数可以从过程中调用,而过程不能从函数中调用.
  • 可以在过程中通过try-catch块来处理异常,而try-catch块不能在函数中使用.
  • 我们可以在过程中进行交易管理,而我们不能发挥功能.
  • 无法在选择语句中使用过程,而函数可以嵌入Select语句中.
  • udf可以在sql语句中使用where/have/select部分中的任何位置.
  • 返回表的UDF可以将其视为另一个行集.这可以与其他表格一起使用.
  • 内联udf可以是采用参数的视图,可用于连接和其他rowset操作.

source 其他推荐答案

函数始终返回值,并且可以不是执行DML语句(插入/Update/Delete).

存储过程无法返回值 - 您需要使用OUT参数 - 可以运行DML语句.

使用函数与存储过程的优势?


除了上面的比较外,它们是相等的.但是考虑到比较,根据您需要做的事情,您可能会比您的功能更频繁地使用存储过程.

其他推荐答案

用户定义的功能几乎没有限制,例如DML统计CANBE使用等等,请检查

本文地址:https://www.itbaoku.cn/post/597444.html

问题描述

Can anyone explain what is the exact difference between stored procedures and user defined functions, and in which context each is useful?

推荐答案

This is what i always keep in mind :)

  • Procedure can return zero or n values whereas function can return one value which is mandatory.
  • Procedures can have input/output parameters for it whereas functions can have only input parameters.
  • Procedure allows select as well as DML statement in it whereas function allows only select statement in it.
  • Functions can be called from procedure whereas procedures cannot be called from function.
  • Exception can be handled by try-catch block in a procedure whereas try-catch block cannot be used in a function.
  • We can go for transaction management in procedure whereas we can't go in function.
  • Procedures can not be utilized in a select statement whereas function can be embedded in a select statement.
  • UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored procedures cannot be.
  • UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables.
  • Inline UDF's can be though of as views that take parameters and can be used in JOINs and other Rowset operations.

Source http://www.codeproject.com/Tips/286539/Difference-between-stored-procedure-and-function

其他推荐答案

A function always returns a value, and can not perform DML statements (INSERT/UPDATE/DELETE).

A stored procedure can not return a value - you need to use an OUT parameter - and can run DML statements.

Advantage of Using a Function vs a Stored Procedure?


Aside from the comparison above, they are equal. But given the comparison, depending on what you need to do it's likely you will use a stored procedure more often than you will a function.

其他推荐答案

User defined function has few limitiations like DML statments canbe used etc pls check