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 theCREATE
PROCEDURE
statement is different 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) routine so that it can be called from PL/SQL. You can also use the SQL CALL
statement to call such a method or routine. The call specification tells the database which Java method, or which named procedure in which shared library, to invoke when a call is made. It also tells the database what type conversions to make for the arguments and return value.
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.
See Also:
For more information about such prerequisites:(parameter_declaration ::=, declare_section ::=, body ::=)
call_spec ::=
Keyword and Parameter Descriptions
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 the 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 package, then the database marks the indexes DISABLED
.
Specify the schema to contain the procedure. If you omit schema
, then the database creates the procedure in your current schema.
Specify the name of the procedure to be created.
Specifies the AUTHID
property of the member functions and procedures of the object type. For information about the AUTHID
property, see "Using Invoker's Rights or Definer's Rights (AUTHID Clause)".
Specify CURRENT_USER
to indicate that the procedure executes with the privileges of CURRENT_USER
. This clause creates an invoker's rights procedure.
This clause also specifies that external names in queries, DML operations, and dynamic SQL statements resolve in the schema of CURRENT_USER
. External names in all other statements resolve in the schema in which the procedure resides.
Specify DEFINER
to indicate that the procedure executes with the privileges of the owner of the schema in which the procedure resides, and that external names resolve in the schema where the procedure resides. This is the default and creates a definer's rights procedure.
See Also:
Using Invoker's Rights or Definer's Rights (AUTHID Clause) for more information about invoker's rights and definer's rightsThe 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.
Use the call_spec
to map a Java or C method name, parameter types, and return type to their SQL counterparts.
In the Java_declaration
, string
identifies the Java implementation of the method.
See Also:
Oracle Database Java Developer's Guide for an explanation of the parameters and semantics of the Java_declaration
Oracle Database Advanced Application Developer's Guide for information about calling external procedures
EXTERNAL The EXTERNAL
clause is an alternative way of declaring a C method. In most cases, Oracle recommends that you use the LANGUAGE
C
syntax. However, EXTERNAL
is required if a default argument is used as one of the parameters or if one of the parameters uses a PL/SQL data type that must be mapped (for example, Boolean). EXTERNAL
causes the PL/SQL layer to be loaded so that the parameters can be properly evaluated.
Creating a Procedure: Example The following 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 call the procedure, you must specify the employee_id
of the employee to be removed.
The procedure uses a DELETE
statement to remove from the employee
s table the row of employee_id
.
See Also:
Creating a Package Body: Example to see how to incorporate this procedure into a packageIn the following 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 );
See Also:
Oracle Database SQL Language Reference for information about the CALL
statement)
Oracle Database Advanced Application Developer's Guide for more information about call specifications