WL#3696: Dynamic Compound Statements
Affects: Server-7.0 — Status: Un-Assigned — Priority: Medium
Pass and process statements that contain statements, e.g. " mysql> delimiter // mysql> label1: BEGIN DECLARE v INT DEFAULT 7; SELECT v; END// +------+ | v | +------+ | 7 | +------+ 1 row in set (0.10 sec) Query OK, 0 rows affected (0.10 sec) " The feature is not standard but is in Oracle and DB2. It was once believed that it might solve a problem with OUT parameters for connectors.
Syntax ------ [label:] SQL-control statement [end label] The SQL-control statements for Persistent Stored Modules are: BEGIN/END "compound statements", CASE, IF, LOOP, WHILE, REPEAT, and the not-yet-implemented FOR (WL#3309). For example: " mysql> delimiter // mysql> begin declare v int; set v =7; select v; end// +------+ | v | +------+ | 7 | +------+ 1 row in set (0.10 sec) Query OK, 0 rows affected (0.10 sec) " Effectively this means that anything that can be in the body of a stored procedure -- not just single statements -- can be executed from mysql's command line, or passed as a statement from a connector. This is not new syntax; Peter Gulutzan is only saying that such "compound statements" (alternative name "control statements") can be prepared and executed dynamically, that is, they are "dynamic compound statements". History ------- This was "ISSUE #29" in "Specification of stored procedures", 24 Oct 2003: "ISSUE #29 (TRIVIAL): MySQL only allows BEGIN/END statements within stored procedures. That's normal. But I [Peter] don't see why we want such a restriction." [ mysql intranet ] /secure/mailarchive/mail.php?folder=5&mail=19197 The minutes of the Saint Petersburg meetings show that this came up for discussion as topic "Compound statements can occur anywhere", but apparently was not voted on. DB2 allows "Compound SQL (Dynamic) statement"s and says they "can be embedded in a trigger, SQL function, or SQL method, or issued through the use of dynamic SQL statements." DB2 does not allow labels. Oracle has "Anonymous Blocks" or "Anonymous PL/SQL Blocks". EnterpriseDB also calls them "Anonymous blocks". But MySQL prefers the term "dynamic compound statement" because MySQL stored procedures are like DB2's, and MySQL uses "compound statement" in the manual. PostgreSQL 9 will support "anonymous functions". In the standard, a compound statement is not preparable and therefore is not immediately executable. So this is an "extension". (Peter is using "preparable" in the general sense; whether MySQL PREPARE is okay is one of the points below.) Similarity to routines ---------------------- Konstantin Osipov's expectation is that "Internally there will be an anonymous procedure created for every compound statement ..." With this in mind, consider CREATE PROCEDURE's requirements. To use dynamic compound statements, does one need CREATE ROUTINE or ALTER ROUTINE privilege? Decision: no. There is no special privilege. The suggestion arose that people should require something extra if they're going to create long loopy statements, but that's not what privileges are for. Since one cannot specify "routine characteristics", what will they be? Decision: MODIFIES SQL DATA NOT DETERMINISTIC SQL SECURITY DEFINER /* Default. Won't matter since invoker=definer */ COMMENT '' /* Won't matter. */ Will dynamic compound statements appear in information_schema.routines, with NULL for ROUTINE_SCHEMA and ROUTINE_NAME (or perhaps some unique identifier derived from the connection and the label)? Decision: No. They are not persistent. Eventually one might see dynamic compound statements in performance_schema.statements, though (WL#2360). Does the usual restriction on USE statements apply? Decision: Yes. The statement "BEGIN USE d; END//" will cause "ERROR 1314 (0A000): USE is not allowed in stored procedures" or something similar. Will BEGIN ... END appear as a single statement in the slow log? If so, does long_query_time apply to the entire compound statement? Decision: not sure yet. Again, the behaviour should be what you get with stored procedures, but stored procedure behaviour might change. If @@sql_mode is set within a dynamic compound statement, the current values is 'pushed' when BEGIN happens and 'popped' when END happens. Therefore this has no effect: BEGIN SET @@SQL_MODE='ANSI'; END// Why? Because that's what a stored procedure would do. Similarity to events -------------------- Events, which also may have compound statements, have a limitation which the MySQL Reference Manual describes: "An event may not be created, altered, or dropped by a trigger, stored routine, or another event. An event also may not create, alter, or drop triggers or stored routines." Will something similar apply in this case? Decision: yes. a dynamic compound statement may not create, alter, or drop triggers or stored routines (functions and stored procedures) or events. PREPARE ------- The following statement will not be legal: PREPARE stmt1 FROM 'BEGIN DECLARE v INT; END'; There could be parser-related "pitfalls" with such syntax, and MySQL doesn't currently allow PREPARE within PREPARE. Therefore this task will not be a help for BUG#14115 Prepare() with compound statements breaks. SHOW STATUS and SHOW PROCESSLIST -------------------------------- In effect there is a BEGIN statement, an IF statement, etc. SHOW STATUS may have these new counters: Com_compound_statement Com_if Com_loop Com_repeat Com_while Why not Com_begin? Unfortunately MySQL still has Com_begin for the old BEGIN statement, the synonym for START TRANSACTION. The counter doesn't go up for each loop-statement iteration. It is not settled whether these counters will also go up for non-dynamic compound statements. SHOW PROCESSLIST, in the 'Info' field, will show the entire statement, for example "BEGIN DECLARE v INT; END" rather than each component statement. It is not settled how each counter applies, for example see BUG#24289 "Status Variable "Questions". Here it's only necessary to say that whatever applies for stored-procedure accounting should apply for dynamic compound statements. Connectors ---------- Konstantin proposed that dynamic compound statements may be useful for OUT parameters with connectors, as follows: " ... it appears that the only solution that works both in the text and binary modes and is also backward compatible is [dynamic compound statements]. Then a connector can wrap every call to a stored procedure into a block like: BEGIN DECLARE param1 INT; DELCARE param2 VARCHAR(16); CALL subject_procedure(param1, param2); SELECT param1, param2; END; In this case, on the CLI level the OUT parameters would look like a legitimate additional result set returned from a query. " References ---------- DB2 "Compound SQL (Dynamic) Statement" http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/admin/r0004240.htm Oracle "Anonymous Blocks" http://download-east.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_packages.htm#sthref769 EnterpriseDB "Anonymous Blocks" http://www.enterprisedb.com/documentation/spl-programs.html SQL Server "Batches" http://technet.microsoft.com/en-us/library/ms175502.aspx dev-public thread "Possible solution for BUG#17898 "No straightforward way to deal with output parameters"" [ mysql intranet ] /secure/mailarchive/mail.php?folder=5&mail=60840 WL#3524 Implement prepared statements for compound SQL BUG#11638 Cannot prepare and execute a stored procedure with OUT parameter BUG#17898 No straightforward way to deal with output parameters BUG#48777 "Interactive Procedure SQL"
Copyright (c) 2000, 2018, Oracle Corporation and/or its affiliates. All rights reserved.