CREATE PROCEDURE Statement

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

Note:

A standalone procedure that you create with the CREATE 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".

A call specification declares a Java method, a C function, or a JavaScript 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 specification tells the database which JavaScript function, Java method, or which named 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.

Topics

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.

See Also:

For more information about such prerequisites:

Syntax

create_procedure ::=

Description of the illustration create_procedure.eps

plsql_procedure_source ::=

Description of the illustration plsql_procedure_source.eps

( sharing_clause ::=, default_collation_clause ::=, invoker_rights_clause ::=, accessible_by_clause ::=, call_spec ::=, body ::=, declare_section ::=, parameter_declaration ::=)

Semantics

create_procedure

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.

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 OR REPLACE.

plsql_procedure_source

schema

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

procedure_name

Name of the procedure to be created.

Note:

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.

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

The reference to a call specification mapping a C procedure, Java method name, or JavaScript function name, parameter types, and return type to their SQL counterparts.

Examples

Example 15-22 Creating a Procedure

This statement creates the procedure remove_emp in the schema hr.

CREATE PROCEDURE IF NOT EXISTS 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.

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

See Also:

"CREATE PACKAGE BODY Statement" to see how to incorporate this procedure into a package

Example 15-23 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 BY REFERENCE phrase.

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

Example 15-24 Creating Procedures Using MLE Module and Inline Call Specifications

In this example, the same procedure is created in JavaScript twice. Once using an inline call specification and the other using an MLE module.

The following statement creates a JavaScript function with its declaration inline:

CREATE OR REPLACE PROCEDURE hello_inline(
  "who" VARCHAR2
)
AS MLE LANGUAGE JAVASCRIPT
{{
  console.log(`Hello, ${who}`);
}};
/

You can then call the procedure, as in the following:

EXEC hello_inline('Angela');

Result:

Hello, Angela

The following statements first create an MLE module that implements the hello function and then publish the procedure using a call specification:

CREATE OR REPLACE MLE MODULE hello_mod
LANGUAGE JAVASCRIPT AS 
  export function hello(who){
    console.log(`Hello, ${who}`);
  }
/

CREATE OR REPLACE PROCEDURE hello(
  "p_who" VARCHAR2
)
AS MLE MODULE hello_mod
SIGNATURE 'hello';
/

The following is an example of a call to the hello procedure:

EXEC hello('Chris');

Result:

Hello, Chris