Oracle 中的函数与过程

Functions vs procedures in Oracle

提问by 0bj3ct

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?

谁能解释一下 Oracle 中函数和过程之间的主要区别是什么？如果我可以用函数做任何事情，为什么我必须使用过程？

1. If I cannot call procedure in sql statement, ok, I'll write a function to do the same work.
2. Procedures don't return values, ok, I'll return only sql%rowcount or 1(success), 0(exception) after any dml operation
3. Both procedures and functions can pass variables to calling environment via OUT/IN OUT parameters

1. 如果我不能在 sql 语句中调用过程，好吧，我会写一个函数来做同样的工作。
2. 程序不返回值，好吧，我将在任何 dml 操作后只返回 sql%rowcount 或 1(success), 0(exception)
3. 过程和函数都可以通过 OUT/IN OUT 参数将变量传递给调用环境

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

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

Thanks in advance.

提前致谢。

回答by Romo Daneghyan

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 OUTor IN OUTparameters to get the results. You can use a function in a normal SQLwhere as you cannot use a procedure in SQLstatements.

不同之处在于 - 函数必须在默认情况下返回一个值（任何类型），而在程序的情况下，您需要使用像OUT或IN OUT参数这样的参数来获取结果。您可以在正常情况SQL下使用函数，而不能在SQL语句中使用过程。

Some Differences between Functions and Procedures

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

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, OUTparameters can still be used in functions, they are not advisable neither are there cases where one might find a need to do so. Using OUTparameter restricts a function from being used in a SQL Statement.

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

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

4. 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.

1. 一个函数总是返回使用return语句而程序可通过参数返回一个或多个值或在all.Although可能不会返回一个值，OUT参数仍然可以用在函数中，它们是不可取也不存在情况下，可能会发现有必要这样做。使用OUT参数限制函数在 SQL 语句中使用。

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

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

4. Oracle 提供了创建“基于函数的索引”的规定，以提高后续 SQL 语句的性能。这适用于在查询的 where 子句中的索引列上执行函数时。

More Information on Functions Vs. Procedures hereand here.

关于函数的更多信息。程序在这里和这里。

回答by Jon Heller

There is almost never a performance difference between procedures and functions.

过程和函数之间几乎没有性能差异。

In a few extremely rare cases:

在极少数情况下：

• A procedure IN OUTargument is faster than a function return, when inlining is enabled.
• A procedure IN OUTargument is slower than a function return, when inlining is disabled.

• IN OUT当启用内联时，过程参数比函数返回更快。
• IN OUT禁用内联时，过程参数比函数返回慢。

Test code

测试代码

--Run one of these to set optimization level:
--alter session set plsql_optimize_level=0;
--alter session set plsql_optimize_level=1;
--alter session set plsql_optimize_level=2;
--alter session set plsql_optimize_level=3;

--Run this to compare times.  Move the comment to enable the procedure or the function.
declare
    v_result varchar2(4000);

    procedure test_procedure(p_result in out varchar2) is
    begin
        p_result := '0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789';
    end;

    function test_function return varchar2 is
    begin
        return '0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789';
    end;
begin
    for i in 1 .. 10000000 loop
        --Comment out one of these lines to change the test.
        --test_procedure(v_result);
        v_result := test_function;
    end loop;
end;
/

Results

结果

Inlining enabled:  PLSQL_OPTIMIZE_LEVEL = 2 (default) or 3
Function  run time in seconds: 2.839, 2.933, 2.979
Procedure run time in seconds: 1.685, 1.700, 1.762

Inlining disabled: PLSQL_OPTIMIZE_LEVEL = 0 or 1
Function  run time in seconds:  5.164, 4.967, 5.632
Procedure run time in seconds: 6.1, 6.006, 6.037

The above code is trivial and perhaps subject to other optimizations. But I have seen similar results with production code.

上面的代码很简单，可能还需要进行其他优化。但是我在生产代码中看到了类似的结果。

Why the difference doesn't matter

为什么差异无关紧要

Don't look at the above test and think "a procedure runs twice as fast as a function!". Yes, the overhead of a function is almost twice as much as the overhead of a procedure. But either way, the overhead is irrelevantly small.

不要看着上面的测试并认为“一个过程的运行速度是一个函数的两倍！”。是的，函数的开销几乎是过程开销的两倍。但无论哪种方式，开销都非常小。

The key to database performance is to do as much work as possible in SQL statements, in batches. If a program calls a function or procedure ten million times per second then that program has serious design problems.

数据库性能的关键是尽可能多地在 SQL 语句中分批完成工作。如果程序每秒调用一个函数或过程一千万次，那么该程序就有严重的设计问题。

回答by typoerrpr

State-changing vs non-state-changing

状态改变与非状态改变

On top of Romo Daneghyan's answer, I've always viewed the difference as their behaviour on the program state. That is, conceptually,

除了Romo Daneghyan 的回答之外，我一直将差异视为他们在程序状态上的行为。也就是说，在概念上，

• Procedures can change some state, either of the parameters or of the environment (eg, data in tables etc).
• Functions do not change state, and you would expect that calling a particular function would not modify any data/state. (Ie, the concept underlying functional programming)

• 过程可以改变一些状态，无论是参数还是环境（例如，表中的数据等）。
• 函数不会改变状态，您会期望调用特定函数不会修改任何数据/状态。（即函数式编程的底层概念）

Ie, if you called a function named generateId(...), you'd expect it to only do some computation and return a value. But calling a procedure generateId ..., you might expect it to change values in some tables.

也就是说，如果你调用了一个名为 的函数generateId(...)，你会期望它只做一些计算并返回一个值。但是调用过程generateId ...，您可能希望它更改某些表中的值。

Of course, it seems like in Oracle as well as many languages, this does not apply and is not enforced, so perhaps it's just me.

当然，似乎在 Oracle 以及许多语言中，这并不适用，也没有强制执行，所以也许只是我。

回答by dinesh

1. Procedure may or may not return value but functions return value.

2. procedure use out parameter returnvalue purpose but function returnstatment provide.

3. procedure used manipulation of data but function use calculation of data.
4. procedure execution time not use select statement but function use select statement. These are major difference of it.

1. 过程可能会或可能不会返回值，但函数会返回值。

2. 程序使用参数返回值的目的而函数returnstatment提供。

3. 过程使用数据操作，而函数使用数据计算。
4. 过程执行时不使用 select 语句，但函数使用 select 语句。这些是它的主要区别。

回答by Webucator

This is a great question and as far as I can tell has not really been answered. The question is not "What's the difference between a function and a procedure?" Rather, it is "Why would I ever use a procedure when I can do the same thing with a function?"

这是一个很好的问题，据我所知还没有真正得到回答。问题不是“函数和过程之间有什么区别？” 相反，它是“当我可以用函数做同样的事情时，我为什么还要使用过程？”

I think the real answer is "It's just convention."And as it's convention, it's what other developers are used to and expect, so you should follow the convention. But there is no functional reason to write a subprogram as a procedure over a function. The one exception may be when there are multiple OUTparameters.

我认为真正的答案是“这只是惯例”。因为它是约定俗成的，这是其他开发人员习惯和期望的，所以你应该遵循约定。但是没有功能上的理由将子程序编写为函数上的过程。一个例外可能是当有多个OUT参数时。

In his 6th edition of Oracle PL/SQL Programming, Steven Feuerstein recommends that you reserve OUTand IN OUTparameters for procedures and only return information in functions via the RETURN clause (p. 613). But again, the reason for this is convention. Developers don't expect functions to have OUTparameters.

在他的第 6 版 Oracle PL/SQL 编程中，Steven Feuerstein 建议您为过程保留OUT和IN OUT参数，并且仅通过 RETURN 子句 (p. 613) 返回函数中的信息。但同样，这是因为约定俗成。开发人员不希望函数有OUT参数。

I've written a longish post herearguing that you should only use a procedure when a function won't do the job. I personally prefer functions and wish that the convention was to use functions by default, but I think a better practice is to accept the things I cannot changeand bow to the actual convention and not the one I would wish for.

我在这里写了一篇很长的帖子，认为你应该只在函数不能完成工作时使用过程。我个人更喜欢函数，并希望约定默认使用函数，但我认为更好的做法是接受我无法改变的事情并屈服于实际约定，而不是我希望的约定。

回答by shalini

i think the major difference is :

我认为主要区别在于：

Functions can not contain DML Statemnt whereas the procedures can. for example like Update and Insert.

函数不能包含 DML 语句，而过程可以。例如像更新和插入。

if i am wrong correct me

如果我错了请纠正我

回答by Anh Tu?n

As I know, Store procedure is compiled once and can be called again and again without compiled again. But function is compiled each time called. So, Store procedure improves performance than function.

据我所知，存储过程编译一次，可以一次又一次地调用而无需再次编译。但是每次调用时都会编译函数。因此，存储过程比函数提高了性能。