Name

CREATE PROCEDURE

Synopsis

CREATE [OR REPLACE] PROCEDURE
[schema.]procedure_name
[(argument [IN | OUT | IN OUT]
datatype[,argument[IN | OUT | IN OUT]
datatype.)]
{IS | AS}
pl/sql_subprogram

Creates a standalone stored procedure ( procedure_name).

Keywords

OR REPLACE

Specifies that if the procedure exists, it is to be replaced.

argument

Specifies the name of an argument to the procedure.

IN

Specifies that a value must be supplied for argument when calling the procedure.

OUT

Specifies that the procedure will pass a value for argument back to the calling environment.

IN OUT

Specifies that a value must be supplied for argument when calling the procedure, and that the procedure will pass a value for argument back to the calling environment.

pl/sql_subprogram

Specifies the procedure code, written in PL/SQL. You must specify either the IS or AS keyword.

Notes

This command creates a procedure as a standalone object in the specified or default schema. To include the procedure in a package, see the CREATE PACKAGE command. You must have the CREATE PROCEDURE or CREATE ANY PROCEDURE privilege to issue this command.

Example

The following example creates a procedure named “post” in scott’s schema:

CREATE PROCEDURE scott.post (acct IN NUMBER, amt IN NUMBER)
AS BEGIN
     UPDATE account_master
     SET bal = bal + amt
     WHERE account_no = acct;
END;

Get Oracle Database Administration: The Essential Refe now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.