NuoDB SQL Procedural Language

NuoDB supports a SQL procedural language for use in stored procedures, user defined functions (UDFs), and triggers.

Most of the statements of the procedural language can be used in standalone statements without the need to create a stored procedure.

Overview

The NuoDB SQL procedural language allows you to create routines that perform a series of SQL statements to be executed when called as a stored procedure (See CALL, EXECUTE, CREATE PROCEDURE), user defined function (CREATE FUNCTION), or when initiated by a trigger (CREATE TRIGGER). The SQL statements that make up the NuoDB SQL procedural language are described in this section.

Alternatively, stored procedures, UDFs and triggers can be written in Java. See Using Embedded Java Stored Procedures.

See Working with Transactions for a description of NuoDB SQL default transaction behavior in a stored procedure and how to override this default behavior.

The schema where the stored procedure, function, or trigger is created does not have to be the schema where the procedure, function or trigger is invoked from. They can be invoked from any schema by using its schema qualified name. Any database object referenced in a stored procedure, function or trigger that is not referenced by its schema qualified name will attempt to locate the object in the following order:

  1. Return tables (SQL procedures only)

  2. The schema of the procedure, function, or trigger

  3. Temporary tables

  4. The DUAL system pseudo table

This section describes sql_code_block in the syntax for CREATE PROCEDURE, CREATE FUNCTION, and CREATE TRIGGER as referenced below.

CREATE PROCEDURE

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;
}

CREATE FUNCTION

CREATE [OR REPLACE] FUNCTION [schema.]name
    [ ( arg_name arg_type [ ,arg_name arg_type ]... ) ]
    RETURNS
    {
        return_data_type
    |
        TABLE table_name ( column_name column_type [, column_name column_type ]... )
    }
    [ DETERMINISTIC | NOT DETERMINISTIC ]
    [ LANGUAGE SQL | LANGUAGE JAVA ]
    [ SECURITY INVOKER | SECURITY DEFINER ]
{
    EXTERNAL 'jar_id:method_name'
|
    AS
        sql_code_block
    END_FUNCTION;
}

CREATE TRIGGER

CREATE [OR REPLACE] TRIGGER trigger_name
    {[FOR] [schema.]table_name|ON DATABASE }
    [[ BEFORE | AFTER ] event]
    [ POSITION number ]
    [ ACTIVE | INACTIVE ]
    [ LANGUAGE { SQL | JAVA } ]
    [FOR EACH ROW|FOR EACH STATEMENT]
{
    EXTERNAL 'trigger_routine'
|
    AS
        sql_code_block
    END_TRIGGER;
}