Skip Headers
Oracle® Database PL/SQL Language Reference
11g Release 2 (11.2)

Part Number E17126-03
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

CREATE PROCEDURE Statement

The CREATE PROCEDURE statement creates or replaces a standalone stored procedure or a call specification.

A standalone stored procedure is a procedure (a subprogram that performs a specific action) that is stored in the database.

Note:

A standalone stored 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 about the latter, see "Procedure Declaration and Definition".

A call specification declares a Java method or a third-generation language (3GL) subprogram 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 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 stored procedure in your own schema, you must have the CREATE PROCEDURE system privilege. To create or replace a standalone stored 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 ::=

create_procedure
Description of the illustration create_procedure.gif

See:

invoker_rights_clause ::=

invoker_rights_clause
Description of the illustration invoker_rights_clause.gif

call_spec ::=

call_spec
Description of the illustration call_spec.gif

Java_declaration ::=

v
Description of the illustration java_declaration.gif

C_declaration ::=

C_declaration
Description of the illustration c_declaration.gif

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.

schema

The name of the schema containing the procedure. The default is your own schema.

procedure_name

The 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)".

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

Maps a C procedure or Java method name, parameter types, and return type to their SQL counterparts. In Java_declaration, string identifies the Java implementation of the method.

See Also:

EXTERNAL

Deprecated way of declaring a C procedure, supported only for backward compatibility. Oracle recommends that you use the LANGUAGE C syntax.

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.

See Also:

"Creating a Package Body: Example" to see how to incorporate this procedure into a package

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:

In other chapters:

See Also: