在SQL中什么时候应该使用函数而不是存储过程，反之亦然?每一个的目的是什么?
当前回答
存储过程与用户自定义函数的区别:
Stored procedures cannot be used in Select statements. Stored procedures support Deferred Name Resolution. Stored procedures are generally used for performing business logic. Stored procedures can return any datatype. Stored procedures can accept greater numbers of input parameter than user defined functions. Stored procedures can have up to 21,000 input parameters. Stored procedures can execute Dynamic SQL. Stored procedures support error handling. Non-deterministic functions can be used in stored procedures.
User-defined functions can be used in Select statements. User-defined functions do not support Deferred Name Resolution. User-defined functions are generally used for computations. User-defined functions should return a value. User-defined functions cannot return Images. User-defined functions accept smaller numbers of input parameters than stored procedures. UDFs can have up to 1,023 input parameters. Temporary tables cannot be used in user-defined functions. User-defined functions cannot execute Dynamic SQL. User-defined functions do not support error handling. RAISEERROR OR @@ERROR are not allowed in UDFs. Non-deterministic functions cannot be used in UDFs. For example, GETDATE() cannot be used in UDFs.
其他回答
用户定义函数是sql server程序员可用的重要工具。您可以像这样在SQL语句中内联使用它
SELECT a, lookupValue(b), c FROM customers
其中lookupValue将是UDF。这种功能在使用存储过程时是不可能实现的。同时，您不能在UDF中做某些事情。这里要记住的基本事情是UDF的:
不能产生永久的变化 无法更改数据
存储过程可以做这些事情。
对我来说，UDF的内联使用是UDF最重要的使用。
函数是计算值，不能对SQL Server执行永久的环境更改(即不允许INSERT或UPDATE语句)。
如果函数返回标量值，则可以在SQL语句中使用它;如果函数返回结果集，则可以连接它。
从总结答案的评论中，有一点值得注意。感谢@Sean K Anderson:
函数遵循计算机科学的定义，即它们必须返回一个值，并且不能更改作为参数接收的数据 (论点)。函数不允许改变任何东西，必须 至少有一个参数，并且它们必须返回一个值。存储 Procs不需要有参数，可以改变数据库对象， 并且不需要返回值。
| STORE PROCEDURE | FUNCTION (USER DEFINED FUNCTION) |
|---|---|
| Procedure can return 0, single or multiple values | Function can return only single value |
| Procedure can have input, output parameters | Function can have only input parameters |
| Procedure cannot be called from a function | Functions can be called from procedure |
| Procedure allows select as well as DML statement in it | Function allows only select statement in it |
| Exception can be handled by try-catch block in a procedure | Try-catch block cannot be used in a function |
| We can go for transaction management in procedure | We can not go for transaction management in function |
| Procedure cannot be utilized in a select statement | Function can be embedded in a select statement |
| Procedure can affect the state of database means it can perform CRUD operation on database | Function can not affect the state of database means it can not perform CRUD operation on database |
| Procedure can use temporary tables | Function can not use temporary tables |
| Procedure can alter the server environment parameters | Function can not alter the environment parameters |
| Procedure can use when we want instead is to group a possibly- complex set of SQL statements | Function can use when we want to compute and return a value for use in other SQL statements |
It is mandatory for Function to return a value while it is not for stored procedure. Select statements only accepted in UDF while DML statements not required. Stored procedure accepts any statements as well as DML statements. UDF only allows inputs and not outputs. Stored procedure allows for both inputs and outputs. Catch blocks cannot be used in UDF but can be used in stored procedure. No transactions allowed in functions in UDF but in stored procedure they are allowed. Only table variables can be used in UDF and not temporary tables. Stored procedure allows for both table variables and temporary tables. UDF does not allow stored procedures to be called from functions while stored procedures allow calling of functions. UDF is used in join clause while stored procedures cannot be used in join clause. Stored procedure will always allow for return to zero. UDF, on the contrary, has values that must come - back to a predetermined point.
下面是一个总结差异的表格:
| Stored Procedure | Function | |
|---|---|---|
| Returns | Zero or more values | A single value (which may be a scalar or a table) |
| Can use transaction? | Yes | No |
| Can output to parameters? | Yes | No |
| Can call each other? | Can call a function | Cannot call a stored procedure |
| Usable in SELECT, WHERE and HAVING statements? | No | Yes |
| Supports exception handling (via try/catch)? | Yes | No |
推荐文章
- 如何在Ruby On Rails中使用NuoDB手动执行SQL命令
- 查询JSON类型内的数组元素
- 确定记录是否存在的最快方法
- 获得PostgreSQL数据库中当前连接数的正确查询
- 在SQL选择语句Order By 1的目的是什么?
- 从现有模式生成表关系图(SQL Server)
- 我如何循环通过一组记录在SQL Server?
- 数据库和模式的区别
- 如何在SQL Server中一次更改多个列
- 如何从命令行通过mysql运行一个查询?
- 外键约束可能导致循环或多条级联路径?
- 使用LIMIT/OFFSET运行查询，还可以获得总行数
- 当恢复sql时，psql无效命令\N
- 货币应该使用哪种数据类型?
- 如何选择每一行的列值不是独特的
