我什么时候应该在 PL/SQL 中使用过程或函数？

when should I go for procedure or function in PL/SQL?

提问by user1252398

I am new in PL/SQL trying some practice examples.
I have few questions regarding PL/SQL PROCEDURE& FUNCTION:

我是 PL/SQL 新手，正在尝试一些练习示例。
我有几个关于 PL/SQL PROCEDURE& 的问题FUNCTION：

When should I go for the PROCEDUREor FUNCTION?

我应该什么时候去PROCEDURE或FUNCTION?

Means, whatever task I am doing using FUNCTIONthe same task will do by using PROCEDURE. then why should I go for the function? is their any advantage of FUNCTIONover the PROCEDUREin PL/SQL?

意思是，无论我使用FUNCTION相同的任务执行什么任务，都可以使用PROCEDURE. 那我为什么要去做这个功能呢？它们FUNCTION比PROCEDUREPL/SQL 有什么优势吗？

FUNCTIONmust return value. Is this the only advantage for using a function or are there any other advantages of functions?

FUNCTION必须返回值。这是使用函数的唯一优势还是函数的其他优势？

回答by Rachcha

I would like to clarify that the answer for whether you should use a stored procedure or a function is completely dependent upon your business requirement and design workflow, provided you are clear about your program objective. If you are unclear about your objective, just the way your question is, no amount of coding procedures and functions would be useful.

我想澄清一下，是否应该使用存储过程或函数的答案完全取决于您的业务需求和设计工作流程，前提是您清楚您的程序目标。如果你不清楚你的目标，就像你的问题一样，再多的编码程序和功能都没有用。

You must note that stored procedures and functions serve different purposes in PL/SQL programming. These are as follows:

您必须注意，存储过程和函数在 PL/SQL 编程中有不同的用途。这些如下：

1. Stored procedures:

   a. Stored procedures represent named blocks (as opposed to anonymous blocks) that are capable of accepting parameters and work on them.

   b. Stored procedures define an independent procedural workflow where you can perform a series of DML and/or other operations.

   c. Stored procedures do not have to return a value. Hence, they cannot be called from inside an SQL statement. Stored procedures must be executed from a PL/SQL block- named or anonymous.

   d. Merits:

   • A procedure does not have to return a value (This can be a demerit too).
   • Can be used to perform a series of DML or DDL (yes, this is possible through dynamic SQL with a few restrictions) operations.

   • Can be simply called as an independent statement from a PL/SQL block. e.g.,

     myProcedure (x, y);
     

   e. Demerits:

   • Cannot be called from an SQL query - DML or a SELECTstatement.
   • Cannot be used in indexes.

2. Functions:

   a. Functions are named blocks that are capable of accepting parameters and return a value.

   b. Functions also define a procedural workflow but when used in SQL statements, you cannot perform any DML or DDL.

   c. A function must be called from a SQL or PL/SQL statement where the value returned by the function is utilized- i.e., assigned to a variable, passed as a parameter, etc.

   d. Merits:

   • Can be used in an SQL query - DML or a SELECTstatement.
   • Can be used in function-based indexes if the function is deterministic (meaning for a definite set of inputs the function returns the same output every time it is called).

   e. Demerits:

   • If the function being called from an SQL query contains any DML, the query fails.
   • It is obligatory for a function to return a value. Hence a function call cannot be an independent statement like a procedure call.

1. 存储过程：

   一种。存储过程表示能够接受参数并对其进行处理的命名块（与匿名块相对）。

   湾 存储过程定义了一个独立的过程工作流，您可以在其中执行一系列 DML 和/或其他操作。

   C。存储过程不必返回值。因此，不能从 SQL 语句内部调用它们。存储过程必须从 PL/SQL 块命名或匿名执行。

   d. 优点：

   • 过程不必返回值（这也可能是一个缺点）。
   • 可用于执行一系列 DML 或 DDL（是的，这可以通过具有一些限制的动态 SQL 实现）操作。

   • 可以简单地作为来自 PL/SQL 块的独立语句调用。例如，

     myProcedure (x, y);
     

   e. 缺点：

   • 不能从 SQL 查询调用 - DML 或SELECT语句。
   • 不能在索引中使用。

2. 职能：

   一种。函数是能够接受参数并返回值的命名块。

   湾 函数还定义了过程工作流，但在 SQL 语句中使用时，您不能执行任何 DML 或 DDL。

   C。必须从 SQL 或 PL/SQL 语句调用函数，其中使用函数返回的值 - 即分配给变量、作为参数传递等。

   d. 优点：

   • 可用于 SQL 查询 - DML 或SELECT语句。
   • 如果函数是确定性的（意味着对于一组确定的输入，函数每次调用时都返回相同的输出），则可以在基于函数的索引中使用。

   e. 缺点：

   • 如果从 SQL 查询调用的函数包含任何 DML，则查询失败。
   • 函数必须返回一个值。因此，函数调用不能像过程调用那样是独立的语句。

For further reference, visit Oracle Docs.

如需进一步参考，请访问Oracle 文档。

回答by tawman

A user defined function, with certain limitations, can be used in SELECTstatements and PL/SQL IFstatements whereas a PROCEDUREcannot.

用户定义的函数有一定的限制，可以在SELECT语句和 PL/SQLIF语句中使用，而 aPROCEDURE不能。

You can SELECTfrom a FUNCTIONthat is CASTas a table using pipeline and PIPE ROWstatements, but that is an advanced PL/SQL feature you can use much later.

你可以SELECT从一个FUNCTION就是CAST因为使用管道和表PIPE ROW的语句，但是这是一种先进的PL / SQL功能，您可以多备后用。

Consult the Oracle Developer documentation online as it is free and very good: Developing and Using Stored Procedures

在线查阅 Oracle Developer 文档，因为它是免费且非常好的：开发和使用存储过程

回答by Gaurav Soni

1. A function will return a value, A "value"have be one of many things including PL/SQL tables, ref cursors etc. Adding to that, it is possible to use a function in SQL statements, whereas procedures cannot be used.
2. Procedures are used to execute business logic, where we can return multiple values from the procedure using OUTor IN OUTparameters.
3. Personally I use function for computations - For example: check for a specific condition like retrieving a value based on the condition, checking the condition for true or false.

4. You can have DML(insert, update, delete) statements in a function. But, you cannot call such a function in a SQL query. *Eg: If you have a function that is updating a table, you can't call that function in any SQL query.

   select myFunction(field) from sometable; --will throw error.
   

1. 函数将返回一个值，A "value"它是包括 PL/SQL 表、引用游标等在内的许多事物之一。除此之外，可以在 SQL 语句中使用函数，而不能使用过程。
2. 过程用于执行业务逻辑，我们可以使用OUT或IN OUT参数从过程中返回多个值。
3. 我个人使用函数进行计算 - 例如：检查特定条件，例如根据条件检索值，检查条件是否为真。

4. 您可以在函数中使用 DML（插入、更新、删除）语句。但是，您不能在 SQL 查询中调用这样的函数。*例如：如果您有一个正在更新表的函数，则不能在任何 SQL 查询中调用该函数。

   select myFunction(field) from sometable; --will throw error.
   

It is on your choice whether to use procedure or function depends on you requirement and your comfortability.

使用程序还是函数取决于您的需求和舒适度。

回答by manju

Main advantages:

主要优势：

1. Function it must return a values where as procedure may or may not return a values.
2. Function mainly used for to computes a values. Where as procedure mainly used for executive business logic .
3. Function to retrive a value where as procedure to manupliate a values.
4. Function it must return only one values but it accept many return types.

1. 函数必须返回一个值，而过程可能会或可能不会返回值。
2. 函数主要用于计算一个值。其中procedure主要用于执行业务逻辑。
3. 检索值的函数，其中作为操作值的过程。
4. 函数它必须只返回一个值，但它接受多种返回类型。

回答by user2001117

There is more advantage of using procedure over the function:

与函数相比，使用过程有更多优势：

1. If we want to execute the Dynamically SQL statement using execute immediate statement then we use Procedure .It can not done inside the Function.

2. Procedure can be execute independently, while a Function must be part of an executable statement, as it cannot be executed independently.

1. 如果我们想使用立即执行语句来执行动态SQL语句，那么我们使用Procedure。它不能在Function内部完成。

2. 过程可以独立执行，而函数必须是可执行语句的一部分，因为它不能独立执行。

回答by user2488292

function must return a value it is used in calculation purpose we are use . Function it must return only one values but it accept many return types. function we can use select statement. procedure it may or may not return a value . procedure is we can use dml operations . procedure we can execute only.

函数必须返回一个值，它用于我们正在使用的计算目的。函数它必须只返回一个值，但它接受多种返回类型。函数我们可以使用select语句。过程它可能会也可能不会返回一个值。程序是可以用dml操作的。我们只能执行的程序。

回答by Piyush

Whenever you want to return some value and have to use that value further, go for function. If you want to return some value as the end result, go for procedure.

每当您想返回某个值并且必须进一步使用该值时，请使用函数。如果您想返回某个值作为最终结果，请转到过程。

回答by Glen

Of course you can execute dynamic sql in a function. Write a simple function to do the test and you will see it does work.

当然你可以在函数中执行动态sql。编写一个简单的函数来进行测试，您将看到它确实有效。

You can use either function or procedure most of the time. Differences are whether you want to use them in an SQL statement or in an IF statement then use a function. Otherwise use what best suits you and the calling application.

大多数情况下，您可以使用函数或过程。区别在于您是要在 SQL 语句中使用它们还是在 IF 语句中使用它们然后使用函数。否则，请使用最适合您和呼叫应用程序的方式。