Oracle 中的存储过程和独立过程有什么区别？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/31286156/
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
What is the difference between stored procedure and standalone procedure in Oracle?
提问by Prabha Christ
standlone procedure
独立程序
create procedure proc1
(
begin
end;
)
stored procedure
存储过程
create package pkg1
(
procedure proc2
begin
end;
)
采纳答案by MT0
From the oracle documentation for CREATE PROCEDURE
来自 oracle 文档 CREATE PROCEDURE
A standalone procedure is a procedure (a subprogram that performs a specific action) that is stored in the database.
独立过程是存储在数据库中的过程（执行特定操作的子程序）。
A nested procedureis a procedure that is in a PL/SQL block or a package.
甲嵌套的过程是，在一个PL / SQL块或包的过程。
From the CREATE PACKAGEdocumentation:
从CREATE PACKAGE文档：
The CREATE PACKAGE statement creates or replaces the specification for a stored package, which is an encapsulated collection of related procedures, functions, and other program objects stored as a unit in the database. The package specification declares these objects. The package body, specified subsequently, defines these objects.
CREATE PACKAGE 语句创建或替换存储包的规范，存储包是作为一个单元存储在数据库中的相关过程、函数和其他程序对象的封装集合。包规范声明了这些对象。随后指定的包体定义了这些对象。
Standalone procedures and procedures nested in a package are both stored (compiled) within the database - so are "stored" procedures. Procedures defined in an anonymous PL/SQL block are not "stored" procedures.
独立过程和嵌套在包中的过程都存储（编译）在数据库中——“存储”过程也是如此。在匿名 PL/SQL 块中定义的过程不是“存储”过程。
This is not a stored procedure:
这不是存储过程：
DECLARE
n NUMBER := 1;
PROCEDURE incr( a IN OUT NUMBER ) IS
BEGIN
a := a + 1;
END;
BEGIN
incr(n);
DBMS_OUTPUT.PUT_LINE(n);
END;
/
There is not a huge difference between nested procedures in packages and standalone procedures:
包中的嵌套过程和独立过程之间没有太大区别：
- A standalone procedure is defined using
CREATE PROCEDURE ...whereas a nested procedure is defined within the PL/SQL block usingPROCEDURE .... - A standalone procedure always requires a procedure definition (the
AS BEGIN ... END;part) but a (public) nested procedure in a package only declares the procedure heading (thePROCEDURE NAME(...)part) and then in the package body will restate the heading and define the procedure definition. - Nesting a procedure in a package allows it to be grouped with similar functionality and allows it to access functions, procedures and data which is private to the package (i.e. defined in the package body but not in the public package specification).
- 使用 定义独立过程，
CREATE PROCEDURE ...而在 PL/SQL 块中使用 定义嵌套过程PROCEDURE ...。 - 独立过程总是需要过程定义（
AS BEGIN ... END;部分），但包中的（公共）嵌套过程仅声明过程标题（PROCEDURE NAME(...)部分），然后在包体中将重新声明标题并定义过程定义。 - 在包中嵌套一个过程允许它以相似的功能分组，并允许它访问包私有的函数、过程和数据（即在包体中定义但不在公共包规范中）。
回答by Mureinik
These are both stored procedures, as they are both stored in the database and can be called later.
它们都是存储过程，因为它们都存储在数据库中并且可以稍后调用。
Putting procedures in a package is just a neat way to organize them. It helps remembering to update all the relevant procedures together, keep your creation scripts tidy, etc. The main functional difference is the ability to grant and revoke privileges on an entire package, instead of having to manage a dozen "stand-alone" procedures independently.
将程序放在一个包中只是一种组织它们的巧妙方式。它有助于记住一起更新所有相关程序，保持您的创建脚本整洁等。主要的功能区别是能够授予和撤销整个包的权限，而不必独立管理十几个“独立”程序.
