SingleStore DB

Anonymous Code Blocks

Anonymous code blocks are unnamed blocks of SingleStore (MemSQL) Procedural SQL (MPSQL) code that are executed immediately. They allow you to create a code block without defining and calling a stored procedure and are useful in situations such as testing or debugging.

Syntax
DO [DECLARE variable_list] [ ...n ]
BEGIN
    procedure_body
END
Arguments
  • variable_list: An optional list of variables along with their data types. These variables are visible inside the anonymous code block, only.

  • procedure_body :See procedure_body in the Arguments section in CREATE PROCEDURE.

Remarks
  • Anonymous code blocks have the same syntax and semantics as stored procedures, except as noted in the remainder of this topic. The syntax and semantics for stored procedures is detailed in CREATE PROCEDURE.

  • The commands, functions, and data types that can be used inside of stored procedures, as shown in the Procedural SQL Reference, apply to anonymous code blocks also.

  • Anonymous code blocks cannot use input or output parameters.

  • Anonymous code blocks cannot return values.

  • As with stored procedures, anonymous code blocks can raise and handle exceptions. However, exceptions that are raised in an anonymous code block must be handled in the same code block. Unhandled exceptions generate an exception message and a stack trace that is returned to the client.

  • When a user session is terminated, anonymous code blocks will be removed (garbage collected). Garbage collection may not happen immediately. When such code blocks are removed, plans for queries and statements that were part of the anonymous code block are not dropped from the in-memory or the on-disk plancache.

  • A user who creates and executes an anonymous code block must have at least one permission on the database where the anonymous code block was created. This can be any database permission, such as USAGE (a minimal permission). Additionally, the user who executes an anonymous code block must have permission to execute any SQL queries, stored procedures, or user-defined functions that are in the anonymous code block.

  • SHOW PROCEDURES shows anonymous code blocks, only when run by a user with the SUPER permission. The same applies to the information_schema.ROUTINES table.

Caching of Anonymous Code Blocks

When an anonymous code block is first invoked, its shape is compiled and saved in the plancache if a plan with the same shape does not already exist in the plancache. The shape encapsulates the anonymous code block’s body. Subsequent invocations of the same anonymous code block (or a similar anonymous code block having the same shape) will be faster because the cached anonymous code block will be used; the anonymous code block is not recompiled.

A user session will reuse the plan for an anonymous code block that was generated during a different session, if the shape of the code block in both sessions is the same.

See Understanding the Plancache for details on how the plancache works, including the engine variables that control the expiration of plans.

Similarity to Temporary Stored Procedures

Anonymous code blocks are similar to temporary stored procedures. But as compared to an anonymous code blocks, temporary stored procedures can additionally be called multiple times, and can accept parameters.

Examples
A Simple Anonymous Code Block
DELIMITER //

DO BEGIN
    ECHO SELECT 1;
END //

DELIMITER ;
****
+---+
| 1 |
+---+
| 1 |
+---+
Use Case: Generating Test Data

Suppose you want to generate test order regions to test an application that processes orders. You populate a random region for each order in the orders_regions_test_data table, which is defined as follows:

CREATE TABLE orders_regions_test_data(order_region CHAR);

Use the following anonymous code block to populate the table with 1000 order regions. You know that in the future, you will not need to populate the table with more data.

DELIMITER //

DO DECLARE
    region_numeric INT;
    region CHAR;
BEGIN
    FOR i in 1 .. 1000 LOOP
        region_numeric = CEIL(RAND() * 3); /* Generate a random integer between 1 and 3 */
        /* Convert to the region prefix for east, central, or west region */
    	IF region_numeric = 1 THEN
      	    region = 'E';
     	ELSIF region_numeric = 2 THEN
       	    region = 'C';
        ELSE
       	    region = 'W';
        END IF;
	    INSERT INTO orders_regions_test_data(order_region) VALUES (region);
	END LOOP;
END //

DELIMITER ;

See the results for ten orders. Your results will vary, as order_region is randomly generated.

SELECT * FROM orders_regions_test_data LIMIT 10;
****
+--------------+
| order_region |
+--------------+
| C            |
| W            |
| W            |
| E            |
| C            |
| C            |
| W            |
| W            |
| E            |
| W            |
+--------------+

A similar use case for generating test data is given in Use Case: Generating Test Data, where test order regions and amounts are generated. In this use case, a temporary stored procedure is used instead of an anonymous code block; the temporary stored procedure can be called multiple times and different values for the order region and maximum order amounts can be supplied each time.