CREATE PROCEDURE

The CREATE PROCEDURE statement is used to create or replace standalone stored procedures or call specifications.

A standalone stored procedure is a procedure stored in the database that executes specific operations.

Prerequisites

To create or replace a standalone stored procedure in the current 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.

Syntax

The syntax of create_procedure is as follows:

CREATE [ OR REPLACE ]
PROCEDURE plsql_procedure_source

The syntax of plsql_procedure_source is as follows:

[ schema. ] procedure_name
[ ( parameter_declaration [, parameter_declaration ]... ) ] [ sharing_clause ]
[ ( default_collation_option | invoker_rights_clause | accessible_by_clause)... ]
{ IS | AS } { [ declare_section ] body | call_spec } ;

Semantics

Examples

Create a stored procedure named userlogin.

CREATE TABLE loghistory
      (userid VARCHAR2(20),
      logdate DATE DEFAULT SYSDATE);

CREATE OR REPLACE PROCEDURE userlogin
      IS
  BEGIN
      INSERT INTO loghistory (userid) VALUES (USER);
  END;
/