Definition:
In Oracle PL/SQL, a PROCEDURE is a named PL/SQL subprogram which can (optionally) accept parameters and may or may not return a value to the host. Its major function is to embed a business logic process and perform data manipulation with the help of the supplied data. It can return values to the calling environment only through OUT parameters. A procedure can contain one or more RETURN statements, which logically return the control to the calling environment, thereby skipping the further execution of the procedure body.
The User must have CREATE [ANY] PROCEDURE privilege to create a stored procedure. Procedures promote restoring the logic, its reusability and maintainability.
Example Syntax:
CREATE [OR REPLACE] PROCEDURE [NAME] [PARAMETERS]
[IS | AS]
BEGIN
END;
In the syntax, REPLACE is used to preserve the privileges associated with the procedure.
Example Usage:
The procedure P_TEST_PROC calculates sum of two numbers and returns the result to the host (or to the calling environment).
CREATE OR REPLACE PROCEDURE P_TEST_PROC (P_ARG1 NUMBER,
P_ARG2 NUMBER,
P_RESULT OUT NUMBER)
IS
BEGIN
P_RESULT := P_ARG1 + P_ARG2;
END;
Related Links:
Related Code Snippets:
