MemSQL supports procedural SQL language extensions as of version 6.0. These extensions provide familiar mechanisms for SQL developers and database administrators to encapsulate custom programmatic logic, namely:

• Stored Procedures (SPs)
• User-Defined Scalar-Valued Functions (UDFs)
• User-Defined Table-Valued Functions (TVFs)
• User-Defined Aggregate Functions (UDAFs)

Performance

All types of MemSQL extensions are optimized for high performance. Once created, each is compiled directly to machine code using MemSQL’s unique code generation techniques. This process ensures that subsequent execution of a procedure or function is highly performant, as a node can execute the function without requiring interpretation or a multi-step process of intermediate language compilation.

Additionally, stored procedures and table variables benefit from further optimizations for MemSQL’s distributed execution. SQL statements within stored procedures operate with full parallelism across the MemSQL cluster. QUERY type variables are processed with a lazy evaluation strategy. The underlying SELECT statement associated with a QUERY type variable is only executed when the rows of the variable need to be returned. UDFs are also processed in parallel on different data partitions.

Control Flow

All common control flow statements are supported, including:

• Conditional control, such as IF, ELSE, and ELSIF
• Iterative control, such as LOOP, FOR, WHILE, EXIT, CONTINUE, and loop labels

For more information, see the Control Flow Statements topic.

Additional Data Types

Stored procedures and UDFs can use ARRAY and RECORD types by accepting them as input parameters, creating and manipulating them in the procedure or UDF definition, and optionally returning them as a return type. Stored procedures can also use QUERY types.

Stored Procedures

Stored procedures can accept input parameters, query tables using SQL statements, call UDFs, define custom logic using control flow statements and variable assignment, and optionally return a value. Stored procedures can also be called across databases. See the CREATE PROCEDURE topic for more information.

User-Defined Scalar-Valued Functions (UDFs)

UDFs can accept input parameters, call other UDFs, define custom logic using control flow statements and variable assignment, and return a value. See the CREATE FUNCTION (UDF) topic for more information.

User-Defined Table-Valued Functions (TVFs)

TVFs can accept input parameters, execute a single SELECT statement, and return the result as a table-typed value. For more information, see CREATE FUNCTION (TVF).

User-Defined Aggregate Functions (UDAFs)

UDAFs support creation of custom aggregation logic, beyond the built-in aggregate functions supplied by MemSQL. See the CREATE AGGREGATE topic for more information.

See Also

• Procedural SQL Reference

Was this Article Helpful?

Thanks For Your Feedback!