CREATE PROCEDURE

CREATE PROCEDURE — define a stored procedure

Syntax

CREATE 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 (see EXECUTE, CALL). The SQL may contain Data Manipulation Language (DML) and some Data Definition Language (DDL) statements ( SQL Statements Supported in Procedures, UDFs and Triggers) plus additional statements used to control the flow of execution or handle/report error conditions (see NuoDB SQL Procedural Language).

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. To replace the current definition of an existing procedure use ALTER PROCEDURE. To remove the stored procedure use DROP PROCEDURE. (See ALTER PROCEDURE, DROP PROCEDURE.) 

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.

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 NuoDB 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.

Note: 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 NuoDB 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

Note: 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

One or more of the examples below make use of tables defined in the HOCKEY schema, which is a schema defined in the test database. The test database is created when the QuickStart process is run. See Running the SQL QuickStart.