SingleStore DB (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:
- Stored Procedures (SPs)
- User-Defined Scalar-Valued Functions (UDFs)
- User-Defined Table-Valued Functions (TVFs)
- User-Defined Aggregate Functions (UDAFs)
All types of SingleStore DB 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 SingleStore DB 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.
All common control flow statements are supported, including:
- Conditional control, such as
- Iterative control, such as
CONTINUE, and loop labels
For more information, see the Control Flow Statements topic.
Additional Data Types
Stored procedures and UDFs can use
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
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 singlestore_docs_example; CREATE DATABASE singlestore_docs_example; use singlestore_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);
+------+-------------+ | 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
Other examples of static parameterized queries are shown below.
SELECT * FROM order WHERE amount >= <parameter>
INSERT INTO item (name) VALUES (<parameter>)
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 singlestore_docs_example; CREATE DATABASE singlestore_docs_example; use singlestore_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');
+------+-------------+ | 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
SELECT id, name FROM part <WHERE clause>
You can also substitute a MPSQL variable for a
SELECT <rest of SELECT statement>
Example Dynamic SQL Use Cases
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.
You have a feature tracking database where you track the features that are installed for each customer. You [write a stored procedure] (/v7.3/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.
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.
|Run a non-
||Use EXECUTE IMMEDIATE.|
|Run a dynamic
||Use EXECUTE IMMEDIATE with ECHO SELECT.|
|Run a dynamic
||Use COLLECT with a query type variable.|
|Run a dynamic
||Use SCALAR with a
See this topic for an explanation of the Definer Security Model and information on how to write secure stored procedures.