CREATE PROCEDURE

You can use the CREATE PROCEDURE statement to create or replace an independent stored procedure or a call specification.

An independent stored procedure is a procedure stored in the database. It is a subprogram that executes specific operations.

Prerequisites

To create or replace an independent stored procedure in your own schema, you must have the CREATE PROCEDURE system privilege. To create or replace an independent stored procedure in the schema of other users, 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;
/