WL#3696: Dynamic Compound Statements

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"