Anonymous Code Blocks
On this page
Anonymous code blocks are unnamed blocks of SingleStore Procedural SQL (PSQL) code that are executed immediately.
DO [DECLARE variable_list] [ ...n ]BEGINprocedure_bodyEND
list: An optional list of variables along with their data types. These variables are visible inside the anonymous code block, only.
procedure_in the Arguments section in CREATE PROCEDURE.
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 PROCEDURESshows anonymous code blocks, only when run by a user with the
The same applies to the
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.
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.
Anonymous code blocks are similar to temporary stored procedures.
DELIMITER //DO BEGINECHO SELECT 1;END //DELIMITER ;
+---+ | 1 | +---+ | 1 | +---+
Suppose you want to generate test order regions to test an application that processes orders.
orders_ 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.
DELIMITER //DO DECLAREregion_numeric INT;region CHAR;BEGINFOR i in 1 .. 1000 LOOPregion_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 THENregion = 'E';ELSIF region_numeric = 2 THENregion = 'C';ELSEregion = 'W';END IF;INSERT INTO orders_regions_test_data(order_region) VALUES (region);END LOOP;END //DELIMITER ;
See the results for ten orders.
order_ 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 CREATE [OR REPLACE] TEMPORARY PROCEDURE …, where test order regions and amounts are generated.
Last modified: June 12, 2023