PROCEDURE

A stored procedure is a software module that can be called from a client, another procedure, function, executable block or trigger.Stored procedures are written in procedural SQL (PSQL) or defined using a UDR (User-Defined Routine).Most SQL statements are available in PSQL as well, sometimes with limitations or extensions.Notable limitations are the prohibition on DDL and transaction control statements in PSQL.

Stored procedures can have many input and output parameters.

`CREATE PROCEDURE`

Creates a stored procedure

Available in

DSQL, ESQL

Syntax

CREATE PROCEDURE procname [ ( [ <in_params> ] ) ]
  [RETURNS (<out_params>)]
  {<psql_procedure> | <external-module-body>}

<in_params> ::= <inparam> [, <inparam> ...]

<inparam> ::= <param_decl> [{= | DEFAULT} <value>]

<out_params> ::= <outparam> [, <outparam> ...]

<outparam> ::= <param_decl>

<value> ::= {<literal> | NULL | <context_var>}

<param_decl> ::= paramname <domain_or_non_array_type> [NOT NULL]
  [COLLATE collation]

<type> ::=
    <datatype>
  | [TYPE OF] domain
  | TYPE OF COLUMN rel.col

<domain_or_non_array_type> ::=
  !! See Scalar Data Types Syntax !!

<psql_procedure> ::=
  [SQL SECURITY {INVOKER | DEFINER}]
  <psql-module-body>

<psql-module-body> ::=
  !! See Syntax of Module Body !!

<external-module-body> ::=
  !! See Syntax of Module Body !!

Table 1. `CREATE PROCEDURE` Statement Parameters
Parameter	Description
procname	Stored procedure name.The maximum length is 63 characters.Must be unique among all table, view and procedure names in the database
inparam	Input parameter description
outparam	Output parameter description
literal	A literal value that is assignment-compatible with the data type of the parameter
context_var	Any context variable whose type is compatible with the data type of the parameter
paramname	The name of an input or output parameter of the procedure.The maximum length is 63 characters.The name of the parameter must be unique among input and output parameters of the procedure and its local variables
collation	Collation

The CREATE PROCEDURE statement creates a new stored procedure.The name of the procedure must be unique among the names of all stored procedures, tables, and views in the database.

CREATE PROCEDURE is a compound statement, consisting of a header and a body.The header specifies the name of the procedure and declares input parameters and the output parameters, if any, that are to be returned by the procedure.

The procedure body consists of declarations for any local variables, named cursors, and subroutines that will be used by the procedure, followed by one or more statements, or blocks of statements, all enclosed in an outer block that begins with the keyword BEGIN and ends with the keyword END.Declarations and embedded statements are terminated with semicolons (‘;’).