CREATE PROCEDURE

CREATE PROCEDURE — create a stored procedure

Syntax

CREATE [OR REPLACE] PROCEDURE [schema.]name
    [ ( [ [ IN | INOUT | OUT ] parameter_definition
          [ ,[ IN | INOUT | OUT ] parameter_definition ]... ] ) ]
    [ RETURNS
       [TABLE] table_name ( column_name column_type [ ,column_name column_type ]... )
        [ ,[TABLE] table_name ( column_name column_type [ ,column_name column_type ]... ) ]... ]
    [ LANGUAGE SQL | LANGUAGE JAVA ]
    [ SECURITY INVOKER | SECURITY DEFINER ]
{
    EXTERNAL 'sp_routine'
|
    AS
        sql_code_block
    END_PROCEDURE;
}

Description

The CREATE PROCEDURE statement is used to create routines that perform a series of SQL statements when invoked by a single EXECUTE or CALL statement. The SQL may contain Data Manipulation Language (DML) and some Data Definition Language (DDL) statements, and additional statements used to control the flow of execution or handle/report error conditions, see SQL Procedural Language.

When the OR REPLACE clause is used for an existing procedure name, the effect is that of dropping the existing procedure and creating a new one with the given specification. OR REPLACE does not have any effect on the command if a procedure with the specified name does not already exist.

CREATE PROCEDURE may also be used to create stored procedures written in Java, see Language below and Using Embedded Java Stored Procedures.

Only users assigned the system DBA role can create a stored procedure, see GRANT. If schema_name is provided, then the procedure is created in the specified schema. Otherwise, it is created in the current schema. The name of the procedure must not match any existing procedure in the same schema.

The user that creates the stored procedure becomes the owner of that stored procedure and must use the GRANT statement to give ALTER and EXECUTE privileges to other users in order for them to be able to change or execute the stored procedure. Any user that is granted ALTER to a stored procedure can also DROP the stored procedure. If a user drops and recreates a stored procedure, the new stored procedure is not the same entity as the old; that user will now be the new owner of the stored procedure. That user must GRANT all privileges to other users.

When using CREATE OR REPLACE statements, and the same database object is created concurrently from two different transactions, the following error may be returned:

duplicate value in unique index ...

In the event that a user-defined function is called by an expression-based index, any attempt to drop or modify the function will complete with an error warning but the index will not be recreated.

Arguments

If the procedure declares one or more arguments, they will appear as variables to all the statements of the procedure. Each argument to the stored procedure is declared as IN, INOUT, or OUT followed by the argument name and its type.

IN declares the argument as an input variable passed to the procedure. The final value of an IN parameter is instead discarded.

You can specify a result set as an IN parameter to a stored procedure. See Using a Result Set as Input to a Stored Procedure.

INOUT declares the argument as an input and output variable passed to the procedure. It can be modified by the procedure and returned out again to the caller.

OUT declares the argument as an output variable that can be modified by the procedure and returned out to the caller.

An argument that doesn’t specify the direction is assumed to be a IN argument.

See arg_name and arg_type parameters.

Return Values

A stored procedure can optionally return one or more result sets. A result set is a temporary, in-memory table containing the results being returned from the stored procedure. To designate that the stored procedure is to return one or more result sets, use RETURNS followed by a comma separated list of table definitions, table_name ( field [,field]…), that represent the result sets being returned. See table_name, column_name and column_type parameters below.

If a procedure is declared as RETURNS table_name…, a temporary, in-memory table with the specified structure will appear in the database, and its content will be returned to the caller as an output result set. This temporary, in-memory table cannot be accessed outside of the stored procedure.

If the current connection already has a temporary, in-memory table defined with the same name, references to such table from inside the stored procedure will actually be referencing this RETURNS table.

If the current schema already has a permanent table defined with the same name, it must be accessed within the stored procedure by using its fully qualified name (i.e. schema.tablename).

See Returning a Result Set from a Stored Procedure and Returning Multiple Result Sets from a Stored Procedure for more information and examples.

Language

The language for a stored procedure, user defined function or trigger may be SQL or Java. This is specified by declaring the procedure, function or trigger with LANGUAGE SQL or LANGUAGE JAVA. The default language, if none is specified, is SQL. SQL stored procedures, UDFs, and triggers are defined by sql_code_block. See SQL Procedural Language for more information.

Stored procedures, user defined functions and triggers written in Java must use the EXTERNAL keyword. See Using Embedded Java Stored Procedures.

Security

You may declare a stored procedure or user defined function to work in two different ways with respect to the calling user’s privileges on database objects affected. The procedure or function may be declared in one of two ways:

SECURITY INVOKER

Checks the privileges of the calling user. If a stored procedure or user defined function is inserting, selecting, and/or updating one or more tables, the calling user has to have the appropriate privilege for the operation on each table.

SECURITY DEFINER

Specifies that the stored procedure or user defined function is to be executed with the privileges of the user that created it. For example, the calling user may not have privileges to SELECT or INSERT INTO tables when referencing them outside the stored procedure or user defined function, but will be allowed access when executing the stored procedure or function. If a security level is not specified, this is the default.

Whoever gets the execution rights on the procedure can access the same tables/schema/sequence/etc.. of the creator of the procedure, even if the current user is still the invoker.

External Definition

Declaring a stored procedure as EXTERNAL is done to describe a dynamically loadable Java stored procedure that is to be executed. For LANGUAGE JAVA, the EXTERNAL stored procedure defined by sp_routine must be in the format classid:fully_qualified_class.method, where classid is the ID used in the CREATE JAVACLASS command used to upload to the NuoDB server the JAR with the defined code. See CREATE JAVACLASS.

SQL Procedural Language - `CREATE PROCEDURE` body

If defining this stored procedure in SQL, sql_code_block is one or more SQL statements. See SQL Procedural Language for more information.

System Tables

The body of the procedure is stored in SYSTEM.PROCEDURES in the PROCEDURETEXT column. See PROCEDURES System Table Description and Example 1 below.

Parameters

schema: Name of the schema in which the procedure will be created. The default is the current schema as defined by the USE command. If schema is provided, and the schema does not exist, it will be created.
name: Optionally schema-qualified name of the stored procedure. If the schema does not exist, it will be created. Procedures cannot be created in the SYSTEM schema. This schema is reserved for database metadata.
parameter_definition: This is typically:

arg_name arg_type

where:

arg_name specifies the variable name for the input and/or output parameter to the stored procedure and arg_type specifies the data type of the input and/or output parameter for the stored procedure.

When used as an IN table, the, parameter_definition can be:

table_name(column_name column_type [,column_name column_type]… )

where:

table_name Specifies the name of the temporary, in-memory table containing the result set being returned from the stored procedure. See Using a Result Set as Input to a Stored Procedure for more information.

column_name: Column name of a column in the temporary, in-memory table containing the result set being returned from the stored procedure.
column_type: Column data type of a column in the temporary, in-memory table containing the result set being returned from the stored procedure. This cannot be defined by an SQL domain. Column constraints cannot be defined, such as NOT NULL, DEFAULT values, etc.

When creating or altering multi-statement stored procedures in nuosql, the SET DELIMITER command must be used prior to defining the stored procedure. See SET. This new delimiter is used to indicate the end of the stored procedure definition and will execute the CREATE PROCEDURE statement. See the examples below.

Examples

Example 1: Arguments appearing as variables in all statements defined in the procedure

SET DELIMITER @
CREATE PROCEDURE prc_player_info(IN inPlayer INTEGER) AS
   VAR outParm STRING;
   IF (inPlayer > 99)
       THROW 'Player Numbers are 0 to 99';
   END_IF;
   outParm =  (SELECT name||' plays position '||position FROM hockey.hockey WHERE number = inPlayer);
   THROW outParm;
END_PROCEDURE
@
SET DELIMITER ;

EXECUTE prc_player_info(100);
Procedure HOCKEY.PRC_PLAYER_INFO, Player Numbers are 0 to 99
EXECUTE prc_player_info(37);
Procedure HOCKEY.PRC_PLAYER_INFO, PATRICE BERGERON plays position Forward

SET OUTPUT VERTICAL;
SELECT * FROM system.procedures;
==================================== Row #1 ====================================
SCHEMA: HOCKEY
PROCEDURENAME: PRC_PLAYER_INFO
CREATOR: DBA
PROCEDURETYPE: 0
PROCEDURETEXT: (IN inPlayer INTEGER) AS    VAR outParm STRING;
IF (inPlayer > 99)        THROW 'Player Numbers are 0 to 99';    END_IF;
outParm =  (SELECT name||' plays position '||position FROM hockey.hockey
WHERE number = inPlayer);    THROW outParm; END_PROCEDURE ;

VERSION: 1

Example 2: Using the RETURNS option to return a result set of data from a database table

The INSERT statement must be a valid insert statement as supported by NuoDB. The values provided can be either a select statement, a values list, or a combination of both (see INSERT for examples of valid syntax).

/* Using select statement as values for INSERT statement */
DROP PROCEDURE IF EXISTS prc_player_position;
SET DELIMITER @
CREATE PROCEDURE prc_player_position (IN in_pos STRING)
      RETURNS tmp_tab ( number INTEGER,
                        name   STRING,
                        team   STRING)
AS
      INSERT INTO tmp_tab SELECT number, name, team
                           FROM hockey.hockey
                           WHERE position = in_pos
                            ORDER BY number;
END_PROCEDURE
@
SET DELIMITER ;
EXECUTE prc_player_position('Defense');
 NUMBER        NAME         TEAM
 ------- ----------------- ------
   21    ANDREW FERENCE    Bruins
   27    DOUGIE HAMILTON   Bruins
   33    ZDENO CHARA       Bruins
   44    DENNIS SEIDENBERG Bruins
   45    AARON JOHNSON     Bruins
   54    ADAM MCQUAID      Bruins
   55    JOHNNY BOYCHUK    Bruins

/* Using a combination of select statement and a values column list to provide
/* values for INSERT.
/* In this case, using a table alias in the select statement is required */

DROP PROCEDURE IF EXISTS prc_player_position;
SET DELIMITER @
CREATE PROCEDURE prc_player_position (IN in_pos STRING)
      RETURNS tmp_tab ( id       INTEGER,
                        number   INTEGER,
                        name     STRING,
                        position STRING,
                        team     STRING,
                        date     DATE,
                        active   BOOLEAN)
AS
      INSERT INTO tmp_tab SELECT h.*, current_date, 'TRUE'
                           FROM hockey.hockey h
                           WHERE h.position = in_pos
                           ORDER BY h.id;
END_PROCEDURE
@
SET DELIMITER ;
EXECUTE prc_player_position('Defense');
 ID  NUMBER        NAME        POSITION   TEAM     DATE    ACTIVE
 --- ------- ----------------- --------- ------ ---------- -------
 15    55    JOHNNY BOYCHUK     Defense  Bruins 2015-04-23  TRUE
 16    33    ZDENO CHARA        Defense  Bruins 2015-04-23  TRUE
 17    21    ANDREW FERENCE     Defense  Bruins 2015-04-23  TRUE
 18    27    DOUGIE HAMILTON    Defense  Bruins 2015-04-23  TRUE
 19    45    AARON JOHNSON      Defense  Bruins 2015-04-23  TRUE
 20    54    ADAM MCQUAID       Defense  Bruins 2015-04-23  TRUE
 21    44    DENNIS SEIDENBERG  Defense  Bruins 2015-04-23  TRUE

Example 3: Java stored Procedure Example: See Using Embedded Java Stored Procedures for a fuller treatment of creating and executing Java stored procedures in NuoDB.

DROP PROCEDURE IF EXISTS myproc;
CREATE JAVACLASS firstprocid FROM 'test.jar';
CREATE PROCEDURE myproc(str string)
    LANGUAGE JAVA EXTERNAL 'firstprocid:com.mycompany.MyFirstJavaProc.testMethod';
CALL myproc('Alberto');
java.sql.SQLException: Hello, Alberto

For more information and examples, see SQL Procedural Language.