You are here: SQL Development > SQL Procedures, Functions, and Triggers > NuoDB SQL Procedural Language

NuoDB SQL Procedural Language

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

Note: Starting with NuoDB version 2.6, 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 is assumed to be referencing a database object defined in the same schema where the stored procedure, function or trigger was created.

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

CREATE PROCEDURE

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

CREATE FUNCTION

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

See the following topics:

SQL Procedural Variables

SQL Procedural Supported Statements

SQL Procedural Conditional Statements

SQL Procedural Control Flow

SQL Procedural Dynamic SQL

SQL Comments