Clever Geek Handbook

What is the difference between a stored procedure and a stand-alone procedure in Oracle?

separate procedure

create procedure proc1
(
begin

end;
)

stored procedure

create package pkg1
(
procedure proc2
begin

end;
)
+3


source to share


2 answers


From the oracle documentation for CREATE PROCEDURE

A separate procedure is a procedure (a subroutine that performs a specific action) that is stored in the database.

A nested procedure is a procedure that resides in a PL / SQL block or package.

From the doc 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 single unit in a database. The package specification declares these objects. The package body subsequently specified defines these objects.



Standalone procedures and procedures nested in a package are stored (compiled) in the database, so they are "stored" procedures. Procedures defined in an anonymous PL / SQL block are not "stored" procedures.

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 much difference between nested procedures in packages and separate procedures:

  • An individual procedure is defined with CREATE PROCEDURE ...

    , while a nested procedure is defined in a PL / SQL block with PROCEDURE ...

    .
  • In a stand-alone procedure, a procedure definition (part AS BEGIN ... END;

    ) is always required , but a (open) nested procedure in a package declares only the procedure header (part PROCEDURE NAME(...)

    ) and then the header will be validated in the package body and define the procedure definition.
  • Nesting a procedure in a package allows you to group it with similar functionality and allows it to access functions, procedures, and data that are private to the package (that is, defined in the package body, but not in the general package specification).
+2


source


These are both stored procedures because 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. This helps you forget to update all relevant routines together, keep your create scripts, etc. The main functional difference is the ability to grant and revoke privileges on an entire package, instead of managing dozens of "stand-alone" procedures independently,

+3


source






More articles:


All Articles