CREATE PROCEDURE
Purpose
Procedures are defined using PL/SQL. Therefore, this section provides some general information but refers to Oracle Database PL/SQL Language Reference for details of syntax and semantics.
Use the CREATE
PROCEDURE
statement to create a standalone stored procedure or a call specification.
A procedure is a group of PL/SQL statements that you can call by name. A call specification (sometimes called call spec) declares a Java method, a JavaScript method, or a third-generation language (3GL) routine so that it can be called from SQL and PL/SQL. The call spec tells Oracle Database which Java method, JavaScript function, or third-generation language (3GL) routine to invoke when a call is made. It also tells the database what type conversions to make for the arguments and return value.
Stored procedures offer advantages in the areas of development, integrity, security, performance, and memory allocation.
See Also:
-
Oracle Database Development Guide for more information on stored procedures, including how to call stored procedures and for information about registering external procedures.
-
CREATE FUNCTION for information specific to functions, which are similar to procedures in many ways.
-
CREATE PACKAGE for information on creating packages. The
CREATE
PROCEDURE
statement creates a procedure as a standalone schema object. You can also create a procedure as part of a package. -
ALTER PROCEDURE and DROP PROCEDURE for information on modifying and dropping a standalone procedure.
-
CREATE LIBRARY for more information about shared libraries.
Prerequisites
To create or replace a procedure in your own schema, you must have the CREATE
PROCEDURE
system privilege. To create or replace a procedure in another user's schema, you must have the CREATE
ANY
PROCEDURE
system privilege.
To invoke a call spec, you may need additional privileges, for example, the EXECUTE
object privilege on the C library for a C call spec.
To embed a CREATE
PROCEDURE
statement inside an Oracle precompiler program, you must terminate the statement with the keyword END-EXEC
followed by the embedded SQL statement terminator for the specific language.
See Also:
Oracle Database PL/SQL Language Reference or Oracle Database Java Developer's Guide for more information
Syntax
Procedures are defined using PL/SQL. Alternatively they can refer to non-PL/SQL code such as Java, JavaScript, C, and others by means of call specifications. Therefore, the syntax diagram in this book shows only the SQL keywords. Refer to Oracle Database PL/SQL Language Reference for the PL/SQL syntax, semantics, and examples.
create_procedure::=
(plsql_procedure_source
: See Oracle Database PL/SQL Language Reference.)
Semantics
OR REPLACE
Specify OR
REPLACE
to re-create the procedure if it already exists. Use this clause to change the definition of an existing procedure without dropping, re-creating, and regranting object privileges previously granted on it. If you redefine a procedure, then Oracle Database recompiles it.
Users who had previously been granted privileges on a redefined procedure can still access the procedure without being regranted the privileges.
If any function-based indexes depend on the procedure, then Oracle Database marks the indexes DISABLED
.
See Also:
ALTER PROCEDURE for information on recompiling procedures
IF NOT EXISTS
Specifying IF NOT EXISTS
has the following effects:
-
If the procedure does not exist, a new procedure is created at the end of the statement.
-
If the procedure exists, this is the procedure you have at the end of the statement. A new one is not created because the older one is detected.
You can have one of OR REPLACE
or IF NOT EXISTS
in a statement at a time. Using both OR REPLACE
with IF NOT EXISTS
in the very same statement results in the following error: ORA-11541: REPLACE and IF NOT EXISTS cannot coexist in the same DDL statement
.
Using IF EXISTS
with CREATE
results in ORA-11543: Incorrect IF NOT EXISTS clause for CREATE statement
.
[ EDITIONABLE | NONEDITIONABLE ]
Use these clauses to specify whether the procedure is an editioned or noneditioned object if editioning is enabled for the schema object type PROCEDURE
in schema
. The default is EDITIONABLE
. For information about editioned and noneditioned objects, see Oracle Database Development Guide.
plsql_procedure_source
See Oracle Database PL/SQL Language Reference for the syntax and semantics of the plsql_procedure_source
.