PL/SQL 中的函数和过程有什么区别？

What is the difference between function and procedure in PL/SQL?

提问by Tony

What is the difference between function and procedure in PL/SQL ?

PL/SQL 中的函数和过程有什么区别？

采纳答案by Petros

A procedure does not have a return value, whereas a function has.

过程没有返回值，而函数有。

Example:

例子：

CREATE OR REPLACE PROCEDURE my_proc
   (p_name IN VARCHAR2 := 'John') as begin ... end

CREATE OR REPLACE FUNCTION my_func
   (p_name IN VARCHAR2 := 'John') return varchar2 as begin ... end

Notice how the function has a return clause between the parameter list and the "as" keyword. This means that it is expected to have the last statement inside the body of the function read something like:

请注意该函数如何在参数列表和“as”关键字之间有一个 return 子句。这意味着函数体中的最后一条语句应该是这样的：

return(my_varchar2_local_variable);

Where my_varchar2_local_variable is some varchar2 that should be returned by that function.

其中 my_varchar2_local_variable 是一些应该由该函数返回的 varchar2。

回答by ConcernedOfTunbridgeWells

A function can be in-lined into a SQL statement, e.g.

函数可以内联到 SQL 语句中，例如

select foo
      ,fn_bar (foo)
  from foobar

Which cannot be done with a stored procedure. The architecture of the query optimiser limits what can be done with functions in this context, requiring that they are pure (i.e. the same inputs always produce the same output). This restricts what can be done in the function, but allows it to be used in-line in the query if it is defined to be "pure".

这是不能用存储过程完成的。查询优化器的架构限制了在此上下文中可以使用函数完成的操作，要求它们是纯函数（即相同的输入总是产生相同的输出）。这限制了可以在函数中执行的操作，但如果它被定义为“纯”，则允许它在查询中内嵌使用。

Otherwise, a function (not necessarily deterministic) can return a variable or a result set. In the case of a function returning a result set, you can join it against some other selection in a query. However, you cannot use a non-deterministic function like this in a correlated subquery as the optimiser cannot predict what sort of result set will be returned (this is computationally intractable, like the halting problem).

否则，函数（不一定是确定性的）可以返回变量或结果集。在函数返回结果集的情况下，您可以将它与查询中的其他选择相结合。但是，您不能在相关子查询中使用这样的非确定性函数，因为优化器无法预测将返回什么样的结果集（这在计算上是难以处理的，就像停机问题一样）。

回答by Aparna

Both stored procedures and functions are named blocks that reside in the database and can be executed as and when required.

存储过程和函数都是驻留在数据库中的命名块，可以在需要时执行。

The major differences are:

主要区别是：

1. A stored procedure can optionally return values using out parameters, but can also be written in a manner without returning a value. But, a function must return a value.

2. A stored procedure cannot be used in a SELECT statement whereas a function can be used in a SELECT statement.

1. 存储过程可以选择使用 out 参数返回值，但也可以不返回值的方式编写。但是，函数必须返回一个值。

2. SELECT 语句中不能使用存储过程，而 SELECT 语句中可以使用函数。

Practically speaking, I would go for a stored procedure for a specific group of requirements and a function for a common requirement that could be shared across multiple scenarios. For example: comparing between two strings, or trimming them or taking the last portion, if we have a function for that, we could globally use it for any application that we have.

实际上，我会为一组特定的需求寻找一个存储过程，并为一个可以在多个场景中共享的公共需求寻找一个函数。例如：比较两个字符串，或修剪它们或取最后一部分，如果我们有一个函数，我们可以将它全局用于我们拥有的任何应用程序。

回答by Dulith De Costa

In dead simple way it makes this meaning.

以极其简单的方式，它使这个含义成为可能。

Functions :

职能 ：

These subprograms return a single value; mainly used to compute and return a value.

这些子程序返回一个值；主要用于计算和返回一个值。

Procedure :

程序 ：

These subprograms do not return a valuedirectly; mainly used to perform an action.

这些子程序不直接返回值；主要用于执行一个动作。

Example Program:

示例程序：

CREATE OR REPLACE PROCEDURE greetings

BEGIN 

dbms_output.put_line('Hello World!');

END ;
/

Executing a Standalone Procedure :

执行独立程序：

A standalone procedure can be called in two ways:

可以通过两种方式调用独立过程：

? Using the EXECUTEkeyword ? Calling the name of procedure from a PL/SQL block

? 使用EXECUTE关键字 ? 从 PL/SQL 块调用过程名称

The procedure can also be called from another PL/SQL block:

该过程也可以从另一个 PL/SQL 块调用：

BEGIN 
greetings;
END;
/

Function:

功能：

CREATE OR REPLACE FUNCTION totalEmployees 
RETURN number IS
total number(3) := 0;
BEGIN 
SELECT count(*) into total 
FROM employees;
RETURN total; 
END;
/

Following program calls the function totalCustomersfrom an another block

以下程序totalCustomers从另一个块调用该函数

DECLARE 
c number(3);
BEGIN 
c := totalEmployees();
dbms_output.put_line('Total no. of Employees: ' || c);
END;
/

回答by mohan

The following are the major differences between procedure and function,

以下是过程和功能之间的主要区别，

1. Procedure is named PL/SQL block which performs one or more tasks. where function is named PL/SQL block which performs a specific action.
2. Procedure may or may not return value where as function should return one value.
3. we can call functions in select statement where as procedure we cant.

1. 过程被命名为执行一项或多项任务的 PL/SQL 块。其中函数被命名为执行特定操作的 PL/SQL 块。
2. 过程可能会也可能不会返回值，而函数应该返回一个值。
3. 我们可以在 select 语句中调用函数，而作为过程我们不能。

回答by Tomek Krasuski

In the few words - function returns something. You can use function in SQL query. Procedure is part of code to do something with data but you can not invoke procedure from query, you have to run it in PL/SQL block.

简而言之 - 函数返回一些东西。您可以在 SQL 查询中使用函数。过程是对数据做一些事情的代码的一部分，但你不能从查询中调用过程，你必须在 PL/SQL 块中运行它。

回答by Pearl

1. we can call a stored procedure inside stored Procedure,Function within function ,StoredProcedure within function but we can not call function within stored procedure.
2. we can call function inside select statement.
3. We can return value from function without passing output parameter as a parameter to the stored procedure.

1. 我们可以在存储过程中调用存储过程，在函数中调用函数，在函数中调用存储过程，但不能在存储过程中调用函数。
2. 我们可以在 select 语句中调用函数。
3. 我们可以从函数返回值，而无需将输出参数作为参数传递给存储过程。

This is what the difference i found. Please let me know if any .

这就是我发现的不同之处。如果有的话请告诉我。