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 the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.