Oracle中的功能与过程 [英] Functions vs procedures in Oracle

问题描述

有人可以解释Oracle中函数和过程之间的主要区别是什么吗?如果我可以使用函数进行所有操作，为什么必须使用过程?

can anybody explain what is the main difference between functions and procedures in Oracle? Why must I use procedures if I can do everything with functions?

1. 如果我不能在sql语句中调用过程，好吧，我将编写一个函数来完成相同的工作.
2. 过程不返回值，好的，在执行任何dml操作后，我只会返回sql％rowcount或1(成功)，0(异常)
3. 过程和函数都可以通过OUT/IN OUT参数将变量传递给调用环境

我听说主要区别在于性能，过程比功能更快".但没有任何细节.

I heard that the main difference is in performance, 'procedures are faster than functions'. But without any detail.

谢谢.

推荐答案

区别在于-默认情况下，函数必须返回一个值(任何类型)，而在过程中，则需要使用诸如OUT或IN OUT参数以获取结果.您可以在普通的SQL中使用函数，因为您不能在SQL语句中使用过程.

The difference is- A function must return a value (of any type) by default definition of it, whereas in case of a procedure you need to use parameters like OUT or IN OUT parameters to get the results. You can use a function in a normal SQL where as you cannot use a procedure in SQL statements.

函数和过程之间的某些差异

Some Differences between Functions and Procedures

1. 函数始终使用return语句返回值，而过程可能通过参数返回一个或多个值，或者根本不返回.尽管OUT参数仍然可以在函数中使用，但不能使用最好不要在某些情况下可能需要这样做.使用OUT参数限制在SQL语句中使用函数.

1. A function always returns a value using the return statement while a procedure may return one or more values through parameters or may not return at all.Although, OUT parameters can still be used in functions, they are not advisable neither are there cases where one might find a need to do so. Using OUT parameter restricts a function from being used in a SQL Statement.

函数可以在典型的SQL语句中使用，例如SELECT，INSERT，UPDATE，DELETE，MERGE，而过程则不能.

Functions can be used in typical SQL statements like SELECT, INSERT, UPDATE, DELETE, MERGE, while procedures can't.

函数通常用于计算，而过程通常用于执行业务逻辑.

Functions are normally used for computations where as procedures are normally used for executing business logic.

Oracle提供了创建"基于函数的索引"的规定.提高后续SQL语句的性能.在查询的where子句中的索引列上执行该功能时适用.

Oracle provides the provision of creating "Function Based Indexes" to improve the performance of the subsequent SQL statement. This applies when performing the function on an indexed column in where clause of a query.

有关功能的更多信息.过程此处和

More Information on Functions Vs. Procedures here and here.

这篇关于Oracle中的功能与过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！