PROCEDURE statement creates or replaces a standalone procedure or a call specification.
A standalone procedure is a procedure (a subprogram that performs a specific action) that is stored in the database.
A standalone procedure that you create with the
PROCEDURE statement differs from a procedure that you declare and define in a PL/SQL block or package. For information, see "Procedure Declaration and Definition" or "CREATE PACKAGE Statement".
function so that it can be called from PL/SQL. You can also use the SQL
CALL statement to invoke such a method or subprogram. The call
procedure in which shared library, to invoke when an invocation is made. It also tells
the database what type conversions to make for the arguments and return value.
To create or replace a standalone procedure in your schema, you must have the
PROCEDURE system privilege. To create or replace a standalone procedure in another user's schema, you must have the
PROCEDURE system privilege.
To invoke a call specification, you may need additional privileges, for example, the
EXECUTE object privilege on the C library for a C call specification.
To embed a
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.
Re-creates the procedure if it exists, and recompiles it.
Users who were granted privileges on the procedure before it was redefined can still access the procedure without being regranted the privileges.
If any function-based indexes depend on the procedure, then the database marks the indexes
[ EDITIONABLE | NONEDITIONABLE ]
Specifies whether the procedure is an editioned or noneditioned object if editioning is enabled for the schema object type
EDITIONABLE. For information about editioned and noneditioned objects, see Oracle Database Development Guide.
IF NOT EXISTS
Creates the procedure if it does not already exist. If a procedure by the same name does exist, the statement is ignored without error and the original procedure remains unchanged.
IF NOT EXISTS cannot be used in combination with
Name of the schema containing the procedure. Default: your schema.
Name of the procedure to be created.
If you plan to invoke a stored subprogram using a stub generated by SQL*Module, then the stored subprogram name must also be a legal identifier in the invoking host 3GL language, such as Ada or C.
The required executable part of the procedure and, optionally, the exception-handling part of the procedure.
The optional declarative part of the procedure. Declarations are local to the procedure, can be referenced in
body, and cease to exist when the procedure completes execution.
Example 15-21 Creating a Procedure
This statement creates the procedure
remove_emp in the
CREATE PROCEDURE IF NOT EXISTS remove_emp (employee_id NUMBER) AS
DELETE FROM employees
WHERE employees.employee_id = remove_emp.employee_id;
tot_emps := tot_emps - 1;
remove_emp procedure removes a specified employee.
When you invoke the procedure, you must specify the
employee_id of the
employee to be removed.
The procedure uses a
DELETE statement to remove from the
employees table the row of
IF NOT EXISTS clause is used to ensure that
the statement is idempotent. The resulting output message (in this case
Procedure created) is the same whether the procedure is created or
the statement is ignored.
"CREATE PACKAGE BODY Statement" to see how to incorporate this procedure into a package
Example 15-22 Creating an External Procedure
In this example, external procedure
c_find_root expects a pointer as a parameter. Procedure
find_root passes the parameter by reference using the
CREATE PROCEDURE find_root ( x IN REAL ) IS LANGUAGE C NAME c_find_root LIBRARY c_utils PARAMETERS ( x BY REFERENCE );
In this chapter:
In other chapters:
In other books: