Back
Outdated Version

You are viewing an older version of this section. View current production version.

Procedural Extensions

MemSQL Procedural Extensions (MPSQL) are available beginning in MemSQL version 6.0. These extensions provide familiar mechanisms for SQL developers and database administrators to encapsulate custom programmatic logic, namely:

Within stored procedures, you can write flexible queries that use static parameterized SQL and dynamic SQL.

Performance

All types of SingleStore DB (MemSQL) extensions are optimized for high performance. Once created, each is compiled directly to machine code using SingleStore DB’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 SingleStore DB’s distributed execution. SQL statements within stored procedures operate with full parallelism across the 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 SingleStore DB. See the CREATE AGGREGATE topic for more information.

Static Parameterized SQL

Within stored procedures, you can write queries that contain MPSQL variables representing parameters. These queries are known as static parameterized queries.

In the following example, SELECT id, description FROM product ORDER BY id LIMIT row_count; is a static parameterized query. row_count is an MPSQL variable representing a parameter.

DROP DATABASE IF EXISTS memsql_docs_example;
CREATE DATABASE memsql_docs_example;
use memsql_docs_example;
CREATE TABLE product(id INT, description TEXT);

INSERT INTO product VALUES (1, 'white paper'), (2, 'blue pen'), (3, 'black pen');

DELIMITER //
CREATE PROCEDURE get_product_data(row_count INT) RETURNS QUERY
	(id INT, description TEXT) AS
DECLARE
  qry QUERY(id INT, description TEXT) = SELECT id, description
  	FROM product ORDER BY id LIMIT row_count;
BEGIN
  RETURN qry;
END
//
DELIMITER ;

ECHO get_product_data(2);

Output:

+------+-------------+
| id   | description |
+------+-------------+
|    1 | white paper |
|    2 | blue pen    |
+------+-------------+

Within stored procedures, you define static queries using the QUERY data type.

Parameters in static queries always represent constants. In the above example, the variable row_count evaluates to the constant value 2.

Other examples of static parameterized queries are shown below.

SELECT * FROM order WHERE amount >= <parameter>

INSERT INTO item (name) VALUES (<parameter>)

Dynamic SQL

Dynamic queries give you the most flexibility in defining SQL statements.

Before proceeding to the rest of this section, see the previous section to understand how static parameterized queries work.

Using MPSQL variables, you can substitute strings anywhere at run time. In the following example, the query SELECT id, name FROM customer ORDER BY <column name> is built dynamically using a string. The EXECUTE IMMEDIATE statement uses the string to run the query.

SET sql_mode = 'PIPES_AS_CONCAT';

DROP DATABASE IF EXISTS memsql_docs_example;
CREATE DATABASE memsql_docs_example;
use memsql_docs_example;
CREATE TABLE product(id INT, description TEXT);

INSERT INTO product VALUES (1, 'white paper'), (2, 'blue pen'),
	(3, 'black pen');

DELIMITER //
CREATE PROCEDURE get_product_data(col TEXT) AS
DECLARE
  str TEXT;
BEGIN
  str = 'ECHO SELECT id, description FROM product ORDER BY ' || col;
  EXECUTE IMMEDIATE str;
END
//
DELIMITER ;

CALL get_product_data('description');

Output:

+------+-------------+
| id   | description |
+------+-------------+
|    3 | black pen   |
|    2 | blue pen    |
|    1 | white paper |
+------+-------------+

More examples of dynamic queries are shown below. These queries cannot be written as static parameterized queries because column names and table names are not constant values.

SELECT <column name> FROM location`

INSERT INTO <table name> (id) VALUES (1),(2),(3)

With dynamic queries, you’re not limited to using MPSQL variables that represent column and table names. For instance, you can substitute a MPSQL variable for a WHERE clause:

SELECT id, name FROM part <WHERE clause>

You can also substitute a MPSQL variable for a SELECT statement:

SELECT <rest of SELECT statement>

Example Dynamic SQL Use Cases

  1. You have an application that takes daily snapshots of the data in one table. You write a stored procedure that takes the table name as a parameter. Using this parameter, you write dynamic queries to analyze the data for one day.

  2. You have a feature tracking database where you track the features that are installed for each customer. You [write a stored procedure] (/v7.1/reference/sql-reference/procedural-sql-reference/execute-immediate/#example-1-feature-tracking) that takes a customer ID as a parameter. Using dynamic SQL, you create a FEATURE table for the customer (with the customer ID) and populate this table with the default feature set.

  3. You want to write a table analyzer utility that returns the number of rows in a table as well as the number of unique values in each column of the table. Using dynamic SQL, you write a stored procedure that accepts the table name as a parameter and runs the calculations.

Writing Dynamic SQL

When you write dynamic SQL, you will often follow one or more of the approaches outlined below.

Task Approach
Run a non-SELECT dynamic query such as an UPDATE Use EXECUTE IMMEDIATE.
Run a dynamic SELECT query and output the results to the console or a client application. Use EXECUTE IMMEDIATE with ECHO SELECT.
Run a dynamic SELECT query that returns multiple rows. Iterate through each row and perform an action on the row. Use COLLECT with a query type variable.
Run a dynamic SELECT query that returns only one row and one column. Use SCALAR with a QUERY type variable.

Security

See this topic for an explanation of the Definer Security Model and information on how to write secure stored procedures.

See Also

Questions?

Ask the Community, find solutions in the Troubleshooting Overview, get Self-Paced Training or learn more about our Support Plans.