create procedure

Create a stored procedure

Syntax

create_procedure:
          CREATE PROCEDURE [database_name.]proc_name [(proc_arg_decl[, proc_arg_decl]...)]
          [LANGUAGE SQL] 
          [[NOT] DETERMINISTIC] 
          [CONTAINS SQL | {MODIFIES | READS} SQL DATA] 
          sqlproc_stmt

proc_arg_decl:
          [IN | OUT | INOUT] arg_name var_type

var_type:
          non_numeric_vartype | numeric_vartype

non_numeric_vartype:
          {CHAR | VARCHAR} [(length_num)]
     |    {BINARY | VARBINARY} (length_num)
     |    DATE | TIME | TIMESTAMP
     |    UUID
     |    ROWID

numeric_vartype:
          REAL | FLOAT | DOUBLE
     |    BOOLEAN | TINYINT | SMALLINT | INTEGER | BIGINT
     |    DECIMAL [(precision_num [, scale_num)]

Description

The CREATE PROCEDURE statement is used to define a stored procedure named proc_name. The name of the database (database_name) with which the procedure is to be associated can optionally be specified. If not specified, then the stored procedure will be associated with either the database accessed by the SQL statements contained in it or, if there are none, then the most recently opened database. Procedure names associated with the same database must be unique.

The optional LANGUAGE SQL clause specifies that the procedure is coded in the SQL PL which is the only available choice in RDM.

A DETERMINISTIC procedure is one which returns the same output argument values every time the procedure is called with the same input argument values. Specifying this clause does not currently affect the operational behavior of RDM stored procedures. A procedure is non-deterministic by default.

The particular type of access performed by the stored procedure is indicated by the MODIFIES SQL DATA or READS SQL DATA clauses. Inclusion of one of these clauses will cause RDM SQL to ensure that the body of the procedure only contains SQL statements that conform to the clause. The READS SQL DATA clause is required if the procedure contains SELECT statements that access a database. The MODIFIES SQL DATA clause allows both reading and modification of the database and is required if the procedure contains INSERT, UPDATE, or DELETE statements. Omission of this clause means that SQL statements that access a database are not allowed in the procedure.

The CONTAINS SQL clause can be specified for conformance to the SQL standard. It indicates that the procedure is programmed in SQL. The alternative option (NO SQL) is not supported as it is irrelevant here as SQL statements are the only option available in RDM SQL. The CONTAINS SQL is the default if no clause is specified.

Procedure arguments can be declared to be input only (IN), output only (OUT), or both input and output (INOUT). Input only argument variables cannot be assigned a value. Output only variables must first be assigned a value before any statements that reference that argument's value. An input/output argument's value can be referenced as well as being assigned a new value.

Procedure arguments can be declared to be any of the basic SQL data types except LONG VARCHAR or LONG VARBINARY. There is no internal difference between a CHAR and a VARCHAR or BINARY and VARBINARY. The length specifies the maximum length of the CHAR or VARCHAR and must be specified for an OUT or INOUT argument. For an IN CHAR/VARCHAR argument the length is optional and when not specified indicates that the input argument can be a string of any length.

The sqlproc_stmt and statement_list possibilities are described in the next section.

Note that the CREATE PROCEDURE statement is not transactional so that the stored procedure is created immediately upon successful execution of the statement.

Example

create procedure n_oldest_authors(in nrows smallint)begin   declare rowno smallint default 1;   get_authors: for        select  			 full_name,            if(yr_died = 0, year(current_date()), yr_died)-yr_born author_age        from author order by 2 desc   do        select rowno, full_name, author_age;        if rowno > nrows then            leave get_authors;        end if;        set rowno = rowno + 1;   end for;end;