Creating Procedures

CREATE PROCEDURE Statement

You create new procedures with the CREATE PROCEDURE statement, which may declare a list of parameters and must define the actions to be performed by the standard PL/SQL block. The CREATE clause enables you to create stand-alone procedures that are stored in an Oracle database.

• PL/SQL blocks start with BEGIN, optionally preceded by the declaration of local variables. PL/SQL blocks end with either "END" or "END procedure_name".
• The REPLACE option indicates that if the procedure exists, it is dropped and replaced with the new version created by the statement.

Syntax

CREATE [OR REPLACE] PROCEDURE procedure_name 
    [(parameter1 [mode] datatype1, 
      parameter2 [mode] datatype2, ...)] 
IS|AS 
    [local_variable_declarations; …] 
BEGIN 
    -- actions; 
    -- SQL statements
    -- PL/SQL statements
END [procedure_name];