oracle 函数、过程和触发器 - 它们有何不同，以及何时使用？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8337311/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Functions, Procedure & Triggers - how are they different, and when to use?
提问by Avi
I am very new to Oracle PL/SQL...
我对 Oracle PL/SQL 很陌生...
What is the difference between Oracle Functions, Procedure & Triggers? And what are appropriate uses of each?
Oracle 函数、过程和触发器之间有什么区别？每种方法的适当用途是什么？
回答by Daniel Haviv
- Functions and Procedures: basically just a piece of code that you run at will. In any language Function will return a value (eg' number of rows updated, a string etc') and a Procedure will not return any value.
- Triggers: Are pieces of code that run because of an event; For example you have a table and you would like that after every insert to this table, you will get an email - Then you define an AFTER INSERT trigger ON myImportant table and tell it to send you an email with the contents of the recent Insert.
- 函数和过程：基本上只是一段你随意运行的代码。在任何语言中，函数都会返回一个值（例如“更新的行数、字符串等”），而过程不会返回任何值。
- 触发器：是由于事件而运行的代码段；例如，您有一个表，并且您希望在每次插入该表后，您都会收到一封电子邮件 - 然后您在 myImportant 表上定义一个 AFTER INSERT 触发器，并告诉它向您发送一封包含最近插入内容的电子邮件。
A trigger can and probably will use functions and procedures.
触发器可以而且很可能会使用函数和过程。
回答by Rajesh Chamarthi
All three are different types of PL/SQL Blocks, which means that you can group multiple SQL statements and use variables, exception blocks so on. Procedures and functions in particular help re-use, so that you don't have to write the same logic at multiple places and you can grant someone/some application access to "do something" or "get some value" but not access the tables directly.
这三种都是不同类型的PL/SQL Blocks，这意味着你可以将多个SQL语句组合在一起，并使用变量、异常块等。过程和函数特别有助于重用，因此您不必在多个地方编写相同的逻辑，并且您可以授予某人/某些应用程序“做某事”或“获得一些价值”的访问权，但不能访问表直接地。
It is probably better to explain the difference with different use cases. Let's consider the simple case of a customer placing an order.
用不同的用例来解释差异可能会更好。让我们考虑一个客户下订单的简单案例。
Every order that is placed will have a series of steps, so you can have a PL/SQL Block that does that work (executes a set of sql statements). A procedure is the way to implement this.
每个下的订单都会有一系列的步骤，所以你可以有一个PL/SQL 块来完成这项工作（执行一组 sql 语句）。程序是实现这一点的方法。
/* this is usually part of a package and not a stand-alone procedure,
but everything else holds good */
create or replace procedure p_create_order(
i_product_id in products.product_type,
i_price in orders.price%type,
i_shipping_method in
i_state in address.state%type)
as
begin
insert into orders(item_id,
product_price,
tax_and_other_charges)
values (i_product_id,
i_price,
f_get_tax(i_price, i_state)); **---function call**
update inventory set quantity_on_hand = quantity_on_hand - 1;
insert into shipping_request(....) values(....)
commit;
end;
/
Let's assume the total tax calculation is based on series of conditions, you can (and should) avoid coding the logic at multiple places by calling a function instead. A function can return only one value, which is what you need in most cases. If you need more than one value to be returned, you can use a procedure and out parameters.
假设总税款计算基于一系列条件，您可以（并且应该）通过调用函数来避免在多个地方编写逻辑代码。一个函数只能返回一个值，这是您在大多数情况下所需要的。如果需要返回多个值，可以使用过程和输出参数。
create or replace function f_get_price(
i_price in product.price%type,
i_state in address.state%type)
return number
as
o_total_price number;
begin
select i_price *tax_per percent
into o_total_price
from state_tax
where state = i_state;
if(...some condition..)
then o_total_price = o_total_price*0.8
else...
...
end if;
return o_total_price;
end;
/
Triggers are used for a completely different reason. If you want to execute a set of statements when some condition is met (triggering event), trigger might beyour solution.
触发器用于完全不同的原因。如果您想在满足某些条件（触发事件）时执行一组语句，触发器可能是您的解决方案。
You can read more about their use cases in the link below. Please note that more often than not, there is an oracle feature (like auditing, versioning) etc, which is already in place so that you don't have to re implement it using triggers. Make sure you research before developing a solution, particularly using triggers.
您可以在下面的链接中阅读有关其用例的更多信息。请注意，通常情况下，有一个 oracle 功能（如审计、版本控制）等，该功能已经到位，因此您不必使用触发器重新实现它。确保在开发解决方案之前进行研究，尤其是使用触发器。
http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/triggers.htm#i1006211
http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/triggers.htm#i1006211
