Anonymous Code Blocks

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

DELIMITER //

DO BEGIN
    ECHO SELECT 1;
END //

DELIMITER ;

+---+
| 1 |
+---+
| 1 |
+---+

CREATE TABLE orders_regions_test_data(order_region CHAR);

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 ;

SELECT * FROM orders_regions_test_data LIMIT 10;

+--------------+
| order_region |
+--------------+
| C            |
| W            |
| W            |
| E            |
| C            |
| C            |
| W            |
| W            |
| E            |
| W            |
+--------------+

Related Topics

• CREATE [OR REPLACE] TEMPORARY PROCEDURE …

• CREATE PROCEDURE