CREATE PROCEDURE

Purpose

Prerequisites

To create a procedure in your own schema, you must have CREATE PROCEDURE system privilege. To create a procedure in another schema, you must have CREATE ANY PROCEDURE system privilege. To replace a procedure in another schema, you must have REPLACE ANY PROCEDURE system privilege.

If you are using Trusted Oracle7 in DBMS MAC mode, you can only create a procedure in another user's schema if your DBMS label dominates the creation label of the other user.

To create a procedure, you must be using Oracle7 with PL/SQL installed. For more information, see PL/SQL User's Guide and Reference.

Syntax

Keywords and Parameters

OR REPLACE

recreates the procedure if it already exists. You can use this option to change the definition of an existing procedure without dropping, recreating, and regranting object privileges previously granted on it. If you redefine a procedure, Oracle7 recompiles it. For information on recompiling procedures, see the ALTER PROCEDURE command .

Users who had previously been granted privileges on a redefined procedure can still access the procedure without being regranted the privileges.

schema

is the schema to contain the procedure. If you omit schema, Oracle7 creates the procedure in your current schema.

procedure

is the name of the procedure to be created.

argument

is the name of an argument to the procedure. If the procedure does not accept arguments, you can omit the parentheses following the procedure name.

IN

specifies that you must specify a value for the argument when calling the procedure.

OUT

specifies that the procedure passes a value for this argument back to its calling environment after execution.

IN OUT

specifies that you must specify a value for the argument when calling the procedure and that the procedure passes a value back to its calling environment after execution.

If you omit IN, OUT, and IN OUT, the argument defaults to IN.

datatype

is the datatype of an argument. As long as no length specifier is used, an argument can have any datatype supported by PL/SQL. For information on PL/SQL datatypes, see PL/SQL User's Guide and Reference.

Datatypes are specified without a length, precision, or scale. For example, VARCHAR2(10) is not valid, but VARCHAR2 is valid. Oracle7 derives the length, precision, or scale of an argument from the environment from which the procedure is called.

pl/sql_subprogram_body

is the definition of the procedure. Procedure definitions are written in PL/SQL. For information on PL/SQL, including how to write a PL/SQL subprogram body, see

PL/SQL User's Guide and Reference.

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.

Usage Notes

With PL/SQL, you can group multiple SQL statements together with procedural PL/SQL statements similar to those in programming languages such as Ada and C. With the CREATE PROCEDURE command, you can create a procedure and store it in the database. You can call a stored procedure from any environment from which you can issue a SQL statement.

Stored procedures offer you advantages in the following areas:

• development

• integrity

• security

• performance

• memory allocation

When you create a procedure in Trusted Oracle7, it is labeled with your DBMS label.

The CREATE PROCEDURE command creates a procedure as a stand-alone schema object. You can also create a procedure as part of a package. For information on creating packages, see the CREATE PACKAGE command .

Example

The following statement creates the procedure CREDIT in the schema SAM:

CREATE PROCEDURE sam.credit (acc_no IN NUMBER, amount IN NUMBER) 
	AS BEGIN 
			UPDATE accounts 
				SET balance = balance + amount 
				WHERE account_id = acc_no; 
	END; 

The CREDIT procedure credits a specified bank account with a specified amount. When you call the procedure, you must specify the following arguments:

ACC_NO

This argument is the number of the bank account to be credited. The argument's datatype is NUMBER.

AMOUNT

This argument is the amount of the credit. The argument's datatype is NUMBER.

The procedure uses an UPDATE statement to increase the value in the BALANCE column of the ACCOUNTS table by the value of the argument AMOUNT for the account identified by the argument ACC_NO.

Related Topics