plsql Syntax


Example

CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter_name [IN | OUT | IN OUT] type [, ...])]
{IS | AS}
  < declarations >
BEGIN
  < procedure_body >
EXCEPTION                      -- Exception-handling part begins
  <exception handling goes here >
   WHEN exception1 THEN 
       exception1-handling-statements
END procedure_name;
  • procedure-name specifies the name of the procedure.
  • [OR REPLACE] option allows modifying an existing procedure.
  • The optional parameter list contains name, mode and types of the parameters. IN represents that value will be passed from outside and OUT represents that this parameter will be used to return a value outside of the procedure. If no mode is specified, parameter is assumed to be of IN mode.
  • In the declaration section we can declare variables which will be used in the body part.
  • procedure-body contains the executable part.
  • The AS keyword is used instead of the IS keyword for creating a standalone procedure.
  • exception section will handle the exceptions from the procedure. This section is optional.