CREATE PROCEDURE Statement

Prerequisites

To create or replace a standalone procedure in your schema, you must have the CREATE PROCEDURE system privilege. To create or replace a standalone procedure in another user's schema, you must have the CREATE ANY 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 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.

Syntax

create_procedure ::=

plsql_procedure_source ::=

See:

• "accessible_by_clause ::="

• "call_spec ::="

• "body ::="

• "declare_section ::="

• "parameter_declaration ::="

invoker_rights_clause ::=

Semantics

OR REPLACE

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 DISABLED.

[ EDITIONABLE | NONEDITIONABLE ]

Specifies whether the procedure is an editioned or noneditioned object if editioning is enabled for the schema object type PROCEDURE in schema. Default: EDITIONABLE. For information about editioned and noneditioned objects, see Oracle Database Development Guide.

schema

Name of the schema containing the procedure. Default: your schema.

procedure_name

Name of the procedure to be created.

invoker_rights_clause

Specifies the AUTHID property of the procedure. For information about the AUTHID property, see "Invoker's Rights and Definer's Rights (AUTHID Property)".

accessible_by_clause

Has the same behavior for a procedure as it does for a function. See the CREATE FUNCTION "accessible_by_clause".

body

The required executable part of the procedure and, optionally, the exception-handling part of the procedure.

declare_section

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.

call_spec, EXTERNAL

See "call_spec" and "EXTERNAL".

Examples

Creating a Procedure: Example This statement creates the procedure remove_emp in the schema hr.

CREATE PROCEDURE remove_emp (employee_id NUMBER) AS
   tot_emps NUMBER;
   BEGIN
      DELETE FROM employees
      WHERE employees.employee_id = remove_emp.employee_id;
   tot_emps := tot_emps - 1;
   END;
/

The 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 employee_id.

In this example, external procedure c_find_root expects a pointer as a parameter. Procedure find_root passes the parameter by reference using the BY REFERENCE phrase.

CREATE PROCEDURE find_root
   ( x IN REAL ) 
   IS LANGUAGE C
      NAME c_find_root
      LIBRARY c_utils
      PARAMETERS ( x BY REFERENCE );

Related Topics

In this chapter:

• "ALTER PROCEDURE Statement"

• "CREATE FUNCTION Statement"

• "DROP PROCEDURE Statement"

In other chapters:

• "Formal Parameter Declaration"

• "Procedure Declaration and Definition"

• Chapter 8, "PL/SQL Subprograms"